9. Using Sparklines, Data Visualizations, and Other Nonchart Methods

Edward Tufte wrote about small, intense, simple data words in his 2006 book, Beautiful Evidence. Tufte called them sparklines and produced several examples where you could fit dozens of points of data in the space of a word. Six months later, the Excel team began planning for Excel 2010 and Tufte’s concepts made it into Excel 2010.

Sparklines join other Excel 2007 additions such as:

• Data bars—This tool offers a tiny, in-cell bar chart for each number in a range. The reader’s eye is drawn to the largest numbers because of the size of the bars.

• Color scales—Color scales, also called heat maps, color cells in a range of colors. You can use one of six built-in schemes where red is high and blue is low, or green is high and red is low, or you can define your own color scheme.

• Icon sets—You can now add a tiny icon next to each number in a range. Icons can use a traffic light metaphor, power bar metaphor, or arrow metaphors to show which data points are the best performers in a dataset.

With each of these new data visualizations, Excel requires only three clicks to get a result. However, each visualization offers advanced settings that give you finer control.

This chapter starts by discussing the new sparklines, then data visualizations and finally some of the legacy data visualizations, such as using the REPT function to build bar charts in cells.

Fitting a Chart Into the Size of a Cell with Sparklines

Excel’s implementation of sparklines offer line charts, column charts, and a win/loss chart. See Figure 9.1 for an example of each.

• Win/Loss—The 1951 Pennant Race in Rows 7 and 8 show two examples of a Win/Loss chart. In this case, each event in a baseball game is represented by either an upward facing marker to indicate a win, or a downward facing marker to indicate a loss. This type of chart shows winning streaks. The final three games were the playoff between the Dodgers and the Giants, with the Giants winning 2 games to 1.

• Sparkline—The sparkline in Row 12 shows 120 monthly points of the Dow Jones Industrial Index showing the closing price for each month in one decade.

• Sparkcolumns—Rows 16 through 21 compare monthly high temperatures for various cities. The minimum and maximum values for each city are marked in a contrasting color. Curitiba, in the southern hemisphere has its warmest month in February.

Figure 9.1 Excel 2010 offers three types of sparklines.

Sparklines can exist as a single cell such as the Dow Jones example or as a group of sparklines such as the temperature example. When sparklines are created as a group, you can specify that all of the sparklines should have the same scale or that they should be independent. There are times where each is appropriate.

The sparkline feature offers the ability to mark the high point, the low point, the first point, the last point, and/or all negative points.

There is no built-in way to label sparklines. However, sparklines are drawn on a special drawing layer that was added to Excel 2007 to accommodate the data visualizations discussed later in this chapter. This layer is transparent, so with some clever formatting, you can add some label information in the cell behind the sparkline.

Creating a Group of SparkLines

The worksheet in Figure 9.2 includes a decade of leading economic indicators. You want to add sparklines to the table.

Figure 9.2 Add space in your table for the sparklines.

1. Insert a blank column between Columns A and B. This will provide room for the sparklines to appear next to the labels in Column A. You might find that you do not need to print the table of numbers, just the labels and sparklines will suffice.

2. Select the data in C5:L8. Note that you do not include any headings in this selection.

3. On the Insert tab, select Column from the Sparkline Group. Excel displays the Create Sparklines dialog.

4. Select B5:B8 as the location range, as shown in Figure 9.3.

5. Click OK to create the default sparklines.

As shown in Figure 9.4, the sparklines have no markers. They are scaled independently of each other. The unemployment max of 9.3 reaches nearly to the top of Cell B5, indicating the maximum for Unemployment is probably about 10. By contrast, the maximum for GDP in B6 is closer to 14,500.

The Show group of the Sparkline toolbar allows you to mark certain points on the line. In Figure 9.5, the high point is marked with a dot. This one change adds a lot of information to the sparklines. New Construction peaked in 2006. GDP and Bank Credit peaked in 2008. Unemployment peaked in 2009. Did the drop in new construction in 2007 foretell the other items?

Figure 9.3 Preselect the data range, and then specify the location range.

Figure 9.4 Default sparklines have no markers and are autoscaled to fit the cell.

