References and names in Excel (Excel for beginners session 3)

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 (this session)
• Excel formatting
• Excel tables
• Excel formulas

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

Skills tree for beginner-level Excel

• you might find the list of Excel shortcuts helpful too

Previous attendees have said…

• 38 previous attendees have left feedback
• 97% would recommend this session to a colleague
• 92% said that this session was pitched correctly

NoteThree random comments from previous attendees

• I consider myself intermediate in excel however this session was really helpful in relation to basic areas I wasn’t aware of such as the absolute reference as I’m pretty much self taught! I found Brendan’s teaching methods really good and engaging thank you!
• Didn’t feel this was aimed at beginner level, an explanation at the start of why we do referencing would have been beneficial as I was quite lost as it went straight into referencing cells.
• Really helpful practical session. Safe environment to ask questions and ensure everyone is on track before moving on. Training hand outs really help when the sessions are not recorded. Have already recommended to colleague. Thank you :)

TipForthcoming session(s)

Booking link	Date
References and names in Excel (Excel for beginners session 3)	12:30-14:00 Wed 17th September 2025
References and names in Excel (Excel for beginners session 3)	10:00-11:30 Thu 20th November 2025

Video overview

Some sample data

user	pre-training score	post-training score	session
Steve	4	6	Excel 1
Emma	7	6	Excel 2
Bhavin	3	9	Excel 1

References

• Excel sheets are made up of cells
• cells are arranged in rows and columns
  • columns have letters - like C
  • rows have numbers - like 3
• references are the ‘address’ of a particular cell - like C3
  • you can ‘point’ at a cell by using its reference

Finding a reference

• from the name box:
• or work out from the column and row labels
  • always column letter, then row number
  • C3, not 3C

Using a reference

• we can write a formula that copies the value from C3 into another cell
• formulas start with an =
• so we’ll use = C3 to get Emma’s visit date (or whatever value you referenced)

References in formulas

• we use references to allow us to pass data around our spreadsheet
• an example: how much did Emma’s score improve during the session?
• calculate this with = C3 - C2

References move

• by default, Excel references are relative
• when you move a reference, the cell that reference points to also moves
• it is possible to ‘fix’ references so that they not move
  • we call these absolute references
  • the $ fixes a reference, so $C$3 is absolute

You can name things

• almost everything in Excel can be named
• the easy way to start with that is using the name box
• names can replace references in formulas
• this is valuable when things get more complicated

You can use and manage names

• via the Name manager (Ctrl + F3)
• Go to (F5)

Practical

Using a reference

• we can write a formula that copies the value from C3 into another cell

Tip

• go to an empty cell, type = C3, and press Enter/⏎
• that cell should now have Emma’s post-training score (or whatever value you referenced)

References in formulas

• how much did people improve during the training session?

TipTask

• add a new column called improvement
• in the first row of that column, add the formula: = C2 - B2
  • that calculates the post-training score minus the pre-training score
• then copy/fill down

References move

TipTask

• try copying your new block of improvement cells around your sheet
• their value should change (almost certainly to a #VALUE error)
• now try clicking those cells to see where the updated reference points to

Absolute references

TipTask

• delete your copied blocks of cells from the last step
• now update your references by adding $ before the letter and number
  • so C2 becomes $C$2
  • you might prefer to press F4 instead
• now copy and paste those blocks of cells again. What’s different this time?
• please delete those new blocks of cells when you’re finished

You can name things

TipTask

• let’s average the post-training score column
• in a new cell (C5 perhaps?) add this formula:
  • =AVERAGE(C2, C3, C4)
• now name that new cell av_score and try using it in a formula
  • = INT(av_length) would round that to the nearest whole number

You can manage those names

TipTask

• find the Name manager (or press Ctrl + F3)
• try renaming your av_score cell
• try making a new named range from your three pre-training score cells
• try using the F5 Go to interface to navigate between your named cells