Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you summarize numerical data using a PivotTable, Excel displays the values with either no formatting, which can make the numbers difficult to interpret, or using a number format. In both cases, to get an idea of how two values compare you must visually estimate the number of digits used to express the values and if they're of the same length, focus on the individual digits. Comparing two numbers isn't difficult, but comparing a few dozen or more can be confusing. In Excel, you can add color bars to a cell's background. The length of the bar reflects the relative magnitude of the value in the cell.
To create a color bar, click any data cell in the body of the PivotTable and then on the Home tab click the Conditional Formatting button, then point to Data Bars, and then select the type of data bar that you want to create. And there are two different groups within this palette. The first one is the Gradient Fill and the second is the Solid Fill. Gradient fills taper off as they move to the right, so it actually makes it little bit harder to tell the relative magnitude of values. Those were introduced in Excel 2007 and in Excel 2010 they've been kept for backward compatibility. But I highly recommend that you use one of the Solid Fill Data Bar styles which you can find at the bottom of the palette.
So I will select the Orange and when I click it Excel applies the data bar conditional format to this cell. I actually want to apply this data bar conditional format to all the cells in the data area so I can click the Formatting Options button to the right of the cell and then I can either select All cells showing "Sum of Revenue" values, which includes grand totals and subtotals, or I can select the bottom option which limits the data bar conditional format to those cells within the body of the PivotTable excluding subtotals and grand totals.
And the bottom option is the one I want, so I'll click it and apply the data bars. You can create a data bar by using one of the six default colors or you can select a custom color. To do that, click the Conditional Formatting button, click Data Bars, and then click More Rules. When you do, you can go down to the Bar Appearance section of the New Formatting Rule dialog box and select your color by clicking the Color button and then selecting a color from the palette. You can also select whether to have a border around the bar.
So for example, I currently have No Border but I can make it a Solid Border and then choose the color from here if I wanted to. If you have negative values that you want to represent using a data bar then you can click the Negative Value and Axis button and that opens a dialog box of the same name and you can select how to fill negative values. In this case, my example file has no negative values but I wanted to let you know that these options are here so that you can work with them. I'll click Cancel to close that. And finally, if you only want to display the data bar and not the data behind it you can select the Show Bar Only checkbox.
in this case I do want to display the value so I'll clear it and then click Cancel. One downside to using data bars is that because they fill a percentage of the cell's interior and don't have a fixed maximum length, two cells with the same value can have data bars of different lengths. In this PivotTable, which is laid out in compact form, similar values in columns B and C have differently sized data bars. When I apply data bars to a PivotTable I like to choose Outline Form as my report layout. To do that you can click any cell on the PivotTable and then on the Design contextual tab click Report Layout and then click Outline Form.
When you do, the cells are given identical widths, which makes it easier to compare relative values. Data bars provide a quick visual summary of the relative magnitude of values in your PivotTable. When you format your PivotTables so the columns are all the same width, the bars provide information that aids your analysis.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90743 Viewers
80 Video lessons · 137991 Viewers
59 Video lessons · 56782 Viewers
52 Video lessons · 70400 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.