Relative, absolute, mixed, structured, and R1C1 references in Excel

Welcome

• this session is for 🌶🌶 intermediate Excel users
• we’ll get going properly at 15.05
• you’ need Excel of some sort to follow along
• you’ll also need the exercise workbook
• if you can’t access the chat, you might need to join our Teams channel: tinyurl.com/kindnetwork
• you can find session materials at tinyurl.com/kindtrp

The KIND network

• a social learning space for staff working with knowledge, information, and data across health, social care, and housing in Scotland
• we offer social support, free training, mentoring, community events, …
• Teams channel / mailing list

Forthcoming Excel sessions

Session outline

• this is an intermediate-level review of referencing techniques in Excel
• it’s a technical session, so we’ll concentrate on the how of referencing
• A1 references
  • relative
  • absolute
  • mixed
• structured references
• R1C1

A1 references

• A1 references are the standard way that references are made in Excel
• A1 references are usually made up of a letter and a number (like A2, B56, …)
  • the letter stands for the column a cell is in
  • the number stands for the row
• we can use those references in formulas
  • =A1 will copy the values from A1 into the current cell
• you can also use the range operator (:) to select several values - like A1:A3

References

Absolute vs relative references

• by default, A1 references are relative
  • if the formula moves, the reference will change

Absolute vs relative references

• as you’ve seen, relative references can cause problems
• you can change relative references to absolute (=fixed) references using $
  • $A$1 refers to A1 no matter what
    • use this if you absolutely always want to refer to the same cell

Absolute vs relative references

Mixed references

• $A1 will be absolute in the column, but relative in the rows
• A$1 will be relative in the row, but absolute in the column
• take care with these - they’re pretty confusing (and we’ll look at a much better way of doing the same thing below)

Structured referencing

• most objects in Excel can be named - including cells, tables, and ranges
• structured referencing is a special case
  • Table[Column] syntax
  • shared across Power BI, PowerQuery…

Implicit intersection

• you can convert a structured reference to a column to refer to a single value
• that’s done using the implicit intersection operator [@col]
  • (if you’re using an older version of Excel, you might also see this written as [@[Service_duration]])
• if you pick reference locations with the mouse, this is how Excel will render your references

R1C1

• R1C1 gives an alternative way of referring to cells
• slightly more complicated, but more precise, and less confusing
• largely encountered in VBA, but available and helpful in standard Excel

R1C1

R1C1

• column letters (A, B, C,…) are replaced by numbers
• row numbers remain as row numbers
  • R1C1 = A1
  • R2C8 = H2

Types of R1C1 referencing

• three kinds of R1C1 reference:
  • absolute reference = specify a row and column number (R2C2)
  • relative reference by adding a numeric offset in square brackets (R2C[-6])
  • implicit intersection-like “give me this row’s column 2” by using a bare R/C (RC2)
• a much nicer way of doing mixed referencing

Why bother with R1C1?

• R1C1 adds a bit of complexity to simple formulas
• but it makes complicated formulas (and repeated formulas) much easier to understand
• this is a big help when working with more complex Excel documents - particularly if you’re debugging something that’s gone wrong

Why bother with R1C1?

• R1C1 gives the same formulas for the same work across different columns
• we have to work this out by hand with A1 referencing - the row number would be different each time
• this helps us understand quickly when and where a formula is repeated
• (apparently) faster with very complex sheets - but given that Excel benchmarking is a closed book to me, I’ve been unable to find any solid information about this anywhere.

Forthcoming Excel sessions

Feedback and resources

• please can I ask for some feedback - takes less than a minute, completely anonymous, helps people like you find the right training for them