Excel formulas

This session is part of our Excel for beginners course. That’s a series of six linked sessions, delivered on Teams, that give an introduction to Excel for people working in health and social care. The sessions are:

• Excel first steps
• Tidy data in Excel
• References and names in Excel
• Excel formatting
• Excel tables
• Excel formulas (this session)

Together, they aim to help you develop an appropriate set of Excel skills to help your work. This session covers formulas in Excel:

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
• you might find the list of Excel shortcuts helpful too

Previous attendees have said…

• 21 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

• I thought the session for very structured and informative, wish I could have made it to more of them
• Really good beginner session
• short sharp straight to the point

Session materials

• all materials
• slides html / pdf
• sample file .xlsx

Session outline

• values and formulas
• simple functions
• references in formulas

Values and formulas

• cells in Excel contain either values, or formulas
• we’ll start with some data derived from NRS 2022 population estimates
• make sure you have the sample data available

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
• or you can use the \(fx\) button by the formula bar

Function arguments

• adding a function by \(fx\) or the menu brings up the 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

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”