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

Become A Member

Hassan Blackburn

United States

Member since November 28, 2012

This is the unofficial Google Spreadsheets usually asked queries web page

Normal Issues

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

How can I change off the autosave option

How can I have a lot more than one hundred 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 fully black.

How does the autofill function work?

How do I cover the gridlines.

Can I use names for ranges?

How can I make an electronic mail address stay/clickable?

Time and date relevant concerns.

How can I calculate working times ?

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

How can I format time without hours?

How can I format time values without the seconds ?

How can I add occasions ?

How can I exhibit time values which are more than 24 hours ?

How to show the day of the week ?

Guidelines & methods

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

Shorter URL for shared spreadsheets. best stocks

Employing intermediate outcomes.

Making graphics devoid of graphs.

List of not (but?) carried out attributes

Ask for a feature

Valuable links

Research Google Spreadsheets

Your Google on-line documents

The Google Document discussion board

Introduction to spreadsheets (pdf)

Tech on the web state-of-the-art (excel) things

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

No, you really don't need a account, but you do need to have a account.

It is incredibly effortless to attain one. The following is the website link


If you send an invitation to reveal a spreadsheet to an email tackle that is not connected with a Google account, the invitee is instantly invited to create a Google account first.


A account is not the exact same as a account.

A account can be made with any e mail deal with (like )

A account is usually automatically a account.

How can I switch off the autosave alternative

You are not able to, due to the fact that would demolish the most fundamental attribute of the application The capability to share information in real time. But you can

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

make a duplicate options trading of your spreadsheet with one more name like mysheet - in progress ( - Help you save a copy)

In case you require to revert back again to an older version you can then

- Add new edition to restore from your personal computer

- Rename to consolidate a secure version

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

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

See also Employing intermediate resultsandHow can I develop a custom format penny stocks ? How can I make a custom made format ?

Custom made formats can be made employing the TEXT() purpose. Quite a few undocumented formats are obtainable.

Click here for a list of custom made format directives

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

One particular of the protection configurations for IE on your desktop is most very likely set to disable binary and script behaviors under ActiveX controls. Environment this to allow ought to resolve this and present your spreadsheet.

It may possibly also be necessary to obvious your cache Instruments-Internet Options-Delete short term files How does the autofill characteristic operate?

If you choose a column, row or block, Ctrlr will propagate the method(s) in the most left column to all the chosen cells to the suitable, although Ctrld will propagate the leading row down. You can also duplicate 1 cell/row/column and paste it to a block of cells. How can I have far more than 100 rows ?

Select all cells (by clicking the sq. previously mentioned row one and left of column A)

Click the button. Now you have the selection to include one hundred rows.

Repeat previously mentioned methods and you forex charts can insert 200 rows, then 400, etc. How to conceal gridlines

In excel, choose an location (or all cells) and then pick FormatCellsBorder.

In this dialog box, select Color White (or whichever is your default cell qualifications coloring), and then choose the Outline and Inside of buttons beneath Presets. Then verify Ok. Now you have a completely white segment (or sheet), i.e. mainly 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 create them is in Excel or Open up Workplace. How? How can I make an e mail tackle reside/clickable?

http// and ftp// are recognised by Gsheets and generate clickable hyperlinks, on the other hand mailto is not carried out.

The workaround is to use the HYPERLINK() operate in combo with a small javascript as follows

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

E-mail address in A1

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

Time and date related concerns. How can I determine doing work instances ?

Starttime in A1, conclusion time in B1 use the formula MOD(B1-A1,one)

This components is correct also for evening shifts. (when A1 B1) How can I get the local 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 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 trade rush CEDT (NOW() -(-seven/24)(2/24)). Click on here for a table of timezones How can I format time values with no the seconds ?

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

Or for instance TEXT(Scratch!$D$5, "HHmm") if you use a scratch sheet to conceal your raw info. How can I format time devoid of the hours?

Appropriate(TEXT(A1, "mm"),2) - only minutes

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

Appropriate(TEXT(A1, "mmss.SSS"),nine) - shows also fractions of a second (upto milliseconds) How can I add occasions ?

Times can be extra just as quantities, commodity trading the question is how to display them. See up coming concern. How can I exhibit time values better than 24 hrs ?

Use the formula

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

The components INT(A1*24) & TEXT(A1,"mm") works as well, but is not usually exact to the 2nd.(due to complications with the HOUR operate and rounding glitches respectively) How to screen the day of the week ?

There are two ways. The limited way is


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

- or -

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

The advantage of the latter penny stocks is that it can be tailored to other languages and is less cryptic.

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

Obtain the file to Excel with -export-.xls


Obtain the file to Open up Business office with -export-.ods

Open up the downloaded file in Excel or Open up Business office

Do whatever you want to do and could not do in Gsheets and help you save the modifications.

Go again 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 1. Shorter URL for shared spreadsheets.

You can get a straightforward/small version of the URL for a provided spreadsheet from the "Sharing Choices" pane. Working with intermediate benefits.

Numerous formulas and formats available in Excel can be emulated in Gsheets with the use of intermediate benefits. Also formulas can be stored simple and a lot more concise employing intermediate effects. This demands further cells which you in all probability never want to see in your presentation. It is therefore recommendable to use cells in a committed commodities market worksheet. Why don't we get in touch with this sheet "Scratch".

Example You want a date displayed as 24-3-06, a format not obtainable in the format menu. Put this date somewhere on sheet Scratch, say D5, and in the cell where you want to exhibit it set the formulation TEXT('Scratch'!$D$5,"dd-m-yy") How do I create a graph of my data?

While graphics are not implemented in Gsheets, there is a easy and stylish workaround utilizing the REPT operate and the "" character.

Suppose the data to be displayed in a graph is in column A, then propagate the next formulation in the column exactly where you want the graph

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

Substitute the number 99 by the highest number of characters you want shown and A by the column the data is in.


At the over hyperlink you will locate a lot more innovative tips to emulate graphics. Not applied (still?)

API (Software Programmers Interface)



Conditional formatting

Custom made formats

Drop down lists

Locate / Substitute

Freeze columns


Pivot tables

Macro assistance

Merge vertically

Referring ranges in other spreadsheet information


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



Last modified by Lexcel 2006-ten-20 1323 UTC

Suggestions for this web page are welcome.

about HannaAguirre1985

Contact Hassan Blackburn

My Interests

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