Final Formatting of Graphs in Excel-2007

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 Click on the chart and choose Chart Tools>Format>Format Selection for a host of options, the option you want must be already selected. Alternatively and more quickly, right-click on an element in the graph you want to change and select Format Selection, e.g., Formal Axis, Format Trendline, Format Chart Area, etc., from the dropdown menu to fomat the element). Mac versions without a right mouse button, use Command-Click (I think).

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

Note that there is overlap on some elements between the two methods. 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, right-clicking & selecting 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.

Some modifications are more involved than others. Modification of the graph axes is one of the more involved options.

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 Tools>Format menu, and by right-clicking and selecting 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 5a.

Figure 5b.

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

Figure 6.

Return to Top