Figure 9.5 Adding a marker at the high point adds key information to the sparkline.

Built-In Choices for Customizing Sparklines

The Sparkline Tools Design tab offers several built-in choices for customizing sparklines:

• The Edit Data drop-down allows you to re-specify the data range for the source data and the location. If you have to add new data to existing sparklines, you can do so here. Generally, you would edit the location for the whole group, but the drop-down menu allows you to edit data for a single sparkline.

• The Type group allows you to switch between Line, Column, and Win/Loss charts.

• The Show group offers the second-most useful settings in the tab. Here, you can choose to highlight the High Point, Low Point, First Point, Last Points. Note that if there is a tie for High or Low point, both points in the tie will be marked. You can also choose to highlight all points and/or the negative points.

• For sparklines, any item that you choose in the Show group is drawn as a marker on the line. You can control the color for each of the six options using the Marker Color drop-down, discussed in the following section.

• For sparkcolumns, the markers are always shown. Choosing any of the five other choices will cause those particular columns to be drawn in a different color.

• For Win/Loss, you will generally always select Markers and Negative. This is how the losses show in a contrasting color from the wins.

Figure 9.6 shows examples of the various options:

• In Cell B3, the high, low, first, and last points are shown.

• In Cell B5, all markers are shown in the same color.

• When you select Markers and Negative, all points appear, but you can change the negative points to another color, as shown in Cell B7.

• In Cells B11 and B13, the chosen markers are shown in a contrasting color.

• Cell B9 and B15 show examples where the horizontal axis is shown. This helps to differentiate positive from negative. Note that the axis always appears at a zero location.

The Style gallery seems to be a huge waste of real estate. In the Office theme, it offers 36 ugly alternatives for sparkline color. This group also offers the Sparkline Color drop-down, which is the standard Excel 2010 color chooser. The color chosen here controls the line in a sparkline. The Marker Color drop-down is where you can control the color of the High, Low, First, Last, Negative points, as well as the default color for regular markers. Figure 9.7 shows the Marker Color drop-down.

The Group group is where you logically unlink a set of sparklines.

Any changes that you make on the Design tab apply to all of the sparklines in the group. This is usually a desired outcome. However, if you needed to mark the High point in one line and the Low Point in another line, you would want to ungroup the sparklines. You can also group sparklines or clear sparklines using icons in the Group group. The Axis drop-down appears in this group and contains the most important settings for sparklines. You will learn how to use the Axis drop-down in the next example.

Controlling Axis Values for Sparklines

Figure 9.8 shows a group of sparkcolumns showing the average high temperature for several cities. These cities are a mix of tropical and frigid cities.

Figure 9.6 Use the Show Group to highlight certain points.

Figure 9.7 Control the color of the chosen markers in this drop-down.

The default behavior of sparklines is that each sparkline in the group gets its own scale. This worked for the varying economic indicators shown previously in Figure 9.1. However, it does not work here.

When the vertical axis scale is set to Automatic, you can never really know the high and low of the scale in use. If you study the data and the sparkline for Trinidad, it appears as if Excel has chosen a min point of 84.8 and a max point of 89. Without any scale, you might thin that Trinidad in January is as cold as Chicago in January.

Figure 9.9 shows the options available in the Axis drop-down on the right end of the Sparkline Tools Design tab. The important settings here are the choices for the Minimum value and Maximum value.

Figure 9.8 The automatic vertical scale assigned to each sparkline does not work in this example.

Figure 9.9 Control the vertical axis using this drop-down.

If you change the min and max to the setting of Same For All Sparklines, then all six sparklines in this group will have the same min and max scale. The sparklines in Figure 9.10 initially look better. Juneau is never as warm as Tucson. However, you still do not know what the max and min values are. Take a close look at Chicago. It appears that the January high temperature is about zero, but the data table shows that the average high temperature in January is 29. You can estimate that these columns run from a min of 28 to a max of 101, based on looking through the data.

Figure 9.10 Force all sparklines to have the same vertical scale.

Figure 9.11 For absolute control, define a custom min and max value.

Setting Up Win/Loss Sparklines

The data for a Win/Loss sparkline is simple: Put a 1 (or any positive number) for a win. Put a -1 (or any negative number) for a loss. Put a zero to have no marker.

