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
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.
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
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: Mail:
[email protected] 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
Google Spreadsheets
Microsoft Excel 2003
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 webbased 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
Saves the current spreadsheet and displays sharing options (visible only on newly createdthat is, unsavedspreadsheets)
All
Element
Tab
Description
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
Element
Tab
Description
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.
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.
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.
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.
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.
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: - Number Typed: 5 .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.
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
Formula
Result
=(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.
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.
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.
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 .
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)
Table 10. Google Spreadsheet Functions Function Type
Description
Use (Arguments)
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.
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
