1. To get the spread sheet to do the work in this section Right Click on this http://www.greig.net.au/geoff/For_Pivit_Table_execises.ods and then click on save link as, to download the spreadsheet above. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the spreadsheet and open it
3. Have a close look at this spreadsheet. I use it to summarise all that information that goes through my Credit Card. Rather than key in this information I download it via my banks (westpac) website see Getting information from Websites. This completes Columns A, B and C. I then enter information in the other columns. Column D I use to make a broad classification as to weather it is P for Private B for Business or I for Investment. Column E is the classification of what I spent the money on. The other columns further to the right are for any other notes I wish to make about the expenses.
4. If you look at the classification column, E, you will see that a number of the classifications are repeated. The objective is to determine what the total amount that has been spent on each classification. One way of do this could be to set up a separate column for each classification type and copy or move the amount into the appropriate column and total all the columns. Typically what is done in a accounting cash book. However this would require a lot of work
5. Another alternative would be to sort all the row via the Calc sort function (Data menu,Sort) and then use Calc's subtotal function (Data Menu, Subtotal). However that requires 2 steps and requires undoing the sub total and redoing it each time you all more rows to the sheet.
6. By far the best way to total the amount of each classification is to us the what is generally known as a pivot table, but is called a Data Pilot in Calc. What Data pilot does is summarise information based on what is repeated in a column or row. In our case summarise the amount column by adding the amounts where the classification column has the same items in it.
7. To use the Data Pilot is is best to first select the cells that you want to do this on
8. Click on cell A4. Whilst holding down the shift key press the right arrow key a number of times until the columns A through to E (Classification) are highlighted. Do not release the shift key yet
9. Whilst still holding down the Shift key also hold down the CTRL key and them press the down arrow key.
10. What happened? All the cells to the end of the data are selected (highlighted)
11. We will now start using the Data Pilot function
12. From the Data Menu, point at the DataPilot menu and then click on Start. What happened?
13. Click on Ok to accept the Current Selection
14. Not what is now show on the DataPilot Window. Look at it carefully to see if you can work out how it operates.
15. Drag the Classification button to Row Fields box
16. Drag the Amount button to the Data Fields box. Notice it now shows as Sum-Amount meaning that the amount will be summed (added up) for each classification.
17. Click on the More button at the bottom right of the window
18. Change the “Results to” to -new sheet-
19. Click on OK
20. What happened? The results are in a new sheet because of the option selected above.
21. Try doing the process again only this time additionally drag the Bus/Priv button to the Column Fields box. Note how the result is different

You have learn how to use the Data Pilot function to summarise information