In Figure 9.12, you can see the data for a pair of Win/Loss sparklines. The 2 in Cell F3 does not cause the marker to appear any taller than any of the 1s in the other cells. It does cause the marker to appear as a different color if you choose to mark the high point. Maybe you can think of a use where you need to show two different colors among the wins or losses.

Figure 9.12 Datasets for wins and losses consist of 1s and -1s.

Showing Detail by Enlarging the Sparkline

Professor Tufte’s definition of sparklines included the word small. If you are going to be showing the sparklines on a computer screen, there is no reason that the sparklines have to stay small.

When you increase the height and width of the cell, the sparkline automatically grows to fill the cell. If you merge cells, the sparkline will fill the complete range of merged cells.

In Figure 9.13, the 279-game season of the Harlem Globetrotters seemed as if it needed more than just one cell, so Cells B2 and C2 were merged. In Cell B4, the row height was increased to 30 to show more detail.

Figure 9.13 Increase cell size or merge cells to increase the detail in the sparkline.

Labeling a Sparkline

The examples of sparklines created by Tufte in Beautiful Evidence almost always labeled the final point. Some examples included min and max values or a gray box to indicate the normal range of values.

Figure 9.13 shows labels for Min and Max to the left of the sparkline and a label for the final point to the right of the sparkline. Those labels are simply cell values. To create the label on the left, follow these steps:

1. Type the high value. Press Alt+Enter four times to move to the fifth line of text in the cell. Type the low value. Press Ctrl+Enter.
2. Format the cell as 8 point or smaller.
3. Use the Right Align and Top Align icons on the Home tab to position the labels at the right edge of the cell.

To label the final point, you can precede the value with the appropriate number of Alt+Enter keystrokes to vertically position the label close to the correct place.

If you set a row height equal to 110, you can fit ten lines of text in the cell using Alt+Enter. Even with a height of 55, you can fit five lines of text. This will allow the label for the final point to get near to the final point.

In Figure 9.14, the city labels are simply values typed in the same cell as the sparkcolumns. The max scale was set to 120 to make sure that there was room for the city name to appear. The Month abbreviations below the charts are “J F M A M J J A S O N D” in 6.5 point Courier New font. After trying both 6 point and 7 point and not having the labels line up with the columns, I ended up using 6.5 point and adjusted the column widths until the columns lined up with the labels.

Figure 9.14 Labels are created by typing in a small font in the cell.

In Figure 9.15, a semitransparent gray box indicates the acceptable limits for a measurement. In this case, anything outside of 95 percent to 105 percent is sent for review. Those gray boxes are simply Shapes from the Insert tab.

Some tips when setting up the box:

1. Temporarily change the first two points in the first cell to be at the min and max for the box.

2. Increase the zoom to 400 percent.

3. Draw a rectangle in the cell.

4. Use the Drawing Tools Format tab to set the outline to None.

5. Under Shape Fill, select More Fill Colors. Select a grey. Because shapes are drawn on top of the sparkline layer, drag the transparency slider up to about 70 percent transparent.

Figure 9.15 A gray box shows the acceptable range to help the reader locate items outside of the acceptable range.

6. Use the resize handles to make sure the top and bottom of the box go through the first and second points of the line.

7. After getting the box sized appropriately, reset the first two data points back to their original values.

8. Copy the cell that contains the first box. Paste onto the other sparkline cells. Because the sparklines are not copied, only the box will be pasted.

Using Data Bars to Create In-Cell Bar Charts

A data bar is a swath of color that starts at the side of a cell and extends into the cell based on the value of the cell. Small numbers get less color. The largest numbers might be 100 percent filled with color. This creates a visual effect that enables you to visually pick out the larger and smaller values. Figure 9.16 shows many examples of data bars.

Many new options are available in Excel 2010 data bars:

• Data bars can be solid or a gradient. In Excel 2010, the default gradient bar has a border. Tufte and others complained that the gradient in E14:E20 were misleading. The gradient is useful for helping to see the numbers behind the data bar. Contrast the solid bar in B2 and the gradient in B8. By adding the border around the gradient, Microsoft leaves no doubt where the data bar ends, but allows the numbers to show through.

