Using Google™ Spreadsheets

Next Page

Book Cover
Using Google™ Spreadsheets
By Michael Miller
...............................................
Publisher: Que
Pub Date: July 26, 2006
Print ISBN-10: 0-7686-6833-6
Print ISBN-13: 978-0-7686-6833-9
Pages: 50

Table of Contents
Overview

Google Spreadsheets is the new web-based spreadsheet application that's giving Microsoft Excel a run for its money. With Google Spreadsheets, you can create complex spreadsheets online, using any web browser, complete with sophisticated formulas and functions. You can even share spreadsheets online and perform collaborative editing on those spreadsheets with other users anywhere in the world. And the best thing is, Google Spreadsheets is completely free!

Using Google Spreadsheets is the first e-book to cover Google's innovative online spreadsheet program. You learn how to create new web-based spreadsheets, upload and work with existing Excel spreadsheets, create complex formulas, apply a variety of formatting effects, and let other users share and edit their spreadsheets. Delivered in Adobe PDF format for quick and easy access, Using Google Spreadsheets includes everything you need to create your own sophisticated online spreadsheets. It also includes a detailed reference to the more than 200 functions included in the program, and a great cheatsheet you can print off and refer to for quick reference.

Next Page


Table of Contents

Previous Page
Next Page

Book Cover
Using Google™ Spreadsheets
By Michael Miller
...............................................
Publisher: Que
Pub Date: July 26, 2006
Print ISBN-10: 0-7686-6833-6
Print ISBN-13: 978-0-7686-6833-9
Pages: 50

Table of Contents

Copyright
Introduction
We Want to Hear from You!
Section 1. Introducing Google Spreadsheets
 | What's Unique About Google Spreadsheets?
 | Comparing Google Spreadsheets to Excel
 | Other Web-Based Spreadsheets
 | Privacy and Security Concerns
 | Should You Use Google Spreadsheets?
Section 2. Migrating from Excel to Google Spreadsheets
Section 3. Navigating the Google Spreadsheets Workspace
 | Understanding Elements of the Workspace
 | Navigating with the Keyboard
 | Freezing Rows While You Scroll
Section 4. Creating, Opening, and Saving Google Spreadsheets
 | Creating a New Spreadsheet
 | Opening an Existing Spreadsheet
 | Importing a Spreadsheet from Excel
 | Saving a Spreadsheet
 | Saving a Copy of a Spreadsheet
 | Renaming a Spreadsheet
 | Exporting a Google Spreadsheet to Excel Format
 | Deleting a Spreadsheet
Section 5. Entering and Editing Data
 | Entering New Data
 | Editing Previously Entered Data
Section 6. Working with Ranges
 | Understanding Ranges
 | Selecting a Range
Section 7. Sorting Data
 | Executing a Sort
 | Sorting a Range
 | Performing Multiple-Column Sorts
Section 8. Inserting, Deleting, Moving, and Copying Data
 | Inserting Rows and Columns
 | Deleting Data
 | Moving Data with the Cut and Paste Commands
 | Copying Data
Section 9. Changing Column Width and Row Height
 | Changing Column Width
 | Changing Row Height
Section 10. Formatting Google Spreadsheets
 | Enhancing Text with Bold, Italic, and Other Attributes
 | Changing Fonts and Type Sizes
 | Aligning Information in a Cell
 | Changing Number Formats
 | Changing the Background Color of Cells and Ranges
Section 11. Entering and Editing Formulas
 | Understanding Formulas
 | Entering Formulas
 | Creating Formulas with Your Mouse
 | Editing Formulas
Section 12. Using Functions
 | Understanding Functions
 | Entering Functions
 | Using the SUM Function
 | Using the COUNT Function
 | Using the AVERAGE Function
 | Using the MIN Function
 | Using the MAX Function
 | Using the PRODUCT Function
 | Other Google Spreadsheets Functions
Section 13. Working with Multiple Sheets
 | Adding New Sheets
 | Renaming Sheets
 | Deleting Sheets
Section 14. Printing Google Spreadsheets
Section 15. Sharing and Collaborating with Google Spreadsheets
 | Sharing a Spreadsheet for Viewing
 | Sharing a Spreadsheet for Collaboration
 | Chatting with Spreadsheet Viewers
 | Rescinding Access
 | Versioning Your Work in Progress
Section 16. A Final Word
Command Reference

Previous Page


Copyright

INCLUDES A HANDY, PRINTABLE COMMAND REFERENCE

Print it and keep it next to your keyboard while getting up to speed!

Copyright © 2007 by Que Publishing

All rights reserved. No part of this document shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this document, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.

Trademarks

All terms mentioned in this document that are known to be trademarks or service marks have been appropriately capitalized. Que Publishing cannot attest to the accuracy of this information. Use of a term in this document should not be regarded as affecting the validity of any trademark or service mark.

Google is a trademark of Google, Incorporated.

Warning and Disclaimer

Every effort has been made to make this document as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an "as is" basis. The author(s) and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this document.

PUBLISHER Paul Boger
ASSOCIATE PUBLISHER Greg Wiegand
ACQUISITIONS EDITOR Michelle Newcomb
DEVELOPMENT EDITOR Rick Kughen
MANAGING EDITOR Patrick Kanouse
PROJECT EDITOR Tonya Simpson
INDEXER Tim Wright
PROOFREADER Elizabeth Scott
TECHNICAL EDITOR Rajat Gupta
PUBLISHING COORDINATOR Cindy Teeters
MULTIMEDIA DEVELOPER Dan Scherf
BOOK DESIGNER Anne Jones


Introduction

Introducing Google Spreadsheets | 4
Migrating from Excel to Google Spreadsheets | 12
Navigating the Google Spreadsheets Workspace | 12
Creating, Opening, and Saving Google Spreadsheets | 21
Entering and Editing Data | 26
Working with Ranges | 27
Sorting Data | 29
Inserting, Deleting, Moving, and Copying Data | 30
Changing Column Width and Row Height | 33
Formatting Google Spreadsheets | 34
Entering and Editing Formulas | 39
Using Functions | 42
Working with Multiple Sheets | 64
Printing Google Spreadsheets | 65
Sharing and Collaborating with Google Spreadsheets | 65
A Final Word | 70
Index | 71
Command Reference | 73


We Want to Hear from You!

As the reader of this document, you are our most important critic and commentator. We value your opinion and want to know what we're doing right, what we could do better, what areas you'd like to see us publish in, and any other words of wisdom you're willing to pass our way.

As an associate publisher for Que Publishing, I welcome your comments. You can email or write me directly to let me know what you did or didn't like about this documentas well as what we can do to make our electronic documents better.

Please note that I cannot help you with technical problems related to the topic of this document. We do have a User Services group, however, where I will forward specific technical questions related to the document.

When you write, please be sure to include this document's title and author as well as your name, email address, and phone number. I will carefully review your comments and share them with the author and editors who worked on the document.

Email: feedback@quepublishing.com

Mail: Greg Wiegand
Associate Publisher
Que Publishing
800 East 96th Street
Indianapolis, IN 46240 USA

For more information about this document or another Que Publishing product, visit our website at www.quepublishing.com. Type the ISBN (excluding hyphens) or the title of a document in the Search field to find the page you're looking for.


Section 1. Introducing Google Spreadsheets

Google Spreadsheets is a web-based spreadsheet that mimics some of the key features of freestanding spreadsheet programs, such as Microsoft Excel. Using a familiar row-and-column format, Google Spreadsheets allows you to manipulate numeric data in a variety of ways. Instead of using ledger paper, calculator, and pencil, you can now use Google Spreadsheets to do both simple and complex number-crunching activitiesfrom any computer connected to the Internet.

Google Spreadsheets was born in Google Labs (labs.google.com), which is the incubator for many of Google's most innovative applications. Google, of course, is the Internet's most popular search site, and also host to a variety of search-based and non-search applicationsincluding Google Maps, Gmail, Picasa, Blogger, and the like.

You access Google Spreadsheets (shown in Figure 1) at spreadsheets.google.com.

Figure 1. Access Google Spreadsheets at spreadsheets.google.com.

[View full size image]

Note

Learn more about Google and all its services and applications in my companion book, Googlepedia (Michael Miller, Que Publishing, 2006).

Note

Google Spreadsheets is currently an experimental product in beta test. This means that Google may (or may not) add or change functionality as the test proceeds.


What's Unique About Google Spreadsheets?

At first blush, Google Spreadsheets looks pretty much like every other spreadsheet you've ever seen. You can enter numbers, words, formulas, functionsyou name itinto any cell, and then format each cell as you like. And, as with Excel, you can have multiple sheets in each spreadsheet.

What's unique about Google Spreadsheets is that it's all web-based. The application and all your spreadsheets reside on Google's server, not on your computer. One nice thing about this is that your spreadsheets can be accessed wherever you are, from any PC; you'll never discover that the spreadsheet you need is located on your office PC when you're at home or away. The other nice thing is that, by being web-based, you can share your spreadsheets with others. That makes workgroup collaboration possible, which is something you don't have with Excel and other spreadsheet programs.

Another benefit of being web-based is that you can't lose your worktheoretically, anyway. Once you've named the spreadsheet you're working on, Google Spreadsheets saves your file on its servers. From that point on, every change you make to the spreadsheet gets saved to the Google servers automatically. Nothing gets lost if you close your web browser, navigate to another website, or even turn off your computer. Everything you do is saved by Google.

Caution

Given the way in which websites and web browsers work (or sometimes don't), it's always possible that your latest changes might not make it to Google's servers; server overload or a slow connection can sometimes cause your changes to take more time than expected to get stored on the server. Bottom line, even though Google goes to great lengths to avoid data loss, there's always some slight chance of losing your latest data when the Internet is involved.

The other thing that's unique about Google Spreadsheets is that it's free. That's free, as in it costs zero dollars, unlike the increasingly more expensive Microsoft Excel. Being free makes it easy to take for a test drive, and even easier to add to your bag of applications. Many early users who've tried Google Spreadsheets have said that they're likely to switch from Excel; it can do almost everything Excel can do, from a numbers standpoint (there aren't any charts and graphs as yet), and it's perfect for corporate and small business environments.

Note

Google Spreadsheets looks to be the first component of a full-featured Google office suite. Next up? Google recently purchased Writely, a web-based word processor; look for it to join Google Spreadsheets online in the near future.


Comparing Google Spreadsheets to Excel

If you've used Microsoft Excel at all in the past, Google Spreadsheets will look somewhat familiarto a point. Although Google Spreadsheets adds some unique web-based collaboration features, it also lacks some features that you might be used to in Excel, such as charts and macros. So the two programs, while similar, end up being somewhat different. (Figures 2 and 3 show how similar the two applications look.)

Figure 2. A blank spreadsheet in Google Spreadsheets...

[View full size image]

Figure 3. ...compared to a similar blank spreadsheet in Microsoft Excel.

[View full size image]

Just how similaror differentare Google Spreadsheets and Excel? Table 1 compares and contrasts the two spreadsheet applications.

Table 1. Comparison: Google Spreadsheets Versus Microsoft Excel

Google Spreadsheets

Microsoft Excel 2003

Reads and writes XLS-format files

Yes

Yes

Imports and exports CSV-format files

Yes

Yes

Multiple sheets per spreadsheet

Yes

Yes

Excel-type formulas

Yes

Yes

Number of built-in functions

232

288

Macros

No

Yes

Pivot tables

No

Yes

Database functionality

No

Yes

Charts and graphs

No

Yes

Text formatting

Yes

Yes

Number formatting

Yes

Yes

Cell formatting

Yes

Yes

Online sharing and collaboration

Yes

No

Saves documents online for access from any PC

Yes

No

Can use when not connected to the Internet

No

Yes

As you can see from this table, the major features lacking in Google Spreadsheets are

  • Macros

  • Pivot tables

  • Database functionality

  • Charts and graphs

However, Google Spreadsheets does offer online sharing and collaboration and the capability to save all documents online.

Note

The lack of charts and graphs is due to the fact that Google Spreadsheets doesn'tas yethave a built-in graphics engine.

