SCE         In-class      
  MS EXCEL - POWER USER SKILLS     notes.      
  FALL 2016     (Click      
              here)      
                     
                     
# Today's Topics - Monday, Nov. 28, 2016            
                     
  24.                  
    Terminology & Concepts Review (cont'd)            
     - Once again we will download this file to the Documents folder            
       - If you see a gold area at the top, click the "Enable Editing" button            
     - Here is the file we will review            
        Terminology & Concepts Review from 10/24/16   LINK      
                     
  25.                  
    The wonderful world of IF! - Part 2            
     - We will put some logical tests to work as the first argument            
    of the IF function            
     - We can also make note of different purposes for using IF            
        IF and nested IF   LINK      
                     
  26.                  
    The wonderful world of VLOOKUP            
     - VLOOKUP can be used two ways            
      A. Pluck values from specific cells of a lookup table in order to use             
      them in a report or a formula            
      B. Recode data from one form to another.  (Example: convert a            
      day-of-the-week number to the name of that day.)            
     - VLOOKUP works in a two-stage process            
      1. Locate a specific row by matching a key value to the first cell's value             
      (in the first column of the lookup table)            
      2. Go to the desired cell in that row (determined by a column number),             
      and return that cell's value            
     - Introduction to the basic usage of VLOOKUP            
        Lookup using the Vlookup function   LINK      
                     
  27.                  
    How to work faster            
     - When a person gets comfortable with Excel, it is natural to            
    want to find ways to work faster            
     - Sometimes people discover right-mouse-click shortcuts            
     - In this series of exercises, we will explore some keyboard            
    shortcuts and keyboard+mouse techniques            
        Hard acceleration methods   LINK      
                     
                     
    Home Work            
                     
    9. Practice writing the IF formulas we did in class            
        IF and nested IF   LINK      
              Home      
              Work      
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
                     
  Previous Topics            
                     
        Monday, November 21, 2016            
                     
        WARM-UP & QUESTIONS            
                     
    Regarding your HW...            
       - How did it go?            
       - Do you want to share what you did?            
       - Did doing HW spark some questions?            
        Fix name & format errors in a patient census workbook   LINK      
                     
                     
  23.                  
    The wonderful world of IF - Part 1            
     - The IF function is a versatile "decision-making" tool            
       - It allows you to dynamically alter the value that an expression returns            
     - Perhaps the trickiest part of the IF function is its first            
    argument, known as logical_test            
       - Therefore, this is a good place to start            
        Logical test & son of logical test   LINK      
                     
        Monday, November 14, 2016            
                     
  21.                  
    Why are named vectors SO useful in tables?            
     - After creating the named vectors, you can make easy-to-            
    understand formulas using those names            
     - Tables are perfectly set up to name vertical vectors            
       - Every column has text at the top            
         - This text can be used as the "helper cell" for the Create from            
        Selection command            
     - Here is an example that illustrates named vertical vectors in            
    a table            
        THE POWER OF NAMED VECTORS FOR WRITING FORMULAS   LINK      
                     
    Home Work            
                     
    8. Fix name errors (and other errors) in this workbook     Home      
       - If you can't fix all the errors, come back to class with questions!     Work      
        Fix name & format errors in a patient census workbook   LINK      
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
  22.                  
    Pivot tables, Part 2            
     - There is a very interesting feature called "drill-down"            
       - It allows you to see the full set of records in the table that contributed             
      to a single summary statistic            
        Using Drill-down in Pivot Tables   LINK      
                     
        Monday, November 7, 2016            
                     
  19.                  
    Named cells and vectors (cont'd)            
     - Here is an exercise to make named cells and ranges            
        Named cells and ranges   LINK      
                     
  20.                  
    Separate first and last name            
     - Each step is in a separate cell            
     - PART A            
       - In these steps, put the formula for each step in the cell to the right of             
      the prevous cell            
        1. Type a first and last name in a cell (such as Mickey Mouse)            
        2. How long is the text in the cell with the full name?            
         - LEN  function            
        3. Get the first name.  Type the number of characters (in this             
        case, 6).             
         - LEFT  function            
        4. Get the last name.  Type the number of characters (in this             
        case, 5).             
         - RIGHT  function            
                     
     - PART B            
       - Now, go to a different row, and start over            
        1. Type a first and last name in a cell (such as Mickey Mouse)            
        2. How long is the text in the cell with the full name?            
         - LEN  function            
        3. Get the character position of the " " (space) in the cell with the           
        name            
         - FIND  function            
        4. Use the cell from Step 3 minus 1 to get the length of the first             
        name            
        5. Get the first name.  Instead of typing the number of characters,           
        use the reference to the cell from step 4.            
         - LEFT  function            
         - This is equivalent to:            
        LEFT(full_name, FIND(" ", full_name) - 1)            
        6. Get the last name.  Instead of typing the number of characters,           
        use the cell from Step 2 minus the cell from Step 3.            
         - RIGHT  function            
         - This is equivalent to:            
        RIGHT(full_name, LEN(full_name) - FIND(" ", full_name) )            
                     
    Home Work            
                     
    7. Separate first and last name, part 2            
        Text formulas for separating names   LINK      
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
        Monday, October 31, 2016            
                     
        WARM-UP & QUESTIONS            
                     
    Regarding your HW...            
       - How did it go?            
       - Do you want to share what you did?            
       - Did doing HW spark some questions?            
                     
    Introduction to using text functions            
     - Separate first and last name.  Each step is in a separate cell.            
      1. Type a first and last name in a cell (such as Mickey Mouse)            
      2. How long is the text in that cell (LEN function)            
      3. Use the LEFT function to get the first name (in this case, 6             
      characters)            
      4. Use the RIGHT function to get the last name (5 characters)            
                     
     - Bonus question:            
       - Can you do the same thing with this name?            
        Hubert Wolfeschlegelsteinhausenbergerdorff            
         - Hint: you may wish to copy the name from the the line above             
        this line            
        Is that a real name?   LINK      
                     
                     
  18.                  
    How to record a macro            
     - Macros can automate a sequence of commands that you want to            
    perform on a spreadsheet            
     - One way to create a macro is to record yourself performing a            
    sequence of commands            
       - This is tricky.  There are a lot of things that can go wrong.            
        Introduction to Macros   LINK      
                     
  19.                  
    Named cells and vectors            
     - We have learned how to name individual cells            
       - There can be multiple benefits from using named cells            
         - Turn formulas into "stories"            
         - Use values stored in another worksheet (or workbook)            
         - Avoiding problems with relative reference when copying a            
        formula            
     - We can create named vectors in the same fashion as we            
    created named cells            
       - Vector: a range of cells that is only one column wide or 1 row tall            
       - Use a "helper cell" and the Create from Selection command on the            
      Formulas ribbon            
       - The benefit from named vectors is hard to grasp            
         - Excel's Name Manager sees the name one way (as a range)            
         - The formula in a cell sees the name another way (as a single            
        cell)            
     - Here is an exercise to make named cells and ranges            
        Named cells and ranges   LINK      
                     
    Home Work            
                     
    6. Run and view some simple macros            
     - Here are some simple macros that were created using the            
    macro recorder            
        Simple Macros You Can Use   LINK      
         - To run a macro use the "View Macros" command or else use            
        a shortcut provided by the macro programmer            
         - View ribbon > Macros section > View Macros command            
        (or use the shortcut Alt-F8)            
         - Click on a macro, then click the Run button            
         - To view the code for a macro in the VBA editor, use either of            
        the methods above to go to View Macros dialogue box            
         - Click on a macro, then click the Edit button            
        (or use the shortcut Alt-F11)            
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
        Monday, October 24, 2016            
                     
  14.                  
    Freeze Panes & Scrolling Panes            
     - This allows you to keep rows at the top of the worksheet            
    visible when you scroll down            
     - It also allows you to keep columns on the left visible            
    when you scroll to the right            
     - Or both at the same time            
     - Where is Freeze Panes in the ribbon structure?            
       - Go to the View tab            
       - Look in the Window section (on the right)            
       - Click on the Freeze Panes icon.  You'll see 3 commands.            
        1. Freeze Panes            
        2. Freeze Top Row*            
        3. Freeze First Column*            
        * You won't need commands 2 & 3 after this exercise            
     - Let's practice Freeze Panes            
        Freeze Panes   LINK      
     - Now, lets discuss Scrolling Panes            
       - This is also known as the Split command            
                     
  15.                  
    Using Excel's built-in functions, part 2            
     - Let's practice writing some formulas            
        Prior to using Insert Function   LINK      
                     
  16.                  
    Using Excel's built-in functions, part 3            
     - The "Insert Function" tool helps you find and write functions            
       - This is the fx icon to the left of the formula bar            
        Examples of functions   LINK      
                     
  17.                  
    Terminology & Concepts Review (cont'd)            
     - Once again we will download this file to the Documents folder            
       - If you see a gold area at the top, click the "Enable Editing" button            
     - Here is the file we will review            
        Terminology & Concepts Review from 10/17/16   LINK      
                     
    Home Work            
                     
    5. Practice searching for & writing functions using the "Insert             
    Function" tool            
        Examples of functions   LINK      
      a.  In this workbook go to the third worksheet ("Some helpful hints")            
      b. Click in one of the green cells.  Notice the "PHRASE TO SEARCH"            
      below that cell.            
      c. Click the fx to the left of the Formula Bar            
      d. Use the phrase in the search box            
      e. Select a function from the list at the bottom, and click OK            
      f. In the "Function Arguments" fill in each argument box, and click OK            
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
        Monday, October 17, 2016            
                     
  11.                  
    A pre-introduction to Pivot Tables (cont'd)            
     - This lecture traces Bob's journey to figure out pivot tables            
        A Pre-introduction to Pivot Tables   LINK      
                     
  12.                  
    Pivot table time!            
     - Let's jump into Excel's pivot table functionality            
       - The purpose of this exercise is to allow us to start getting used to the            
      pivot table user interface            
        Introduction to Pivot Tables   LINK      
                     
  13.                  
    Terminology & Concepts Review (cont'd)            
     - Once again we will download this file to the Documents folder            
       - If you see a gold area at the top, click the "Enable Editing" button            
     - Here is the file we will review            
        Terminology & Concepts Review from 10/10/16   LINK      
                     
    Home Work            
                     
    4. Practice the pivot table exercises that we practiced in class            
     - The purpose of this exercise is to allow you to start getting used             
    to the pivot table user interface            
        Introduction to Pivot Tables   LINK      
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
        Monday, October 10, 2016            
                     
        WARM-UP & QUESTIONS            
                     
    Here was your last HW opportunity            
     - Collect some data from people (age in years & height in inches),             
    and make a chart out of the data            
       - How did it go?            
       - Do you want to share what you did?            
       - Did doing this HW spark some questions?            
                     
                     
  9.                  
    Terminology & Concepts Review (cont'd)            
     - Once again we will download this file to the Documents folder            
       - If you see a gold area at the top, click the "Enable Editing" button            
     - Here is the file we will review            
        Terminology & Concepts Review from 9/26/16   LINK      
                     
  10.                  
    Using Excel's built-in functions, part 1            
     - What is a function?            
       - A function is a calculation procedure that produces a single value            
     - How does a function work?            
       - A function takes some input "ingredients" and its calculation            
      procedure to compute the final value.  We call the inputs            
      "arguments".            
        Functions and Arguments   LINK      
                     
  11.                  
    A pre-introduction to Pivot Tables            
     - It took Bob a long time to wrap his head around pivot tables            
       - What does the word pivot mean?            
       - What is a multi-dimensional dataset?            
       - Why can't a pivot table calculate everything a spreadsheet can?            
       - How do you know when a pivot table will be useful?            
     - This lecture traces Bob's journey to figure out pivot tables            
        A Pre-introduction to Pivot Tables   LINK      
                     
        Monday, September 26, 2016            
                     
        REMINDER            
        We have no class next week (Oct. 3)            
        We will resume the following week (Oct. 10)            
                     
  7.                  
    Why is "Save As..." a very important function?            
     - There are three ways to save a file you are working on            
       - When should you use each one?            
        IMPORTANT IDEA: Save as… (versus Save or the X)   LINK      
                     
  8.                  
    Charts in Excel            
     - Let's do a basic chart in Excel            
       - Note: Charts are complex and have many features.  It takes time to             
      learn all the features.  You can expect a lot of trial-and-error learning.          
        Basic Chart Exercise   LINK      
                     
    Home Work            
                     
    2. Make an Excel chart            
     - Collect some data            
       - Ask people around you their age in years & their height in inches            
     - Make a table with these headings: AGE, HEIGHT            
     - Make a chart out of the data in this table            
      Optional: play with some of the chart options            
     - Example:            
     
 
             
                     
                     
                     
                     
                     
                     
                     
                     
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
        Monday, September 19, 2016            
                     
  4.                  
    Feedback from last week's Excel usage survey            
     - I compiled the responses you submitted            
       - Here is the result            
        EXCEL INTERESTS FROM THIS CLASS            
     - This is one way I fine-tune the course.  But that is an ongoing            
    conversation.            
       - You still have plenty of opportunities to communicate requests,            
      feedback and questions            
                     
  5.                  
    About doing HW in Bob's classes            
     - Did you know...            
       - that I welcome students to class even if they did not do the HW?            
       - that, in my class, HW does not go stale.  So, you can do it "late"?            
       - that doing HW does not mean getting the "right" answer?            
       - that coming to class with a question about something that is not clear            
      clear about the HW is a completely legitimate result of doing HW?            
                     
  6.                  
    Terminology & Concepts Review (cont'd)            
     - Once again we will download this file to the Documents folder            
                     
      1. Use Internet Explorer to view Bob's web page            
         - Click the Start button and type "internet explorer"            
      2. Click on the hyperlink to a class file (see below)            
      3. In Internet Explorer: use "Save As" (not "Open" or "Save")            
      4. Save it to the "Documents" folder (not the library called Documents)            
        a. Click on the leftmost arrow in the "path" of the File Explorer            
        inside the Save As window            
        b. Go to "Desktop"            
        c. Click on the arrow after Desktop in the path and go to the            
        profile folder (man sticking his head in a folder)            
        d. Click on the arrow after the profile folder in the path and go              
        to the Documents folder            
        e. Click the Save button in the Save As window            
      5. Use File Explorer to open the folder that contains the file            
         - Start File Explorer, and follow navigation steps a to d,  above.            
      6. Find the file, and double-click it to open it            
      7. If you see a gold area at the top, click the "Enable Editing" button            
                     
     - Here is the file we will review            
        Terminology & Concepts Review   LINK      
                     
    Home Work            
                     
    1. Terminology & Concepts Review            
      A. Download this file to the Documents folder            
        Terminology & Concepts Review   LINK      
      B. Tell Bob what parts of it you don't fully understand            
         - An important result of HW is coming to class with questions!            
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK      
                     
                     
        Monday, September 12, 2016            
                     
  1.                  
    Getting started            
     - Course outline & grading procedure            
        Course outline   LINK      
       - 80% attendance + complete 50% of homework = you pass!            
     - Tour of this web site            
       - Open all day and night for you to explore            
         - Easy web address: nahlej.net            
                     
  2.                  
    Student strengths and learning interests            
     - In this class, the curriculum is tailored to the students            
     - There are two ways that this can be done            
      1. A quick assessment of your Excel background            
        Student Background Knowledge   LINK      
      2. Having a conversation            
         - Provide Bob with requests or feedback            
         - In-person            
         - Or by email ( nahlej@nahlej.net )            
                     
  3.                  
    How to download files from this web site            
     - Goal: Use "Save As" to download a file to the Documents            
    folder            
      1. Use Internet Explorer to view Bob's web page            
         - Click the Start button and type "internet explorer"            
      2. Click on the hyperlink to a class file            
         - NOTE: Do this for a file stored on the web site by clicking on a            
        hyperlink.  Do not try to save the entire web page.            
      3. In Internet Explorer: use "Save As" (not "Open" or "Save")            
      4. Save it to the "Documents" folder (not the library called Documents)            
        a. Click on the leftmost arrow in the "path" of the File Explorer            
        of Save As            
        b. Go to "Desktop"            
        c. Click on the arrow after Desktop in the path and go to the            
        profile folder (man sticking his head in a folder)            
        d. Click on the arrow after the profile folder in the path and go              
        to the Documents folder            
        e. Click the Save button in the Save As window            
      5. Use File Explorer to open the folder that contains the file            
         - Start File Explorer.  Then follow navigation steps a to d,  above.            
      6. Find the file, and double-click it to open it            
      7. If you see a gold area at the top, click the "Enable Editing" button            
     - Example            
        Terminology & Concepts Review   LINK      
                     
    Home Work            
                     
    1. Terminology & Concepts Review            
      A. Download this file to My Documents            
        Terminology & Concepts Review   LINK      
      B. Tell Bob what parts of it you don't fully understand            
         - An important result of HW is coming to class with questions!            
                     
                     
                     
    LOG YOUR COMPLETION OF EACH ASSIGNMENT   LINK