Excel IF Formula to Change a Cell Value Based on T or F
Question details
The user needs to return zero when a condition cell contains T and preserve the original value when it contains F.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A condition is stored in one column and the original value is stored in another column. The user wants either a formula-based result in a separate column or an in-place transformation.
- Observed behavior
- The desired result is 0 for rows where the condition is T, while rows with F retain their original values.
Confirm which column contains the T/F condition and which column contains the original value. Decide whether you want a new calculated column or an in-place transformation with Power Query.
Use an IF formula in a separate Excel column
Use this option when you want to preserve the original data and calculate the changed value in a new column.
The IF function tests the condition cell and returns 0 for T. Any other value, including F, returns the original value from the source column.
In Excel, select the first cell of a blank result column, such as C1, next to the condition in A1 and the original value in B1.
Type =IF(A1="T",0,B1) in the formula bar or directly in the cell, then press Enter.
Select the cell containing the formula and drag its fill handle down, or use Home > Fill > Down, to apply the formula to the remaining rows.
Verify that rows with T display 0 and rows with F display the corresponding value from column B.
Replace values in place with Power Query
Use Power Query when the target column must be transformed in place or the dataset requires a repeatable data-cleaning process.
Apply IF-Based Value Changes in WPS Spreadsheets
WPS Spreadsheets lets you create the same conditional formula in a separate column, fill it across your data, and preserve the original values when the condition is not met.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open your XLS or XLSX workbook.
- 2. Select a blank result cell: Click the first blank cell beside your condition and original-value columns.
- 3. Enter the IF formula: Type =IF(A1="T",0,B1), replacing the cell references if your columns differ, and press Enter.
- 4. Fill the formula down: Drag the fill handle down the result column to apply the conditional logic to all rows.
Frequently Asked Questions
What formula returns zero when a cell contains T?
Use =IF(A1="T",0,B1), where A1 is the condition cell and B1 contains the original value. The formula returns 0 for T and the B1 value otherwise.
How can I keep the original value when the condition is F?
The formula =IF(A1="T",0,B1) automatically returns the original value in B1 whenever A1 is not T, including when A1 contains F.
Can I change the original values without creating a new column?
Yes. Load the range into Power Query through Data > From Table/Range, then use a conditional Table.ReplaceValue transformation and load the result back to Excel.
What happens if the condition cell is blank or contains another letter?
With =IF(A1="T",0,B1), any value other than T returns the original value from B1. Use an additional IF or IFS condition if blanks or unexpected values need separate handling.
Can this conditional formula be used in WPS Spreadsheets?
Yes. Open the workbook in WPS Spreadsheets and enter the same =IF(A1="T",0,B1) formula in a blank column, adjusting the cell references to match your table.
