Microsoft Office Tutorials and References
In Depth Information
USE GET.CELL TO HIGHLIGHT NON-FORMULA CELLS
To highlight every cell that does not contain a formula, use =NOT(HasFormula)
in the conditional formatting dialog.
Massive Gotcha: You cannot copy any cells that contain this formula to a
different worksheet without risking an Excel crash.
Breaking It Down: While most people typically use A1-style references, the
R1C1-style reference works better in the INDIRECT function. Normally, an
R1C1-style reference points to another cell. For example, =RC[-2] refers
to the current row and two cells to the left of the current cell. =RC
refers to 10 rows below and 3 columns to the right of the current cell. An R1C1
formula without any modiﬁ ers, =RC, refers to the current cell. This is a case
where an R1C1 formula is far simpler than the equivalent A1 formula, =ADDRE
Alternate Strategy: The advantage of using the method described above is that
the formatting will automatically update whenever someone changes a cell to
contain either a formula or a constant. If you simply need to get a snapshot of
which cells contain formulas, follow these steps:
Select all cells by pressing Ctrl+A.
Press Ctrl+G to display the Go To dialog.
Click the Special button in the lower-left corner of the Go To dialog.
In the Go To Special dialog, choose Formulas and click OK.
Choose a color from the Paint Bucket icon.
Additional Details: The complete list of GET.CELL arguments follows. Note
that in some cases, functionality has changed signiﬁ cantly, and the argument
may no longer return valid values.
Absolute reference of the upper-left cell in reference, as text in the
current workspace reference style (usually A1 style , but it might be
R1C1 style if someone has chosen R1C1 style in their Excel Options
Row number of the top cell in the reference.
Column number of the leftmost cell in the reference.
Same as TYPE (reference).
Contents of the reference.
Formula in the reference, as text, in either A1 or R1C1 style, depending
on the workspace setting.
Number format of the cell, as text (for example, “m/d/yy” or “General”).
Number indicating the cell’s horizontal alignment:
1 = General
2 = Left