Conditional Formatting and the Countif Formula

Conditional Formatting is a very simple, yet useful tool in data analysis. By understanding how it works, you can cut down your time tracking data entry errors by allowing you to visually see if specific criteria was met or not. The Countif formula gives you the total of the number of times a condition has been met. Let's get started with a simple example by downloading the file from Figure 1.1 by clicking here.

See Figure 1.1
























From Figure 1.1, we want to see which of the 20 students have a failing grade. Highlight cells B2:B21 and then select Format>Conditional Formatting... See Figure 1.2.

Figure 1.2
























After you left click on 'Conditional Formatting...', a Conditional Formatting popup box should be displayed. See Figure 1.3.

Figure 1.3








Change the values in the Conditional Formatting popup box to the following as shown in Figure 1.4. (i.e. Cell Valus Is less than 50)

Figure 1.4








Now, click on the 'Format...' button on the far right. See Figure 1.5

Figure 1.5








After you click on the 'Format...' button, a 'Format Cells' popup menu is displayed. Select the 'Pattern' tab at the top of the menu. Select the Yellow box in the color palette. Pres the OK button. See Figure 1.6.

Figure 1.6






















Now, the Course Mark cell color should be yellow for Joe Blow, Stanley Berdych, Bill Doughnut and Peter Griffin. See Figure 1.7.

Figure 1.7























Now, in cell A23 enter "Total Marks under 50". In cell B23 enter the following formula:

=COUNTIF(B2:B21,"<50")

where

COUNTIF is the formula

B2:B21 is the range

"<50" is the criteria

which in plain English tells us to look at the course marks of the students from cells B2 to B21 and count how many cells are less than a course mark of 50. See Figure 1.8.

Figure 1.8























If you examined the figures carefully, you will see conditional formatting did not catch a data entry error: Lester Judd's score of 20 should be yellow and there should be 5 marks under 50! The problem is Lester's mark of 20 was entered as the number 2 and the letter O (instead of the number zero). This will be handled in a different tutorial: Data Validation.

For the completed file, click here.

That's it. Keep Excelling!