Tips, tricks, tools, and techniques for Microsoft Office

Google’s Spreadsheet -- released in limited beta about two weeks ago -- isn’t going to keep the Excel team up at night. If you’re the typical Excel user, you’re not likely to give up Excel for Google Spreadsheet. Your customers or colleagues just might, however, which is why you need to know what this program can -- and can’t -- do.

Google Spreadsheet (we tested version 1.14b) lacks many high-end features that Office Letter readers likely use. It offers no support for charts or Pivot Tables, for example. If you use Excel to manage lists, you’ll lose features such as data filtering. The program can sort text columns in ascending or descending alphabetical order, but you can’t define a custom sort order. Forget using macros and VBA, custom toolbars, any of your add-in tools, or user-defined functions. You won’t be able to zoom in or out, rotate text, or password protect a file.

WHAT IT’S GOOD AT

However, as a simple-to-use, quick-and-easy spreadsheet program, there’s much to recommend Google Spreadsheet. We imported dozens of worksheets, and all formulas were properly evaluated -- except for array formulas, which imported only the formula values, not the formulas themselves (a major deficiency). Besides support for over 225 functions, you can set the font used (6 are supported) and pick from 11 font sizes, set the text and background colors (from a menu of 40 fixed choices), copy and paste formulas, align the contents (left, center, or right), wrap text in a cell, and undo/redo your edits.

Figure 1. Click to enlarge

Formatting cells is easy thanks to a pulldown menu (see illustration). However, formatting options are limited -- you can only have zero, two, or the original number of decimal digits in a number, and there is no way to format February 3, 2004 except 2/3/2004 or 3-Feb-2004. On the plus side: the pulldown menu offers plain-English options plus an example for each option.

There are many user-friendly options in the pull-down menus –the Insert option offers “Row Above” and “Column Left” as options. Select 4 rows and then pick the Insert button and you’ll see a new “4 Columns Left” option. Choose two adjacent cells in a row and you can use the Merge Across option; to undo that action, use the “Break Apart” button. (You cannot merge cells vertically, however.)

While Excel 2007 is expanding to 16,000 rows and one million columns, Google is more conservative. New worksheets are built with 100 rows and 20 columns (though you can add more). You can work with up to 100 spreadsheets in all, and each of these can have no more than 20 tabs, 50,000 cells, 10,000 rows, or 256 columns. If any worksheet exceeds one of these limits, the worksheet can’t be modified. One final limit worth mentioning: your .XLS and .CSV files cannot exceed 400KB each.

When it comes to doing the math, the biggest omission is help for the built-in functions. When you select a function from a list, Google Spreadsheet pastes the function name plus “(args)” into the cell -- so you’d better know what the argument list is. Beta 1 offered no online help on these functions, either, so get yourself a good book on formulas.

Figure 2: You can insert functions, but there's no help provided.

Other features we miss: You can drag column borders to change the width of columns, but you can’t double-click on a border to automatically change the width to the largest value in the column. Likewise, there’s no menu option to set column width by entering a number. You can’t set page breaks, and there’s virtually no control over priting.

Some of the features are inconsistent. You can freeze up to five rows, so as you scroll through your worksheet those rows will always be visible at the top of the screen. (The frozen rows are excluded from your sorts, too.) Unfortunately, there is no way to freeze columns.

Don’t expect the kind of speed you get from Excel when you change a value and watch all the dependent cells update, but it’s no slouch either. We have no complaints about the speed.

COLLABORATION SETS IT APART

While Google Spreadsheet mimics Excel behavior, in one aspect it sets itself apart. You can share your Google Spreadsheet worksheet with other users (the beta has limited invitations to those with Gmail accounts). There are two options: read-only or edit, which reflect what the recipient can do. In fact, if you and a recipient have the worksheet open at the same time, the recipient can view your changes in real time. (If you’ve granted the recipient editing rights, you can see his/her edits in real-time as well.) There’s a danger: editing a cell while your recipient edits a cell that depends on the cell you’re editing could put the worksheet out of sync.

The good news is that the process is incredibly simple. If you don’t want the person to have access to the file, simply remove their e-mail address from the permission box. You don’t need SharePoint or any other program to share your work. Just keep in mind that your unencrypted data is being viewed over the Internet, so the process isn’t completely without risk (however limited that risk is).

Speaking of keeping control of your data, worksheets are stored to Google’s servers. If you’re concerned about storing your sensitive data on a location other than your own hard drive -- especially sensitive information -- you have options. You can import existing Excel worksheets, or create new worksheets from scratch, then export them (as .XLS or .CSV files) without ever saving your work to Google’s servers.

WHY YOU SHOULD CARE

So if you aren’t going to switch to Google Spreadsheet, why should you care? By the sheer force of Google’s market presence, someone you trade files with will probably be using the program in the future. That means your worksheets must not use macros, fancy formatting, array formulas, and the other high-end features.

For simple math, Google Spreadsheet does a good job. For everyday list-keeping (I’ve used it to track the progress of articles for The Office Letter, for example), it’s also a fine choice. For more sophisticated uses -- when you might use a range name, need to transform rows and columns into columns and rows, or need a chart to perk up a reader’s interest -- Google Spreadsheet seriously lags.

But this is only Beta 1. We’ll be keeping our eyes on this one. You should, too.

To sign up to join the limited beta, visit http://labs.google.com.

-- Richard Ericson

^ Top of page ^

If you like this newsletter, share the free edition with your friends and co-workers. Just point them to:

^ Top of page ^