7. Advanced Chart Techniques

A Tool Chest of Advanced Charting Techniques

As the host of MrExcel.com, I get to see a lot of wild spreadsheets that people create. I have seen some amazing things come across my desk. This chapter covers some of the usual advanced charting tricks, and some unusual charts that you do not typically see in Excel. At the end of the chapter are a few examples of some charts that impressed me, so that you can see some of the cool things people can coax out of Excel.

The ideas in this section can be useful on most chart types that you work with. They include many techniques that you will come to realize are the basic, why-didn’t-I-think-of-doing-that kind of ideas.

Mixing Two Chart Types on a Single Chart

Although the Chart Type dialog does not offer it as a choice anymore, you can represent a chart’s series with different chart types. Instead of two lines on a chart, you can show one series as a line and one series as columns. Or you can mix columns and area charts, as shown in Figure 7.1.

To change the chart type for a series, you right-click the series and select Change Series Chart Type from the context menu.

When you change the chart type of a series in a chart, you set up a new chart group. Say that you have a stacked column chart with four series. When you change Series D to be a line chart, then chart group 1 contains the three series in the stacked columns. Chart group 2 contains the line series.

If you then change Series C to be an area chart, that creates a third chart group. The original chart group now contains only the two series that remain in the stacked columns. In Figure 7.2, five series occupy three chart groups.

Figure 7.1 To emphasize one series, you can mix chart types on a single chart.

Figure 7.2 Stacked columns, stacked lines, and an area chart create three chart groups.

In general, every different chart type in the same chart is in a new group. Moving one or more series to the secondary axis also creates a new chart group. Certain settings such as width of the column markers apply to all of the series in the same group. Some of the examples in this chapter will purposely move a series to the second axis or change the chart type of a series to create a different group, where different settings can apply. If you need to mix clustered and stacked columns, you will have to use this technique.

For best results when mixing chart types, follow these guidelines:

• You should stick with 2-D chart types. Excel does not let you mix 3-D charts.

• You can not stack markers from chart group 2 on top of markers from chart group 1. Every chart group starts stacking from the category axis.

• Although Excel will let you do it, you should not mix vertical types with horizontal types. Figure 7.3 shows a chart where one series is moved to a bar chart. Most people will not be able to glean anything from this chart (other than perhaps the fact that you have no sense of design).

Figure 7.3 Although Excel lets you mix column and bar charts, I am not sure why you would.

• You can mix circular charts. For example, you can change one series of a doughnut chart to a pie chart.

• Remember that in many cases, a line chart can be changed to an XY scatter with line series. The advantage is that up/down bars and hi-lo lines ignore an XY scatter chart line.

To see a demo of mixing chart types, search for “MrExcel Charts 7” at YouTube.

Mixing Stacked Columns with Clustered Columns

Column charts come in three basic flavors: clustered, stacked and 100 percent stacked. You might think that creating a chart which combines clustered and stacked column, as shown in Figure 7.4 would be easy, but it requires a lot of trickery to create the chart.

Figure 7.4 Compare how sales of components 1A and 1B stack up against a total forecast.

Figure 7.5 will reveal some of the magic behind the curtain. The chart is actually made up of five series. Series 1A and Series 1B make up the stacked column and are tied to the left axis. Series 2 as well as Blank 1 and Blank 2 are clustered columns and are tied to the right axis. In Figure 7.4, the blank series were filled with zeros and were not visible, but were still taking up space. In Figure 7.5, those series have small values so you can see the horizontal space that they are occupying.

Figure 7.5 Two blank series move Series 2 over to the right.

There are other tricks along the way of creating the chart. To mix clustered and stacked columns, one chart group has to be moved to the secondary axis. Anytime that you have two values axes, you need to switch to manual scaling to that they stay in sync. To make the stacked column narrower, you will increase the gap width between the columns.

Follow these steps to create the chart in Figure 7.4:

1. Start with data that has Series 1B, Series 1A, Blank 1, Blank 2, Series 2.

2. Create a stacked column chart from that data.

3. Because you have five rows and four columns, Excel assumes that you want the quarters to be stacked. On the Design tab, click Switch Row/Column so that the quarters are running across the horizontal axis. You should have something like the top-left chart in Figure 7.6.

Figure 7.6 Three steps toward the final chart.

4. Double-click one of the Series 2 columns to open the Format dialog. Select to move Series 2 to the secondary axis. You now actually have Series 1A and Series 1B in one stack and Series 2 in another stack. Unfortunately, because they are on different axis, Microsoft plots those columns directly on top of each other. Series 2 is covering up the detail in Series 1A and Series 1B. You can see this in the bottom-left chart of Figure 7.6.

5. On the Layout tab, select Blank 1 from the first drop-down. Select Format Selection. Move that series to the secondary axis.

6. While the Format dialog is open, select Blank 2 from the drop-down on the Layout tab. Move Blank 2 to the secondary axis. Close the Format dialog.

7. Blank 2 should still be selected. Go to the Design tab and select Change Chart Type. Select a clustered column chart. This changes the chart type of all three series that are on the secondary axis. You now have the top-right chart in Figure 7.6. There are still problems. All the Series 2 columns should be taller than the total of the Series 1 columns. The problem is that the right scale goes from 0 to 700 and the left scale goes from 0 to 500. Also, from an aesthetic point of view, the stacked column is appearing much wider than the clustered column.

8. Double-click the numbers along the right axis to open the Format dialog. Change the Min and Max from Automatic to Fixed. Use 0 as the Min and 700 as the Max. Keep the Format dialog open.

9. Click the left axis on the chart. Change the Min and Max from Automatic to Fixed. Use the same values as in step 8. Keep the format dialog open.

10. Click Series 1A in the chart. The format dialog box now offers a setting called Gap Width. When you increase the gap between columns, you automatically make the columns narrower. Increase the gap width from 150 to about 350. You can tweak this setting until the stacked column and the clustered column look to be about the same width.

