Excel 2000 in a Nutshell By Jinjer Simon 1st Edition August 2000 1-56592-714-1, Order Number: 7141 606 Pages, $29.95

Chapter 10
Chart

The Chart menu provides options that allow you to modify the settings for an existing chart. This menu can only be used with an existing chart; you cannot create a new chart with it. To create a new chart you must select Insert Chart. In fact, you can only see this menu when you have a chart selected within a worksheet or on a chart sheet. When the Chart menu displays it temporarily replaces the Data menu.

If you have a chart selected, you can change the chart type, modify the source for the data used to create the chart, or just change the chart options such as whether to include axes titles. This menu also provides the ability to create custom chart types by selecting the Chart Chart Type option.

So what type of chart should I create? It doesn't take long to become totally overwhelmed by the vast number of chart types available within Excel; you can even create your own custom chart types. Once you select the type of chart you want to create, you have the option of selecting from several sub-types. So which one should you use? Amazingly enough, with all the types of charts available, most people only use Column, Line, Pie, Scatter, and Area charts. Make sure you try some of the less commonly used chart types; it is not always what you say, but how you say it.

Excel divides the chart types into two basic but broad categories: Standard Types and Custom Types. The category names actually have very little significance. The Standard Types tab contains 14 different chart types and each of these types has at least two different sub-types or variations of the type. If you select a chart from the Standard Types tab you also need to select a subtype. The chart types listed on the Custom Types tab do not have subtypes--these are really just additional variations of chart types from the Standard Types tab. The tab is named Custom Types because this is also where Excel stores any additional custom chart types that you create.

Column Charts

You are probably very familiar with column charts. They are frequently used to show a relationship between a group of data values, and they typically show data changes that occur over time. A column chart always displays vertical columns that are oriented along a horizontal axis, such as the total sales over the past five years, shown in Figure 10-1.

Figure 10-1. Column charts allow you to view data changes over time

The column type provides seven different subtypes that you can select from:

Clustered Column

The Clustered Column chart creates a standard column chart. This chart type creates a vertical column for each data value. If the chart is showing multiple data series, the values are grouped based upon the category value. For example, if a Clustered Column chart was selected for Figure 10-1, the sales by each salesperson would display as separate columns above the corresponding year.

Stacked Column

The Stacked Column chart, shown in Figure 10-1, shows the corresponding data series stacked. For example, the figure shows the sales figures for each year stacked, allowing you to compare the total sales between each year. If you only have one data series selected, this chart appears the same as the Clustered Column chart.

100% Stacked Column

The 100% Stacked Column chart shows the corresponding data series values stacked as a percentage of the total. Instead of comparing total amounts you are able to see what percentage of the total that value is. For example, if this subtype was selected to show the data in Figure 10-1, you would be able to see the percentage of sales that each salesperson made for each year. This chart type is similar to creating multiple Pie charts.

3-D Clustered Column

The 3-D Cluster Column chart takes the standard Clustered Column chart and adds a three-dimensional perspective look.

3-D Stacked Column

The 3-D Stacked Column chart takes the Stacked Column chart and adds a three-dimensional look.

3-D 100% Stacked Column

The 3-D 100% Stacked Column chart takes the 100% Stacked Column chart and adds a three-dimensional look.

3-D Column

The 3-D Column chart is probably the showiest of the seven column subtypes. This type of chart provides a true three-dimensional chart by creating a third axis. The third axis creates the depth that gives the chart the 3-D perspective. For example, if this chart type was selected for Figure 10-1 the foreign sales figures would display behind the domestic figures.

Bar Charts

A Bar chart is very similar to a Column chart. In fact the main difference is that a Bar chart has bars that run horizontally from the left edge. It resembles a Column chart that has been rotated 90 degrees. If you have a large number of data values that you need to show, a Bar chart is typically a better choice than a Column chart if you plan to print the chart. Figure 10-2 shows how a Bar chart can be used to see the percentage of sales that occurred in each region.

