Before going to know how to highlight all the cells with error flags in the drop-down in Google Sheets, we must understand the reason for the red error flags.
When you violate a Data validation rule in a cell, that cell will show a red error flag. It is to alert the user that the cell violates the data validation rule applicable to that cell.
There are different types of data validation rules that we can set in a cell in Google Sheets.
Here, we are only discussing how to highlight all the cells with error flags in the data validation drop-down in Google Sheets.
You can prevent red error flags in the data validation drop-down by checking the option “Reject input” instead of “Show warning” in the data validation settings.
Are you new to “Reject input” as well as “Show warning” in the data validation drop-down settings? Then please see the below image.
In this post you will get a conditional format rule that can highlight data validation drop-down in the following case.
Assume you have set a drop-down in cell A1 and “On invalid data” is set to “Show warning”.
Instead of selecting an existing item in the drop-down, you have entered a different value.
It may cause a red error flag in cell A1. My conditional format rule will highlight such cells. Let’s go to two examples.
Highlight All the Cells with Error Flags in the Drop-Down
We can set two types of data validation drop-downs in Google Sheets. They are the criteria type “List from a range” and “List of items”.
I’m starting with the “List of items”.
List of Items and Highlighting Red Error Flags
In Sheet3!A1, I want a drop-down with the text “Low”, “Average”, “Above Average”, and “High”. Let’s first set it.
Steps:-
1. In your Google Sheets, in Sheet3, click cell A1 and go to the menu Data > Data validation.
2. Choose “List of items” and enter the texts as comma-separated-values as below.
3. It will set up a drop-down with the above four texts to select.
4. Copy-paste the cell A1 drop-down list to A2:A5. So you will have five drop-downs in the range A1:A5.
5. Select “Average” in A1, “Low” in A2, “Above Average” in A3, and “Low” in A5.
In A4, do not select any text from the drop-down menu. Type the text “Extra Ordinary” instead.
Since the text “Extra Ordinary” is not in the “List of items”, it violates the data validation rule. That causes a red error flag in cell A4.
Now let’s see how to highlight the cells (here cell) with red error flags in the data validation drop-down “List of items” in Google Sheets.
Steps to Highlight the Cells with Error Flags in the List of Items Drop-Down
1. Select the range A1:A5.
2. Go to the FORMAT menu Conditional formatting.
3. Under the “Format rules”, select “Custom formula is” and insert the below formula in the field given.
=and(
len(A1),
countif({"Low", "Average", "Above Average", "High"},A1)<1
)That’s all that you want to do to highlight red error flags in the drop-down range A1:A5.
Formula Explanation
As you can see, I have used the AND logical function to highlight the cells with error flags in the drop-down list in A1:A5.
Here is the syntax of the said function in Google Sheets (the function returns the Boolean TRUE [here highlights] if all of the provided arguments are logically true).
AND(
logical_expression1,
[logical_expression2, …]
)The logical_expression_1 as per our formula is len(A1). It means to test whether A1 has any value.
The logical_expression_2 is;
=countif({"Low", "Average", "Above Average", "High"},A1)<1It’s a COUNTIF formula and here is the syntax.
COUNTIF(range, criterion)The “range” is the list of items within the Curly Braces and the criterion is the value in cell A1.
The above formula returns 1 if the criterion is within the list of items. If it is not present, the formula will return 0.
The <1 part forces the formula to return TRUE or FALSE depending on the Countif output.
If the result is <1 (if the criterion is not present in the range), the formula will return TRUE, else FALSE.
If A1 has a value (the LEN part) and that value is not in the list of items (the COUNTIF part), both the conditions in the AND will meet TRUE. Such cells will be highlighted in the range A1:A5.
There is only one cell that meets this criteria and that is the cell A4.
Note:- In conditional formatting, no need to write separate rules for the cells A1 to A5. The A1 rule will apply to A1:A5, if the “Apply to range” is A1:A5 in the “Conditional format rules” panel.
You can read more about this here – Relative Reference in Conditional Formatting in Google Sheets.
List from a Range and Highlighting Red Error Flags
Here is one more example to highlight cells with error flags in the drop-down in Google Sheets.
This time there are two sheets (tabs) in use. Sheet1 is for drop-downs, and sheet2 is for the drop-down list.
Here are the values in Sheet2.
In Sheet1!B3, I have the following drop-down menu, which is copied down until Sheet1!B16.
Data Validation Settings:
Cell range: Sheet1!B3
Criteria: List from a range – Sheet2!A2:A11
On invalid data: Checked “Show warning”.
In Sheet1!C3, I have the following drop-down menu, which is copied down until Sheet1!C16.
Data Validation Settings:
Cell range: Sheet1!C3
Criteria: List from a range – Sheet2!B2:B4
On invalid data: Checked “Show warning”.
In cell B8, B14 to B17, C5, C7, C8, and C14 in Sheet1 have values that are not in their respective drop-downs. I have entered them forcefully.
So these cells have red flags indicating violation in the data validation rules.
Here to highlight the above-mentioned cells with error flags in the drop-down, we can use the below rule in the conditional formatting in Google Sheets.
Conditional Format Settings:
Apply to range: B3:C16.
The custom formula is;
=and(
len(B3),
countif(indirect("Sheet2!A2:B11"),B3)<1
)This formula is similar to our formula used in the example 1 (list of items) above. The difference is in the COUNTIF use.
Earlier, we have presented the COUNTIF range (list of items) within Curly Brackets. Here we have the list in a separate sheet (Sheet2).
Here the COUNTIF range (list from a range) refers to Sheet2.
In conditional formatting, we can’t refer to another sheet directly. We should use the INDIRECT function for that.
So, instead of referring to Sheet2!A2:B11, I have used indirect("Sheet2!A2:B11") in COUNTIF.
Related: Role of Indirect Function in Conditional Formatting in Google Sheets.
Exceptions
I have given two similar formulas to highlight red error flags in the data validation drop-down menu in Google Sheets. But the above formulas may not work in all the data validation drop-downs.
Here are two exceptions.
- Data Validation to Enter Values from a List as per the Order in the List.
- Distinct Values in Drop Down List in Google Sheets.
Since I have used a particular type of settings in the drop-down, the above highlighting rules may not work in the above-linked tutorials.
Remove Error Flags in the Drop-Down Menu
Can I remove error flags in the drop-down menu in Google Sheets?
The answer is YES. Include the invalid items in the “List of items” or “List from a range” to remove the red error flags. I think it goes without saying.
In the list of items method, edit the data validation rule to include the new (red flagged) items.
In the list from a range method, as per our example, edit Sheet2 to include the flagged items.
Then edit the data validation list from range reference to include added values.
That’s all. Enjoy.