In addition, there are some minor differences between the two programs. For example, while Google Spreadsheets can work with most Excel data and import Excel XLS files, some Excel text and date formatting doesn't directly import. Google Spreadsheets also lacks cell border formatting (you can format cell background color, however), and doesn't offer Excel's time-worn right mouse button options. Finally, unlike Excel, Google Spreadsheets doesn't let you zoom in or out of a spreadsheetyou can't change the size of the spreadsheet grid.


Other Web-Based Spreadsheets

Google Spreadsheets isn't the only web-based spreadsheet out there, either. There are a number of standalone web-based spreadsheet applications, as well as a few that are part of online office suites.

These alternative online spreadsheets applications include

  • EditGrid (www.editgrid.com)

  • iRows (www.irows.com), shown in Figure 4

    Figure 4. The iRows online spreadsheet.

    [View full size image]

  • Num Sum (www.numsum.com)

The following web-based office suites also have spreadsheet components:

  • ThinkFree (www.thinkfree.com)

  • Zoho (www.zoho.com), shown in Figure 5

    Figure 5. The Zoho Sheet spreadsheet application in the Zoho web-based office suite.

    [View full size image]

Most of these web-based applications offer the same type of sharing and collaboration that you find in Google Spreadsheets. Many of them also offer a few more features than Google Spreadsheets, such as charting and graphing. All, like Google Spreadsheets, are free.


Privacy and Security Concerns

When you're using Google Spreadsheets, you're relying on Google to store your work on their servers. This may raise some concerns about privacy and security; all your data is in Google's hands.

These sound like reasonable concerns, but Google says you shouldn't worry. Although Google Spreadsheets stores your spreadsheets on their servers, they do not collect other personal information about you. In addition, Google uses a secure authentication method to control access to any spreadsheet you create. While you can grant others access to share your spreadsheets, those spreadsheets are private by default. Unless you share a spreadsheet URL, no one else can view that spreadsheet.


Should You Use Google Spreadsheets?

Before you jump in to the Google Spreadsheets waters, you need to ask the question, is Google Spreadsheets right for your particular needs? The answer, of course, is that it all depends.

Here are the following users for whom I'd say Google Spreadsheets holds promise:

  • Beginning spreadsheet users If you're just starting out in the spreadsheet world, there's no better place to start than with Google Spreadsheets. GS's slightly limited functionality actually works to the benefit of beginning users; you won't be overwhelmed by all the advanced options that clutter the Excel workspace. Plus, Google Spreadsheets is extremely easy to use; everything you need is right out in the open, not hidden beneath layers of menus and dialog boxes. I wish I'd had Google Spreadsheets 20 years ago, when I was learning how to use PC spreadsheets (with Lotus 1-2-3, if your memory extends back that far).

  • Casual spreadsheet users Google Spreadsheets is also a good choice if you have modest spreadsheet needs. If all you're doing is creating a few lists, totaling a few numbers, or creating a simple budget or two, Google Spreadsheets gets the job done with ease.

  • Anyone who wants access to their spreadsheets from multiple locations If you work on the same data at work and at home (or on the road), you know what a hassle it is to carry your data around with you from computer to computerand keep it synchronized. Google Spreadsheets solves this problem. Wherever you are (home, office, on the road), you're always accessing the same version of your spreadsheet file, stored on Google's servers. There are no synchronization issues; you work on the same file wherever you go.

  • Anyone who needs to share their spreadsheets with others Sometimes you need others to view what you're working on. Maybe you have a family budget that you and your spouse both need to see. Maybe you have a soccer team schedule that other parents need to view. Whatever the need, Google Spreadsheets lets you share your spreadsheets with anyone you like, over the Web.

  • Anyone who needs to edit their spreadsheets in a collaborative environment Sharing is one thing; collaborative editing is another. If you need multiple users to both access and edit data in a spreadsheet, Google Spreadsheets lets you do things that are impossible in Excel. For example, I know of one entrepreneur who adopted Google Spreadsheets for his small telemarketing company. He has five employees making calls at the same time, all from their homes. He has all five employees work from the same spreadsheet; they not only access the same call data, they also enter their results into the spreadsheetlive, via the Internet.

All that said, Google Spreadsheets isn't for everyone. So who shouldn't use Google Spreadsheets?

  • Power users If you've created your own custom spreadsheet or database applications in Excel, Google Spreadsheets is not for you. It lacks many of Excel's most advanced features and simply won't get the job done. Same thing if you use a lot of macros and advanced functions; Excel has a lot of high-end features that Google Spreadsheets doesn't.

  • Anyone who wants to create charts and graphs At present, Google Spreadsheets lacks a graphics engine. This means no pie charts or bar graphs. If you need graphing capability, stick with Excel.

  • Anyone who wants to create sophisticated printouts Likewise, Google Spreadsheets lacks some of the more sophisticated formatting options that some Excel users take for granted. (No cell borders, for example.) With Google Spreadsheets, what you see onscreen is exactly what prints outfor better or for worse. If you need fancy printouts, Google Spreadsheets will probably disappoint.

  • Anyone who needs to work when not connected to the Internet This is the blatantly obvious one, but if you're not connected to the Internet, you can't connect to and work with Google Spreadsheets. To work offline, you need Excel.

So, if you're a beginning or casual spreadsheet user who doesn't need fancy charts or printouts, or if you need to share your data or collaborate online with other users, Google Spreadsheets might be for you.


Section 2. Migrating from Excel to Google Spreadsheets

If you've decided to give Google Spreadsheets a spin, the first question (if you've used Excel at all in the past) is how to transfer your existing Excel spreadsheets to Google Spreadsheets. Fortunately, it's pretty easy.

As you'll learn in the "Importing a Spreadsheet from Excel" section of this document, it's easy to open your Excel spreadsheets from within Google Spreadsheets. All you have to do is click the File button and select the Open command, then select and upload the spreadsheet you want to import. In most cases, the Excel spreadsheet imports into Google Spreadsheets with only superficial formatting changes.

In some instances, however, Google Spreadsheets won't be able to import an Excel spreadsheet. You'll probably run into problems if you try to open a spreadsheet that has embedded charts or graphics, that include macros or pivot tables, or are overly large.

In some cases, these spreadsheets simply won't import; Google will give you a message saying that it couldn't open the file. In other cases, Google Spreadsheets will be able to open the file, but the non-Google features will be carved off. Even with fully compatible spreadsheets, it's not uncommon to find some formatting changes when you import into Google Spreadsheets; for example, the cell backgrounds might be one color in Excel and another in Google Spreadsheets. Full compatibility is just a dream at this point.

It's also possible to export from Google Spreadsheets back into Excel's XLS format. This lets you work on your spreadsheets online, for sharing and collaboration, but then go offline with an Excel file for more private or detailed work. Just click the File button and select Download as XLS; this downloads an XLS-format version of the spreadsheet file to your computer.


Section 3. Navigating the Google Spreadsheets Workspace

The Google Spreadsheets workspace looks a lot like every other PC-based spreadsheet application you've ever used. Whether you started with VisiCalc, 1-2-3, Quattro Pro, or Excel, you'll recognize the row-and-column grid you see when you first access Google Spreadsheets. Sure, the buttons or links for some specific operations might be in slightly different locations, but pretty much everything you expect to find is somewhere on the page.


Understanding Elements of the Workspace

Let's take a quick look at what's where in the Google Spreadsheets workspace. The first thing to note is that the workspace changes slightly, depending on which tab (Format, Sort, or Formulas) you select at the top of the page. You can view the three different tabs in Figures 6, 7, and 8; Table 2 details all the functionality of the various workspace elements.

Figure 6. Google SpreadsheetsFormat tab.

1 File button

2 Save button

3 New link

4 Open link

5 Title and info

6 Show/Hide Sharing Options

7 Format tab

8 Sort tab

9 Formulas tab

10 Cut

11 Copy

12 Paste

13 Undo

14 Redo

15 Column headings

16 Row numbers

17 Add Sheet

18 Choose different sheet

19 Reference area

20 Choose Format

21 Bold

22 Italic

23 Underline

24 Font Family

25 Font Size

26 Text Color

27 Background Color

28 Clear Format

29 Align

30 Insert

31 Delete

32 Wrap Text

33 Merge Across

Figure 7. Google SpreadsheetsSort tab.

1 File button

2 Save button

3 New link

4 Open link

5 Title and info

6 Show/Hide Sharing Options

7 Format tab

8 Sort tab

9 Formulas tab

10 Cut

11 Copy

12 Paste

13 Undo

14 Redo

15 Column headings

16 Row numbers

17 Add Sheet

18 Choose different sheet

19 Reference area

20 Freeze Rows

21 Sort A>Z

22 Sort Z>A

Figure 8. Google SpreadsheetsFormulas tab.

1 File button

2 Save button

3 New link

4 Open link

5 Title and info

6 Show/Hide Sharing Options

7 Format tab

8 Sort tab

9 Formulas tab

10 Cut

11 Copy

12 Paste

13 Undo

14 Redo

15 Column headings

16 Row numbers

17 Add Sheet

18 Choose different sheet

19 Reference area

20 Sum

21 Count

22 Average

23 Min

24 Max

25 Product

26 More formulas

Table 2. Elements of the Google Spreadsheets Workspace

Element

Tab

Description

File button

All

Lets you save, open, import, and export spreadsheet files

Save button

All

Saves the current spreadsheet

New link

All

Opens a new spreadsheet in a new browser window

Open link

All

Opens a previously saved spreadsheet, or uploads an XLS spreadsheet file stored on your computer

Spreadsheet title and info

All

Displays the title of the current spreadsheet, as well as when it was last saved (visible only when using a saved spreadsheet)

Share This Spreadsheet link

All

Saves the current spreadsheet and displays sharing options (visible only on newly createdthat is, unsavedspreadsheets)

Show/Hide Sharing Options link

All

Lets you invite other users to share or view the current spreadsheet (visible only when using a saved spreadsheet)

Format tab

All

Displays formatting controls

Sort tab

All

Displays sorting controls

Formulas tab

All

Displays formula controls

Cut button

All

Cuts the data in the selected cell(s)use when you want to move data to another location

Copy button

All

Copies the data in the selected cell(s)

Paste button

All

Pastes cut or copied data

Undo button

All

Undoes the most recent entry, edit, or operation

Redo button

All

Redoes an undone operation

Add Sheet button

All

Adds a new sheet to the current spreadsheet

Sheet 1, Sheet 2, etc.

All

Click to select different sheets within the current spreadsheet

Reference area

All

Displays the contents of the current celleither raw data or the formula behind the numbers

Choose Format button

Format

Selects a number, date, or plain text format for the selected cell(s)

Bold

Format

Bolds the contents of the selected cell(s)

Italic

Format

Italicizes the contents of the selected cell(s)

Underline

Format

Underlines the contents of the selected cell(s)

Font Family

Format

Applies a particular font (typeface) to the selected cell(s)

Font Size

Format

Changes the font size of the selected cell(s)

Text Color

Format

Changes the color of the contents of the selected cell(s)

Background Color

Format

Changes the background color of the selected cell(s)

Clear Format

Format

Clears all formatting from the selected cell(s)

Align button

Format

Changes the alignment (left, center, right, etc.) of the selected cell(s)

Insert button

Format

Inserts new rows or columns into the spreadsheet

Delete button

Format

Deletes rows or columns from the spreadsheet

Wrap Text

Format

When checked, wraps long text to additional lines within the selected cell(s)

Merge Across

Format

Merges adjacent cells into a single cell

Freeze Rows

Sort

When sorting, freezes the top row(s) of the spreadsheet as header rows

Sort Sheet by Selected Column: A > Z

Sort

Sorts the spreadsheet by the currently selected column, in alphabetical (or numerical) order

Sort Sheet by Selected Column: Z > Z

Sort

Sorts the spreadsheet by the currently selected column, in reverse alphabetical (or numerical) order

Reference area

Formulas

Displays the name of the selected cell

Sum

Formulas

Calculates the total of a group of cells

Count

Formulas

Counts the number of cells in a range that contain numeric values

Average

Formulas

Calculates the mean average of a group of numbers

Min

Formulas

