Google Spreadsheets FAQ 

This is the unofficial Google Spreadsheets frequently asked questions site

Do I need to have a gmail account to use Google Spreadsheets?

No, you don't need a account, but you do need a account.
It is very easy to obtain one. Here is the link:
If you send an invitation to share a spreadsheet to an email address that is not associated with a Google account, the invitee is automatically invited to create a Google account first.
A account is not the same as a account.
A account can be created with any email address (like
A account is always automatically a account.

How can I switch off the autosave option

You can't, because that would destroy the most basic feature of the application: The ability to share data in real time. But you can :
  • download to your own computer as backup ( -> Export , choose .ods or .xls)
  • make a copy of your spreadsheet with another name like mysheet - in progress ( -> Save a copy)
In case you need to revert back to an older version you can then:
  • -> Upload new version ; to restore from your own computer
  • -> Rename ; to consolidate a stable version
  • -> Delete ; to delete an obsolete version

The format I want is not in the menu. What can I do?

You can use the TEXT function to display formats that are not in the menu.

How can I create a custom format ?

Custom formats can be created using the TEXT() function. Many undocumented formats are available.
Click here for a list of custom format directives

I have opened up a spreadsheet and it is totally black.

One of the security settings for IE on your desktop is most likely set to disable binary and script behaviors under ActiveX controls. Setting this to enable should fix this and show your spreadsheet.
It might also be necessary to clear your cache: Tools->Internet Options->Delete temporary files

How does the autofill feature work?

If you select a column, row or block, Ctrl+r will propagate the formula(s) in the most left column to all the selected cells to the right, while Ctrl+d will propagate the top row down. You can also copy one cell/row/column and paste it to a block of cells.

How can I have more than 100 rows ?

Select all cells (by clicking the square above row 1 and left of column A)
Click the button. Now you have the option to add 100 rows.
Repeat above steps and you can insert 200 rows, then 400, etc.

How to hide gridlines

In excel, select an area (or all cells) and then choose Format|Cells|Border.
In this dialog box, select Color: White (or whatever is your default cell background color), and then select the Outline and Inside buttons under Presets. Then confirm: OK. Now you have a completely white section (or sheet), i.e.: because the grid is white and the cells are also white, you have "hidden" the gridlines. Now import this to Gsheets.

Can I use names for ranges?

Gsheets supports named ranges but does not supply a mechanism to define them. The only way to create them is in Excel or Open Office. How?

How can I make an email address live/clickable?

http:// and ftp:// are recognised by Gsheets and produce clickable hyperlinks, however mailto: is not implemented.
The workaround is to use the HYPERLINK() function in combination with a short javascript as follows: - or -

Time and date related questions.

How can I calculate working times ?

Starttime in A1, end time in B1 : use the formula =MOD(B1-A1,1)
This formula is correct also for night shifts. (when A1 > B1)

How can I get the local time/date for my timezone?

NOW() and TODAY() return values in PDT/PST (Pacific Daylight saving Time/Pacific Standard Time). This can be compensated by subtracting the offset to UTC (Coordinated Universal Time) of PDT/PST and adding the offset of your own timezone. E.g. PST has an offset of -7 hours to UTC and CEDT (Central European Daylight Time) has an offset of + 2 hours to UTC. So 'now' in CEDT =(NOW() -(-7/24)+(2/24)). Click here for a table of timezones

How can I format time values without the seconds ?

Use the TEXT() function: =TEXT(A1, "HH:mm")
Or for example =TEXT(Scratch!$D$5, "HH:mm") if you use a scratch sheet to hide your raw data.

How can I format time without the hours?

=RIGHT(TEXT(A1, ":mm"),2) - only minutes
=RIGHT(TEXT(A1, ":mm:ss"),5)- minutes and seconds
=RIGHT(TEXT(A1, ":mm:ss.SSS"),9) - displays also fractions of a second (upto milliseconds)

How can I add times ?

Times can be added just as numbers, the question is how to display them. See next question.

How can I display time values greater than 24 hours ?

Use the formula:
=INT(A1)*24 + HOUR(A1) & TEXT(A1,":mm")
The formula =INT(A1*24) & TEXT(A1,":mm") works too, but is not always exact to the second.(due to problems with the HOUR function and rounding errors respectively)

How to display the day of the week ?

There are two ways. The short way is:
The other way is: =CHOOSE(WEEKDAY(A1),"Sun","Mon","Tues","Wednes","Thurs","Fri","Satur","Sun") & "day"
 - or -
=CHOOSE(WEEKDAY(A1), "Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
The advantage of the latter is that it can be adapted to other languages and is less cryptic.

Miscellaneous tips & tricks

I want to do XXX. I can do XXX in Excel/Open Office, but not in Gsheets. What can I do?

  1. Download the file to Excel with ->export->.xls
    Download the file to Open Office with ->export->.ods
  2. Open the downloaded file in Excel or Open Office
  3. Do whatever you need to do and couldn't do in Gsheets and save the changes.
  4. Go back to the Google Spreadsheet in you browser and choose ->Upload new version
    If you are the only editor of the file, you can skip step 1.

Shorter URL for shared spreadsheets.

You can get a simple/short version of the URL for a given spreadsheet from the "Sharing Options" pane.

Using intermediate results.

Many formulas and formats available in Excel can be emulated in Gsheets with the use of intermediate results. Also formulas can be kept simple and more concise using intermediate results. This requires extra cells which you probably don't want to see in your presentation. It is therefore recommendable to use cells in a dedicated worksheet. Let's call this sheet "Scratch".
Example: You want a date displayed as 24-3-06, a format not available in the format menu. Put this date somewhere on sheet Scratch, say D5, and in the cell where you want to display it put the formula: =TEXT('Scratch'!$D$5,"dd-m-yy")

How do I create a graph of my data?

Although graphics are not implemented in Gsheets, there is a simple and elegant workaround using the REPT function and the "|" character.
Suppose the data to be displayed in a graph is in column A, then propagate the next formula in the column where you want the graph:
=REPT("|",(A1 - MIN(A:A)) * 99 / (MAX(A:A) - MIN(A:A)))
Replace the number 99 by the maximum number of characters you want displayed and A by the column the data is in.
At the above link you will find more sophisticated ideas to emulate graphics.

Not implemented (yet?):

  • API (Application Programmers Interface)
  • Autofilter
  • Comments
  • Conditional formatting
  • Custom formats
  • Drop down lists
  • Find / Replace
  • Freeze columns
  • Graphics
  • Pivot tables
  • Macro support
  • Merge vertically
  • Referring ranges in other spreadsheet files
  • Validation
  • 3D referencing like SUM('sheet1:sheet9'!C6)