Excel Graphs Xls Format - PowerPoint by eqr17250

VIEWS: 153 PAGES: 48

More Info
									           Excel Graphs

2/3/2011       Excel Graphs   1
Open and save a new Excel workbook
                                 • Open a new Excel
                                 • 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
                                     • 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
                                     • Click on cell E3 to select it
                                     • You will be entering the

                                             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
                                    • 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
                                    • Pressing special function
                                      key F4 causes the $ signs
                                      to be inserted in the cell
                                    • 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
                                   • 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
                                  • No action is needed on
                                    this step
                                  • Click on the Next button to

 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
                                   • 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
                                     • However, if gridlines are
                                       used, they should be
                                       shown in both the X and Y
                                     • Minor gridlines are only
                                       shown when it its
                                       necessary to accurately
                                       read data point
                                     • 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
                                   • This feature is rarely used
                                     for plots included in
                                     reports and other

 2/3/2011           Excel Graphs                        19
Step 4 – Chart Location
                                   • We will create the chart as
                                     an element of the
                                   • 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
                                      – 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
                                    • 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
                                       • 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
                                      • Set the font to 12 pt bold
                                      • Turn the Auto Scale option
                                      • 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
                                       • 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
                                   • 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
                                   • 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

                                  • 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
                                    • 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
                                      • Create a heavy border
                                        around each table with a
                                        light line separating the

 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
                                    • 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
                                    • 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
                                   • Set the legend border to a
                                     black medium light line
                                   • Set the font to 10 pt

 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
                                   • Select the name Series 2
                                     from the list at bottom left
                                   • Type the new name
                                     Measured in the Name

 2/3/2011           Excel Graphs                         47

 2/3/2011   Excel Graphs   48

To top