Join our network of non-profits, companies and individuals who believe social change can happen through design.

Become A Member

Elizabeth Lavine

United States

Member since December 06, 2012

This is the unofficial Google Spreadsheets generally asked questions website

Basic Issues

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

How can I change off the autosave solution

How can I have additional than a hundred rows ?

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

How can I produce a custom format ?

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

How does the autofill feature perform?

How do I conceal the gridlines.

Can I use names for ranges?

How can I make an e mail tackle commodity prices live/clickable?

Time and date connected queries.

How can I determine functioning days ?

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

How can I format time with out hours?

How can I format time values without the seconds ?

How can I add moments ?

How can I show time values which are over 24 hrs ?

How to display the day of the week ?

Recommendations & methods

I want to do XXX. I can do XXX in Excel/Open up Place of work, but not in Gsheets. What can I do?

Shorter URL for shared spreadsheets. day trading

Employing intermediate outcomes.

Developing graphics without having graphs.

List of not (yet?) applied characteristics

Ask for a function

Beneficial inbound links

Lookup Google Spreadsheets

Your Google on-line paperwork

The Google Document forum

Introduction to spreadsheets (pdf)

Tech on the internet superior (excel) stuff

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

No, you never need a account, but you do need a account.

It is incredibly effortless to obtain one particular. Right here is the hyperlink


If you deliver an invitation to share a spreadsheet to an e mail deal with that is not associated trade rush with a Google account, the invitee is immediately invited to generate a Google account first.


A account is not the similar as a account.

A account can be produced with any electronic mail tackle (like )

A account is constantly automatically a account.

How can I change off the autosave solution

You are not able to, since that would demolish the most basic feature of the application The skill to reveal info in genuine time. But you can

down load to your private laptop or computer as backup ( - Export , choose .ods or .xls)

make a duplicate stock trading platform of your spreadsheet with yet another name like mysheet - in progress ( - Help save a duplicate)

In situation you need to have to revert back again to an older edition you can then

- Add new model to restore from your own pc

- Rename to consolidate a secure edition

- Delete to delete an out of date model The format I want is not in the menu. What can I do?

You can use the TEXT operate to exhibit formats that are not in the menu.

See also Working with intermediate resultsandHow can I develop a custom format penny stocks ? How can I generate a custom format ?

Custom formats can be made working with the TEXT() operate. Several undocumented formats are accessible.

Click the following for a record of customized format directives

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

One of the safety configurations for IE on your desktop is most very likely set to disable binary and script behaviors underneath ActiveX controls. Setting this to enable ought to repair this and display your spreadsheet.

It may also be needed to crystal clear your cache Equipment-World wide web Alternatives-Delete momentary information How does the autofill characteristic function?

If you pick out a column, row or block, Ctrlr will propagate the formula(s) in the most left column to all the picked cells to the proper, whilst Ctrld will propagate the top row down. You can also duplicate 1 cell/row/column and paste it to a block of cells. How can I have much more than one hundred rows ?

Pick all cells (by clicking the sq. above row one and left of column A)

Click the button. Now you have the selection to add 100 rows.

Repeat over steps and you investment online can insert 200 rows, then four hundred, and so forth. How to hide gridlines

In excel, choose an region (or all cells) and then opt for FormatCellsBorder.

In this dialog box, choose Coloration White (or whatever is your default cell background coloring), and then pick the Outline and Within buttons underneath Presets. Then validate Okay. Now you have a fully white part (or sheet), i.e. because the grid is white and the cells are also white, you have "concealed" the gridlines. Now import this to Gsheets. Can I use names for ranges?

Gsheets supports named ranges but etfs does not provide a mechanism to outline them. The only way to produce them is in Excel or Open Place of work. How? How can I make an e mail deal with reside/clickable?

http// and ftp// are recognised by Gsheets and generate clickable hyperlinks, however mailto is not applied.

The workaround is to use the HYPERLINK() functionality in mix with a brief javascript as follows

HYPERLINK("http//","Send mail to Unofficial Q & A") - or -

E mail deal with in A1

HYPERLINK("http//"& A1, A1)

Time and date relevant inquiries. How can I determine performing moments ? commodities market

Starttime in A1, end time in B1 use the formula MOD(B1-A1,1)

