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 seven 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
  
• and a final practical review session to help you put your new skills to work

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

No feedback found for this session

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