In This Chapter
Using the new data visualization conditional formatting options
Creating charts by using formulas and text characters
Creating sparklines by using tiny Excel charts
Identifying commercial add-ins for in-cell charting
This chapter describes some charting techniques that you may find useful. You'll find a variety of data visualization examples that go beyond traditional charting.
Conditional formatting has improved significantly in Excel 2007, and it's now a useful tool for visualizing numeric data. In some cases, you may be able to use conditional formatting in lieu of a chart.
To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home
Note
This type of conditional formatting is new to Excel 2007, and it is not compatible with previous versions of Excel.
Figure 9-1 shows a few examples of conditional formatting that are relevant to visualizing data.
The data bars conditional format displays horizontal bars directly in a range of cells. By default, the length of the bar is based on the value in the cell relative to the other values in the range. Data bars are easy to apply and allow you to quickly see outliers, spot trends, and compare values.
Figure 9-2 shows a range with student names and test scores. Data bars are applied to the second column.
Note
The examples in this section are available on the companion CD-ROM. The filename is data bars examples.xlsx.
Warning
Using data bars can be a bit deceptive because Excel does not use a zero base. For example, in Figure 9-2 compare the length of the bars in rows 11 and 12. Most people would expect that a data bar for the value 100 (row 12) would be twice as long as the data bar for a value of 50 (row 11). Clearly that is not the case. The result is similar to creating a bar chart that doesn't use zero as its minimum scale value.
Adding data bars to a range is simple. Follow these steps:
Select the range of values (in this example, B2:B23).
Choose Home
For more control over the data bars, choose Home
To display the data bars only (no values), select the Show Bar Only check box. You can also specify how the bars are generated by using the Type drop-down lists. By default, the shortest bar represents the lowest value in the range, and the longest bar represents the highest value. Other options are as follows:
Number: Enter a value. For the shortest bar option, if a number is lower than the specified value, Excel displays the shortest bar. For the longest bar option, if a number is greater than the specified value, Excel displays the longest bar.
Percent: Enter a percentage value between 0 and 100. Bar length is based on the numeric range of the data. For example, if your data ranges from 1 to 5, 000 and you specify 20% as the shortest bar, cells that have a value less than 1, 000 (that is, 20% of 5, 000) display the shortest bar.
Formula: Enter a formula that evaluates to a value. The formula must use absolute cell references.
Percentile: Enter a percent value between 0 and 100. Unlike the Percent option, the Percentile option rank-orders the data and bases the bar length on the ordinal position. This option is useful if you'd like to identify values that appear in a particular percentile — use the longest bar for all values above the 80th percentile.
The New Formatting Rule dialog box also lets you specify the color of the data bars. Also, keep in mind that you can apply a fill color to a range that uses data bars. In other words, you can display colored bars on top of a colored range.
Warning
If you need to modify the conditional formatting for a range, choose Home
Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. Figure 9-4 shows a range of data with data bars (range (D4:D15) and a bar chart created from the same data. The bar chart might take a bit more time to create, but it's a lot more flexible. But for a quick-and-dirty chart, data bars are a good option — especially when you need to create several such charts.
Note, however, that the data bar display looks quite a bit different than the bar chart. That's because the data bars don't use zero as the base. In this case, the minimum scale value is approximately 2, 400. Unfortunately, Excel does not provide a way to specify the scaling for data bars.
Figure 9-5 shows a worksheet that uses data bars to display the results of a dozen survey items. Applying conditional formatting was much faster than creating 12 separate charts.
Note
In this example, all the data bar cells are Part of the same conditional formatting range. Each survey item is not a separate conditional formatting range. As a result, each item uses the same scale, and it's easy to compare the items. To select a noncontiguous range of cells prior to applying conditional formatting, press Ctrl while you select the ranges with the mouse.
Conditional formatting data bars are useful, but they do have some limitations:
Data bars always display as a dark-to-light gradient, and you can't specify a solid color or a different gradient.
Data bars don't handle negative numbers the way you might like. People are accustomed to viewing a negative value in a bar chart to extend in the opposite direction. This is not the case with data bars.
In some cases, you might prefer that the data bar is hidden for a zero value. Every cell in a range that's formatted with data bars will display a bar — even zero values.
Data bars are not accurate, and they can even be deceiving. Figure 9-6, for example, shows a set of data bars in a range. A viewer might expect that each data bar would be 10 times longer than the data bar in the cell above. Clearly, that's not the case.
The color scale conditional formatting option varies the background color of a cell based on the cell's value, relative to other cells in the range. Figure 9-7 shows a range of cells that use color scale conditional formatting. It uses a two-color scale (black to white) and is set up such that higher scores have a lighter color.
Note
The examples in this section are available on the companion CD-ROM. The filename is color scale examples.xlsx.
Applying a color scale is as easy as applying data bars. Follow these steps:
Select the range of values (in this example, B2:B23).
Choose Home
Excel provides four 2-color scale presets and four 3-color scales presets. For more control over the colors, choose Home
Figure 9-8 shows another color scale example. The worksheet depicts the number of employees on each day of the year. This is a 3-color scale that uses red for the lowest value, yellow for the midpoint, and green for the highest value. Values in between are displayed using a color within the gradient. The grayscale figure doesn't do this example justice. The effect is much more impressive when you view it in color, and it's very easy to spot staffing trends.
It's important to understand that color scale conditional formatting uses a gradient. For example, if you format a range using a 2-color scale, you will get a lot more than two colors. You'll get colors with the gradient between the two specified colors.
Figure 9-9 shows an extreme example that uses color scale conditional formatting on a range of 10, 000 cells (100 rows × 100 columns). The column width was adjusted so that each cell is a square. The worksheet is zoomed down to 30% to display a very smooth 3-color gradient. The range contains formulas like this one, in cell C5:
=SIN($A2)+COS(B$1)
Values in column A and row 1 range from 0 to 4, in increments of 0.04. Change the value in cell A1, and the colors change instantly. This type of gradient contour display can be a good alternative to a surface chart. Figure 9-10 shows the same data, displayed as a surface chart.
The result, when viewed on-screen, is stunning (it loses a lot when converted to grayscale).
The third new conditional formatting option displays an icon in the cell. The icon depends on the value of the cell, relative to other cells in the specified range. Excel provides 17 icon sets to choose from (and you can't create your own set of icons). The number of icons in the sets ranges from 3 to 5.
Figure 9-11 shows an example that uses the icon set named 3 Symbols (Uncircled). The symbols graphically depict the sales volume of each salesperson, relative to the group as a whole.
Note
The examples in this section are available on the companion CD-ROM. The filename is icon set examples.xlsx.
Follow these steps to apply an icon set to a range:
Select the range of values (in this example, B3:B14).
Choose Home
If you would like more control over how the icons are assigned, choose Home
If you don't use the New Formatting Rule dialog box, the symbols are assigned using percents. In Figure 9-11, notice that only one value has a check mark. That's because Frank's sales are significantly higher than the others. The algorithm used to convert values into percents is as follows:
percent = (value –minimum) / (maximum –minimum)
Using this algorithm, Frank's sales is the only value in the range that exceeds 67% (the minimum value for the third icon). If you would like an equal number of symbols, use the Percentile option.
Tip
Unfortunately, the icons always appear on the left side of the cell. If you have multiple columns of data, it's very easy to mistakenly associate an icon with the value in the cell to the left. One solution to this problem is to create a separate column for the icons, to the right of the data. Figure 9-13 shows an example. In the table on the left, the icons seem to be associated with the Day 3 column, rather than with the Total column. Column K contains simple references to the values in Column J.
Also, if an icon occupies a cell in which the value is not displayed, the icon responds to horizontal alignment settings (left, center, or right). If the value is displayed, only the value is aligned.
Figure 9-14 shows another icon set example. The table contains two test scores for each student. The Change column contains a formula that calculates the difference between the two tests. The Trend column uses an icon set to display the trend graphically.
This example uses the icon set named 3 Arrows, and I customized the rule as follows:
Up arrow: When the value is >= 5
Level arrow: When the value is < 5 and >= −5
Down arrow: When the value is < −5
In other words, a difference of five points or fewer in either direction is considered an even trend. An improvement of more than five points is considered a positive trend, and a decline of more than five points is considered a negative trend.
Note
The Trend column contains a formula that references the Change column. I used the Show Icon Only option in the Trend column.
In some cases, you might want to display only one icon from an icon set. Excel doesn't provide this option directly, but you can display a single icon if you use two rules. Figure 9-15 shows a range of values. Only the values greater than or equal to 80 display an icon.
Here's how to set up an icon set such that only values greater than or equal to 80 display an icon:
Select the cells, choose Home
With the range selected, choose Home
Click the Edit Rule button to display the Edit Formatting Rule dialog box.
Change the first icon setting to When Value Is >= 80 and specify Number as the type. Leave the other icon settings as they are, and click the OK button to return to the Conditional Formatting Rules Manager dialog box.
Click the New Rule button, and then choose this rule type: Format Only Cells That Contain.
In the bottom section of the dialog box, specify Cell Value Less Than 80 and click the OK button to return to the Conditional Formatting Rules Manager dialog box. The range now has two rules.
For the first rule, select the Stop If True check box. Figure 9-16 shows the completed dialog box.
Click the OK button.
The first rule checks whether the value is less than 80. If so, rule checking stops, and no conditional formatting is applied. If the value is greater than or equal to 80, the second rule kicks in. This rule indicates that values greater than or equal to 80 are displayed with an icon.
Figure 9-17 shows one more icon set example. Sales data is entered into a calendar display at the top, and an icon set indicates the relative sales on a daily basis. Each cell in the lower calendar contains a formula that references the corresponding cell in the upper calendar. The icon set uses the Show Icon Only option.
This section presents two methods of creating charts directly in a range. The examples use formulas and text characters to display data visually. As you'll discover, this technique does not offer the level of precision available in a "real" chart, but it does have several advantages over the data bars discussed earlier in this chapter.
Figure 9-18 shows a simple example. Column D contains formulas that convert the value in column C to a series of "pipe" characters (that is, the vertical-line character usually located on the backslash key). The formula in cell D3 is as follows:
=REPT("|",C3/10000)
Note
The examples in this section are available on the companion CD-ROM. The filename is text character charts.xlsx.
The key to this technique is the use of the REPT function, which displays a character (specified in the function's first argument) the number of times specified in the function's second argument. Notice that the formula "scales" the value by dividing it by 10, 000. Without this scaling, the formula would attempt to display 783, 832 characters. The scaling factor you use will depend on the magnitude of the data. But, in all cases, the scaling factor should be the same for all values that make up the chart.
Figure 9-19 shows another example. This chart uses a character from the Wingdings 2 font. The scaling factor is 400 (each character represents 400 units of sales). As you can see, this type of chart has limited precision. For example, Fanny and Georgette both have the same number of characters, even though their sales amounts differed by 399 units. In many situations, absolute accuracy isn't critical.
Figure 9-20 shows another in-cell chart that displays percent values in a crude "dot chart." In this case, the scaling is done by multiplying the value. For example, the formula in cell I3 is as follows:
=REPT(" ",H3*600)&"O"The REPT function generates a series of space characters, and an uppercase letter O is appended to the string. Figure 9-20 also shows a variation that substitutes a hyphen character for the space character.
As you may have figured out, the possibilities for in-cell charting are virtually limitless. Experiment with different characters, scaling factors, and font sizes. You can even toss in some conditional formatting to vary the colors based on the values.
The example in Figure 9-21 uses formulas that are slightly more complex. Columns F and H contain formulas that graphically depict monthly budget variances by displaying a series of characters in the Wingdings font.
The data used in this chart is in columns A:C. Formulas in column D calculate the percent difference between the Budget and Actual amounts. Columns F and H contain formulas that use the value in column D. The formulas for columns F and H follow. I copied these formulas down to accommodate the 12 rows of data:
E2: =IF(D2<0, REPT("n",-ROUND(D2*100, 0)),"")
G2: =IF(D2>0, REPT("n",-ROUND(D2*-100, 0)),"")The cells that display the bars use the Wingdings font — the letter n in this font produces a rectangular block. In column F, the text is aligned to the right. In column H, the text is aligned to the left.
The final example in this section is shown in Figure 9-22. It's a Gantt chart that displays a project schedule graphically in a range by using formulas. Column B contains the Start Date for each task, and column C contains the duration of the task. Formulas in column D calculate the End Date for each task. Formulas, beginning in column F, display a character that represents a day in the task.
The formula in cell F2, which is copied to all other cells in the chart range, is as follows:
=IF(AND(F$1>=$B2, F$1<=$D2),"n","")
Row 1, above the chart, contains consecutive dates that begin with the earliest project date and end with the latest date. Note that the columns are so narrow, these dates aren't even visible. The formula uses an IF function to determine whether the date value for the column is between the Start Date and the End Date. If so, the cells display the character. The cells are formatted using the Wingdings font, so the n character displays as a small rectangle.
Note
Refer to Chapter 8 for instructions on creating a Gantt chart from a bar chart.
Edward Tufte coined the term sparkline to refer to a minimalist chart that appears in line with text. A sparkline can be described as a tiny graphic that reveals a general trend. Often, several sparklines are displayed together, and this type of chart is often used in dashboard applications.
Excel doesn't officially support sparkline charts, but you can create such charts by using text, or with an actual (very small) chart. The sections that follow describe both techniques.
Figure 9-23 shows three examples of sparkline charts that depict the sequence of games won and lost by four teams. These charts use identical formulas. The only difference is the characters used to represent games won and games lost.
Note
The examples in this section are available on the companion CD-ROM. The filename is sparkline text charts.xlsx.
Note
The graphic characters used in these charts are from the Arial Unicode MS font. I used the Symbol dialog box (choose Insert
The formula in cell B3 is as follows:
=SUBSTITUTE(SUBSTITUTE(C3&D3&E3&F3&G3&H3&I3&J3&K3&L3, "W","_"),"L","_")
This formula concatenates the W and L characters in columns C through L, and then substitutes a character for the W and a different character for the L. The characters used in the formulas are as follows:
Example 1: Character code 2534 and character code 252C, from the Box Drawing subset of the Arial Unicode MS font
Example 2: Character code 25A0 and character code 25A1, from the Geometric Shapes subset of the Arial Unicode MS font
Example 3: Character code 2584 and character code 2580, from the Block Elements subset of the Arial Unicode MS font
Using characters to display a sparkline chart is appropriate when the data values take on only a very limited number of values. The Arial Unicode MS font contains characters that display columns of various heights (character codes 2581 through 2588). If you don't mind complicated formulas, you can use these characters to create sparkline charts.
Figure 9-25 shows some sparkline charts that use eight characters to display columns. The formula bar displays the formula in cell B27. As you can see, the formula is lengthy and rather complicated. However, it accurately depicts the data, which ranges from 1 to 8.
If you like the idea of sparkline charts, but require more flexibility than the text-based sparklines described in the previous section, the solution is to use a real Excel chart. Figure 9-26 shows a few examples of charts as sparklines: column charts, column charts with no gap, line charts, area charts, pie charts, and 100% stacked bar charts.
Note
The examples in this section are available on the companion CD-ROM. The filename is sparkline chart examples.xlsm. This workbook contains the PositionAndSizeChartsByName macro, described later in this chapter.
To create a cell-sized chart, follow these steps:
Create a normal-sized chart as usual. For best results use only one data series.
Delete all chart elements from the chart, except the series itself.
Make the chart area and plot area transparent (no fill).
Remove the border from the chart area.
Make the chart's plot area as large as possible so that it fills the entire chart area.
Adjust the colors and series formatting.
If you will be displaying multiple sparkline charts that will be compared with one another, you might want to make the value scale use fixed values rather than automatic values.
Drag a corner of the chart and reduce its size until it's the size of a cell.
Following are some general tips for creating tiny sparkline charts:
After you've reduced the size of the chart, you'll find that it's much easier to position the chart (and modify its formatting) if you increase the zoom level of the worksheet. Use the Zoom controls on the right side of the status bar. Also, press Alt while moving the chart to enable "snap to grid."
If you need to create multiple charts, create the first and then make a copy of the chart for each additional range. After you've made the copy, change its data range by selecting the series and dragging the outline that appears around the range.
By default, charts change their size if you change the column width or row height. In addition, if you copy or move the underlying cell, the chart is also copied or moved. If, for some reason, you don't want a chart to move and size with cells, you can change this setting on the Properties tab of the Size and Properties dialog box. To display this dialog box, activate the chart and then click the dialog box launcher in the Chart Tools
Chart-Positioning and -Sizing Macro
Sizing and positioning a tiny sparkline chart can be very frustrating. I created a VBA macro to help.
Following is a macro that can size and position all charts in the active worksheet. Before you use this macro, you must rename your charts so that each name corresponds to the cell over which it will be placed. For example, if you'd like a chart to be positioned above cell F32 and be exactly the same size as cell F32, name the chart CellF32.
Note
To change the name of a chart, select the chart and use the Chart Name control in the Chart Tools
If a chart's name doesn't adhere to the naming convention I described, its size and position are not changed by the macro.
Sub PositionAndSizeChartsByName()
Dim ChtObj As ChartObject
Dim ULC As Range
For Each ChtObj In ActiveSheet.ChartObjects
On Error Resume Next
If UCase(Left(ChtObj.Name, 4)) = "CELL" Then
Set ULC = Range(Right(ChtObj.Name, Len(ChtObj.Name) −4))
If Err.Number = 0 Then
ChtObj.Left = ULC.Left
ChtObj.Top = ULC.Top
ChtObj.Width = ULC.Width
ChtObj.Height = ULC.Height
End If
End If
Next ChtObj
End SubFigure 9-27 shows seven charts of various sizes. These charts use the data in the preceding rows (one chart per row of data). The charts are named to correspond to their target cell. The names are CellB3, CellB4, CellB5, and so on.
Figure 9-28 shows the worksheet after executing the PositionAndSizeChartsByName macro.
A Sparkline Add-In
I created a simple Excel 2007 add-in that can facilitate creating sparkline charts. Figure 9-29 shows the dialog box for this add-in. You specify the range of cells that will contain the charts and the range of data for the charts. In addition, you can specify fixed minimum and maximum values for the chart. The chart types are limited to the most commonly used: column charts (including an option with no gaps), line charts, and area charts.
Note
A copy of the Sparkline Generator add-in is available on the companion CD-ROM. The file is named sparkline generator.xlam. To install the add-in, copy it to your hard drive. Then press Alt+TI to display the Add-Ins dialog box. Click the Browse button and locate the sparkline generator.xlam file. When the add-in is installed, you will see a new Ribbon command: Insert
Figure 9-30 shows a worksheet that contains several sparkline charts created by the add-in.
I'm aware of two Excel add-ins that add support for sparklines:
MicroCharts, from Bonavista Systems (
www.bonavistasystems.com)SparkMaker, from Bissantz & Company (
www.bissantz.com)
Both products provide a wide variety of in-cell charting possibilities, and neither of them uses "real" Excel charts. Both have a trial version that you can download for free.
Figure 9-31 shows some in-cell charts created by MicroCharts.
