Crunching Numbers with Google Spreadsheet

The spreadsheet was one of the very first PC computer applications. More than 25 years ago, the first PC programmers thought that one of the most important programs they could write would be a spreadsheet. They are just as important today, and considerable functionality has been added to them since then.

To create a new spreadsheet, click the New Spreadsheet link from the Google Docs main page (http://docs.google.com). This launches a blank spreadsheet as shown in Figure 18.6. By default, you are placed in the first sheet with the Edit tab selected. You can begin adding information to the empty cells. Each spreadsheet can have more than one sheet. To add a new sheet to an existing spreadsheet, click Add Sheet in the lower-left corner of the page.

Figure 18.6: Keeping your spreadsheets on Google lets you maintain them remotely.

Change the order of the sheets in your spreadsheet document by first clicking on the sheet you want to move, and then selecting Move Right or Move Left. The popup menu allows you to rename or delete individual sheets.

In addition to creating spreadsheets on the Google Docs site, you can also import spreadsheets in Excel (XLS), Comma Separated Value (CSV), and OpenOffice (ODS) formats.

Typing alpha (nonnumeric) text in the cell causes it to automatically align to the left of the cell. Numeric data is automatically aligned to the right. While both alpha text and numeric data can be sorted, only numeric data can be used in formulas, such as adding columns of numbers.

Using the Spreadsheet toolbar

The spreadsheet editor, like the document editor, has a toolbar along the top of the sheet as shown in Figure 18.7. The items in the toolbar, from left to right, are:

• Undo last edit: Restores your last edit to its original state

• Redo last edit: Restores an edit that was previously undone with Undo last edit

• Cut: Removes selected text or image and copies it to the clipboard

• Copy: Copies selected text or images to the clipboard

• Paste: Places clipboard contents into the document at the cursor

• Format: Formats the way numbers and dates in the cell are displayed

• Bold: Causes new or selected text to appear in Bold

• Italics: Causes new or selected text to be formatted as italics

• Underline: Causes new or selected text to appear underlined

• Font change: Allows you to change the font face of new or selected text

• Font size change: Allows you to change the font size of new or selected text.

• Font color change: Changes the color of new or selected text

• Background Color: Changes the background color of new or selected text

• Borders: Customizes cell and spreadsheet borders

• Remove formatting: Discards previous current formatting

• Align: Selects a horizontal and vertical alignment for information within a cell

• Insert: Inserts rows and columns

• Delete: Deletes a row of cells, a column of cells, or clears a selection without deleting the cells

• Wrap text: Selects whether to wrap text within a cell

• Merge Across: Merges data between sheets

Figure 18.7: Use the toolbar items to easily edit your spreadsheet.

Sorting

You can sort data in your sheet by selecting the columns of data (you can sort columns of information, not rows) and clicking the Sort tab. Choose whether you want data sorted from lowest to highest (ascending) (A Z) or highest to lowest (descending) (Z A) by clicking the corresponding button in the toolbar. Numeric data is sorted in ascending or descending order also be clicking these same buttons. Determine which column is used to sort the data by clicking on any cell in that column. You will see the Sort sheet by Column message along the top of the sheet change as you click in different columns.

To identify and freeze column headers so they are not sorted along with the data, click the Freeze header row drop-down list and select how many rows of data to freeze. You can choose from 0 rows to 5.

Using formulas

Like other spreadsheet programs, the Google Docs spreadsheet enables you to enter formulas that perform functions on your data. The types of functions include:

• Math: Perform math, trigonometry, and important functions like rounding, creating a random number, and more.

• Financial: Perform financial functions such as interest calculations and many more

• Logical: Perform Boolean logic calculations using AND, OR, IF, NOT, OR, and TRUE.

• Date: Perform date arithmetic and access your computer or network clock for current time and date.

• Lookup: Perform lookup functions on lists of textual data.

• Statistical: Perform the magic of statistics using these functions.

• Text: Manipulate text strings using these many text functions.

• Info: Get information about your data and calculations using these functions, most of which return a logical TRUE or FALSE.

Each formula type accepts a particular type of data. For example, the Info formula (ISEVEN()) can be entered into a cell preceded by the equal sign and with a value or cell address entered in the parentheses as a parameter like =ISEVEN(7). The value included in the parens is evaluated to determine whether it is even. The result is TRUE or FALSE. This, of course, displays the word FALSE in the cell. This is not a text entry, but a Boolean entry that can be used in Boolean calculations using the Logical formulas.

Tip 

Use the Info formula ERROR.TYPE to determine what error has occurred and display an error message.

The Text formulas operate on textual data. For example, when cell D5 contains the text “My country tis of thee” using the LEFT() text formula, entering the cell address and the number of characters from the left to extract =LEFT(D5,2) returns “My.”

Click the More link to see a list of all the formulas. Selecting a formula from the Insert a Function dialog box inserts the function into the cell identified by the mouse cursor and displays the syntax of the formula in the bottom of the dialog box. For help on that formula or all formulas, click the more link at the bottom of the dialog box. This launches a page with a complete description of all the supported formulas.

Saving your spreadsheet

Like documents, spreadsheets are automatically saved on a regular basis. The Save button in the upper right only becomes active when an auto-save has not already taken place. Otherwise, the button is disabled and reads Automatically Saved.

To close the spreadsheet and save it at the same time, click Save & close in the upper-right corner.

Exporting to your local computer

Spreadsheets can be saved to your local hard drive by exporting the data you’re your files in Google Docs. Export them as CSV, HTML, ODS, PDF, or XLS files. You can then use applications on your computer, such as Excel, Acrobat reader, or your Web browser to read or edit the files depending on the file type you’ve selected.

Collaborating and discussing

Invite collaborators or viewers of your spreadsheet just as described in the document section. Click the Collaborate tab (see Figure 18.8) and type the e-mail addresses of new collaborators and viewers, or select existing people from the list. Click Invite these people to launch a dialog box allowing you to personalize the e-mail that you send to your invitees.

Figure 18.8: Collaborate with others while building your spreadsheet.

You can see a list of collaborators at the bottom of the Collaborate tab. Click the Turn off link to keep your invited collaborators from inviting other collaborators.

Chatting with collaborators

Chat in real time with others who edit your spreadsheet. When collaborators log in to their Google Docs account and edit your document, you see them in the list displayed in the Discuss tab. Chat with them by typing messages in the text box at the bottom of the tab and press Enter to send the message. Your conversation appears in the chat box. You can chat with several people editing the spreadsheet simultaneously.

Publishing your spreadsheet

Like documents, you can publish your spreadsheet to the Web for everyone to view. The terminology and the way you do it are slightly different, but the result is exactly the same. At the bottom of the Collaboration tab is a “Let anyone view” checkbox. Selecting this checkbox causes Google Docs to publish this sheet to the Web, giving you the URL right next to the checkbox.