PIVOTTABLE FORMATTING
CHEAT SHEET
GET STEP-BY-STEP VIDEO HERE: https://bit.ly/pt-tips
1. Custom Sort Order You can’t simply type ‘Actual’ in place of ‘Sum of
Actual’ because this field name is already taken,
4. Subtotal Position
Use the built-in sort options to sort based on a but you can add a space before or after ‘Actual’ You can easily move subtotals to the bottom:
numeric field in ascending or descending order: to differentiate it. Adding a space at the front
allows to right-align the column label neatly:
The Grand Total row can be turned off if it is not
You can replace the ‘Row Labels’ header with a required for a report:
space to override it. You can also turn the filter
drop-down or the header off by deselecting Field
Sort based on a custom list via the Headers on the PivotTable Analyze tab:
More Options… button:
5. Custom Styles
To create a custom style, first duplicate any
style by right-clicking on the thumbnail in the
style gallery:
3. Inserting Blank Rows
Improve the readability of PivotTables by adding
blank rows between each section:
Then click on Clear for each of the elements in a
bold font (the elements not in a bold font don’t
Often, it is quicker to sort by manually
have any formatting in that style):
dragging the labels into place, or typing
the label in the position you want it.
It immediately looks more like a Profit and Loss
2. Column Labels report, rather than a PivotTable:
Default column/row labels for the value fields
are prefixed with ‘Sum of’, ‘Average of’ etc.,
which is often unnecessary. Same goes for the
‘Row Labels’ header, as you can see below:
After creating your custom style, apply it to the
PivotTable by clicking on it in the style gallery.
Now you can select elements in the PivotTable
and apply the formatting you want.
Mynda Treacy, TEACHING YOU
CAREER TRANSFORMING SKILLS
© Copyright 2023
Follow me for tips and tutorials
8. Repeating Item Labels 10. Hiding (blank)s
PIVOTTABLE CHEAT You may want to repeat the row labels to use Blank cells in the source data get labelled
FORMATTING SHEET them in lookup formulas, or just for aesthetics. ‘(blank)’ in PivotTable row and column labels:
TIP: select elements by clicking the left
side (see image below) to select them
all together. This way the formatting will
be applied to the element, not the cells,
and if your PivotTable changes shape Ideally there should be no blanks, but the next
the formatting will follow the element: best thing is to hide the blanks by selecting one
One way to do this is to turn it on for all item of them and pressing the space bar > ENTER.
labels via the Design tab:
This needs to
be done for
each field that
contains
blanks, but
once applied,
any new blanks
6. Conditional Formatting that are added
will not display
Apply conditional formatting to PivotTable value (blank).
areas and have them automatically update when
the PivotTable is refreshed by selecting one of Another way is to hide blanks in PivotTables with
the ‘all cells showing’ options: Conditional Formatting. This can be done once
It can also be done individually by right clicking for all (blank) records and is quicker.
on the field you want repeated > Field Settings >
Layout & Print tab > Repeat item labels: 11. Preserving Formatting
PivotTable Options (right click) > Layout & Format
tab > Preserve cell formatting on update:
7. Expand/Collapse Buttons
Expand and Collapse buttons enable you to
quickly hide and unhide rows in the PivotTable:
9. Report Layouts
By default, PivotTables are created in compact
form where row labels are nested, but we can
also choose an Outline layout or Tabular layout:
12. Default PivotTable Layout
Once you’ve done the formatting, you can set a
default PivotTable layout via the File tab > Options:
If you don’t need these buttons, you can turn
them off on the PivotTable Analyze tab:
It’s easy to change the layout via the Design tab:
Mynda Treacy, TEACHING YOU
CAREER TRANSFORMING SKILLS
© Copyright 2023
Follow me for tips and tutorials