Figure 10-2. With a 100% Stacked Bar chart you can compare the percentages within different categories

The bar type chart provides six different subtypes that you can select from:

Clustered Bar

The Clustered Bar chart creates a standard Bar chart. This chart type creates a horizontal column for each data value. If the chart shows multiple data series, the values are grouped based upon the category value.

Stacked Bar

The Stacked Bar chart displays the corresponding data series stacked. If you only have one data series selected, the chart appears the same as the Clustered Bar chart.

100% Stacked Bar

The 100% Stacked Bar chart, shown in Figure 10-2, shows the corresponding data series values stacked as a percentage of the total. Instead of comparing total amounts, you are able to see what percentage that value was of the total. This chart type is similar to creating multiple Pie charts.

3-D Clustered Bar

The 3-D Cluster Bar chart takes the standard Clustered Bar chart and adds a three-dimensional look.

3-D Stacked Bar

The 3-D Stacked Bar chart takes the Stacked Bar chart and adds a three-dimensional look.

3-D 100% Stacked Bar

The 3-D 100% Stacked Bar chart takes the 100% Stacked Bar chart and adds a three-dimensional look.

Line Charts

A Line chart provides the ability to plot continuous data values. With this type of chart you can show trends that have occurred in your data. For example, if you wanted to show the value of your stock of the last year you could use a Line chart to plot the values, as shown in Figure 10-3.

Figure 10-3. With a Line chart you can quickly illustrated any trends (ups or downs) that have occurred in your data

The Line type chart provides seven different subtypes that you can select from:

Line

The Line chart creates a chart with a solid line that connects the various data points on the chart.

Stacked Line

The Stacked Line chart displays the corresponding data series stacked. If you only have one data series selected, this chart appears the same as the Line chart. If you have multiple data series it charts the first data series and then charts the second data series above it. For example, in Figure 10-3 instead of charting the value of each stock, you can show the total value of the stocks where the top line shows the value of adding the first stock to the second one.

100% Stacked Line

The 100% Stacked Line chart shows the corresponding data series values stacked as a percentage of the total. Instead of comparing total amounts you are able to see what percentage that value was of the total. This chart type is similar to creating multiple Pie charts.

Line with Data Markers

The Line with Data Markers chart takes the standard Line chart and adds data markers to identify the individual data values.

Stacked Line with Data Markers

The Stacked Line with Data Markers chart creates a Stacked Line chart with data markers that represent each data value.

100% Stacked Line with Data Markers

The 100% Stacked Line with Data Markers chart creates a 100% Stacked Line chart with data markers that represent each data value.

3-D Line

The 3-D Line chart creates a chart with a three-dimensional representation of each data series.

Pie Charts

A Pie chart allows you to easily show each value proportional to the whole. For example, Figure 10-4 uses a Pie chart to show how monthly expenses were allocated. Keep in mind that a Pie chart works best if you do not have more than six or seven different values. It also only works with one data series.

NOTE: You can make a piece of the Pie chart appear to be removed or exploded by clicking on it and dragging it away from the center of the chart.

Figure 10-4. Pie charts allow you to show how individual values contribute to a whole

The Pie type chart provides six different subtypes that you can select from:

Pie

This option creates a standard Pie chart.

3-D Pie

This option creates a Pie chart with a three-dimensional perspective.

Piece of Pie

This chart type, shown in Figure 10-4, allows you to emphasize certain elements of the chart by moving them into a smaller Pie chart that correlates to a specific section of the larger chart. For example, if you have several smaller data values you can drag them onto the smaller pie so that they are easier to see.

Exploded Pie

This chart type explodes or removes each piece from the pie. You can adjust the location of the pieces by clicking on each pie and dragging to the desired location.

Exploded 3-D Pie

This chart type creates an exploded Pie chart with a 3-D perspective.

Bar of Pie

This chart type is very similar to the Piece of Pie type. The only difference is that the values removed from the pie are displayed on a bar (similar to a vertical column on a Bar chart) instead of on a pie. Adjust the values that are displayed on the bar by dragging pieces between the Pie chart and the Bar chart.

