Conditional formatting in Excel enables you to apply a format to a cell or range of cells, so you can identify important information. The video tutorial below explains.
Conditional formats are formats that vary based on cell values and the conditions you specify.
In Figure 01 there is an extract from a budget input sheet. Columns H, I and J are for inputs. Column F is used to validate the input columns. Column G displays an icon to inform the user about the status of the input row. In column G, no icon means no input has been made (row 10). A green tick means the row contains valid input entries (rows 2 and 3). A red cross means the row is invalid (rows 4 to 9).
The input rules are:
- the Department and Account columns must contain valid codes (based on lists in another sheet)
- the Amount column must contain a number
- a valid input line must contain all three entries
Columns H and I each have in-cell drop-down lists based on lists in another sheet. Figure 02 shows the two drop-down lists. Columns A to F are used to validate the entries in columns H to J.
In-cell drop-down lists are created using Data Validation in the Data ribbon tab. They have two issues that can lead to invalid entries.
- Users can use Paste Special Values with a cell that has an in-cell drop-down list and cause an invalid entry in a cell.
- Changing the source list that the in-cell drop-down is based on doesn’t change the cell entry. For example, in Figure 01, cell H2 contains DISTRIBUTION. If the source list was modified and DISTRIBUTION was deleted or changed, the input cell would not change and hence would be invalid.
The Data Validation system has an option called Circle Invalid Data, which identifies cells that don’t match their validation rules. Its limitation is that it doesn’t notify you if there are any invalid entries. You have to manually examine all the data validation cells to see if any have been circled.
In Figure 03, I have used Paste Special Values to enter an invalid code (DIST) in cell H2. I then used Circle Invalid Data.
Notice that in Figure 03, cell G2 has changed to a cross (it was a tick in Figure 01). This is a dynamic indication of an error and it doesn’t require any manual intervention. Table 01 contains the formulas from row 2 and their explanations. These formulas have been copied down.
The formulas in columns A to F are all logical tests. They return either TRUE or FALSE. Usually, these types of formulas are used with the IF function, but they can be used on their own.
The small minus grouping button above column G in Figure 01 allows these validation cells to be easily and quickly hidden/unhidden.
The COUNTA function counts the number of entries in a range.
The COUNTIF function counts how many times an entry appears in a range.
The ISNUMBER function returns TRUE if the cell it is examining is a number or a date.
The OR function returns TRUE if any of its logical tests are TRUE. The only time an OR function returns FALSE is if all the logical tests are FALSE.
The AND function only returns TRUE if all its logical tests are TRUE. One or more FALSE logical tests will cause the AND function to return FALSE.
Refer to the formulas in Table 01 to understand these descriptions better.
What do the columns do?
Column A identifies if the input range is blank/empty. If the input range is empty, then neither a tick nor a cross will be displayed in column G.
Columns B and C both use the COUNTIF function to ensure that their entry is in their respective named range, either Depts or Accounts. Both ranges are in a separate sheet.
Column D ensures there are three entries in the three input cells.
Column E ensures a number has been entered in column J.
Column F assumes the row is valid (or at least not invalid) if column A is TRUE. Column A is TRUE only when there are no entries in the input cells. Column A is an override column for column F. If column A has TRUE (empty input cells), then column F will return TRUE. The AND function within the OR function will return TRUE only when all the other validation columns (B to E) return TRUE.
A FALSE in any of the columns from B to E will prompt a FALSE result from the AND function. Hence, if there is an entry in the input cells, then A2 will return FALSE. The second logical test in the OR function is the AND function. The AND function will only return TRUE if all the other validation columns are TRUE.
The formula in cell G2 is simple and controls the correct icon to display:
When you use logical test results in numeric calculations, in this case adding them together, they are converted into values, TRUE = 1 and FALSE = 0. Hence the possible values returned in G2 are:
- 0 (two FALSE entries) – cross required – invalid row
- 1 (only one cell contains a TRUE) – tick required – valid row
- 2 (both cells contain TRUE) – no icon required – empty row
Figure 04 shows the Conditional Formatting Icon option we will use to mark the rows.
The settings for the Conditional Format in the range G2:G10 are shown in Figure 05. You will need to use the drop-downs to change the defaults in the Icon and Type sections to make the selections.
A value of 2 means the row is blank and no icon is required. A value of 1 means the row is valid and requires a tick. A zero means the row is invalid and requires a cross.
Note: the Show Icon Only tick box has been ticked. This hides the underlying values in G2:G10.
The companion video and Excel files (blank & complete) will go into more detail to demonstrate these techniques.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to firstname.lastname@example.org
Counting invalid entries
As I mentioned previously, the Circle Invalid Data option doesn’t tell you how many errors there are. In this case we can count the number of FALSE entries in column F to count how many errors there are. The following formula counts the number of invalid entries in column F:
Data validation in-cell drop-down list
To insert an in-cell drop-down list, press in sequence Alt A V V. Don’t hold the keys down. This is the shortcut for the Data Validation option in the Data ribbon. See Figure 6.
In the dialog that opens, select List from the Allow drop-down and use the Source box to select the range of the list you want to use. In Figure 7 I have used a range name called Depts as the source range. This example is from column H in Figure 1. Click OK. That’s it, done.
Highlight invalid cells
Even though the above instructions identified invalid rows with a cross, they didn’t show which input cells were invalid. We can use another type of conditional format to do that.
Formula-based conditional formats
Select the range H2:H10. Click the Conditional Formatting icon on the Home ribbon. Select the New Rule option near the bottom. In the dialog that opens (see Figure 8), select the last option in the top section – “Use a formula …”. In the Formula box in the bottom section enter the following formula:
A formula in this section needs to return TRUE to apply the format. The formulas in column A are a logical test, so we can just refer to them directly as they return TRUE or FALSE. We have not made a format selection, so in effect it is not applying a format if A2 is TRUE. TRUE in column A means there are no entries in the three input cells. If that is the case, we don’t need to apply any formats.
Click the Conditional Formatting icon again, the New Rule option and create another formula-based rule using the following formula:
Click the Format button. Click the Fill tab and choose one of the red colours and click OK and OK again – see Figure 9. This formula checks column B for FALSE. FALSE means the entry is not in the list or the cell is blank.
Once more click the Conditional Formatting icon, but this time choose Manage Rules. Ensure the dialog looks like Figure 10. Note the tick in the Stop If True column of the first rule. You can move rules up or down by selecting them and clicking the arrow icons on the right of the Delete button.
These rules mean if column A has TRUE, no format is applied and further conditions are ignored (Stop If True). If column A is not TRUE, then column B is examined for FALSE, which means the cell entry is not in the list or the cell is blank and requires a selection from the drop-down. If the column has FALSE, then the red fill will be applied to the corresponding cell in column H.
This example may be over the top for three input cells, but it demonstrates techniques that highlight cells that don’t meet required conditions.
See if you can apply formats to columns I and J that change their colour if they are invalid.