Learn how to use both of those strategies in Microsoft Excel to display negative values in red.
To enter a negative worth right into a Microsoft Excel sheet, precede the worth with a hyphen character, (-), which represents the “negative sign.” Often although, you received’t need to display that character. Instead, you may want Excel to display negative values in red, or with parentheses, or each. Microsoft Excel gives two ways to achieve this: a format and a conditional format.
The first technique is clear and fast as a result of Excel provides 4 built-in codecs for displaying negative values. The different technique will do nearly the identical. You’ll use this function when a format is already in use however doesn’t consider for negative values. To be sincere, this received’t be a problem for many of us. However, it’s good to know that there’s another, if wanted.
In this tutorial, I’ll present you each ways to display negative values in red in a Microsoft Excel sheet. I’m utilizing Microsoft 365 desktop on a Windows 10 64-bit system, however you need to use an earlier model. For your comfort, you possibly can obtain the demonstration .xlsx and .xls recordsdata. If you’re utilizing the .xls format, the Table object received’t be accessible, however you don’t want it. Excel for the online helps each strategies.
SEE: Windows, Linux, and Mac instructions everybody wants to know (free PDF) (TechRepublic)
How to use a format
Perhaps the standard and simplest way to display negative values in red is to use an Excel format. Fortunately for this process, there’s a built-in format that mechanically shows negative numbers in red.
Our instance knowledge, proven in Figure A, is an easy transaction sheet, the place the person enters debits as negative values. The identify of this Table object is BalanceSheet. There’s a easy expression in column D that provides the earlier steadiness to the present steadiness to return the steadiness after every transaction. If you’re following manually as an alternative of utilizing the demonstration file, enter the next into D3:
Then, enter the next into D4 and replica to the remaining cells:
This is a kind of instances when a Table object throws in an pointless wrench as a result of these two expressions aren’t the identical: Excel will overwrite the expression in D3. If you’re utilizing a Table and the second expression returns a worth error, re-enter the expression, =C3, in D3. You can use a standard knowledge vary for those who favor.
If you need debits displayed in red, apply an Excel format as follows:
- Select the forex values in C3:E10.
- On the Home tab, click on the Number group’s dialog launcher. In Excel for the online, select More Number Formats from the Number dropdown.
- In the ensuing window, select Currency from the Category listing — for those who’ve already assigned a Currency format, Excel will choose Currency for you. Figure B reveals the built-in Currency codecs under the pattern. There are 4 codecs for negative numbers.
- Choose the final format, the one which shows the quantity in red and wrapped in parentheses.
As you possibly can see in Figure C, the format shows negative values—the debits—in red. In addition, Excel wraps them in parentheses. The person nonetheless enters the values as negatives by previous the values with the hyphen character (-), however the format received’t display the hyphen.
Although we utilized the format to each columns, not one of the values in column D are red. That’s as a result of there aren’t any negative values in that column. But, if one pops up, the format will apply.
If you’d relatively not apply a format, otherwise you’ve already utilized a format that does one thing else, you need to use conditional formatting.
How to use conditional formatting
A easy format received’t at all times be the reply. When that is the case, use conditional formatting. It’s nearly as simple; it takes just a few extra steps.
To apply a conditional format for less than negative values, do the next:
- Select C3:D10.
- On the Home tab, click on Conditional Formatting in the Styles group.
- From the dropdown, select New Rule.
- In the ensuing window, select the Use a Formula to Determine Which Cells to Format choice in the higher pane.
- Enter the easy expression, =C3<0 in the decrease pane. When making use of this to your personal work, be certain to refer to the primary cell in the choice. In our case, that’s C3.
- Click Format.
- Click the Font tab. You may click on the Number tab, which permits you to set the identical built-in format used in the primary part. Remember although, we’re pretending that choice isn’t accessible.
- From the Color dropdown, select red.
- Click OK. Figure D reveals the expression and the format.
- Click OK to return to the sheet.
The conditional format will display the identical values in red that the format does. However, as you possibly can see in Figure E, it doesn’t wrap the values in parentheses. The rule is easy: If the present worth is lower than 0, that means the present worth is a negative quantity, the expression is True, and Excel applies the format (the red font).
As talked about, the built-in format must be your go-to technique, however you need to use a conditional format if crucial. It’s at all times nice to have alternate options.