Figure 9.16 The data bars illustrate many of the new properties in Excel 2010 data bars.

• Values of zero now actually get no data bar as shown in Cell E10. Previously, the smallest value would get 4 pixels of color.

• Data bars can now be negative. Negative bars are shown in a different color and usually extend to the left of a central axis. You have three choices in where to place the zero axis. In Cells B14:B20, the setting is Automatic. Because the largest positive number is further from zero than the smallest negative number, the axis appears slightly to the left of center. This allows the bar for 4.5 percent in B15 to appear larger than the bar for -3.3 percent in B17. You can also force the axis to appear in the center as in Cells C14:C20. Alternatively, in a bizarre setting, you can force the negative bars to extend in the same direction as the positive values, but with a different color.

There are two philosophical ways to show the negative bars. You can assign -3.3 percent the most color since it is farthest from zero, or you can assign -1.3 percent the most color since it is the mathematically the largest of the negative numbers (-1.3 percent > -3.3 percent).

• You can control the color of the positive bar, positive bar border, negative bar, negative bar border, and axis color.

• With Excel 2010, bars can now extend right-to-left, as shown in Cells C3:C10. This allows comparative histograms as in C2:E10.

The following options are not new in Excel 2010, but still remain from Excel 2007:

• You can specify the scale of the data bars. While the scale is initially set to automatic, you can specify that the min/max are set to a certain number or to the lowest value, a percentage, a percentile, or a formula.

• You can choose to show only the data bar and to hide the number in the cell. This is how words were included in Cells E14:E20. The numbers are hidden by the conditional formatting dialog, and then a linked picture of the words is pasted over the cells. Since the data bars are on a drawing layer above the regular drawing layer, this works.

• You can format the number in the cell with a custom number format of specific text. For example, a custom number format of “Akron” in cell E14 will always show the word Akron in the cell, no matter what number is typed in the cell.

• All data bars in a group have the same scale. This is unlike sparklines where the scale is allowed to change from graphic to graphic.

Creating Data Bars

Creating data bars requires just a few clicks. You follow these steps:

1. Select a range of numeric data. Do not include the total in this selection. If the data is in noncontiguous ranges, hold down the Ctrl key while selecting additional areas. This range should be numbers of similar scale. For example, you can select a column of sales data or a column of profit data. If you attempt to select a range that contains both units sold and revenue dollars, the size of the revenue numbers will overpower the units sold numbers, and no color will appear in the units sold cells.
2. From the Home tab, select Conditional Formatting, Data Bars. You see six built-in colors for the data bars: blue, green, red, orange, bright blue, and pink. The colors appear both in solid and gradient forms. Select one of them.

The result is a swath of color in each cell in the selection, as shown in Figure 9.17.

Customizing Data Bars

By default, Excel assigns the largest data bar to the cell with the largest value and the smallest data bar to the cell with the smallest value. You can customize this behavior by following these steps:

1. From the Conditional Formatting drop-down on the Home tab, select Manage Rules.

2. From the Show Formatting Rules drop-down, select This Worksheet. You now see a list of all rules applied to the sheet.

3. Click the Data Bar rule.

Figure 9.17 After applying a data bar, you can see that California is a leading exporter of agriculture products.

4. Click the Edit Rule button. You see the Edit Formatting Rule dialog, as shown in Figure 9.18.

Figure 9.18 You customize data bars by using the Edit Formatting Rule dialog.

There are a number of customizations available in this dialog:

• Select the Show Bar Only setting to hide the numbers in the cells and to show only the data bar.

• For the Minimum and Maximum values, you have choices of Automatic, Number, Percent, Percentile, Formula, or Smallest/Largest Number. If you choose Automatic, Excel will choose a minimum and maximum value. You can override this by setting one value to a specific number.

• In the Bar Appearance section, you can specify gradient or solid fill for the bar. You can specify a solid border or no border. Two color chooser drop-downs allow you to change the color of the bar and the border.

• The Bar Direction drop-down allows you to choose Context, Left to Right or Right to Left. The default choice of Context will always be left to right, unless you are in an international edition of Excel where the language reading order is right to left.

