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

Become A Member

Ronald Lam

United States

Member since October 24, 2012

This is the unofficial Google Spreadsheets generally asked inquiries web page

Standard Questions

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

How can I swap off the autosave alternative

How can I have far more than 100 rows ?

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

How can I create a custom made format ?

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

How does the autofill feature operate?

How do I hide the gridlines.

Can I use names for ranges?

How can I make an e-mail deal with stay/clickable?

Time and date connected concerns.

How can I determine performing times ?

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

How can I format time with out hours?

How can I format time values with out the seconds ?

How can I add instances ?

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

How to show the day of the week ?

Suggestions & tricks

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

Shorter URL for shared spreadsheets.

Employing intermediate outcomes.

Making graphics without having graphs.

List of not (still?) applied features

Request a characteristic

Beneficial inbound links

Search Google Spreadsheets

Your Google on-line files

The Google Document discussion board

Introduction to spreadsheets (pdf)

Tech on the net sophisticated (excel) things

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

No, you do not require a account, but you do will need a online investing courses account.

It is incredibly simple to attain 1. Right here is the url


If you deliver an invitation to reveal a spreadsheet to an e-mail deal with that is not connected with a Google account, the invitee is automatically invited to develop a Google account initially.


A account is not the same as a account.

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

A account is always routinely a account.

How can I switch off the autosave solution

You are not able to, simply because that would ruin the most simple feature of the software The capacity to share information in real time. But you can

get to your personal laptop or computer as backup ( - Export , pick .ods or .xls)

make a duplicate of your spreadsheet with a different name like mysheet - in progress ( - Help you save a duplicate)

In case you will need to revert again to an older version you can then

- Add new edition to restore from your possess computer

- Rename to consolidate a steady version

- 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 functionality to screen formats that are not in the menu.

See also Utilizing intermediate resultsandHow can I create a customized format ? How can I produce a custom format ?

Customized formats can be developed employing the TEXT() functionality. Several undocumented formats are readily available.

Click right here for a record of custom made format stock investing online directives

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

1 of the protection settings for IE on your desktop is most probably set to disable binary and script behaviors beneath ActiveX controls. Environment this to permit must deal with this and exhibit your spreadsheet.

It may well also be necessary to obvious your cache Instruments-World-wide-web Possibilities-Delete short term files How does the autofill element function?

If you pick a column, row or block, Ctrlr will propagate the formula(s) in the most left column to all the chosen cells to the appropriate, though Ctrld will propagate the leading row down. You can also copy a single cell/row/column and paste it to a block of cells. How can I have much more than a hundred rows ?

Pick all cells (by clicking the square above row 1 and left of column A)

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

Repeat previously mentioned methods and you can insert 200 rows, then 400, and so on. How to cover gridlines

In excel, pick out an spot (or all cells) and then select FormatCellsBorder.

In this dialog box, select Color White (or whatever is your default cell qualifications coloring), and then select the Outline and Inside buttons underneath Presets. Then verify Ok. Now you have a totally white part (or sheet), i.e. since 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 does not online investing reviews provide a mechanism to define them. The only way to produce them is in Excel or Open up Workplace. How? How can I make an e-mail handle stay/clickable?

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

The workaround is to use the HYPERLINK() function in mixture with a limited javascript as follows

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

E-mail deal with in A1

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

Time and date associated concerns. How can I calculate performing days ?

Starttime in A1, conclusion time in B1 use the system MOD(B1-A1,1)

This formula is right also for night shifts. (when A1 B1) How can I get the neighborhood time/date for my timezone?

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

Use the TEXT() perform 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 with no the hrs?

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

Proper(TEXT(A1, "mmss"),5)- minutes and online investing trading seconds

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

Occasions can be additional just as quantities, the query is how to display them. See following problem. How can I show time values higher than 24 hours ?

Use the components

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

The components INT(A1*24) & TEXT(A1,"mm") functions as well, but is not generally specific to the second.(due to complications with the HOUR purpose and rounding glitches respectively) How to screen the day of the week ?

There are two approaches. The brief way is


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

- or -

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

The benefit of the latter is that it can be tailored to other languages and is considerably less cryptic.

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

Get the file to Excel with -export-.xls


Down load the file to Open up Place of work with -export-.ods

Open the downloaded file in Excel or Open Place of work

Do whichever you want to do and couldn't do in Gsheets and help you save the alterations.

Go back to the Google Spreadsheet in you browser and pick -Add new edition

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

You can get a easy/limited version of the URL for a given spreadsheet from the "Sharing Selections" pane. investing online Using intermediate benefits.

Quite a few formulas and formats readily available in Excel can be emulated in Gsheets with the use of intermediate effects. Also formulas can be held easy and far more concise making use of intermediate results. This requires further cells which you almost certainly do not want to see in your presentation. It is therefore recommendable to use cells in a committed worksheet. Why don't we simply call this sheet "Scratch".

Example You want a date displayed as 24-three-06, a format not accessible in the format menu. Place this date someplace on sheet Scratch, say D5, and in the cell the place you want to show it set the system TEXT('Scratch'!$D$five,"dd-m-yy") How do I generate a graph of my knowledge?

Even though graphics are not carried out in Gsheets, there is a uncomplicated and elegant workaround utilizing the REPT perform and the "" character.

Suppose the data to be exhibited in a graph is in column A, then propagate the following formula in the column in which you want the graph

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

Replace the amount ninety nine by the utmost amount of characters you want shown and A by the column the knowledge is in.


At the above url you will find additional refined suggestions to emulate graphics. Not implemented (nevertheless?)

API (App Programmers Interface)



Conditional formatting

Customized formats

Drop down lists

Discover / Replace

Freeze columns


Pivot tables

Macro assist

Merge vertically

Referring ranges in other spreadsheet files


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



Previous modified by Lexcel 2006-10-20 1323 UTC

Suggestions for this page are welcome.

about HayesFrench2005

Contact Ronald Lam

My Interests

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