Microsoft Excel Charting FAQs

• Chart Data
• Setting Up the Chart's Data
• Changing the Chart's Data
• Adding a Series to the Chart
• Charting Data from Different Worksheets or Workbooks
• Positioning, Sizing, and Aligning Embedded Charts
• Copying a Chart
• Copying an Embedded Chart
• Copying a Chart Sheet
• Exporting a Chart
• Naming a Chart
• Link Chart Text to Cell Text
• Chart Types
• Chart Types Not Available in Standard or Custom Types
  (Make your own Combination Charts)
• Stacked-Clustered Column Chart
• Saving a User-Defined Chart Type
• Data Labels
• Using Text in Cells for Data Labels
  
• Hiding Labels that Show Zero Value
• Showing Label, Value, and Percent Together on a Pie Chart
• Hyperlink to a Chart
• Chart Axis Questions
• Log Scale Axes
  
• Secondary Axes
• Axis Tips and Tricks
• Gaps in Chart Series; Blanks Chart as Zero
• X Axis Plots Like the Numbers 1, 2, 3, not Like the Actual Values; Points Drop to Zero
• Data Tables
• Chart Colors
• Charting A Subset of the Data
• Filtering the Data (Screening According to Selection Criteria)
• Decimating the Data (Plotting Every Nth Point)
• Common Errors and Problems with Excel Charts
• No New Fonts May Be Applied in This Workbook
• Refreshing a PivotChart Removes Formatting
• 3D Charting Deficiencies
• Limitations of Shared Workbooks and Grouped Worksheets
• Links to Charting Resources

Setting Up the Chart's Data

The following data arrangement is appropriate for most charts you will make. Naturally, you don't have to use this strict arrangement for your data. But when you deviate from this structure, you will have to do more work to make the chart come out right. If you can maintain the "first row-first column-top left cell blank" scheme for each series, it will make your charting efforts easier and more error-resistant.

The data range consists of four regions: the top left cell, the top row, the first column, and the bulk of the range. The top left cell should be kept blank; its presence tells Excel that the first row and the first column are special. The top row of the data range is used for the names of the series (the text entries in the legend); this is highlighted in green in the range pictured below. The first column of the range contains the X values (or category labels) for all of the series in the chart; this is highlighted in purple. Finally, the columns in the rest of the data range contain the Y values for each of the series; this range is highlighted in blue.

This arrangement assumes you are plotting the series by columns, which is my preferred option. If you are plotting by rows, you still keep the top left cell blank, the first column contains the series names, and the first row contains the X data.

When the data is neatly arranged, select the range to be charted and run the Chart Wizard to make your chart. If a single cell is selected, Excel will use the complete range of filled cells containing the active cell.

When you select a chart which is based on a simple data range like this, the data range is highlighted with the colors shown in the image above. If the data range is more complicated, e.g., because series have unequal numbers of points or series don't share their X data, it will not be highlighted. When you select a single series in the chart, its data range is highlighted in the same colors.

back to top

Changing the Chart's Data

Colored Highlight

The easiest way to change the chart's data, if the chart is embedded in the same worksheet that contains the data, is to select the chart, and drag the colored outlines that highlight the chart's data. Suppose our sample chart uses the data highlighted in the range shown below:

If you drag a colored outline by an edge, the outlined area will move to include a new range. Drag the blue or purple outline downward to use points 2-6 or points 3-7 in the chart instead of points 1-5. Drag the blue or green outline to the right to plot series Bart and Clara instead of series Alex and Bart.

If you drag the outline by the square in the bottom right corner, the outlined area will grow or shrink. Drag the blue or purple square downward to add points to the existing series (or upward to remove points). Drag the blue or green square to the right to add another column, that is, add a series, to the chart (or to the left to remove a series).

In this illustration the chart's entire data range is highlighted. If the chart's data range is more complex, for instance, if the series have different X value ranges or different numbers of points, the chart's data range will not be highlighted. If you select a single series, only the data range for the selected series is highlighted in this way.

Source Data

You can access the Source Data dialog from the chart menu, or from the context menu that pops up when you right click on a chart. This is the same as the Source Data dialog which is Step 2 of the Chart Wizard. The Data Range tab has a range selection box that allows you to select the entire data range for the chart. Click in the range selection box, then select a range with the mouse. After clicking in the range selection box, you can select a data range from a different worksheet using the chart tabs, or from a different workbook using the Windows menu.

Chart Series Formula

When a chart series is selected, the name of the series is shown in the Name Box (above cell A1) and the Series Formula is displayed in the Formula Bar (to the right of the Name Box, above the column headers). In the figure below, I've used blue text to highlight the series name in the Name Box and red text to highlight the series formula in the Formula Bar.

The chart series formula for the selected series is:

=SERIES(Sheet1!$C$5,Sheet1!$B$6:$B$10,Sheet1!$C$6:$C$10,1)

This formula can be broken up into four elements as follows:

=SERIES([Series Name],[X Values],[Y Values],[Plot Order])

The [Series Name] can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The [X Values] can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The [Y Values] can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The [Plot Order] can be a whole number between 1 and the number of series in the chart.

You can edit the series formula directly. You can type any replacement text, or highlight one of the elements (including sheet name and cell reference) and with the mouse select a different range for that element of the formula. You can select a data range from a different worksheet using the chart tabs, or from a different workbook using the Windows menu. Press the Enter key to implement your changes.

If a range reference refers to a range in another workbook, the workbook's name is surrounded by square brackets and is placed in front of the worksheet name. If there is a space in the worksheet or workbook name, you must place the worksheet and workbook names within single quotes.

back to top

Adding a Series to the Chart

Colored Highlight

You can add a series to the chart by selecting the chart, then stretching the highlighted ranges, expanding the chart's data range as described in Changing the Chart's Data, above.

Source Data

Right click on the chart and choose Source Data from the pop up menu, then click on the Series tab. Click the Add button under the list of series. Use the range selection boxes to tell Excel which data ranges to use for your new series.

Chart Series Formula

One easy way to add a series is to select an existing series, copy the text of the series formula from the Formula Bar, press Enter or Esc to take the focus off the formula, select the chart area or plot area of the chart, click in the formula bar, and paste. This adds a new series with the same formula as the copied series; edit the series formula to reference the appropriate data range.

Select and Drag Worksheet Range

Select a worksheet range, and drag it onto the chart. Excel will add the data to the chart according to the settings established by the existing series in the chart. This may or may not add the data the way you intend.

Copy and Paste Worksheet Range

Copy a worksheet range, select a chart, and paste. Excel will add the data to the chart according to the settings established by the existing series in the chart. This may or may not add the data the way you intend.

To have more control over the process, copy the worksheet range, select your chart, and select Paste Special from the Edit menu. You are presented with a form which lets you set your preferences for how to add the data to the chart.

back to top

Charting Data from Different Worksheets or Workbooks

Source Data

Select the chart and choose Source Data from the Chart menu, or right click on the chart and choose Source Data from the pop up menu. Click in a range selection box in the Data Range tab or the Series tab, then select a data range from a different worksheet using the chart tabs, or from a different workbook using the Windows menu.

Chart Series Formula

Select a series to edit the series formula directly. Type a different sheet name or preface the sheet name with a different workbook name in square brackets. Alternatively, highlight one of the elements (including sheet name and cell reference) and with the mouse select a different range for that element of the formula. You can select a data range from a different worksheet using the chart tabs, or from a different workbook using the Windows menu. Press the Enter key to implement your changes.

A series with data from different worksheets and workbooks might look like this:

=SERIES(Sheet1!$C$5,Sheet2!$B$6:$B$10,'[Book4.xls]Sheet 3'!$C$6:$C$10,1)

Note the square brackets around Book4.xls, the source of the Y Values, and the single quotes around the book and sheet name, because the sheet name contains a space.

Copy and Paste Worksheet Range

Copy a worksheet range from any worksheet, switch to the chart and select it, and paste. Excel will add the data to the chart according to the settings established by the existing series in the chart. This may or may not add the data the way you intend.

To have more control over the process, copy the worksheet range, switch to your chart and select it, and select Paste Special from the Edit menu. You are presented with a form which lets you set your preferences for how to add the data to the chart.

back to top

Positioning, Sizing, and Aligning Embedded Charts

When moving a chart with the mouse, hold down the Shift key to constrain its movement to follow a vertical or horizontal line. Hold down the Alt key to make the chart snap to the worksheet's cell boundaries. Hold down to Ctrl key to move a copy of the chart.

When resizing a chart with the mouse, hold down the Alt key to snap the moving edges of the chart to the worksheet's cell boundaries.

To align multiple charts, hold down the Alt key while moving each one, so they are aligned along the same rows and columns. You can hold down the Shift key to select multiple charts, then from the Draw menu on the Drawing toolbar, choose Align or Distribute to align the selected charts.

back to top

back to top

The Copy-Paste, Copy-Paste Picture, and Copy Picture-Paste procedures described for embedded chart objects above are applicable for copying a chart sheet for use in another application.

back to top

Exporting a Chart

You can export a chart to an image file using VBA. The syntax you need is this:

  Chart.Export [filename], [filter]

where [filename] is the path and file name of the chart image file, and [filter] is the filter for the file type. Among the filters that will work are "GIF", "JPG", and "TIFF". This macro shows a working example:

  Sub Create_GIF()
      Dim mychart As Chart
      Set mychart = ActiveSheet.ChartObjects(1).Chart
      mychart.Export FileName:="c:\Mychart.gif", FilterName:="GIF"
  End Sub

The best filter to use for exporting an Excel chart is "GIF", because it works well with line drawings: images that have regions of uniform (unvarying) color, with sharp transitions between colors, and relatively few colors. Lines and Text look good in GIF images. A lot of people think "JPG" should be better, but it is better suited to images like photographs, which have gradual transitions in color intensity, hue, and brightness. Text and other sharp features come out poorly in JPG images. Note: all of the images in this article are in GIF format.

John Walkenbach's Chart Tools (a free add-in downloadable from http://j-walk.com) includes a Chart Export utility.

back to top

Naming a Chart

Excel automatically assigns a name to every chart embedded in a worksheet. The default names are like "Chart 3" or "Chart 17", according to the order in which they were created. VBA procedures often encounter problems because they do not operate on the correct chart, because of uncertainties with the chart names.

Charts are actually referred to by the names of their parent chart objects. To rename a chart object, select a cell, then hold Shift while selecting the chart. It is surrounded by white resizing handles. Type the new name in the Name Box (above cell A1, to the left of the Formula Bar) and press Enter.

To rename the chart using VBA, select the chart and run the following macro:

  Sub RenameChart()
      ActiveChart.Parent.Name = "My Chart" ' Select an appropriate name
  End Sub

To refer to the chart in VBA, use this syntax:

  Sub ActivateNamedChart()
       ActiveSheet.ChartObjects("My Chart").Activate
  End Sub

back to top

Link Chart Text to Cell Text

