How many unique formulas exist in this budget demo?
What is a unique formula in an Excel workbook:
Financial models in Excel should be audited by the author or a 3rd party to ensure robustness. Excel auditing work is greatly simplified, if unique formulas are verified only, instead of checking every single formula in the model, assuming unique formulas have been copied correctly across ranges.
How can unique formulas be detected programmatically?
It is quite difficult to track unique formulas across a workbook using the common 'A1 notation' for referring to cells. In Excel 2007 or later, the A1 style consists of 1 to 3 case insensitive letters that identify the column (A to XFD), followed by a row number (1 to 1,048,576). Absolute references are indicated with a $ dollar sign in front of the row and/or column.
However, there is a second alternative style of notation called R1C1. This consists of the letter R and the row number (1 to 1,048,576), plus the letter C and the column number (1 to 16,384). Relative references are indicated by enclosing the row or column number in square brackets.
- Any top-left formula, which has been copied to cells below and/or to the right using a relative reference.
- Any formula with absolute references, which has been copied as is to any other cell(s).
- Any formula with mixed references, given that the source formula absolute references remain unchanged, while the formula is copied below and/or to the right.
Financial models in Excel should be audited by the author or a 3rd party to ensure robustness. Excel auditing work is greatly simplified, if unique formulas are verified only, instead of checking every single formula in the model, assuming unique formulas have been copied correctly across ranges.
How can unique formulas be detected programmatically?
It is quite difficult to track unique formulas across a workbook using the common 'A1 notation' for referring to cells. In Excel 2007 or later, the A1 style consists of 1 to 3 case insensitive letters that identify the column (A to XFD), followed by a row number (1 to 1,048,576). Absolute references are indicated with a $ dollar sign in front of the row and/or column.
However, there is a second alternative style of notation called R1C1. This consists of the letter R and the row number (1 to 1,048,576), plus the letter C and the column number (1 to 16,384). Relative references are indicated by enclosing the row or column number in square brackets.
Formulas shown in A1 notation - click image to zoom
Formulas shown in R1C1 notation. Unique formulas are easily identified. Click image to zoom
Formula in cell B4 is unique within the B4:G8 range
Unique Formulas in R1C1 & A1 notation
