Function Problems

Fill Multiple Excel Cells With X When a Trigger Cell Contains X

Phi Hung VoPhi Hung Vo Nov 12, 2026 868 views

Question details

The user wants selected cells in a row to display X whenever a trigger cell, such as the total-points cell, contains X.

Fill Multiple Excel Cells With X When a Trigger Cell Contains X
Product
Microsoft Excel worksheet
Device & OS
not provided
Scenario
A basketball statistics worksheet uses X when a points calculation is not numeric, and related cells in the same row must reflect that status.
Observed behavior
Several non-adjacent cells need to show X automatically when the trigger cell contains X; otherwise, they should remain blank or keep their normal formula result.
Before you start

Identify the trigger column and every target column before entering formulas. If the trigger cell may contain uppercase or lowercase text, use a case-insensitive comparison unless an exact uppercase X is required.

Solution 1Recommended

Use an IF Formula in Each Non-Adjacent Target Cell

Use this method when only specific, non-adjacent cells need to display X based on a trigger cell in the same row.

Each target cell needs its own formula because non-adjacent cells cannot be filled as one continuous range. Lock the trigger column with a dollar sign so the formula always checks the correct cell as you copy it across or down.

1
Select the first target cell

Click the first cell that should display X, for example F2, then enter =IF($D2="x","x",""). This checks whether the trigger cell D2 contains x.

2
Enter the formula

Press Enter to apply the formula. The target cell displays x when D2 is x and stays blank when D2 is not x.

3
Add the formula to other target cells

Click each other non-adjacent target cell in row 2, such as H2, K2, or N2, and enter the same formula =IF($D2="x","x","").

4
Copy formulas to additional rows

Select the completed target cells and drag their fill handles down, or copy and paste them into the matching cells below. The row reference changes from $D2 to $D3, $D4, and so on.

Use an IF Formula in Each Non-Adjacent Target Cell
Formula result: The X is formula-driven. If the trigger cell changes, the related target cells update automatically.
WPS Spreadsheet formula help

Show X in Related Cells With WPS Spreadsheets

WPS Spreadsheets supports the same IF formula workflow for marking selected basketball-statistics cells when a total-points trigger cell contains X. Enter the formula once in each target column, then fill it down for all player or game rows.

  1. 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the statistics table.
  2. 2. Choose a target cell: Click the first related output cell, such as F2, that should respond to the total-points cell.
  3. 3. Enter the IF formula: Type =IF($D2="x","x","") in the formula bar and press Enter, replacing D with your trigger-cell column.
  4. 4. Repeat for selected columns: Enter the same formula in each non-adjacent target cell that must show X.
  5. 5. Fill the formulas down: Select the formula cells and drag the fill handle downward to apply the row-based logic to the remaining records.
Use IF formulas to display X when a trigger cell contains X.Fill formulas down quickly with the fill handle.Open and edit Microsoft Excel .xlsx and .xls worksheets with compatible formulas.Use a familiar spreadsheet interface for easy migration from Excel.
microsoft office alternative - wps office

Frequently Asked Questions

How do I make the target cells blank when the trigger cell does not contain X?

Use =IF($D2="x","x","") in each target cell. The final "" returns a blank result when D2 is anything other than x.

Can I use the same formula for several non-adjacent columns?

Yes. Enter =IF($D2="x","x","") in each selected target cell. Keep $ before the trigger column letter so every formula in the row checks column D.

How can I preserve an existing calculation unless the trigger cell is X?

Wrap the existing calculation in an IF formula. For example, use =IF($D2="x","x",your_existing_formula) so the normal calculation runs only when D2 is not x.

Does the formula treat X and x as different values?

No. Excel and WPS Spreadsheets IF text comparisons are normally case-insensitive, so "X" and "x" both match the condition $D2="x".