Here’s a way to show conditional formatting of data bars only in Excel:
You can download the file here to follow along.
Data bars in conditional formatting are a nice visual feature to show the relationship of values in a data range. However, sometimes they can look a bit cluttered, and it might look better to show them separately without the values below.
Here is an example of data bars on the sales in a data range. Conditional formatting can be found in the Styles group on the Home tab:
But, if you want to have the data bars separate from the values, you can accomplish this in one of two ways. First, you will need to create another column with the sales data in it. You can do this in column I by typing =G2 and copying that formula down:
Now, while those values are still highlighted, click on Conditional Formatting, select data bars and the color you want:
Then, go back into Conditional Formatting and select Manage rules:
Click on Edit Rule:
Then click on Show Bar Only:
Click OK and OK and now you have your data bars only separate from the values:
The other way to achieve the same result is, after you have created the separate column with the sales data in it, highlight the sales data, click on Conditional Formatting and choose New Rule:
Then in the Format Style drop-down, select Data Bar:
From here you can choose your color, solid or gradient, and don’t forget to check Show Bar Only:
And you now have your data bars only: