Update Charts Automatically When You Enter New Data

Category: Charts & Graphics | [Item URL]

Q. I use an Excel workbook to track and chart daily sales. Since the number of data points changes every day, I have to update the chart manually so it includes the new data. How do I get the chart range to expand automatically?

When you select a chart series in Excel 97 or later, the ranges used by the series are outlined on the worksheet. You could simply drag a corner of the outline to extend the range.

This document describes another approach that uses formulas to define the ranges used in a chart. The steps listed below describe how to create dynamic ranges for the chart shown in figure below.

1. Enter the data and create the chart shown in the figure.
2. Select Insert, Name, Define to bring up the Define Name dialog box.
3. In the 'Names in workbook' field, enter Date. In the 'Refers to' field, enter this formula:
   =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)
4. Click Add to create the name. Notice that the OFFSET function refers to the first data point (cell A2) and uses the COUNTA function to get the number of data points in the column. Because column A has a heading in row 1, the formula subtracts 1 from the number.
5. Now type Sales in 'Names in workbook', and in 'Refers to' enter this formula:
   =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
6. Click Add, and then OK to close the dialog box.
7. Activate the chart and select the data series. In this example, the (unmodified) formula in the formula bar will read:
   =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10,1)
8. Replace the range references in the SERIES formula with the names you defined in steps 4 and 5. The SERIES formula should read:
   =SERIES(,Sheet1!Date,Sheet1!Sales,1)

After performing these steps, you'll find that the chart updates automatically when you add new data to the worksheet.

To use this technique for your own data, make sure that the first argument for the OFFSET function refers to the first data point, and that the argument for COUNTA refers to the entire column of data. Also, if the columns used for the data contain any other entries, COUNTA will return an incorrect value.

Creating A Non-Graphic Chart Directly In A Range

Category: Charts & Graphics | [Item URL]

This tip describes how to create a non-graphic chart. It uses formulas to display crude "bars" directly in a range of cells.

The figure below shows an example of what you can produce with his technique.

The formulas in columns E and G graphically depict monthly budget variances by displaying a series of characters in the Wingdings font. The number of characters displayed is determined by an IF function.

To re-create this chart in Excel, enter the data shown in columns A through D, and then enter the following formulas:

E2: =IF(D2<0,REPT("n",-ROUND(D2*100,0)),"") 

F2: =A2

G2: =IF(D2>0,REPT("n",-ROUND(D2*-100,0)),"")

Assign the Wingdings font to cells E2 and G2, and then copy the formulas down the columns to accommodate all the data. Right-align the text in column E, and adjust any other formatting as you like.

Depending on the numerical range of your data, you may need to change the scaling. Experiment by replacing the '100' value in the formulas. You can, of course, substitute any character you like for the "n" in the formulas to produce a different character in the chart.

Creating A Linked Picture Of A Range

Category: Charts & Graphics | [Item URL]

Many users overlook a useful Excel feature that can create a live "snapshot" of a range and placing it anywhere you like.

To use this feature:

1. Select a range of cells

2. Choose Edit, Copy

3. Hold down the Shift key, and then choose Edit, Paste Picture Link.

The result is an image of the selected range that will reflect any subsequent changes to the source.

This technique is great for printing noncontiguous ranges on a single page. After creating a series of linked pictures of ranges, set them to print on one page.

Note: The Paste Picture Link command is a hidden command, and it appears on the Edit menu only if you press the Shift key.

Creating A Thermometer Style Chart

Category: Charts & Graphics | [Item URL]

Most people are familiar with "thermometer"-style graphs, which show the percentage of a project completed. It's easy to make such a display in Excel. The key is to create a chart that uses a single cell (containing a percentage value) as a data series.

The example below tracks daily progress toward a goal: 1000 new customers in a 15-day period.

Cell B18 contains the goal value. Cell B19 contains a simple sum formula:

=SUM(B2:B16)

Cell B21 contains the following formula, which calculates the percentage of the goal attained

 =B19/B18

As new data is entered in column B, the formulas display the current results.

To create the chart:

1. Enter the formulas listed above, along with the worksheet's sample data.

2. Select cell B21, and click the Chart Wizard button. Notice the blank row preceding cell B21. If you fail to include this blank row, Excel will use the entire data block--not just the single cell--to construct the chart. Since B21 is isolated from the other data, the Chart Wizard uses only the single cell.

3. In step 1 of the Chart Wizard dialog, specify a Column chart and a Clustered Column subtype (the first choice).

4. Click Next twice, and then in step 2 make additional adjustments: Add a Chart Title (Title tab), dump the Category (x) axis (Axes tab), delete the legend (Legend tab), and specify Show value (Data Labels tab). Click Finish to view the chart.

5. Double-click the column to display the Format Data Series dialog box.

6. Click the Options tab, and set the Gap width to 0 (this setting instructs the column to occupy the entire width of the plot area).

7. To change the pattern used in the column, click the Patterns tab and make your selection. The example shown here uses a gradient fill effect.

8. Double-click the vertical axis to bring up the Format Axis dialog. In the Scale tab of the Format Axis dialog, set Minimum to 0 and Maximum to 1.

Displaying A value in an AutoShape

Category: Charts & Graphics | [Item URL]

You want to make a particular value really stand out? Use an AutoShape.

