Data Bars are created by the Conditional Formatting tool and are a great way to create in-cell bar charts.
“But how do we create them in a Pivot Table?”
It’s not much different than creating Data Bars in a regular table but there is an extra step.
Using the above Pivot Table as the example:
- Add the “Sales USD” to the Values This displays the expected numbers (in column B).
- Add the “Sales USD” a second time to the Values
- Select any number in the second set of “Sales USD” and click Home (tab) -> Styles (group) -> Conditional Formatting -> Data Bars -> pick your desired color.
You will see a Data Bar on the single cell you originally selected.
Here comes the extra step.
Click the small button to the right of the cell containing the Data Bar to reveal the Formatting Options list.
If you want EVERY number in that column (including the totals) to display a Data Bar, select “All cells showing “Sales USD” values.
If you only want to display Data Bars for the row-level items (no totals), select “All cells showing “Sales USD” values for “Product Description”.
Tweaking the Results
Since we don’t need to see the numbers twice, we can hide the numbers in the Data Bar column by selecting Home (tab) -> Styles (group) -> Conditional Formatting -> Manage Rules.
Select the Data Bars rule and click “Edit Rule”.
In the Edit Formatting Rule dialog box, place a check in the option labeled “Show Bar Only” and click OK.
If you don’t want to display a title above the Data Bars, you can manually change the name in the cell to a single space.
Word of Warning!!!
If you refresh the Pivot Table, you run the risk of collapsing the column containing Data Bars.
To prevent this from happening, set the Data Bar’s column to your desired width, then select the Pivot Table Options (upper-left).
In the PivotTable Options dialog box, on the “Layout & Format” tab, uncheck the option labeled “Autofit column widths on update” in the “Layout” section.