Plotting Graphs

Excel shares the Microsoft Graph program with the other Office components . If you re new to Graph, this section is a good introduction to its features and flexibility.

X axis and y axis

Graph uses the terms x axis and y axis in some of its dialog boxes. For clarification , here are a couple of definitions: The x axis shows the information categories ”for example, expenses and net income; the y axis shows the data points (plotted values).

 

With Excel, you can create graphs in three ways: on the current worksheet, as a separate sheet in the current workbook, or in another workbook. (In this discussion we use the term graph to refer collectively to all types of graphs and charts .) In this topic, you ll plot a graph on the current worksheet. The advantage of this method is that you can then print the graph and the underlying worksheet on the same page.

The default graph format

Graph s default format is a two-dimensional column graph. To change the default format, click Chart Type on Graph s Chart menu, and select a different type on the Standard Types or Custom Types of the Chart Type dialog box. (Note that you must first select a graph before you can change the default type.) Click the Set as default chart button at the bottom of the dialog box. When Excel asks you to confirm your default graph selection, click Yes. Click OK in the Chart Type dialog box to have the new default graph type take effect immediately.

 

Creating a Graph

When you create a graph from a selected range of data in a worksheet, Excel maintains a link between the worksheet and the graph. This link is dynamic: If you make changes to the worksheet data, Graph revises the graph to reflect the new data. Let s create a graph based on the income and expenses data in the Annual sheet. Follow these steps:

1. In the Annual worksheet, select A5:E5. Then hold down the Ctrl key, and select A7:E7.

2. On the Standard toolbar, click the Chart Wizard button.

   The wizard has four pages that will lead you through the process of creating and customizing a graph. The first page is shown in this graphic:

   

3. With Column selected in the Chart type list, select Clustered column with a 3-D visual effect (the first option in the second row) in the Chart sub-type area, and click Next .

   The default graph format Graph displays the page shown in this graphic:

   

   You can see how the selected range will look as a 3-D column graph, with all labels and other information in place.

4. Click Next again to accept the default settings and move to the next dialog box.

   Graph s next page looks like the one shown in this graphic:

   

   At any point in the graph-creating process, you can click the Back button to move back to a previous page so that you can change its settings.

5. Click each of the tabs of this page to become familiar with the options to add titles, axes, gridlines, and so forth. Then click the Next button to accept the default settings.

   On the last page of the wizard, shown in this graphic, you specify where you want to display the graph:

   

6. Click Finish to accept the default settings.

   Graph plots the data as a column graph on the Annual worksheet and displays the Chart toolbar, as shown in this graphic:

   

   Don t be concerned if your graph doesn t look exactly the same as ours. Differences in screen setup or the order of selecting functions can change the way graphs are displayed.

7. If the Chart toolbar is floating, click its Close button to turn it off. Otherwise, right-click any toolbar, and click Chart in the shortcut menu to make it disappear.

   As you can see, four groups of columns , called data markers , represent the four quarters of data you selected. Within each group , one data marker represents the expenses category and the other represents the net income category. The data markers are identified in the legend by the labels from column A of the selected range: Expenses and Net Income. The graph is surrounded by small black squares, called handles , to indicate that it is selected. As long as the graph is selected, Graphs menus and toolbars are displayed so that the tools appropriate for working with graphs are available to you.

8. Point to any element of the graph, such as a legend.

   Graph displays the element s name in a box.

Formatting graph elements

You can format all the elements of a graph to achieve the look you want. To select a specific element, click it once or select it from the Chart Objects dropdown list at the left end of the Chart toolbar. Then right-click the element, click the Format command on the shortcut menu, and make your selections in the dialog box that appears.

 

Creating graphs on chart sheets

To create a graph on a separate chart sheet, build the graph in the Chart Wizard s dialog boxes as usual, and then select the As new sheet option on the Chart Wizard s fourth page. In the adjacent text box, you can type a name for the new sheet or leave the default name, Chart1. Then click Finish. Excel inserts a chart sheet in front of the active worksheet and plots the graph on the new sheet. To quickly build a graph in the default format on a separate chart sheet, select the worksheet data you want to plot, and press the F11 key.

 

Adding notes

