• Excel Book Excerpt

Excel Convert a Table of Numbers to a Visualization

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Convert a Table of Numbers
to a Visualization

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?

LE10001135.jpg 

Figure 1103 Help your manager to understand this data.

Strategy: You can use one of the three new 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. You can see which cells have the largest values by seeing which cells 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 solid. You can choose More Rules to add any of 16 million colors.

LE10001136.jpg 

Figure 1104 Choose a color for the data bars.

Below, you can see that Wednesday is the busiest day. Calls fall of on Friday for everyone except Missy. Missy is consistently the strongest performer.

LE10001137.jpg

Figure 1105 Easily spot trends in the data.

Gotcha: 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. Below, the 904 in cell G6 makes all the cells in B2:F5 look relatively the same.

LE10001138.jpg

Figure 1106 Don’t include totals in a visualization.

Additional Details: You can use color scales to apply a mix of colors to a range. Excel offers built-in three-color scales such as red-yellow-green as well as 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, the largest numbers are in the darker green, and the smallest numbers are in the lighter yellow.

LE10001139.jpg

Figure 1107 Each cell is lighter or darker based on size.

The final new visualization is icon sets.

In Excel 2010, there are 20 sets of icons. Some have three symbols, others have four, and some have five. The items in a red box in this figure are new in Excel 2010 and will not render when opened in Excel 2007.

LE10001141.jpg

Figure 1108 Twenty icon sets are available.

Note that for many of these sets you need to print 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.

After you choose an icon set, Microsoft will display 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.

LE10001140.jpg

Figure 1109 Indent numbers to move them closer to the icons.

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!

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:



Popular Pages

More Info