Excel first steps

Previous attendees have said…

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

Three random comments from previous attendees

• I am a regular user of excel, all completely self-taught, and have learnt some useful hints and tips today
• Good understanding of what excel doesn’t like (eg Day of week or space before date).
• Fantastic - Brendan takes his time to explain steps simply so it is easy to follow

Session outline

KIND Excel beginner’s skill tree

A brief overview of Excel

Excel might be the cement of the universe

• one of the oldest, and most widely used, pieces of desktop software
• ubiquitous in business, finance, health and social care…
• proprietary, closed-source, paid-for
• many versions

But Excel is badly used, on the whole

• PHE lost 16k COVID19+ results
• MI5 bugged 134 people they didn’t mean to
• JP Morgan 2012 had a manually-updated spreadsheet
  • divided by sum, rather than average
  • massive losses, $2 - $6 billion??

And most of us fall into Excel work…

• most of us haven’t had any data training
  • e.g. in KIND network (which is a very techy group) 75% didn’t have a formal qualification
• and the responsibility for doing that data work well falls on many of us
  • data work has become more prominent in lots of jobs
• but good data work is really important…

Good data helps…

• answering which/when questions
• seeing effects of changes
• comparing different areas
• looking at services over time
• …

One word of warning before the practical part

• there are lots of versions of Excel
• they all work slightly differently
• please don’t panic if what you see looks slightly different from what I’m showing you
• please ask if you’re not happy/confident!

Opening Excel (desktop)

• find Excel in your Windows Start menu
  
• you’ll need to create a new blank workbook
  

Opening Excel (web)

• usually at https://www.office.com/launch/excel
• you’ll need to create a new blank workbook
  

Interface

Excel interface

Central idea

• pieces of data are stored in cells
  • one value, one cell
• cells are arranged into rows and columns
  
• arranging data like this allows us to analyse it
• it’s not just Word on a grid

Save, close, open

• save your new workbook
• close it
• re-open it (possibly from recent files)

References

• every cell in Excel has a reference - like its address
• references look like this: E7
  
  • E refers to a vertical column
  • 7 to a horizontal row

Data entry

• let’s add some data…
• we can type into the cells
  
• that data appears in the formula bar too

An example: school holidays

• Here are some school holiday dates
• make three headings in row 1: name, start_date, end_date
  

Values

• let’s add some values from the school holiday dates
• name of holiday
• start date
• end date
  • dates usually go DD/MM/YYYY
  • as we’ll see, Excel does lots of helping when we enter dates…
  • …😱
• we’ll only need a few rows of data

name start_date end_date Mid-term holidays 14/10/2024 18/10/2024 In service day 21/10/2024 21/10/2024 St Andrew’s Day 02/12/2024 02/12/2024

Tidy data

• keep things consistent - spelling, case, formats, etc
  • one piece of data per cell
  • one instance per row (one school holiday per row)
  • one variable per column (a column of names, one of start dates…)

Tables

• we can convert this group of cells into a proper table
• click in one of your cells of data
  
• Insert > Table (or Ctrl + T)
  
• click OK
  

Lots of new things to see

Table features

Autofill

• we’ll add a formula to calculate how long our holidays are
• add a new column called days
• paste = 1 + (C2 - B2) into the first cell of that new column
  • and look out for our session on formulas…
• double-click the green fill handle to fill the rest of the column
  

Formatting

• we can change the way that our spreadsheet looks
• we can also change the way our values look

Cell formatting

• some examples
  
  • highlighting
  • bold
  • borders

Number formatting

• our dates are number formatted
• Excel dates are actually numbers (like 45583) formatted to look like dates
• 1900 dates!
• percentages, £, …

Conditional formatting

• we can also format based on values
  
• take care though - this can be both ugly, and inaccessible