Docstoc

Excel 2007

Document Sample
Excel 2007 Powered By Docstoc
					Lesson 8: Working with Large Worksheets
Sorting by a Single Column
     Sort Buttons
         Sort by one column only



       Ascending          Descending




            If a sorting problem occurs, close without saving
            and revert to presorted data.




2
Sorting Selected Rows
     Select rows to sort if
         No blank row separates the list from a total row
         List contains should not be sorted
     Select entire other rows that rows, not
      individual cells




     Sort is based on column A


3
 Sorting by Multiple Columns
            DataSort & FilterSort

                                      Sort each column in ascending
                                      or descending order
Sort by any number
of columns




                     Any same last names are
                     then sorted by first name




  4
 Freezing Rows and Columns
                                Leaves column A visible
                                when scrolling right




Leaves row 1
visible when
scrolling down




                 Use Freeze Panes to lock the headings in worksheets
                 containing more rows or columns than can fit on one screen.



  5
Splitting the Worksheet Window
     View different sections (window panes) of
      a worksheet at the same time

                                             Separate
                                             scroll bars
                                             for vertical
                                             panes




                  Separate scroll bars for
                  horizontal panes



6
Lesson 9: Protecting Workbooks
Protecting Workbooks and
Worksheets
     Three levels:
         Workbook level
         Worksheet level
         Cell level




8
Protecting Workbooks
     Protect against:
         Moving a worksheet
         Adding/deleting worksheets
         Renaming a worksheet
         Changing the window size and position




9
 Protecting Worksheets



Choose exactly
what users may
change in each
worksheet




                 Assign a password to prevent users from turning off protection.




  10
Unlocking Cells before Protecting
a Worksheet
      All cells are locked by default
      To allow editing in selected cells:
          Remove checkmark to unlock them
          Protect the worksheet




11
Protecting Workbooks with Digital
Signatures
      Authenticates that the workbook:
          Originated from you
          Came from a reliable source
          Has not been altered since it was saved
      Methods
          Self-signature – limited security
          Via digital certificate – recommended

             Your network security administrator can
             give you a digital certificate.




12
Creating a Self-Signature
      Digital ID is valid only on your computer
      Not necessary if a digital signature is
       installed




13
Creating a Digital Signature
      Signing Methods
          Invisible
          Signature line in worksheet




           Sign using an existing digital ID or certificate




14
Checking Signature Details
      Workbook status bar display button
      Signatures task pane


      Menu




15
Lesson 10: Managing Multiple-Sheet
           Workbooks
Using Multiple Worksheets
      Summarize data into a summary sheet
      Create linking formulas between sheets




17
Modifying the Default Number of
Slides
      Excel default is three sheets
      Change affects only new workbooks




18
Linking Cells: Why Link?
      Reflect management needs
          Top-level managers want to see the big picture
          Department-level managers are interested in details
      Automatic updating
          Results in linked cells update when detail cells change
      Data entered only once




19
Creating Linking Formulas
               Create the linking formula in the Summary
                worksheet


                                                          Source cell reference

Begin with
                =Postage!B16
equals sign
                  Sheet name of source cell
                  followed by exclamation point




                    Use Point Mode to create a linking formula.




 20
   Copy and Move Worksheets
              Create an exact duplicate of the original
                 worksheet




Check to copy;
leave blank to
move




   21
Copy Formats between
Worksheets
               Copy formats consistently between
               worksheets

 Select All                         Format Painter
 button




22
Naming Cells and Ranges
      Enter a name in the Name box for any cell
         or cell range
        Use in formulas in place of cell references
        Available throughout a workbook


                            Name box –
                            PostageTotal is the
                            defined name of cell B16




23
Naming Rules
        Must begin with a letter
        Cannot resemble a cell reference (A3)
        No spaces, hyphens, or symbols
        Underscores, periods, capital letters OK
            Instructional_Materials
            Instructional.Materials
            InstructionalMaterials




24
Creating Names from Row or
Column Titles


     Select titles and values   FormulasDefined Names
     (columns A and B)          Create from Selection




     Specify which cells
     to use for names

                                Defined names


25
Using Names to Navigate

                            Choose a name from the list




     Highlight displays
     defined range chosen




26
Using Names in Formulas
      =SUM(Salaries)
      =Sales – Expenses
      =TotalPostage
          Linking formula




27
Modifying and Deleting Defined
Names
      Name Manager
                                    Delete the selected name . . .




           . . . or change its cell reference
           with the Collapse button



28
Types of Hyperlinks
      Internal
          To cells in a workbook
      External
          To another workbook or non-Excel file
          To a web page
          To an email address in Outlook




