Highlight Cells with Error Flags in the Drop-down in Google Sheets

Have you ever wondered how to highlight all the cells with error flags in the drop-down in Google Sheets? 🤔 Well, you’re in luck because I have the solution for you! But first, let’s understand why those red error flags appear in the first place.

Whenever you violate a data validation rule in a cell, Google Sheets displays a red error flag to alert you. These error flags indicate that the cell doesn’t meet the specified data validation criteria. Now, there are various types of data validation rules that you can set in Google Sheets, but today we’ll be focusing on highlighting cells with error flags in the data validation drop-down.

To prevent those red error flags from appearing in the data validation drop-down, you can choose the option “Reject input” instead of “Show warning” in the data validation settings. This way, users will be unable to enter any invalid data.

Now, let’s dive into the steps to highlight cells with error flags in the drop-down! Here’s how you can do it for both the “List of items” and “List from a range” data validation criteria.

List of Items and Highlighting Red Error Flags

If you’re using the “List of items” criteria, follow these steps:

1. In Google Sheets, navigate to the sheet where you want to set the drop-down.
2. Click on the cell where you want the drop-down to appear (let’s say A1) and go to Data > Data validation in the menu.
3. Choose the option “List of items” and enter the desired options separated by commas.
4. Copy the cell A1 and paste it in the range A2:A5 to have multiple drop-downs.
5. Now, if a cell violates the data validation rule by having a value that’s not in the list, a red error flag will appear.

To highlight these cells, follow these steps:

1. Select the range A1:A5 where your drop-downs are located.
2. Go to the Format menu and choose Conditional formatting.
3. Under “Format rules”, select Custom formula is and insert the following formula:

=AND(LEN(A1), COUNTIF({"Low", "Average", "Above Average", "High"}, A1)<1)

That’s it! With this formula, any cell with a value that’s not in the list will be highlighted with a red error flag.

List from a Range and Highlighting Red Error Flags

If you’re using the “List from a range” criteria, here’s how you can highlight cells with error flags:

1. Create two sheets in Google Sheets, let’s call them Sheet1 and Sheet2.
2. In Sheet2, enter the values that you want to include in the drop-down list.
3. In Sheet1, select the cell where you want the drop-down to appear (let’s say B3).
4. Go to Data > Data validation and choose the option “List from a range”.
5. In the criteria settings, select the range in Sheet2 that contains the values you want for the drop-down.
6. Copy the drop-down cell (B3) and paste it in the desired range (B4:B16 or wherever you need it).
7. If a cell violates the data validation rule by having a value that’s not in the range, a red error flag will appear.

To highlight these cells, follow these steps:

1. Select the range where your drop-downs are located (B3:C16).
2. Go to the Format menu and choose Conditional formatting.
3. Under “Format rules”, select Custom formula is and insert the following formula:

=AND(LEN(B3), COUNTIF(INDIRECT("Sheet2!A2:B11"), B3)<1)

By using this formula, any cell with a value that’s not in the range will be highlighted with a red error flag.

Exceptions

Keep in mind that the formulas provided might not work in all data validation scenarios. There are a couple of exceptions to consider:

1. Data Validation to Enter Values from a List as per the Order in the List: If you’re using this type of data validation, the highlighted rules mentioned above may not work. The formulas are specific to the settings we discussed.
2. Distinct Values in Drop-Down List in Google Sheets: Similarly, if you’re working with drop-down lists that require distinct values, the highlighted rules may not apply. Be sure to adjust your formulas accordingly based on your specific criteria.

Remove Error Flags in the Drop-Down Menu

Now, what if you want to remove those pesky error flags from the drop-down menu? It’s easy! Just include the invalid items in your “List of items” or “List from a range”.

For the “List of items” method, simply edit the data validation rule and add the new items that are causing the error flags. In the “List from a range” method, edit the corresponding sheet to include the flagged items, and update the data validation list to reflect the changes.

That’s all there is to it! Now you know how to highlight cells with error flags in the drop-down in Google Sheets and how to remove those flags. Enjoy exploring the possibilities and making the best use of your Google Sheets skills!

For more tips and tricks on using Google Sheets and other helpful resources, be sure to check out Crawlan.com. Happy spreadsheeting! 📊🎉