1/31/2015

Using LibreOffice Calc to create pivot tables

A pivot table lets you provide various data viewpoints for analyses and reports. You can create pivot tables through a spreadsheet application, such as Calc in the LibreOffice productivity suite. This document can help you use Calc to:
  1. Create a practice spreadsheet
  2. Create practice pivot tables
  3. Refresh pivot tables
For more information, please see Pivot Table in LibreOffice help.

Creating a practice spreadsheet

A practice spreadsheet can help you learn to create pivot tables. To create your practice spreadsheet, do either of the following:
  • Click Stooge Computers potential profit, click File, click Download as, and then click OpenDocument format (.ods).
  • Open a LibreOffice Calc spreadsheet, and then type a source-data worksheet similar to Figure 1.
    Figure 1 - The main (SourceData) worksheet in your practice spreadsheet.
    You can analyze this information through pivot tables.

Creating practice pivot tables

After you create your practice spreadsheet according to the section above, you can create one or more practice pivot tables as worksheets within your spreadsheet. For example, to view potential profits, you might create two pivot tables:
  • Potential-profit data in Warehouse rows and Product columns
  • Potential-profit data in Product rows and Warehouse columns

Example Warehouse rows and Product columns

To create a practice pivot table that displays potential-profit data in Warehouse rows and Product columns, do the following:
  1. As shown in Figure 2, your SourceData worksheet, select the rows and columns that you wish to make available to your pivot table. In this example, I selected rows 2 through 14 and columns A through H.
    Figure 2 - Starting a pivot table.
  2. Click Data, click Pivot Table, click Start, select Current selection, and then click OK to display a Pivot Table window.
  3. As shown in Figure 3, drag Warehouse into the Row Fields frame, drag Product into the Column Fields frame, and then drag Potential Profit into the Data Fields frame.
    Figure 3 - Defining a pivot table through a Warehouse row field,
    a Product column field, and a Potential Profit data field.
  4. Click OK to display a pivot table similar to Figure 4. This pivot table contains Warehouse rows, Product columns, and Total Results for both.
    Figure 4 - A pivot table that displays potential profits
    through Warehouse rows and Product columns.
  5. As necessary, adjust column widths so you can see all data correctly.
  6. Rename the pivot-table worksheet according to its content, such as PotentialByWarehouse.
  7. Create a second example pivot table according to the procedure below.

Example Product rows and Warehouse columns

To create a practice pivot table that displays potential-profit data in Warehouse rows and Product columns, do the following:
  1. As shown in Figure 2,  your SourceData worksheet, select the rows and columns that you wish to make available to your pivot table. In this example, I selected rows 2 through 14 and columns A through H.
  2. Click Data, click DataPilot, click Start, select Current selection, and then click OK to display a DataPilot window.
  3. As shown in Figure 5, drag Product into the Row Fields frame, drag Warehouse into the Column Fields frame, and then drag Potential Profit into the Data Fields frame.
    Figure 5 - Defining a pivot table through a Product row field,
    a Warehouse column field and a Potential Profit data field.
  4. Click OK to display a pivot table similar to Figure 6. This pivot table contains Product rows, Warehouse columns, and Total Results for both. Note: Figure 6 shows information that is 'pivoted' from that in Figure 4.
    Figure 6 - A pivot table that displays potential profits
    through Product rows and Warehouse columns.
  5. As necessary, adjust column widths so you can see all data correctly.
  6. Rename the pivot-table worksheet according to its content, such as PotentialByProduct.

Refreshing pivot tables

When you create a pivot table, it contains only information that is current at that moment. Therefore, you need to refresh a pivot table after you update its source data. To refresh a pivot table, do the following:
  1. Select the worksheet of the pivot table you wish to refresh. For example, you might select your PotentialByProduct worksheet as shown in Figure 6.
  2. Right-click Filter to display its menu.
  3. Click Refresh to update pivot-table information.

1 comment :

  1. Anonymous16:59

    Exactly what I was looking for! A clear, concise example with sample data. You, sir, a a hero. Keep up the good work.

    ReplyDelete