Excel formatting

excel

beginner

Excel for beginners

Published

June 4, 2025

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:

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

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 formulas as much as possible today
you might find the list of Excel shortcuts helpful too

Previous attendees have said…

83 previous attendees have left feedback
98% would recommend this session to a colleague
96% said that this session was pitched correctly

Three random comments from previous attendees

Excellent session with great tips to take away.
Amazing session very clear and informative
Good light hearted session, easily explained and easy for me to understand! Although I knew some of the information shared, I still picked up some useful tips and hints. Thank you Brendan

Session materials

all materials
slides html / pdf

Session outline

(thanks Deborah Calvin, Brian Orpin, Michael Roarty, Kenneth Mack, Catherine McGrenera, June Livey, Catriona Scott)

accessibility
formatting cells
formatting values (aka number formatting)
formatting vs other things
shortcuts and tips
an introduction to conditional formatting

Accessibility

many of us will have legal duties to make parts of our services accessible
- e.g. The Public Sector Bodies (Websites and Mobile Applications) Accessibility Regulations 2018
there are useful standards for making sense of accessibility

Excel is a mixed bag for accessibility

it is easy to make non-accessible documents in Excel
- merged and split cells
- inconsistent use of tables
- poor column headings, sheet names, etc
- unlabelled charts
- low-contrast themes
- meaning conveyed by colour and fonts
there is a built-in accessibility checker in some versions of Excel, which you might find useful

Formatting cells

please create a new Excel workbook
you can format each cell individually via the right click menu and Format cells (or by the Ctrl + 1 shortcut)

Format cells dialogue

you can fill cells with custom fonts, alignments, colours, patterns, and gradients (via Fill Effects...)
you can add borders to individual cells

Formatting many cells

this is fine for a few cells, but gets boring (and inconsistent) if you have lots of styling to do
you can select several cells, and access the Format cells dialogue to style them together
or you can select a cell with a theme you want to copy, and use the Format Painter tool to spread that format about the place

Theming

you can also theme your whole workbook
take care, though, as many of these themes are not accessible and often produce hard-to-read results
and lots of ugly clunky stuff to find (e.g. default font changes)
on balance, better to avoid themes unless you’re certain they’ll be of benefit for you

Help! I hate my formatting!

Clear > Clear Formats (or Alt, H, E, F)
note this doesn’t clear out all the formatting from themes - especially odd fonts etc
also worth having the Paste as Values trick in your back pocket for when things go horribly wrong

Number formatting

number formatting is taking a value, and changing its meaning with formatting
number formatting is how dates work (or fail to work) in Excel
dates are v. important, so we’ll concentrate on them in this part of the session
you’ll also encounter number formatting when working with…
- money
- percentages
- decimal places
- …

Date formatting

we’ll take a number, and apply special formatting to it to make it look like a date
- so 45483 will become 10/07/2024
Excel dates are stored as the number of days since Jan 1st 1900
you can prove this to yourself by typing 1 into a cell, then formatting it as a date using the number formatting menu:
you can also format as a date via the Format Cells dialogue (Ctrl + 1)

Don’t try and cheat with dates!

date formatting is one of the commonest pain points in Excel
dates are extremely complicated, and we often want to calculate with them
the advice is simple: dates should always be stored as these numbers, and then formatted to look like dates
- don’t try to cheat by writing dates as text
- use formatting to produce your preferred date format in the Format Cells tools instead

Formatting vs other things

(or, keep your sheets clean)

it’s common to see Excel sheets that contain lots of extraneous bits
- headers, copyright info, explanations…
ideally, all this extra stuff should be kept well away from your data, as it makes it harder to process that data safely
…and you should definitely be using tables to help with this

Conditional formatting

this can get complicated, and we’ll revisit more fancy conditional formatting in the intermediate training sessions
the big idea is that conditional formatting alters the format as values change

Progress bars

Put a few random numbers in a column
Select them, and go to the Conditional Formatting menu
Select Data Bars

Duplicate values

Now clear that conditional formatting
Add a duplicate value to your numbers

Duplicate values

Highlight duplicate values

Red-Amber-Green reporting

RAG reporting with icon sets

Key shortcuts

Ctrl + 1 for the Format Cells dialogue
Alt, H, E, F to clear formats
Ctrl + v, Ctrl, v to paste values only

Formatting tips

Accessibility is much more important than beauty
Use number formatting for dates, currency, percentages
Consider using validation to make sure that cells only contain what you expect them to contain
Consider locking cells to protect formatting when that formatting is really important
Use conditional formatting sparingly