Hands on: Google Spreadsheets is more powerful than you think

June 9, 2006 (Computerworld) Google Spreadsheets is a free, Web-based spreadsheet tool from Google that does a surprisingly good job as a basic number cruncher, and the price is right. Google began accepting sign-ups to participate in a limited public beta test earlier this week, so you can sign up and try it yourself.

I didn't expect to see high-end spreadsheet features. There are no PivotTables, there's no charting, and apart from sorting, you can't do much with text. But there's a lot more functionality than you might expect. Google Spreadsheets' user interface, functions support, collaboration features and performance are all strengths.

Where the files are

Files you create or import with Google Spreadsheets are automatically saved by default to Google's server, though it's easy to delete them there. Data files are not encrypted, though, which should give at least some users pause. Google offers encryption with some of its other user-data services, so perhaps this capability will be added later. But even if Google does eventually offer worksheet encryption, any notion of saving sensitive data to some other company's server via the Internet is rarely a smart move. (This problem might potentially be offset if Google were to offer an enterprise product that ran on corporate intranets.)

You can use Google Spreadsheets to work with your pre-existing worksheets (either native Microsoft Office Excel .xls or comma-separated-value .csv files) by uploading them to Google Spreadsheets. You can also save a Google Spreadsheets worksheet to your hard drive and convert it to an .xls or .csv file. Be prepared to lose some formatting, such as row height and column width, when saving to .xls format. You can also create an HTML file of your worksheet as a read-only file.

 

Google Spreadsheets at work from the Formats tab.  (Click image to see larger view)

Every time you make a change, the Google server automatically updates the worksheet. While I experienced no serious lag, and you do have access to undo and redo buttons throughout the interface, this constant saving means you can't make a series of changes and then save the file in a particular state. Every time you make a change, your spreadsheet is saved, so "Wait, wait, wait, I want to go back to the last good version!" is not an option.

Regular use of Google Spreadsheets might tend to make spreadsheets smaller. By default, new worksheets are sized at 100 rows by 20 columns, and inserting large numbers of rows or columns is cumbersome. Although Google Spreadsheets can import existing worksheets with more rows, there are limits. You can work with up to 100 spreadsheets in all, each with up to 20 worksheet tabs, 50,000 cells, 256 columns or 10,000 rows, according to Google. If any worksheet exceeds any of these conditions, you're prevented from adding data to the spreadsheet. Your .xls and .csv files cannot exceed 400KB each.

The Format tab

Google Spreadsheets' interface is clean and simple. Standard drop-down menus, icons and buttons handle many common chores. Buttons control formatting choices, such as toggling between two or no decimal points, applying dollar or percent signs, aligning text and inserting rows and columns.

The main interface is divided into three tabs, Format, Sorting and Formulas. The Format tab is where you'll probably spend most of your time. I like the feedback and naming conventions given to user interface elements, particularly on the Format tab. For instance, when you select a cell range and press the Delete menu button, the online spreadsheet tool presents you with user-friendly, plain-English options that offer feedback about the range you selected, such as "Delete Rows 1-4," "Delete Columns G-H" or "Clear Selection." Likewise, select two cells and click the "Merge Across" button to merge the cells, or choose a merged range and click "Break Apart" to do the opposite, just as the button says. There are no cryptic icons to decipher.

Formatting is simple, direct and fast. You can format cells in any of seven font faces and 11 font sizes; make them bold, italic, and underlined; and wrap text within a cell. You can choose among 40 text and background colors, although there's no provision similar to Microsoft Office Excel's custom colors.

Google Spreadsheets provides good feedback in many places. When you select a range, the program initially turns the background of that cell range blue. When you perform an operation on the range, such as Copy, it temporarily changes the background to light red to let you know it recognized and is performing your command.

Choosing a font size from a drop-down menu.

There are some disappointing omissions. For instance, you can change cell width and height by dragging cell borders, but you can't double-click on a row/column border to automatically adjust the dimension to the widest or tallest entry in the selected row or column. You also can't control the number of decimal points uniformly in a worksheet. Google Spreadsheets lets you format numbers in these settings: showing all digits to the right of the decimal point, showing two digits, or not showing the decimal point.

The Sorting tab

The second tab, Sort, does precisely what you'd expect. To sort a range of cells, highlight them, click the appropriate button, and you're done.

The Sort feature lets you freeze up to five rows. You might do this to keep row headings in view as you scroll or to exclude them from being included in a sort operation when you select entire rows. A drawback of Google Spreadsheets is that you can't freeze columns in a similar way.

Sorting in ascending or descending order is possible only on a single column; you can't sort on Column A and within that by Column B. Likewise, you can't sort by a custom order, such as a custom list of region or department names, as you can in Excel.

Nevertheless, for everyday list management, Google Spreadsheets' sorting feature is perfectly serviceable.

The Formulas tab

You'll find all the commands necessary to perform calculations on the Formulas tab. There are simple shortcuts for frequently used functions. For example, you can select a range of cells and click the Sum text link to direct Google Spreadsheets to insert a SUM-based formula in the next row of that range. The online spreadsheet tool also offers shortcuts for Count, Average, Min, Max and Product.

Most Excel functions are supported. To insert a function, you click the More text link to open a pop-up list of functions organized by type. The list of compatible functions is extensive (I counted 233), but that number is also deceptive because it's incomplete. For example, Excel has an "hour" function to extract the hour from a time field. Even though Google Spreadsheets doesn't list that function, it supports it. Likewise the NOW function isn't listed but it also works. If you're casually evaluating Google Spreadsheets, you might make the mistake of assuming it's less powerful than it is.