This system is correct also for night time shifts. (when A1 B1) How can I get the regional time/date for my timezone?

NOW() and Nowadays() return values in PDT/PST (Pacific Daylight protecting Time/Pacific Typical Time). This can be compensated by subtracting the offset to UTC (Coordinated Universal Time) of PDT/PST and incorporating the offset of your personal timezone. E.g. PST has an offset of -7 hours to UTC and CEDT (Central European Daylight Time) has an offset of two hrs to UTC. So 'now' in CEDT (NOW() -(-seven/24)(2/24)). Click here for a table of timezones How can I format time values devoid of the seconds ?

Use the TEXT() purpose TEXT(A1, "HHmm")

Or for case in point TEXT(Scratch!$D$five, "HHmm") if you use a scratch sheet to disguise your raw knowledge. How can I format time devoid of the hours?

Correct(TEXT(A1, "mm"),two) - only minutes

Proper(TEXT(A1, "mmss"),five)- minutes and seconds

Right(TEXT(A1, "mmss.SSS"),9) - displays also fractions of a 2nd (upto milliseconds) How can I include occasions ?

Instances can be extra just as figures, buy stocks the question is how to show them. See subsequent problem. How can I exhibit time values higher than 24 hrs ?

Use the components

INT(A1)*24 HOUR(A1) & TEXT(A1,"mm")

The formulation INT(A1*24) & TEXT(A1,"mm") works as well, but is not constantly exact to the second.(because of to complications with the HOUR operate and rounding mistakes respectively) How to exhibit the day of the week ?

There are two approaches. The brief way is


The other way is Select(WEEKDAY(A1),"Sun","Mon","Tues","Wednes","Thurs","Fri","Satur","Sunlight") & "day"

- or -

Pick(WEEKDAY(A1), "Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

The advantage of the latter forex software is that it can be tailored to other languages and is significantly less cryptic.

Miscellaneous ideas & methods I want to do XXX. I can do XXX in Excel/Open up Workplace, but not in Gsheets. What can I do?

Download the file to Excel with -export-.xls


Get the file to Open Workplace with -export-.ods

Open up the downloaded file in Excel or Open Office

Do no matter you need to do and could not do in Gsheets and help you save the changes.

Go again to the Google Spreadsheet in you browser and decide on -Upload new edition

If you are the only editor of the file, you can skip action 1. Shorter URL for shared spreadsheets.

You can get a basic/short model of the URL for a presented spreadsheet from the "Sharing Possibilities" pane. Making use of intermediate results.

A lot of formulas and formats readily available in Excel can be emulated in Gsheets with the use of intermediate benefits. Also formulas can be stored uncomplicated and more concise employing intermediate benefits. This involves added cells which you possibly do not want to see in your presentation. It is therefore recommendable to use cells in a committed forex charts worksheet. Let us phone this sheet "Scratch".

Example You want a date displayed as 24-3-06, a format not obtainable in the format menu. Set this date somewhere on sheet Scratch, say D5, and in the cell wherever you want to display it put the formulation TEXT('Scratch'!$D$five,"dd-m-yy") How do I generate a graph of my info?

Despite the fact that graphics are not applied in Gsheets, there is a simple and classy workaround employing the REPT functionality and the "" character.

Suppose the knowledge to be exhibited in a graph is in column A, then propagate the trade oil next formulation in the column the place you want the graph

REPT("",(A1 - MIN(AA)) * 99 / (MAX(AA) - MIN(AA)))

Exchange the amount ninety nine by the greatest number of characters you want shown and A by the column the knowledge is in.


At the higher than backlink you will come across much more subtle strategies to emulate graphics. Not carried out (nevertheless?)

API (Software Programmers Interface)



Conditional formatting

Custom made formats

Drop down lists

Find / Replace

Freeze columns


Pivot tables

Macro help

Merge vertically

Referring ranges in other spreadsheet information


3D referencing like SUM('sheet1sheet9'!C6)



Previous modified by Lexcel 2006-10-twenty 1323 UTC

Strategies for this webpage are welcome.

about HavivaHyde1971

Contact Elizabeth Lavine

My Interests

  • Industrial Design
  • Environmental Design
  • Communication Design
  • Fashion Design
  • Audio/Visual Design