11. The rogue blank series are still in the legend. The first click on the word Blank 1 in the legend selects the whole legend. The second click on Blank 1 selects just Blank 1. Press Delete. Repeat the two clicks to select Blank 2 in the legend. Press Delete.

12. As shown in the bottom-right chart of Figure 7.6, you now have numbers along both the left and right axis. While this might serve as a reminder that you have two axis, you can hide the right numbers. One easy way: Click the numbers to select them. Go to the font color drop-down on the Home tab. Select a white font.

Moving Charts from One Worksheet to Another

You can combine charts from many worksheets into a single dashboard by moving the charts from their original locations.

For example, if your sales data is on a sales worksheet, you can build the chart on the sales worksheet. When the chart is selected, you select Move Chart from the Design tab. You can then choose to move the chart to a different worksheet (see Figure 7.7).

The chart continues to point to data on the original worksheet, but you end up with an uncluttered screen of just charts. Even though the charts are on a new worksheet, they still respond to data changes on the source worksheets.

Using Shapes to Annotate a Chart

Excel offers 165 shapes on the Insert tab. Any of these shapes can contain text and can be added to a chart to call attention to certain aspects of the chart. As an example, in the chart in Figure 7.8, a block arrow shape points out reduced revenue levels in July through September. A callout below the chart points to the reduced June profits.

Figure 7.7 You use the Move Chart dialog to build a dashboard of charts on a single sheet.

Figure 7.8 Shapes with added text annotate key points in this chart.

To add a shape to a chart, you follow these steps:

1. Click the chart.

2. On the Insert tab, open the Shapes drop-down. This drop-down offers 165 shapes in 8 categories, as shown in Figure 7.9.

3. Click a shape. Your mouse pointer changes to a small plus sign.

Figure 7.9 You can choose from these 165 shapes.

4. Click and drag on the chart to draw a shape. The initial shape is filled with a solid color.

5. Use the white resizing handles to adjust the size of the shape. Use the yellow diamond handles to change the inflection points of the shape. Use the green rotation handle to rotate the shape. All the shapes in Figure 7.10 are right arrow callout shapes that have been modified by dragging the yellow inflection handles.

6. From the Format tab, select Shape Fill, White, and then Shape Outline, Black to remove the fill color from the shape.

7. Right-click the shape and select Edit Text.

8. Type your text in the shape.

9. Use the formatting icons on the Home tab to change the alignment and font size of the text in the shape.

When working with shapes, you will note that the Shapes drop-down includes a number of lines and arrows. While adding an arrow to a chart, you can hover over an existing shape to reveal four red connector dots. If you start or end an arrow on a connector dot, the arrow automatically moves when the connected shapes move.

Figure 7.10 You can use the handles to resize, reshape, and rotate a shape.

Making Columns or Bars Float

In Figure 7.11, the black bars appear to float in midair. This type of chart is good for showing the components of a whole.

The secret is that you plot the floating bars as Series 2. Series 1 is a dummy series that you fill using No Fill and No Outline in Excel.

There are some interesting settings involved in creating the charts shown in Figure 7.11. You follow these steps to create the chart in the figure:

1. Set up a data table to split the single series into two series. In Figure 7.12, Column B shows the sales for each category. The formulas in Column C:E are the data used to create the chart. The formulas in Column C copy the values from Column A. The formulas in Column E copy Column B.

2. Enter 0 in Cell D2. This is the “height” of the invisible column. For the first series, the height is therefore zero.

3. In Cell D3, enter =D2+E2. This formula adds the starting height of the last column (D2) and the height of the previous column (E2).

4. Double-click the fill handle in Cell D3 to copy the formula down to the rest of the series. Your data should now look like the data shown in Figure 7.12.

5. Select Cells C1:E6. From the Insert tab, select Column, Stacked Column. Excel creates the chart shown in Figure 7.13.

Figure 7.11 The floating columns or bars demonstrate how components make up a whole.

Figure 7.12 Formulas in Column C show the starting point for each column. That series will later become invisible.

6. From the Layout tab, select Legend, None.

7. Click any of the lower columns to select the first series. From the Format tab, select Shape Fill, No Fill. You might think that Excel is still outlining the first series, but those lines are the selection border. Click away from the series to make the selection disappear completely.

Figure 7.13 You can see the Invisible series before it disappears.

8. Right-click the visible series and select Format Data Series. In the Series Options category, set Gap Width to No Gap.

9. The second series is whichever color happens to be the second accent color in the current theme. While the series is selected, select Shape Fill and select a desired color.

10. Right-click the second series and select Add Data Labels. If your series fill color is dark, you cannot see the labels at all. Right-click in the center of one column and if you are lucky, you actually select the label. If you are not lucky, use the Current Selection drop-down in the Layout tab to select Series Sales Data Labels.

11. With the data labels selected, select Font Color on the Home tab, and then select a font color that contrasts with the column. You can now see that Microsoft has labeled the columns with the sales value. Click the Bold button on the Home tab. After changing the label in step 13, you might come back to the Home tab to change the font size so that the labels completely fit in the column.

12. From the Layout tab, select Format Selection. Excel displays the Format Data Labels dialog.

13. In the Format Data Labels dialog, change Label Contains from Value to Category Name. Note that in Label Position, you do not have a choice for Outside End, and that is really the choice you want. Turn toward Redmond and grumble that Microsoft disabled this choice in stacked charts. Click Close to close the dialog. Your chart now has the categories shown as labels on the columns and along the horizontal axis. This is a redundant use of ink.

14. Right-click the labels along the baseline and select Format Axis. Change the Axis Labels drop-down to None. Change Major Tick Mark Type to None, and then click Close.

The preceding steps create the column chart shown in Figure 7.11.

Using a Rogue XY Series to Label the Vertical Axis

The chart in Figure 7.14 shows annual scores against a government performance index. The actual index number is not as relevant as the ranges shown in A11:B15. These ratings decide whether a company can continue to do business in a particular segment. The top chart has default Excel gridlines at levels of 2, 4, 6, and so on. It would be better to create gridlines at the specific category levels of 7, 12, 18, 23, and 28.

The goal is to draw gridlines at unevenly spaced locations of 3, 8, 13, 18, 23, and 27. There is not a good way to adjust the gridlines to show horizontal lines for each category. The main problem is that Excel treats the collection of horizontal gridlines as a single object. When you format one major gridline, all major gridlines change. If each gridline could be formatted individually, you could draw a gridline every one unit and make most of the gridlines invisible.

The gridlines in the bottom chart of Figure 7.14 are actually error bars that are attached to an invisible XY series added to the chart. This solution may seem bizarre. It is not one of those obvious, do-two-steps-and-you-are-done solutions.

Figure 7.14 The top chart has automatic gridlines. The bottom chart’s gridlines have actual meaning.

Showing Several Charts on One Chart by Using a Rogue XY Series

Figure 7.21 shows a single chart that appears to stack up four different charts. This chart is especially useful because one of the middle series, which is the subject of the chart, has a particularly low Q3 value. If you used a series of overlaid area charts, you would never be able to see the Q3 value in question. Note that it is important that all four charts in the stack have the same scale. Even with the current arrangement, it is difficult to compare one year to another. Which Q1 is largest? Without looking at the data, you cannot really tell.

Figure 7.21 This stacked arrangement of charts allows you to compare one year to the next.

The chart is actually a stack of seven area charts. The second, fourth, and sixth charts are invisible charts that are the complements to the first, third, and fifth charts. For example, if you have decided that the range for each chart should be 0 to 250, then the formula for each point in the second series will be 250 minus the corresponding point in the first series.

You follow these steps to set up the data for this chart stack:

1. Insert blank columns before years 2, 3, and 4.

2. The formula in the blank column should be 250 minus the column to the left. Copy to all three blank columns. Your data should like the data shown in Figure 7.22.

Figure 7.22 You can add a series between each year to force the next year to start at an even increment of 250.

Figure 7.23 The chart initially shows seven areas.

3. Select the range of data. From the Insert tab, select Area, Stacked Area. Because there are four rows and seven columns, Excel creates the chart with the data reversed: four series with seven points each.

4. From the Design tab, select Switch Row/Column to create the chart as seven series with four points each. The chart appears as shown in Figure 7.23.

5. In the chart, click Series 2. From the Format tab, select Shape Fill, No Fill. There is a fast way to repeat for Series 4 and Series 6; click Series 4. Press F4. Click Series 6. Press F4.

6. Double-click the numbers along the vertical axis to open the Format dialog. Then do the following:

• Select Maximum, Fixed, 1000.

• Select Major Unit, Fixed, 250.

• Select Minor Unit, Fixed 50.

• Select Axis Labels, None.

• Select Major Tick Mark Type, None.

7. Type three columns of data. The first column is the new data labels that you want to appear along the y-axis. This could be four sets of 0, 50, 100, 150, and 200. In the next column, fill zeros down the column for the x location of the XY points. In the next column, type values from 0 to 1,000, in 50-unit increments for the y locations of the XY points.

8. Although the second column contains x locations, you initially add only the y locations to the chart. Select the third column, containing y locations and the heading above the data.

9. Press Ctrl+C to copy the data. Select the chart. Press Ctrl+V to paste the new series to the chart. You now have a complete mess, as shown in Figure 7.24.

Figure 7.24 The new series initially ruins the chart.

10. Click the new Label series. From the Design tab, select Change Chart Type, XY (Scatter), Scatter with Only Markers.

11. Select Series Label Series from the Current Selection drop-down in either the Layout or Format tab.

12. Wait until the formula bar shows the SERIES function. Edit the third argument to point to the column of zeros. Do not include the label for the zero heading:

=SERIES(Stacked!$L$3,Stacked!$K$4:$K$24,Stacked!$L$4:$L$24,8)

Markers will appear along the vertical axis as shown in Figure 7.25.

13. Double-click the numbers along the right vertical axis to display the Format dialog box. Then do the following:

• Select Maximum, Fixed, 1000.

• Select Major Tick Mark Type, None.

• Select Axis Labels, None.

• While the Format Axis dialog remains open, click the axis labels along the top of the chart and select Major Tick Mark Type, None. Then select Axis Labels, None.

Figure 7.25 After you add the x values as the second argument in the SERIES function, the markers move to the left vertical axis.

14. Right-click the markers along the left vertical axis. Select Add Data Labels. Right-click the data labels and then select Format Data Labels. Select Label Position, Left.

15. Click the plot area. Grab the bottom-left resizing handle and drag toward the center of the chart until there is enough room for the new labels.

16. From the Layout tab, select Primary Horizontal Gridlines, Display Major & Minor Gridlines. Format the major gridlines to have a thicker line weight by selecting Vertical (Value) Axis Major Gridlines from the Current Selection drop-down. Then, from the Format tab, select Shape Outline, Weight, and select 21/2 point.

17. Select Series Label Series from the Current Selection drop-down on the Format tab. Click Format Series, Marker Options, Marker Type to None. In Figure 7.26, you can see that three issues remain. The legend contains entries for the blank series. The Q1 and Q4 points do not extend to the left and right axes. The labels along the left side of the chart run from 0 to 1,000 instead of 0 to 250 repeatedly.

Figure 7.26 You still have to fix the legend and the labels along the left axis.

18. Click the Legend to select the whole legend. Click again on the Blank 3 legend entry to select only that entry and press Delete. Excel removes that entry from the legend. Repeat with Blank 2, Blank 1, and Label Series to delete them from the legend. To fix the labels along the y-axis, you have three choices:

• Write some VBA (see Chapter 13).

• Download Rob Bovey’s XY Chart Labeler utility from http://www.appspro.com/Utilities/ChartLabeler.htm.

• Repeat step 19 several times.

19. To fix the labels manually, click the data labels on the left side of the chart to select all the labels. Click just the 250 label. Type 0 and press Enter. Repeat to change the 300 label to 50, the 350 label to 100, and so on.

20. Add a title, if desired.

21. Notice that there is a gap between the left vertical axis and the Q1 label. To remove this gap, right-click the Q1 label and select Format Axis. The bottom setting on the Axis Options category is the Position Axis option button. Change from Between Tick Marks to On Tick Marks. In Figure 7.27, the right chart is set to On Tick Marks. The left chart is set to Between Tick Marks.

Figure 7.27 You choose where to position the axis.

When you distribute this chart to others, they will be wondering how you managed to make Excel put four charts on a single chart. Actually, your manager will not even know this is difficult, but other people who use Excel might be impressed.

Creating Dynamic Charts

In this section, dynamic refers to a chart that expands, contracts, changes, or moves in response to changes in the underlying worksheet.

In many cases, you need to change the SERIES function attached to a data series to calculate a series on-the-fly. However, the examples in this section rely on four worksheet functions: VLOOKUP, MATCH, INDEX, and OFFSET. In case you are new to these functions, coverage of each of them follows.

Using the OFFSET Function to Specify a Range

The OFFSET function allows you to specify a rectangular range of data. You have to provide five arguments to specify the range:

• Any starting cell—An example is Sheet1!A1.

• A number of rows to move down from the starting cell to the first cell in the range—If you specify a positive number, the range starts below the starting cell. If you specify 0, the range starts in the same row as the starting cell. If you specify a negative number, the range starts above the starting cell.

• A number of columns to move right from the starting cell to arrive at the first cell in the range—You can specify a positive number to move right, 0 to stay in the same column as the starting cell, or a negative number to move to the left.

• The number of rows in the range—If you specify 1, you describe a range that is one row tall.

• The number of columns in the range—If you specify 1, you describe a range that is one column wide.

To understand the various types of ranges that the OFFSET function can return, consider the shapes in Figure 7.28.

Figure 7.28 Examples of the OFFSET function.

The top-left shape in Figure 7.28 highlights a range that is eight rows tall by four columns wide. If the starting cell is G12, you would have to move nine rows up from the starting cell and four columns left from the starting cell to arrive at the top-left corner cell of the range. The function to refer to this range is =OFFSET(G12,-9,-4,8,4).

The box underneath Cell G12 is 12 rows tall by one column wide. It starts one row from the starting cell of G12 and zero columns to the right. The function to refer to this range is =OFFSET(G12,1,0,12,1).

The single cell in I9 is three rows above the starting cell, two columns to the right, one row tall, and one column wide. The formula is =OFFSET(G12,-3,2,1,1).

Of course, it is silly to write any of these formulas. If you knew that your chart range was always going to be in Cells G13:G23, you would just refer to Sheet1!G13:G23. The power of the OFFSET function is that you can use other functions for some of the arguments. For example, you could count the category labels in your dataset today by using the formula COUNTA($J:$J). The formula =OFFSET(J13,0,0,COUNTA(J:J),1) starts in Column J and extends down to include the number of cells with data in Column J. This formula counts on the data in Column J to not include any blank cells.

Using VLOOKUP or MATCH to Find a Value in a Table

The VLOOKUP function does a vertical lookup. It looks for a particular value in the first column of a lookup table. When the first exact match is found, Excel returns a particular column from that row of the table.

VLOOKUP usually has four arguments: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

• Lookup value—This is the name or value you are trying to find. In Figure 7.29, it is the name in Cell A1.

Figure 7.29 Examples of the VLOOKUP and MATCH functions.

• Table array—This is a rectangular range of cells. Excel searches the first column of the table array in an effort to find a cell that has the same value as the lookup value. In Figure 7.29, this is the range C10:E33. If you plan on copying the VLOOKUP formula in either direction to find additional customers or columns, you should use the F4 key to make the table array absolute.

• Column index number—This specifies the column Excel should return as the result of the formula. The columns are numbered 1, 2, 3, and so on, starting with the column that contains the lookup value. In Figure 7.29, specifying 2 would give you the Q1 quota for the person. Specifying 3 would give you the Q1 sales. Note that specifying 1 would give you the person’s name again. Although this might seem silly, because you already have the person’s name as the lookup value, you can sometimes use VLOOKUP to test to see whether the lookup value is a valid name in the table array. In this case, it is fine to specify 1 as the column index. Note that if you need to copy this function across several columns to return the second, third, fourth, and fifth columns from table array, you can use COLUMN(B1) instead of using 2 as the third argument. As the formula is copied to the right, Excel automatically returns COLUMN(C1), COLUMN(D1), COLUMN(E1), and so on, which then ask for the third, fourth, and fifth columns from the table array.

• Range lookup—This is either TRUE or FALSE. You specify FALSE to indicate that you are looking for an exact match. In the FALSE version, the table array can be in any order. If you instead specify TRUE, Excel returns the value of the closest match that is equal to or lower than the lookup value. In the TRUE version, the table array must be sorted in ascending order. If you leave off this parameter, it is the same as specifying TRUE.

VLOOKUP is a workhorse function in Excel. If you have ever dabbled in Access, you might have joined tables to bring new columns from a lookup table into a query. The VLOOKUP function allows you to simulate joining tables in Excel.

In Figure 7.29, a VLOOKUP function in Cell D1 asks for the second column of the table array and finds the Q1 quota for the sales rep listed in Cell A1. A second VLOOKUP in Cell D2 asks for the third column of the table array and finds the Q1 sales. The HLOOKUP can do a horizontal lookup, in case your table array has the key values across one row.

There is a curious variant of VLOOKUP called the MATCH function, which looks for a lookup value in a one-column-wide lookup array. When a corresponding value is found in the lookup array, Excel tells you the relative row number within the lookup array where the match is found. MATCH can also find a value in a one-row-tall lookup array, similar to the HLOOKUP function.

This seems completely useless. Has your manager ever called you to ask, “By the way, can you tell me in what relative row number within a range that customer is located?” This is a bit of trivia that rarely comes up in conversation. However, bear with me because the MATCH function can be useful when combined with the INDEX function, which is discussed in the next section.

The MATCH function requires arguments that are similar to the first, second, and fourth arguments of the VLOOKUP function:

• Lookup value—This is the value that you are trying to find.

• Lookup array—This is the first column of the table array range from VLOOKUP. This is the column where Excel looks to find a match for the lookup value.

• Match type—This is conceptually similar to using TRUE or FALSE as the fourth argument in the VLOOKUP function. A FALSE in VLOOKUP requires an exact match. In the MATCH function, a 0 requires an exact match. A TRUE in VLOOKUP returns the next-smallest value from a sorted table array. Similarly, a 1 in MATCH returns the next-smallest value. (Remember that spreadsheets store TRUE as a 1.) MATCH offers one more option for match type. If you specify -1, Excel finds the next-largest value from the descending sorted lookup array.

In Figure 7.29, a MATCH function in Cell D3 looks for the sales rep from Cell A1 in the range C10:C33. Note that although the match is actually on Row 21, this row is the 12th row in the table array. Thus, the MATCH function returns 12.

Both the VLOOKUP and MATCH functions are CPU intensive, particularly when the lookup table contains thousands of records. The lengthy part of the function is finding the matching record from the first column of the lookup table. After Excel locates that value, moving right to grab the second or third column is relatively quick.

Combining INDEX and MATCH

Initially, the INDEX function does not seem that useful. Its syntax, which is used in a later chart example, is as follows:

=INDEX(Rectangular Range, Which Row in the Range, Which Column in the Range)

=INDEX(A1:Z26,5,10) returns the value at the 5th row and 10th column of the range A1:Z26. There are certainly easier ways to refer to Cell J5 such as =J5.

The INDEX function becomes powerful when you use a MATCH function as the second argument to specify which range to return.

In Figure 7.30, a processor-intensive MATCH function in Cell D1 finds the row number where the sales rep can be found. Relatively fast INDEX functions in Cells D2 and D3 then return the value from that row in the quota or sales column.

The next three charting examples make use of the OFFSET, VLOOKUP, INDEX, and/or MATCH functions to change a chart in response to changes in the underlying worksheet.

Figure 7.30 Examples of the MATCH and INDEX functions.

Using Validation Drop-Downs to Create a Dynamic Chart

This example creates a chart that is a bit like a pivot chart, except it uses a drop-down on the worksheet to choose which data to chart.

Figure 7.31 shows a simple chart that compares sales to quota by quarter for a particular sales rep.

Figure 7.31 This simple chart could be part of an executive information system for a vice president of sales.

When you click in Cell A1 of this chart, a drop-down appears. When you open the drop-down and select a new sales rep, the chart instantly updates to show that particular sales rep’s figures (see Figure 7.32).

This chart is not that amazing; it uses just some basic Excel tools that are hiding on the worksheet.

First, hidden out of sight in Row 63 is all the data needed to create the chart for any particular sales rep. Sales reps are in A64:A87. Their individual quota and sales figures are in Columns B:K (see Figure 7.33).

Figure 7.32 If you choose a new rep from the drop-down in A1, the chart updates.

Figure 7.33 The source data for all charts is hidden.

Hidden behind the chart are a number of formulas:

• In Cell A2, the formula =MATCH(A1,A64:A87,0) indicates the location of the sales rep selected in Cell A1.

• In C4:F5, formulas similar to =INDEX($A$64:$K$87,$A$2,2) pull the information for the selected sales rep and format it into two data series. The structure of the original dataset is typical of how accounting keeps track of such data. If the original dataset were in a different format, a simpler formula could be written. As it is, the third argument of this formula had to be edited in each of the other seven cells after being copied to C4:F5. To avoid editing each formula, you could replace the 2 with COLUMN(B1). You could also abandon the MATCH formula in Cell A2 and build eight VLOOKUP formulas here, and that would be fine; it is a matter of personal preference.

To create the drop-down in Cell A1, you follow these steps:

1. Select Cell A1.
2. From the Data tab, click the top half of the Data Validation icon.
3. In the Data Validation dialog that appears, change the Allow drop-down from Any Value to List. New fields appear in the dialog, including a Source field.
4. Click the Reference box at the right side of the Source field and select the A64:A87 range, which contains names.
5. By default, the In-cell Drop down check box is selected. Leave it selected. Click OK to close the dialog.

Figure 7.34 shows the structure of the data hidden behind the chart as well as the completed Data Validation dialog box.

Figure 7.34 Formulas convert the name in Cell A1 into a chartable dataset. The drop-down in Cell A1 appears because of the Data Validation settings.

After you set up the formulas, you create a chart from B3:F5. You follow these steps to create the chart:

1. Select the Range B3:F5. From the Insert tab, select Column, Clustered Column.
2. Click any column marker in the chart for quota. From the Design tab, select Change Chart Type, Line, Line.
3. From the Layout tab, select Legend, Show Legend at Top.
4. From the Layout tab, select Axes, Primary Vertical Axis, Show Axis in Thousands.
5. Click outside the plot area but inside the chart border to select the chart area. From the Format tab, select Shape Outline, No Outline.
6. Drag the selection border of the chart to move it to Cell A2, and then resize the chart so that it covers any of the formulas behind the chart.

As mentioned earlier, this chart is not really dynamic. The worksheet is dynamic and responds using formulas. The chart is always plotting a static range of data. The chart in the next example is truly dynamic, expanding or contracting as data is added or removed from the spreadsheet.

Using Dynamic Ranges in a Chart

A chart based on a dynamic range expands or contracts as new data points are added to a specific range.

The chart in this example requires an understanding of the SERIES function and the OFFSET function.

As shown in Figure 7.35, the data for the chart is initially located in A5:B16. There are headings in Cell A4 and Cell B5. The COUNTA() function, described later, counts all the entries in Column A and subtracts 1 to factor out the heading. This approach requires two assumptions, which you have to work to control:

• There should be no other data anywhere in the million other rows of Column A. You should not build new tables down below, and you should not add a title in Cell A1. If you are doing either of these things, you will have to adjust the COUNTA() function to subtract more than just the first heading cell.

• Customers should always start in Cell A5 and extend in a contiguous range. You should not leave a blank cell in Cell A17 and type a new customer in Cell A18. This will not work. If you want to delete a customer from the chart, you need to delete the entire row containing the customer. You should not delete the values in Row 8, leaving a blank row.

To build this chart, you first build and format a static chart. You then make the chart a chart that will dynamically resize. Here’s how you create this chart:

1. Select the data in Cells A4:B16. From the Insert tab, select Bar, 2D Bar, Clustered Bar.

2. From the Layout tab, select Legend, None.

3. Right-click the customer names along the left side of the chart and select Format Axis to open the Format dialog box.

4. Select Categories in Reverse Order.

5. Select Specify Interval Unit and make sure the unit is 1. Close the Format dialog box.

6. Resize the chart so that it is taller.

7. Click outside the chart. You now need to define a couple of range names that point to a dynamic range of data.

8. From the Formulas tab, select the Name Manager.

9. Click the New button. Excel displays the New Name dialog.

10. Type a name such as Customers.

11. Leave Workbook as the scope.

12. Adding a comment, which was new in Excel 2007, helps when you want to document how a complicated name works.

13. Set Refers to Box to the following:

=OFFSET('Dynamic Chart'!$A$5,0,0,COUNTA('Dynamic Chart'!$A:$A)-1,1)

This basically says to start in Cell A5, move zero rows down, and zero columns over but to include as many entries as are found in Column A, except for the heading.

14. Click OK to return to the Name Manager dialog. You then see the new name added in the box.

15. Repeat steps 9 through 14 to assign a name to a formula for the sales values. Use the name Sales, and use the following formula in step 13:

=OFFSET('Dynamic Chart'!$B$5,0,0,COUNTA('Dynamic Chart'!$A:$A)-1,1)

Note that the fourth argument is still counting the number of customer names in Column A. This allows a new customer to appear but have a blank in Column B to indicate zero sales. People should never leave a cell blank to indicate a zero, but this would handle it in case they did.

16. Close the Name Manager dialog box.

17. Test whether your range names are working. Click in the Name Box drop-down just to the left of the Formula bar. Type Customers and press Enter. Excel highlights the range of customers. Repeat for sales. Type a new test customer in A17. Type Customers in the name box again and press Enter. Excel should now highlight a range that has been extended to include the new customer. If this is not working, go back and check the formulas you entered in steps 13 and 15.

18. Click the chart to activate it.

19. Click the data bars in the chart. Wait for Excel to display the SERIES formula in the formula bar, which should initially look like this:

=SERIES('Dynamic Chart'!$B$4,'Dynamic Chart'!$A$5:$A$16,
    'Dynamic Chart'!$B$5:$B$16,1)

The first argument in this formula means that the series name comes from Cell B4. The labels along the vertical axis come from Cells A5:A16. The values for each bar are located in B5:B16. This is the first (and only) series in the chart.

20. On the Design tab, click the Select Data button. Excel displays the Select Data Source dialog.

21. In the left side of the Select Data Source dialog, select Series1 and click Edit. Excel displays the Edit Series dialog.

22. Assuming that Sales is a workbook-level name, you have to specify the workbook name in apostrophes, an exclamation point, and then the range name as the series values. In my workbook, this is =’7-Dynamic1.xlsm’!Sales. Click OK.

23. On the right side of the Select Data Source dialog, select Edit for Axis Labels. Excel displays the Axis Labels dialog.

24. In the Axis Labels dialog, type a similar reference that points to Customer. In my case, this is =’7-Dynamic1.xlsm’!Customers. Click OK to close the Axis Labels dialog.

25. Click OK to close the Edit Series dialog. In the formula bar, you should see the following new SERIES formula:

=SERIES('Dynamic Chart'!$B$4,'7-Dynamic1.xlsm'!Customers,
    '7-Dynamic1.xlsm'!Sales,1)

Figure 7.35 shows the final chart.

Figure 7.35 As new customers are added to the bottom of the data range, the series formula automatically expands. Customers and Sales are names that are defined using the OFFSET function.

This example uses the OFFSET function to automatically expand a chart. The next example uses the OFFSET function to chart a rolling 12 months of data.

Creating a Scrolling Chart

An interesting dynamic chart is a chart that shows a scrolling 12 months of data. In Figure 7.36, 36 months of data are available and are shown in the bottom chart. However, you can show a scrolling 12 months in the top chart. As you move the scrollbar, the top chart shows a closer view of a 12-month segment of the longer chart.

In Figure 7.36, a gray box shades the portion of the bottom chart that is shown in the top chart.

In Figure 7.37, the scrollbar is moved left to show a different portion of the detail.

You follow these steps to create a scrolling chart:

1. Format your dates with a MMMMMYY custom format. The five M’s are the code to force the month to be displayed with only the first letter of the month. This allows more dates to fit across the bottom of the chart.

Figure 7.36 When the scrollbar in Row 2 is scrolled toward the right, you see recent months.

2. Select the data in Cells A1:B37. From the Insert tab, select Line, Line. Delete the legend. Excel creates a basic line chart with all 36 months of data.

3. Move the chart so that it starts in Row 22. Make a copy of the chart that starts in CellD3 by Ctrl+dragging the chart border to D3.

4. Deselect the chart. Enter a number between 1 and 25 in Cell D1.

Figure 7.37 Note the number 17 in Cell D1. This cell changes in response to the scrollbar changes.

5. From the Formulas tab, select Name Manager. Click the New button.

6. Type Months in the Name field. In the Refers To field, enter the following:

=OFFSET(Scroll!$A$1,Scroll!$D$1,0,12,1)

This says to start from Cell A1 of the Scroll worksheet, move down the number of rows in Cell D1, and then take a range that is 12 rows by 1 column. Click OK to accept the name.

7. Add another name. Type the name SalesByMonth. In the Refers To box, use: =OFFSET(Scroll!$A$1,Scroll!$D$1,1,12,1) The only change is that in the third argument, you move right one column to grab data from Column B.

8. Click the line series in the first chart. Wait for the SERIES formula to appear in the formula bar. Edit the formula so that it looks like this:

=SERIES(Scroll!$B$1,'7-Dynamic.xlsm'!Months,
    '7-Dynamic.xlsm'!SalesByMonth,1)

9. Test the chart. Enter a new number between 1 and 25 in Cell D1. The top chart should change. You now need to provide an easy way for the reader to change the number in Cell D1. This can be accomplished through a scrollbar. The icon for adding a scrollbar is located on the Developer tab. Open the Insert drop-down and find the Scrollbar icon in the center of the second row of Form Controls. Since many people do not have the Developer tab available in the Ribbon, you can use step 10 to add the scrollbar control to the Quick Access toolbar.

10. From the Office icon menu, select Options. Select Quick Access Toolbar. In the top-left drop-down, select Commands Not in the Ribbon. Browse through the list box on the left for Scroll Bar (Form Control). Select this item and click the Add button to add the control to the Quick Access toolbar. Click OK to close the Excel Options dialog.

11. Click the newly added scrollbar control in the Quick Access toolbar. After clicking the scrollbar icon, drag to draw a scrollbar control on the worksheet. Drag from the top left of Cell D2 to the bottom right of Cell K2.

12. Right-click the scrollbar and select Format Control.

13. Change Minimum Value to 1 and Maximum Value to 25. Set Incremental Change to 1. For Cell Link, click the Refers To button and select Cell D1. Click OK. Click outside the scrollbar to deselect it. To test the scrollbar, drag the slider. The value in Cell D1 should change. The top chart should also change.

14. Change the chart title of the top chart to indicate that it is a rolling 12 months. The top chart is now complete. If you want to draw the shaded box on the bottom chart, continue with the following steps.

15. Enter the heading Shading in Cell C1.

16. Enter the following formula in Cell C2:

=IF(AND(ROW(A1)>=$D$1,ROW(A1)-$D$1<12),MAX(SalesByMonth),0)

Copy this formula down to the other 35 months.

17. Select the Range C1:C37. Press Ctrl+C to copy the range. Select the bottom chart. Press Ctrl+V to paste a new series to the chart.

18. Click the line for the second series. From the Design tab, select Change Chart Type, Area Chart.

19. Click the area chart to select the second series. From the Format tab, select Shape Fill and select light gray to make the box less obtrusive.

The scrollbar makes this chart fun to use. People will be encouraged to interact with the chart, which will mean they spend more time with the chart. Perhaps it is a bit gimmicky. Perhaps it makes sense when you have 20 years of monthly data. I can certainly understand the argument that the 12-month chart is less informative than the 36-month chart. However, the scrolling chart is here in this book because it is unusual.

Modifying the Scrollbar Example to Show the Last 12 Months

In the previous section, a scrollbar was used to determine which 12-month range was plotted on a chart. You can use similar concepts without the scrollbar. Say that you always want to display the latest 12 months of data. After you add a new month to the end of the range, and you want the chart to automatically shift to show the last 12 months. This would be the start of the OFFSET formula:

=OFFSET(A1,Some Number of Rows,0,12,1)

This formula always grabs 12 rows by 1 column. The trick is figuring out where to start the selection to get the last 12 months.

You can figure out how many months are present in Column A by using =COUNTA(A:A). When there are 36 months, you want to use the data A26:A37. This means you need to move 25 rows down from a starting position of Cell A1. Thus, the Some Number of Rows argument would be =COUNTA(A:A)-11.

To create a chart that shows the latest 12 months, you follow the steps in the previous example except that in step 6, the formula for months should be =OFFSET(Scroll!$A$1, COUNTA(Scroll!$A:$A)-11,0,12,1) and in step 7, the formula for SalesByMonth should be =OFFSET(Scroll!$A$1,COUNTA(Scroll!$A:$A)-11,1,12,1).

Creating Advanced Charts

The last few examples in this chapter are unconventional charts that you can create. Each chart requires a few tricks to coax the result out of Excel.

Thermometer Chart

A thermometer chart is a big way to display a single number. It is great for use on a dashboard display where you want everyone to see progress toward a goal.

To create a thermometer chart like the one shown in Figure 7.38, you follow these steps:

Figure 7.38 This chart is a single column based on the number in Cell A1.

1. Enter a number between 0 and 100 in Cell A1.

2. Select Cell A1. From the Insert tab, select Column, 2-D Column, Clustered Column.

3. From the Layout tab, select Legend, None.

4. From the Layout tab, select Axes, Primary Horizontal Axis, None.

5. From the Layout tab, select Gridlines, Primary Horizontal Gridlines, None.

6. Right-click the single column and select Format Data Series. Change Gap Width to 0 percent. For the Fill category, select Gradient Fill. In the Preset Colors drop-down, select the ninth thumbnail, a blend from red to orange. For Border Color, select Solid Line and then choose an orange color from the drop-down. You can leave the Format dialog open while you do steps 7 and 8.

7. Select Plot Area from the Current Selection drop-down. Format the border color as a solid line and choose an orange color.

8. Select the chart area and format the border as No Line. Close the Format dialog box.

9. Resize the chart area so that it is narrow and long.

10. Right-click the numbers along the vertical axis. Select Format Axis. Then do the following:

• Select Minimum, Fixed, 0.

• Select Maximum, Fixed, 100.

• Select Major Unit, Fixed, 10.

• For the Line Color category, select Solid Line and then select orange.

• Click OK to close the Format dialog box.

11. With the axis labels still selected, select Font Color and then select orange on the Home tab.

12. Select the plot area. Drag the bottom resizing handles up so that there is space between the bottom of the plot area and the bottom of the chart area.

13. From the Insert tab, select Shapes, Basic Shapes and then select an oval. Hold down the Shift key while you draw a circle at the bottom of the chart. Note that the Shift key forces the oval shape to be drawn as a circle.

14. From the Format tab, select Shape Fill, and then select a dark orange, and then select Shape Outline and select a dark orange color for the ball at the bottom of the thermometer.

Benchmark Chart

A benchmark chart shows sales for each period as a column chart. The quota, goal, or benchmark for the period is shown as a cap. If the sales exactly meet the quota, the sales column fits perfectly into the cap. If sales fell short of the quota, you see some whitespace between the cap and the column.

You follow these steps to create a benchmark chart like the one in Figure 7.39:

1. Enter Months in Column A, Sales in Column B, and Quota in Column C.
2. Create a clustered 2-D column chart from the data. Delete the legend.
3. Click the Quota series in the chart. From the Design tab, select Change Chart Type and then select Scatter (XY). Excel automatically moves the series to a secondary axis.
4. Select one of the quota data markers. Right-click and select Format Data Series. Select Primary Axis.
5. Make sure the quota series is still selected. From the Layout tab, select Error Bars, More Error Bar Options. Close the Error Bar Format dialog and open the Current Selection drop-down from the left side of the Ribbon. If you see Series Quota Y Error Bars, select that and press the Delete key. Select Series Quota X Error Bars and click Format Selection. Change Fixed Value from 1.0 to 0.2. For the Line Style category, select a 3-point width.
6. Select Series Quota from the Current Selection drop-down. Click Format Selection. For the Marker Options category, select None.

Figure 7.39 The horizontal markers on each column indicate where the quota had been for that month.

The horizontal lines in this chart are actually error bars that show the goal or quota for each month.

Delta Chart

You can use a delta chart to plot revenue and a quota as line charts. A special data marker appears halfway between the two lines to show the percentage of quota (see Figure 7.40). The hard part of creating a delta chart is getting the labels to float halfway in between the two lines.

Figure 7.40 The labels automatically float halfway between the two lines.

You follow these steps to create a delta chart:

1. Set up a dataset with months in Column A, quota in Column B, and revenue in Column C.

2. In Column D, enter the following formula to average revenue and quota:

=AVERAGE(B2:C2)

This will be the location point for the data label. Copy the formula down the column.

3. In column E, enter the formula =C2/B2 to hold the label. This formula will show the percentage to quota. Format the results as a percentage with zero decimal places and copy the formula down the column.

4. Create a chart from Cells A1:D13. From the Insert tab, select Line, Line with Markers.

5. Select Value Axis from the Current Selection drop-down on the Layout tab. Click Format Selection. Choose a fixed minimum and a fixed maximum that are appropriate to zoom in on the data in the chart.

6. Select Series Average from the Current Selection drop-down on the Layout tab. Click Format Selection. Select to move the series to the secondary axis. This allows the labels to be adjusted for just this series.

7. Select Secondary Vertical (Value) Axis from the Current Selection drop-down on the Layout tab. Choose the same minimum and maximum as in step 5.

8. Close the Format dialog box.

9. Click the Average series to select it. From the Design tab, choose Select Data.

10. Click the Average series on the left side of the Select Data Source dialog. On the right side of the dialog, click the Edit button. Excel displays the Axis Labels dialog, where you can select new Horizontal Category Axis Labels. Point to the percentages in Column E. Click OK to close the Axis Labels dialog. Click OK to close the Select Data Source dialog.

11. Make sure Average Series is still selected. From the Layout tab, select Data Labels, More Data Label Options. In the Format Data Labels dialog, select Category Name, and deselect Value. Then do the following:

• For Label Position, select Center.

• For Fill, select Solid Fill and choose a light color.

• For Border Color, select Solid Line and choose a dark color.

12. Click the Average series to select it. From the Format ribbon tab, select Shape Fill, No Fill. Select Format, Shape Outline, and then select No Outline. Close the Format dialog box.

13. From the Layout tab, select Lines, High-Low Lines. Select High Low Lines 1 from the Current Selection drop-down. From the Format tab, select Shape Outline, Dashes, and then select the fourth dash selection.

14. From the Layout tab, select Axes, Secondary Value Axis and set it to None.

15. Click the Average entry in the legend. The first click selects the whole legend. The second click selects just the Average entry. Press Delete to remove that entry.

Because the average series was converted to an XY chart, you could have a separate set of category labels for the series. This allows the markers to be at one height while displaying the label from a different value.

Amazing Things People Do with Excel Charts

The last few examples are not charts that you are likely to create. They are just a few displays that you will not believe are created in Excel. In each case, the author created the display using VBA.

Earl Takasaki submitted the Civil War chart shown in Figure 7.41 as an entry to a MrExcel.com Challenge of the Month as the most innovative use of graphics in Excel. With this chart, you can enter a cannon trajectory in an attempt to hit a target. The trajectory of the shot is graphed at the bottom of the figure.

Earl’s workbook and many other entries from this contest are available for download at http://www.mrexcel.com/pc11.shtml.

Mala Singh of XLSoft Consulting created the chart in Figure 7.42. Mala used a shape to create a cross-section of a river at the proposed location for a bridge.

Figure 7.41 The graph at the bottom teaches students about physics.

In Figure 7.43, Mala drew the bridge abutment using an XY chart in Excel.

Brett Bernardo sent in the chart shown in Figure 7.44 for his entry in the innovative charts contest at MrExcel.com. The VBA macro in this proprietary system starts with a manifest and actually draws the 3-D bundles in the Excel workbook.

You can read more about innovative chart entries at http://www.mrexcel.com/pc15.php.

Figure 7.42 Blue shapes create the profile of the river at the bridge location.

Figure 7.43 If you do not have AutoCAD, this blueprint is actually an XY chart.

Figure 7.44 VBA macros draw bundles to scale as they should be loaded on a truck.

Next Steps

In Chapter 8, “Creating and Using Pivot Charts,” you will learn how to summarize thousands of rows of detailed data into a summary chart. Pivot tables are Excel’s most powerful feature. The pivot chart feature was vastly improved beginning with Excel 2007.