When you choose Negative Values and Axis, you have new settings to adjust the color of the bar and the border for negative bars. You can also control if the zero axis is shown at the cell midpoint, or at an automatic location based on the relative size of the negative and positive numbers. If the axis is shown, you can adjust the color, too.

Showing Data Bars for a Subset of Cells

In the data bars examples given in the previous sections, every cell in the range receives a data bar. However, what if you just want some of the values such as the top 20 percent or the top 10 to have data bars? The process for making this happen is not intuitive, but it is possible. Basically, you apply the data bar to the entire range. Then you add a new conditional format (a very boring format) to all the cells that you do not want to have data bars. For example, you might tell Excel to use a white background on all cells with values outside of the top 10.

The final important step is to manage the rules and tell Excel to stop processing more rules if the white background rule is met. This requires clever thinking. If you want to apply data bars to cells in the top 10, you first tell Excel to make all the cells in the bottom 40 look like every other cell in Excel. Turning on Stop If True in the Conditional Formatting Rules Manager dialog is the key to getting Excel to not apply the data bar to cells with values outside of the top 10.

Figure 9.19 shows data bars applied to only the top 10 states.

Figure 9.19 Using Stop If True after formatting the lower 21 with no special formatting allows the data bars to appear only on the top states.

Using Color Scales to Highlight Extremes

Color scales are similar to data bars. However, instead of having a variable-size bar in each cell, color scales use gradients of two or three different colors to communicate the relative size of each cell. Here’s how you apply color scales:

1. Select a range that contains numbers. Be sure not to include headings or total cells in the selection.
2. Select Conditional Formatting, Color Scales from the Home tab.
3. From the Color Scales fly-out menu, select one of the 12 styles to apply the color scale to the range.

In a two-color red-white color scale, the largest number is formatted with a dark red fill. The smallest number has a white fill. All of the numbers in between receive a lighter or darker shade of pink based on their position within the range (see Figure 9.20).

Figure 9.20 Excel provides a range of shading, depending on the value. You can see that Carole and John’s receivables have been increasing throughout the year.

Customizing Color Scales

You are not limited to the color scales shown in the fly-out menu. If you select Home, Conditional Formatting, Manage Rules, Edit Rule, you can choose any two or three colors for the color scale.

You also have choices of where to assign the smallest, largest, and midpoint values (see Figure 9.21).

You should be aware of one strange situation. Normally, Excel will let you mix conditional formatting in the same range. You might apply both a color scale and an icon set.

Figure 9.21 You can choose any colors to use in the color scale.

If you have a three-color scale applied to some cells and choose a different three-color scale from the fly-out menu, the latter choice will overwrite the first choice.

However, Excel treats two-color scales as a different visualization than three-color scales. If you have a three-color scale applied and you then try to switch it to a two-color scale using the fly-out menu, Excel will create two rules for those cells. The latter two-color scale will be the only one to appear in Excel 2010, but you might be confused when you go to the Manage Rules dialog to see two different rules applied to the cells.

Using Icon Sets to Segregate Data

Icon sets, which were popular with expensive management reporting software in the late 1990s, have now been added to Excel. An icon set might include green, yellow, and red traffic lights or another set of icons to show positive, neutral, and negative meanings. With icon sets, Excel automatically applies an icon to a cell, based on the relative size of the value in the cell compared to other values in the range.

Excel 2010 ships with 20 different icon sets that contain three, four, or five different icons. The icons are always left-justified in the cell. Excel applies rules to add an icon to every cell in the range:

• Three-icon sets—For the three-icon sets, you have a choice between arrows, flags, two varieties of traffic lights, signs, stars, triangles, and two varieties of what Excel calls 3 Symbols. This last group consists of a green check mark for the good cells, a yellow exclamation point for the middle cells, and a red X for the bad cells. You can either get the symbols in a circle (that is, 3 Symbols(Circled)) or alone on a white background (that is, 3 Symbols). One version of the arrows is available in gray. All the other icon sets use red, yellow, and green.

