Using Google Spreadsheets

ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 T able of C ontents 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. Page 1 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 T able of C ontents Copy right Introduction We Want to Hear f rom You! Section 1. Introducing Google Spreadsheets What's Unique About Google Spreadsheets? Comparing Google Spreadsheets to Excel Other Web-Based Spreadsheets Priv acy and Security Concerns Should You Use Google Spreadsheets? Section 2. Migrating f rom Excel to Google Spreadsheets Section 3. Nav igating the Google Spreadsheets Workspace Understanding Elements of the Workspace Nav igating with the Key board Freezing Rows While You Scroll Section 4. Creating, Opening, and Sav ing Google Spreadsheets Creating a New Spreadsheet Opening an Existing Spreadsheet Importing a Spreadsheet f rom Excel Sav ing a Spreadsheet Sav ing 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 Prev iously Entered Data Section 6. Working with Ranges Understanding Ranges Selecting a Range Section 7. Sorting Data Executing a Sort Sorting a Range Perf orming Multiple-Column Sorts Section 8. Inserting, Deleting, Mov ing, and Copy ing Data Inserting Rows and Columns Deleting Data Mov ing Data with the Cut and Paste Commands Copy ing 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 Ty pe Sizes Aligning Inf ormation in a Cell Changing Number Formats Changing the Background Color of Cells and Ranges Page 2 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f or Viewing Sharing a Spreadsheet f or Collaboration Chatting with Spreadsheet Viewers Rescinding Access Versioning Your Work in Progress Section 16. A Final Word Command Ref erence Page 3 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Page 4 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Introduction Introducing Google Spreadsheets Migrating from Excel to Google Spreadsheets Navigating the Google Spreadsheets Workspace Creating, Opening, and Saving Google Spreadsheets Entering and Editing Data Working with Ranges Sorting Data Inserting, Deleting, Moving, and Copying Data Changing Column Width and Row Height Formatting Google Spreadsheets Entering and Editing Formulas Using Functions Working with Multiple Sheets Printing Google Spreadsheets Sharing and Collaborating with Google Spreadsheets A Final Word Index Command Reference 4 12 12 21 26 27 29 30 33 34 39 42 64 65 65 70 71 73 Page 5 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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: feedback@quepublishing.com 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. Page 6 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull size image] Note Learn more about Google and all its services and applications in my companion book, Googlepedia (Michael Miller, Que Publishing, 2006). Note Page 7 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 8 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 9 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull size image] Figure 3. ...compared to a similar blank spreadsheet in Microsoft Excel. [View f ull size image] Page 10 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Reads and writes XLS-format files Imports and exports CSV-format files Multiple sheets per spreadsheet Excel-type formulas Number of built-in functions Macros Pivot tables Database functionality Charts and graphs Text formatting Yes Yes Yes Yes 232 No No No No Yes Microsoft Excel 2003 Yes Yes Yes Yes 288 Yes Yes Yes Yes Yes Page 11 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 1. Comparison: Google Spreadsheets Versus Microsoft Excel Google Spreadsheets Number formatting Cell formatting Online sharing and collaboration Saves documents online for access from any PC Can use when not connected to the Internet Yes Yes Yes Yes Microsoft Excel 2003 Yes Yes No No 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. Page 12 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull size image]  Num Sum (www.numsum.com) The following web-based office suites also have spreadsheet components:   ThinkFree (www.thinkfree.com) Zoho (www.zoho.com), shown in Figure 5 Figure 5. The Zoho Sheet spreadsheet application in the Zoho web-based office suite. [View f ull size image] Page 13 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 14 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 15 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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.  Page 16 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 17 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 18 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 19 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Page 20 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 21 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Page 22 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Page 23 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 File button Save button New link Open link Tab All All All All Description Lets you save, open, import, and export spreadsheet files Saves the current spreadsheet Opens a new spreadsheet in a new browser window Opens a previously saved spreadsheet, or uploads an XLS spreadsheet file stored on your computer Displays the title of the current spreadsheet, as well as when it was last saved (visible only when using a saved spreadsheet) Saves the current spreadsheet and displays sharing options (visible only on newly createdthat is, unsavedspreadsheets) Lets you invite other users to share or view the current Spreadsheet title and All info Share This Spreadsheet link All Show/Hide Sharing All Page 24 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 2. Elements of the Google Spreadsheets Workspace Element Options link Tab Description spreadsheet (visible only when using a saved spreadsheet) All All All All Displays formatting controls Displays sorting controls Displays formula controls Cuts the data in the selected cell(s)use when you want to move data to another location Copies the data in the selected cell(s) Pastes cut or copied data Undoes the most recent entry, edit, or operation Redoes an undone operation Adds a new sheet to the current spreadsheet Click to select different sheets within the current spreadsheet Displays the contents of the current celleither raw data or the formula behind the numbers Selects a number, date, or plain text format for the selected cell(s) Bolds the contents of the selected cell(s) Italicizes the contents of the selected cell(s) Underlines the contents of the selected cell(s) Applies a particular font (typeface) to the selected cell(s) Changes the font size of the selected cell(s) Changes the color of the contents of the selected cell(s) Changes the background color of the selected cell(s) Clears all formatting from the selected cell(s) Format tab Sort tab Formulas tab Cut button Copy button Paste button Undo button Redo button Add Sheet button Sheet 1, Sheet 2, etc. Reference area All All All All All All All Choose Format button Bold Italic Underline Font Family Font Size Text Color Background Color Clear Format Format Format Format Format Format Format Format Format Format Page 25 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 2. Elements of the Google Spreadsheets Workspace Element Align button Tab Format Description Changes the alignment (left, center, right, etc.) of the selected cell(s) Inserts new rows or columns into the spreadsheet Deletes rows or columns from the spreadsheet When checked, wraps long text to additional lines within the selected cell(s) Merges adjacent cells into a single cell When sorting, freezes the top row(s) of the spreadsheet as header rows Sorts the spreadsheet by the currently selected column, in alphabetical (or numerical) order Insert button Delete button Wrap Text Format Format Format Merge Across Freeze Rows Format Sort Sort Sheet by Sort Selected Column: A > Z Sort Sheet by Sort Selected Column: Z > Z Reference area Sum Count Sorts the spreadsheet by the currently selected column, in reverse alphabetical (or numerical) order Formulas Displays the name of the selected cell Formulas Calculates the total of a group of cells Formulas Counts the number of cells in a range that contain numeric values Formulas Calculates the mean average of a group of numbers Formulas Returns the minimum value in a range of cells Formulas Returns the maximum value in a range of cells Formulas Calculates the product of the specified valuesthat is, it multiplies all the values together Formulas Displays all available Google Spreadsheets functions Average Min Max Product More>> Page 26 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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) Up arrow Down arrow Right arrow Left arrow Tab Shift+Tab Ctrl+Home Page Down Page Up Navigation Move up one cell Move down one cell Move right one cell Move left one cell Move right one cell Move left one cell Move to cell A1 Move one screen down Move one screen up Page 27 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 28 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 29 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 30 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull 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. Page 31 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 32 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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.) Page 33 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 34 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 35 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull size image] Page 36 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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.) Page 37 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 38 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Numbers Description Numbers can be in a variety of formats, including currency and percent formats. All numbers can be manipulated mathematically. Text can contain both alphabetic and numerical characters. Text cannot be manipulated mathematically. Dates are specially formatted numbers. Formulas tell Google Spreadsheets how to make calculations using data in other cells. Text Dates Formulas All data can be formatted in a variety of styles. Formatting and styles are discussed in later lessons. Page 39 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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.     Page 40 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 41 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 42 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 43 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 44 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 45 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Executing a Sort Sorting data in Google Spreadsheets is a two-step operation. You first have to "freeze" the header row(s) of your spreadsheet, then you identify the column by which you want to sort. Google will then order all the "unfrozen" (non-header) rows of your spreadsheet in whichever order (ascending or descending) you specified. Here's how to do it: 1. Click the Sort tab, as shown in Figure 19. Figure 19. Getting ready to sort via the Sort tab. 2. Click the Freeze Rows button and select how many rows you want to include as the spreadsheet's header, as shown in Figure 20. (In most cases, it's just one row.) When you freeze a header row, it no longer scrolls with the rest of the spreadsheetand is not included in any sort. Figure 20. Choosing which rows to freeze. 3. Identify which column you want to sort by, and move the cursor to any cell within that column. 4. To sort in ascending order, click the A>Z button; to sort in descending order, click the Z>A button. The A>Z and Z>A sorts don't just sort by letter; they also sort by number. An A>Z sort will arrange numerical data from smallest to largest; a Z>A sort will arrange numerical data from largest to smallest. Page 46 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 47 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 48 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 49 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 50 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 51 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 52 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 53 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 54 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 55 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 56 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 57 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. 2. 3. Select the Format tab. Select a cell or range of cells. 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 Page 58 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html To remove all text formatting within a cell or range of cells, select the cell(s) and click the Clear Format button. Page 59 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 60 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Figure 26. Choosing a new font size for your text. Page 61 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 62 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Plain Text Normal Rounded 2 Decimals Financial Rounded Financial Dollar Dollar and Cents Number Typed: 5000 5000 5000 5,000 5,000.00 5,000 Number Typed: Number Typed: 5 -5 5 5 5 5.00 5 -5 -5 -5 -5.00 (5) Number Typed: .5 0.5 0.5 0 0.50 0 5,000.00 $5,000 $5,000.00 5.00 $5 $5.00 (5.00) -$5 -$5.00 0.50 $0 $0.50 Table 6. Google Spreadsheets Percent Formats Number Format Percent Rounded Percent Number Typed: Number Typed: 5 .5 500% 50% Number Typed: .005 0% Number Typed: -.5 -50% 500.00% 50.00% 0.50% -50.00% Page 63 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 7. Google Spreadsheets Date and Time Formats. Number Format Date (1) Date (2) Time Date Time Example 2/14/2006 14-Feb-2006 2:45:30 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. Page 64 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Page 65 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 66 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 67 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 =2 =A1 =A1*2 =A1/2 =A1+A2 =(A1+A2)/2 Result Places the numerical value 2 in the selected cell Places the value of cell A1 in the selected cell Places the value of cell A1 multiplied by 2 in the selected cell Places the value of cell A1 divided by 2 in the selected cell Places the value of cell A1 plus the value of cell A2 in the selected cell 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. Page 68 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 Page 69 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 70 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 71 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 72 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 73 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 74 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull size image] 2. 3. Move the cursor into the cell you want to hold the results of the function. 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 Page 75 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 76 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 77 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 78 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 79 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 80 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 81 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 82 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Other Google Spreadsheets Functions Google Spreadsheets includes more than 200 individual functions. These functions are identical to the ones built in to Microsoft Excel, so if you're an Excel user, you'll be right at home. If you're not an Excel user, or if you want a handy reference to all the available functions, see Table 10. Table 10. Google Spreadsheet Functions Descriptio n Calculates the absolute value of a number Function ABS Type Math Use (Arguments) ABS(number) ACCRINT Financi Returns the ACCRINT(issue,first_interest,settlement,rate,par,frequen al accrued cy,basis) interest for a security that pays periodic interest Financi Returns the ACCRINTM(issue,settlement,rate,par,basis) al accrued interest for a security that pays interest at maturity Math Returns the ACOS(number) arccosine (inverse cosine) of a number Returns the ACOSH(number) inverse hyperbolic cosine of a number ACCRINTM ACOS ACOSH Math ADDRESS Lookup Creates a ADDRESS(row,column,type_of_reference,reference_style cell address , external_sheet_name) as text, given specified row and column Page 83 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n numbers AND Logical Returns AND(condition1,condition2,condition3...) TRUE if all arguments are true; returns FALSE if any argument is false Math Returns the ASIN(number) arcsine (inverse sine) of a number Returns the ASINH(number) inverse hyperbolic sine of a number Returns the ATAN(number) arctangent (inverse tangent) of a number Returns the ATAN2(x,y) arctangent (inverse tangent) of the specified x and y coordinates Returns the ATANH(number) inverse hyperbolic tangent of a number AVEDEV(range) Function Type Use (Arguments) ASIN ASINH Math ATAN Math ATAN2 Math ATANH Math AVEDEV Statisti Calculates cal the average of the absolute Page 84 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n deviations of data points from their mean AVERAGE Statisti Calculates cal the average (arithmetic mean) of a group of numbers Statisti Calculates cal the average (arithmetic mean) of a group of numbers, text, or logical values Statisti Calculates cal the individual term binomial distribution probability Math AVERAGE(range) Function Type Use (Arguments) AVERAGEA AVERAGEA(range) BINOMDIST BINOMDIST(number_s,trials, probability_s,cumulative) CEILING Rounds up CEILING(number,significance) a number to the nearest multiple of significance Returns the CHAR(number) character specified by a numer CHOOSE(index_number,value1, value2,value3...) CHAR Text CHOOSE Lookup Selects a number from a list based on an index number Page 85 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Returns a numeric code for the first value in a text string Function CODE Type Text Use (Arguments) CODE(text) COLUMNS Lookup Returns the COLUMNS(range) number of columns in a range Math Returns the COMBIN(number_of_items,number_of_items_in_each_co number of mbination) combinatio ns for a given number of items Joins CONCATENATE(text1,text2,text3...) several text strings into a single text string COMBIN CONCATENA Text TE CONFIDENC E Statisti Returns a CONFIDENCE(alpha,standard_deviation, size) cal value that can be used to construct a confidence interval for a population mean Statisti Returns the CORREL(range1,range2) cal correlation coefficient of two cell ranges Math Returns the COS(number) cosine of a number Returns the COSH(number) hyperbolic CORREL COS COSH Math Page 86 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n cosine of a number COUNT Math Counts the number of cells in a range that contain numeric values Counts the number of non-blank cells in a range COUNT(range) Function Type Use (Arguments) COUNTA Math COUNTA(range) COUNTBLAN Math K Counts the COUNTBLANK(range) number of empty cells in a range Counts the number of cells in a range that meet a specified criteria COUNTIF(range,criteria) COUNTIF Math COUPDAYBS Financi Returns the COUPDAYBS(settlement, maturity, frequency,basis) al number of days from the beginning of the coupon period to the settlement date COUPDAYS Financi Returns the COUPDAYS(settlement,maturity, frequency,basis) al number of days in the coupon period that contains the settlement Page 87 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n date COUPDAYSN Financi Returns the COUPDAYSNC(settlement,maturity, frequency,basis) C al number of days from the settlement date to the next coupon date COUPNCD Financi Returns a al number that represents the next coupon date after the settlement date COUPNCD(settlement, maturity, frequency, basis) Function Type Use (Arguments) COUPNUM Financi Returns the COUPNUM(settlement,maturity,frequency,basis) al number of coupons payable between the settlement date and maturity date Financi Returns a COUPPCD(settlement,maturity,frequency,basis) al number that represents the previous coupon date before the settlement date Statisti Returns cal covariance (the average of the COVAR(range1,range2) COUPPCD COVAR Page 88 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n products of deviations for each data point pair) CRITBINOM Statisti Returns the CRITBINOM(trials,probability_s,alpha) cal smallest value for which the cumulative binomial distribution is greater than or equal to the criterion value Financi Returns the CUMIPMT(rate,nper,present_value,start_period,end_peri al relative od,type) interest paid between the start and the end of a loan Financi Returns the CUMPRINC(rate,nper,present_value,start_period,end_peri al cumulative od,type) principal paid on a loan between two dates Date Converts a date into a sequential serial number that represents that date Converts a properly formatted text string DATE(year,month,day) Function Type Use (Arguments) CUMIPMT CUMPRINC DATE DATEVALUE Date DATEVALUE(date_text) Page 89 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n (such as "1/20/2006" ) into a valid date DAY Date Returns the DAY(date) day of the week that correspond s to a specified date Returns the DAYS360(start_date,end_date,method) 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 Function Type Use (Arguments) DAYS360 Date DB Financi Calculates DB(cost,salvaged,life,period,month) al depreciatio n using the fixed-declin ing balance method Financi Calculates DDB(cost,salvage,life,period,factor) al depreciatio n using the double declining balance method Math Converts DEGREES(angle) radians into degrees DDB DEGREES DEVSQ Statisti Returns the DEVSQ(number1,number2,number3...) Page 90 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n sum of squares of deviations of data points from their sample mean Function Type cal Use (Arguments) DISC Financi Returns the DISC(settlement,maturity,pr,redemption,basis) al discount rate for a security Text Converts a DOLLAR(number,decimals) number to text format and applies a currency symbol DOLLAR DOLLARDE Financi Converts a DOLLARDE(fractional_dollar,fraction_denominator) al dollar price expressed as a fraction into a price expressed as a decimal number Financi Converts a DOLLARFR(decimal_dollar,expressed as a fraction al dollar price fraction_denominator) expressed as a decimal number into a price Financi Returns the DURATION(settlement,maturity,coupon,yield,frequency,b al Macauley asis) duration for an assumed par value of $100 Date Returns the EDATE(start_date,months) serial number DOLLARFR DURATION EDATE Page 91 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n that represents the date that is the indicated number of months before or after a specified date EFFECT Financi Returns the EFFECT(nominal_rate,periods_per_year) al effective annual interest rate, given the nominal annual interest rate and the number of compoundin g periods per year Date Returns the EOMONTH(start_date,months) serial number for the last day of the month that is the indicated number of months before the start date Returns a ERRORTYPE(error_value) number correspondi ng to a specified error value Rounds up EVEN(number) the number to the next even Function Type Use (Arguments) EOMONTH ERRORTYPE Info EVEN Math Page 92 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n integer EXACT Text Compares EXACT(text1,text2) two text strings; returns TRUE if they are exactly the same, or FALSE if not Returns the EXP(number) value of e raised to the power of a number Function Type Use (Arguments) EXP Math EXPONDIST Statisti Calculates EXPONDIST(x,lambda,cumulative) cal the exponential distribution Math Returns the FACT(number) factorial of a number Returns the FACTDOUBLE(number) double factorial of a number Locates FIND(find_text,within_text,start_num) one text string within a second text string, and returns the starting position of the interior string FACT FACTDOUBL Math E FIND Text FISHER Statisti Returns the FISHER(x) cal Fisher transformat ion at x Page 93 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Function FISHERINV Type Use (Arguments) Statisti Returns the FISHERINV(y) cal inverse of the Fisher transformat ion Text Rounds a FIXED(number,decimals,no_commas) number to the specified number of decimals, formats the number with a period and commas, and returns the result as text Rounds FLOOR(number,significance) down the number, to the nearest multiple of significance FORECAST(x,known_y's,known_x's) FIXED FLOOR Math FORECAST Statisti Calculates cal a future value by using existing values FREQUENCY Statisti Calculates cal how often values occur within a range of values, then returns a vertical array of numbers FV FREQUENCY(data_array,bins_array) Financi Calculates FV(rate,periods,payment,present_value,type) al the future value of an Page 94 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n investment, based on periodic, constant payments and a constant interest rate FVSCHEDUL Financi Calculates FVSCHEDULE(principal,schedule) E al the future value of an initial principal after applying a series of compound interest rates GCD Math Returns the GCD(number1,number2,number3...) greatest common divisor of two or more numbers Function Type Use (Arguments) GEOMEAN Statisti Returns the GEOMEAN(number1,number2,number3...) cal geometric mean of a range of positive data Statisti Calculates GROWTH(known_y's,known_x's,new_x's,constant) cal predicted exponential growth by using existing data Statisti Returns the HARMEAN(number1,number2,number3...) cal harmonic mean of a data set GROWTH HARMEAN Page 95 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Function HLOOKUP Type Use (Arguments) Lookup Searches LOOKUP(lookup_value,range,row_index_number,range_lo for a value okup) in the top row of a range, and then returns a value in the same column from another specified row HYPGEOMDI Statisti Returns the HYPGEOMDIST(sample_s,number_sample,population_s,nu ST cal hypergeom mber_population) etric distribution (the probability of sample successes) IF Logical Tests IF(condition,value_if_true,value_if_false) whether a condition is true or false Lookup Returns the INDIRECT(cell,type_of_reference) reference specified by a text string Math Rounds a INT(value) number down to the nearest integer INTERCEPT(known_y's,known_x's) INDIRECT INT INTERCEPT Statisti Calculates cal the point at which a line will intersect the x-axis by using existing x and y Page 96 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n values IPMT Financi Calculates IPMT(rate,period,number_of_periods,present_value,futur al the interest e_value,type) payment for a given period for an investment based on periodic, constant payments and a constant interest rate Financi Calculates IRR(values,guess) al the internal rate of return for a series of cash flows Info Tests ISBLANK(cell) whether a cell is blank Tests ISERR(cell) whether a cell contains an error value (except for #N/A) Tests whether a cell contains any error value Tests whether a number is even Tests ISERROR(cell) Function Type Use (Arguments) IRR ISBLANK ISERR Info ISERROR Info ISEVEN Info ISEVEN(number) ISLOGICAL Info ISLOGICAL(cell) Page 97 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n whether a cell contains a logical value ISNA Info Tests whether a cell contains the #N/A error value ISNA(cell) Function Type Use (Arguments) ISNONTEXT Info Tests ISNONTEXT(cell) whether a cell contains an item that is not text Tests whether a cell contains a number Tests whether a number is odd Tests whether a cell contains a reference Tests whether a cell contains text ISNUMBER(cell) ISNUMBER Info ISODD Info ISODD(number) ISREF Info ISREF(cell) ISTEXT Info ISTEXT(cell) KURT Statisti Returns the KURT(number1,number2,number3...) cal kurtosis of a data set Statisti Returns the LARGE(range,k) cal k-th largest value in a LARGE Page 98 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n range of cells LCM Math Returns the LCM(number1,number2,number3...) least common multiple of integers Returns the LEFT(text,number_of_characters) first character(s ) in a text string, based on the number of characters you specify Returns the LEN(text) number of characters in a text string LINEST(known_y's,known_x's,constant,stats) Function Type Use (Arguments) LEFT Text LEN Text LINEST Statisti Calculates cal a straight line that best fits given data using the "least squares" method, returning an array that describes the line Math LN Returns the LN(number) natural logarithm of a number Returns the LOG(number,base) logarithm of a number to the base LOG Math Page 99 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n you specify LOG10 Math Returns the LOG10(number) base-10 logarithm of a number Function Type Use (Arguments) LOGEST Statisti In LOGEST(known_y's,known_x's,constant,stats) cal regression analysis, calculates an exponential curve that fits the given data, then returns an array that describes the curve Statisti Returns the LOGINV(probability,mean,standard_deviation) cal inverse of the lognormal cumulative distribution function of x LOGINV LOGNORMDI Statisti Returns the LOGNORMDIST(x,mean,standard_deviation) ST cal cumulative lognormal distribution of x LOWER Text Converts all LOWER(text) characters in a text string to lowercase MAX Statisti Returns the MAX(range) cal largest numeric value in a range of cells Page 100 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Function MAXA Type Use (Arguments) Statisti Returns the MAXA(range) cal largest value in a range of cells; can include numbers, text, or logical values Text Returns the MATCH(lookup_value,lookup_range,match_type) relative position of an item in a range that matches a specified value in a specified order Returns the MDETERM(array) matrix determinan t of an array MATCH MDETERM Math MDURATION Financi Returns the MDURATION(settlement,maturity,coupon,yield,frequency, al modified basis) Macauley duration for a security with an assumed par value of $100 MEDIAN Statisti Returns the MEDIAN(range) or MEDIAN(number1,number2,number3...) cal median of a range of numbers or cells Text Returns a MID(text,start_number,number_of_characters) specific number of characters from a text string, starting at MID Page 101 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n a specified position MIN Statisti Returns the MIN(range) cal minimum numeric value in a range of cells Statisti Returns the MINA(range) cal minimum value in a range of cells; can include numbers, text, or logical values Math Returns the MINVERSE(array) inverse matrix for the matrix stored in an array Function Type Use (Arguments) MINA MINVERSE MIRR Financi Calculates MIRR(values,finance_rate,reinvest_rate) al the modified internal rate of return for a series of periodic cash flows Math Returns the MMULT(array1,array2) matrix product of two arrays Returns the MOD(number,divisor) modulus of a divisor and a dividendtha t is, the remainder MMULT MOD Math Page 102 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n after a number is divided by the divisor MODE Statisti Returns the MODE(range) or MODE(number1,number2,number3...) cal most frequently occurring value in a range of data Date Returns the MONTH(serial_number) month of a date represente d by a given serial number Returns a MROUND(number,multiple) number rounded to the desired multiple Returns the MULTINOMIAL(number1,number2,number3...) ratio of the factorial of a sum of values to the product of the factorials Returns a value converted into a number N(value) Function Type Use (Arguments) MONTH MROUND Math MULTINOMI AL Math N Info NA Info Returns the NA() error value #N/A (which means "no value is available") Page 103 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Function Type Use (Arguments) NEGBINOMDIST(number_f,number_s,probability_s) NEGBINOMD Statisti Calculates IST cal the negative binomial distribution NETWORKD AYS Date Returns the NETWORKDAYS(start_date,end_date,holidays) number of whole working days between a start and an end date (working days exclude weekends and holidays) NOMINAL Financi Calculates NOMINAL(effective_rate,periods_per_year) al the nominal annual interest rate, given the effective rate and the number of compoundin g periods per year Statisti Returns the NORMDIST(x,mean,standard_deviation,cumulative) cal normal distribution for the specified mean and standard deviation Statisti Returns the NORMINV(probability,mean,standard_deviation) cal inverse of the normal cumulative distribution for the NORMDIST NORMINV Page 104 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n specified mean and standard deviation NORMSDIST Statisti Returns the NORMSDIST(z) cal standard normal cumulative distribution NORMSINV Statisti Returns the NORMSINV(probability) cal inverse of the standard normal cumulative distribution Logical Reverses the value of its argument Time NOT(value) Function Type Use (Arguments) NOT NOW Returns the NOW() serial number of the current date and time, using your PC's built-in clock NPER Financi Calculates NPER(rate,payment,present_value,future_value,type) al the number of payments required to pay off a loan at a given interest rate Financi Calculates NPV(rate,value1,value2...) al the net present value of an investment, NPV Page 105 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n using a discount rate and a series of future payments (negative values) and income (positive values) ODD Math Returns the ODD(number) number rounded up to the nearest odd integer OFFSET(reference,rows,columns,height,width) Function Type Use (Arguments) OFFSET Lookup Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells OR Logical Returns OR(condition1,condition2,condition3...) TRUE if any argument is true; returns FALSE if all arguments are false Statisti Returns the PEARSON(range1,range2) cal Pearson product moment correlation coefficient PEARSON PERCENTILE Statisti Returns the PERCENTILE(range,k) cal k-th percentile of values in Page 106 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n a range PERCENTRA NK Statisti Returns the PERCENTRANK(range,x,significance) cal rank of a value in a data set as a percentage of the data set Statisti Calculates PERMUT(number,number_chosen) cal the number of permutatio ns for a given number of objects that can be selected from number objects Math Used in the PI() place of the value pi Function Type Use (Arguments) PERMUT PI PMT Financi Calculates PMT(rate,number_of_periods,present_value,future_value al the ,type) payment amount required for an investment to bepaid off given a specific term and interest rate Statisti Returns the POISSON(x,mean,cumulative) cal Poisson distribution (to predict the number of events over a specific POISSON Page 107 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n time) POWER Math Returns the POWER(number,power) result of a number raised to a given poser Function Type Use (Arguments) PPMT Financi Calculates PPMT(rate,period,number_of_periods,present_value,futur al the amount e_value,type) of principal being paid during any payment period Financi Returns the PRICE(settlement,maturity,rate,yield,redemption,frequen al price per cy,basis) $100 face value of a security that pays periodic interest Financi Returns the PRICEDISC(settlement,maturity,discount,redemption,basi al price per s) $100 face value of a discounted security Financi Returns the PRICEMAT(settlement,maturity,issue,rate,yield,basis) al price per $100 face value of a security that pays interest at maturity Statisti Returns the PROB(x_range,prob_range,lower_limit,upper_limit) cal probability that values within a range are between two limits Math Multiplies PRODUCT(number1,number2,number3...) or PRICE PRICEDISC PRICEMAT PROB PRODUCT Page 108 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n all the numbers in a given range or argument PROPER Text Function Type Use (Arguments) PRODUCT(range) Capitalizes PROPER(text) 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 PV Financi Calculates PV(rate,number_of_payments,payment,future_value,typ al the present e) value of an investment Statisti Calculates QUARTILE(range,quart) cal the quartile of a data set Math Returns the QUOTIENT(numerator,denominator) integer portion of a division, discarding the remainder Converts degrees to radians RADIANS(angle) QUARTILE QUOTIENT RADIANS Math RAND Math Returns an RAND() evenly distributed random real numbergrea ter than or Page 109 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n equal to zero and less than 1 RANDBETWE Math EN Returns a RANDBETWEEN(lower_number,higher_number) random integer number between two numbers you specify Function Type Use (Arguments) RANK Statisti Returns the RANK(number,ref,order) cal rank of a number in a range or list of numbers Financi Calculates RATE(number_of_periods,payment,present_value,future_ al the interest value,type,guess) rate per period of an annuity Financi Calculates RECEIVED(settlement,maturity,investment,discount,basis al the amount ) received at maturity for a fully invested security Text Replaces part of a text string with another text string Repeats text a given number of times REPLACE(old_text,start_number,number_of_characters,n ew_text) RATE RECEIVED REPLACE REPT Text REPT(text,number_of_times) RIGHT Text Returns the RIGHT(text,number_of_characters) last character(s Page 110 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n ) in a string ROUND Math Rounds a number to a specified number of digits Rounds down a number to a specified number of digits Rounds up a number to a specified number of digits ROUND(number,number_of_digits) Function Type Use (Arguments) ROUNDDOW Math N ROUNDDOWN(number,number_of_digits) ROUNDUP Math ROUNDUP(number,number_of_digits) ROW Lookup Returns the ROW(cell) row number of a cell or range of cells Lookup Returns the ROW(range) number of rows in a range Statisti Returns the RSQ(known_y's,known_x's) cal square of the Pearson product moment correlation coefficient Text Locates SEARCH(find_text,within_text,start_number) one text string within a second text string, and returns the starting ROWS RSQ SEARCH Page 111 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n point of the inside text string SERIESSUM Math Returns the SERIESSUM(x,n,m,coefficients) sum of a power series Determines SIGN(number) the sign (positive or negative) of a number Returns the SINE(number) sine of a given number or angle Returns the SINH(number) hyperbolic sine of a number or angle Function Type Use (Arguments) SIGN Math SIN Math SINH Math SKEW Statisti Returns the SKEW(number1,number2,number3...) cal skewness of a distribution Financi Calculates SLN(cost,salvage,life) al depreciatio n using the straight line method Statisti Returns the SLOPE(known_y's,known_x's) cal slope of the linear regression line through known x and y data points Statisti Returns the SMALL(range,k) cal k-th SLN SLOPE SMALL Page 112 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n smallest value in a data set SQRT Math Returns a SQRT(number) positive square root Returns the SQRTPI(number) square root of (number * pi) Function Type Use (Arguments) SQRTPI Math STANDARDI ZE Statisti Returns a STANDARDIZE(x,mean,standard_deviation) cal normalized value from a distribution characteriz ed by mean and standard deviation Statisti Estimates cal standard deviation based on a numerical sample STDEV(range) STDEV STDEVA Statisti Estimates STDEVA(range) cal standard deviation; can include numbers, text, or logical values Statisti Calculates cal standard deviation based on the entire population given as arguments Statisti Calculates STDEVP(range) STDEVP STDEVPA STDEVPA(range) Page 113 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n standard deviation based on the entire population given as arguments, including text and logical values Function Type cal Use (Arguments) STEYX Statisti Returns the STEYX(known_y's,known_x's) cal standard error of the predicted y value for each x in the regression Text Substitutes SUBSTITUTE(text,old_text,new_text,instance_number) new text for old text in a text string Calculates SUM(range) the total of a group of cells Calculates SUMIF(range,criteria,sum_range) the total of a group of cells defined by a given criteria Multiplies SUMPRODUCT(array1,array2,array3...) correspondi ng component s in the given arrays, and returns the sum of those products SUBSTITUT E SUM Math SUMIF Math SUMPRODU CT Math Page 114 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Function SUMSQ Type Math Use (Arguments) Returns the SUMSQ(number1,number2,number3...) sum of the squares of a series of numbers Returns the SUMX2MY2(array_x,array_y) sum of the difference of squares of correspondi ng values in two arrays Returns the SUMX2PY2(array_x,array_y) sum of the squares of correspondi ng values in two arrays Returns the SUMXMY2(array_x,array_y) sum of squares of differences of correspondi ng values in two arrays SUMX2MY2 Math SUMX2PY2 Math SUMXMY2 Math SYD Financi Calculates SYD(cost,salvage,life,period) al depreciatio n using the sum of the years' digits method Text Returns the T(value) text referred to by value Returns the TAN(number) tangent of a number T TAN Math Page 115 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Function TANH Type Math Use (Arguments) Returns the TANH(number) hyperbolic tangent of a number TBILLEQ Financi Calculates TBILLEQ(settlement,maturity,discount) al the bond-equiv alent yield for a Treasury bill Financi Calculates al the price per $100 face value for a Treasury bill Financi Calculates al the yield for a Treasury bill Text Converts a value to text in a specific number format TBILLPRICE(settlement,maturity,discount) TBILLPRICE TBILLYIELD TBILLYIELD(settlement,maturity,price) TEXT TEXT(value,format_text) TODAY Date Returns the TODAY() serial number of the current date TRANSPOSE Lookup Flips a TRANSPOSE(range) vertical range of cells horizontally , or vice versa TREND Statisti Returns cal values TREND(known_y's,known_x's,new_x's,constant) Page 116 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n along a linear trend TRIM Text Removes all TRIM(text) spaces from a text string except for single spaces between words Function Type Use (Arguments) TRIMMEAN Statisti Returns the TRIMMEAN(range,percent) cal mean of the interior of a data set, excluding a percentage of the data points from the top and bottom tails of the set Math Truncates TRUNC(number,number_of_digits) 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 Converts text to all uppercase UPPER(text) TRUNC UPPER Text Page 117 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n Converts a text string that represents a number to that number Function VALUE Type Text Use (Arguments) VALUE(text) VAR Statisti Estimates cal variance based on a numeric sample VAR(range) VARA Statisti Estimates VARA(range) cal variance based on a sample; can include numbers, text, and logical values Statisti Calculates cal variance based on the total numeric population Statisti Calculates cal variance based on the entire population, including numeric, text, and logical values VARP(range) VARP VARPA VARPA(range) VLOOKUP Lookup Searches VLOOKUP(lookup_value,range,column_index_number,rang for a value e_lookup) in the first column of a range, and then returns a value in the same row Page 118 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n from another specified column WEEKDAY Date Returns the WEEKDAY(serial_number,return_type) day of the week that correspond s to any valid date Function Type Use (Arguments) WEIBULL Statisti Returns the WEIBULL(x,alpha,beta,cumulative) cal Weibull distribution Date Returns a WORKDAY(start_date,days,holidays) number that represents a date that is the indicated number of working days before or after a start date WORKDAY XIRR Financi Calculates XIRR(values,dates,guess) al the internal rate of return for a schedule of cash flows that is not necessarily periodic Financi Calculates XNPV(rate,values,dates) al the net present value for a schedule of cash flows that is not necessarily periodic Date Returns the YEAR(serial_number) XNPV YEAR Page 119 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html Table 10. Google Spreadsheet Functions Descriptio n year correspondi ng to a specified date serial number YEARFRAC Date Calculates YEARFRAC(start_date,end_date,basis) the fraction of a year represente d by the number of whole days between two dates Function Type Use (Arguments) YIELD Financi Calculates YIELD(settlement,maturity,rate,price,redemption,frequen al the yield on cy,basis) a security that pays back periodic interest Financi Calculates al the annual yield for a discounted security YIELDDISC(settlement,maturity,price,redemption,basis) YIELDDISC ZTEST Statisti Returns the ZTEST(range,value_to_test,sigma) cal one-tailed probability value of a z-test Page 120 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 121 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 122 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 123 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 124 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull 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. Page 125 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 126 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull 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 f ull size image] Page 127 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 128 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 129 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull 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. Page 130 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 f ull 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. Page 131 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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. Page 132 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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! Page 133 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html 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 File button Save button New link Open link Description Lets you save, open, import, and export spreadsheet files Saves the current spreadsheet Opens a new spreadsheet in a new browser window Opens a previously saved spreadsheet, or uploads an XLS spreadsheet file stored on your computer Displays the title of the current spreadsheet, as well as when it was last saved (visible only when using a saved spreadsheet) Spreadsheet title and info Share This Spreadsheet Saves the current spreadsheet and displays sharing options link (visible only on newly createdthat is, unsavedspreadsheets) Show/Hide Sharing Options link Format tab Sort tab Formulas tab Cut button Lets you invite other users to share or view the current spreadsheet (visible only when using a saved spreadsheet) Displays formatting controls Displays sorting controls Displays formula controls Cuts the data in the selected cell(s)use when you want to move data to another location Copies the data in the selected cell(s) Pastes cut or copied data Undoes the most recent entry, edit, or operation Redoes an undone operation Adds a new sheet to the current spreadsheet Click to select different sheets within the current spreadsheet Copy button Paste button Undo button Redo button Add Sheet button Sheet 1, Sheet 2, etc. Page 134 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html All tab 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) Bolds the contents of the selected cell(s) Italicizes the contents of the selected cell(s) Underlines the contents of the selected cell(s) Applies a particular font (typeface) to the selected cell(s) Changes the font size of the selected cell(s) Changes the color of the contents of the selected cell(s) Changes the background color of the selected cell(s) Clears all formatting from the selected cell(s) Changes the alignment (left, center, right, etc.) of the selected cell(s) Inserts new rows or columns into the spreadsheet Deletes rows or columns from the spreadsheet When checked, wraps long text to additional lines within the selected cell(s) Merges adjacent cells into a single cell Bold Italic Underline Font Family Font Size Text Color Background Color Clear Format Align button Insert button Delete button Wrap Text Merge Across Sort Tab Freeze Rows When sorting, freezes the top row(s) of the spreadsheet as header rows Sort Sheet by Selected Sorts the spreadsheet by the currently selected column, in Column: A > Z alphabetical (or numerical) order Sort Sheet by Selected Sorts the spreadsheet by the currently selected column, in Column: Z > Z reverse alphabetical (or numerical) order Formulas Tab Page 135 ABC Amber CHM Converter Trial version, http://www.processtext.com/abcchm.html All tab Reference area Sum Count Average Min Max Product Displays the name of the selected cell Calculates the total of a group of cells Counts the number of cells in a range that contain numeric values Calculates the mean average of a group of numbers Returns the minimum value in a range of cells Returns the maximum value in a range of cells Calculates the product of the specified valuesthat is, it multiplies all the values together Displays all available Google Spreadsheets functions More>> Page 136