XY (Scatter) Charts

The XY or Scatter chart is commonly used to plot scientific data, but it can be used for any chart where you want to see the relationship between two different numerical variables and compare trends across uneven time periods. For example, Figure 10-5 shows the number of support calls that were received compared to the number of sales during each month over a year.

Figure 10-5. Scatter charts allow you to see the correlation between two sets of data

The XY (Scatter) type chart provides five different subtypes that you can select from:

Scatter

The Scatter chart type creates a chart using data markers to show each value. If you are comparing two or more data series, each data series is represented with a different color and/or shape.

Scatter with Smoothed Lines

This chart type takes a standard Scatter chart and connects the data markers in each series with smoothed lines. The lines are smoothed by removing any jagged points that may occur when you connect two data markers.

Scatter with Smoothed Lines and No Data Markers

This chart type takes the Scatter with Smoothed Lines chart and removes the data markers. This chart type closely resembles a Line chart.

Scatter with Lines

The only difference between this chart type and the Scatter with Smoothed Lines is that the lines that connect the data points are not smoothed. Therefore, the lines tend to look a little more jagged as they connect the data markers.

Scatter with Lines and No Data Markers

This chart type uses lines to connect the data points but there are no data markers to indicate where the data values are located.

Area Charts

An Area chart is similar to a Line chart. In fact, they can be easily interchanged and show the same results. The only difference is that an area chart is colored in to emphasize each data series. In Figure 10-6 you can see the total sales and compare the retail sales versus Internet sales.

Figure 10-6. The 3-D Staked Area Chart allows you to quickly compare values from different data series

The Area type chart provides six different subtypes that you can select from:

Area

The Area chart creates a chart with each data series colored in. Excel places the data series on the chart in the order listed on the worksheet. For example, if column A is the first column selected, it is placed on what appears to be the back of the chart so that if there is a larger data value in Column B it will appear to cover up the Column A value on the chart.

Stacked Area

The Stacked Area chart displays the corresponding data series stacked, similar to Figure 10-6. If you select this subtype, you will not get the 3-D perspective shown in the figure.

100% Stacked Area

The 100% Stacked Area chart shows the corresponding data series values stacked as a percentage of the total. Instead of comparing total amounts you are able to see what percentage that value was of the total. This chart type is similar to creating multiple Pie Charts.

3-D Area

The 3-D Area chart takes the standard Area chart and gives it a three-dimensional perspective.

3-D Stacked Area

The 3-D Stacked Area chart creates a Stacked Line chart with a three-dimensional perspective, as shown in Figure 10-6.

3-D 100% Stacked Area

The 3-D 100% Stacked Area chart creates a 100% Stacked Line chart with a three-dimensional perspective.

Doughnut Charts

At first glance a Doughnut chart appears to just be a Pie chart with a hole in the middle. This is basically true if you are plotting one data series. But unlike the Pie chart, you can use a Doughnut chart to show more than one data series.

When you create a Doughnut chart with multiple data series, as shown in Figure 10-7, the first data series is placed in the center of the doughnut. Each successive series forms a ring on the doughnut. Keep in mind that although a data item may appear larger on the outside of the ring than one on a ring toward the center of the chart, the size is proportional to the values within the series.

Although Doughnut charts provide a catchy way to illustrate the data values, they can be rather difficult to read, especially if you have multiple data series. You will probably find that a Stacked Column chart provides a more readable chart. In fact, most avid Excel users try to avoid using the doughnut chart because it can be more confusing.

Figure 10-7. Doughnut charts provide an interesting way to compare data

There are really only two types of Doughnut charts: a standard chart and an exploded chart. Keep in mind that with either chart type you can move elements by clicking the desired element of the chart and dragging it.

Radar Charts

You don't have to look at a Radar chart long to figure where it got its name. Anyone who has watched the evening news is familiar with the weather radars that are used by meteorologists. But this type of graph can be used for any type of data.

