How to Make an Excel Value Negative When Another Cell Contains Text
Question details
The user wants to automatically change a numerical value to a negative amount based on the text content of an adjacent cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing financial data where 'Expense' entries need to be represented as negative numbers, while other categories remain positive.
- Observed behavior
- The user needs a formula to conditionally output a negative or positive number based on a text string in another column.
Ensure your data is organized in clear columns, with one column for the category text (e.g., 'Expense') and another for the numerical amounts. Verify that there are no trailing spaces in your text cells, as this can cause the logical formula to fail.
Use the IF Function to Conditionally Change the Sign
The most straightforward method is to use an IF formula in a new column to check the text and apply a negative sign if it matches your criteria.
The IF function evaluates a logical test. If the test is true (the cell contains 'Expense'), it multiplies the value by -1 (represented simply by putting a minus sign before the cell reference). If false, it returns the original unchanged value.
Click on an empty cell in a new column where you want the calculated result to appear, for example, cell C2.
Type the formula =IF(A2="Expense",-B2,B2) into the formula bar. Replace A2 with the cell containing your text and B2 with the cell containing your numerical value.
Press Enter to get the result. Then, click the fill handle (the small green square at the bottom-right corner of the cell) and drag it down to apply the formula to the rest of your data list.
Easily Manage Conditional Formulas with WPS Spreadsheet
WPS Spreadsheet provides full support for logical functions like IF, IFS, and conditional formatting, making it incredibly easy to manage financial data and automate calculations.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet module, and load your financial workbook.
- 2. Input the IF formula: Select the target cell in an empty column and type =IF(A2="Expense",-B2,B2) to set your logical condition.
- 3. Drag to fill the column: Click and drag the fill handle down your column to instantly categorize all your expenses as negative values.
Frequently Asked Questions
Why is my IF formula returning the original positive value even when the cell says 'Expense'?
This usually happens if there are hidden trailing or leading spaces in the text cell. For example, 'Expense ' will not match 'Expense'. You can fix this by wrapping the cell reference in the TRIM function, like this: =IF(TRIM(A2)="Expense",-B2,B2).
Can I check for multiple text conditions, like 'Expense' or 'Fee'?
Yes, you can use the OR function inside your IF statement to check for multiple keywords. The formula would look like this: =IF(OR(A2="Expense", A2="Fee"), -B2, B2).
How do I make the value negative if the cell contains a specific word within a longer sentence?
If the cell contains more than just the exact word (e.g., 'Office Expense account'), you can use the SEARCH function combined with ISNUMBER. The formula would be: =IF(ISNUMBER(SEARCH("Expense", A2)), -B2, B2).
Is there a way to format the negative numbers in red automatically?
Yes. After applying your IF formula, select the entire result column, go to the 'Home' tab, click 'Conditional Formatting', choose 'Highlight Cells Rules', and select 'Less Than'. Enter 0 in the prompt box and choose a red text format to highlight negative numbers.
