References and names in Excel

excel
beginner
Excel for beginners
Published

May 7, 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 references and names in Excel:

Skills tree for beginner-level Excel

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

Three random comments from previous attendees
  • Enjoyed it, really useful thanks
  • I followed this fairly well although for me personally I like to see how you’d use the example in the real work situation. eg accountant adding up columns or where a name reference would be used etc hope that makes sense.
  • Good basic session on excel references

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:
    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
    References are relative
  • it is possible to ‘fix’ references
    • 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?
Task
  • 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

Task
  • 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
    where does the updated reference point to?

Absolute references

Task
  • 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

Task
  • 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

Task
  • find the Name manager (or press Ctrl + F3)
    Name manager
  • 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