Instructor Lead On-Demand Learning Courses - August Big $10 Sitewide Extravaganza All Pro Web Designs and Udemy are happy to offer this special to you, good only on dates: 08/21-08/31 Act Now!

How to Fix Excel Data Bars Appearance

To make a bar chart right in the worksheet cells, use Excel Data Bars, built with conditional formatting. See how to add a standard set of Data Bars (Excel 2007 and later), and adjust their settings to make the bars look better.

And remember to mark your calendars – next Tuesday, October 17th, is Spreadsheet Day – alert your family and friends!

Add Excel Data Bars

First, to add a standard set of Data Bars , follow these steps:

Instructor Lead On-Demand Learning Courses - August Big $10 Sitewide Extravaganza All Pro Web Designs and Udemy are happy to offer this special to you, good only on dates: 08/21-08/31 Act Now!
  • Select the cells with numbers (don’t include any row or column totals).
  • On the Ribbon’s Home tab, click Conditional Formatting.
  • Click Data Bars, and then click any one of the Data Bar options

NOTE: You can change the Fill Style (gradient or solid) later, or choose a different colour for the Data Bars.

The selected cells will show Excel Data Bars, along with the original numbers. In the screen shot below, the Green Gradient data bar option was selected

Excel Data Bars with Green Gradient option

Change the Data Bar Settings

After you add the Data Bars, you can make a few changes to the default settings, to make the bars look better. Here are the steps for opening the Settings dialog box:

  • Select the cells that contain the data bars.
  • On the Ribbon, click the Home tab
  • In the Styles group, click Conditional Formatting, and then click Manage Rules.
  • In the list of rules, click your Data Bar rule.
  • Click the Edit Rule button, to open the Edit Formatting Rule dialog box.

Hide the Numbers

You won’t make any changes in the “Select a Rule Type” at the top. Look at the settings in the lower section – “Edit the Rule Description”.

One simple tweak is to hide the cells’ numbers. This could be useful if you want people to focus on the “big picture”, rather than the details.

  • To hide the numbers, add a check mark to “Show Bar Only”

Excel Data Bars Show Bar Only

  • Then, click OK, to see the revised Data Bars.

Excel Data Bars with no numbers

Change Excel Data Bar Appearance

Another easy change is to select different options in the “Bar Appearance” section.

  • The Fill can be Gradient or Solid, and you can pick a colour from the standard palette, or from the “More Colors” screen.
  • The Border can be Solid, with any colour choice, or choose No Border

Excel Data Bars appearance settings

  • A Preview, at the bottom right of the dialog box, shows what the revised Data Bars will look like.
  • When you’re happy with the appearance, click OK

databars10

Data Bar Minimum

You can also change the Minimum and Maximum settings for the set of Data Bars. By default, both are set to “Automatic”. From my experience, that results in a Minimum of zero, and a Maximum of the highest value.

The Automatic settings are fine for some data, but you can change them. For example, if there is only a small difference among the numbers, as in the screen shot below, you might want to highlight that differences.

small difference among the numbers

To focus on the differences in this example, change the minimum setting.

  • Open the Edit Rule dialog box
  • For Minimum, click the Type arrow, and choose Number
  • In the Value box, type 10000

databars12

  • Click OK, to close the dialog box

The Data Bars change – range is only 357, instead of 10357. so you can clearly see the small difference among the numbers.

NOTE: Using a hard-coded value of 10000 worked well in this case, but hard coding wouldn’t be a good solution if the numbers will change. See the next section for another option.

databars13

Data Bar Maximum

If you use the default Maximum setting, the bar for the highest value fills the cell. If the numbers are showing, and the bars have a dark fill colour, it will be difficult to read some of the numbers

To fix that problem, change the Maximum, to leave  a space at the right end of the bar. Instead of hard-coding a Maximum value, we’ll use a flexible solution.

  • Open the Edit Rule dialog box
  • For Maximum, click the Type arrow, and choose Formula
  • In the Value box, type this formula: =MAX($C$4:$C$9)*1.3

databars17

  • Click OK, to see the result

The MAX function finds the highest number in the range of cells, and that amount is multiplied by 1.3.  That creates enough space for the numbers at the right of the cell.

databars18

Use a Minimum Formula

You could use a similar formula for the Minimum, instead of a hard-coded number. Use MIN, and a multiplier that is less than 1, so a bit of the smallest bar remains visible.

For example, with the set of large numbers shown above, use this formula:

=MIN($C$4:$C$9)*0.99

Use a Cell Reference

For even more flexibility in the Maximum or Minimum formulas, enter the multiplier in a worksheet cell, then refer to that cell in the Data Bars formula.

In the screen shot below, the multiplier is in cell E1 – you could use a cell on a different sheet, if you prefer.

The formula for this Maximum is:  =MAX($C$4:$C$9)*$E$1

Then, if you need a little more space for the numbers, increase the multiplier in cell E1. That’s quicker than going back into the Edit Rules dialog box to make the adjustment.

databars19

Video: Excel Data Bars

This video shows how to set up Excel Data Bars, and change their default settings.

Your browser can’t show this frame. Here is a link to the page

Download the Sample File

Learn more about Excel Data Bars on my Contextures website, and download the sample file on that page, to see how data bars work.

The zipped file is in xlsx format, and does not contain any macros.

_________________________

Save

Save

Save

The post How to Fix Excel Data Bars Appearance appeared first on Contextures Blog.

See more about How to Fix Excel Data Bars Appearance

Instructor Lead On-Demand Learning Courses - August Big $10 Sitewide Extravaganza All Pro Web Designs and Udemy are happy to offer this special to you, good only on dates: 08/21-08/31 Act Now!

Leave a Reply