Formatting Issues

How to Conditionally Format Nonblank Cells by Category in Excel

Olivia MillerOlivia Miller Nov 11, 2026 868 views

Question details

The user wants to apply different fill colors to nonblank cells in a specific range based on the text category listed in a reference column.

How to Conditionally Format Nonblank Cells by Category
Product
Excel
Device & OS
not provided
Scenario
Managing categorizations where rows require distinct formatting for non-empty cells across multiple columns.
Observed behavior
Automatically assigning fill colors to nonblank cells based on multiple predefined categories and corresponding formatting rules.
Before you start

Ensure your data range is well-defined and that the category column contains exact text matches for the rules you plan to set up.

Solution 1Recommended

Use the AND Formula in Conditional Formatting

Create a custom formula rule for each category to check if a cell is not blank and matches the specified category in the reference column.

By combining the AND function with conditional formatting, you can evaluate two conditions simultaneously: whether the cell contains data, and whether the corresponding row belongs to a specific category.

Using mixed references (like $A6) is critical here. It locks the category column reference while allowing the formatting to apply dynamically across all other columns in your range.

1
Select your target range

Highlight the range of cells where you want the formatting to appear (for example, C6:AT25).

2
Open Conditional Formatting

Go to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.

3
Choose formula rule type

Click on 'Use a formula to determine which cells to format'.

4
Enter the AND formula

In the formula box, type =AND(C6<>"",$A6="CATEGORY1"). Replace 'C6' with the top-left cell of your selection, '$A6' with the first cell of your category column, and 'CATEGORY1' with your actual category name.

5
Set the formatting

Click the 'Format' button, choose your desired fill color from the Fill tab, and click OK. Repeat this entire process for CATEGORY2, CATEGORY3, etc., using different colors for each.

Use the AND Formula in Conditional Formatting
Reference Check: Make sure your category reference locks the column (e.g., $A) but leaves the row relative (e.g., 6) so the formula adjusts correctly down the rows.
Conditionally Format Data in WPS Spreadsheet

Easily Apply Advanced Conditional Formatting with WPS Spreadsheet

WPS Spreadsheet offers full support for advanced conditional formatting formulas, allowing you to highlight nonblank cells by category effortlessly. It boasts a familiar interface and is highly compatible with your existing data files.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data categories.
  2. 2. Highlight the data range: Select the cells you want to conditionally format, ensuring you start from the top-left cell of your data.
  3. 3. Access Conditional Formatting: Navigate to the Home tab, click on the 'Conditional Formatting' icon, and select 'New Rule'.
  4. 4. Apply the formula: Choose 'Use a formula to determine which cells to format', input your =AND() formula, and define your fill color.
  5. 5. Manage multiple rules: Repeat for other categories, and use 'Manage Rules' to prioritize or edit your existing formatting conditions.
Supports complex AND/OR conditional formatting rules100% compatibility with Microsoft Excel .xlsx and .xls formatsFree, lightweight, and easy-to-navigate interfaceProcess massive datasets and custom rules without lagging
QA img-9

Frequently Asked Questions

Why is my conditional formatting formula highlighting the wrong cells?

This usually happens due to incorrect relative or absolute cell references. Ensure you are locking the category column with a dollar sign (like $A6) so the formula always checks column A, while leaving the evaluated cell reference relative (like C6).

Can I apply conditional formatting across multiple sheets?

Conditional formatting rules are specific to the worksheet they are created in. You cannot directly reference another sheet in the rule without using the INDIRECT function or setting up named ranges.

How do I edit or delete a conditional formatting rule?

Select any cell in your formatted range, go to Home > Conditional Formatting > Manage Rules. From this dialog box, you can edit the existing formula, change the formatting style, or delete rules you no longer need.

Does conditional formatting slow down my spreadsheet?

Applying highly complex volatile formula rules over hundreds of thousands of cells can impact performance. It is recommended to apply rules strictly to your actual data range rather than entire columns (like A:A).