Authoritative Excel range syntax reference

Question

Sorry if this has been asked before but I can't find it. I am looking for an authoritative description of all valid strings that can be used as a reference, e.g., "A1:C5", "$A:$A", $A2" etc etc. That seems a pretty basic thing yet I've wasted hours trying to locate it. All I can find is a swamp of "helpful" examples but no reference.

Answer 1

This seems like a fun exercise. I'm going to list as many as I can, and hopefully other people could point out ones that I've forgotten/missed/didn't know about.

A sort-of guide to what you're asking for, and a source for anyone looking for additional documentaion: http://www.excelfunctions.net/Excel-Reference-Styles.html

I'm going to start with 5 broad categories - A1, R1C1, Table, Formulas, and VBA references. I'm going to ignore other programs that can interact with Excel for the moment (Although I might add in Python if that's officially added in)

All examples will be using A1 reference style, since they're more intuitive for an inexperienced user.

A1 Reference type:

A1 style tells us the coordinates of a given cell. The alphanumeric portion tells us which column we're in, while the numeric portion tells us which row we're in.

Cell References:

• A1 - The basic cell reference. References the cell A1, and as you drag formulas through columns and rows, it'll change. For example, if your formula is in C1, and you move it to D2, it'll now reference B2.

  Good use: Comparing two sheets to each other. ='Sheet1'!A1='Sheet2'!A1 will compare the data in sheet 1 to sheet 2, and give a true/false if they match or not. Formula can easily be slid sideways and up/down for additional comparisons.

• $A1 - This locks the column. As you drag formulas, the row will change, but the column won't. Useful if you always want to reference the same column in a formula. For example, if your formula is in C1, and you move it to D2, it'll now reference $A2.

  Useful example: Formulas in helper columns. A1 = $C1*2 will neatly slide down without any issues, and if you move it over to the B column, it'll continue to reference C.

• A$1 - This locks the row. As you drag formulas, the column will change, but the row won't. Useful if you always want to reference the same row in a formula. For example, if your formula is in C1, and you move it to D2, it'll now reference B$1.

• $A$1 - This locks the cell reference. No matter how you drag the formulas around, it'll continue to reference cell A1. For example, if your formula is in C1, and you move it to D2, it'll now reference $A$1.

  Useful example: Constant multiplier for all numbers.

To summarize, A1 References are broken out as follows:

• Alphanumeric portion - What column we're in
• Numeric portion - What row we're in
• $ - Locking the portion immediately following

Range References:

Due to the sheer number of combinations of range references, I'm sicking to the most common. Mixing and matching types of cell references with types of range references will get every combination.

• A1:B2 - References the grid of cells with A1 being at the top-left, and B2 being at the bottom-right. Both parts of the formula will slide when moved. It's generally recommended not to have unlocked references in formulas applied over multiple cells, since the reference range will also move. For example, if you have =Sum(A1:B2) in C1, and you move it to D2, it will transform to =Sum(B2:C3).

  Unlocked range references most often cause problems in vlookup formulas, where the reference range ends up changing as people slide the formula down.

• $A$1:$B$2 - References the grid of cells with A1 being at the top-left, and B2 being at the bottom-right. This range reference won't change, even when moved. For example, if you have =Sum($A$1:$B$2) in C1, and you move it to D2, it will transform to =Sum($A$1:$B$2)

• $A$1:A2 - This locks the first cell, but leaves the second part of the reference flexible. This is very useful when you want to see "Everything that's happened so far"- For example, if you're numbering a list, when combined with =countifs ("What instance of occurrence is this one?") For example, if you have =Sum($A$1:A2) in C1, and you move it to D2, it will transform to =Sum($A$1:B3)

  Similar effects can be used with $A$1:B1 going horizontally.

• A:A - This gives the entire column A. Since this is unlocked, it'll slide. Useful for grabbing everything in a given column. For example, if you have =Sum(A:A) in C1, and you move it to D2, it will transform to =Sum(B:B)

• $A:A - This gives the entire column A. This will expand as you go across to grab more columns. I can't think of an immediate practical use for it. For example, if you have =Sum($A:A) in C1, and you move it to D2, it will transform to =Sum($A:B)

• $A:$A - This gives the entire column A. This is locked to column A, even if you move the formulas around. For example, if you have =Sum($A:$A) in C1, and you move it to D2, it will stay as =Sum($A:$A)

Slightly lesser known are rows:

• 1:1 - The entire first row. Unlocked. For example, if you have =Sum(1:1) in C2, and you move it to D3, it will transform to =Sum(2:2)

• $1:1 - The entire first row. Partially locked. For example, if you have =Sum($1:1) in C2, and you move it to D3, it will transform to =Sum($1:2)

• $1:$1 - The entire first row. Completely locked. For example, if you have =Sum($1:$1) in C2, and you move it to D3, it will stay as =Sum($1:$1)

R1C1 References

R1C1 is more of a reference style as opposed to a coordinate style. This can be extremely useful, since your formulas in all of your cells look exactly the same, and makes entering sliding formulas in VBA significantly easier.

R1C2 is broken down as such:

• R - Rows
• 1 - Row 1 OR
  [1] - We're 1 row down from our current cell
• C - Columns
• 1 - column 1 OR
  [1] - We're 1 column over from our current cell

In other words, if you don't have brackets, you're referencing the cell or column in question. If you do have brackets, you have a relative reference.

Cell References:

• R3C7 is an absolute reference - 3rd row, 7th column. This would be the same as saying $G$3 in A1 style, as detailed above.

• R[3]C7 is partially absolute, partially relative reference. This is asking for "Give me the cell 3 rows down from the current cell, in column 7" (Which is column G) If I have this in cell A1, I'd be referencing cell G4. If I move it to cell B2, I'd be referencing cell G5.

• R[2]C[-2] is a full relative reference. You can also reference earlier columns or rows, as indicated by the - sign. If I had this formula in cell C1, it would be referencing cell A3. If I move the formula to D2, I would now be referencing cell B4.

Range references:

Table References

Tables make referencing other cells and ranges easy and intuitive. They're broken out as follows:

• TableName[ColumnName]

Formula References

VBA References

VBA Conversion from Letter to Column

Something that's come up a few times is changing column numbers to column letters. I found this very helpful piece of code (somewhere on Stack Overflow, I don't currently have the link handy) to convert column numbers to column letters for ease of use in VBA.

Function Col_Letter(lngCol As Long) As String
  'Converts a number (usually generated from an application.match function) to a letter. For example 1 turns into A, 5 turns into E, etc.
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function  

Under Construction*