The biggest problem with composing formulas is that there's no help available for the online spreadsheet's functions. When, for example, you choose NPV from the function list to compute net present value, you're on your own on figuring out what the arguments should be. Google Spreadsheets inserts =NPV(args), where "args" stands for arguments. But what are your options? It's not immediately obvious. My advice: Keep a list of function parameters handy or a good Excel book. Google Spreadsheets in this first beta lacks online help about its functions. Hopefully that will be remedied in later releases.

Google Spreadsheets offers more than 233 functions. (Click image to see larger view)

The program also doesn't suggest cell ranges when it might. When you click the Sigma toolbar button in Excel, it does a good job of figuring out what cell range you want to total. Google Spreadsheets lacks anything like that ability. You have to select the range you want to add up first, which defeats the purpose. Moreover, when building a SUM formula, it's handy to use the arrow keys to select cells and populate the formula as you go. Google Spreadsheets doesn't support that, either; you'll have to enter all cell addresses directly or use the mouse to paste in the addresses of your cells.

In Excel, array formulas let you perform sophisticated calculations, multiplying one array by another to get a total value — such as a column of prices by a column of quantities — in one fell swoop rather than creating individual formulas. Google's online spreadsheet product doesn't support array formulas. But it gets worse. When you use Google Spreadsheets to open an Excel spreadsheet that contains an array formula, Google simply strips out the formula and displays the results. Look Ma, no formula! That's dangerous because you may not realize the formula is gone, and Google Spreadsheets gives you no warning about it.

On the plus side, Google Spreadsheets' formula tools do highlight circular references. Thus, if cell A1 refers to cell B1, which in turn is dependent on cell A1, there's no way to resolve this "circular logic." Google Spreadsheets and Excel both wisely point out the problem.

Teaming up

The built-in instant messaging features make collaboration a breeze.  (Click image to see larger view)

Although sharing worksheets is currently limited to sending invitational e-mails to Google Gmail account holders, it's magically uncomplicated. You invite people by e-mail to view or edit a worksheet. Invitees with view permission click a link in the e-mail message to load the worksheet, and they'll be able to view changes in real time. An instant-messaging chat window also pops up automatically to let you swap comments as you work.

If your sent a colleague an invite with edit permission, both of you can edit the document at the same time, creating a very useful and simple collaboration process. The only limitation is that there's no built-in phone function in the IM client so you'll have to use another means (telephone or Skype, for example) to communicate verbally as you edit.

Printing

To print, you must use the "Get HTML" option from the File menu, which produces an HTML rendition of your work. You then use your browser's Print function to render the printed page. The program supports Internet Explorer 6 and Firefox 1.07 and above for Windows, Macintosh and Linux. (In my tests, Google Spreadsheets also worked fine in IE7 Beta 2.) The print function requires JavaScript functionality to be enabled in your browser.

There's no shrink-to-fit option in Google Spreadsheets. You can't even use Firefox's or IE7's shrink-to-fit-printing features because Google Spreadsheets doesn't let you set page breaks; it insists on setting those break points. As it stands in this first beta, printing is a weak spot for Google Spreadsheets.

Keyboard commands

Some Excel keyboard commands work the same way in Google Spreadsheets, though they aren't documented. For example, Ctrl+D copies the value in the first cell of a selected range throughout the rest of the cells in the range. Unfortunately, one of the smartest features in Excel isn't available in Google's spreadsheet. Excel can fill a series of cells with values that increment automatically in a common way — for example, 6/10/2006, 6/11/2006, 6/12/2006. If you show Excel the initial progression between two cells and then drag the mouse to extend the cell range, it automatically populates all the cells in the range with consecutive dates in ascending order. Google Spreadsheets doesn't offer this feature.

Some keyboard shortcuts from Excel work while others don't. Double-clicking or pressing F2 to perform in-cell editing, for instance, works. And while Ctrl+C for Copy and Ctrl+V for Paste work, your Web browser's Edit menu Copy and Paste commands do not. It is a minor shortcoming, however.

Some other types of user interface features are elementary. You can add multiple worksheet tabs and rename them, but you can't change their order. That's more an inconvenience or annoyance than a have-to-have feature.

Bottom line

In order to keep operations simple, Google Spreadsheets has omitted features that some Excel power users might desire. For example, there is no Find command. Also missing are spell check, conditional formatting, headers and footers, named ranges, hidden rows and columns, protection of cell ranges (to confine entry to only some cells), borders, shading, macros, groups and outlining, goal seeking, comments, automatic formatting of hyperlinks in cells, data filtering, and zoom.

Despite its beta designation, Google Spreadsheets' speed is remarkable. I experienced no perceptible delay when entering numbers into a worksheet, and recalculating formula-laden worksheets entailed only a slight delay.

Google Spreadsheets' Open dialog . (Click image to see larger view)

Google Spreadsheets is not an Excel killer — but no one expected it to be. If you're a financial analyst responsible for consolidating large budget spreadsheets, you're not going to adopt Google Spreadsheets. Need a chart? Stick with Excel. Ditto for graphics (such as WordArt) or PivotTables.

For simple math or managing short lists, it's a perfectly fine, easy-to-use tool. Its clean user interface, straightforward design, nimble performance and user-friendly menus are all pluses. A lack of help for composing formulas, if that's not remedied, is a serious omission, however.

The program isn't as rich as another free alternative — OpenOffice.org — but its collaboration features may win you over. All in all, Google Spreadsheets offers surprising utility for a Web-based spreadsheet. It's not a real alternative for everyday Excel users, but for occasional use when the data isn't sensitive, it deserves consideration.

Richard Ericson is one of several contributors to The Office Letter, a weekly e-mail newsletter covering Microsoft Office.

Related Opinion:

• IT Blogwatch: Net book disagreement, Google spreadsheet (and $2799 cat toy)
• Jerri Ledford: Google's office apps offer alternatives
• Robert L. Mitchell: The missing link in the Google office