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
2) Find Today's Date
3) Dates Within a Period
4) Identifying Odd Numbers
5) Identifying Even Numbers
6) Find Current Month
7) Shade any Blank Cells
8) Largest Value
9) Two Smallest Values
10) Non Numeric Cells
11) Below Average
12) Cells Containing Spaces
13) Cells Containing a Particular Character
14) Matching Text
15) Hide Duplicate Values

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.



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext