Data Visualizations in Excel 2007

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Member of the National Speakers Association

Data Visualizations in Excel 2007

The tip in this show is from Excel 2007 Miracles Made Easy.
(Click the book for more details)

To watch the video, use this control:

Excel 2007 offers amazing new data visualization tools such as icon sets, data bars, and color scales. These are great for the manager who's eyes glaze over when presented with a table of numbers.

Data bars are tiny cell-sized bar charts that can be applied to a range of numbers.
Color scales allow you to apply a gradient to a range of numbers; think largest numbers in green, smallest numbers in red
Icon sets allow you to apply traffic light icons to a range of numbers.

To apply a color scale, select a range then choose, Home, Conditional Formatting, Color Scales, and a color scheme. The 4 choices in the top row are three color schemes and work best when displayed in color. The four choices on the bottom are 2-color schemes and work better when printing.

For an icon set, select a range of numbers then choose Home, Conditional Formatting, Icon Sets. The 17 built-in schemes offer a variety of 3, 4, and 5 icon sets. Some of them are only good if you print in color. (For example, the red, yellow, green traffic light will not look good on a black-and-white printout. This icon set offers different shapes (checkmark, exclamation, x) in different colors and looks good whether you are printing or displaying the worksheet.

Data bars provide an in-cell bar chart for each cell. The largest numbers get the largest swath of color and the smaller numbers get a smaller swatch of color. Choose Home, Conditional Formatting, Color Bars, and a color.

Note: For any visualization, choose More from the flyout menu and you can define your own custom colors.

Showing Only Checkmarks
In the segment, I show how to create an icon set where only the top icon is shown. Follow these steps:

Apply an icon set as usual. All three icons will appear. Note the smallest number with a green checkmark.
Use Home, Conditional Formatting, New Rule. Choose Format Only Cells that Contain. In the bottom, choose Cell Value, Less Than, and the number just below the lowest green checkmark. Don't apply any formatting in this rule!
Go back to Home, Conditional Formatting, Manage Rules. The most recent rule (Cell Value < 35000) will be on top. Click the checkbox to Stop If True:

The result…for cells less than 35000, Excel will apply no formatting and stop. Cells above 35000 will proceed to the icon set, and you will have only checkmarks.

I really hope to give away 5 million copies of the book, so please send your co-workers and friends to leobook.html.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.