Spreadsheets

# Excel Tip: Bars, Scales, and Sets

Convert a table of numbers to a visualization.
Bill JelenJuly 27, 2015

User Problem: My manager’s eyes glaze over when he sees a table of numbers. Is there anything I can do to help him spot trends in the data?

Solution: Use one of the three data visualization tools on the Conditional Formatting menu: data bars, color scales, and icon sets. Adding a data bar to a range adds an in-cell bar chart to each cell. The cells with the largest values have the most color.

To add data bars, you select a range of numbers and then select Home, Conditional Formatting, Data Bars, choose a color. Excel 2007 offered six built-in colors. Excel 2010 offers six gradients and six solids. You can choose More Rules to add any of 16 million colors. (Figure 1, left).

Below, you can see that Wednesday is the busiest day. Calls fall off on Friday for everyone except Missy. Missy is consistently the strongest performer (Figure 2, below).

CAUTION: You should not include any total cells in your selection when applying conditional formatting. The relative size of the totals would make all the detail numbers receive small bars. The 904 in cell G6 (Figure 3) makes all the cells in B2:F5 look relatively the same.

Use color scales to apply a mix of colors to a range. Excel offers built-in three-color scales in addition to two-color scales.

The two-color scales look better than three-color scales when printed in monochrome. You can also use More Rules to design your own color scheme. Below in Figure 4, the largest numbers are in the darker green, and the smallest numbers are in the lighter yellow.

The final new visualization is icon sets.

In Excel 2010 there are 20 sets of icons. Some have three symbols, others have four, or five. The items in a red box in Figure 5 (below, left) are new in Excel 2010 and will not render when opened in Excel 2007.

Note that for many of these sets you need to print them in color in order for the reader to differentiate the symbols. If you are printing in monochrome, the arrows or power bars are good choices to increase clarity.

After you choose an icon set, Microsoft displays the icon at the left of each cell. Since numbers are usually right-aligned, the number from B2 and the icon from C2 are too close together and many will think that they go together.

I’ve begun using Ctrl+1 to visit the Format Cells dialog. On the Alignment tab, use a horizontal alignment of Right (Indent). You can then increase the indent to 2 or 3 to move the numbers closer to their icons (Figure 6, below.)

The icons won’t respond to the horizontal alignment of the cell, unless you use Home, Conditional Formatting, Manage Rules, Edit Rule, Show Icon Only. Ironically, when you use this setting, the icon responds to the Left, Center, and Right Align buttons in the Home ribbon!

This article first appeared in the October 26, 2014, edition of the CFO LearningPro, Excel Edition, newsletter. Click here to see a video explaining the solution.

CFO contributor Bill Jelen. a.k.a. Mr. Excel, is an Excel MVP and the author of 35 books about Microsoft Excel.