It’s easy to view all the cells with conditional formatting rules applied in Google Sheets. However, there is no built-in option to select all such cells at once.

Unfortunately, as of writing this post, Google Sheets does not offer an equivalent to Excel’s Go To > Special > Conditional Formats feature. This is one of the features I miss in Google Sheets, along with Goal Seek and Formula Auditing.

How to View All Cells with Conditional Formatting in Google Sheets

This is a quick tip, so let’s jump straight to the steps. To view all the cells with conditional formatting applied, you first need to select all the cells in the sheet.

How to Select All Cells in Google Sheets

To select all cells in a sheet, use one of the following methods:

• Press Ctrl + A on Windows or ⌘ + A on Mac.
• Click the Select All button (the small square in the top-left corner where the row numbers and column letters meet).

View All Conditional Formatting Rules in a Sheet

Once all the cells in the sheet are selected, follow these steps:

1. Go to the Format menu.
2. Click on Conditional Formatting.
3. This will open the Conditional Format Rules sidebar, where you can see all the applied rules.

Hover your mouse over any rule to highlight the affected cells. A light green border will appear around the cells where that particular rule is applied.

This allows you to view all conditional formatting rules in a sheet at once by simply hovering over each rule.

Note: If some rules apply to areas outside the visible screen, you may need to reduce the zoom level to 50% or scroll horizontally/vertically to locate them.

Managing Conditional Formatting Rules in Google Sheets

From the Conditional Format Rules sidebar, you can:

• Edit existing rules.
• Delete rules that are no longer needed.
• Reorder the rules to change their priority.

That’s it! Now you know how to find and manage all the cells with conditional formatting in Google Sheets.

FAQs

Can I View Conditional Formatting Rules for the Entire Workbook?

No, Google Sheets only allows you to view the conditional formatting rules for the current worksheet, not the entire workbook.

How Do I Select a Conditional Formatted Range?

To navigate to a specific range affected by a conditional formatting rule:

1. Open the Conditional Format Rules sidebar.
2. Click on the rule you want to navigate to.
3. Copy the range listed under Apply to range.
4. Go to the Accessibility menu.
5. Click Go to range.
6. Paste the copied range.
7. Click the Right Arrow to jump to the range.

Additional Resources

• Date-Related Conditional Formatting Rules in Google Sheets
• Google Sheets: Copy Cell Values Without Conditional Formatting
• AND, OR, and NOT in Conditional Formatting in Google Sheets
• Highlight an Entire Row with Conditional Formatting in Google Sheets
• Using the INDIRECT Function in Conditional Formatting in Google Sheets
• How to Highlight Duplicates Across Sheet Tabs in Google Sheets