• Four-icon sets—For the four-icon sets, there are two varieties of arrows: a black-to-red circle set, a set of cell phone power bars, and a set of four traffic lights. In the traffic light option, a black light indicates an option that is even worse than the red light. The power bars icons seem to work well on both color displays and monochromatic printouts.

• Five-icon sets—For the five-icon sets, there are two varieties of arrows, boxes, a five-power bar set, and an interesting set called 5 Quarters. This last set is a monochromatic circle that is completely empty for the lowest values, 25 percent filled, 50 percent filled, 75 percent filled, and completely filled for the highest values.

Setting Up an Icon Set

Icon sets require a bit more thought than the other data visualization offerings. Before you use icon sets, you should consider whether they will be printed in monochrome or displayed in color. Several of the 20 icon sets rely on color for differentiation and look horrible in a black-and-white report.

To set up an icon set, you follow these steps:

1. Select a range of numeric data of a similar scale. Do not include the headers or total rows in this selection.
2. From the Home tab, select Conditional Formatting, Icon Sets. Select 1 of the 20 icon sets. Figure 9.22 shows the 3 Stars choice selected.

Moving Numbers Closer to Icons

In the top rows of Figure 9.23, the icon set has been applied to a rectangular range of data. The icons are always left-justified. Numbers are typically right-justified. This can be problematic. Someone might think that the icon at the left side of Cell G3 is really referring to the right-aligned number in F3.

You might try centering the numbers to get the numbers closer to the icons in Rows 7-9. This will drive purists crazy, because the final digit of the 100 in Cell H8 does not line up with the final digits of Cells H7 and H9.

A better solution is to use the Alignment tab of the Format Cells dialog. Select Right (Indent) for the horizontal alignment. Bump the indent figure up to move the numbers closer to the icon. In Rows 12-14, the indent is set at four characters.

Figure 9.22 You can choose from the 20 icon sets.

If you do not want to show numbers at all, you can edit the conditional formatting rule and select Show Icon Only. Rows 17 through 19 show this solution. Ironically, when the numbers are no longer displayed, you can position the icons by using the Left Align, Center Align, and Right Align icons.

The over-the-top solution in Rows 22-24 involve using Show Icon Only and then pasting a linked picture of the numbers from other cells.

Figure 9.23 Changing the alignment of the numbers moves them closer to the icon.

Here are the steps to create Rows 22 through 24:

1. Select one of the cells with the icon set formatting.
2. From the Home tab, select Conditional Formatting, Manage Rules.
3. In the Conditional Formatting Rules Manager dialog, click the Icon Set rule, and then click Edit Rule.
4. In the middle of the Edit Formatting Rule dialog, select Show Icon Only. Click OK twice to close the two dialog boxes.
5. Select all the cells that contain icons and click the Align Center button on the Home tab.
6. Page down so that you are outside of the printed range. Stay in the same column. Set up a formula to point to the number in the top left corner of the icon set range. Copy this formula down and over to be the same size as your icon set range. This gives you a range of just the numbers.
7. Format this range of numbers to be right-aligned with an indent of 1. Choose the range and press Ctrl+1 to display Format Cells. On the Alignment tab, open the Horizontal dropdown and choose Right (Indent). Increase the Indent spin button to 1.
8. Copy this range of numbers.
9. Go back to the original set of icons and Paste, Picture Link. A picture of the original numbers will appear, behind the icons.

Creating a Chart Using Conditional Formatting in Worksheet Cells

In the old days, charts were drawn by hand, using a sheet of graph paper and a pencil. Think about the Excel worksheets on your computer. Basically, an Excel worksheet is a very large sheet of graph paper, with 17 billion tiny little boxes.

You can create plenty of charts right on a worksheet, without ever invoking the Excel charting engine. Figure 9.24 shows such a chart. The gray bars in D2:R6 are drawn based on conditional formatting rules in response to data entered in B2:C6. Note how the bars have expanded or contracted in the bottom image when starting or ending years are adjusted.

I created this worksheet for a friend who was trying to visualize the years of production for various models of Mullins Steel Boats. The years stretch from Cell D1 and would extend as far right as necessary. To make the chart narrow, you can select Vertical Text from the Orientation drop-down in the Home tab. You can then resize the columns to a column width of 2.

