Software

Number cruncher

If you carry a laptop around just so you can show clients your latest marketing forecasts or call up sales figures at company meetings, you can save yourself some bother by using Google Spreadsheets instead. The program provides editable spreadsheets that are compatible with Microsoft Excel—and, best of all, you and your colleagues can modify them simultaneously.

Using spreadsheets

When you open a new document in Spreadsheets, you see a standard spreadsheet grid with resizable rows and columns, and the usual array of options for formatting. You enter text or numbers in spreadsheet cells, as you would with any other spreadsheet application. Using icons and menus on the toolbar, you can merge cells; apply borders and highlighting; change fonts, font sizes, and styles; and choose whether to wrap text within a cell. You may miss some of Excel’s more-advanced features, but for basic data formatting and calculations, the necessary features are there.

Formulas and More You can enter Excel-style formulas manually, or click on the Formulas tab to see links that quickly insert Sum, Count, Average, Min, Max, and Product formulas. For more options, click on the More link to display a pop-up list of hundreds of formulas (see “Hundreds of Formulas”). Hover over a formula name to see its syntax, or click to insert it in the active cell. Spreadsheets lacks advanced Excel features such as macros, PivotTables, auditing, data filtering, and validation.

Creating Charts If you need to create a quick visual representation of your data, such as a pie chart or a bar graph, Spreadsheets can help. Begin by selecting the range of cells you want to chart, and then click on the pie-chart icon in the toolbar. Select the type of chart you want to make, and supply any optional data you choose—such as a title, or labels for horizontal and vertical axes. Click on Save Chart. The chart will appear right on top of your spreadsheet; you can move it or resize it by clicking and dragging. Note that although your spreadsheet can include multiple charts, a Spreadsheets chart cannot represent noncontiguous ranges of cells.

Importing and Exporting You can upload Excel spreadsheets (.xls), comma-separated text files (.csv), and OpenDocument spreadsheets (.ods) in one of two ways (and as with Docs, there is a 500K size limit).

The first method is to go to the Docs & Spreadsheets home page, click on the Upload link, and follow the instructions for uploading a file, just as you would for a text document.

Alternatively, when you’re viewing an existing Spreadsheets file, you can choose Import from the File pop-up menu, click on Browse, locate the file on your hard drive, click on Open, and then click on the Open Now link. At press time, importing via e-mail was not available. Also, unlike Docs, Spreadsheets currently has no option that lets you e-mail a finished document directly from the program.

Working with Multiple Sheets As in Excel, each document can have multiple sheets. To create a second sheet, click on the Add Sheet button at the bottom of the document window. You can rename or rearrange the current sheet by clicking on its name and choosing Rename, Move Left, or Move Right from the pop-up menu. You can also cut and paste cells from one sheet to another. For best results, use the toolbar’s Cut, Copy, and Paste icons.

Tracking Revisions The Spreadsheets program automatically saves your work after almost every change, as well as when you click on the Save & Close button. To see any previous version of a file, click on the Revisions tab and select the version you want from the Revision pop-up menu. Although Spreadsheets tracks revisions, it doesn’t highlight specific changes in each version, as Excel’s Track Changes feature does, nor does it let you compare two versions, as Docs does.

Collaborating Sometimes you need input from several people—for instance, forecasts from the sales department, history from accounting, and strategy from project management. Spreadsheets makes it easy for multiple people to view and edit the same spreadsheet simultaneously. As with Docs, you can publish your spreadsheet to a Web page where anyone can see it. You can also export your spreadsheet in a variety of file formats, and invite other people to collaborate on it with you.

Use the form on the Collaborate tab to invite other people to edit or view a spreadsheet. To make the spreadsheet available to anyone with a Google account, click on Allow Anyone To View. The site produces a URL that you can send to anyone you want. To publish your spreadsheet for anyone to view without logging in, click on the Publish tab and then on Publish Now.

Have a quick question about a spreadsheet change? Because Google’s chat program, Google Talk, is integrated with Spreadsheets, you can have a live chat with every collaborator who’s currently viewing or editing the same spreadsheet (see “Spreadsheet Chat”). Click on the Discuss tab to see a list of available collaborators. Type your message into the field at the bottom, and a message pops up on the other users’ screens, asking them to join the chat.