29
Creating Hyperlinks


                                     Create a ScreenTip to
                                     display in the worksheet
     Location
     type                                Cell reference
                                         and worksheet




                Inserted hyperlink


30
Printing Multiple-Sheet Workbooks
      Select multiple worksheets
            (Shift) – Select adjacent sheets
            (Ctrl) – Select nonadjacent sheets
      Apply page setup options to multiple
         worksheets
        Print selected sheets
        Print all sheets in workbook




31
Lesson 11: Creating Tables and Outlines
Why Use a Table?
      Automatic table expansion when rows or
         columns are added
        Calculated columns copy a formula
         automatically
        Table style library
        Filtering automatically available
        Function drop-down list for cells in total
         row




33
     Table Parts
                             Column (field)                           Calculated
                             holds one                                column
                                                      Filter button
                             category of data




Row (record)
holds data for
one person,
event, or
transaction




                           Labels outside the table
      Optional total row
34
Converting a Range of Cells to a
Table


                             Choose a table style
                                                               Accept defaults
                             (may be changed later)
Select the worksheet range

                                    Rename the table to a descriptive name for
                                    use in formulas.



Result of table conversion




35
Formatting a Table
                                      First Column and
                                      Last Column:
                                      Highlight all entries in
                                      the column




Banded Rows:
Apply a fill to alternating rows
to enhance readability

         Total Row:
         Displays total in last
         column if contains numbers




36
Summary Formulas in the Total
Row
      A summary function may be chosen from
      a list for any number column




                Result of   Result of   Result of
                choosing    choosing    choosing
                AVERAGE     COUNT       SUM
37
Selecting Table Rows and
Columns


     Click inside the first cell of a table row to select the row

                                    Click the top of the table
                                    column heading to select
                                    the table column




38
Creating Calculated Columns in a
Table
      Select any cell in the table column
      Create a formula as usual with Point Mode
          =H5+I5
      Excel converts cell references to
       structured reference in the formula




              The formula is applied automatically
              to all cells in the calculated column


39
Sorting a Table Column
     Drop down the
     column heading list




                           Up arrow indicates the list is sorted
                           from A to Z or lowest to highest



                               In this example of sorting by color,
                               some text in the table is red




40
     Filtering a Table Column
                     Drop down the
                     column heading list


Filter by color or
for text, such as
Begins with or                     Indicates that not all
Does not contain                   records currently display




                                           All records except those with IL
Limit the display                          are hidden temporarily
to one or more
selected entries




41
Custom Filters




                                               Filter will display only records
                                               with a Review Date between
                                               April 15 and June 15


        Choose Clear Filter from “[column name]” in the
        column heading list to remove any type of filter.

42
Working with Outlines and
Grouping
      How outlines work
          Group data by rows and columns
                   Outline levels are displayed
                   on the top and left




            Expand and collapse
            grouped data
43
Working with Outlines and
Grouping
      Auto outline                         Create groups
           For smooth Auto Outline,         manually
            arrange detail data to right         Select detail data and
            or left of summary formulas           group, even if Auto Outline
                                                  has been performed




44
Displaying Subtotals in an Outline
      First sort the list by the column on which
       subtotals will be based




45
Lesson 12: Creating PivotTables and Macros
Working with PivotTables:
Example 1

                  Raw data in worksheet


                  Summarized by products sold
                  by each city in a PivotTable




                                               Product totals

                                          City totals




47
Working with PivotTables:
Example 2



                                  City totals




     Summarized by city then by
     product



48
How PivotTables Work




49
Manipulating Fields on a
PivotTable


         Three fields are
         selected
                                            Region field dragged to
                                            Column Labels area




                            Pivoting is the process of
                            dragging a field from a row
                            to a column, or vice versa.




50
Changing Field Data on a
PivotTable
      Add or remove fields         Suppress display of an
                                     item within columns




      Filter for specific items
       within rows




51
Filtering a PivotTable Report




                     Choose (All) from the drop-down list
                     to display all items in the field.



52
Editing PivotTable Calculations
      Change the function
      Create a calculated field
       (custom formula)




                                   PivotTables do not
                                   automatically update after
                                   the source data is changed.

      Update the active
       PivotTable or all
       PivotTables in
       workbook

53
Working with PivotCharts
      Create from a PivotTable or worksheet
         data
        Add or move fields in PivotTable Field List
         task pane as usual
        Filter fields as usual




             Format a PivotChart just as
             you would a regular chart.




54
Setting the Macro Security Level
      Choose OfficeExcel Options




      Disabling with notification displays
       message when workbook is opened




