Conditional Formatting Tips and Tricks

Finding the Unique Values with the help of Conditional Formatting.

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 desired 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 formatting will be applied

•   To apply formatting 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.

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(“I”,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”

Creating a quick project plan / gantt chart using conditional formatting.

Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.

First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…

Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,

    =IF(AND(F$8>=$D9, F$8<=$E9),"1","")

Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.

Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below:

Highlight the Non-Multiples

Shown below is the sample data for this conditional formatting example.

To add the conditional formatting, and highlight the non-multiples in column D:

• Select cells D2:D6, with D2 as the active cell

• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule

• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'

• In the formula box, type the MOD formula:   =MOD(D2,C2) <> 0

• Click Format, and on the Fill tab, select Red, then click OK, twice, to close the dialog boxes.

The MOD function returns the remainder after a number is divided by divisor. If the result is zero, then D2 is a multiple of C2.

Now the non-multiples in column D highlighted in red.

Format the Highest Value

To set up the conditional formatting for the highest value:

• Select cells C2:E6, with C2 as the active cell

• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule

• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'

• In the formula box, type the MAX formula:   =C2 = MAX($C2:$E2) The first reference to C2 is relative, so each cell will check its value compared to the MAX in the $C2:$E2 range.

• Click Format, and on the Font tab, select Red as the font colour, then click OK, twice, to close the dialog boxes.

Now the highest values are highlighted in each row.

Format the Second Highest Value

To set up the conditional formatting for the second highest value:

• Select cells C2:E6, with C2 as the active cell

• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule

• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'

• In the formula box, type the LARGE formula:   =C2 = LARGE($C2:$E2,2)

• Click Format, and on the Font tab, select Blue as the font colour, then click OK, twice, to close the dialog boxes.

Format the Lowest Value

To set up the conditional formatting for the second highest value:

• Select cells C2:E6, with C2 as the active cell

• On the Excel Ribbon's Home tab, click Conditional Formatting, and click New Rule

• In the New Formatting Rule dialog box, under 'Select a Rule Type', click 'Use a formula to determine which cells to format'

• In the formula box, type the MIN formula:   =C2 = MIN($C2:$E2)

• Click Format, and on the Font tab, select Green as the font colour, then click OK, twice, to close the dialog boxes.

Now the highest value in each row is highlighted in red, the second highest is blue, and the lowest is green.

Validation Rule for Conditional Formatting :

Conditional formatting won't let you refer to cells on a different worksheet, or in a different workbook. However, you can refer to a workbook level named range that's on a different worksheet.