Function Problems

How to Use Excel IF Formula to Display Negative Amounts as Positive

Maira Mehtab Oct 22, 2026 868 views

Question details

The user needs to convert a negative reconciliation amount into a positive absolute value using an IF formula, while applying bold red text and shading through conditional formatting.

How to Use an Excel IF Formula to Display Funds Due as a Positive Amount

Product: Excel
Device & OS: not provided
Scenario: Calculating financial reconciliation where escrow or funds due must be displayed positively, formatted as currency, and highlighted conditionally.
Observed behavior: Negative numbers need to be conditionally calculated as positive absolute values with specific formatting, displaying $0.00 when no funds are due.

Before you start

Identify the cell containing your original reconciliation calculation (e.g., cell D122) before setting up the IF formula and conditional formatting rules.

Solution 1Recommended

Convert Negative Values with an IF Formula

Use the IF function to check if the balance is negative, and multiply it by -1 to display a positive amount.

This approach evaluates the source cell to see if the value drops below zero. If it does, the formula reverses the sign to make it positive. If it does not, it returns zero or remains blank based on your preference.

Select the target cell

Click on the cell where you want the positive funds due to be displayed (for example, cell D119).

Enter the IF formula

Type =IF(D122<0,-D122,"") into the formula bar and press Enter. This displays a positive number if cell D122 is negative, and leaves it blank otherwise. To explicitly display $0.00 instead of a blank cell, use =IF(D122<0,-D122,0).

Format as Currency

Right-click the cell, select 'Format Cells', choose the 'Currency' or 'Accounting' category, and click OK to ensure the number displays properly.

Apply Conditional Formatting

Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'. Choose to format cells containing values greater than 0, and set the formatting to use bold red text with a shaded fill color.

Convert Negative Values with an IF Formula

Formula Tip: Using a minus sign before the cell reference (e.g., -D122) is the fastest way to mathematically convert a negative number into a positive one within an IF statement.

Solution 2

Prevent Negative Balances Using the MAX Function

Adjust your original calculation so it never falls below zero by wrapping it in a MAX function.

Manage Finances Easily

Effortlessly Apply Financial Formulas in WPS Spreadsheet

WPS Spreadsheet offers comprehensive formula capabilities, allowing you to use advanced IF statements, conditional formatting, and financial calculations seamlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing financial workbook or create a new one.
2. Input the IF statement: Select your target cell and type =IF(D122<0,-D122,0) to ensure negative values convert to positive.
3. Set up conditional rules: Navigate to the Home tab, click Conditional Formatting, and define a rule to display text in red for any amount greater than zero.

100% compatibility with Microsoft Excel (.xlsx) formulas and file formatsRich conditional formatting features for easy visual financial trackingIntuitive interface with powerful, lightweight calculation performanceFree to download and use for your daily accounting tasks

microsoft office alternative - wps office

Frequently Asked Questions

How can I display exactly $0.00 when no funds are due instead of a blank cell?

Instead of ending your IF formula with empty quotes (""), end it with a zero. Use the formula =IF(D122<0,-D122,0) and ensure the cell is formatted as Currency or Accounting to successfully display $0.00.

Can I use the ABS function instead of a negative sign in the IF formula?

Yes. The ABS (Absolute Value) function can also be used. Your formula would look like =IF(D122<0, ABS(D122), 0). This achieves the exact same result by returning the absolute positive value of the negative number.

Why is my conditional formatting rule highlighting empty cells in red?

If your IF formula returns an empty string (""), spreadsheet software sometimes evaluates this text string as being 'greater than 0', triggering the rule. To fix this, change your conditional formatting rule to evaluate values using a formula like =AND(D119>0, ISNUMBER(D119)), or simply ensure your IF formula returns 0 instead of "".

Excel & Data Problems