Advanced Excel Training Manual - PowerPoint

							Microsoft Excel –
Advanced Topics

              April 17, 2008
            Abby Wiertzema
         wiertzem@oswego.edu
  http://www.oswego.edu/~wiertzem
                    Topics
   Protecting your data
   Excel Tricks
   Customizing your workspace
   Macros
   Chart Wizard
   Link data on multiple worksheets
   Pivot Tables
   Importing data
                   Protecting your Data

   Multiple copies of different media & locations
       Local hard drive
       Server hard drive
           http://www.oswego.edu/admin
       Diskette, CD, flash drive
   Autosave – set a period of time you are
    willing to re-do your work
   Save before attempting something new and
    unsure; undo may not work (macros, some
    sorts, pivot tables)
        Trick 1 – Different SAVE Options
   Save – for the first time and to save open file
   Save As – Saves the open file with different name/format
       Text format - .csv, .dif, .prn, .slk, .txt
       Database format - .dbf, wbx (Quattro Pro), .wks (MS Works)
       Lotus 1-2-3 format - .wk1, .wk3, .wk4
       Previous Excel format - .xls
       Web ready format - .htm, .html
   Save As Web Page
   Save Workplace – saves display information, not data
   Autosave – Use Tools, Options, Save tab; not a
    replacement for regular saves
          Trick 2 – Pick from a List
   When you want to
    select from values
    already entered in a
    column of cells, right-
    click on the cell and
    select “Pick from Drop-
    down List”
       Trick 3 – Insert Break in a Cell

   To start a new line of text at a specific
    point in a cell, click where you want to
    break the line, and then press
    ALT+ENTER.
      Trick 4 – Hide Rows or Columns

   Highlight rows or columns to be hidden
   For Rows, select Format, Row, Hide
   For Columns, select Format, Column, Hide
   Select different ranges by holding CTRL
   Hidden rows show „missing numbers‟
   Hidden columns show „missing letters‟
   Easiest way to Unhide is to highlight entire
    spreadsheet, then select Format, Row,
    Unhide or Format, Column, Unhide
             Trick 5 – Named Ranges
   Allow you to select a range of cells and to
    refer to them by a name rather than the
    range, i.e. Depts rather than A2:A4
   Can be used in formulas & data validation
   How:
       Type the values in a column
       Highlight all values
       In the Names field, type a unique name
       Press Enter.
   Find them under Insert, Name, Define
                Trick 6 – Data Validation
   Use this when you want to define data in a cell
       For specific values from a list:
           On Sheet2, create a list of values that are valid
           Highlight and name the range
           On Sheet1, click on a cell, select Data, Validation
           On Settings tab, change Allow to a “List”. Type “=range-
            name” in Source. It is case-sensitive.
           Copy down to multiple columns.
       Other validations – number minimum/maximum,
        length of characters entered, date interval
       Can have Input Message or Error Message

   http://office.microsoft.com/en-us/excel/HA010346571033.aspx
                   Exercise
   Open both workbooks. Arrange as desired.
    Save As Workplace. Close & reopen.
   Save a workbook as a web page. Open to
    see how it looks.
   Practice hiding & unhiding rows & columns.
   On Trick5-Validation tab, enter several
    School names. Name the range “Schools”.
    Then use the range name to validate the
    School value in cells D23 through D27.
    Customizing your workspace (1 of 3)

   Tools, Customize – allows you to change
    how the toolbars look and what is displayed
       Toolbars tab – decide which toolbars to display
       Commands tab – decide which icons appear
       Options tab – show full menus, show Screentips
   To move icons on a toolbar
       Select Tools, Customize, then go to the toolbar
        and drag-and-drop