To add explanatory notes to a graph, select the graph, click in the formula bar, type the note, and press Enter. Graph displays a text box containing the note in the middle of the graph. You can use the frame and handles around the text box to reposition it or size it. To format the text box and the note it contains, click Selected Object on the Format menu, and make your selections on the tabs of the Format Text Box dialog box.

 

Moving and Sizing a Graph in Excel

When Graph finishes plotting your data and places the graph on the worksheet, the program rarely places it where you want it. Fortunately, moving and sizing graphs is easy. You can always drag the graph to the desired location and then adjust its size by dragging the handles around the graph s frame. Try this:

1. Point to the middle handle on the left side of the graph s frame, and drag it to the left to increase the graph s width.

2. Drag a corner handle diagonally inward to decrease both the height and the width proportionally.

3. Move the entire graph below the data of the Annual sheet by pointing just inside its frame and dragging in the direction you want to move.

Updating a Graph

Excel actively links a graph to its underlying data, so if you change the data, Graph automatically redraws the graph to reflect the change. As a demonstration, let s see what Graph does when you restore the original amount in cell E20 of the Invoices 2002 worksheet:

1. Click the Invoices 2002 tab, and enter 1898.46 in cell E20 .

2. Click the Annual tab. The updated graph looks like this:

   

Adding values to an existing graph

If you add values to a set of data in your worksheet and want to update a graph you created earlier to reflect the new values, you can select the values, and drag the selection to the graph. When you release the mouse button, Excel displays the Paste Special dialog box. Click New point(s), select Rows or Columns, and then click OK to add the additional data points along the category axis. To add values to a graph located on a separate chart sheet, select the values, click the Copy button, move to the chart sheet, select the graph, and click the Paste button.

 

Information about  

Chart Wizard, page 148

Changing the Chart Type in Excel

No matter what type of graph you need ”bar, pie, line, or some other type ”Graph has a format that will probably do the job. You can always come up with impressive visual support for a worksheet by carefully selecting from among Graph s many predefined types. Let s try changing the type of the graph currently on your screen so that you can see the possibilities:

1. Be sure the graph is selected (surrounded by handles). If it isn t, click anywhere within the graph to select it.

2. On the Chart menu, click Chart Type .

   Excel displays a dialog box similar to the first Chart Wizard box.

3. In the Chart type list, select the Line option, and click OK .

   Graph redraws the graph, with expenses and net income represented as two separate lines, as shown in this graphic:

   

4. Click the Undo button to restore the previous type.

5. Display and dock the Chart toolbar, and then click the Chart Type button s down arrow.

   Excel displays this drop-down palette of graph types:

   

6. Select the 3-D Pie Chart option (the second option in the fifth row).

   Now the four quarters of expenses data are represented as colored wedges in a circular pie with the illusion of three dimensions, as shown in this graphic:

   

   You no longer see the net income data because a pie graph can display only one set of data.

7. Click the other buttons on the palette displayed when you click the Chart Type button s down arrow to see what options are available. Then finish up by clicking the Column Chart button to restore the two-dimensional column graph.

Using a Custom Graph Type

Clicking one of the buttons in the Chart Type palette (or selecting one of the options in the Chart Type dialog box) often produces exactly the graph you need, but occasionally you might want something slightly different. Before you spend time adjusting the format of a graph type, it is worth exploring Graph s custom types. Follow these steps:

1. With the graph selected, click Chart Type on the Chart menu. Then click the Custom Types tab in the Chart Type dialog box.

2. Scroll through the Chart type list, selecting any options that catch your eye and noting their effect in the Sample box on the right.

3. When you ve finished exploring, click Columns with Depth , and click OK .

   The result is shown in this graphic:

   

   You might want to continue exploring some of the other available custom types. In particular, try creating a combination graph, which plots one type of graph on top of another as an overlay.

Combination graphs

To create a combination graph, select one of the combination graph types (for example, Line-Column) on the Custom Types tab of the Chart Wizard or Chart Type dialog box. You can plot your data as one graph type overlaid by another ”for example, a column graph overlaid by a line graph. If your data includes series that span widely divergent ranges of values, you can plot the data against two y axes of different scales ”for example, one series against a y-axis scale on the left of the graph ranging from 0 to 100 and another series against a y-axis scale on the right of the graph ranging from 100,000 to 1,000,000.

 

Deleting graphs

If you decide you want to delete a graph from a worksheet, simply select the graph and press the Delete key.