Open Office Navigation

OOBase Database | OOCalc Spreadsheet | OODraw Vector Graphics | OOImpress Presentation | OOWriter Word Processor Using Mail Merge

Latest news

26 Mar 2008: OpenOffice 2.4 released with some improvements in functionality for Calc e.g. columns and rows can now be moved with drag and drop.

Links:

• OpenOffice

Open Office Calc

This is the spreadsheet component of the Open Office Suite which I am now using in place of Microsoft Excel. I am using the latest 2.0.3 version and so far I haven't seen anything to stop me using this version.

After several months usage it seems to offer quite comparable features and performamce to the Microsoft product. Of course as you would expect there is some lack of polish, but it does most things you would expect of a spreadsheet with little fuss. There are some annoying quirks mainly associated with formatting cells but nothing to really deter me from full time use. The developers have quite a heavy bug fixing program which has had quite a long backlog; but at least the bugs are getting fixed. I'm not really sure what Sun Microsystems hopes to achieve from this massive development, but I wish them well in providing a viable alternative to MS Office.

Main Features

Increased number of Rows

The main improvement is an increase in the number of rows to 65,536 making it the same as Excel. The old limit of 32,768 made it incompatible and there were several occasions where it became a pain to convert from Excel. Unfortunately, the maximum number of columns is set to 256 which again is the maximum size for Excel - this is a bit of a shame since one can understand Excel suffering from this limitation as it is such an old design. It is strange that OpenOffice did not increase this limit after all even Quattro Pro allows 18,278.

DataPilot

With the DataPilot in OpenOffice.org 2.0 (similar to Microsoft Excel's PivotTable feature) it is now possible to create new groups, filter data based on values, show differences and percentages instead of absolute values, etc. The new DataPilot features turn OpenOffice.org Calc into a very powerful tool for data analysis. Note that the DataPilot can read the values from a database.

Styles and Formatting

This allows you to apply a common style including cell formatting to a spreadsheet. I have found a few problems in this area, which could be due to my user error, but whatever, my opinion is that the product is not quite polished enough in this area.

Autoformat

Open Office comes installed with some pre-designed formats which you can apply to your spreadsheet. I have found the best way to use this function is to select the area of the spreadsheet that you wish to format and then use Format/Autoformat. You can then select the appropriate format or if you wish you can add your own autoformat by selecting an already formatted spreadsheet and using the add button on the autoformat window. You must then give the new format a name. I have noticed some minor points using this feature. For example if you wish to modify the format by inserting new lines then you must go back to no autoformat make your changes then reapply the autoformat. Fair enough; however sometimes the only way you can achieve a reasonable format is to apply the formatting after applying the autoformat. Perhaps the autoformat template needs some tweaking.

Intelligent Sum Button

I have found this to be a timesaver when writing ad-hoc spreadsheets and you need a quick sum.

Converting from Excel

Calc readily reads Excel format spreadsheets and allows you to keep your Excel format or convert to Open Office open source format. I have only had one problem converting an Excel spreadsheet to Calc and this involved sound files. Of course you will find problems trying to use Excel macros - these are incompatible with Calc.

Downsides

I use Calc mainly for financial planning and budgetting, so I would not describe myself as being a power user. During use I have noticed one or two small features such as problems with formatting text or referencing cells across sheets. Normally when I find these problem they can be overcome with a bit of effort. Hopefully when the 2.0 release comes these minor problems will be overcome.

Copying and Pasting

Fairly recently I needed to build a chart in Calc and then do a copy and paste it into another application because I was trying to automatically generate some XAML code from the chart. Apparently this works fine with Excel, but when an attempt is made to paste it into another application, only the text is taken across leaving the chart behind. So there is a subtle difference between what is copied to the clipboard between Calc and Excel. I even tried copying the chart into Draw and then exporting the file out as an EMF file; but no luck. In the end I got fed up and downloaded the free Excel Viewer from the Microsoft site and then after having built the chart in Calc, saved it in Excel format and used the viewer to view the chart and also to copy to the clipboard. This worked fine and saved me having to buy Excel.