Data Pilots (Pivot Tables) in OpenOffice Calc

See also this blog post.

Data Pilots are the OpenOffice Calc equivalent of pivot tables.

How do you use them? Very carefully. If you haven't used them before, it's just a bit of a mind-bender to get started--it's all about the data that you start off with. You start off with raw, plain old data, not data that you've already summed up. 

I'm including a lesson here on how to use the data pilot. Download this file, datapilotspreadsheet, if you want to use the same spreadsheet I've used. (Don't worry if your numbers are a little off from the screen shots--I tweaked it a little along the way.)

1. Open a spreadsheet.

2. Click in the upper left corner of the data. Don't select the data; you don't need to.

3. Choose Data > Data Pilot > Start.

4. Choose to use the current selection, then click OK.

5. Here's the window you'll be working with. Just drag the fields where you want them, and click More to have more options. When you have things the way you want them, just click OK.

I'll show you a few examples: the setup, and what the data results look like.

NOTE: Click any of these images to see a bigger version.

Example 1

Here's what the setup looks like.

Click OK, and you get this data.

Click the Filter button and you can filter things out more -- say, just view invoices that are more than $5000,  or whatever you like.

Example 2

Here's the setup. The Store window in the Page Fields area will give you a dropdown list so you can view all values, or one value at a time for the category in the Page Fields area.

Click OK and you get these results, with the option to use the dropdown list.

Select a different value in the dropdown list and you get different results.

Example 3

Here's the setup. As you can see the More section is displayed with different options.

Here's a closer look at those options. I specified that no total for rows or columns should be included, and that the results be put in Sheet2 in the upper left corner.

Here are the results you get with that setup.

Example 4
Here,  I double-clicked the Sum-Amount field to get other function options. I'm going to choose Average for this example

Here are the results.

Double-Clicking a Field
You can see the detail rather than the sum or average if you double-click the name of a field. The following window appears.

Click OK in that window after making a choice and you get the detail. Double-click a detail field to go back to the regular view.

---

Comments

[Kingsa]

Tip: Right click in the "filter" box (located in cell A1) will alow user to change pivot-table without delete the new sheet (if the pivot table is created as a new sheet).

http://i18.tinypic.com/5yb4tqe.png

[Paul Feakins]

Wow, a really nice example. I now understand DataPilots :)

[Solveig Haugland]

Hi Paul,

I'm glad it helped! They're a bit of a mindbender before you ee examples.

Solveig

[Solveig Haugland]

Hi Kingsa,

Thanks! I'll check it out.

Solveig

[Stephen]

can they be made to work with questionnaire type data where responses are yes/no and would need to be summed? Eg we know occupations as the first question, and would like to look at different frequency of responses by profession

[Stephen]

To answer my own question. Yes, you can do this for questionnaire data. I chose to convert the yes and no data to 1s and 0s with a find and replace. then exported it to a database - this last step is probably not really necessary. But anyway, then I ran data pilot. Needs some further work to convert into percentages and for plotting ...

Previously I had done this by a series of sumproduct functions

what would be interesting would be whether it can deal with data in ranges.

[Solveig Haugland]

Hi Stephen,

Thanks for posting the solution, I apologize -- I wasn't quite sure of the answer and have been on the road.

Solveig

[Mimin]

I was read your posting. Thanks you very much

[Sajan]

Hi,

I guess when you wish to have the average/sum/max/min of the Amounts in the column section, Open office snaps up. This is a convenient feature in Excel where you get to compare it with various values of the same parameter.

Any workarounds to this limitation ??

Cheers
Sajan

[Solveig]

Hi Sajan,

I'm sorry, I don't follow -- could you clarify or expand on "snaps up"?

Solveig

[Dave]

This is why I haven't bought your book yet. I refuse to do so until I can get at least one straight, clear, intelligible answer to a question.
Until then, it's Excel all the way. Its help files are about as murky as Open Office's, but at least there are dozens of books and millions of users.

When I followed the example you gave hear things didn't happen as you said they would. And if I have to spend fifteen or twenty minutes figuring out why, then what exactly is the purpose of coming to an expert's blog?

Please, I'm begging. Just one straight answer, one clear tutorial, and I'll happily contribute my $36.

[Solveig]

Hi Dave,

Thanks for your comment. I have set up my blog to post all lessons from my workbook in a Data Pilot tutorial. See the posting that is now listed at the top of this blog post.

You can also see data pilot info here.
http://www.openofficetips.com/blog/archives/2005/08/datapilot_revis.html

[Sneha]

Hi Solveig
The example you gave helped a lot. I have a query if anyone can help me with it. In the case where there is ample of data in the spread sheets, and lot of it is repetitive data, is there any shortcut using data pivot to put them in desired order?

Rgds
Sneha

[free pass4sure]

Thanks for posting the solution. Thanks! I'll check it out.

[Rajesh]

How can i include new rows in the existing pivot table?

[Siva]

Great stuff, thanks!

[chetan]

Really this work

[jag]

Is there a way to group data with datapiots like with pivottables in excel?