Formatting Issues

Change Excel Table Cell Background When Text Is Entered

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 869 views

Question details

Automatically apply a gray background to populated cells in an Excel table's Subject column.

Change an Excel Table Cell Background When Text Is Entered
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants Subject cells containing text or numbers to have a gray fill, including cells added as the table grows.
Observed behavior
Subject cells should appear gray when they contain text or numbers and remain unfilled when blank.
Before you start

Identify the Subject column and its first data cell, excluding the header. The example below assumes that cell is C2; replace C2 with your actual first data cell if needed.

Solution 1Recommended

Apply a Conditional Formatting Formula to the Subject Column

Use a formula-based rule to fill Subject cells gray whenever they are not empty.

1
Select the Subject cells

Select the Subject data cells starting at C2, excluding the column header. If Subject is in another column, start with its first data cell instead.

2
Create a formula rule

Go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, and enter =C2<>"". Use the first selected cell's address in place of C2 if necessary.

3
Choose the gray fill

Click Format, open the Fill tab, select a light gray, and click OK. Click OK again to create the rule.

4
Check the range and priority

Open Home > Conditional Formatting > Manage Rules to check the Applies to range. If other rules affect the same cells, adjust their order as needed. After adding table rows, check that the range includes the new Subject cells and extend it if necessary.

Apply a Conditional Formatting Formula to the Subject Column
Formula results: A cell whose formula returns an empty string is treated as blank by this rule. A numeric value of zero is treated as nonblank.
Format cells automatically

Highlight Populated Subject Cells in WPS Spreadsheet

Use a formula-based conditional formatting rule in WPS Spreadsheet to give populated Subject cells a gray background.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and select the Subject data cells, starting with the first data cell and excluding the header.
  2. 2. Create a rule: On the Home tab, choose Conditional Formatting > New Rule and select the option to use a formula.
  3. 3. Enter the formula and fill: Enter =C2<>"", replacing C2 with the first selected cell's address. Open Format, choose a light-gray fill, and confirm the rule.
  4. 4. Verify the range: Use Conditional Formatting > Manage Rules to check the applied range and any competing rules. Check the range again after adding rows, and extend it if needed.
Automatically highlights Subject cells containing text or numbersSupports Microsoft Excel XLSX and XLS filesUses a familiar spreadsheet interfaceFree to download and use
microsoft office alternative - wps office

Frequently Asked Questions

Will the rule highlight numbers as well as text?

Yes. The formula =C2<>"" is true for text and numbers, including zero.

Why is a blank-looking Subject cell not gray?

The cell may be empty or contain a formula that returns an empty string. The rule =C2<>"" leaves both unfilled.

How do I include new rows in the formatting?

After adding rows, open Home > Conditional Formatting > Manage Rules and check the Applies to range. Extend it to include the new Subject cells if they are not covered.

What if another conditional formatting rule changes the fill?

Open Home > Conditional Formatting > Manage Rules and review the rules affecting the Subject cells. Adjust their order so the gray-fill rule is evaluated as intended.