When you create a Radar chart you have a separate axis for each category of data, as shown in Figure 10-8. It basically has the appearance of spokes on a bike tire. In fact, if all data values in a series have the same values it will look like a perfect circle on your spokes (axes).

Figure 10-8. Radar charts illustrate the values of each data item within each category

The Radar type chart provides three different subtypes that you can select from:

Radar

The standard Radar chart type creates a radar chart with lines showing the value of each data item within the series. Different colors are used for each series.

Radar with Data Markers

The Radar with Data Markers chart type creates a radar chart with markers for each data value and lines used to connect the data values within each series.

Filled Radar

The Filled Radar series, shown in Figure 10-8, takes the standard radar chart and fills in the area created by the lines.

Surface Charts

A Surface chart provides an interesting way to show combinations between data values. Surface charts look like topographical maps, as shown in Figure 10-9, because they use colors and patterns to indicate where data values are the same.

Figure 10-9. Surface Charts provides a topographical type of view of the data values

There are four different types of surface charts:

3-D Surface

The 3-D Surface chart creates a topographical look at the data values, as shown in Figure 10-9.

3-D Surface (wireframe)

The 3-D Surface (wireframe) chart creates a surface chart without filling it in with color.

Surface (top view)

The Surface (top view) chart provides a view of the surface chart from above.

Surface (top view wireframe)

The Surface (top view wireframe) chart provides a view of the wireframe surface chart from above.

Bubble Charts

A bubble chart is basically just an XY (scatter) chart with an additional data series. For example, Figure 10-10 illustrates the revenue earned for each training seminar. The x axis indicates the cost paid by each attendee for the seminar and the y axis indicates the number of people that attended the seminar. The size of the bubbles represents the amount of revenue earned from each seminar. This type of chart allows you to determine when you produce the desired results.

Figure 10-10. Bubble charts provide an interesting method for showing the correlation between three sets of data values

You can either create a standard bubble chart, as shown in Figure 10-10, or one with 3-D bubbles.

Stock Charts

Stock charts are typically used to illustrate results of stock market information. Each of these charts requires at least three data series, and chart data must be arranged in the appropriate order. Figure 10-11 illustrates a High-Low-Close chart that requires the high, low, and closing value for each day.

Figure 10-11. Stock charts are designed to show the results of stock market information

There are four different types of stock charts available. You need to have the corresponding stock values for the chart that you select. These chart types are more temperamental than other Excel charts. You need to have the appropriate data values selected on your sheet before selecting this chart type.

High-Low-Close

Select this chart subtype to create a stock chart, like Figure 10-11, that shows the highest value, lowest value, and closing values. You need to make sure these values are arranged in the order highest value, lowest value, and closing value on your worksheet, or Excel will not create the chart.

Open-High-Low-Close

Select this chart subtype if you have the opening value, highest value, lowest, value, and closing values (arranged in the same order).

Volume-High-Low-Close

Use this chart type to compare the stock's trading volume to the highest, lowest and closing prices. The data in your worksheet must be arranged in the order of volume: high, low, and closing.

Volume-Open-High-Low-Close

Use this chart if you have your data in the order of trading volume, opening value, highest value, lowest value, and closing value.

Cylinder, Cone, and Pyramid Charts

These three chart types are essentially the same. The only difference is the type of graphic image used on the chart. These charts types are interchangeable with column and Bar charts to show a relationship between a group of data values; typically they show data changes that occur over time, as shown in Figure 10-12.

Figure 10-12. Cylinder, Cone, and Pyramid charts provide an interesting alternative to standard column and Bar charts

There are seven different subtypes that you can select from:

Clustered Column

The Clustered Column chart creates a standard column chart using the selected shape (cylinder, cone, or pyramid) in place of the column. This chart type creates a vertical column for each data value. If the chart is showing multiple data series, the values are grouped based upon the category value.

Stacked Column