Returns the minimum value in a range of cells

Max

Formulas

Returns the maximum value in a range of cells

Product

Formulas

Calculates the product of the specified valuesthat is, it multiplies all the values together

More>>

Formulas

Displays all available Google Spreadsheets functions


Navigating with the Keyboard

While the easiest way to navigate Google Spreadsheets is with your mouse, you can also use your keyboard to move from cell to cell in a spreadsheet. Table 3 details the key combinations that enable you to move around the worksheet.

Table 3. Google Spreadsheets Navigation Keys

Key(s)

Navigation

Up arrow

Move up one cell

Down arrow

Move down one cell

Right arrow

Move right one cell

Left arrow

Move left one cell

Tab

Move right one cell

Shift+Tab

Move left one cell

Ctrl+Home

Move to cell A1

Page Down

Move one screen down

Page Up

Move one screen up


Freezing Rows While You Scroll

As just noted, you can use your keyboard's Page Up and Page Down keys to scroll through long spreadsheets. You can also use your mouse to click the onscreen scrollbars to achieve the same movement.

The only problem with scrolling in this manner is that the top rows of the spreadsheettypically used as column headersscroll off the screen when you move down a spreadsheet. Wouldn't it be nice if you could "freeze" your spreadsheet's header row so that it's always visible, even when you're scrolling down?

You're in luck; Google Spreadsheets provides just such a header row freeze function. As you can see in Figure 9, when you freeze the top row (or rows) of a spreadsheet, it stays at the top of the screen, no matter how far down you scroll down. Here's how you do it:

1. | From within your spreadsheet, click the Sort tab.
2. | Click the Freeze button.
3. | Select how many rows you want to freeze as the header row.

Figure 9. A spreadsheet with a frozen header row.

Now, when you scroll through your spreadsheet, the selected rows stay frozen in place at the top of the spreadsheet.


Section 4. Creating, Opening, and Saving Google Spreadsheets

Google Spreadsheets makes it really easy to create new spreadsheets, save the ones you're working on, and reopen previously saved spreadsheets. You can also import your Excel spreadsheets and download your Google spreadsheets in XLS format to work on from within Excel.

