Get documents about "Excel Graphs Xls Format - PowerPoint
W
Description
Excel Graphs Xls Format document sample
Document Sample
Excel Graphs
2/3/2011 Excel Graphs 1
Open and save a new Excel workbook
• Open a new Excel
workbook
• Save the workbook on
your TechDrive with the
file name hw2.xls
2/3/2011 Excel Graphs 2
Enter coefficients for power law calculations
• Enter labels for the
coefficients a and b in
column A
• Enter values for the
coefficients in column B
2/3/2011 Excel Graphs 3
Create the column of X values
• Enter the column heading
X in cell D2.
• Type the values 0.2 and
0.4 in D3 and D4. These
will set the pattern for
generation of the
remaining X values.
• “Drag select” cells D3 and
D4 by “left clicking” on D3
and moving the cursor to
D4 while holding the
button down. Release the
mouse button.
• Left click on the fill tab at
the lower right corner of
the selected area. Drag
the tab down the column
to cell D17 and release.
• The pattern set in D3 and
D4 is replicated through
the selected cells.
2/3/2011 Excel Graphs 4
Format the X column
• Drag select cells D2-D17
• Right click in the selected
area to bring up the pop-
up menu
• Select Format Cells
2/3/2011 Excel Graphs 5
Format the X column
• Select the Format
Cells/Number dialog pane
• Select the Number
category
• Set the number format to
one decimal place
2/3/2011 Excel Graphs 6
Format the X column
• Click the Alignment tab
• Set the horizontal
alignment to Center.
2/3/2011 Excel Graphs 7
Save your work frequently
2/3/2011 Excel Graphs 8
Enter first X and a formula for Y
• Enter the Y column
heading
• Click on cell E3 to select it
• You will be entering the
formula
y a xb
• Start the entry of the
formula for the cell by
typing the equal key
• Complete the formula by
clicking the respective
cells for the values of a, b,
and X.
• Raising X to a power uses
the exponent operator ^
• Clicking on the cells is
more convenient than
typing the cell names
• Press the Enter key to
complete the formula entry
2/3/2011 Excel Graphs 9
Use absolute references for the coefficients
• The formula in E3 will be
replicated to complete the
column of Y values
• A relative reference is
appropriate for the X value
since replication of the
formula yields the value in
the adjacent cell of the X
column.
• An absolute reference is
needed for the a and b
coefficients in B3 and B4
• Click E3 and then click
before, after, or in the
middle of B3 in the formula
bar
• Pressing special function
key F4 causes the $ signs
to be inserted in the cell
reference
• Repeat the procedure for
the reference to B4
• Press Enter
2/3/2011 Excel Graphs 10
Replicate the formula in the Y column
• Select E3
• Left click on the replication
tab and drag to the bottom
of the Y column
• Note that each of the
replicated cells is
generated with a relative
reference to the adjacent
X value and absolute
references to the a and b
coefficients
• Select the Y column, right
click on the selected area
and click Format Cells
from the popup menu
• Set the number format for
the cells to 2 decimal
places and the horizontal
alignment to Center
2/3/2011 Excel Graphs 11
Start the Chart Wizard
• Drag select the cells
containing the X and Y
values to be plotted
• Click on the Insert Chart
toolbar button
2/3/2011 Excel Graphs 12
Step 1 – Select Chart Type
• In Step 1 of the Chart
Wizard, select the XY
(Scatter) chart type
• Set the sub-type to plot the
points with no connecting
line segments
• Note the button at the
lower right that allows a
preview of the plot
• Click on the Next button to
proceed to the next step
2/3/2011 Excel Graphs 13
Step 2 – Chart Source Data
• The selection of data to be
plotted can be adjusted in
Step 2
• Additional data sets can
be added to the plot on the
Series dialog pane
• We will come back to this
later
• No action is needed on
this step
• Click on the Next button to
proceed
2/3/2011 Excel Graphs 14
Step 3 – Chart Options / Titles
• Chart options are selected
in Step 3
• Click on the Titles tab
• Set the Chart Title to
“Power Law”
• Set the label for the X axis
to “X”
• Set the label for the Y axis
to “Y”
2/3/2011 Excel Graphs 15
Step 3 – Chart Options / Axes
• Axes can be turned on or
off on the Axes dialog
pane.
• Its very rare that we would
not want both axes shown.
2/3/2011 Excel Graphs 16
Step 3 – Chart Options / Gridlines
• Major and minor gridlines
in the X and Y direction
are selected on the
Gridlines dialog pane.
• Its not uncommon to not
show gridlines for plots
that are intended to
convey qualitative
information about the data
trends.
• However, if gridlines are
used, they should be
shown in both the X and Y
directions
• Minor gridlines are only
shown when it its
necessary to accurately
read data point
coordinates.
• Otherwise, showing minor
gridlines only adds clutter.
2/3/2011 Excel Graphs 17
Step 3 – Chart Options / Legend
• The plot legend is
controlled from the Legend
dialog pane.
• If there is only one data
set, it is not necessary to
show the legend. The plot
title should be sufficient to
describe the data.
• If two or more data sets
are shown, there must be
a legend.
2/3/2011 Excel Graphs 18
Step 3 – Chart Options / Data Labels
• Labels for individual data
points are controlled from
the Data Labels dialog
pane.
• This feature is rarely used
for plots included in
reports and other
publications.
2/3/2011 Excel Graphs 19
Step 4 – Chart Location
• We will create the chart as
an element of the
worksheet
• Click on Finish
2/3/2011 Excel Graphs 20
The basic plot is done. Now format it.
The Chart Wizard yields the
basic plot but several more
formatting operations are
needed before an
acceptable plot is achieved.
– relocate the chart area
– resize the chart area
and the plot area
– set plot background to
white with a heavy
border
– set fonts for the axes,
tic mark labels and the
plot title
– set the axis scales and
tic mark intervals
– control the number of
decimal digits in the tic
mark labels to remove
trailing zeros
– set the axis and tic
mark styles
2/3/2011 Excel Graphs 21
Relocate and resize the chart area
• click and drag the chart
area so that it can be seen
on the worksheet without
obscuring other data
• click and drag on the chart
area grab points (small
black squares on the
border) to resize and
reshape the chart.
2/3/2011 Excel Graphs 22
Relocate and resize the plot area
• Left click on the axis labels
and the plot title to move
them to the plot edges.
• Relocate and resize the
plot area to fit on the chart
area.
• Note that the font sizes are
changed by resizing the
chart. The font sizes must
be set manually.
2/3/2011 Excel Graphs 23
Format the Plot Area
• Right click on the plot area
and select Format Plot
Area from the popup menu
• Note that the four steps
from the Plot Wizard are
also shown on the menu.
• Any decision that was
made while configuring the
Chart Wizard can be
reversed here.
• It should never be
necessary to start a plot
again from the beginning.
2/3/2011 Excel Graphs 24
Format the Plot Area
• Set the area color to white
• Select a black, medium
heavy border
2/3/2011 Excel Graphs 25
Set the plot fonts
• Use a 10 pt regular font for
the tic mark labels, a 12 pt
bold font for the axis
labels, and a 14 pt bold
font for the plot title.
• Turn the “Auto scaling” off
for all fonts – this provides
direct control of the font
sizes and prevents the
fonts from being resized
when the plot is resized
• Right click on the Y axis
and select Format Axis
from the popup menu.
2/3/2011 Excel Graphs 26
Set the plot fonts / tic mark labels
• The font for the tic mark
labels is set on the Format
Axis / Font dialog pane.
• Set the font to 10 pt
regular
• Turn off the Auto Scale
option at the lower left of
the dialog pane.
• Repeat this operation for
the X axis.
2/3/2011 Excel Graphs 27
Set the plot fonts / axis labels
• Right click on the Y axis
label and select “Format
Axis Title” from the popup
menu.
• Set the font to 12 pt bold
• Turn the Auto Scale option
off
• Left clicking on the axis
label allows the label text
to be edited without
returning to the Title pane
of the Chart Options dialog
• Repeat this operation for
the X axis
2/3/2011 Excel Graphs 28
Set the plot fonts / title
• Set the font for the plot title
to 14 pt bold
• Turn the Auto Scale option
off
• The title text can be edited
by left clicking on the title
2/3/2011 Excel Graphs 29
Format the axes / Patterns
• Right click on the X axis
and select Format Axis
from the popup menu
• Set the tic mark type to
none. By default, they
were set to outside as can
be seen in the figure.
• If grid lines are included on
the plot, tic marks are not
necessary.
• The minor tic mark type
defaults to none
• Set the location for the tic
mark labels to low. This is
the default for an axis at
the bottom or left edge of a
plot but it is also what we
want if the axis is in the
middle of the plot, i.e., a
plot with positive and
negative Y coordinates
• Set the axis color and
weight to match the border
2/3/2011 Excel Graphs 30
Format the axes / Scales
Rules for axis scales:
• tic mark intervals or major
scale units must be
powers of 10 times 1, 2, or
5, e.g., 1,2,3 or 200, 400,
600 or 0.05, 0.10, 0.15
• the axis limits should be
on an even tic mark
interval
• there should be no fewer
than 3 and no more than 5
major unit divisions along
an axis
• set minimum and
maximum to minimize
empty plot area
2/3/2011 Excel Graphs 31
Format the axes / Number
• Set the number of decimal
places to eliminate trailing
zeros
• Repeat the format
selections for the Y axis
2/3/2011 Excel Graphs 32
Format data series
• To set the format for the
data points, right click on
any point and select
Format Data Series from
the popup menu
2/3/2011 Excel Graphs 33
Format data series
Rules for use of lines and
markers:
• if data consists of
individual points such as
measured data, use data
point markers with no line
• if data is generated from a
calculation which could be
used to generate as many
points as desired, use a
line with no markers
• generally, never use a line
and marker combination
for a single data set
• Set the data point marker
to none
• Set the line to a black,
medium light line
2/3/2011 Excel Graphs 34
Add a second data table
• Resize and relocate the
chart area to make room
for a second data set
• Enter the second data
values shown
• Use the Merge and Center
toolbar button to create
“Calculated” and
“Measured” headings for
the two data sets
2/3/2011 Excel Graphs 35
Create table borders
• Drag select the table of
calculated values.
• Right click on the selected
area and select Format
Cells from the popup
menu
• Create a heavy border
around each table with a
light line separating the
columns
2/3/2011 Excel Graphs 36
The formatted data table
2/3/2011 Excel Graphs 37
Add second data set to the plot
• Left click and drag select
the two columns of data
• Right click on the selected
area
• Select Copy from the
popup menu
2/3/2011 Excel Graphs 38
Add second data set to the plot
• Left click on the plot to
indicate that it is where the
data will be pasted
• Pull down the Edit menu
and select Paste Special
2/3/2011 Excel Graphs 39
Add second data set to the plot
Select:
• Add cells as New Series
• Values (Y) in Columns
• Categories (X Values) in
First Column
2/3/2011 Excel Graphs 40
Format the new data series
• Right click on one of the
new data points
• Select Format Data Series
from the popup menu
• Set the marker foreground
color to black and the
background to no color
• set the marker size to 7 pt
• set the line type to none
2/3/2011 Excel Graphs 41
Add a legend
• With two data sets on the
plot, a legend is required
• Right click on the plot and
select Chart Options from
the popup menu
2/3/2011 Excel Graphs 42
Add a legend
• Select Show Legend
2/3/2011 Excel Graphs 43
Format the legend
• Click and drag the legend
to an open area of the plot
• Resize the plot area to fill
the chart area
• Right click on the legend
• Select Format Legend
from the popup menu
2/3/2011 Excel Graphs 44
Format the legend
• Set the legend area to
white
• Set the legend border to a
black medium light line
• Set the font to 10 pt
regular
2/3/2011 Excel Graphs 45
Change the data series names in the legend
• Right click on the plot
• Select source data from
the popup menu
2/3/2011 Excel Graphs 46
Change the data series names in the legend
• Select the name Series1
from the list at bottom left
• Type the new name
Calculated in the Name
block
• Select the name Series 2
from the list at bottom left
• Type the new name
Measured in the Name
block
2/3/2011 Excel Graphs 47
Done!
2/3/2011 Excel Graphs 48