You can link the text in chart elements to worksheet cells so that changing the worksheet text will change what is displayed in the chart. This technique works for the Chart Title, Axis Titles, individual Data Labels, and partially works with Text Boxes. Select the element so it is highlighted with a thick gray border. You need two clicks to select a data label: one click to select all the labels for a series (even if there's just one), and the second to pick the one you want. With the element highlighted, press the Equals key, click in the cell with your mouse, and press Enter. The text element shows the text in the linked cell, while the Formula Bar shows the link, in the form of a formula, for example, =Sheet1!$A$1.

Text Boxes don't seem to work so nicely, at least not in my installation of Excel 2000. To create an unlinked Text Box, select the chart area or plot area of a chart, type something, and press Enter. To create a linked Text Box, select the plot area or chart area, press the Equals key, select a cell, and press Enter; the text in the selected cell is shown in the Text Box, while the Formula Bar shows the link to the cell.

The link in a linked Text Box can be edited in the Formula Bar, to change the link. An unlinked Text Box cannot be made to link to a cell. The process described above merely puts an equals sign into the text box. Selecting the Text Box, clicking in the Formula Bar, then pressing Equals and selecting a cell, leaves the unlinked Text Box unchanged, and creates a new linked Text Box. Apparently the working procedure is to delete the unlinked Text Box, and create a new linked Text Box.

back to top

Chart Types Not Available in Standard or Custom Types

Make your own Combination Charts

Excel comes with a brief selection of "Built-In Custom" Chart Types. What if the chart type you envision isn't included? It is an easy matter to make your own combination chart, using almost any combination of chart types Excel offers.

Bubble charts, and charts with 3D effects, cannot be used in combination charts. You can combine other types to get interesting charts you would not have expected, given the options offered by Excel. For example, you can make a Speedometer-type chart by combining the Donut and Scatter chart types.

The easiest way to make a combination chart is to create a chart of one type with all the data. Right-click a series which you want to change to another type, choose Chart Type from the pop up menu, and select the chart type you want. Repeat for other series that you want to change. You can add more series, then change their chart types in the same way.

back to top

Stacked-Clustered Column Chart

Through careful arrangement of the data in your worksheet, you can make a stacked column chart that looks like a clustered-stacked column chart.

There is a tutorial showing this technique on Bernard Liengme's site:
- http://www.stfx.ca/people/bliengme/ExcelTips/Columns.htm

which is based on the example on Stephen Bullen's web site:
- http://oaltd.co.uk/Excel/Default.htm
Go to Charting Examples, and download FunChart4.xls

The following MSKB article has an example:
XL2000: Creating Charts with Multiple Groups of Stacked Bars
- http://support.microsoft.com/default.aspx?scid=kb;en-ca;214119

back to top

Saving a User-Defined Chart Type

Excel comes with a brief selection of "Built-In Custom" Chart Types. But you've just created a wonderful custom chart, that you want to be able use frequently, without having to format it from scratch. You can save the chart as a "User-Defined Custom" chart type.

Start with your carefully customized chart. Right click on the chart, and pick Chart Type from the pop up menu (or choose Chart Type from the Chart menu). Click on the Custom Types tab, click User-Defined at the bottom, then click the Add button. Type a suitable name and description for this chart, then click Okay. If you want this to be the default chart type, click the Set As Default Chart button at the bottom before pressing Okay again.

When making a new chart, you can choose this custom chart type in Step 1 of the Chart Wizard, or you can apply it to an existing chart by selecting Chart Type from the Chart menu (or by right-clicking on the chart and choosing Chart Type from the pop up menu).

In VBA, you would apply the user-defined chart type "MyChartType" to the active chart using this syntax:

  ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="MyChartType"

back to top

Using Text in Cells for Data Labels

Excel lets you apply data labels to each point in a series, with options to use the Y values or the X labels in the data labels (pie charts allow a few other options as well). You can manually change each label, but what if you want to use a range of cells in your worksheet as data labels for your chart series? There are a couple useful and free Excel add-ins that can automatically label data points, using labels in a worksheet range:

• Rob Bovey's Chart Labeler, http://appspro.com
   
• John Walkenbach's Chart Tools, http://j-walk.com

back to top

Hiding Labels that Show Zero Value

Data labels are useful, but they can clutter a chart, particularly if you have extraneous labels that show "0.00" or "0%" for points that have zero value. You can manually delete these individual labels, but if the data changes, the new nonzero values will not be labeled, and more zero labels may appear. A better solution is to use a custom number format that doesn't display zero values. Double click the labels, select the Number tab, and click on Custom in the left-hand list. A number format generally consists of four format options, separated by semicolons:

  [format if positive];[format if negative];[format if zero];[format if text]

Define a custom format that provides no format for zeros to prevent their appearance, such as one of these:

  0;;;

  0%;;;

back to top

Showing Label, Value, and Percent Together on a Pie Chart

Excel lets you apply data labels to each wedge in a pie chart, with options to use the values, percents, labels, or labels and percents. How can you get labels, values, and percents? There are a few approaches.

Normally I strongly suggest arranging your data as shown below left, with category labels (X values) in the leftmost column, values (Y values) in one or more columns to the right, a blank cell above the labels, and a series name above each value column. Select the entire range including the header row (A1:B6), run the chart wizard, choose a Pie Chart in Step 1; click okay in Step 2; and in Step 3, on the Data Labels tab, select the Labels and Percent option. The chart's labels are two rows high: top row label, bottom row percent, as shown below right. There is no apparently easy way to get the values into the data labels.

One way to get labels, values, and percents into the data labels works when you put the labels to the right of the values, as shown below, at left. Select just the values (range B1:B6 below), start the Chart Wizard, choose a Pie Chart in Step 1; in Step 2 click on the Series tab, click in the Category Labels range selection box, and select the range of values and labels (range B2:C6 below); and in Step 3, on the Data Labels tab, select the Labels and Percent option. The chart's labels are three rows high: top row label, middle row value, bottom row percent, as shown below right. The legend contains values and labels, in the confusing order of values before labels. With the data labels, the legend should probably be removed since it's redundant.

Another way to get labels, values, and percents into the data labels works when you insert a column between the labels and the values, as shown below, at left. In the first data row of the new column (cell B2), enter a formula that concatenates the label in column A with the value in column C; a formula like this works:

  =A2&": "&C2

Drag the formula down to fill the range B2:B6. Select the concatenated labels and values (range B1:C6), start the Chart Wizard, choose a Pie Chart in Step 1; click okay in Step 2; and in Step 3, on the Data Labels tab, select the Labels and Percent option. The chart's labels are two rows high: top row label and value, bottom row percent, as shown below right. The shorter labels allow a larger pie within the same size chart object than the first approach. The legend contains labels and values, in the form concatenated for the data labels. As above, the legend should probably be removed since it's redundant.

A third method to get anything you want into the data labels is to start with the original data arrangement, and add formulas in column C to concatenate the labels, values, and computed percentages; a formula like this:

  =A2&": "&B2&" ("&TEXT(B3/SUM(B$2:B$6),"0%")&")"

Select the labels and values (range A1:B6), create a Pie Chart, then use Rob Bovey's Chart Labeler, http://appspro.com, or John Walkenbach's Chart Tools, http://j-walk.com, to apply the concatenated labels in C2:C6 to the pie chart.

back to top

Hyperlink to a Chart

Hyperlink to an Embedded Chart

You can't hyperlink to a chart directly, but you can link to a cell on the worksheet in which the chart is embedded. Use the cell under the top left corner of the chart, or the entire range under the chart.

Hyperlink to a Chart Sheet

You can't hyperlink to a chart directly, and a chart sheet has no underlying cells you can link to. You can fake it with a Worksheet_SelectionChange event procedure, however. In this example, the hyperlink is in cell B2. Enter the name of the chart sheet in cell B2, and format it with blue underlined text, so it looks like a real hyperlink. Right click on the sheet tab, select View Code, and paste this macro into the code module that
appears:

  Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
      If Not Intersect(Target, Range("B2")) Is Nothing Then
           Cancel = True
      End If
  End Sub

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Not Intersect(Target, Range("B2")) Is Nothing Then
           On Error Resume Next
           Charts(Target.Value).Activate
           If Err.Number <> 0 Then
               MsgBox "No such chart exists.", vbCritical, _
                     "Chart Not Found"
           End If
           On Error GoTo 0
      End If
  End Sub

When the user selects cell B2, the procedure is activated. The code jumps to the sheet with the name in the cell. If it can't go to that sheet, it assumes it's because the sheet doesn't exist, and it alerts the user.

back to top

Log Scale Axes

Any value axis can be turned into a logarithmic scale axis. This includes any Y axis, and the X axis from an XY Scatter chart. If you have a Line chart, you must convert it to an XY Scatter chart before changing the axis to logarithmic. Double click on the axis, and click on the Scale tab. If it says Category Axis Scale under the tabs, you cannot convert the axis without changing the chart type. If it says Value Axis Scale just under the tabs, you will also see a Logarithmic Scale checkbox near the bottom. Check this checkbox, and set appropriate scaling parameters.

Excel's built-in logarithmic scale allows only complete decades along the axis, meaning the minimum and maximum scale parameters both must be powers of ten. You can simulate intermediate values for min and max following the Flexible Log Scale technique on Tushar Mehta's web site, http://tushar-mehta.com.

back to top

Secondary Axes

You must have at least two series in a chart in order to display secondary axes: one series is needed for each axis group. Double click on the series you want to move to the secondary axis, click on the Axis tab, and select Secondary. Repeat for any other series you want to place on the secondary axis.

By default, Excel only shows the secondary Y axis. Right click on the chart, and select Chart Options from the pop up menu. On the Axes tab, you have the ability to turn on and off any of the primary and secondary, category and value axes in the chart.

back to top

Axis Tips and Tricks

Entering Dates or Times as Axis Scale Parameters

Excel uses whole numbers for dates and decimal fractions for times, and displays these in the Axis Scale dialog. Rather than struggling to determine which day number corresponds to a particular date or which fraction represents a particular time, you can enter the desired date or time in any format Excel recognizes (e.g., 1 jan 2003 or 14:30:00), and Excel will convert them when you close the dialog.

Axis Crosses at Minimum

Excel provides a checkbox for Axis Crosses At Maximum, but omits one for Axis Crosses At Minimum. One trick to ensure that the axis crosses at the minimum value is to enter an unrealistically large negative number (e.g., -999999999999) in the Axis Crosses At box. If this value is less than the axis minimum, Excel crosses the axis at the minimum. Note: for a logarithmic scale axis, use an unrealistically small fraction (e.g., 0.0000000001).

Break in an Axis

Excel has no built in way to show a break in an axis, for example, when one value in a series is much larger than the others. There are a few was to handle this deficiency. First, you could use a logarithmic scale for the data. Second, you could plot the larger value on a secondary axis. Unfortunately both of these methods may not be clear to the viewer. A third approach, too long to be presented here, is to simulate the breaks is the axis and the data using the Broken Y Axis technique shown on my web site, http://peltiertech.com/Excel/Charts/BrokenYAxis.html.

More Axis Tips and Tricks

There are several other axis tricks presented on my web site:
- http://peltiertech.com/Excel/Charts/axes.html

back to top

Gaps in Chart Series; Blanks Chart as Zero

Gaps in Chart Series

There are several ways Excel may treat a blank cell in a series of data, shown below for a sample series. Given the data shown below, Excel may simply not plot the data, leaving a gap (the default behavior), plot the empty cell as zero, or interpolate by drawing a line spanning the points on either side of the blank cell. Select the chart, choose Options from the Tools menu, and click on the Chart tab to select the option you want used for this chart.

Blanks Chart as Zero

If the apparent blank cell is the result of a formula returning a zero-length string, the chart will not respond to any selection in Tools > Options > Chart > Plot Empty Cells As, above. The cell isn't empty, it contains a text value, treated as zero in any Excel chart. The other alternative is to interpolate over this non-empty cell, by changing the "" returned by the formula to NA(), which puts the #N/A error into the cell.

The #N/A looks ugly in the worksheet, but charts nicely. To fix the appearance of the worksheet, use conditional formatting to hide the error. Select the range of cells, choose Conditional Formatting from the Formatting menu, pick Formula Is from the Condition 1 dropdown, and enter this into the formula box, where A1 is the address of the top-left cell in the range:

  =ISNA(A1)

Click the format button, and choose a font color that matches the cell's background color (usually white).

back to top

X Axis Plots Like the Numbers 1, 2, 3, not Like the Actual Values; Points Drop to Zero

Your data is spread uniformly across the X axis, with the points arranged left to right in the order they appear in the worksheet, not according to the actual X values.

There are a few causes for this behavior. First, make sure you are using an XY Scatter chart, not a Line chart (see Scatter Chart or Line Chart?). Second, make sure you are using the right range for your series' X Values: select Source Data from the Chart menu, click on the Series tab, and be sure a range is specified in the X Values range selection box. Finally, make sure all the numbers in the range are interpreted by Excel as numbers, not as text. Often numbers imported from another program will be treated as text, and it only takes one non-numeric cell in the range to mess up the entire axis. See below for advice on detecting and fixing this problem.

One or more points in your chart plunge to zero, even though there aren't zeros in the cells.

Make sure all the numbers in the range are interpreted by Excel as numbers, not as text. Often numbers imported from another program will be treated as text. See below for advice on detecting and fixing this problem.

How can you tell if you have any text values? And how can you convert them to numbers?

A number is right-aligned in the cell by default, while text is left aligned. This is the easiest way to tell whether the cell has numbers or text in it, and is why I only specify alignment in the pages that some manager will print out. If your cells look like numbers (except for alignment) but are really text, it's easy enough to fix them: select and copy a blank cell, select the cell(s) to be converted, select Paste Special from the Edit menu, and select the Values and Operation-Add options. Forcing Excel to perform a mathematical operation on a cell causes the cell to be re-evaluated as a number.

back to top

Data Tables

About Data Tables

At first glance, Data Tables sound like a nice addition to Excels charts. A Data Table is a table of charted data right in the chart, where it can be quickly reviewed alongside the chart. For a simple chart, a Data Table is a nice feature. But it seems that there are more limitations than capabilities.

Data Tables are available for line, column, area, and barchart types only. In line, column, and area charts, the columns of the data table lines up nicely below the corresponding category label on the X axis. Formatting of data tables is rudimentary at best. It is not possible to highlight or differentiate selected values in the table using colored text, bold or italic, or thicker cell borders. And all the corresponding data must appear both in the chart and in the data table (of course, you can format a chart series so it is not visible). There is no provision for comments or labels in the data table.

Alternative Approach

One way to place numeric values in the chart to enhance comprehension is simply to use data labels, showing the categories (X data) or values (Y data). Or you can use custom data labels, as described elsewhere in this article.

If I want to display a table of numbers with my chart, I embed the chart in a worksheet, then create my own table in the cells beneath or beside the chart. I can put anything I want in this table, and I can format it however I want. These can be the values which are charted, or they can be linked to the charted values, or they can be completely static. Best of all, I have total control over them. I'm not limited in the chart types I can use, I can add annotation to the table, and best of all, I'm not giving up prime real estate in my chart.

back to top

Chart Colors

You can specify which default colors Excel uses in your charts. Select Options from the Tools menu, and click on the Color tab to bring up Excel's color palette (see below). A palette is limited to 56 colors, but each workbook can have its own color palette. To change any of the colors, select the color, and press the Modify button. Pick a color from the Standard tab, or dial up a color (by RGB if you want) on the Custom tab. The colors in the bottom row ("Chart lines") in the Color dialog are used for the first 8 line or scatter series in a chart. The colors in the row above this ("Chart fills") are used for the first 8 column, bar, or area series in a chart; the colors in the bottom row are used for series 9 to 16.

back to top

Filtering the Data (Screening According to Selection Criteria)

Suppose you have a table of data, perhaps figures for several companies, and you want to chart only the data for a single company. You can apply data filters to the table. Select one cell within the table, or select the entire table, then select Filter from the Data menu, and select AutoFilter. Cells in the header row now have drop down arrow indicators beside them. Click on one of these arrows, and a list of the distinct values in that column appears. For example, the dropdown above column A in the table at left contains the items AAA, Bravo, and Cornwall, as well as (All), (Top 10), and (Custom). Selecting AAA from this list hides the rows with other values in that column, as shown below right. The arrow indicator and visible row headers turn blue to indicate that the list is filtered. Excel's default behavior is not to chart hidden values, so a chart initially created from the entire table will change to display only the filtered rows. To change this default behavior, select the chart, pick Options from the Tools menu, click on Chart, check or uncheck the Plot Visible Cells Only checkbox.

back to top

Decimating the Data (Plotting Every Nth Point)

Sometimes a data set contains many points, and putting a marker on each point obscures the details of the series. But it may still be desirable to use markers to identify the series. You can use worksheet formulas to "decimate" the data, allowing you to use a marker for only one point out of several. The data set below shows the very beginning of a much larger w orksheet.CellA1isblank,cellB1hasthelabelY1,cell C1 has the label Yn, and cell D1 has the desired frequency of markers (5 in this case). The X data are in A2 to A1000 and the Y data are in B2 to B1000. C2 to C1000 will hold every 5th point from column B. The formula in C2 is

  =IF(MOD(ROW()-ROW(C$1),$D$1)=0,B2,NA())

This formula puts the value from column B into column C at the frequency indicated in cell D1, with #N/A error values (which are ignored by the chart) in between. An XY Scatter chart of a portion of the data is shown beside the table: column B is charted using a line without markers, while column C is charted with markers and no line.

back to top

No New Fonts May Be Applied in This Workbook

When you have multiple charts each on several worksheets within a workbook, you may burn through the system resources Excel uses for charts. Apparently Excel needs dedicated font resources for each chart (even if the charts use the same fonts), and if font autoscaling is enabled, the resource requirements are doubled. Font autoscaling can be disabled for each chart to alleviate this resource crisis.

  XL2000: Error Copying Worksheets Containing Charts (215573)

I've compiled more information and workarounds for this issue on my web site, including a registry hack to turn off font autoscaling by default, and VBA code to fix the fonts in individual charts.

  http://peltiertech.com/Excel/Charts/FixFonts.html

back to top

Refreshing a PivotChart Removes Formatting

When a pivot table or pivot chart is refreshed, or the layout of the pivot is changed, or individual items in a pivot field are hidden, custom series formatting in the chart may be lost. This is because Excel removes series from the pivot chart, then adds them back with default formatting. Even Microsoft suggests recording a macro to apply your custom formatting, then rerunning the macro after changing the pivot table.

  XL2000: Changing a PivotChart Removes Series Formatting (215904)

Pivot charts suffer from additional shortcomings. There are limited chart types available to pivot charts, and you cannot add data from outside the underlying pivot table to the chart. These issues can be worked around by creating a regular chart from dummy data, then changing the data ranges of the chart's series to refer to parts of the pivot table.

back to top

3D Charting Deficiencies

In general, 3D charts have problems with accuracy and comprehension. The angular relationships make it difficult to determine exactly where a data point sits on any axis, particularly with varying perspectives. If there is a gap between a marker (column or bar) and the axis or gridlines, the paralax effect leads to inaccuracies. Paralax and perspective can even distort the data presentation. In a simple chart, the 3D effects add nothing except perhaps the opportunity for more colors and shading effects, and can decrease the viewer's ability to read the chart precisely.

Excel does not have a 3D XYZ Scatter charting capability. You can make a surface or contour chart, for fixed X and Y categories, with a Z value for each X-Y grid point. These charts provide no more information than the 3D Column chart type, which has a 2D array of columns, of variable height. Andy Pope has a simulated 3D scatter chart example, which makes use of worksheet trignometry formulas (http://andypope.info/charts.htm).

Excel's charts that make use of 3D "visual effects" (column, bar, line, pie, area, cylinder, cone, pyramid) have shortcomings compared to Excel's 2D chart types. Combination of different chart types is not possible, and there are no data label positioning options.

back to top

Limitations of Shared Workbooks and Grouped Worksheets

In a shared workbook, you cannot insert or change charts. You can change the Shared setting of a workbook through the Share Workbook command on the Tools menu. Check the topic "Limitations of shared workbooks" in the help files to uncover other problems with shared workbooks.

Grouped worksheets seem to suffer from many of the same disabilities as shared workbooks. On grouped worksheets, charts cannot be created or changed. Unfortunately documentation on this topic is scarce.

back to top

Links to Charting Resources

What follows are two rather incomplete lists of links. The first list pertains to Excel charting techniques, tips, and workarounds. The second list concerns good graphical practices relevant to Excel charting and to any other charting exercises.

Excel Charting Resources

• http://geocities.com/jonpeltier/Excel/Charts/index.html
  A tribute to compulsive behavior by its host, me.
   
• http://tushar-mehta.com
  Tushar Mehta's web site, which includes charting content and much more.
   
• http://j-walk.com
  Web site of John Walkenbach, author of myriad Excel books, including the only book available on Excel Charts; also the author of Chart Tools, a very useful add-in.
   
• http://appspro.com
  Site of Excel MVP Rob Bovey, where you'll find the indispensible Chart Labeler add-in.
   
• http://oaltd.co.uk/Excel/Default.htm
  Stephen Bullen's web site, which features a section on advanced charting techniques.
   
• http://andypope.info/charts.htm
  Web site of Andy Pope, who has developed some creative charting solutions.
   
• http://edferrero.m6.net/Charting.html
  Ed Ferrero's charting examples.
   
• http://www.prodomosua.it/ppage02.html
  Fernando Cinquegrani's web site, containing many downloadable utilities (it's in Italian, but worth it for the clever techniques he's implemented).
   

Non-Excel Charting Resources

• http://www.edwardtufte.com
  The Work of Edward Tufte and Graphics Press
   
• http://www.washington.edu/computing/training/560/zz-tufte.html
  Graphics and Web Design Based on Edward Tufte's Principles
   
• http://www.math.yorku.ca/SCS/Gallery/intro.html
  Gallery of Data Visualization (graphics guidelines, not Excel oriented).
   
• http://lilt.ilstu.edu/gmklass/pos138/datadisplay/goodcharts.htm
  Gary Klass: Constructing Good Charts and Graphs (non-Excel with links to Excel tips).
   
• http://www.itl.nist.gov/div898/handbook/index.htm
  NIST - SEMATECH Engineering Statistics Internet Handbook
   
• http://mathworld.wolfram.com/topics/ProbabilityandStatistics.html
  MathWorld Probability and Statistics
   

back to top