OpenOffice Calc Tips

An introduction to the data consolidation functionality that is built in to OOo Calc. This is a useful feature when merging similar data from different souces.


Consider the example below. We have sales data for each month for different regions. The company has two products, widgets and Each product’s sales data is on a separate sheet.

We can use the data consolidation functionality of OOo Calc to combine the two sets of data into a single Totals sheet.

The Totals sheet will have same layout as the Widgets and Gadgets sheets.

The Consolidate data dialog is invoked as shown below.

Any source or result data ranges that will be needed here must be first declared as a named range

The named source data ranges are added - one at a time. Next, the results location is added. Please note while the full ranges of the source data are displayed, only the origin cell of the results is shown. This is probably an OOo Calc bug.

You may also want to check the Link to Source Data checkbox to ensure that the results are updated automatically if the source data changes.

The end result. Note the hidden rows in the sheet - which contain copies of the source data.

This entry was posted on Tuesday, March 22nd, 2005 at 8:18 pm and is filed under Using OpenOffice Calc. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.