PIVOT tables are one of the most useful widgets for visualizing data. They enable you to quickly summarize and analyze large amounts of data.

Adding Data

Select the data to appear in the PIVOT.

  1. In the ROWS panel, click ADD + to select the field(s) whose values will be placed in the rows of the PIVOT table.

pivotRows

When more than one ROW is selected, the ROWS are broken into sub-rows (groups). For example, the following shows Condition added as a second ROW:

pivotRows2

  1. In the COLUMNS panel, click ADD + to select the field(s) whose values will be placed in the columns of the PIVOT table.

[alert type=”success” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″]You can drag and drop the fields that you added to COLUMNS to the ROWS area and vice versa.[/alert]

  1. In the VALUES panel, select a field whose values will appear in the ROWS and COLUMNS of the PIVOT table.

Tip: Right-click the value to add data bars to your pivot table.

data bars

SUBTOTALS

You can add subtotals to one or more rows, and define how to calculate subtotals.

To add subtotals to rows:

  • Click on the menu of the ROW in the Data Panel, or right-click on the row name (header) in the widget, and select Subtotals.

sub totals new

To define how to calculate the subtotals:

  • Click on the menu of the VALUE in the data panel, or right-click on the value name (header) in the widget, and then click Subtotal by and the method.

sub totals

The default option is Auto, which aggregates all the data. The other custom options (Sum, Min, Max, etc.) calculate only the values in the rows above the subtotal.

Look at the following example:

sub totals eg4

In the top example, using the Auto option, the subtotal for average revenue aggregates all the sales and revenue data, thus representing a true subtotal of the average revenue for the Asia region.

In the bottom example, Average is used to calculate the subtotal, and therefore calculates the average of all the average revenue values in the rows above the subtotal.

GRAND TOTALS

Grand totals aggregate the rows in the pivot table.

To add grand totals:

  • Click on the menu of the row in the data panel, or right-click on the row name (header) in the widget, and then click Grand total.

Grand totals is effected by subtotals if defined.  In the following example, the Grand total for Average Sales is 10, representing the average sales for two regions (Asia and Europe). In the example below, Average Sales has a subtotal (see above) set to Sum, and therefore the Grand total is the total of the rows above.

sub totals eg5

Designing the Pivot Table

Fine-tune the appearance of the PIVOT table, using the following tools.

  • PAGE SIZE: Specify how many rows appear in each page. Paging options are provided accordingly.
  • COLORS: Select the properties in the table to which you want to add color.

Select how the rows and columns of the PIVOT table are highlighted.

Exception Highlighting

Conditional formatting can be used for exception highlighting in a Pivot table. For example, as shown below:

pivotConditional

See Defining Conditional Coloring – Condition for more information.

Note: For more styling options, including font style and colors, see this article.