Main menu

"Goodbye, Jean-Luc, I'm gonna miss you. You had such potential. But then again, all good things must come to an end."
- Q, Star Trek: TNG

Spreadsheets with Open Office

by Ken Newquist / October 28, 2007

After a few days of working with Open Office's word processor, it was time to turn to the spreadsheet. I don't work with spreadsheets much at home, but I do have a few specialized uses for them. One of those is a campaign manager for my Mutants & Masterminds campaign – I use it to track past, current and future events in the campaign. Each entry has a column for event name, date, weather, type of event (e.g. blog post, crime, super activity) and a brief description.

My first task in using NeoOffice (the Mac-based Open Office port) was to see if it had an “AutoFilter” and how well it worked. I use AutoFilter to generate quick dropdown menus that I can use to filter the campaign calendar according to the type of post or date. I quickly found the function in NeoOffice, and discovered it worked the same as in Microsoft Office. Just as importantly I was able to open, modify and save my original Excel document without losing any formatting or text.

My other big use of spreadsheets at home is tracking combat and calculating experience for my Dungeons & Dragons games, and all the features I use – sums, quick sort, conditional formatting – work within Open Office pretty much the way they did within Microsoft Office. Digging deeper, I found that Open Office supported its own version of pivot tables, which are something I've been using more and more at work. I haven't tried them to see how well they work vs. Excel, but its good to know they're there.

As far as more complex sheets go, Open Office is certainly capable of some impressive feats. For example, check out this Mutants & Masterminds character builder, which relies on all manner of cross-sheet lookups and data lists. While not easy to use as the Microsoft Office-based HeroForge for Dungeons & Dragons, it's still a handy tool and a good illustration of what Open Office is capable of.

Unfortunately, HeroForge demonstrates the largest shortcoming of Open Office; namely macro incompatibilities with Microsoft Office. Some might argue that this is Microsoft's doing, rather than Open Office's; Microsoft's suite uses Visual Basic for Applications for its macro-writing (at least for now; it's migrating to something else in coming years) and while Open Office supports a variety of scripting langauges, it doesn't can't handle Microsoft's out of the box. As a result, anyone trying to use a complex, macro-driven sheet like HeroForge in Open Office is going to see it fail miserably.

Overall, Open Office's spreadsheets app is a competent program that should be able to handle most of your day-to-day calculation needs. It will also handle basic Excel files easily, but anyone who has to deal with complex sheets involving numerous Microsoft-originated macros will find it simply can't get the job done.