Final Formatting of Graphs in Excel-2003

Table of Contents

  1. Formatting the final display of a scatter plot
  2. Drawing a reference line


Most graphs will need to have some additional modification done to its layout once the basic elements are in place. Excel uses defaults for a number of parameters and options which, in some cases, will need to be modified the optimize the display of the data. Remember, the primary goal is to communicate your results as clearly and concisely as you can.

There are two primary ways of modifying the display of your graph, through the menu choice Chart>Chart Options... and by clicking once to highlight an element in the graph and then choosing Format>Selected... (or by simply double-clicking on the element). (In Excel-2007, click on the chart element and choose Layout>Format Selection for a host of options. Alternatively, right-click on an element in the graph to change and select Format Selection to fomat the element). Mac versions without a right mouse button, use Command-Click (I think).

Here is a breakdown of what can be modified with each of these methods:

Note that there is overlap on some elements between the two methods. In general, the Chart Options control whether an item is displayed or not (e.g., show values on the Axes) while Format controls the final format of the element (e.g., font size and text orientation of the axis values). Also note that the listing of elements under Format does not hold for all graphs. What can be formatted depends on what elements the graph contains. In general, double-clicking (right-clicking) on an element will get you the Format dialogue for that element.

What should be modified and how? This will be highly dependent on your data set and the goals for your graph. You may want to refer to the resource on revising your visuals for more advice.

Some modifications are more involved than others. Modification of the graph axes is one of the more involved options. The Advanced module on data display goes into more detail about this option.

Below is an example of a few of modifications that can made to a graph.

Return to Top

Formatting the final display of a scatter plot

The readability and display of the scatter plot below can be further enhanced by modifying a number of the parameters and options for the chart. Many of these modifications can be accessed through the Chart menu, the Chart floating palette, and by double-clicking the element on the chart itself.

Here is the initial scatter plot with a regression line (Figure 1). Let's start by creating a better contrast between the data points and regression line and the background.

Figure 1.

In the Chart Area Format dialogue, set the border and background colors (see Figure 2)

Figure 2.

Now, delete the horizontal grid lines

Now, adjust the color and line weight of the regression line and the color of the data points

Finally, you can move the regression equation to a more central location on the chart

If necessary, resize the font size for text elements in the graph.

The results can be seen in Figure 3.

Figure 3.

Return to Top

Drawing a reference line

A titration is when an acid of known concentrtation is added to an unknown basic solution or vice versa and the change in pH is recorded. This change in pH vs. the amount of acid or base added can be graphed as a titration curve. The critical point on this titration curve (Figure 4) is where it passes through a pH of 7. The amount ofadded acid/base of a known concentration at a pH of 7 can be used to calculate the concentration of the unkown solution. The inclusion of a horizontal reference line will help indicate this point.

Figure 4.

There is not an existing element associated with the graph to use as a reference line, so one has to be drawn from scratch using the drawing tools:

Figure 5.

Figure 6.

Figure 7.

Figure 8.

Figure 9.

Your final graph will look like this (Figure 10):

Figure 10.

Return to Top