As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013. Much of the content will be a result of my daily interactions with business users and other BI devs. In order to not forget what I learn or discover, I write it down … here. I hope you too will discover something new you can use. Enjoy!
Using the Data Bar
This feature has been a part of Excel for a long time. However, as with any tool, some of the oldies are really good. As a BI architect who worked with SQL Server tools, I am always amazed at what has been around in Excel for years. So as part of this series, I will also highlight some important visualizations that have been around.
What is the Data Bar?
The data bar is a conditional formatting feature that can be applied to cells in Excel. Data bars “fill” the cell proportionally based on the data that the formatting is applied to. Data bars work with pivot table and standard data in Excel. Our focus will be on using the data bar with pivot tables.
You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.
As you can see Data Bars are one option under Conditional Formatting. Look for future tips to come on some of the other Conditional formatting options.
Adding Data Bars
The following sample is from the MyVote data generated from the Modern Apps Live! project. In this sample, I have a simple pivot table which shows the Age range and number of poll submissions as shown below.
To add data bars highlight the area to add the bars and choose a format.
Advanced Settings
By clicking More Rules … you will be able to apply advanced options.
In my opinion, the most important setting to use here is at the top. When working with pivot tables, you should at least choose Apply Rule to “All cells showing {field name} values.” By choosing this option, if new row values are added, the data bars will be appropriately applied. This also is necessary for filters and slicers to work correctly.
However, this option will also highlight the any total columns (in our case Grand Total was included). If you only want counts with the row labels, you would choose “All cells showing “{field name}” values for “{field name}”. You will find that this is the most common option to select when using data bars for data visualization.
Modifying Existing Data Bars
Once it has been added, you can modify the data bar choosing the Manage Rules option in the Conditional Formatting drop down. This will open a dialog box which has the formatting rules for the selected pivot table. There is a drop down, which allows you to select the rules for the sheet or other parts of the workbook. From here you can see all of the rules applied and can edit the rule, create a new rule, delete a rule, or reorder the rules.
Data bars are a simple, but effective data visualization when you need to highlight the variance between values. With the ability to apply the data bar to a field in a pivot table, it becomes a flexible visualization as well with very little effort involved.