The Stacked Column chart shows the corresponding data series stacked. If you only have one data series selected, this chart appears the same as the Clustered Column chart.

100% Stacked Column

The 100% Stacked Column chart shows the corresponding data series values stacked as a percentage of the total. Instead of comparing total amounts you are able to see what percentage that value was of the total. This chart type is similar to creating multiple Pie charts.

Clustered Bar

The Clustered Bar chart creates a standard Bar chart using the selected shape for the bar (cylinder, cone, or pyramid). This chart type creates a horizontal column for each data value. If the chart is showing multiple data series, the values are grouped based upon the category value.

Stacked Bar

The Stacked Bar chart displays the corresponding data series stacked. If you only have one data series selected, this chart appears the same as the Clustered Bar chart.

100% Stacked Bar

The 100% Stacked Bar chart shows the corresponding data series values stacked as a percentage of the total. Instead of comparing total amounts you are able to see what percentage that value was of the total. This chart type is similar to creating multiple Pie charts.

3-D Column

The 3-D Column chart is probably the showiest of the subtypes. This type of chart provides a true 3-D chart by creating a third axis. The third axis creates the depth that gives the chart the three-dimensional perspective.

Chart Chart Type

The Chart Chart Type option allows you to change the chart type for the currently selected chart. The option also provides the ability to make the currently select chart type the default chart and to create a custom chart type. When you select this option, the Chart Type dialog displays, as shown in Figure 10-13. The currently selected chart type is indicated on the Chart Type dialog. For example, if you currently have one of the standard chart types selected, that chart and its sub-type will be selected on the Standard Types tab.

Figure 10-13. Chart Type dialog

Standard Types Tab

The Standard Types tab, shown in Figure 10-13, displays a list of several different chart types that can be selected in the Chart Type list box. When you select one of these types, a selection of sub-types display in the Chart sub-type section. If you want to preview how your data will look on your new chart selection, select the "Press and Hold to View Sample" button.

If you have made modifications to the formatting of the chart, you can have Excel resort back to the default formatting for the selected chart type by selecting the Default formatting option.

Custom Types Tab

The Custom Types tab, shown in Figure 10-14, displays a list of custom chart types that can be selected. These chart types are either ones that were included with Excel or ones that you have customized and saved.

NOTE: The only real difference between the built-in chart types that appear on the Custom Types tab and those on the Standard Types tab is that the ones on the Custom Types tab do not have the sub-type selections. Other than that, they are just additional chart types that can be selected.

Figure 10-14. Selecting a Custom Chart

You can either select Built-in (Microsoft-created) charts or user-defined charts by selecting the appropriate radio button in the Select from box. If you select the User-defined radio button you have the option of saving a custom chart for future selection. A custom chart is created by modifying one of the existing chart types to meet your desired criteria. When you select the User-defined radio button, the Add and Delete buttons display on the dialog box, allowing you to create and delete custom charts. When you select the Add button, the "Add Custom Chart Type" dialog displays, as shown in Figure 10-15.

Figure 10-15. Use the Add Custom Chart Type to specify the name of the new chart

You need to specify a name for the new chart type. The description that you specify will display when you click on the chart name on the Chart Type dialog. Keep in mind that to find this chart type in the future you will need to select the Custom Types tab on the Chart Type dialog and then select the User-defined radio button.

You can change the default chart that is used by Excel by locating the desired chart type on the Chart Type dialog and then selecting the "Set as Default Chart" button. You can quickly create a chart using the default chart type by selecting the desired data in the chart and then pressing F11. The default chart type is selected using Chart Chart Type.

If you have made custom modifications to your current chart, such as changing colors of data series, those changes are typically lost if you select a different chart type. Therefore, make sure you have the desired chart type selected before you start customizing it. If you have not created any user-defined charts, Excel displays Default as the chart type option. This is the default chart type that you have selected with the "Set as Default Chart" button. If you have not changed the default chart type, Excel uses the standard Clustered Column chart as the default chart type.

