Excel formulas

excel
beginner
Published

July 3, 2024

Session materials

Previous attendees have said…

  • 14 previous attendees have left feedback
  • 100% would recommend this session to a colleague
  • 100% said that this session was pitched correctly

Three random comments from previous attendees
  • short sharp straight to the point
  • Great session, easy to follow. Thanks Brendan
  • Really useful for going over basic concepts that I haven’t studied in a long time. So it was great for relearning the basic building blocks of how Excel works. Appropriate level for beginners session.

Where does this fit in?

KIND Excel beginner skill tree
  • for this session, you’ll need to be familiar with the Excel basics (getting around in Excel, opening/saving/closing files)
  • you’ll also need to be familiar with A1 referencing, values, and tables
  • we’re going to avoid talking about formatting as much as possible today

Session outline

  • values and formulas
  • simple functions
  • references in formulas

Values and formulas

Values

Example value, showing identical cell contents and formula bar contents
  • when you enter some data in a cell, we call that a value
  • values look the same in their cell as in the formula bar

Formulas

  • we can also make a value using a formula
  • Excel evaluates/calculates the formula, then displays the result as a value
  • in an empty cell, try entering = 2 + 2

Example formula, showing different cell contents and formula bar contents

Example formulas

  • we can do ordinary arithmetic in formulas /, *, -, +…
  • you can also try & which joins values
    • = "This number is " & 5

Functions

  • But most formulas use functions, which are like the verbs of Excel
  • Try = SUM(2, 2)

Adding functions

  • you can type functions in by name (as above)

  • you can also add from the Formulas area of the ribbon
    add from the Formulas area of the ribbon

  • or you can use the \(fx\) button by the formula bar
    use the fx button by the formula bar

Function arguments

  • adding a function by \(fx\) or the menu brings up the Function Arguments interface
    Function Arguments interface

  • This is a helpful builder for more complicated functions

What are arguments?

  • arguments are the values we supply to a function

    • in =SUM(2,3), the arguments are 2 and 3

  • we separate arguments with a comma ,

  • we need to be sure we’re supplying the right arguments in the right order (this gets described as the syntax of a function)

  • we also, while we’re on the jargon, say that functions return a value so = SUM(4,3) will return 7

Useful simple functions

  • in the same way as =SUM(), you can try:
    • AVERAGE
    • MIN and MAX
    • SUBSTITUTE
    • CEILING
    • TODAY
    • ROUND

References in formulas

  • mostly, you won’t add values directly into your formulas
  • instead, you’ll bring them in from another part of your spreadsheet
  • can you use SUM() to make a total Male + Female column in the supplied data?
    • add a header in cell G2
    • if needed, extend the table to include your new column
    • in G3, enter the formula = SUM(C3, D3)
    • then copy down the rows by double-clicking the green fill handle green fill handle

Practice

  • calculate what proportion of your total population is under 16 (thanks, Lara Paterson (NHS Lanarkshire))
  • estimate the population density
  • find an average area for each council area

Troubleshooting functions

  • #NAME? = “I don’t recognise that function name”
  • #VALUE! = “Your syntax has gone wrong - usually in the wrong order”
  • #REF! = “I don’t recognise that reference”
  • #DIV/0! = “You’ve divided by zero”