How to Work With LibreOffice Calc

How to Work With LibreOffice Calc
Calc from LibreOffice gets our expert view

LibreOffice is a complete office suite for Windows, Mac and Linux, and offers users the ability to get more work done for less. Our spreadsheet expert Gordon Guthrie looks at Libre Office's spreadsheet Calc and decides if it's right for your business.

You probably have heard of the free office suite Open Office which has been around for a while. Open Office is free because it is open source – a kind of 'worker co-operative' where software developers get together to achieve more than they could alone. Open Office had been under the umbrella of a company called Sun, and when Sun were taken over by Oracle the developer community decided to leave the nest and go it alone – and LibreOffice was born – a child of Open Office.

LibreOffice 3.5 is the latest incarnation of this suite – you can download the full suite from http://www.libreoffice.org/ and because it's open source, the product is free, you can download it for nothing, and install it on as many machines as you like.

More than just a spreadsheet

This article focuses on just the spreadsheet component of the office suite, but LibreOffice is designed to be a complete replacement for Microsoft Office, and includes six programmes:

  • Base – their version of Access
  • Calc - an Excel-compatible spreadsheet
  • Draw – a drawing/charting package
  • Impress – a PowerPoint substitute
  • Math – an editor for mathematical formulae and chemical equations
  • Writer – a Word-compatible document writer

There is a high degree of compatibility between some components (spreadsheets and documents, presentations) and a high degree of similarity between Base and Access.

There is a great documentation set in many languages which is also available to download for free: http://www.libreoffice.org/get-help/documentation/

How does it compare to Excel?

When you open LibreOffice Calc it will be very familiar to you. It looks like an old version of Excel before the ribbon:

Libre Office Calc Spreadsheet

Libre Office Calc Spreadsheet

There is a good set of functions (over 300) most of which are identical to Excel so you will feel at home.

If you look through the menus and try out the features you will see that things are pretty much as you would expect from Excel. I will pick the most important elements of a spreadsheet and compare them:

  • the function wizard
  • basic formats
  • charting

The Function Wizard

This is Libre Office's:

Libre Office Calc - Function Wizard

Libre Office Calc - Function Wizard

You select the function and press next to input actual parameters:

Libre Office Calc - Function Wizard with a function selected

Libre Office Calc - Function Wizard with a function selected

If you want your function to call functions you can press the fx button on the parameter and build up a complex function bit-by-bit.

By comparison Excel 2003 starts with a function selection:

Libre Office Calc - Excel with a function selected

Libre Office Calc - Excel with a function selected

When you have done that it takes you on to filling out the parameters:

Libre Office Calc - Function Wizard parameter fill

Libre Office Calc - Function Wizard parameter fill

Basic formatting in LibreOffice Calc

The LibreOffice formatting dialog box looks like this:

Libre Office Calc - formatting dialog

Libre Office Calc - formatting dialog

As you can see it is very similar to the Excel 2003 equivalent, getting the formats you want is pretty straightforward:

Libre Office Calc - formatting dialog choosing currency

Libre Office Calc - formatting dialog choosing currency

Charting Options

The charting options are likewise very similar. Libre Office's set of charts will be very familiar:

Libre Office Calc - Charting options

Libre Office Calc - Charting options

Excel 2003's set is nearly identical:

Libre Office Calc - Charting options in Excel compared

Libre Office Calc - Charting options in Excel compared

Other familiar features

It is not surprising that LibreOffice is so familiar – the intention of the project is to allow people to move away from Microsoft Office and so naturally LibreOffice tracks features in Microsoft Office 2003 and copies them. Here is a short list of things you will also recognise:

  • comments
  • data tools like filter, sort and subtotal
  • pivot tables
  • workbook and sheet protection
  • hiding rows, columns and sheets

Things that are different

The big compatibility challenge to LibreOffice is macros and Visual Basic for Applications (VBA) – which is hardly surprising if you know the history of Excel. Microsoft won the spreadsheet market from Lotus-1-2-3 by ensuring the Excel could open 1-2-3 files and do everything 1-2-3 could do.

The team at Microsoft were determined not to let anyone steal the crown from them – and Visual Basic was the mechanism they settled on. To open ordinary spreadsheet files you only have to copy the 'surface' of the other spreadsheet. To make the scripting language compatible you have to copy the internals of the other programme as well.

Microsoft's major source of revenue from Excel comes from corporate licensing. Only a tiny fraction of spreadsheets use Macros but these spreadsheets tend to be important for big companies and also tend to have a lot of time and effort invested in them.

LibreOffice additional features

While LibreOffice has modelled Calc on Excel there are a few features that are unique to Calc.

The first is that zoom is built into the spreadsheet frame in the bottom right hand corner:

Libre Office Calc - Zoom feature

Libre Office Calc - Zoom feature

Sometimes you just want to zoom out to see the structure of the worksheet to find a cell before zooming back in to where you working – this makes it a cinch.

The second is that you can export to a PDF natively. There are tools that let you do this in Excel but it is a faff to find them, install them and make sure they do a good job.

The third is the export to HTML files. Sometimes you just need to publish some figures in your WordPress blog.

Microsoft's strategy with export to HTML is to try and make a good two way process – you can write as HTML and read it back in. This means it produces very verbose HTML output with lots of classes and formatting and other gumph.

LibreOffice assumes you want to save as HTML to publish something and you wont be opening it back up in LibreOffice – so its HTML is quite lean and sparse and manageable – so if you need to bodge it into your blog it is a lot easier to do.

When to use Libre Office

LibreOffice is great when you:

  • are short of money
  • want a spreadsheet that runs on Windows, Mac's and Linux
  • are just starting up

LibreOffice might not be suitable if you:

  • have a large suite of existing Excel spreadsheets which are business critical
  • share a lot of complex spreadsheets with business partners who use Excel
  • need a lot of VBA/macros in your spreadsheets

Gordon Guthrie loves spreadsheets so much he has written his own one – Vixo, like spreadsheets but better, which you can explore at Vixo.com. For more spreadsheet tips and links follow him on Twitter@gordonguthrie