Know, however, that Google Spreadsheets is still in beta testing, and that Google has set some limits as to how large a spreadsheet you can work with. At present, Google lets you create a maximum 100 spreadsheets. Each spreadsheet can contain up to 20 tabs (sheets), 50,000 cells, 256 columns, or 10,000 rowswhichever comes first. As to importing existing Excel spreadsheets, Google only lets you import files sized 400KB or less. (Which means you can't import really large Excel files.) Past that, you're free to use Google Spreadsheets however you see fit.


Creating a New Spreadsheet

Creating your first new Google spreadsheet is as easy as logging on to the main Google Spreadsheets page. When you access Google Spreadsheets, you're presented with a blank spreadsheet. That's your new spreadsheet to work with. (You can then save this spreadsheet, as we'll discuss in a moment.)

To create another new spreadsheet, you can either click the New link at the top of the page, or click the File button and select New. (Figure 10 shows the menu that appears when you click the File button.) Either action opens a new spreadsheet in a new browser window; the current spreadsheet you were working on remains open.

Figure 10. Use the File menu to create a new spreadsheet.

Previous Page
Next Page


Opening an Existing Spreadsheet

As you continue to work with Google Spreadsheets, you'll want to return to spreadsheets you previously worked with. To open a spreadsheet you created during a previous session, either click the Open link or select File, Open. Either action opens the Open a Spreadsheet dialog box, shown in Figure 11. Click the spreadsheet you want to open from the list presented; that spreadsheet now opens in a new browser window.

Figure 11. Opening a previously saved spreadsheet.

[View full size image]

Note

A new browser window opens only if you have a saved spreadsheet currently open. If you have a new, blank spreadsheet open, the spreadsheet you select will open in the current browser window.


Importing a Spreadsheet from Excel

Google Spreadsheets lets you import XLS- and CSV-format spreadsheet files created with Microsoft Excel. You can then work with these Excel spreadsheets from within Google Spreadsheets.

Note

XLS is the standard Excel worksheet format. A CSV (comma-separated values) file is a spreadsheet file in text format, with fields separated by commas.

To import an Excel file, follow these steps:

1. | Click the Open link or select File, Open.
2. | When the Open a Spreadsheet dialog box appears, click the Browse button.
3. | When the Choose File dialog box appears, navigate to and select the XLS or CSV file you want to import, then click the Open button.
4. | When the Open a Spreadsheet dialog box displays the message, "File imported successfully," click the Open Now link.

Google now displays the selected spreadsheet in a new browser window. You can now edit the spreadsheet as you like; Google automatically saves a copy of the file on its servers, for your future use.


Saving a Spreadsheet

When you are finished with a spreadsheet, you need to save the file. When you first save a file, you must do this manuallyand give the file a name. After this first save, Google automatically resaves the file every time you make a change to the spreadsheet. In essence, this means that you only have to save the spreadsheet once; Google saves all further changes automatically.

Note

Google's automatic file save feature is called, appropriately enough, Autosave.

To save a new spreadsheet, follow these steps:

1.
Click the Save button or select File, Save.

2.
When the dialog box shown in Figure 12 appears, enter a name for the spreadsheet.

Figure 12. Opening a previously saved spreadsheet.

3.
Click the OK button.

That's all there is to it. The spreadsheet is now saved on Google's servers, and you don't have to bother resaving it at any future point.

Just rememberthe spreadsheet file you just saved isn't on your PC's hard disk. It's stored on Google's servers, which means you must be connected to the Internet to access it. That's the thing about Google Spreadsheets; Google handles all the file storage, and your computer is used merely to access the Google Spreadsheets site. There are no Google Spreadsheets files stored on your computer, period. (Although you can choose to manually download a copy of any Google Spreadsheets file to your PC, as explained in the "Exporting a Google Spreadsheet to Excel Format" section coming up in a few moments.)


Saving a Copy of a Spreadsheet

You can, however, save a copy of any spreadsheet under a different name. You might want to do this if you want to keep different versions of a spreadsheet, for whatever reason.

To save a copy of a spreadsheet under a different name, follow these steps:

1. | Select File, Save As.
2. | When the dialog box appears, enter a new name for the spreadsheet.
3. | Click the OK button.

This closes the current spreadsheet and displays the newly saved spreadsheet in the current browser window. Any further editing you do is to the new spreadsheetuntil you reopen the original file, of course.


Renaming a Spreadsheet

Google also lets you change the name of any spreadsheet you've saved. To rename a spreadsheet, follow these steps:

1. | Select File, Rename.
2. | When prompted, enter a new name for the spreadsheet.
3. | Click the OK button.

The current spreadsheet is now assigned the new name.


Exporting a Google Spreadsheet to Excel Format

By default, all the spreadsheets you work with in Google Spreadsheets are stored on Google's servers. You can, however, download files from Google to your computer's hard drive to work with in Excel. In essence, you're exporting your Google spreadsheet to an XLS-format Excel file.

To export the current spreadsheet, follow these steps:

1.
Select File, Download as .XLS.

2.
When the File Download dialog box appears, as shown in Figure 13, click the Save button.

Figure 13. Downloading a Google Spreadsheets file.

3.
When the Save As dialog box appears, as shown in Figure 14, select a location for the downloaded file, rename it if you like, and then click the Save button.

Figure 14. Saving a spreadsheet file in XLS format.

[View full size image]

The Google Spreadsheets file is saved in XLS format on your hard disk. You can now open that file with Excel, and work on it as you would with any Excel spreadsheet. Know, however, that whatever changes you make to the file from within Excel affect only the downloaded file, not the copy of the spreadsheet that still resides on the Google Spreadsheets site. If you later want to reimport the Excel file to Google Spreadsheets, select File, Upload New Version to upload the changed spreadsheet from your PC to Google's servers.

Note

You can also export a Google spreadsheet as a CSV-format file by selecting File, Download as .CSV. You can then import the CSV-format file into Excel or any other spreadsheet program. (Excel will not directly open CSV-format files, however; you'll have to use the import function from within Excel.)


Deleting a Spreadsheet

Spreadsheets you create with Google Spreadsheets are saved on Google's servers indefinitelyunless you choose to manually delete a file. To delete a file, follow these steps:

Caution

Be certain you want to delete a file before you attempt this operation. The delete action cannot be undone.

1. | Open the spreadsheet you want to delete.
2. | Select File, Delete.
3. | When asked if you really want to delete the file, as shown in Figure 15, click OK.

Figure 15. Google prompts you before you delete a spreadsheet.

That's it. The selected file is now deleted from Google's servers.


Section 5. Entering and Editing Data

Entering data into a Google spreadsheet can be as simple as selecting a cell and starting typingdepending on the type of data you wish to enter. Google Spreadsheets lets you enter four different types of data, as detailed in Table 4.

Table 4. Types of Google Spreadsheets Data

Type of Data

Description

Numbers

Numbers can be in a variety of formats, including currency and percent formats. All numbers can be manipulated mathematically.

Text

Text can contain both alphabetic and numerical characters. Text cannot be manipulated mathematically.

Dates

Dates are specially formatted numbers.

Formulas

Formulas tell Google Spreadsheets how to make calculations using data in other cells.

All data can be formatted in a variety of styles. Formatting and styles are discussed in later lessons.


Entering New Data

Entering data is as simple as selecting a particular cell and typing input from the keyboard. Just follow these steps:

1. | Move the cursor to the desired cell, using either the mouse or the keyboard arrow keys.
2. | Begin typing.
3. | When you are finished typing, press the Enter key to accept the data entry.

This approach works for all types of data, with the exception of formulas. Entering a formula is almost this simple, except that you must enter an equal sign (=) first. Just go to the cell, press the = key on your keyboard, and then enter the formula.

As to how the individual data is formattedthat is, how Google Spreadsheets interprets numbers and lettersit depends on what type of data you enter:

  • If you typed only numbers, the data will be formatted as a number (with no commas or dollar signs).

  • If you typed a number with a dollar sign in front of it, the data will be formatted as currency.

  • If you typed any alphabetic characters, the data will be formatted as text.

  • If you typed numbers separated by the - or / characters (such as 12-31 or 1/2/06), the data will be formatted as a date.

  • If you typed numbers separated by the : character (such as 2:13), the data will be formatted as a time.


Editing Previously Entered Data

Editing existing data in a cell is a fairly simple exercise; you actually edit within the cell. Follow these steps:

1.
Move the cursor to the desired cell.

2.
Press the F2 key; this opens the cell for editing, as shown in Figure 16.

Figure 16. Editing data within a cell.

3.
Move the cursor to the data point within the cell you want to edit.

4.
Use the Delete and Backspace keys to delete characters; use other keys to insert characters.

5.
Press Enter when you are finished editing. Your changes are accepted into the selected cell.


Section 6. Working with Ranges

When you reference data within a spreadsheet, you can reference individual cells or you can reference a range of cells. When you reference more than one contiguous cell, that's called a range.

You typically use ranges with specific functions, such as SUM (which totals a range of cells) or AVERAGE (which calculates the average value of a range of cells). We'll learn more about functions later in this document.


Understanding Ranges

A range is simply a collection of adjacent cells. These may be in a column, a row, or a larger area consisting of several rows and columns. A range reference is expressed by listing the first and last cells in the range, separated by a colon (:). For example, the range that starts with cell A1 and ends with cell A9 (shown in Figure 17) is written like this:

A1:A9

Figure 17. The range of cells specified A1:A9.

As just noted, ranges can run down columns or across rows, or both. For example, a range starting with cell A1 in the top-left corner and running through cell D10 in the bottom-right corner (as shown in Figure 18) is written like this:

A1:D10

Figure 18. The range of cells specified A1:D10.

Previous Page
Next Page


Selecting a Range

You can select a range with either your mouse or your keyboard. Using your mouse, you can simply click and drag the cursor to select all the cells in the range. For example, to select the cells from A1 to D1, click the first cell (A1), hold down the mouse button, drag the mouse across to the last cell (D1), and then release the mouse button.

You can also select a range with the keyboard. Position the cursor in the first cell in the range, hold down the Shift key, and then use the cursor keys to expand the range in the appropriate direction.

Finally, you can use a combination of mouse and keyboard to select a range. Use either the mouse or the keyboard to select the first cell in the range. Then hold down the Shift key, and click the mouse in the last cell in the range. All the cells in between the two cells will be automatically selected.


Section 7. Sorting Data

Often, you want your data to appear in a sorted order. You might want to sort your data by date, for example, or by quantity or dollar value. Fortunately, Google Spreadsheets lets you sort your data either alphabetically or numerically, in either ascending or descending order.


Executing a Sort

Sorting data in Google Spreadsheets is a two-step operation. You first have to "freeze" the header row(s) of your spreadsheet, then you identify the column by which you want to sort. Google will then order all the "unfrozen" (non-header) rows of your spreadsheet in whichever order (ascending or descending) you specified.

Here's how to do it:

1.
Click the Sort tab, as shown in Figure 19.

Figure 19. Getting ready to sort via the Sort tab.

2.
Click the Freeze Rows button and select how many rows you want to include as the spreadsheet's header, as shown in Figure 20. (In most cases, it's just one row.) When you freeze a header row, it no longer scrolls with the rest of the spreadsheetand is not included in any sort.

Figure 20. Choosing which rows to freeze.

3.
Identify which column you want to sort by, and move the cursor to any cell within that column.

4.
To sort in ascending order, click the A>Z button; to sort in descending order, click the Z>A button.

The A>Z and Z>A sorts don't just sort by letter; they also sort by number. An A>Z sort will arrange numerical data from smallest to largest; a Z>A sort will arrange numerical data from largest to smallest.


Sorting a Range

This procedure conducts a sort on the entire contents of your spreadsheet. You can also sort within a selected range of cells, with certain limitations. To do this, follow these steps:

1. | Select the Sort tab.
2. | Position the cursor on the first cell in the first column of the range.
3. | Use the mouse or the keyboard to select the entire range; the first cell stays selected.
4. | Click the A>Z button to sort the range on the first column, in ascending order; click the Z>A to sort the range on the first column, in descending order.

Caution

When you sort a range, you can sort only on the first column selected. You cannot sort on a middle column within a range.


Performing Multiple-Column Sorts

Know that, unlike Excel, Google Spreadsheets can sort only on one column at a time. (Excel can sort on up to three columns, in order.) That means you may need to sort your spreadsheet multiple times, for each column you need sorted.

For example, if you have one column for names and another for dates, and you want your spreadsheet sorted by date first and then alphabetical by name within each date, you must do two sorts. The first sort should be by name (to set up the alphabetical arrangement), and the second sort should be by date.


Section 8. Inserting, Deleting, Moving, and Copying Data

There are times when you need to rearrange data on your worksheetbeyond simple sorting, that is. When you need to move data from place to place, duplicate certain cells, or delete something you don't like, it's time to learn Google Spreadsheets' cell, row, and column editing functions.


Inserting Rows and Columns

To insert a new row or column, follow these steps:

1. | Position the cursor in the row or column where you want to insert a new row or column.
2. | Click the Insert button.
3. | Select whether you want to insert a row or a column.

Google Spreadsheets now inserts the new row above the selected row or the new column to the left of the selected column. The existing row or column is shifted either down or to the right.

Note

Unlike Microsoft Excel, Google Spreadsheets only lets you insert entire rows or columns; you can't insert individual cells into the spreadsheet. You also can't insert rows below the current row or to the right of the current column.


Deleting Data

Google Spreadsheets lets you delete entire rows and columns or clear the contents of individual cells. To delete a row or column, follow these steps:

1. | Position the cursor in a cell in the row or column you wish to delete.
2. | Click the Delete button.
3. | Choose to delete the selected row or column.

Note

Google Spreadsheets doesn't let you delete a cell per se, only clear its contents.

To clear the contents of an individual cell or range of cells, follow these steps:

1. | Select the cell or range of cells you want to clear.
2. | Click the Delete button.
3. | Select Clear Selection.

The contents of the selected cell(s) are now deletedeven though the cells themselves remain.

Tip

If you accidentally delete data you want to keep, don't panic! Google Spreadsheets includes an Undo option that lets you unwind your last command. All you have to do is click the Undo button at the top right of the workspace. Presto! You've undone your last delete and your data is back where it belongs.


Moving Data with the Cut and Paste Commands

Google Spreadsheets lets you move data from one place to another in your worksheet by cutting and pasting the data. The cut and paste procedure is similar to that used in most Windows-based applications.

To cut and paste data in your spreadsheet, follow these steps:

1. | Select the cell or range of cells to move. (You can cut individual cells, entire rows and columns, or marked cell ranges. Use your mouse or the keyboard to select the range.) Tip To select an entire column, click the column header (A, B, C, and so on). To select an entire row, click the row header (1, 2, 3, and so on).
2. | With the selection highlighted, click the Cut button.
3. | Move the cursor to the first cell where you want to move your data. There is no need to select the entire range, just the first (upper-left) cell of the range.
4. | Click the Paste button.

Google now moves the highlighted data to the new location.

Caution

Be careful when cutting and pasting data! When you use the Paste command, Google will paste the copied data over any data that previously existed in the target range. Be sure no important data resides in the target range.

Tip

You can also use the following keyboard shortcuts when cutting and pasting data: CutCtrl+X or Shift+Delete; PasteCtrl+V.


Copying Data

Copying data is similar to moving data, except that both the original data and the copied data remain in your spreadsheet. You can use the copy command to duplicate table headings or entire ranges of data.

To copy and paste data, follow these steps:

1. | Select the cell or range of cells to copy. (You can cut individual cells, entire rows and columns, or marked cell ranges. Use your mouse or the keyboard to select the range.)
2. | With the selected highlighted, click the Copy button.
3. | Move the cursor to the first cell where you want to paste your data. There is no need to select the entire range, just the first (upper-left) cell of the range.
4. | Click the Paste button.

Google places a copy of the selected data in the new location.

Tip

You can also use the following keyboard shortcuts when copying and pasting data: CopyCtrl+C or Ctrl+Delete or Ctrl+Insert; PasteCtrl+V.


Section 9. Changing Column Width and Row Height

Often you will find that you need to adjust your column or row size to accommodate large amounts of data or large type sizes. With Google Spreadsheets you can adjust your column and row size with either the mouse or the keyboardor you can let Google adjust the size to automatically fit your data.


Changing Column Width

Using a mouse to change column width is similar to changing the size of a window in any Windows application. Follow these steps:

1.
Use your mouse to move the cursor to the heading area at the top of the column you want to adjust.

2.
Move the cursor to the line on the right border of the column. The cursor now changes shape and the border changes to a blue color, as shown in Figure 21.

Figure 21. Adjusting column width.

3.
Hold down the left mouse button and drag the line to the right (to widen the column) or the left (to shrink the column).

4.
Release the mouse button to accept the new column width.


Changing Row Height

There is normally less call to change the height of rows than there is to change the width of columns, primarily because Google automatically adjusts the row height to accommodate different type sizes. However, if you want to change row height for effect, the operation is identical to that used to change column width. Follow these steps:

1.
Use your mouse to move the cursor to the heading area at the left of the row you want to adjust.

2.
Move the cursor to the line on the bottom border of the row. The cursor now changes shape and the border changes to a blue color, as shown in Figure 22.

Figure 22. Adjusting row height.

3.
Hold down the left mouse button and drag the line up or down until the row is the desired height.

4.
Release the mouse button to accept the new row height.


Section 10. Formatting Google Spreadsheets

Let's face it. A basic Google spreadsheet looks pretty plain. Fortunately, you can spruce up your spreadsheet by changing font size, family, and color, and by changing the background color of individual cells. Read on to learn more.


Enhancing Text with Bold, Italic, and Other Attributes

Google Spreadsheets lets you format your text with a number of common attributes, as shown in Figure 23. In particular, you can apply these formatting attributes:

  • Bold

  • Italic

  • Underline

  • Text color

Figure 23. Sample text formatting with Google Spreadsheets.

Note

Although you can change text attributes for an entire cell or range of cells, Google Spreadsheets doesn't let you change attributes for selected characters within a cell.

You apply these formatting attributes to all the text (numbers or letters) within a selected cell or range of cells. Just follow these steps:

1. | Select the Format tab.
2. | Select a cell or range of cells.
3. | Click the appropriate format buttonBold, Italic, Underline, or Text Color.

The formatting you select is now applied to the selected cell(s).

If you choose to change the text color, you have a choice of 40 different colors. When you click the Text Color button, a drop-down color menu appears, as shown in Figure 24. Click the color you want, and it's applied to all the text in the selected cell(s).

Figure 24. The color menu for changing text color.

Tip

To remove all text formatting within a cell or range of cells, select the cell(s) and click the Clear Format button.


Changing Fonts and Type Sizes

To enhance the appearance of your spreadsheet you can employ different fonts and type sizes for various parts of your document. The method to change fonts and type sizes is identical to the method of applying formatting attributes; select the cell or range of cells, and then click the appropriate button.

Note

As with the way Google Spreadsheets handles text formatting, you can change fonts or type sizes only for an entire cell or range of cells, not for text within a cell.

When it comes to type size, you can choose a range from 6 point to 36 point text. As to fonts, you can choose from the following font families:

  • Arial

  • Courier New

  • Georgia

  • Trebuchet

  • Verdana

Note

The Normal font on the Font Family list applies the Arial font.

To change fonts or type size, follow these steps:

1. | Select the Format tab.
2. | Select a cell or range of cells.
3. | Click the Font Family or Font Size buttons, then select the font or size from the pull-down list. (Figure 25 shows the Font Family options; Figure 26 shows the Font Size options.)

Figure 25. Choosing a new font family for your text.

Figure 26. Choosing a new font size for your text.

Previous Page
Next Page


Aligning Information in a Cell

Another way to enhance the look of your worksheet is to make sure that data is aligned properly. There are three horizontal and three vertical alignments possible within Google Spreadsheets:

  • Horizontal: Left, centered, and right (shown in Figure 27)

    Figure 27. Left, centered, and right horizontal alignments.

  • Vertical: Top, middle, and bottom (shown in Figure 28)

Figure 28. Top, middle, and bottom vertical alignments.

You can choose both a horizontal and a vertical alignment for any cell or range of cells in your spreadsheet. Follow these steps:

1. | Select the Format tab.
2. | Select a cell or range of cells.
3. | Click the Align button, then select a horizontal or vertical alignment.

If you want to choose both a horizontal and a vertical alignment, you'll have to work through these steps twiceonce for the horizontal alignment, and a second time for the vertical alignment.


Changing Number Formats

There are numerous ways to express a number. A number can be expressed as a whole number, as a percent, as a fraction, as currency, as a date, and even exponentially. Google Spreadsheets employs a wide variety of number formats that you can apply to your data.

Table 5 details the number formats available in Google Spreadsheets; Table 6 details the percent formats; Table 7 details the date and time formats. Examples are included for each.

Table 5. Google Spreadsheets Number Formats

Number Format

Number Typed: 5000

Number Typed: 5

Number Typed: -5

Number Typed: .5

Plain Text

5000

5

-5

0.5

Normal

5000

5

-5

0.5

Rounded

5,000

5

-5

0

2 Decimals

5,000.00

5.00

-5.00

0.50

Financial Rounded

5,000

5

(5)

0

Financial

5,000.00

5.00

(5.00)

0.50

Dollar

$5,000

$5

-$5

$0

Dollar and Cents

$5,000.00

$5.00

-$5.00

$0.50

Table 6. Google Spreadsheets Percent Formats

Number Format

Number Typed: 5

Number Typed: .5

Number Typed: .005

Number Typed: -.5

Percent Rounded

500%

50%

0%

-50%

Percent

500.00%

50.00%

0.50%

-50.00%

Table 7. Google Spreadsheets Date and Time Formats.

Number Format

Example

Date (1)

2/14/2006

Date (2)

14-Feb-2006

Time

2:45:30

Date Time

2/14/2006 2:45:30

Note

The difference between the plain text and normal formats is in the alignment. The plain text format is left aligned; the normal format, as with all the other number formats, is right aligned.

Google Spreadsheets applies the Normal number format by default when you enter numbers into your worksheet. You can, however, apply any number format to any particular cell. To apply a different number format, follow these steps:

1. | Select the Format tab.
2. | Select the cell or range.
3. | Click the Choose Format button.
4. | Choose the number format from the list, as shown in Figure 29.

Figure 29. Choosing a number format.

Previous Page
Next Page


Changing the Background Color of Cells and Ranges

You can add even more pizzazz to your spreadsheets by changing the background color of individual cells or ranges of cells. This sort of shading is useful when you want to highlight information in a spreadsheet, as shown in Figure 30.

Figure 30. A spreadsheet with cells shaded different colors.

To change the background color of a cell or range of cells, follow these steps:

1. | Select the Format tab.
2. | Select the cell or range.
3. | Click the Background Color button.
4. | When the color chart appears, as shown in Figure 31, select the color you want.

Figure 31. Choosing a cell background color.

Tip

When you change the background color for a cell, be sure the text color is a good contrast to the background color. You don't want green text on a green background, for example; strive for a readable contrast in colors.


Section 11. Entering and Editing Formulas

After you've entered data into your spreadsheet, you need to work with those numbers to create other numbers. You do this as you would in the real world, by using common formulas to calculate your data by addition, subtraction, multiplication, and division. You can also used advanced formulas preprogrammed into Google Spreadsheets; these advanced formulas are called functions and are explained in the next section.

If you're an Excel user, you're used to using formulas. If you're new to spreadsheets, it helps to know that a formula is a mathematical expression that defines the relationship between two or more values. In Google Spreadsheets, formulas are used to calculate values for specific cells and can use common mathematical operatorsas well as references to other cells in the spreadsheet.


Understanding Formulas

Formulas are used to calculate the values of data entered into other cells in your spreadsheet. A formula can consist of numbers, mathematical operators, and the contents of other cells (referred to by the cell reference).

Note

The cell reference is simply the location of a particular cell. Each cell is referenced by the intersection of a column and row; thus, the top-left cell is called A1 (for column A, row 1). The cell reference for the current cell is always listed in the Reference area of the workspace.

You construct a formula from the following elements:

  • An equals sign (=); this = sign is necessary at the start of each formula

  • One or more specific numbers and/or

  • One or more cell references

  • A mathematical operator (such as + or -); this is needed if your formula contains more than one cell reference or number

The best way to get used to formulas is to look at a few. That said, Table 8 shows a few representative formulas:

Table 8. Representative Google Spreadsheets Formulas

Formula

Result

=2

Places the numerical value 2 in the selected cell

=A1

Places the value of cell A1 in the selected cell

=A1*2

Places the value of cell A1 multiplied by 2 in the selected cell

=A1/2

Places the value of cell A1 divided by 2 in the selected cell

=A1+A2

Places the value of cell A1 plus the value of cell A2 in the selected cell

=(A1+A2)/2

Places the value of cell A1 plus the value of cell A2, all divided by 2, in the selected cell

Note that Google Spreadsheets formulas can contain common algebraic expressions and follow common algebraic conventions and logic. Table 9 lists the accepted operators for Google Spreadsheets formulas.

Table 9. Accepted Operators for Google Spreadsheets Formulas

Operator

Description

+

Addition

-

Subtraction

*

Multiplication

/

Division

^

Exponentiation (to the power of)

=

Equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

<>

Not equal to

%

Percentage


Entering Formulas

To enter a formula in a cell, follow these steps:

1. | Move the cursor to the desired cell.
2. | Type = to start the formula.
3. | Type the rest of the formula; remember to refer to specific cells by the "A1, B1, etc." cell reference.
4. | Press Enter to accept the formula or press Esc to reject the formula.

When you're finished entering a formula, you no longer see the formula within the cell; instead, you see the results of the formula. For example, if you entered the formula =1+2, you now see the number 3 in the cell. To view the formula itself, just select the cell, and then look in the reference area in the lower-right corner of the spreadsheet window (shown in Figure 32).

Figure 32. Viewing the formula within a cellvia the spreadsheet reference area.

Previous Page
Next Page


Creating Formulas with Your Mouse

Instead of typing in each cell reference (in the form of A1, A2, and so on), you can simply use the mouse to point to the cell you want to refer to in your formula. For example, if you want to add the contents of cells A1 and B1, you can enter the following formula with your keyboard:

=A1+B1

Or you can use your mouse. In this instance, you'd start by moving the cursor to the cell where you want to put the answer. Use your keyboard to enter the = sign, then use your mouse to click on cell A1. Use the keyboard again to enter the + sign, then use your mouse again to click on cell B1. Press Enter on your keyboard to finish the formula.

You can also use your mouse to enter a range of cells. Let's say that you want to total all the numbers in the range of cells from A1 to A5. In this instance, you use the SUM function (which we'll discuss in due course), followed by the range; the formula looks like this:

=sum(A1:A5)

To enter this formula, start by entering the =sum( with your keyboard. Next, use your mouse to select the cells from A1 to A5. (Click the first cell, hold down the mouse button, and then drag to include all the cells in the range.) Finally, finish things up by entering the final ) with your keyboard, and press Enter.


Editing Formulas

After you've entered a formula, you can edit it by selecting the cell in question and pressing the F2 key. As you can see in Figure 33, this shows the formula within the cell, as opposed to the result of the formula (which is what normally appears). Use your keyboard to make whatever edits are appropriate, then press the Enter key to register your changes.

Figure 33. Editing a formula within a cell.

Previous Page
Next Page


Section 12. Using Functions

A function is a type of formula built in to Google Spreadsheets. You can use Google's built-in functions instead of writing complex formulas in your spreadsheets; you can also include functions as part of your formulas.


Understanding Functions

Functions simplify the creation of complex formulas. For example, if you want to total the values of cells B4 through B7, you could enter the following formula:

=B4+B5+B6+B7

Or you could use the SUM function, which is built in to Google Spreadsheets. The SUM function lets you total (sum) a column or row of numbers without having to type every cell into the formula. In this instance, the formula to total the cells B4 through B7 could be written using the SUM function, like this:

=sum(B4:B7)

A lot easier, don't you think?

Google Spreadsheets uses most of the same functions as those used in Microsoft Excel. All Google functions use the following format:

=function(argument)

Replace function with the name of the function, and replace argument with a range reference. The argument always appears in parentheses.

Note

Functions can be written all lowercase (sum), all uppercase (SUM), or capitalized (Sum). Capitalization doesn't matter.


Entering Functions

You can enter a function into a formula either by typing the name of the function or by pasting the function into the formula from a list of functions displayed on the Formulas tab. (You don't have to be on the Formulas tab to enter functions manually, however.) To use the Formulas tab to enter formulas, follow these steps:

1.
Click the Formulas tab, as shown in Figure 34.

Figure 34. The Google Spreadsheets Formulas tab.

[View full size image]

2.
Move the cursor into the cell you want to hold the results of the function.

3.
Click the More link at the top right of the page.

4.
When the Insert a Function dialog box appears, as shown in Figure 35, click the function you want to use.

Figure 35. Choosing a function from the Insert a Function dialog box.

5.
Click the Close link to close the Insert a Function dialog box.

6.
The function is now pasted into the selected cell. As you can see in Figure 36, the placeholder text args is used for the argument part of the function. Use your keyboard or mouse to replace the args placeholder with the desired argument (typically a range reference) for this function.

Figure 36. The selected function pasted into a cell.

Previous Page
Next Page


Using the SUM Function

The most-used function, as you might expect, is the SUM function. If you're like most spreadsheet users, you'll find that well over half of your formulas involve totaling rows or columns of numbers. For this reason, Google has placed the SUM function in a prominent position in the Google Spreadsheets workspace, at the top right of the Formulas tab, alongside other popular functions. (Figure 37 shows these common functions in the Google Spreadsheets workspace.)

Figure 37. The SUM and other common functions.

To use the SUM function, follow these steps:

1. | Select the cell where you want the total to appear.
2. | Click the Sum link; this inserts =Sum( into the selected cell.
3. | Use your keyboard or mouse to select the range of cells you want totaled.
4. | Use your keyboard to enter the ) character to close the function argument.
5. | Press the Enter key.

Google Spreadsheets now calculates the formula and inserts the total for the selected range into the current cell.


Using the COUNT Function

The SUM function isn't the only function listed separately on the Formulas tab. Also listed are the COUNT, AVERAGE, MIN, MAX, and PRODUCT functions. We'll discuss each in turn; let's start with COUNT.

The COUNT function does just as its name implies; it counts the number of cells in a range that contain numeric values. To use the COUNT function, follow these steps:

1. | Select the cell where you want the count to appear.
2. | Click the Count link; this inserts =Count( into the selected cell.
3. | Use your keyboard or mouse to select the range of cells you want counted.
4. | Use your keyboard to enter the ) character to close the function argument.
5. | Press the Enter key.

Google Spreadsheets now counts the number of numeric cells in the range, and inserts that count into the current cell.


Using the AVERAGE Function

The AVERAGE function calculates the mean average of a group of numbers. To use the AVERAGE function, follow these steps:

1. | Select the cell where you want the average to appear.
2. | Click the Average link; this inserts =Average( into the selected cell.
3. | Use your keyboard or mouse to select the range of cells you want averaged.
4. | Use your keyboard to enter the ) character to close the function argument.
5. | Press the Enter key.

Google Spreadsheets now calculates the mean for the selected cells and inserts that average into the current cell.

Note

The AVERAGE function in Google Spreadsheets calculates the arithmetic mean. You calculate the mean by totaling a range of cells, then dividing the total by the number of cells in the range. For example, if you had three cells with the values 1, 2, and 3, the total would be 6, the number of cells (the COUNT) would be 3, and the mean (AVERAGE) would be 6/3, or 2.


Using the MIN Function

The MIN function returns the minimum value in a range of cells. For example, if you had three cells with the values 2, 3, and 4, the MIN function will enter the number 2the minimum value in the three cells.

To use the MIN function, follow these steps:

1. | Select the cell where you want the result to appear.
2. | Click the Min link; this inserts =Min( into the selected cell.
3. | Use your keyboard or mouse to select the range of cells.
4. | Use your keyboard to enter the ) character to close the function argument.
5. | Press the Enter key.

Google Spreadsheets now examines the selected cells and notes the one with the lowest value; it places that lowest value into the current cell.


Using the MAX Function

The MAX function is similar to the MIN function, except that it returns the maximum value in a range of cells. For example, if you had three cells with the values 2, 3, and 4, the MAX function will enter the number 4the maximum value in the three cells.

To use the MAX function, follow these steps:

1. | Select the cell where you want the result to appear.
2. | Click the Max link; this inserts =Max( into the selected cell.
3. | Use your keyboard or mouse to select the range of cells.
4. | Use your keyboard to enter the ) character to close the function argument.
5. | Press the Enter key.

Google Spreadsheets now examines the selected cells and notes the one with the highest value; it places that highest value into the current cell.


Using the PRODUCT Function

The final function listed separately in the Google Spreadsheets workspace is the PRODUCT function. This function calculates the product of the specified valuesthat is, it multiplies all the selected cells or values together. For example, if you have three cells with the values 2, 3, and 4 (respectively), the PRODUCT functions multiplies 2 times 3 times 4 and returns the value 24.

To multiply a range of numbers with the PRODUCT function, follow these steps:

1. | Select the cell where you want the result to appear.
2. | Click the Product link; this inserts =Product( into the selected cell.
3. | Use your keyboard or mouse to select the range of cells you want multiplied together.
4. | Use your keyboard to enter the ) character to close the function argument.
5. | Press the Enter key.

Google Spreadsheets now multiplies the values in all the selected cells and places the product into the current cell.


Other Google Spreadsheets Functions

Google Spreadsheets includes more than 200 individual functions. These functions are identical to the ones built in to Microsoft Excel, so if you're an Excel user, you'll be right at home. If you're not an Excel user, or if you want a handy reference to all the available functions, see Table 10.

Table 10. Google Spreadsheet Functions

Function

Type

Description

Use (Arguments)

ABS

Math

Calculates the absolute value of a number

ABS(number)

ACCRINT

Financial

Returns the accrued interest for a security that pays periodic interest

ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)

ACCRINTM

Financial

Returns the accrued interest for a security that pays interest at maturity

ACCRINTM(issue,settlement,rate,par,basis)

ACOS

Math

Returns the arccosine (inverse cosine) of a number

ACOS(number)

ACOSH

Math

Returns the inverse hyperbolic cosine of a number

ACOSH(number)

ADDRESS

Lookup

Creates a cell address as text, given specified row and column numbers

ADDRESS(row,column,type_of_reference,reference_style, external_sheet_name)

AND

Logical

Returns TRUE if all arguments are true; returns FALSE if any argument is false

AND(condition1,condition2,condition3...)

ASIN

Math

Returns the arcsine (inverse sine) of a number

ASIN(number)

ASINH

Math

Returns the inverse hyperbolic sine of a number

ASINH(number)

ATAN

Math

Returns the arctangent (inverse tangent) of a number

ATAN(number)

ATAN2

Math

Returns the arctangent (inverse tangent) of the specified x and y coordinates

ATAN2(x,y)

ATANH

Math

Returns the inverse hyperbolic tangent of a number

ATANH(number)

AVEDEV

Statistical

Calculates the average of the absolute deviations of data points from their mean

AVEDEV(range)

AVERAGE

Statistical

Calculates the average (arithmetic mean) of a group of numbers

AVERAGE(range)

AVERAGEA

Statistical

Calculates the average (arithmetic mean) of a group of numbers, text, or logical values

AVERAGEA(range)

BINOMDIST

Statistical

Calculates the individual term binomial distribution probability

BINOMDIST(number_s,trials, probability_s,cumulative)

CEILING

Math

Rounds up a number to the nearest multiple of significance

CEILING(number,significance)

CHAR

Text

Returns the character specified by a numer

CHAR(number)

CHOOSE

Lookup

Selects a number from a list based on an index number

CHOOSE(index_number,value1, value2,value3...)

CODE

Text

Returns a numeric code for the first value in a text string

CODE(text)

COLUMNS

Lookup

Returns the number of columns in a range

COLUMNS(range)

COMBIN

Math

Returns the number of combinations for a given number of items

COMBIN(number_of_items,number_of_items_in_each_combination)

CONCATENATE

Text

Joins several text strings into a single text string

CONCATENATE(text1,text2,text3...)

CONFIDENCE

Statistical

Returns a value that can be used to construct a confidence interval for a population mean

CONFIDENCE(alpha,standard_deviation, size)

CORREL

Statistical

Returns the correlation coefficient of two cell ranges

CORREL(range1,range2)

COS

Math

Returns the cosine of a number

COS(number)

COSH

Math

Returns the hyperbolic cosine of a number

COSH(number)

COUNT

Math

Counts the number of cells in a range that contain numeric values

COUNT(range)

COUNTA

Math

Counts the number of non-blank cells in a range

COUNTA(range)

COUNTBLANK

Math

Counts the number of empty cells in a range

COUNTBLANK(range)

COUNTIF

Math

Counts the number of cells in a range that meet a specified criteria

COUNTIF(range,criteria)

COUPDAYBS

Financial

Returns the number of days from the beginning of the coupon period to the settlement date

COUPDAYBS(settlement, maturity, frequency,basis)

COUPDAYS

Financial

Returns the number of days in the coupon period that contains the settlement date

COUPDAYS(settlement,maturity, frequency,basis)

COUPDAYSNC

Financial

Returns the number of days from the settlement date to the next coupon date

COUPDAYSNC(settlement,maturity, frequency,basis)

COUPNCD

Financial

Returns a number that represents the next coupon date after the settlement date

COUPNCD(settlement, maturity, frequency, basis)

COUPNUM

Financial

Returns the number of coupons payable between the settlement date and maturity date

COUPNUM(settlement,maturity,frequency,basis)

COUPPCD

Financial

Returns a number that represents the previous coupon date before the settlement date

COUPPCD(settlement,maturity,frequency,basis)

COVAR

Statistical

Returns covariance (the average of the products of deviations for each data point pair)

COVAR(range1,range2)

CRITBINOM

Statistical

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to the criterion value

CRITBINOM(trials,probability_s,alpha)

CUMIPMT

Financial

Returns the relative interest paid between the start and the end of a loan

CUMIPMT(rate,nper,present_value,start_period,end_period,type)

CUMPRINC

Financial

Returns the cumulative principal paid on a loan between two dates

CUMPRINC(rate,nper,present_value,start_period,end_period,type)

DATE

Date

Converts a date into a sequential serial number that represents that date

DATE(year,month,day)

DATEVALUE

Date

Converts a properly formatted text string (such as "1/20/2006") into a valid date

DATEVALUE(date_text)

DAY

Date

Returns the day of the week that corresponds to a specified date

DAY(date)

DAYS360

Date

Returns the number of days in between two dates, based on a 360 day year; use TRUE to use U.S. method of calculation, or FALSE to use the European method

DAYS360(start_date,end_date,method)

DB

Financial

Calculates depreciation using the fixed-declining balance method

DB(cost,salvaged,life,period,month)

DDB

Financial

Calculates depreciation using the double declining balance method

DDB(cost,salvage,life,period,factor)

DEGREES

Math

Converts radians into degrees

DEGREES(angle)

DEVSQ

Statistical

Returns the sum of squares of deviations of data points from their sample mean

DEVSQ(number1,number2,number3...)

DISC

Financial

Returns the discount rate for a security

DISC(settlement,maturity,pr,redemption,basis)

DOLLAR

Text

Converts a number to text format and applies a currency symbol

DOLLAR(number,decimals)

DOLLARDE

Financial

Converts a dollar price expressed as a fraction into a price expressed as a decimal number

DOLLARDE(fractional_dollar,fraction_denominator)

DOLLARFR

Financial

Converts a dollar price expressed as a decimal number into a price

DOLLARFR(decimal_dollar,expressed as a fraction fraction_denominator)

DURATION

Financial

Returns the Macauley duration for an assumed par value of $100

DURATION(settlement,maturity,coupon,yield,frequency,basis)

EDATE

Date

Returns the serial number that represents the date that is the indicated number of months before or after a specified date

EDATE(start_date,months)

EFFECT

Financial

Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year

EFFECT(nominal_rate,periods_per_year)

EOMONTH

Date

Returns the serial number for the last day of the month that is the indicated number of months before the start date

EOMONTH(start_date,months)

ERRORTYPE

Info

Returns a number corresponding to a specified error value

ERRORTYPE(error_value)

EVEN

Math

Rounds up the number to the next even integer

EVEN(number)

EXACT

Text

Compares two text strings; returns TRUE if they are exactly the same, or FALSE if not

EXACT(text1,text2)

EXP

Math

Returns the value of e raised to the power of a number

EXP(number)

EXPONDIST

Statistical

Calculates the exponential distribution

EXPONDIST(x,lambda,cumulative)

FACT

Math

Returns the factorial of a number

FACT(number)

FACTDOUBLE

Math

Returns the double factorial of a number

FACTDOUBLE(number)

FIND

Text

Locates one text string within a second text string, and returns the starting position of the interior string

FIND(find_text,within_text,start_num)

FISHER

Statistical

Returns the Fisher transformation at x

FISHER(x)

FISHERINV

Statistical

Returns the inverse of the Fisher transformation

FISHERINV(y)

FIXED

Text

Rounds a number to the specified number of decimals, formats the number with a period and commas, and returns the result as text

FIXED(number,decimals,no_commas)

FLOOR

Math

Rounds down the number, to the nearest multiple of significance

FLOOR(number,significance)

FORECAST

Statistical

Calculates a future value by using existing values

FORECAST(x,known_y's,known_x's)

FREQUENCY

Statistical

Calculates how often values occur within a range of values, then returns a vertical array of numbers

FREQUENCY(data_array,bins_array)

FV

Financial

Calculates the future value of an investment, based on periodic, constant payments and a constant interest rate

FV(rate,periods,payment,present_value,type)

FVSCHEDULE

Financial

Calculates the future value of an initial principal after applying a series of compound interest rates

FVSCHEDULE(principal,schedule)

GCD

Math

Returns the greatest common divisor of two or more numbers

GCD(number1,number2,number3...)

GEOMEAN

Statistical

Returns the geometric mean of a range of positive data

GEOMEAN(number1,number2,number3...)

GROWTH

Statistical

Calculates predicted exponential growth by using existing data

GROWTH(known_y's,known_x's,new_x's,constant)

HARMEAN

Statistical

Returns the harmonic mean of a data set

HARMEAN(number1,number2,number3...)

HLOOKUP

Lookup

Searches for a value in the top row of a range, and then returns a value in the same column from another specified row

LOOKUP(lookup_value,range,row_index_number,range_lookup)

HYPGEOMDIST

Statistical

Returns the hypergeometric distribution (the probability of sample successes)

HYPGEOMDIST(sample_s,number_sample,population_s,number_population)

IF

Logical

Tests whether a condition is true or false

IF(condition,value_if_true,value_if_false)

INDIRECT

Lookup

Returns the reference specified by a text string

INDIRECT(cell,type_of_reference)

INT

Math

Rounds a number down to the nearest integer

INT(value)

INTERCEPT

Statistical

Calculates the point at which a line will intersect the x-axis by using existing x and y values

INTERCEPT(known_y's,known_x's)

IPMT

Financial

Calculates the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate

IPMT(rate,period,number_of_periods,present_value,future_value,type)

IRR

Financial

Calculates the internal rate of return for a series of cash flows

IRR(values,guess)

ISBLANK

Info

Tests whether a cell is blank

ISBLANK(cell)

ISERR

Info

Tests whether a cell contains an error value (except for #N/A)

ISERR(cell)

ISERROR

Info

Tests whether a cell contains any error value

ISERROR(cell)

ISEVEN

Info

Tests whether a number is even

ISEVEN(number)

ISLOGICAL

Info

Tests whether a cell contains a logical value

ISLOGICAL(cell)

ISNA

Info

Tests whether a cell contains the #N/A error value

ISNA(cell)

ISNONTEXT

Info

Tests whether a cell contains an item that is not text

ISNONTEXT(cell)

ISNUMBER

Info

Tests whether a cell contains a number

ISNUMBER(cell)

ISODD

Info

Tests whether a number is odd

ISODD(number)

ISREF

Info

Tests whether a cell contains a reference

ISREF(cell)

ISTEXT

Info

Tests whether a cell contains text

ISTEXT(cell)

KURT

Statistical

Returns the kurtosis of a data set

KURT(number1,number2,number3...)

LARGE

Statistical

Returns the k-th largest value in a range of cells

LARGE(range,k)

LCM

Math

Returns the least common multiple of integers

LCM(number1,number2,number3...)

LEFT

Text

Returns the first character(s) in a text string, based on the number of characters you specify

LEFT(text,number_of_characters)

LEN

Text

Returns the number of characters in a text string

LEN(text)

LINEST

Statistical

Calculates a straight line that best fits given data using the "least squares" method, returning an array that describes the line

LINEST(known_y's,known_x's,constant,stats)

LN

Math

Returns the natural logarithm of a number

LN(number)

LOG

Math

Returns the logarithm of a number to the base you specify

LOG(number,base)

LOG10

Math

Returns the base-10 logarithm of a number

LOG10(number)

LOGEST

Statistical

In regression analysis, calculates an exponential curve that fits the given data, then returns an array that describes the curve

LOGEST(known_y's,known_x's,constant,stats)

LOGINV

Statistical

Returns the inverse of the lognormal cumulative distribution function of x

LOGINV(probability,mean,standard_deviation)

LOGNORMDIST

Statistical

Returns the cumulative lognormal distribution of x

LOGNORMDIST(x,mean,standard_deviation)

LOWER

Text

Converts all characters in a text string to lowercase

LOWER(text)

MAX

Statistical

Returns the largest numeric value in a range of cells

MAX(range)

MAXA

Statistical

Returns the largest value in a range of cells; can include numbers, text, or logical values

MAXA(range)

MATCH

Text

Returns the relative position of an item in a range that matches a specified value in a specified order

MATCH(lookup_value,lookup_range,match_type)

MDETERM

Math

Returns the matrix determinant of an array

MDETERM(array)

MDURATION

Financial

Returns the modified Macauley duration for a security with an assumed par value of $100

MDURATION(settlement,maturity,coupon,yield,frequency,basis)

MEDIAN

Statistical

Returns the median of a range of numbers or cells

MEDIAN(range) or MEDIAN(number1,number2,number3...)

MID

Text

Returns a specific number of characters from a text string, starting at a specified position

MID(text,start_number,number_of_characters)

MIN

Statistical

Returns the minimum numeric value in a range of cells

MIN(range)

MINA

Statistical

Returns the minimum value in a range of cells; can include numbers, text, or logical values

MINA(range)

MINVERSE

Math

Returns the inverse matrix for the matrix stored in an array

MINVERSE(array)

MIRR

Financial

Calculates the modified internal rate of return for a series of periodic cash flows

MIRR(values,finance_rate,reinvest_rate)

MMULT

Math

Returns the matrix product of two arrays

MMULT(array1,array2)

MOD

Math

Returns the modulus of a divisor and a dividendthat is, the remainder after a number is divided by the divisor

MOD(number,divisor)

MODE

Statistical

Returns the most frequently occurring value in a range of data

MODE(range) or MODE(number1,number2,number3...)

MONTH

Date

Returns the month of a date represented by a given serial number

MONTH(serial_number)

MROUND

Math

Returns a number rounded to the desired multiple

MROUND(number,multiple)

MULTINOMIAL

Math

Returns the ratio of the factorial of a sum of values to the product of the factorials

MULTINOMIAL(number1,number2,number3...)

N

Info

Returns a value converted into a number

N(value)

NA

Info

Returns the error value #N/A (which means "no value is available")

NA()

NEGBINOMDIST

Statistical

Calculates the negative binomial distribution

NEGBINOMDIST(number_f,number_s,probability_s)

NETWORKDAYS

Date

Returns the number of whole working days between a start and an end date (working days exclude weekends and holidays)

NETWORKDAYS(start_date,end_date,holidays)

NOMINAL

Financial

Calculates the nominal annual interest rate, given the effective rate and the number of compounding periods per year

NOMINAL(effective_rate,periods_per_year)

NORMDIST

Statistical

Returns the normal distribution for the specified mean and standard deviation

NORMDIST(x,mean,standard_deviation,cumulative)

NORMINV

Statistical

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation

NORMINV(probability,mean,standard_deviation)

NORMSDIST

Statistical

Returns the standard normal cumulative distribution

NORMSDIST(z)

NORMSINV

Statistical

Returns the inverse of the standard normal cumulative distribution

NORMSINV(probability)

NOT

Logical

Reverses the value of its argument

NOT(value)

NOW

Time

Returns the serial number of the current date and time, using your PC's built-in clock

NOW()

NPER

Financial

Calculates the number of payments required to pay off a loan at a given interest rate

NPER(rate,payment,present_value,future_value,type)

NPV

Financial

Calculates the net present value of an investment, using a discount rate and a series of future payments (negative values) and income (positive values)

NPV(rate,value1,value2...)

ODD

Math

Returns the number rounded up to the nearest odd integer

ODD(number)

OFFSET

Lookup

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells

OFFSET(reference,rows,columns,height,width)

OR

Logical

Returns TRUE if any argument is true; returns FALSE if all arguments are false

OR(condition1,condition2,condition3...)

PEARSON

Statistical

Returns the Pearson product moment correlation coefficient

PEARSON(range1,range2)

PERCENTILE

Statistical

Returns the k-th percentile of values in a range

PERCENTILE(range,k)

PERCENTRANK

Statistical

Returns the rank of a value in a data set as a percentage of the data set

PERCENTRANK(range,x,significance)

PERMUT

Statistical

Calculates the number of permutations for a given number of objects that can be selected from number objects

PERMUT(number,number_chosen)

PI

Math

Used in the place of the value pi

PI()

PMT

Financial

Calculates the payment amount required for an investment to bepaid off given a specific term and interest rate

PMT(rate,number_of_periods,present_value,future_value,type)

POISSON

Statistical

Returns the Poisson distribution (to predict the number of events over a specific time)

POISSON(x,mean,cumulative)

POWER

Math

Returns the result of a number raised to a given poser

POWER(number,power)

PPMT

Financial

Calculates the amount of principal being paid during any payment period

PPMT(rate,period,number_of_periods,present_value,future_value,type)

PRICE

Financial

Returns the price per $100 face value of a security that pays periodic interest

PRICE(settlement,maturity,rate,yield,redemption,frequency,basis)

PRICEDISC

Financial

Returns the price per $100 face value of a discounted security

PRICEDISC(settlement,maturity,discount,redemption,basis)

PRICEMAT

Financial

Returns the price per $100 face value of a security that pays interest at maturity

PRICEMAT(settlement,maturity,issue,rate,yield,basis)

PROB

Statistical

Returns the probability that values within a range are between two limits

PROB(x_range,prob_range,lower_limit,upper_limit)

PRODUCT

Math

Multiplies all the numbers in a given range or argument

PRODUCT(number1,number2,number3...) or PRODUCT(range)

PROPER

Text

Capitalizes the first letter in a text string (and any other letters that follow a character other than a letter); converts all other letters to lowercase

PROPER(text)

PV

Financial

Calculates the present value of an investment

PV(rate,number_of_payments,payment,future_value,type)

QUARTILE

Statistical

Calculates the quartile of a data set

QUARTILE(range,quart)

QUOTIENT

Math

Returns the integer portion of a division, discarding the remainder

QUOTIENT(numerator,denominator)

RADIANS

Math

Converts degrees to radians

RADIANS(angle)

RAND

Math

Returns an evenly distributed random real numbergreater than or equal to zero and less than 1

RAND()

RANDBETWEEN

Math

Returns a random integer number between two numbers you specify

RANDBETWEEN(lower_number,higher_number)

RANK

Statistical

Returns the rank of a number in a range or list of numbers

RANK(number,ref,order)

RATE

Financial

Calculates the interest rate per period of an annuity

RATE(number_of_periods,payment,present_value,future_value,type,guess)

RECEIVED

Financial

Calculates the amount received at maturity for a fully invested security

RECEIVED(settlement,maturity,investment,discount,basis)

REPLACE

Text

Replaces part of a text string with another text string

REPLACE(old_text,start_number,number_of_characters,new_text)

REPT

Text

Repeats text a given number of times

REPT(text,number_of_times)

RIGHT

Text

Returns the last character(s) in a string

RIGHT(text,number_of_characters)

ROUND

Math

Rounds a number to a specified number of digits

ROUND(number,number_of_digits)

ROUNDDOWN

Math

Rounds down a number to a specified number of digits

ROUNDDOWN(number,number_of_digits)

ROUNDUP

Math

Rounds up a number to a specified number of digits

ROUNDUP(number,number_of_digits)

ROW

Lookup

Returns the row number of a cell or range of cells

ROW(cell)

ROWS

Lookup

Returns the number of rows in a range

ROW(range)

RSQ

Statistical

Returns the square of the Pearson product moment correlation coefficient

RSQ(known_y's,known_x's)

SEARCH

Text

Locates one text string within a second text string, and returns the starting point of the inside text string

SEARCH(find_text,within_text,start_number)

SERIESSUM

Math

Returns the sum of a power series

SERIESSUM(x,n,m,coefficients)

SIGN

Math

Determines the sign (positive or negative) of a number

SIGN(number)

SIN

Math

Returns the sine of a given number or angle

SINE(number)

SINH

Math

Returns the hyperbolic sine of a number or angle

SINH(number)

SKEW

Statistical

Returns the skewness of a distribution

SKEW(number1,number2,number3...)

SLN

Financial

Calculates depreciation using the straight line method

SLN(cost,salvage,life)

SLOPE

Statistical

Returns the slope of the linear regression line through known x and y data points

SLOPE(known_y's,known_x's)

SMALL

Statistical

Returns the k-th smallest value in a data set

SMALL(range,k)

SQRT

Math

Returns a positive square root

SQRT(number)

SQRTPI

Math

Returns the square root of (number * pi)

SQRTPI(number)

STANDARDIZE

Statistical

Returns a normalized value from a distribution characterized by mean and standard deviation

STANDARDIZE(x,mean,standard_deviation)

STDEV

Statistical

Estimates standard deviation based on a numerical sample

STDEV(range)

STDEVA

Statistical

Estimates standard deviation; can include numbers, text, or logical values

STDEVA(range)

STDEVP

Statistical

Calculates standard deviation based on the entire population given as arguments

STDEVP(range)

STDEVPA

Statistical

Calculates standard deviation based on the entire population given as arguments, including text and logical values

STDEVPA(range)

STEYX

Statistical

Returns the standard error of the predicted y value for each x in the regression

STEYX(known_y's,known_x's)

SUBSTITUTE

Text

Substitutes new text for old text in a text string

SUBSTITUTE(text,old_text,new_text,instance_number)

SUM

Math

Calculates the total of a group of cells

SUM(range)

SUMIF

Math

Calculates the total of a group of cells defined by a given criteria

SUMIF(range,criteria,sum_range)

SUMPRODUCT

Math

Multiplies corresponding components in the given arrays, and returns the sum of those products

SUMPRODUCT(array1,array2,array3...)

SUMSQ

Math

Returns the sum of the squares of a series of numbers

SUMSQ(number1,number2,number3...)

SUMX2MY2

Math

Returns the sum of the difference of squares of corresponding values in two arrays

SUMX2MY2(array_x,array_y)

SUMX2PY2

Math

Returns the sum of the squares of corresponding values in two arrays

SUMX2PY2(array_x,array_y)

SUMXMY2

Math

Returns the sum of squares of differences of corresponding values in two arrays

SUMXMY2(array_x,array_y)

SYD

Financial

Calculates depreciation using the sum of the years' digits method

SYD(cost,salvage,life,period)

T

Text

Returns the text referred to by value

T(value)

TAN

Math

Returns the tangent of a number

TAN(number)

TANH

Math

Returns the hyperbolic tangent of a number

TANH(number)

TBILLEQ

Financial

Calculates the bond-equivalent yield for a Treasury bill

TBILLEQ(settlement,maturity,discount)

TBILLPRICE

Financial

Calculates the price per $100 face value for a Treasury bill

TBILLPRICE(settlement,maturity,discount)

TBILLYIELD

Financial

Calculates the yield for a Treasury bill

TBILLYIELD(settlement,maturity,price)

TEXT

Text

Converts a value to text in a specific number format

TEXT(value,format_text)

TODAY

Date

Returns the serial number of the current date

TODAY()

TRANSPOSE

Lookup

Flips a vertical range of cells horizontally, or vice versa

TRANSPOSE(range)

TREND

Statistical

Returns values along a linear trend

TREND(known_y's,known_x's,new_x's,constant)

TRIM

Text

Removes all spaces from a text string except for single spaces between words

TRIM(text)

TRIMMEAN

Statistical

Returns the mean of the interior of a data set, excluding a percentage of the data points from the top and bottom tails of the set

TRIMMEAN(range,percent)

TRUNC

Math

Truncates a number to an integer by removing the fractional part of the number; you can specify precision of the truncation by indicating how many digits to include

TRUNC(number,number_of_digits)

UPPER

Text

Converts text to all uppercase

UPPER(text)

VALUE

Text

Converts a text string that represents a number to that number

VALUE(text)

VAR

Statistical

Estimates variance based on a numeric sample

VAR(range)

VARA

Statistical

Estimates variance based on a sample; can include numbers, text, and logical values

VARA(range)

VARP

Statistical

Calculates variance based on the total numeric population

VARP(range)

VARPA

Statistical

Calculates variance based on the entire population, including numeric, text, and logical values

VARPA(range)

VLOOKUP

Lookup

Searches for a value in the first column of a range, and then returns a value in the same row from another specified column

VLOOKUP(lookup_value,range,column_index_number,range_lookup)

WEEKDAY

Date

Returns the day of the week that corresponds to any valid date

WEEKDAY(serial_number,return_type)

WEIBULL

Statistical

Returns the Weibull distribution

WEIBULL(x,alpha,beta,cumulative)

WORKDAY

Date

Returns a number that represents a date that is the indicated number of working days before or after a start date

WORKDAY(start_date,days,holidays)

XIRR

Financial

Calculates the internal rate of return for a schedule of cash flows that is not necessarily periodic

XIRR(values,dates,guess)

XNPV

Financial

Calculates the net present value for a schedule of cash flows that is not necessarily periodic

XNPV(rate,values,dates)

YEAR

Date

Returns the year corresponding to a specified date serial number

YEAR(serial_number)

YEARFRAC

Date

Calculates the fraction of a year represented by the number of whole days between two dates

YEARFRAC(start_date,end_date,basis)

YIELD

Financial

Calculates the yield on a security that pays back periodic interest

YIELD(settlement,maturity,rate,price,redemption,frequency,basis)

YIELDDISC

Financial

Calculates the annual yield for a discounted security

YIELDDISC(settlement,maturity,price,redemption,basis)

ZTEST

Statistical

Returns the one-tailed probability value of a z-test

ZTEST(range,value_to_test,sigma)


Section 13. Working with Multiple Sheets

Like Excel, Google Spreadsheets lets you work with multiple sheets (Google calls them tabs) within a single spreadsheet file. Unlike Excel, which always starts with three sheets per spreadsheet, Google defaults to a single sheet. You can then add additional sheets to this first sheet.


Adding New Sheets

To add a new sheet to your spreadsheet, all you have to do is click the Add Sheet button at the bottom of the main spreadsheet window. To switch to a different sheet, just click its link, as shown in Figure 38.

Figure 38. Multiple sheets within a single spreadsheet file.

Previous Page
Next Page


Renaming Sheets

By default, Google names its sheets Sheet1, Sheet2, Sheet3, and so forth. If you'd like a somewhat more descriptive name for a sheet, follow these steps:

1.
Click a sheet link to make it the active sheet.

2.
Click the tab for the active sheet; this displays a pop-up menu, as shown in Figure 39.

Figure 39. Renaming a sheet.

3.
Click Rename.

4.
When prompted, enter a new name for the sheet, and then click OK.


Deleting Sheets

If you add a sheet that you later don't need, it's easy to delete it. Just follow these steps:

1. | Click the sheet link for the sheet you want to delete.
2. | Click the tab for the active sheet; this displays a pop-up menu.
3. | Click Delete.


Section 14. Printing Google Spreadsheets

When you're finished creating your spreadsheet, you might want to print a hard copy. This is a fairly easy, if not overly intuitive, task, as there's no quick and easy "print" button or function. Instead, here's what you need to do:

1.
Click the File button and select Get HTML.

2.
This opens a new browser window that contains just the spreadsheet, no other buttons or controls, as shown in Figure 40. It's this window that you want to print.

Figure 40. Use the Get HTML command to open a new browser window for printing.

[View full size image]

3.
Switch to the new browser window, then click the Print button in your web browser.

The contents of the new browser windowthat is, your spreadsheet datawill now print.

Note

Google Spreadsheets doesn't let you automatically print multiple sheets in a spreadsheet file; it only prints one sheet at a time. You must switch to each sheet separately, and then go through the File, Get HTML procedure.


Section 15. Sharing and Collaborating with Google Spreadsheets

The truly unique feature of Google Spreadsheets is the capability to share a spreadsheet with otherseither for viewing or for collaborative editing. The only hitch to this process is that anyone you wish to share with must have their own Google Account to access the Google Spreadsheets site. That said, you can easily invite another users to create his or own new Google Account.

Note

At present, there is no limit to the number of people with whom you can share a spreadsheet. The only requirement is that anyone you invite to share your spreadsheet have their own Google Account.


Sharing a Spreadsheet for Viewing

We'll start with the process of sharing a spreadsheet for viewing only. This lets other users view your spreadsheet but doesn't give them access to add or edit data. (It's a read-only process.)

To share a spreadsheet for viewing, follow these steps:

1.
From a saved spreadsheet, click the Show Sharing Options link; this opens the sharing pane, shown in Figure 41.

Figure 41. Getting ready to share a spreadsheet.

[View full size image]

2.
In the Invite People to View box, enter the email addresses of the people you want to share the spreadsheet. (Separate multiple addresses with commas.)

3.
Click the Invite People button.

4.
This opens an Invitation window, like the one shown in Figure 42. Enter a personal message if you like, and then click the Send Invitation button.

Figure 42. Inviting another user to share a spreadsheet.

[View full size image]

Your recipients now receive an invitation via email. The invitation contains a link to the spreadsheet; clicking this link opens the spreadsheet in a new browser window.

Anyone viewing your spreadsheet can not only navigate around the entire file (including multiple sheets within the spreadsheet file), but also save that file to their personal Google Spreadsheets online storage area or as an XLS-format file to their own PC. While viewing, other users will see the current work in progress; as soon as you press Enter when editing a cell, the edited contents will appear on the other users' screens. (They can't see the in-progress contents of the cell while you're editing the cell, however.)

Note

Anyone you invite to view a spreadsheet can, in turn, invite other users to also view the spreadsheet.


Sharing a Spreadsheet for Collaboration

When you invite someone to view a spreadsheet, that's all they can do; they can navigate around the spreadsheet, but they can't add to or edit any of the data within the spreadsheet. If you want to collaborate with others on a spreadsheet, you must explicitly open the spreadsheet for collaboration.

Caution

Google permits more than one user at a time to make changes to an open spreadsheet; the spreadsheet isn't "locked" when the first user starts editing. This can create havoc if both users try to make changes to the same data or aren't aware of the other changes being made. For this reason, you should always use caution while collaboratively editing a spreadsheet.

To share a spreadsheet for collaboration, follow these steps:

1. | From a saved spreadsheet, click the Show Sharing Options link; this opens the sharing pane.
2. | In the Invite People to Edit box, enter the email addresses of the people with whom you want to collaborate. (Separate multiple addresses with commas.)
3. | Click the Invite People button.
4. | This opens an Invitation window. Enter a personal message if you like, and then click the Send Invitation button.

Your recipients now receive an invitation via email. The invitation contains a link to the spreadsheet; clicking this link opens the spreadsheet in a new browser window. Once accessed, the other users can now edit the spreadsheet, in real time. In fact, multiple users can edit the spreadsheet at the same time; each person's edits appear on all the other users' screens as soon as they press the Enter key when editing a cell.

Note

Anyone you invite to edit a spreadsheet can, in turn, invite other users to also edit the spreadsheet.


Chatting with Spreadsheet Viewers

As you can see in Figure 43, the view spreadsheet window that other users see includes a chat pane. This lets your viewers chat with you, in real time, while they're viewing your spreadsheet.

Figure 43. Chatting with other viewers/editors of your spreadsheet.

[View full size image]

To view the chat pane in your own spreadsheet window, simply click the Chat With link at the top right of the window. You can close the chat pane at any time by clicking the resulting Hide Chat link.

While chatting, you enter your comments in the bottom text box. When you press Enter, your comments are sent to the other users and appear in the main text box in the chat pane.


Rescinding Access

If, at any time, you want to block a user from viewing or editing your spreadsheet, here's what to do:

1.
Click the Show Sharing Options link. The list of viewers now appears at the bottom of the sharing pane, as shown in Figure 44.

Figure 44. Removing a user from the viewing list.

[View full size image]

2.
Click the Remove link next to the person for whom you want to rescind viewing/editing privileges.

That's it; the person you selected will no longer have access to this spreadsheet.


Versioning Your Work in Progress

When you're collaborating on a spreadsheet, it helps to keep different versions of the file on hand as you work; this lets you return to a previous version, if necessary.

Google Spreadsheets doesn't offer automatic versioning. In fact, as Google saves your changes as you make them, it's a little tricky to move backward through the different iterations of your work. However, there is a trick you can employ to add versioning to your spreadsheets. Here's how it works:

1. | When you're ready to create a new version, select File, Save As.
2. | When prompted, save the current file with a version after the name. For example, if the file is named spreadsheet1, you might save the current version as spreadsheet1v01 or spreadsheet1v02. You might even want to save the date as part of the filename, as in spreadsheet1 7-1-06v01.
3. | Any time you make a major change to the spreadsheet, repeat steps 1 and 2 and give the file a new version name.

Some users version their work once a day; others do it once an hour; still others do it whenever they add or change anything big in the spreadsheet. How often you save new versions is up to you, but the important thing is to save your work in multiple iterations. This will make it relatively easy to recover a previous version, if necessary.


Section 16. A Final Word

And that's all there is to say about Google Spreadsheets. As you've seen, Google Spreadsheets is a great little tool that should prove useful to all types of spreadsheet users. Know, however, that Google Spreadsheets is a work in progress, so keep your eyes open for refinements, improvements, and additional features. As good as it already is, expect Google to make Google Spreadsheets even better in the future!


Command Reference

This handy, printable command reference will serve as your cheat sheet while you get better acquainted with Google Spreadsheets. Print it and keep it next to your keyboard while getting up to speed.

All tab
Element | Description
File button | Lets you save, open, import, and export spreadsheet files
Save button | Saves the current spreadsheet
New link | Opens a new spreadsheet in a new browser window
Open link | Opens a previously saved spreadsheet, or uploads an XLS spreadsheet file stored on your computer
Spreadsheet title and info | Displays the title of the current spreadsheet, as well as when it was last saved (visible only when using a saved spreadsheet)
Share This Spreadsheet link | Saves the current spreadsheet and displays sharing options (visible only on newly createdthat is, unsavedspreadsheets)
Show/Hide Sharing Options link | Lets you invite other users to share or view the current spreadsheet (visible only when using a saved spreadsheet)
Format tab | Displays formatting controls
Sort tab | Displays sorting controls
Formulas tab | Displays formula controls
Cut button | Cuts the data in the selected cell(s)use when you want to move data to another location
Copy button | Copies the data in the selected cell(s)
Paste button | Pastes cut or copied data
Undo button | Undoes the most recent entry, edit, or operation
Redo button | Redoes an undone operation
Add Sheet button | Adds a new sheet to the current spreadsheet
Sheet 1, Sheet 2, etc. | Click to select different sheets within the current spreadsheet
Reference area | Displays the contents of the current celleither raw data or the formula behind the numbers
Format Tab
Choose Format button | Selects a number, date, or plain text format for the selected cell(s)
Bold | Bolds the contents of the selected cell(s)
Italic | Italicizes the contents of the selected cell(s)
Underline | Underlines the contents of the selected cell(s)
Font Family | Applies a particular font (typeface) to the selected cell(s)
Font Size | Changes the font size of the selected cell(s)
Text Color | Changes the color of the contents of the selected cell(s)
Background Color | Changes the background color of the selected cell(s)
Clear Format | Clears all formatting from the selected cell(s)
Align button | Changes the alignment (left, center, right, etc.) of the selected cell(s)
Insert button | Inserts new rows or columns into the spreadsheet
Delete button | Deletes rows or columns from the spreadsheet
Wrap Text | When checked, wraps long text to additional lines within the selected cell(s)
Merge Across | Merges adjacent cells into a single cell
Sort Tab
Freeze Rows | When sorting, freezes the top row(s) of the spreadsheet as header rows
Sort Sheet by Selected Column: A > Z | Sorts the spreadsheet by the currently selected column, in alphabetical (or numerical) order
Sort Sheet by Selected Column: Z > Z | Sorts the spreadsheet by the currently selected column, in reverse alphabetical (or numerical) order
Formulas Tab
Reference area | Displays the name of the selected cell
Sum | Calculates the total of a group of cells
Count | Counts the number of cells in a range that contain numeric values
Average | Calculates the mean average of a group of numbers
Min | Returns the minimum value in a range of cells
Max | Returns the maximum value in a range of cells
Product | Calculates the product of the specified valuesthat is, it multiplies all the values together
More>> | Displays all available Google Spreadsheets functions