Customizing your workspace (2 of 3)
   Tools, Options
     View tab – display settings

     General tab – Recently used file list, Sheets in
      new workbook, Standard font (name and font
      size), Default file location (where documents are
      saved), User name
     Custom Lists

     Save tab – Autosave time interval and location

     Security – Password to open, Remove personal

      information
    Customizing your workspace (3 of 3)

   Exercise – Add a Save As button and change its
    picture
       Open Tools, Customize, Commands tab, <Rearrange
        Tabs> button
       Click on ToolBar
       From File category, add Save As & move below Save
       Click on Modify Selection or right-click icon
       Select Test only (in Menus)
       Select Change Button Image; change to diskette
       Select Edit Button Image; make changes
       Close
              Macros (1 of 2)
   Practice the steps to create the macro
   Go to Tools, Macro, Record New Macro
   Give it a name and a Shortcut key
   Then record the steps to be included
   When complete, click on the Stop button
   Test the macro using shortcut
   It is possible to EDIT the macro
   Once a macro is run, you cannot undo
    the results
                    Macros (2 of 2)

   Exercise: Create a macro and a toolbar icon
    for it
       Create a macro that will transpose a row of cells
        to a column
       Using the Tools, Customize dialog box, add a
        new icon to the Formatting toolbar
       Associate the icon to the macro
       Change the picture on the icon
       Test the icon
                 Chart Wizard
   Select the range of data
   Use Insert, Chart or the icon for Chart Wizard
   Select chart type and follow instructions on
    remaining wizard windows.
   Modify the chart afterwards by manual changes
    or by using the Chart menu
   Exercise – Add sales data for 2 more years.
    Change chart colors. Change the legend. Use
    the Chart menu to make other changes.
Link data on multiple worksheets



 "=SUM('c:\Documents and Settings\wiertzem\
 My Documents\[Budget.xls]Annual'!a1:a6)"

Exercise – Use the example in Excel_Adv_IncomeStmt.xls
in the Mar tab, add values for Income (Sales, Donations,
Other) in column G. Use a formula to create the YTD
values in column H.
               Pivot Tables (1 of 4)
   An interactive view of your data
   Think of it as a kaleidoscope
   Why to use a pivot table:
       You have large amounts of data to analyze and
        summarize
       You need to find relationships in the data
       You need to find data trends
       You anticipate frequent requests for changes
       You need to create subtotals that frequently change
       You need to make charts of the data.
            Pivot tables (2 of 4)
   Components:
     The Data area – numbers being summarized

     The Page area – fields used for filtering

     The Column area

     The Row area
             Pivot Tables (3 of 4)
   Cautions:
       Make sure your data is in tabular layout.
       The first row is labels for column headings
       Use unique headings
       Do not use row labels
       Each row is one individual data element
       Eliminate gaps and empty cells
       Apply appropriate formatting to the cells
               Pivot tables (4 of 4)
   Use the Pivot Table Wizard
       Select the range of data
       Determine where to place the pivot table
       Drag the row, column, and data labels


   Changes to the data
       Use the exclamation symbol (Refresh Data)
       Only when the range of data has not changed
                 Importing data
   The easiest file type to import is tab-delimited text.
   Open Excel, then Open the text file.
   The wizard will walk you through the process.
   Any field that contains a leading zero must be
    marked as “text” to avoid Excel dropping the leading
    zero. This includes Social Security numbers and Zip
    Codes.

   Download Importing.txt. Import into Excel. Be
    careful of zip codes and SSNs.
 Internet Resources – Free Excel Templates
Financial
 http://www.swlearning.com/finance/moyer/excel.html

 http://www.exinfm.com/free_spreadsheets.html

 http://www.dotxls.com/

Educational
 http://www.sabine.k12.la.us/class/excel_resources.htm

Business
 http://www.mhhe.com/business/opsci/aczel05/student/excel.mhtml

Scientific
 http://www.crystalball.com/resources/rec_templates.html




          Microsoft‟s Office home page, Templates tab:
http://office.microsoft.com/en-us/templates/FX100595491033.aspx?pid=CL100632981033
      Pivot Table Internet Resources
   http://office.microsoft.com/training/training.aspx?AssetID=RC010136191
    033&pid=CR061831141033

   http://biostat.mc.vanderbilt.edu/twiki/pub/Main/TheresaScott/PvtTblsNCh
    rts.Macros.pdf

   http://www.ozgrid.com/Excel/PivotTables/ExCreatePiv1.htm

   http://www.cpearson.com/excel/pivots.htm


      Go to Help, Microsoft Excel Help or
                use the F1 key