A Quick Overview of Excel

• I would like to take a look at excel before we go any further.
  • We have been using excel as a tool to sort and filter data.
  • But there are many other things that it can do.
• Spreadsheets were invented in the early 60s
• General usage began in the late 70s early 80s when they were implemented for personal computers.
• Spreadsheets are composed of rows and columns which intersect at cells.
• Cells are the basic "computation" unit.
  • Named after column letter and row number in excel.
• A collection of cells is called a range
  • A rectangular region of the worksheet
  • Cell1:Cell2
  • A1:B5
• Cells contain
  • nothing
  • Data
  • Formulas/functions
• Entering things in a cell
  • If you are in the middle of entering something in a cell and don't press return, excel will not know you are not done entering the values.
  • In cell A2 press =
  • then move around, note that few of the buttons on the ribon are active.
  • Press enter to complete the data entry and the tabs become active again.
• Data
  • Text
    • Text is a collection of characters
    • Usually not just digits (that would be a number)
    • Sometimes in quotation marks
    • I will also refer to text as a string
    • Text is generally left aligned.
    • You can test for text with the istext() function
      • Start excel
      • In cell A1 put : Data
      • In cell B1 put : Test type
      • In cell C1 put : Result
      • In cell A2 put the letter a
      • In cell B2: put istext
      • In cell C2: put =istext(a2)
      • 
      • The first image shows the result of the computation
      • The second shows the data and formulas.
    • Showing formulas is one way to debug worksheets.
      • Formulas tab, Formula Auditing command group the Show Formulas button
      • 
      • Or ctrl ~
    • Try some other values in A2
      • hello
      • a
      • 123
      • =4+4
      • try a space
      • try no space
      • It is always safest to use clear contents
        • Right click in a selected cell or range
        • Select Clear Contents
        • 
        • Hometab Editing command group, Clear dropdown, select the type of clear
        • 
      • 35/5 (no equal sign)
      • =35/5
      • =today() (sure looks like text to me)
  • Numbers
    • Any form of numeric data
    • Integers, or what we call floating point data.
    • Usually right aligned.
    • Usually does not include letters
    • enter isnumber in B3
    • enter =isnumber(a2) in C3
    • Try some numbers and text in a2 again.
    • try =3+2
    • try ="123"
    • try =today()
      • There is no different date type.
      • Dates are just the number of seconds since some event called the epoch
        • in cell f1 put a 1
        • right click on this and select Format Cells
        • 
        • Select a date format from the menu.
        • 
    • The same is true for time
      • Enter =now() in cell F2
      • Format this as a number, with 10 decimal places
      • Enter a value in F3 (anything will do)
      • Notice F2 changes each time the worksheet recalculates.
      • What is happening here?
    • Formatting of cells can deceive us.
      • Especially when dates/times are involved.
    • You should always format numbers in an appropriate way.
      • Right aligned to avoid confusion
      • With commas when appropriate
      • limit decimal places when appropriate
      • As percentages when it is a percentage
      • As currency when it is currency
      • As date/time when it is a date or time.
      • Fractions are tricky, don't use them unless you have spent some time looking at the format.
      • Cells containing ##### are just too narrow, widen them.
  • Logical values
    • Logical values are true or false.
    • I might sometimes call these booleans.
    • We are using logicals with the =is* functions
    • enter islogical in B4
    • enter =islogical(a2) in C4
    • In A2 try
      • true
      • false
      • =not(true)
      • =not(false)
      • =and(true, false)
      • =3=4
      • =5>2
    • WE normally don't store bools in cells
  • computations
    • This is the major strength of excel.
    • It will compute values for you.
    • But you do need to know what you want to do.
    • Computations can be broken into three basic types
      • Formulas
      • Functions
      • Mixture of the two.
    • all start with an equal sign
    • enter isformula in B5
    • enter =isformula(a2) in C5
    • There can be errors in formulas
    • enter iserror in B6
    • enter =iserror(a2) in C6
    • I forgot blanks earlier
    • enter isblank in B7
    • enter =isblank(a2) in C7
    • Formulas
      • = 22/7
      • = 4^2
      • = 22/7*(4^2)
      • = 99/0
      • I use parenthesis even when not required for clarity
      • remember your dear auntie sally (PEMDAS)
      • Formulas should be nice, but they should be your last resort.
      • You should prefer functions
    • Functions
      • Functions are pre built computations.
      • There are many of these.
      • We have been using them throughout the exercise
      • =word(parameters)
      • Parameters are the input to the function
      • Try
        • = pi()
        • = power(4,2)
        • = date(2019,10,7)
      • you should prefer functions when you know them.
        • Functions will be debugged by professionals as opposed to your formulas
        • Functions will consider special conditions and edge cases
        • Functions will be optimized for performance
      • We will spend MUCH more time discussing functions
      • Functions in this class
        • You are responsible for the functions we discuss
        • Especially the ones we discuss in detail
        • you don't need to memorize them,
        • But you do need to know them.
        • Notice
          • The function helper when you are typing a function
          • 
    • Mixture just a mix of formulas and functions
      • Try =pi()*power(4,2)
    • We will spend time working on computations