Scope of the possible with Excel

No feedback found for this session

Welcome

• this session is a non-technical overview designed for service leads

Session outline

• Why Excel, and why this session?
• Strengths and weaknesses
• Great Excel projects, dire Excel projects
• Alternatives
• Skill development

Why Excel?

• 40+ years old
• initially developed as a finance tool
• uptake from financial areas outwards into other services
• likely circular reasoning: “we use Excel” because “we use Excel”…

Why this session?

• now includes several different tools for working with data
• most of us only ever scratch the surface
• but it’s really important to know roughly what’s possible - and that’s hard!

Excel demo

• using open Annual inpatient/daycase data

Strengths and weaknesses

• great for one-off data projects, but Excel sheets often have a long life…
• easy to start projects, but more involved analysis is messy
• share-able and accessible - but many different versions of Excel, add-ins etc, mean that there is grit in the system
• you can do almost anything in Excel, but you probably shouldn’t

Great Excel uses

• simple, quick, one-off projects
• using Power Query to manipulate data
• using Excel to create/load data into Power BI

Terrible Excel uses

• replacing databases (complicated, but Access/SQL)
• anything with free text (probably use R/Nvivo instead)
• anything with many editors (use Forms or Sharepoint Lists instead)
• anything with repetition and extensive manual editing (better Excel design needed)
• very wide data (probably Excel redesign again)

A cautionary tale

• there are staff in Scotland’s health and care sector who spend ~100% of their time manually editing Excel files
• this is almost certainly the result of poor Excel practices
• Excel is a complex product, with several routes to achieving end goals
• and Excel can malfunction to disasterous effect

Five top tips

• keep data tidy (one value per cell, one row per thing, one column per measurement)
• keep things as simple as possible - but not simpler
• don’t tolerate lots of manual spreadsheet work
• take care with formatting and accessibility/reuse
• bet that your spreadsheet will last longer, and travel further, than you think

Chat, queries, questions

• Teams channel: tinyurl.com/kindnetwork
• weekly email update: tinyurl.com/kindupdate
• please let me know (brendan.clarke2@nhs.scot) what would be most helpful to you and your team