Advanced Graphing Using Excel V.1

1
Advanced Graphing Using Excel V.1
Part II Giving your graph style
University of Michigan Dearborn Science
Learning Center
Written and Created by James Golen
2
Introduction

• Before using this module you must have completed
  the primary module entitled Graphing Using Excel.
  
• If you still need help with basic graphing,
  please close this program and refer to the
  Introduction to Graphing module available from
  the Science Learning Center. The concepts within
  this module can be quite challenging.
• Remember, this module is not a tutorial of
  Excels capabilities! Completion of this module
  will allow you to make useful graphs, but in no
  way will you have mastered all aspects of Excel.

3
Data Entry

• Recall from the primary module that data entry is
  very important. Your data must be clear, to both
  you and your readers. The quality of your data
  will turn out to be the same quality of your
  graph. As you create your graph, please save
  your data often.

4
Selecting Your Data

• Highlight your data and click on the chart
  wizard. Recall from the first module that if the
  chart wizard icon is not present. You will have
  to insert/chart from the main toolbar.

5
Using the Chart Wizard

• Once again we will choose XY scatter for the
  chart type. In some cases columns will used for
  histograms, but for the most part in science we
  will be comparing X vs. Y.

6
Using the Chart Wizard

• Additional chart types are available to you if
  you click on the Custom Types tab. These
  options allows for a more eye catching graph, but
  is almost useless when rendering data. I would
  recommend never using the custom types, unless
  your audience expects an entertaining show rather
  than raw data.

7
Using the Chart Wizard

• Step 2 of the chart wizard displays the data
  range your graph will cover. Notice that the
  letters and numbers in the Data range text box
  correspond to the corner cells of your data entry
  that you highlighted before using the chart
  wizard. In our case, B5C16 is equal to
  cells B5 to C16.

8
Using the Chart Wizard

• The Series tab allows you to name your data
  points, and add or remove whole sets of data from
  your plot. In this case we have named the Series
  1, Data point. Unless you have more than one
  series of data, you most likely will not need to
  provide names.

9
Using the Chart Wizard

• Titles and labels are the heart of a graph, make
  sure you supply adequate entries for each. Dont
  worry if at this point it looks somewhat
  cluttered, you will be able to clean up your
  graph when you finish.

10
Using the Chart Wizard

• The Axes tab allows you conceal the values on
  you (X) or (Y) axis. While keeping your data
  intact. It is recommended that you keep your
  values on your graph at all times.

11
Using the Chart Wizard

• The Gridlines tab allows you to set major,
  minor or both gridlines to your graph. Gridlines
  make it easier to locate individual or important
  data points. In most cases no gridlines are
  preferred. Here we have de-selected all
  checkboxes.

12
Using the Chart Wizard

• The Legend tab allows you to make a decision on
  whether a legend is needed. In this case, and in
  most, the series line is obvious and no legend is
  preferable.

13
Using the Chart Wizard

• Data Labels provides a label for each data
  point which will just show each points position
  as it relates to the (X) axis. Rarely will you
  find it necessary to use this tab unless you have
  removed the values for the (X) Axis.

14
Using the Chart Wizard

• The final Chart Wizard window will allow you to
  position your chart within your spreadsheet.
  Selecting the As new sheet option will place
  your graph on an entirely new spreadsheet. The
  As object in option will allow you to place the
  graph on the sheet where your data is stored.

15
Modifying the Graph

• Excel tries its best to give you a nice looking
  graph. You may have to modify titles, labels and
  series headings. To accomplish this just click
  on the title or label. A black box will surround
  the area, then right click. The dropdown box
  will allow you to change font size, or even font
  altogether

16
Good Start

• This is a very good start to your graph. Titles
  and labels are clear with good spacing between
  values. Make sure you continue to save.

17
Little Details, Big Effects

• Left-click on any data point, all will become
  highlighted, then right- click. Select Format
  Data Series from the dropdown box.

18
Little Details, Big Effects

• The only tab you will need to work with here is
  the Patterns tab. The others you have
  addressed earlier in this module. You may cycle
  through them if you wish to make a change.

The main function of this window is to allow you
to change the type of data point your graph will
display. Dropdown boxes allow you change style,
size, and color. Select OK when finished.
19
Little Details, Big Effects

• Now you may add your trendline. The graph
  created during this module will need an
  exponential trendline.
• Dont forget the Display equation on chart
  option, found in the Options tab.!!

20
Finished Product

• This is a very nice example, and should be
  appropriate for most instructors. Try not to use
  flashy colors and axes lines tend to clutter
  things up. Think simple rather then technical.
  For a professional type graph, the equation
  should be placed outside of the graph.

Please remember to save your data and graphs
several times during your work. You may also
consider backing your work up on a secondary
media.
21
How do I change the placement of the axis?

• On occasion you will run into a situation where
  your graph will take place in an area other than
  the first quadrant. Some instructors will accept
  this format, most will not. Clearly, having the
  (Y) axis on the left hand side makes for an
  easier to read graph.

22
How do I change the placement of the axis?

• Position the pointer (mouse) along the axis in
  which you need to change. A small beige box will
  appear. With the box still in view, right-click.

A dropdown box will appear. With your mouse,
select Format Axis.
23
Format of your Axis

• The Format Axis window will provide you with
  the values Excel has given to your graph, these
  are automatically done for you when you use the
  chart wizard. Notice the checkboxes are
  pre-checked with values given for each. You may
  in fact change any value you want, but you must
  de-select the checkbox to the left of your entry.

In the case of our graph, it is much simpler to
check the Values in reverse and Value (X)
boxes at the lower left of the window.
24
Format of your Axis

• This is the Format Axis window for the (X)
  axis. It operates in the same fashion and the (Y)
  window.
• The extra tabs along the top of the window will
  allow you to make simple changes to the
  characters you have chosen for you axis values.
  These tabs will be unnecessary in that you have
  solved these issues during the design portion of
  you graph.

If you want to reverse the order of your graph,
you must check both boxes in the Format Axis
window for the (X) axis.
25
Finished Product

• The upper graph displays the modified axis, while
  the lower shows the original graph. Notice by
  reversing our numbers the slope or values do not
  change. Our graph is now in a format that we are
  comfortable with and is easier to read. This is
  not a completed graph by far. Tiles, labels, and
  such must be added for a finished product. This
  example is merely for procedure only.

26
Additional Modules and Help

• You have now completed the second module covering
  graphing using Excel software. At this point you
  should feel confident in you graphing abilities.
  
• The next module in this series will cover how you
  can use tools within Excel to help you collect
  data from your regression lines.

27
Credits
Advanced Graphing Using Excel V.1 Created
5/15/02 Last modified 5/15/02 Written and
Produced by James Golen
For more information contact University of
Michigan Dearborn Science Learning Center
www.umd.umich.edu/casl/natsci/slc [phone]