Figure 9.24 The gray bars are created through a series of conditional formatting rules.

The logic for creating the bars is as follows:

• If the start and end year are equal and they match the year in Row 1, color the cell gray, with borders on all four sides.

• If the start year in Column B matches the year in Row 1, color the cell gray. Include left, top, and bottom borders.

• If the end year in Column C matches the year in Row 1, color the cell gray. Include right, top, and bottom borders.

• If the year in Row 1 is greater than the start year and less than the end year, color the cell gray, with top and bottom borders but no side borders.

You follow these steps to create the conditional formatting rules for this logic:

1. Select the range D2:R6.

2. From the Home tab, select Conditional Formatting, Manage Rules. Excel displays the Conditional Formatting Rules Manager dialog.

3. Click the New Rule button. Excel displays the New Formatting Rule dialog.

4. In the top half of the dialog, select Use a Formula to Determine Which Cells to Format. The bottom half of the dialog box redraws to show Format Values Where This Formula Is True.

5. Enter the formula =$B2=D$1 for the first condition. This formula checks whether the start year in Column B of the current row is equal to Row 1 of the current column. It is crucial that you enter dollar signs before the B and 1 but not before the 2 and D.

6. Click the Format button in the dialog. On the Fill tab, choose a fill color for the cell. On the Border tab, click None and then click the Top, Bottom, and Left. Click OK to close the Format Cells dialog. Click OK to close the New Formatting Rule dialog. If you click the Apply button, you should see that the first cell for each bar is drawn in the worksheet.

7. Repeat steps 3 and 4 and then enter the formula =$C2=D$1 for the second rule; this is the formula to format the last cell of the bar. The Format selection is the same color fill as in step 6. On the Border tab, select None, Top, Bottom, and Right.

8. Repeat step 7 and then enter the formula =AND($B2<D$1,$C2>D$1) for the third rule; this is the formula to format center cells in the bar. The Format selection is the same fill as in step 6. On the Border tab, select None, Top, and Bottom.

9. Repeat step 7 and then enter the formula =AND($B2=$C2,$B2=D$1) for the last rule; this is the formula to find where the model was only available for a single year. The Format selection is the same fill color as in step 6. On the Border tab, select Outline. Rules are added to the beginning of the rule list. By entering this rule last, you ensure that it is evaluated first.

At this point, your Conditional Formatting Rules Manager dialog should look similar to the one in Figure 9.25.

This example is complicated by the fact that you draw borders on the appropriate edges of each cell. If you instead used a solid black fill, you could create the effect with a single rule, using the formula =AND($B2<=D$1,$C2>=D$1).

Figure 9.25 Four rules create the chart.

Creating a Chart Using the REPT Function

The REPT function, which has been around since Excel 5, takes two arguments. The first argument is the text to repeat. The second argument is the number of times to repeat the text.

In Figure 9.26, Column B shows cotton exports. The numbers range from 1,337 down to 13. You create the bar charts in Column C by repeating the | character numerous times. However, instead of showing a line of 1,337 pipe characters in Cell C4, the repeat argument in Cell B4 is 1,337 divided by 10. Therefore, Cell C4 contains 133 vertical bars. Cell C20 contains one vertical bar.

The result of the REPT function can be left- or right-justified. In Figure 9.27, the results in Column E are right-justified, and the results in Column G are left-justified to create a comparative histogram. The formulas on the right side of the chart use a REPT function concatenated with a space and then the value. The formulas on the left side of the chart concatenate the value, a space, and the REPT function.

To see a demo of using the REPT function for charting, search for “MrExcel Charts 9” at YouTube.

Next Steps

In Chapter 10, “Presenting Excel Data on a Map Using Microsoft MapPoint,” you will learn how to combine Microsoft Excel with Microsoft MapPoint to visually show geographic data. Several of the examples included in this chapter show tables of data by state. Think how these examples would take on a whole new meaning if they were plotted on a map. Microsoft MapPoint adds this functionality to Excel.

Figure 9.26 Using the REPT function is a quick way to produce a bar chart right in a worksheet. The trick is to use the proper scaling factor.

Figure 9.27 Here, pairs of REPT functions create a comparative histogram.