First, click the AutoShapes button on the Drawing toolbar, select the shape you want to use by clicking it, and click in the formula bar. Then enter a cell reference such as the following:

=$B$14 

Press Enter. In the example below, the contents of cell B14 will then be displayed inside the AutoShape. If the content of the referenced cell changes, the graphic will reflect the change. The advantage is that AutoShapes offer formatting options that are not available within the standard Format menus.

To make an AutoShape even snazzier, double-click the graphic to access the Format AutoShape dialog box. From there, you can apply formatting changes -- for instance, adjusting the vertical or horizontal positioning, changing the font, adding color, or making the text bold.

Handle Missing Data In A Line Chart

Category: Charts & Graphics | [Item URL]

When you create a line chart in Excel, missing data points (blank cells) won't be plotted, and the line will contain gaps. Excel provides two other ways of handling missing data:

• Treat blanks as zeros

• Interpolate the data by connecting the line between the nonmissing data points.

The figure below shows both options. In this example, interpolating the missing data seems the better choice in view of the data's time-based nature.

To set how Excel deals with missing data, select your chart and choose Tools, Options. In the Options dialog box, click the Chart tab and then select the appropriate option. Your choice will apply to all data series in the selected chart.

You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box.

Format Cells To Display In Thousands

Category: Charts & Graphics | [Item URL]

In some cases, you may want to display thousands without zeros. For example, you would like 52,000 to appear as 52. Here's how to do it:

First, select the cells to be formatted, then choose Format, Cells. Click the Number tab. Select Custom from the Category list, and in the Type box enter 0, (that's a zero followed by a comma).

When this number format is applied, the cells will retain the correct numerical values, but they will be displayed without the last three digits.

To display values in millions, insert an additional comma at the end of the format string (0,,).

Another possibility is to display one or more decimal places--for example, 52,100 as 52.1. To do this, include a decimal point in your format string (0.0,).

You can also use these number formats in charts. To do so, double-click the chart axis to display the Format Axis dialog box. Then click the Number tab and specify the desired format. In Excel 2000 and later, the Scale tab of the Format Axis dialog box lets you specify the unit scaling directly. Just choose Millions from the "Display units" drop-down box.

Unlink A Chart Series From Its Data Range

Category: Charts & Graphics | [Item URL]

Normally, an Excel chart stores data in a range. If you change the data in that range, the chart updates automatically. Sometimes you may want to unlink the chart from its data ranges and produce a static chart that remains unaffected by later changes in the data. For example, suppose you plot data generated by various what-if scenarios, and you want to save a chart that represents some baseline scenario for comparison with others.

One way to create a static chart is to copy and paste it as a picture. Activate your chart, hold down the Shift key, and choose Edit, Copy Picture (this option is available only when you hold down Shift as you select Edit). The Copy Picture dialog box will appear. Click OK to accept the defaults. Then click anywhere in your worksheet and choose Edit, Paste.

Another way to create a static chart is to convert the range references into arrays. Select a chart series and then click the formula bar to activate the SERIES() formula. Press F9 to convert the range references into arrays. Repeat this for each series in the chart. Now the chart cans till be formatted (it doesn't become a picture). The formula bar in the figure below shows the SERIES() formula after converting the range references to arrays.

Display Multiple Charts On A Single Chart Sheet

Category: Charts & Graphics | [Item URL]

An Excel chart can appear embedded in a worksheet or reside in a separate Chart sheet. Here's a trick for storing multiple charts on a single Chart sheet.

Create charts as usual, placing them in a worksheet. Select any blank cell in the worksheet and press F11; this creates an empty Chart sheet to hold the embedded charts. Reactivate your worksheet, click an embedded chart, and select Chart, Location to display the Chart Location dialog box. Choose "As object in" and specify the empty Chart sheet. Excel will transfer the embedded chart to your Chart sheet.

Select your remaining charts, and use Chart, Location to move them to the Chart sheet. Now you can arrange and size the charts any way you like. Putting multiple charts on a single Chart sheet lets you use the View, Sized with Window command (available when the Chart sheet is active) to scale the charts to the window size and dimensions.

Following is an example of a Chart sheet that contains three charts. When the window is resized, the charts adjust to fill the window automatically.

Layouts For Column Charts

Category: Charts & Graphics | [Item URL]

The layout of a chart can make a big difference in its legibility. Consider the two charts shown below. Both use the same data. The top chart contains two data series, one for each region. The bottom one uses six data series, one for each month. Which is better? There is no correct answer. If your goal is to make month-by-month comparisons, the top chart is a better choice. If you want to emphasize trends in each region, the bottom chart works better.

When you create a new chart, you start by selecting the data to be plotted. Unless you tell Excel otherwise, it makes some assumptions to determine how your data is plotted. If the number of rows in your selected range exceeds the number of columns, the program uses the columns for the data series (as in the top chart). If the number of columns in your range exceeds or equals the number of rows, then Excel uses the rows for the data series.

In Step 2 of Excel's Chart Wizard dialog box, you can specify how the data will be plotted. Choose either Rows or Columns for the "Series in" option (you'll be able to preview your choice). To change the way the data in an existing chart is plotted, select the chart by clicking it, then choose Chart, Source Data. In the Source Data dialog box, click the Data Range tab and then make your selection.

Page 2 of 3 pages
[Previous page]   [Next page]