55
Macros
      Set of instructions that can be played
         back at any time
        Useful for automating routine tasks




56
Recording a Macro
      Works like a video recorder
          Turn on recorder
          Name the macro
          Perform sequence of keystrokes and mouse clicks
          Stop recording




                                               Where macro is stored




                All keystrokes and mouse clicks are recorded,
                including mistakes and corrections. A macro may be
57
                re-recorded, if necessary.
Using a Personal Macro Workbook
      Make macros available in all workbooks
      on your computer system



                                        Macros can be used
                                        with any workbook




          The Personal Macro Workbook is a hidden workbook.




58
Assigning Macros
      Macros may be run using:
          Run command in the Macro dialog box
          Shortcut keys
          Custom buttons




59
Using Shortcut Keys
      Run a macro with a few keystrokes



                                                      Assigning a shortcut key
                                                      to run a macro from
                                                      within a worksheet


           A shortcut key must use either (Ctrl) or
           (Ctrl)+(Shift) plus a letter.




60
Using Custom Buttons in
Worksheets
      Insert a shape
      Assign a macro




      Click the button to run the macro

61
Saving a Workbook as Macro
Enabled
      Use Save As
      Change the Save As Type
      Filename is saved with the extension
       .xlsm




62
Lesson 13: Using Financial Functions and Data
           Analysis
   Using Financial Functions
               Perform financial calculations
               Type the function or use the Insert
                     Function dialog box


                                            Many categories
Get help
                                            to choose from
choosing the right
category/function
                                                Many functions
                                                to choose from




    64
Introducing the PMT (Payment)
Function
      Calculate a periodic payment amount

     Annual rate
                         # of payments          Loan amount
     divided by 12
                     (divide by 12 if years)    (negative number)




                                               Monthly payment
                                               that results




65
Introducing the FV (Future Value)
Function
      Calculate the future value of an annuity

     Annual rate                         Deposit amount
     divided by 12   # of monthly
                       deposits          (negative number)




                                    Type the function in the Formula Bar
                                    or use the Function Wizard




66
    Introducing Data Analysis Tools:
    Using Goal Seek
                Let Excel adjust your variable to achieve a
                     desired outcome




                                                            Specify desired
                                                            outcome


Use Goal Seek
on any cell with a
formula


                                    Choose a cell as a
                                  variable for adjustment
    67
    Using Solver
                Let Excel adjust multiple variables to
                      achieve a desired outcome


                                         To avoid errors, use the point method
                                         when entering cell references.




    Adjust multiple
    variables

Set constraints on
any affected cell
     68
Using Analysis Tools:
Scenario Manager
      Scenario                             Manage scenarios
           Named combination of                 Save and compare multiple
            values assigned to                    scenarios
            variables in a what-if model
                                            Add scenarios
                                                 Input values for additional
                                                  what-if models




