Conditional formatting in Excel

Excel advanced training

Conditional formatting is a great feature in Excel. It allows you to take a bunch of numbers and highlight aspects so that data pops up, for example when data is positive or negative, or when certain changes have occurred.

Examples of conditional formatting in Excel

Heat maps

With Excel heat maps we are using variation in color intensity to represent value ranges.

Screenshot Excel

Screenshot Excel

Example use case: Correlation matrix
Steps:

  1. Start tap
  2. conditional formatting
  3. colour scale
  4. Rules

Highlight data in a spreadsheet

Example use case: Highlight all data that has moved down more than 10%.

Steps:

  • Choose column with the data you like to highlight.
  • Highlight the column with the respective data (Shortcut ctrl+shift+arrow key)
  • Go to conditional formatting and choose highlight cell rules
  • Highlight less than
  • Put in the negative number for less than 10 percent (-10%)

Highlight data in a schedule or calender

Example use case: Let´s say we have a table and we would like to highlight when someone is scheduled.

Steps:

  • Highlight schedule
  • Go to conditional formatting
  • Highlight cell rules
  • select equal to and put in the respective name you like to highlight

If you want other people to use this you can change this name to a cell.

  • For this, highlight the schedule
  • go to conditional formatting and selcet manage rules
  • select the rule
  • change name to a cell
  • Now, you can look up any name, when you type that name in the cell.

And that is it. These are easy simple ways to use conditional formatting in Excel