Microsoft

Pro tip: Two Excel conditional formats that simulate graphs

You can use conditional formatting to display simple graphs at the cell level. Susan Sales Harkins explains how.

Thanks to conditional formats, graphs can be embedded at the cell level. Well, they're not true graphs, but the visual impact is the same. You're simply sharing visual clues that make it easier to interpret your raw data. In this article, we'll use Excel's conditional formats, data bars, and color scale to simulate graphs that you can display along with your raw data.

You can work with a simple data range or download the .xlsx file. These formats are supported by the .xlsx format only.

Data bars

Data bars compare values by extending a band of color across a single cell, reflecting the value stored in that cell. By viewing a group of banded cells, you can quickly glean a lot of information. For instance, Figure A shows the same data range, one without the data bars and one with. Which is more effective visually?

Figure A

Figure A

Data bars, or not?

To generate these bars, do the following:

  1. Select the values you want to format.
  2. Click the Home tab (if necessary). In the Styles group, click Conditional Formatting.
  3. Choose Data Bars.
  4. In the resulting submenu, pick a style and color, as shown in Figure B. You can choose a gradient or solid fill. The options in each section offer different colors.

Figure B

Figure B

Choose a style and color.

If the resulting bars aren't what you expected, consider this: data bars aren't zero-based; the narrowest bar represents the lowest value in the data range and the widest bar represents the highest value. In the example data range, the West region has the lowest figure while the East has the largest. The bars reflect the comparison of the values as they relate to the lowest and highest values. The actual value is less important than the comparison of the values to one another.

Depending on what the values represent, the default bars might not be adequate. In this case, you can change the lowest or highest value, or both. Let's change the highest value to 100 and see how the data bars update:

  1. Select the bars (B2:B5).
  2. Click Conditional Formatting, and then choose Manage Rules.
  3. Select the rule (in this case, it's the only rule), and then click Edit Rule, shown in Figure C.
    Figure C
    Figure C
  4. In the resulting dialog, choose Number from the Maximum drop-down menu.
  5. Enter 100, as shown in Figure D.
    Figure D
    Figure D
  6. Click OK twice to see the results in Figure E.
    Figure E
    Figure E

This change makes one thing more apparent: 60 isn't the highest possible value. Their relationship to one another hasn't changed, but they do tell a different story. Depending on your data, changing the minimum value might be a better choice -- or you might even change both the maximum and the minimum.

As you can see in Figure D, there many options. You'll want to spend some time experimenting to see how changing one of these options updates the actual bars and improves (or not) the point you hope to make visually. Some changes won't be appropriate for the story you want the data to tell, so choose wisely.

There's one option I want you to see before we move on to color scale. Specifically, you can turn the data bars into a more traditional graph by turning off the display of the values as follows:

  1. Repeat steps 1 through 3 above.
  2. In the resulting dialog, check the Show Bar Only option.
  3. Click OK twice to see the results in Figure F.

Figure F

Figure F

Display only the data bars.

Color scale

Color scales create a visual comparison of your values, but they do so by applying colors in a two- or three-color set. The intensity of the cell's color reflects the value's position to the highest and lowest values in the range. In doing so, this format can be helpful in spotting trends. Figure G shows the same data in a two- and three-color scale. In such a simple data set, the true value isn't apparent.

Figure G

Figure G

Color scale.

To apply this conditional format, do the following:

  1. Select the data (B2:B5).
  2. Click the Home tab (if necessary) and choose Color Scale from the Conditional Formatting drop-down.
  3. Choose from one of the 12 built-in settings. The first six are two-scale; the second six are three-scale.

When you choose three-scale, the different colors won't always be obvious. Choose the scale that best represents your data. In this case, the three-scale (shown on the right in Figure G) appears to be a two-scale. If you were to add more records, the difference would be more obvious. While the color scale format is useful in specific data sets, you'll probably find it less effective, in general, than the data bars. Keep in mind that this format corresponds to minimum, midpoint, and maximum thresholds, so unless this context is important to your data, color scaling might not be the right choice.

Now that I've shown you an example that isn't particularly effective, let me show you data where it is effective. Figure H shows a two- (High) and a three-scale (Low) pattern that exposes trends. In this case, the colors quickly tell you that temperatures are warmer in the summer months. (It's a contrived example, but it makes my point easily.)

Figure H

Figure H

Use color scale formatting to distinguish trends.

The color scale format has fewer options for customizing, but like data bars, you can change the minimum and maximum number. You can also specify colors that represent your data, if color can be inferred from your data. For instance, in the three-scale format, you might use blue to represent the cooler temperatures.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible: For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

1 comments
2bczar4u
2bczar4u

I haven't worked too much with the conditional formatting feature but I think I may just have to.  Thank you.