08 February, 2022

Conditional Formatting in Excel: All you need to know

This is about visual identification and/or formatting (differentiation) of our data sets based on our questions (conditions), in order to: 

  • mark/reveal some data of interest, present them
  • take some action (e.g. find errors, correct, sort, delete, evaluate), or
  • find out some trends and patterns, compare.

Here is the Conditional Formatting main menu (on the left) + More Rules dialog:

And here is the account of what we can find out in our dataset, using all those options available to a user.

Identify and format cells/ranges containing:

  • values (numbers or text) which are =, >, <, <....> any values or limits (boundaries)
  • some text (strings, phrases)
  • dates occurring within some time frames
  • duplicates or unique values
  • blanks
  • errors
  • top or bottom ranked values (one or many)
  • top or bottom ranked values, within some percentage (e.g. 10%)
  • average value in a range
  • values below or above statistical parameters (some average value or 1,2,3 standard deviations, etc.)

Identify and format cells based on evaluation of their content by any applied Excel formula result to TRUE.

Some examples of such formulas (rules):

=ISFORMULA(B2), used to highlight cells with formulas in a selected range 

=ISEVEN(ROW()), used to shade alternative rows of data

=B3>=$B$2

= A2=A1, used to hide duplicate values

=AND(cond1,cond2)

=IF(AND(cond1,cond2),TRUE,IF(AND(cond1,cond2),TRUE),FALSE)

=OR(AND(cond1,cond2),cond3)

=MIN(range)

=MOD(ROW(),2)<>0

In most cases we can format (enhance visually) the identified cells with all kinds of COLORS, COLOR SCALES, DATA BARS or ICON SETS (directional, shapes, indicators, or ratings).

Here is e.g. Icon Sets menu available for formatting:

Any of the rules applied in identifying and formatting of cells/ranges (or even of the whole worksheet) can be managed, i.e. edited, prioritized, deleted or added, using Manage Rules... options.

 


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.