Five Handy Charting Tips for Excel Developers (May 2003)

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Tushar Mehta
Microsoft Excel MVP

Frank C. Rice
Microsoft Corporation

May 2003

Applies to:
    Microsoft® Excel 2000 and later versions

Summary: Highlights a collection of handy add-ins and techniques created by Microsoft Excel MVP Tushar Mehta for use with Excel charts. For example, there is an add-in that helps you create a Gantt charts, one that lets you link minimum and maximum charts to worksheet cells, and a technique to create dynamic titles. (19 printed pages)

Download the odc_gantt.exe.

Contents

A Handy Add-In for Creating Gantt Charts
Link Minimum and Maximum Chart Values to Worksheet Cells
Use Dynamic Chart Titles
Adding a Horizontal Line to a Chart
Creating a Floating Bar Chart

Note   The samples and software in this article are copyright® Tushar Mehta and may be distributed and used freely except that they may not be repackaged or constitute any part of any commercial or shareware product.

A Handy Add-In for Creating Gantt Charts

One definition for Gantt charts is:

"A Gantt chart is a horizontal bar chart developed as a production control tool in 1917 by Henry L. Gantt, an American engineer and social scientist. Frequently used in project management, a Gantt chart provides a graphical illustration of a schedule that helps to plan, coordinate, and track specific tasks in a project. Gantt charts may be simple versions created on graph paper or more complex automated versions created using project management applications such as Microsoft Project or Excel." - from the Whatis.com Web site.

Unfortunately, there is no convenient way to use Excel to draw a Gantt chart. The most commonly used method is to customize a stacked bar chart. You do this by plotting the number of series as bar charts and then formatting the various points in order to achieve the effect of having 'floating' bars within an Excel chart. Fortunately, there is another option using an Excel add-in that was created by Microsoft Most Valuable Professional (MVP) Tushar Mehta. This add-in automates much of the process of creating the chart and updates the associated chart as the underlying data change.

Simplified Gantt Chart Sample

The following simplified example demonstrates a Gantt chart that might be used in a project. Assume that there are four people working on a project with busy times as shown below. For example, John is busy in the period ending Jan 1, idle during the period ending Feb 1, then busy all the way through to May 1.

Figure 1. Sample input data for a simplified Gantt chart

The add-in provides tremendous flexibility since it leaves the interpretation of the data in the table to the user. For instance, in the table above the names of the people could just as easily have corresponded to individual tasks.

Likewise, the interpretation of the dates at the top of the chart is also up to the user of the add-in. Each column represents a period that either starts with or ends with the specified date. Finally, the dates can be dates as in this example, or just a number indicating the number of days, weeks, months, or years since project commencement.

The Gantt chart corresponding to the information in Figure 1 would be:

Figure 2. Gantt chart based on the data in Figure 1

To Load the Gantt Chart Add-In

After saving the file on the hard disk, unzip the GanttChart.zip file. Save the GanttChart.xla and the Gantt.chm files in a convenient directory.

Start Excel and load the add-in by selecting Add-Ins from the Tools menu (see Figure 3).

Figure 3. Add-Ins option on the Tools menu

Click the Browse.button (see Figure 4), and locate and open the GanttChart.xla file saved in the previous step. Ensure that the checkbox next to the Gantt (Project) Chart entry is selected.

Figure 4. Gantt (Project) Chart option in the Add-Ins dialog box

When the Gantt Chart add-in first loads, it displays the following dialog as an alert that it is running:

Figure 5. Gantt Chart dialog box

The Gantt Chart add-in is now available for use.

How to uUse the Gantt Chart Add-In

The add-in inserts the Gantt (Project) Chart item on the TM menu. To create a Gantt chart, lay out the project data so that it forms a table similar to the one shown in Figure 1. Ensure that there is at least one empty row and one empty column to each side of the data.

Note   A worksheet (Gantt chart test.xls) with sample data is included with the download that accompanies this article. A CHM file with Help information is also included.

Select any cell within the table and select the Gantt (Project) Chart menu item.

Figure 6. Gantt chart option on the TM menu

The result will be a Gantt chart drawn with a number of series plotted in a bar chart and formatted so that only those bar segments that correspond to non-zero and non-blank table values are visible as shown in Figure 2.

To Stop Automatic Updates

Delete the chart created by the add-in.

Things to Consider

When necessary, the add-in updates the chart to reflect changes in the underlying data . When changes are made, the ability to undo the last change by clicking Undo (or CTRL-Z) on the Edit menu is lost. This is an intrinsic feature of Excel and there is not much you can do about it.

Link Minimum and Maximum Chart Values to Worksheet Cells

This Excel add-in links the minimum and maximum values of a chart's scales to worksheet cells.

Purpose of the AutoChart Add-In