You can create a chart that combines more than one chart type. For example, you may want to create a Bar chart that includes a Line chart for one series of data. To do so, you first chart and then select the data series that you want to change and select Chart Chart Type then select the chart type you want for the selected range.

Chart Source Data

The Chart Source Data option allows you to make modifications to the data ranges and data orientations for the selected chart. When you select this option, the Source Data dialog displays, as shown in Figure 10-16.

Figure 10-16. Source Data dialog

Data Range Tab

The Data Range tab is used to specify the data range that is used to create the chart and whether the data is placed on the chart based upon the values in the rows or columns of the worksheet. The orientation (Series in radio buttons) that you select determines the values selected on the Series tab. For example, if you select the Rows radio button, by default Excel uses the first cell in each row as the series name for the chart. This is the information that appears in the legend.

Series Tab

The Series tab, shown in Figure 10-17, allows you to customize the series names for your chart. To change a series name, highlight the name in the series list and type the desired name in the Name field. You can also click on the Collapse Dialog button and select another field that contains the desired series name. Keep in mind, any modifications made to the series names on this tab do not affect the values in the worksheet.

The "Category (X) axis labels" field indicates the location of the X axis labels for your chart. You can change the reference by clicking on the Collapse Dialog button.

Figure 10-17. Series Tab on Data Range dialog

NOTE: Excel allows you to have a maximum of 255 data series in most charts; with the exception of the Pie chart that can only display one data series. Excel allows you to have a maximum of 32,000 data points for standard charts, 4,000 data points for 3-D charts.

Chart Chart Options

The Chart Chart Options option provides the ability to customize the look of the selected chart. You can indicate items such as the name of the chart, whether gridlines should display, location of the legend, etc. The actual customization options that are available vary depending upon the type of chart selected. When you select this option, the Chart Options dialog displays, as shown in Figure 10-18. The dialog provides several different tabs that can be selected to customize the chart. There are actually six different tabs available on this dialog, but they do not display for all chart types. For example, if you have a Pie chart selected, you will only see the Titles, Legend, and Data Labels tabs.

To customize the chart you need to select each tab and designate the appropriate values for each field. As you make modifications to the chart options, you will be able to see the results in the chart view section that appears on the right side of the dialog.

Figure 10-18. Chart Options dialog

Titles Tab

The Titles tab, shown in Figure 10-18, provides the ability to add custom labels to different parts of your chart. The label types available vary based on the chart type selected.

When you create a title Excel places it in a default location on your chart. Remember, you can drag the titles to any location on the chart.

Axes Tab

The Axes tab allows you to specify whether you want to see the values for a specific axis. The number of axes is different depending upon the chart type selected. For example, Pie and Doughnut charts have no axes; whereas, a 3-D chart has 3 axes. You select each axis by selecting the corresponding checkbox.

The Category axis represents that chart's categories. For example, in Figure 10-18, we are viewing the Travel Expenses for each month of the year, so each month is a category on the chart.

You can customize the look of a specific axis on your chart by clicking on the axis with the right mouse button and selecting the Format Axis option. On the Format Axis dialog you have the ability to change the pattern used to indicate each axis. For example, you may want to make the axis and tick marks red so that they show up better. Be careful when modifying the Scale tab, shown in Figure 10-19. Excel automatically scales your chart based upon the data values in the chart. Manually adjusting the scale of an axis can have a dramatic effect on the look of your chart. For example, if you are showing the values 10, 5, 6, and 9 on your chart and you set the range of the axis to be 0 to 100, the values will almost look like the same number on your chart. Keep in mind that the values on the Scale tab vary depending upon the chart type selected.

Figure 10-19. Be careful about modifying the scale of your chart

Gridlines Tab

On the Gridlines tab you can select the corresponding checkboxes to specify whether you want to have gridlines displayed for each axis. Gridlines help to show what numeric values are represented on the chart. A gridline simply extends the tick marks on the axes across the chart. Gridlines should only be used to improve the readability of your chart.

