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

I don't know if there is a list of formats, because there really are only 2: A1 format and R1C1 format. You're not actually specifying a range format when you use A1:C3 because the : is actually the range operator.

The variants of $A$1, $A1, A$1 are just that - variants, identifying that when copying the reference the coordinate before the $ should not change, but that otherwise the coordinate may change relative to the copy.

R1C1 is more complex, because it allows relativity. R3C2 is an absolute reference to Row 3, Column 2 (B3 in A1 notation), while the use of braces [] in an R1C1 reference indicates that it is relative to the current cell R[-2]C[1] from cell R3C2 would give R1C3 (C1).

Nearly forgot. There can also be a reference to cells in another worksheet. 'Sheet2'!IV256

I use

(((\w*)|(\'[^\']*\')|(\"[^\"]*\"))!)?\$?([a-z]{1,3})\$?(\d+) 

as a regexp for identifying cell references in the A1 format

Answer 2

The problem is that there are a variety of operators (Range, Intersect, implicit intersect etc ), functions (INDEX, OFFSET, CHOOSE, INDIRECT + user-defined functions), Defined Names and structured Table references etc that can all be de-referenced to provide a valid range reference. So to do a complete job means parsing arbitrary Excel formulae. And it also varies by Excel version.

If all you want to do is work with standard explicit range references the usual trick is to convert the string to R1C1 notation and work with that. The syntax for R1C1 references is reasonably well described in Excel documentation.

There is also a BNF description of Excel formulae available somewhere but I have mislaid the reference.

Answer 3

"it's used for identifying cell references that might need modification when inserting a new row or column in a worksheet"

If the rows/columns are inserted with Excel live (as opposed to hacking into the workbook using another application), any references will update to the new address automatically.