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.
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.
Here's what the setup looks like.
Click OK, and you get this data.
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.
Here are the results you get with that setup.
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.