Excel conditional formatting is a powerful tool that, when used correctly, can your sales budget, project plan/report or analytical outputs from ho-hum raw data in default fonts to something dramatic and professional. You don’t need to be a computer genius or excel expert to achieve fantastic results.

  1. Highlight mistakes, errors, ommissions and repetitions
  2. Highlight alternate rows or columns
  3. Build project plans and gantt charts
  4. Adding “In-Cell” graphs
  5. Creating dashboards

Highlight mistakes, errors, omissions and repetitions

Entering large amounts of data into a spreadsheet is monotonous and pron to mistakes, omissions and repetitions. This can be avoided by using conditional formatting whenever typing or pasting a formula over a large range of cells.

To highlight a cell containing an error or omission:

  • Select “Conditional Formating”, “New Rule”, and “Format only cells that contain”
  • In the drop-down list under “Format only cells with:” select either “Error” or “Blanks” depending on what you want highlighted
  • Format your cells to your desired style

To highlight cells containing duplicate values:

  • Select “Conditional Formating”, “New Rule”, and “Format only unique or duplicate values”
  • Format your cells to your disired colors

Highlighting alternate rows or columns

 Data presented in a large table is difficult to read because your eyes may have trouble following the rows and columns, especially when scrolling. To remedy this, try highlighting or changing the background color of alternative rows or columns. You could do this manually, but if you have a large table that keeps changing, you need to use Conditional Formatting.

  • Select the data section of your table you want to format
  • Click “Conditional formatting” in the “Home” ribbon
  • Click “New rule” near the bottom of the drop-down menu
  • Select “Use a formula to determine which cells to edit”
    • To highlight alternate rows, enter the formula =MOD(ROW(),2)=0  in the input box under “Format values where this formula is true:”
      • This means whenever row() of the current cell is even, your desired formating will be applied
      • To apply formating to odd rows, just input =MOD(ROW(),2)=1  as the formula
      • To highlight alternate columns instead of rows, use =MOD(COLUMN(),2)=1  formula
      • What if you want to change background color of every 3rd row: use =MOD(ROW(),3)=0  instead
  • Format background colors, text, text color, borders, etc to your desired style. When you are done, the dialog should look something like the sample to the right:
  • Click OK.

Build a project plan or gantt chart

Example of a Gantt chart (Italian)

Example of a Gantt chart (Italian) (Photo credit: Wikipedia)

Project plans and [[gantt charts]] are everyday activity in the business community. Creating a simple and informative project plan template in excel is easy using conditional formatting.

  • Create a table structure like the one above. Use columns such as “Activity”, “Start” and “End” day, and numbered days 1,2,3,4, etc..
  • Whenever a day falls between start and end day for a corresponding activity, we need to highlight that cell. To do that, we need to identify whether a day on our grid falls between start and end day.
    • Enter the following formula: =IF(AND(F$8>=$D9, F$8<=$E9),”1″,””) .
    • Copy this formula to the other cells in the grid of days.
    • Now, whenever the day number represented on the top row is between start and end, the number “1” will be placed in the corresponding cell.
  • Open “Conditional Formating”, select “New Rule Rule” and “Format only cells that contain:”
  • Under “Format only cells with:” select “Specific Text” and “containing” and type “1” in the last input box.
  • Select “Format” and change the background and the text to your favorite color.

Add “In-Cell” graphs

In-cell graphing is a handy trick that basically uses REPT() function, which repeats a character a given number of times, to generate bar-charts with in a cell. You can apply conditional formatting to the characters to give the charts good effect. Here is a sample:

 The graph highlights the cells that are greater than the average number of sales for the month by changing the font color to red and making it bold.

To create the graph:

  • Enter the formula =REPT(“|”,C4/100) in C4 of the “Graph” column then copy the formula to the cells beneath. (You will need to adjust the “divided by 100” depending on the size of the number in the “Amount” column)

To create the conditional formating:

  • Click “Conditional Formating”, “New Rules” and “Use a formula to determine which cells to format”
  • Under “Format values where this formula is true:” enter

=($C4>AVERAGE($C$4:$C$33))

  • Click “Format” and change font color to “Red” and font style to “Bold” (or whatever format you want).
  • Click “Okay”

Create Dashboards

You can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here.

  • Copy your data table to a new table.
  • Empty the data part and replace it with formulas that link to your data set:

=ROUND(B6,0)&” “&IF(B5<B6,”▲”,IF(B5=B6,”●”,”▼”)

  • What we are doing is, whenever the cell value is greater than its predecessor in the data table we are appending a symbol (go to menu > insert > symbols and look for the above one)
  • Next, conditionally change the color of cell text to red, green or blue.