One of the limitations of Excel's charts is the inability to link worksheet cells to parameters that govern how a chart axis is shown. For example, the minimum and maximum values for an axis can either be set to 'automatic' (meaning that Excel will decide what the values should be) or can be specified as a number. However, there is no way to tell Excel that it should use the contents of a particular cell. The AutoChart add-in allows you to do just that.

In addition, the add-in saves the automation information in the workbook in a form of hidden names. Consequently, the links need to be specified only once, and each time that the workbook is opened, the links will be reestablished (as long as the add-in is loaded).

The add-in figures out for itself what kind of chart it is dealing with. It uses this information to selectively enable only those fields that correspond to axes that can contain maximum and minimum values. For example, only if the chart has secondary axis that can contain maximum and minimum values will the corresponding dialog box fields be enabled. Similarly, the Series axis parameter will be enabled only if it is possible to specify the maximum and minimum values for that axis.

To Load the AutoChart Add-In

After saving the file to the hard disk, unzip the Autochart.zip file. Save the Autochart.xla and the Autochart.chm files in a convenient directory.

Start Excel and load the add-in by selecting Add-ins on the Tools menu (see Figure 7).

Figure 7. The Add-Ins option on the Tools menu

Click the Browse button (see Figure 8), and locate and open the Autochart.xla file saved in the previous step. Ensure that the checkbox next to the Autochart entry is selected.

Figure 8. The Autochart option in the Add-Ins dialog box

The Autochart add-in is now available for use.

How to Use the AutoChart Add-In

This add-in is available after a chart has been created. The chart can be in a separate sheet (that is, a chart sheet) or embedded in a worksheet (within a ChartObject). Enter the maximum and minimum values for the various axes into worksheet cells. Each of these cells can have a number that has been typed in, or a formula that yields a number.

Click the chart (or the tab of the chart sheet). On the chart menu bar, next to the Help menu, will be a new menu named TM. Select Automate Chart limits on the TM menu.

Figure 9. The Automate Chart limits option on the TM menu

This will bring up the dialog box that lets you specify the various limits for the selected chart (see Figure 10).

Figure 10. The Automate Chart Axes dialog box

Only those fields relevant to the chart will be enabled. Fields corresponding to other axes will be disabled and dimmed. For those axes whose limits are to be dynamically adjusted, specify the cell that contains the limit value. Leave other dialog box entries blank.

Chart Name

The dialog box identifies by name the chart that will be automated.

Primary Axis

Identify the cells that contain the minimum and maximum values for each of the three axes, the Category, Value, and Series axes in this area. Each entry should contain a reference to one cell. Those limits (minimum or maximum values) that do not have to be dynamically changed should be left blank.

Remember that a Series axis applies only to a 3-D chart.

Secondary Axis

For a 2-D chart, it is possible to have a secondary category axis as well as a secondary value axis. For each of these, the cells that contain the corresponding minimum and maximum values are specified in this section of the dialog box.

OK, Cancel, and Help Buttons

OK, OK, I know you know, but for the sake of completeness...

The OK button lets the add-in start its work, the CANCEL button stops the add-in from going any further, and the HELP button brings up this help information.

Stop Automatic Updates

To stop the automatic updating of the chart, go through the steps required to automate the process but this time clear out all cell references. Clicking OK will cause the add-in to stop updating the selected chart.

Things to Consider

When necessary, the add-in changes the parameters associated with the axes of the chart being automated. When changes are made, the ability to undo the last change is lost. This is an intrinsic feature of Excel and there is not much one can do about it.

An Example to Illustrate How to Use the Add-In

Start with a sample data set, such as the one shown below.

Figure 11. Sample data set for the AutoChart add-in

Chart the data set to get a graph similar to the one shown below.

Figure 12. Chart of the AutoChart sample data

Now add the formulas representing the limits for the x-axis in column D (see Figure 13). Cell D1 is the minimum value of the axis and D2 is the maximum value for the axis. Similarly, add formulas in column E to calculate the minimum and the maximum values for the y-axis. The resulting cell values and the formulas are shown in Figure 13.

Figure 13. Cell values and formulas for the AutoChart sample

Click on the chart to select it. The chart menu bar will have the menu TM. Select it and then the item Automate Chart limits as shown below in Figure 14.

Figure 14. The Automate Chart limits option of the AutoChart add-in

The Automate Chart Axes dialog box for the example

Set the references for the minimum and maximum values for the x- and y- axes to the appropriate cells as shown in Figure 15 below.

Figure 15. The minimum and maximum setting for the sample data

The final result Figure 16 and Figure 17 below show the result of automatic maintenance of the axes limits. Once the OK button in the dialog box is clicked, the chart will reflect the values in cells D1:E2. As the cell values change, the chart will update automatically.

Figure 16. Chart reflecting values in cells D1 through E2

Changing the data points and then forcing a recalculation (F9) yields different numbers in the chart. This also causes the minimum and maximum values associated with the x- and y-axes to change (see cells D1:E2 in Figure 17 below). The final effect is that the axes and the plotted data points change in the associated chart.

Figure 17. Chart reflecting randomly generated values in cells D1 through E2

Use Dynamic Chart Titles

In this set of steps, you will create a dynamic chart title that changes based on the value in a cell.

1. Create a chart

   The example below uses a simple XY scatter chart.

   

   Figure 18. Simple XY scatter chart

2. Add a title

   Use Chart Options on the Chart menu to add a title. This is shown in Figure 19 and Figure 20. Note that if you use the chart wizard to create a chart, the title can be added in one of the wizard's dialog boxes.

   

   Figure 19. Chart Options item from the Chart menu

   

   Figure 20. Title added to sample scatter chart

3. Change the title to a dynamic title.

   Select the title box as shown in Figure 21 .

   

   Figure 21. Scatter chart with the title selected

   Type the '=' key (without the quotes) and click on cell that will contain the formula for the title. In this example, I use cell D1 (see Figure 22). Press the ENTER key to lock in the cell.

   

   Figure 22.Cell to contain formula for the title

4. Enter the required formula in cell D1.

   In this example, I used the formula shown in Figure 23.

   

   Figure 23. Formula to make the title dynamic

5. The final result is shown in Figure 24.

   

   Figure 24. The finished result

Adding a Horizontal Line to a Chart

There are many instances when one needs to add a visual indicator to a chart. This could be a line indicating a goal of some sort. Or, it could be a line showing the average of the graphed data. In Total Quality Measurement (TQM) control charts, it could be a line so many standard deviations from the mean.

One way to draw such a line would be to calculate the values in the cells in a worksheet, and plot these cells on the chart. Another way is to use a named formula, which means that the charted threshold values do not show up on the worksheet. This section shows how to use a named formula.

The example uses the data set shown below.

Figure 25. The sample data for the horizontal line sample

First, create the chart from the data set. The example uses a XY Scatter chart.

Figure 26. Sample data charted in the XY Scatter chart

Next, define two names. The first, aRng, is just for convenience. The second, meanLine, is the one that generates as many numbers as in the original data, each equal to the average.

Figure 27. Values for named formulas

To create the first named formula, open the Define Name dialog box by pointing to Name on the Insert menu and then clicking Define. In the Names in Workbook box, type aRng. In the Refers to box, type =sheetname!$C$4:$D$18. If you are using the worksheet in the download, just substitute 'Horizontal Line' (with single quotes) for the sheet name. Repeat these steps for the meanLine name, except that the Refers To box will contain the formula =AVERAGE(OFFSET(aRng,0,1))*ROW(aRng)/ROW(aRng).

Figure 28. The Define option on the Name menu item

Tip   How does the meanLine named formula work?

• OFFSET(aRng, 0,1) selects the 2nd column of data, that is, the y-values.
• AVERAGE() gives the average of y-values.
• ROW(aRng)/ROW(aRng) creates an array of ones. The array has as many data points as in the original data.
• Multiplying (b) by (c) replaces the ones in the array by the average value.

Next, add a new series to the chart, using this newly defined name (meanLine). To do this, click the chart, and click Source Data in the Chart menu. Click the Series tab, click Add, and in the X Values box type 'Horizontal Line'!$C$4:$C$18. In the Y Values box, type Book1!meanLine and then click OK where Book1 is the name of the workbook.

Now that you have added a new series to the chart, using this newly defined name, you should have something similar to the following figure.

Figure 29. The resulting line with markers

Format the new series to hide the markers and show the line.

The job is done!

Figure 30. The resulting line of average values

Creating a Floating Bar Chart

Consider a company that wants to manufacture new toys every so often. It has three products code named A, B, and C respectively. Management would like a chart illustrating when each product will be made. The firm's production plans are:

	A	B	C
6	Product	Start	End
7	A	03/01/99	04/30/99
8	B	04/01/99	06/30/99
9	C	05/01/99	07/30/99

First, using the data above, create the table below:

	A	B	C
12	Product	Start	Duration
13	A	03/01/99	2/29/1900
14	B	04/01/99	3/30/1900
15	C	05/01/99	3/30/1900

Note that the Duration column contains the formulas:

	C
12	Duration
13	=C7-B7
14	=C8-B8
15	=C9-B8

Next create a bar chart (sub-type: stacked):

Figure 31. Stacked bar chart

Finally, modify the result by setting the first (blue) series to no border and no area and adjusting the axis values (min. and max.), and other formatting changes:

Figure 32. Floating Bar Chart

About the Authors: Tushar Mehta has been enthusiastic about Excel since he first used it in 1983. His Web site contains tutorials and add-ins developed for his consulting work as well as for his MBA and Ph.D programs. Microsoft has recognized him as a Most Valuable Professional (MVP) since 2000.

Frank C. Rice is a programmer writer for the Office Developer Center on the Microsoft Developer Network. Frank is the author of numerous articles on a number of Office applications and technologies and a regular contributor to the Office Talk and the Office Power User columns.