There are two different types of gridlines: major and minor. The major gridlines display at each label on the axis. For example, if your chart has the values of 10, 20, 30, 40 on the axis, a major gridline would be created for each of those labels. The minor gridlines display between the labels. Typically the major gridlines are adequate in showing your data; but use of the minor gridlines can help show a more exact representation of the data.

Legend Tab

Excel gives you the option of providing a legend for your chart. A legend is made up of text and keys. Each key is basically just a small graphic (typically a colored square) that corresponds to data series on the chart. Each key has a text value next to it that provides a description of the data series. The Legend tab provides the ability to select and/or remove the legend from the chart by selecting the Show Legend checkbox.

By default Excel always places the legend on the right side of the chart. You can change the location by selecting one of the radio buttons. You can also manually move the legend by clicking on it and dragging it with the mouse.

The actual text for the legend comes from the cells that you selected on the Series tab of the Source Data dialog. If you want to change the values, you need to select Chart Source Data.

Data Labels Tab

If you do not have a lot of different data values in your chart, you may want to use labels to identify each value. The Data Labels tab provides the ability to apply labels to the actual chart values. The actual options that are available is based upon the type of chart you have selected.

The cool thing about using the data labels versus manually placing labels is that the labels are linked to the data in the worksheet. Therefore, if your data values change, the labels on the chart will be updated to match. You can, however, manually change a label by selecting it on the chart and then typing the desired text or cell reference for the label in the formula bar.

Data Table Tab

If you have placed your chart on a chart sheet you are aware that the data used to create the chart is not available on that sheet. You can place the data on the sheet within a data table using the options on the Data Table tab.

If you want to add a data table, select the Show data table checkbox. You can have the legend keys displayed next to each element of the table by selecting the Show legend keys checkbox.

If you decide to add the legend, my recommendation is to use the Show legend keys checkbox and then remove your legend. As you can see in Figure 10-20, it is rather redundant to have both the legend and the data table.

Figure 10-20. If you use a data table, add the legend keys and remove the legend from the chart

Chart Location

When you create a new chart you indicate whether you want to place the chart directly in a worksheet or in a separate chart sheet. Once a chart has been created, you can move it to another location by selecting Chart Chart Location to display the Chart Location dialog shown in Figure 10-21.

Figure 10-21. Chart Location dialog

When you place a chart on a new sheet it is placed on a chart sheet. This is the best option to select if you only want to print the chart, without the actual data of the worksheet. The chart sheet displays the chart as it will be printed on a sheet of paper (WYSIWYG). If you want to place the chart directly on a worksheet, you can select the "As object in" radio button and then indicate the worksheet where you want to insert the chart in by selecting the down arrow button next to the field.

Chart Add Data

It is not uncommon to decide you want to add data to a chart once it has been created. The easiest method for doing this is to select the Chart Add Data option. When you select this option the Add Data dialog displays, as shown in Figure 10-22.

Figure 10-22. Select the range of cells that you want to add to your chart

As you can see from the figure, this is really a simple dialog. To add data to your chart you can either type the data range in the Range field, or you can select the Collapse dialog button and highlight the desired range of data.

When you select the OK button, Excel displays the Paste Special dialog, shown in Figure 10-23, so you can indicate how you want to add the new data to the chart.

Figure 10-23. You need to specify how the new data should be added to the chart

Although the Paste Special dialog is rather small, it can also be a little confusing to use. You need to use the options on this dialog to tell Excel how the data should be added to the chart. For example, do you want to add a new data series or should the data values be added to the existing data series.

Add cells as

In the "Add cells as" section, select the appropriate radio button to indicate how you want the new data added to the chart. If you select "New series," Excel will create a new data series on the chart that consists of the selected values. For example, if you have a Line Chart, a new line will be created for these values. If you select the "New point(s)" radio button, the selected data values will be added to the existing data series.

Values (Y) in

Select this option to indicate whether the data series should be created based on each row or column within the selected range. For example, if you have a list of sales totals for each salesperson, you need to indicate if the totals are specified by rows or columns.

