Pivot tables

Number formatting

Take precise control over the number formatting in your pivot tables.
Numbers in pivot tables are right-aligned by default
To set number formatting:
  1.   Hover on the value header, a chevron appears.
  2.   Click on the chevron to open the number formatting popup.

Number types

Number

Displays regular numbers.
Percent

Displays numbers as a percentage by multiplying by 100, e.g. 0.57 becomes 57%.
Select the number of decimal places to round to in the ‘Decimals’ window at the bottom of the popup. Percentage formatting is particularly useful when displaying a calculated loss ratio.
Financial

Displays negative values in brackets.
% of total

Displays values as a percentage of the sum total of the column. This is useful for conveying the relative size of values within a column. For instance you may drag in a column ‘Profit’ and display it as a value, then drag in another instance of ‘Profit’ and display it as a ‘% of total’.
Table showing Profit as a value and as a percentage of total
Note that since the total must always equal 100%, negative values will be counted as their absolute values.

Magnitude

Simplify the display of your data by showing the numbers as Thousands, Millions or Billions. Select the number of decimal places to round to in the ‘Decimals’ window at the bottom of the popup. Hovering over the rounded number in the table shows the full value.

Alignment

Left, right or centre-align your numbers.

Decimals

Adjust the number of decimal places in the values displayed in this column.

Table formatting

Apply table formatting via the table formatting popup.
Table formatting popup

Shrink to fit

Reduces the size of the pivot table to fit to the bounding box.
Shrink to fit is supported for table sizes up to thousands of cells, however attempting to apply shrink to fit to extremely large tables may impair performance.

Compact rows

Reduces the row height to increase the amount of data that can fit to a page.
Pivot table with compact rows

Expand column headers

View the full text of headers of column and row headers. Column headers expand upwards, row headers expand outwards.
Pivot table with expanded column headers

Values as columns

Displays the table values as columns. This is the default setting for pivot tables.
Pivot table with values as columns

Values as rows

Displays the table values as rows.
Pivot table with values as rows

Aggregation

Each cell in a pivot table shows a segment of the raw data derived from the categorisation options selected, aggregating the raw data to show a single value. Different aggregation options show different aspects of the data.

Setting aggregation

Aggregation types

Sum
Sum total of values in each segment
Count
Number of values in each segment
Mean
Displays negative values in brackets
Min
Smallest value in each segment
Max
Largest value in each segment
Std Dev
Standard Deviation of values in each segment, i.e. the amount of variation in the values