Excel Graphs

1
Excel Graphs

• Objectives
• Create tables for plot data
• Create an xy scatter plot
• Control all aspects of the plot format and
  appearance

• Assignment
• This tutorial contains instructions for the
  creation of 3 data tables and plots
• Arrange each plot with its data table on a
  separate worksheet of a single workbook
• Submit the completed workbook as excel_graphs.xlsx

2

• The first step for creating an Excel graph is to
  generate some data. We are going to start by
  plotting a linear function.
• enter the titles and values for the slope and
  intercept coefficients
• create the column of x values by entering the
  first two and replicating the sequence
• enter the formula to calculate values of y with
  absolute references to the slope and intercept
  coefficients
• replicate the formula to the rest of the column
• complete the data table formatting as shown

3

• We will be creating an xy scatter plot.
• select the insert tab to reveal the chart command
  group
• drag-select both the x and y columns of data
• select the Scatter With Only Markers option

4

• We created our graph but it does not look very
  good. We have a lot of work to do before this
  will be an acceptable graphic for a professional
  engineering document.
• Here are the basic rules for an engineering plot
• Plot must include a title and axis labels with an
  indication of units if relevant.
• The tic mark or grid intervals on the axes must
  be powers of 10 times multiples of 1, 2 or 5,
  e.g., 10,20,30,40 and 2,4,6,8, and
  50,100,150,200 , etc., are acceptable but
  3,6,9,12 and 40,80,120,160 , etc., are not.
• The size of the tic mark interval should be such
  that there are a minimum of 3 and a maximum of 6
  intervals along an axis

5

• Click somewhere on the chart.
• Note how a special Chart Tools tab appears when
  you have the chart selected.
• Click on the Layout tab of the Chart Tools
• To add a chart title, select the Above Chart
  option from the Chart Title menu

6

• Click in the chart title box and enter the title
  shown
• Add a title for the horizontal axis by selecting
  Title Below Axis from the Axis Titles menu and
  submenu
• Repeat for the vertical axis

7

• Experiment with clicking on various parts of the
  graph. Click on the central Plot Area and
  reposition and resize it as shown.
• Click on the axis titles and enter the text as
  shown

8

• We need to adjust the appearance of the axes.
• right-click in the vicinity of the x-axis until
  it is selected as shown
• select Format Axis from the resulting popup menu

9

• There are several adjustments to make here
• instead of allowing Excel to auto scale the axis,
  select Fixed and set the Minimum and Maximum
  values to -10 and 10.
• set the Major Unit (interval between tic marks on
  the axis) to a fixed value of 5
• set the Major Tick Mark Type to Cross
• set the Axis Labels (location) to Low or bottom
  of the plot area

10
Since the tick mark intervals are on even
multiples of 5, select the Number Axis Option and
set the number of decimal places to zero.
11

• resize and reshape the plot area until it looks
  as shown
• right click on the vertical axis and select
  Format Axis as before
• repeat the axis formatting operations until the
  vertical axis appears as shown
• Note The rules for tic mark intervals have been
  satisfied, i.e., powers of 10 times 1, 2 or 5
  with a minimum of 3 and maximum of 6 intervals
  along an axis.

12
Since there is only one data set, there is no
need for a legend. Select None from the Legend
menu.
13

• To create a solid border around the plot area
• right-click in the plot area and select Format
  Plot Area
• in the Format Plot Area dialog, select Border
  Color
• select Solid Line
• set the color to Black

14

• Almost done!
• select Major Gridlines from the Vertical
  Gridlines menu

15

• Finished!!!
• with this one
• two to go

16

• Select Sheet2 of the same workbook
• Create a data table based on a Power Law
  Function
• Create a plot of the function with the formatting
  features shown
• add titles for the plot and the axes
• format the axes for the scales, tick mark
  intervals, tick mark types, and number formats
• grid lines on the major tick mark intervals in
  the horizontal and vertical directions

17

• Select Sheet3 of the same workbook
• Create a data table based on an Exponential Decay
  Function
• Create a plot of the function with the formatting
  features shown
• add titles for the plot and the axes
• format the axes for the scales, tick mark
  intervals, tick mark types, and number formats
• grid lines on the major tick mark intervals in
  the horizontal and vertical directions