Part 6. Working with
Charts
You've already learned the fundamentals of creating a worksheet. Now you can concentrate on some of the other features that add to a data presentation. For example, you can create a chart based on data in a worksheet. Charts are very useful for interpreting data; however, different people look at data in different ways. To account for this, you can quickly change the appearance of charts in Excel by clicking directly on the chart. You can change titles, legend information, axis points, category names, and more.
The axes are the grid on which the data is plotted. On a 2D chart, the y-axis is the vertical axis on a chart (value axis), and the x-axis is the horizontal axis (category axis). A 3D chart has three axes (add a z-axis). You can control all the aspects of the axes—the appearance of the line, the tick marks, the number format used, and more.
Using the Chart Toolbar
Creating a Chart
Select the cells you want to
include in your chart.
Click the Chart Wizard button on the Standard toolbar.
The first page of the Chart
Wizard opens. Select the desired Chart Type
and Chart Sub-type; then click Next.
Depending on how you want your
chart information to appear, click Rows or Columns in the Series in
area.
INTRODUCTION
Interpreting numeric data by looking at numbers in a table can be difficult. Using data to create charts can help people visualize the data's significance. For example, you might not have noticed in a spreadsheet that the same month of every year has low sales figures, but it becomes obvious when you make a chart from the data in that spreadsheet. The chart's visual nature also helps others review your data without the need to review every single number. |
TIP
Using Back and Cancel At any time while using the Chart wizard, you can click the Back button to return to previous screens or the Cancel button to start over. In addition, you can click the Finish button any time and add information to your chart afterward. |
Type a name for the chart in
the Chart title field, a value for the x-axis
in the Category (X) axis field, and any other
values you want; then click Next.
Click the As New Sheet option to enter the chart as a new
sheet (and type a sheet name), or the As Object in
option to enter the chart in the sheet you select.
Click Finish.
Excel creates the chart,
displaying it along with a Chart toolbar.
TIP
Clicking and holding to view a sample The first step of the Chart wizard enables you to select how your data looks with a particular chart type and subtype. To do so, move your mouse pointer over the Press and Hold to View Sample button; then press and hold down the left mouse button. |
TIP
Moving a chart Regardless of your selection in step 6, you can always move your chart to another (perhaps new) worksheet. To do so, simply right-click a blank area in the chart and choose Location from the shortcut menu that appears. To place the chart in a new worksheet, click the As New Sheet option in the Chart Location dialog box, and type a name for the new sheet. To move it to a different worksheet, click the As Object in option button and select the worksheet from the drop-down list. Click OK, and Excel moves the chart. |
Changing the Chart Type
Right-click the plot area and
select Chart Type from the shortcut menu.
Select a new chart type and
chart subtype in the Chart Type dialog box.
Click OK.
The updated chart type appears
in your chart.
INTRODUCTION
Charting is one of those skills you learn by doing. At first, you might not even know the type of chart you want to create until you see it. You can always select a different chart type for a chart so that it better represents the data. |
TIP
Using the default chart type The default chart type is a column chart. To make another chart type the default, select it in the Chart Type dialog box and then click the Set as default chart button. |
Altering the Source Data
Range
Right-click the plot area of
your chart and select Source Data from the shortcut menu.
Click directly in your
worksheet and select the new data range. The Data
range area in the Source Data dialog box automatically updates.
Click OK.
The updated data range appears
in your chart.
INTRODUCTION
There might be times when you create a chart and then decide that you want that chart to include additional information. You can easily add data series to your chart by altering the source data you select in your original worksheet. |
TIP
Locating incorrect data If you notice that one of the data points in your chart is way off scale, this is a good sign that you might have entered data into your worksheet incorrectly. If this is the case, edit the worksheet data, and the chart will update automatically. (Also see the task "Changing the Chart Source Data" later in this part for help altering the original data. |
Altering Chart Options
Right-click the plot area and
select Chart Options from the shortcut menu
that appears.
The Chart Options dialog box
opens. Type any changes to the chart titles on the Titles
tab.
Click the Axes tab and select from the Primary axis options to see how altering these
settings affects your chart.
Click the Gridlines tab and
select from the various major gridlines and minor gridlines for each of the
Category, Series, and Value axes.
INTRODUCTION
Changing your chart options can be as much fun as creating the chart in the first place. You can add or edit chart titles, alter your axes, add or remove gridlines, move or delete your legend, add or remove data labels, and even show the data table containing your original data. |
TIP
Double-clicking the chart One of the fastest ways to edit charting options is to double-click directly on the element in the chart you want to alter. The appropriate dialog box opens, enabling you to make the changes you need. |
TIP
Formatting the axes gridlines To change the pattern and scale of the gridlines, double-click the gridline itself. Then use the Format Gridlines dialog box to make your selections. Click OK. |
Click the Legend tab, select
whether you want to show a legend, and review how altering the Placement options affects your chart.
Click the Data Labels tab and
see how the addition of label descriptions affects your chart.
Click the Data Table tab and select whether you want to show
the data table (with or without the legend keys). When you're finished, click OK.
Review how your chart has
changed.
TIP
Adding data tables If you want to show a data table along with the chart, click the Data Table tab in the Chart Options dialog box. Then click the Show data table check box and click OK. |
TIP
Printing charts You can print a chart, just like you print anything else in Excel. If you select the chart in a worksheet, you can choose the Selection option when printing to print only the chart you have selected. If you are in a chart sheet (no data, only the chart on a worksheet), you can print it like a regular worksheet. |
Formatting the Plot Area
Right-click the plot area and
select Format Plot Area from the shortcut
menu that appears.
Click a color in the Area
section of the Patterns tab of the Format Plot Area dialog box.
Click OK.
Review how your chart has
changed.
INTRODUCTION
The plot area consists of a border and the location of the data points in your chart. You can alter the style, color, and weight of the border. You can also alter the color of the plot area. |
TIP
Areas If you are unsure whether you are in the chart area or the plot area, click directly on the chart. A ScreenTip appears telling you what area you are in. Alternatively, you can refer to the Name Box. |
TIP
Changing the border Right-click the chart and select Format Chart Area from the shortcut menu; you can alter the Border options from the Patterns tab. Be careful not to overpower the chart with lines that are too thick—it can take the attention away from the data. |
Formatting the Chart Area
Right-click the chart area and
select Format Chart Area from the shortcut
menu that appears.
Click the Font tab of the Format Chart Area dialog box and
select the Font options you prefer.
Click OK.
Review how your chart has
changed.
INTRODUCTION
The chart area consists of a border, the background, and all the chart fonts. You can alter the style, color, and weight of the border. You can also alter the color of the background. You can also change all the fonts and font styles in the chart. |
TIP
Areas If you are unsure whether you are in the chart area or the plot area, click directly on the chart. A ScreenTip appears telling you what area you are in. Alternatively, you can refer to the Name Box. |
TIP
Using the Patterns tab You can alter the background color of your chart just like you did in the preceding task, when you changed your plot area color. Refer to that task for more information. |
Formatting the Axis Scale
Right-click the Value axis (left axis) and select Format Axis from the shortcut menu that appears.
Click the Scale tab of the
Format Axis dialog box and type changes to the axis scale increments—for
example, decrease the value in the Major unit
field.
Click OK.
Review how your chart has changed.
INTRODUCTION
Excel automatically establishes the axis increments according to the maximum amount on the chart. Usually this will suffice, but if you want to show more detail about actual numbers, it can be convenient to alter your value axis. |
TIP
Number and alignment To change the number format, click the Number tab and select the numeric format you want to use. To change the alignment, click the Alignment tab and select a rotation for the axes. |
Altering the Original Data
Select the worksheet tab or
range that contains the charted data.
Click a cell that you want to
alter or need to update.
Type in the new data and press
the Enter key.
Go back to the chart and see
how the edited data point has changed your chart.
INTRODUCTION
A chart is linked to the worksheet data, so when you make a change in the worksheet, the chart is updated. If you want to change a value in the worksheet, edit it as you do normally. The chart will be updated to reflect the change instantly. If you delete data in the worksheet, the matching data series will be deleted in the chart. |
TIP
Saving changes Make sure that you save your changes to a worksheet and chart often. You wouldn't want to lose any changes you made in case your network goes down or your computer freezes. |
Changing the Chart Source
Data
Right-click your current chart
and select Copy from the shortcut menu that
appears. This will copy the chart exactly.
Switch to the worksheet or
open the file containing the new source data, right-click, and select Paste from the shortcut menu that appears.
Right-click the pasted chart
and select Source Data.
INTRODUCTION
Suppose you just finished creating your chart in exactly the way you intended it, and your boss tells you that the data he gave you was incorrect, and hands you a file with the corrected data on it. Was all the time you spent on your chart for nothing? Of course not! Simply tell Excel what you want the new data source to be, and you are ready to go. |
TIP
Adding and excluding data Changing the chart source data is similar to adding or excluding data from your chart. See the task that follows for more information on altering the source data within your worksheet. |
Excel jumps to the original
data-source worksheet. If the source data workbook is not open, the Data range field in
the Source Data dialog box will appear blank.
Click and drag to select the
new data source cells (for example, from five products to three);
simultaneously, the Source Data dialog box shrinks to show the data.
When you release the mouse
button, the Data range list box in the Source
Data dialog box displays the new source data range; click OK.
The chart is updated to reflect
the new data source. If you need to change some chart options, refer to the
task "Altering Chart Options" earlier in this part.
TIP
Updating source data A chart's source data can be kept in another worksheet. If you make changes to this source data, Excel will ask you whether you want to update the chart data or keep the data the same as the last time you worked on the chart. If you work in the chart without the source data worksheet open, you might get a reference error message indicating that you need to open the source data worksheet. |
TIP
Move and resize charts To move the chart, click the chart, hold the mouse pointer on the chart, and drag it to a new location. To resize the chart, click the chart border and drag it to resize it. |
Adding Data to Charts
Add the new data that you want
to include in your chart to the original worksheet and click directly on your
chart to see what data is currently referenced in the chart.
Click and drag the blue chart
data line to include the newly added data.
Drop the chart data line in
the new location.
The chart automatically
updates to include the new data.
INTRODUCTION
Suppose you want to expand your chart to include additional data. If so, you will need to place the data on your original worksheet and indicate to Excel that you want it included in your chart. |
TIP
Excluding chart data You can click and drag the blue chart data line to exclude data in your chart. Simply drag the blue line so that the data you want to exclude is no longer contained within the chart. |
Adding a Legend
Right-click the plot area or
chart area and select Chart Options from the
shortcut menu to open the Chart Options dialog box.
Click the Legend tab and click the Show legend check box to mark it. Then, see how
altering the Placement options affects your
chart in the preview area.
Click OK.
The legend is inserted in the
chart.
INTRODUCTION
A legend helps to makes sense out of all the data points and colors in a chart. If you didn't have a legend added when you initially created your chart, you can easily add one later. |
TIP
Using the Legend button You can also click the Legend button on the Chart toolbar to insert a default legend (to the right of the chart). |
TIP
Formatting legends Right-click the legend and choose Format Legend from the shortcut menu. From the dialog box that appears, you can alter the patterns, fonts, and even the placement of the legend. |