Working with the DataPilot
When you work with large spreadsheets, where the amount of data seems overwhelming, or with spreadsheets where you want to quickly rearrange how the data is displayed or summarized, you can use the DataPilot to create a pivot table. The DataPilot's table cross-tabulates the information into rows and columns, making it easy for you to quickly analyze the data.
Since you can manipulate a DataPilot table even after you've created it, you can view your data in different ways without creating different tables from scratch. The DataPilot table derives its data from your original spreadsheet, so if you delete the DataPilot table, you aren't affecting the original spreadsheet at all.
To Create a DataPilot Table
1. |
You can create a DataPilot table from a named database or from a selection on the spreadsheet. If you have not named your database, select the entire table. |
2. |
Select the Data menu, point at DataPilot, then select Start. A Select Source dialog box will appear. If you are going to use the selected table as the source for the DataPilot, make sure the Current selection option button is selected (see Figure 9.18). If you are going to select from a list of database names, select the Data source registered in StarOffice option button. Figure 9.18. Select how you will identify the source for the DataPilot table.
|
3. |
Click OK. If you chose to select from data sources, select from the list and then click OK. The DataPilot dialog box will appear. |
4. |
The DataPilot dialog box lists all the column and field headings in your database. Basically, you have to decide which fields you will place in the rows on the pivot table that the DataPilot creates and which fields you will place in the columns. You also need to determine which fields you wish to have appear as data in the table. For example, if you have a sheet of customers, the products they purchased, and the purchase price and date purchased, you could place the products and date in the Row area, place the customer field in the Column area, and then place the price in the Data area. This allows you to analyze the data based on product. Figure 9.19 shows how this would look. You just drag the field names to the appropriate areas of the DataPilot table. Figure 9.19. Drag the field names onto the DataPilot areas to set up the pivot table.
|
5. |
When you have finished placing the field names on the DataPilot layout areas, you can click the More button to specify where the table should be placed on the current sheet. Click the Shrink button next to the Results to box and click the cell where you want the table to start on the sheet. Then click Shrink on the rollup to return to the dialog box. |
6. |
Click OK to create the DataPilot table. |
Figure 9.20 shows a DataPilot table that was created from a table that held customer data and their purchases. The pivot table cross-tabulates the information for easy viewing.
Figure 9.20. The DataPilot table allows you to view the data as you arranged the fields in the DataPilot dialog box.
To Filter a DataPilot Table
You can quickly filter the data in the DataPilot table using the filter box that resides directly over the table.
1. |
Click the Filter box and the Filter dialog box appears (see Figure 9.21). Figure 9.21. You can use the Filter dialog box to filter the entries in a DataPilot table.
|
2. |
Click the first Field name drop-down box to select a field to filter. |
3. |
Click on the Condition drop-down box to select the condition operator for the filter (such as = or <, for example). |
4. |
Click the Value drop-down box to select a value for the filter. The values available will be entries from the original table that were listed under the Field name you selected in Step 2. |
5. |
Select other field names, conditions, and values as required for the filter. If you filter by more than one field, you must select And or Or as the operator in the Operator drop-down box. |
6. |
When you have set all the filter criteria, click OK. The DataPilot table will be filtered. |
You can clear a filter by reopening the Filter dialog box and clearing your filter settings.
To Format and Edit a DataPilot Table
You can add any formatting to a DataPilot table that you wish. It in effect is the same as any table you build in Calc. You can also easily edit a DataPilot table.
1. |
To edit a DataPilot table, click anywhere in the table. |
2. |
Right-click and select Start from the context menu. The DataPilot dialog box for the table will open. |
3. |
You can change how you've set up the layout for the table by dragging field headings to new positions. If you want to remove a field heading from the table's Row, Column, or Data area, drag the heading back to the field heading docking area at the right of the layout box. |
4. |
When you have finished editing the table, click OK to return to the new, edited table. |
You can also delete a DataPilot table when you no longer need it. Right-click on the table and select Delete from the context menu. The DataPilot table will be removed from the current sheet.