How to Conditionally Display TBD in Excel When a Column is Greater Than Zero
Question details
The user needs to display the text 'TBD' in a specific column whenever the corresponding value in another column is greater than zero.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up dynamic cell displays or values based on numeric conditions in adjacent columns.
- Observed behavior
- Requires a method to conditionally output or format cells to show 'TBD' without manual data entry, either by altering the actual cell value or just its visual format.
Determine whether you need the actual cell value to change to 'TBD' for further calculations, or if you only want the cell to visually display 'TBD' while retaining its original underlying numerical value.
Use Conditional Formatting to Display TBD Visually
This method changes only the appearance of the cell to 'TBD' while keeping the original underlying value intact for other formulas.
If you have formulas in your target column that calculate a numeric value, changing the actual value to text might break dependent calculations. Conditional formatting solves this by merely masking the number with text.
Highlight the column where you want 'TBD' to appear (for example, Column K).
Go to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter =F1>0 (adjusting F1 to match the starting row of your selection).
Click the 'Format' button, navigate to the 'Number' tab, and select 'Custom' from the category list. In the 'Type' box, enter exactly "TBD";"TBD";"TBD";"TBD" and click 'OK' twice to apply the rule.
Use an IF Formula to Change the Actual Cell Value
If you want the cell's actual data to become the text 'TBD' instead of just changing its appearance, use the IF function.
Apply Conditional Formatting and IF Formulas Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides an intuitive interface for applying complex IF formulas and custom conditional formatting rules. It is fully compatible with Microsoft Excel, allowing you to manage your conditional data logic effortlessly for free.
- 1. Open your file: Launch WPS Spreadsheet and open your existing Excel workbook.
- 2. Access Conditional Formatting: Highlight your target column, navigate to the Home tab, and select Conditional Formatting > New Rule.
- 3. Set the logic and format: Choose the formula option, enter your condition (e.g., =F1>0), and define your custom number format as "TBD" to instantly update your sheet.
Frequently Asked Questions
Why is my IF formula returning an error instead of 'TBD'?
Ensure that you have enclosed the word TBD in double quotation marks in your formula (e.g., =IF(F1>0, "TBD", 0)). Text strings in Excel formulas must always be wrapped in quotes; otherwise, Excel tries to read it as an undefined named range.
What does the custom format "TBD";"TBD";"TBD";"TBD" mean?
In custom number formatting, the four sections separated by semicolons dictate how positive numbers, negative numbers, zero values, and text are displayed, respectively. Putting "TBD" in all four sections ensures the cell forcefully displays 'TBD' regardless of the underlying content type.
Can I make the 'TBD' text appear in a specific color?
Yes. If you are using Conditional Formatting, you can set the font color within the 'Format' menu at the exact same time you apply the custom number format. For example, selecting a red font color under the 'Font' tab will make 'TBD' visually stand out.
