Examples

All of these formulas can be entered directly as a formula where cell "A1" is the top left cell in your range of cells.
Please contact us if you have any useful formulas we can add to this page.

Formula Summary

1) Shading Alternate Rows

=MOD(ROW(),2)=0

2) Find Today's Date

A1=TODAY()

3) Dates Within a Period

4) Identifying Odd Numbers

=MOD(A1,2) = 1

5) Identifying Even Numbers

=MOD(A1,2) = 0

6) Find Current Month

=MONTH(A1)=MONTH(TODAY())

7) Shade any Blank Cells

=ISBLANK(A1)

8) Largest Value

=MAX($A$1:$B$10)
assuming your cell range is "A1:B10"

9) Two Smallest Values

=A1>=SMALL($A$1:$B$10,2)
assuming your cell range is "A1:B10"

10) Non Numeric Cells

=ISTEXT(A1)

11) Below Average

Identifies all the cells which are below average.
=A1>AVERAGE($A$1:$B$10)
assuming your cell range is "A1:B10"

12) Cells Containing Spaces

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))>0

13) Cells Containing a Particular Character

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,"R",""))>0

14) Matching Text

=A1="text_to_match"
You can apply conditional formatting to check the value in one cell and apply formatting to other cells. Just reference the cells (e.g. ).

15) Hide Duplicate Values

=A1=A2
Shade the font the same as the background colour.