Spreadsheets tips

Because Spreadsheets shares many interface elements with Excel, most people should be able to pick up the basics without ever looking at the online help documentation. But a few interesting features may not be evi-dent at first glance.

Look Up Data on the Web One thing Spreadsheets offers that Excel doesn’t is a pair of special functions that rely on Google’s extensive index of Web-based information. Access these functions by clicking on the Formulas tab, clicking on the More link, and then choosing Google in the Insert A Function box.

The first function, GoogleFinance, gives you an easy way to insert dynamic information about publicly traded companies and mutual funds. For example, you can enter

=GoogleFinance("AAPL", "marketcap")
in a cell to display Apple’s current market capitalization. You can access stock prices, mutual-fund net asset values, and many other types of data with this function.

The second function, GoogleLookup, lets you insert a wide variety of statistical data that can be inferred from Web searches. This formula takes two arguments—an entity (what you want information about) and an attribute (the information you want about the entity). Entering

=GoogleLookup("France", "population")
in a spreadsheet cell, for example, displays the current population of France (a little over 63,000,000). Entering
=GoogleLookup("Halle Berry", "place of birth")
gives a result of Cleveland, Oh, and typing
=GoogleLookup("platinum", "atomic weight")
displays
195.084(9) g•mol
. GoogleLookup can’t display every piece of information you may want, but it can make entering some data much easier.

Fill In a Range of Values Spreadsheets doesn’t have a feature comparable to Excel’s AutoFill, which intelli-gently extends a series into new rows or columns. But it does have a limited fill feature for columns. Start by entering a formula that refers to the cell immediately above it. For example, suppose cell D1 contains the value 18 . Enter a formula—for instance,

=D1+1
—in cell D2. Then select (using the mouse or keyboard) cell D2 (but not D1) and a few cells below it. Press control-D, and Spreadsheets applies the formula you entered to all the selected cells—if you entered
=D1+1
, cells D3 through D5 would contain 20, 21, and 22.

Put Part of a Spreadsheet on a Web Page Sometimes you may want to embed just a portion of a spreadsheet in a Web page (or in, say, a blog entry), rather than providing a link to the whole file. To do this, go to the Publish tab and click on Publish Now. Next, click on the More Publishing Options link, which opens a new window. From the File Format pop-up menu, choose HTML To Embed In A Webpage.

Select the sheet containing the cells you want to publish, and enter a range of cells on that sheet (such as

A1:D5
). Click on Generate URL, and copy the code that appears in the box. Paste this code into your blog or Web page to display the selected portion of your spreadsheet there. (If you select the Publish tab’s Automatically Update Every 5 Minutes option, the spreadsheet copy on your blog or Web page will dynamically reflect changes made to your spreadsheet, without manual intervention.)

Is it for you?

Google Spreadsheets is a good choice for maintaining everything from shopping lists to product-launch plans, especially if you want several people to be able to view or modify them. And even though it lacks some of Excel’s flashier features, Spreadsheets can crunch numbers—including highly complex formulas—with the best of them. For traditional spreadsheet tasks such as tracking sales figures or account balances, Spreadsheets is a perfectly capable application. It’s also a good option for people who want basic spreadsheet capabilities but can’t afford Excel, or who often need to do quick calculations when they’re away from their regular computers—while traveling, for example.

Just like Docs, however, Spreadsheets is useless when you’re not connected to the Internet. And if you need high-powered features, you’ll have to turn to Excel or another full-featured spreadsheet application.

Hundreds of Formulas: Google Spreadsheets’ formulas are available through this pop-up window, which appears when you click on the More link. Hover over a formula to see its syntax, or click to insert it in your spreadsheet.Spreadsheet Chat: Multiple people can not only edit a Google Spreadsheets document simultaneously, but also carry on a live chat while doing so. All collaborators who are currently viewing the spreadsheet appear in the Discuss tab.

Subscribe to the Best of Macworld Newsletter

Comments