Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Chartless Visualization Techniques
The Data Bars are, by default, placed directly inside each cell, almost obfuscating the data. From a
dashboarding perspective, this is less than ideal for two reasons:
➤ The numbers can get lost in the colors of the Data Bars, making them difficult to read —
especially when printed in black and white.
➤ It’s difficult to see the ends of each bar.
Figure 4-23: Showing Data Bars inside the same cell as your values can make it difficult to analyze the data.
The answer to this issue is to show the Data Bars outside the cell that contains the value. Here’s how:
1. To the right of each cell, enter a formula that references the cell that contains your data
For example, if your data is in B2, go to cell C2 and enter =B2.
2. Apply the Data Bar conditional formatting to the formulas you just created.
3. Select the formatted range of cells; then click the Home tab and select Conditional
Formatting ➜ Manage Rules.
The Conditional Formatting Rules Manager dialog box opens.
4. Click the Edit Rule button.
5. Place a check in the Show Bar Only option, as demonstrated in Figure 4-24.
6. Click OK to apply your change.
Figure 4-24: Edit the formatting rule to show only the Data Bars, not the data.