Series Names in First Column (Row)

The name of this option changes based upon the radio button selected in the Values (Y) in section. If the first row or column contains the series names, select this checkbox.

Categories (X Labels) in First Row (Column)

The name of this option changes based upon the radio button selected in the Values (Y) in section. If the first row or column contains the category names, select this checkbox.

Replace Existing Categories

Select this option to replace the existing category labels with the ones in the range you are pasting.

Chart 3-D View

You have probably already found that the most appealing charts available within Excel are the 3-D ones. Although Excel has several charts that are labeled 3-D, not all of them are true 3-D charts. A 3-D chart has three different axes; some of the charts that are labeled as 3-D are really just two-dimensional charts that have a perspective added to give them 3-D appeal.

If you have a true 3-D chart selected you can modify the way it displays by rotating it using the 3-D View dialog that displays when you select Chart 3-D View. If this option is grayed out the chart is not 3-D. The 3-D View dialog, shown in Figure 10-24, provides options for changing the elevation and rotation of your chart. You will see the effect of your selection in the center of the dialog.

Figure 10-24. Use the 3-D View Dialog to rotate a 3-D chart so that it emphasizes the desired data

Changing the Elevation

When you change the elevation of your chart you are basically just rotating it toward the top or bottom. For example, if you want to create the perspective of looking down into your chart you could use the Up Arrow button to rotate the chart so you could see the top.

There are two different methods for changing the elevation. You can use the Up and Down Arrow button to move it or you can manually type the height you want to view the chart from in the Elevation field. For most chart types you can type a value between -90 and 90 degrees. As you click on each button the elevation value is modified in the Elevation field.

Changing the Rotation

You can modify the rotation of your chart by rotating it right or left. To rotate it you can use the Left and Right buttons or you can manually type a value in the Rotation field. A chart can be rotated between 0 and 360 degrees.

Setting the Chart at Right Angles or Using Perspective

If you want to have the axes remain at right angles as you change the rotation and elevation, you need to the select the Right angle axes checkbox. If you remove the checkmark from the box, the Perspective options display on the dialog. By changing the perspective you make the chart look like it has more depth, but it can also make the chart look a little more skewed.

Quickly Rotating a 3-D Chart

TIP: A quick method of rotating a chart is to hold down the Ctrl key and then click on a corner with the mouse and drag it on the screen. As you drag the corner the chart will rotate on the screen.

If you don't like the way your chart appears after changing the rotation, perspective, or elevation you can select the Default option on the 3-D View dialog to change the chart back to the original settings.

Chart Add Trendline

Trendlines provide the ability to show trends that exist within your data. They can also be used to forecast future data, although you should not base too much on the trendline created. You can add a trendline to your chart by selecting Chart Add Trendline to display the Add Trendline dialog shown in Figure 10-25. Trendlines can be added for each data series. In fact, a data series can have multiple trend lines.

Figure 10-25. Add a Trendline to show trends in the data and to forecast future data

The Add Trendline dialog has two different tab options. The Type tab is where you select the type of trendline that you want to add to your chart. There are six different trendlines that you can select, as shown in Figure 10-25. Each of the trendlines uses a specific formula to calculate the values of the trendline based upon the selected data series. The most commonly used type of trendline is a linear trendline. It is of course only valid if there really is a linear relationship between the two variables in your chart. This option creates a straight trendline that shows the trend of the selected data series.

The Options tab allows you to indicate the text that will represent the trendline within the legend. Probably the most useful feature is the ability to forecast previous or future data values for the selected series. For example, if you want to project the sales of wrenches for the next six months you can indicate that you want to forecast forward six periods.

NOTE: On some charts trendlines may appear to be just another data series, but Excel knows the difference. By default, the trendline is given a name such as "Jones Trendline 1." You can double-click on a trendline to display the Format Gridlines dialog where you can modify the name, and formatting options for the selected trendline.

Back to: Excel 2000 in a Nutshell