Is there a shorthand to refer to “all cells in a column after A3”?
No.
However, there are techniques for making dynamic references go “all the way down”.
-
OFFSET() can take vertical and horizontal size parameters.
-
=ROWS(A:A) is perhaps easier to remember than 1048576, and will return max rows also in spreadsheet software with a different row limit.
- Row number of a certain cell is returned by
ROW(< *cell reference* >).
Together, the above functions enables creation of formulas with “variable scope”.
Would restricting conditional formatting to an expected number of cells you’re dealing with as opposed to extending to the rest of the spreadsheet result in any noticeable performance improvement?
Not sure.
Recalculation should not happen for cells not impacted by a change, so after initial formatting it should not be noticeable when most of the formatted cells are empty. I have done this on a fairly large scale, but not with very complex format conditions, and I did not notice any performance impact.
Note that inserting cells or pasting from other cells will most likely override existing formatting, so trusting the “format all” practice for consistency is perhaps not the best strategy. For data integrity purposes, spreadsheet software is not the best choice.
Is it good practice to default to using relative reference and use absolute reference as necessary or the other way around for most typical workflows/data?
No.
It is good practice to always understand what kind of data relationships you are working with, and address it accordingly.
- When your formulas are in a table and reference other elements of the same table, relative references are usually fine.
- When your formulas reference data from a table from outside the table, absolute references may be the way to go. Most likely you should use some kind of lookup to the table range, which should be referenced with absolute address. A named range is very useful.
- When your formulas reference constants, absolute references are usually fine.
- In a significant number of cases, mixed references will be required.
- In many cases, in particular when data is routinely reorganized (sorting, filtering, pivot tables), you need to use lookup/redirection functions.
Those are “best fit” guides, as far as I can provide. There will be exceptions. There is no fixed and settled answer for every computation model. You need to think for yourself. No safe shortcuts. Sorry!
