Excel first steps
excel
beginner
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
- Very straightforward, showing basic skills but not overwhelming. Good session.
- Brilliant just going back to basics
- enjoyed the session and exactly what I was wanting, excel from the very start
Session outline
A brief overview of Excel
- 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
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
Erefers to a vertical column7to 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…
- …😱
- dates usually go
- 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(orCtrl+T)
- click
OK
Lots of new things to see
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