69
Using the Data Analysis ToolPak
      Choose from 19 analysis tools
      Each tool performs its own function




           If Data Analysis is not on the Ribbon,
           you must install it (OfficeExcel OptionsAdd-Ins.




70
Using the Data Analysis ToolPak


                                                             Performs calculations on the
                                                             scores in column D and displays
                                                             results in columns I–L




        Original data

     Results duplicate and sort
     scores, which are ranked from
     highest to lowest




         The rows containing original data in columns A–D are not sorted.

71
Lesson 14: Auditing and Additional Functions
 Using 3-D Cell References in
 Formulas
              Create a formula using data in the same
                  cell on a range of worksheets

Deleting a
worksheet or
moving a
worksheet tab
to outside the
range in the 3-
D reference
affects the
formula.




  73
Introducing Lookup Functions:
Using VLOOKUP
      VLOOKUP (Vertical Lookup)
          Finds values in tax, commission rate, and other lookup
           tables
          Syntax consists of three components (arguments)




                                              2nd argument:
                        1st argument:         Lookup table
                 Cell value to be looked up
                                                           3rd argument:
                                                          Column number
                                                          in lookup table




74
    How PivotTables Work

Function searches
for 14,000 (cell
C6) in left column
of lookup table
                                                                      Formula result




               Search takes place in
               the Comm_Rate
               lookup table
                                                                      Corresponding
               Search stops at
                                                                      contents of 2 columns
               10,000 because the
                                                                      are returned to the
               lookup value is at least
                                                                      formula cell
               10,000 but not 20,000


                          The first column of the lookup table must
                          be sorted from lowest to highest.
    75
Creating a Formula with the IF
Function
      Take an action if the logical test is true
      Take a different action if false
                                   2nd argument:
                                   Action if true




                                             3rd argument:
                 1st argument:               Action if false
          Logical test to be evaluated




76
Using an IF Function to Display
Text
      Display Yes if true
      Display blank if false




77
Using Criteria IF Functions
      Use AVERAGEIF, COUNTIF, and SUMIF to
      calculate using one criterion
                                2nd argument:
                                   Criterion




                1st argument:                   3rd argument:
            Cells to be evaluated          Sum only the cells in this
                                         range that meet the criterion




78
Using Criteria IF Functions
      Use AVERAGEIFS, COUNTIFS, and
         SUMIFS to calculate using up to 127
         criteria sets
        Only cells that meet their respective
         criteria are calculated
                                                    3rd argument:
                          1st argument:               2nd range
                   1st range to be evaluated       to be evaluated




                                                                 4th argument:
                                   2nd argument:
                                                            Criterion for 2nd range
                               Criterion for 1st range



79
Tracing Formulas: Auditing Tools
      Analyze and debug worksheets
      Locate formulas dependent on a cell value
      Locate errors in formulas




80
Tracing Precedents
      Trace precedents
          Precedents = cells referenced by a formula
          Trace Precedents command displays arrows to precedent
           cells




81
How Tracing Precedents Works
      Example:
          Trace precedent cells included in the formula =C6*D6
      Cells from the lookup table are included;
       they are precedents in the D6 formula

                                              Blue tracer arrows
                                              indicate precedent cells




82
Tracing Dependents
      Trace dependents
          Dependents = Cells containing formulas that reference the
           selected cell
          Trace Dependents command displays arrows to the
           dependent cells




83
How Tracing Dependents Works
      Example:
          Trace the formulas that depend on the value in cell C6




             Use the Remove All Arrows
             command to remove the tracer arrows.



84
Checking Errors
      Excel displays a triangle
         icon
        Error Checking menu on
         a cell helps identify the
         cause of a formula error
        Error Checking
         command navigates to
         all cells with errors




85
Using Evaluate Formula
      View the sequence of calculations of each
       part of a formula




86
Using the Watch Window
      Displays formula results when the formula
       is out of view




87
Lesson 15: Using Advanced Formatting and
           Analysis Tools
Working with Grouped Worksheets
      Work simultaneously on all worksheets in
      the group
          Enter data in cells
          Format selected cells
          Copy and paste




                                   The grouped sheet
                                     tabs turn white
89
  Ungrouping Worksheets
            Remember to ungroup when ready to
                enter variable data




Ungroup using
context menu


                                      Or click a sheet tab
                                      not in the group




  90
Consolidating Worksheet Data
          Combine values from source worksheets
              into a destination sheet

Destination
worksheet



                                                  Use Point Mode to select reference
                                                  ranges in each worksheet to be
                                                  consolidated
Reference
list




                  The summary worksheet does not automatically
91                update when source data is changed.
Consolidation Functions
        SUM
        AVERAGE
        MIN
        MAX




            The SUM function is used most often.




92
Creating Links to Source Data in a
Consolidation
      Displays an outline with source data rows
         above summary row
        Changes to source data do update in the
         summary worksheet




93
Types of Consolidation
      By Position
           References same range in
            multiple worksheet
           Works well with identical
            layouts



      By Category
           References cells by row or
            column
           Works well with different
            layouts




94
Working with Data Validation
       Restrict data entry in cells
              Types of values
              Minimum and maximum values




     Create input messages
     and error alert messages
     to customize the validation

95
Locating All Invalid Data
      Red circles indicate a potential problem
          Ignore or correct problem
      Red circles are temporary and do not print




96
Working with Conditional
Formatting
      Formatting applied only if cell contents
         meet set conditions
        Visual alert that a value is outside
         parameters
        Choices on the Ribbon



                                                   Data bars, color scale, icon
                                                   sets

                                    Custom rules
                                    you create

         Presets: Frequently used
97       formats
Creating a Custom Conditional
Formatting Rule




        Conditional formatting is used as a visual alert to
        indicate that a value is outside the parameters.


98
  Data Tables – One Variable
               Helpful in what-if analyses
               Values from data table substituted for a
                  cell reference in a formula
                 Formula =FV(B3/12, B4,–B5) is the basis
                  for the data table calculations shown
Each payment
value in the                                       Value of 0 is returned in
data table is                                      the cell with the formula
substituted for
cell B5 in the
                                                     Formula results for
formula
                                                     each payment value




                           Column of payment
   99                      values that you input
  Data Tables – Two Variables
              The same formula =FV(B3/12, B4,–B5) with
               variables substituted for two cell references
              A result displays for every combination of
               rate and payment listed in the table

                                         Row of interest
                                         rates you input
The input cells
are blank
because
multiple values
are substituted                                            Formula results
from the row                                               for each
and column.                                                combination of
                                                           rate and payment

                   Column of payment
  100
                    values you input
Creating Trendlines on Charts
       Linear trendline
           Best-fit straight line that shows data trend




101
Creating Trendlines on Charts
          Polygonal trendline
                   Smooth out fluctuations by averaging adjacent data points




Increase the
Order option
value to make
a smoother
trendline.




102
Lesson 16: Integrating Excel with Other
           Programs
About File Formats
            File format
                Structure for storing data in a computer file
            Native file format
                Application program normally used to save files
            Identifying a file’s format

                                                                   By
 By icon                                                           filename
                                                                   extension




                   Choose to display filename
                   extensions in Windows Explorer.

104
Compatibility with Previous Excel
Versions
       Excel 2007 file formats
           Open XML, based on Extensible Markup Language
       Prior versions
           Different file formats
           Title bar identifies these files




105
Compatibility with Previous Excel
Versions
       Save a workbook in a different Excel file
        format



                           Compatible file format




106
Handling Compatibility Issues
       Compatibility Checker
           File owner scans workbook
           Any changes to correct incompatibility remove features in all
            Excel versions
       Compatibility Pack
           Users of versions 2000, 2002 (XP), and 2003 install a file
            converter
           Incompatible features hidden
           Features visible when file is reopened in Excel 2007




107
 Handling Compatibility Issues
           Scan non-XML files with Compatibility
               Checker



Correct any                                           Locate these cells
areas marked                                          in worksheet




                     Copy compatibility report to a
                     new, printable worksheet
 108
Converting Workbooks to Other
File Formats
       Converter program allows an application
        program to open or save files in nonnative
        formats
           Text
           PDF
           XPS
       Several converters are installed in Excel
              Excel warns you that formatting or features may be lost with
              the new file format. You may first save in Excel Workbook
              format to preserve a copy of the workbook.




109
Types of Text Files
       Tab delimited               Characteristics
                                         Saves worksheet data in a
                                          text-only format
                                         Removes all formatting
                                         May be only method for
            Arrows do not print          transferring data between
                                          incompatible programs
       Comma delimited




110
Saving Excel Data in Text Formats
       Save a worksheet
          in tab delimited file
          format
         Compare icons for
          files saved in different
          formats
         Open and view text files in Notepad




111
Additional File Format Types
       PDF (Portable Document Format)
       XPS (XML Paper Specification
       Characteristics
           Users may use a free reader to view/print workbooks with all
            formatting intact; don’t need Excel at all
           Prevents users from making any changes or accessing
            hidden information

              The PDF and XPS command appears on the Save As
              submenu after you download and install a converter.




112
Saving Excel Data in PDF Format
       Save a worksheet in
        PDF format




       Open and view
        workbook in Adobe
        Acrobat Reader




113
Using Excel Tables with Word Mail
Merge


Main document containing
merge field codes in Word          Selected records in Excel




                       Personalized document copies in Word




114
   Sharing Excel Data with Access
               Use the filter, query, and report
                    capabilities in Access
Choosing options
    in the Import
    Spreadsheet                              When the link option is
 Wizard (Access)                             selected, any changes to
                                             the original worksheet will
                                             update in the Access table.




       Resulting
    Access table




    115
Link Excel Charts on PowerPoint
Slides
       Copy chart in Excel
       Paste options
           Link to chart data (default)
           Embed the entire workbook
            along with the chart
           Paste the chart as a picture only



                                            Slide containing the chart pasted
                                            into a content placeholder




              Embedding a chart gives the user access to all data
116
              in the workbook, which may not be desirable.
Importing Data from Other
Applications
       Drag and drop




           Dragging and dropping cuts the text from the source document.
           Restore it with Undo or by closing without saving.




117
Importing Data from Other
Applications
        Copy and paste
Copy        Adjust formatting after pasting




                                               Paste




118
Importing External Data
           Import data from text files
                     Adjust formatting after importing text




The Text Import
Wizard guides
you through
importing text
file data.

                                                      Preview shows tab
                                                      codes as small boxes,
                                                      not arrows




119
    Importing External Data
                Import data from a web page
                          Adjust formatting after importing web data




                                                  Enter the desired URL in the Internet
Indicate tables that                              Explorer window
may be selected
from the web page;
once selected,
arrows turn green



     120

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:11/10/2011
language:English
pages:120