It’s actually quite easy! The formatting features in Excel are quite powerful, and they allow you to conveniently spot and highlight important facts about your data such as negative numbers. Losses are usually displayed as red numbers in financial reports such as a company profit and loss statement. This red font color helps to make them easier to see. But you can also use these same techniques to show your negative values in any color of your choice. This post is going to show you all the different ways you can use to display your negative numbers with red font color in Microsoft Excel.
Show negative numbers as red using a number format.Show negative numbers as red using a custom number format.Show negative numbers as red using conditional formatting.Show negative numbers as red using VBA.Show negative numbers as red using Office Scripts.
Download your copy of the example workbook with the above link and follow along!
Display Negative Numbers as Red with a Number or Currency Format
The most straightforward method to show your negative numbers in red font color is to use a number or currency format. Both the number and currency formats have options to show negatives with red font color. Follow these steps to apply the number or currency format to your numbers. You can also use the Ctrl + 1 keyboard shortcut to open the Format Cells dialog box. Any cells you apply this format to will automatically change to a red font color whenever the value is negative. This is great when your values are derived from a formula and might change often as you don’t need to manually update the color.
Display Negative Numbers as Red with a Custom Format
Excel also allows you to define your own custom format from the Format Cells dialog box. You’ll be able to define number formats and colors for positive, negative, and zero values. This includes the ability to specify a font color for the displayed format, so your positive values can be green while your negative values are red. Follow these steps to create a custom format. After you press the Ok button, your custom format will be applied and you will see the various format options applied to your positive, negative, and zero values. The format string entered is made up of several parts. One great thing worth mentioning about the custom format option is there are several preset options available that include a red negative color. This way you can avoid writing the syntax from scratch, and can instead customize one of the existing options.
Display Negative Numbers as Red with Conditional Formatting
Conditional formatting is a feature that allows you to set the format of a cell, based on customizable rules. With conditional formatting, it’s possible to change any type of cell formats such as borders, font size, font style, fill color, and much more. You can also use this to change the font color to red when the cell value is less than zero. Follow these steps to apply conditional formatting to your range of numbers. This will open up the New Formatting Rule menu where you can select from various different types of rules to create. This will open up a simplified version of the Format Cells dialog box which only contains the Number, Font, Border, and Fill tabs. This will open up the New Formatting Rule menu again, but this time the Preview window will show a preview of the selected formats that will be applied when your rule is true. You will now see any values that are less than zero in your selected range appear with red font color. There is one interesting advantage about using a conditional format rule to display your negatives in red. You can apply different number formats such as percentage to your numbers through the regular formatting options, and then control the font color with a single rule. With conditional formatting, you also get more color options than with a custom format. There are many preset color options available to choose and you can even choose a color based on RGB or Hex code for the ultimate color customization possibilities.
Display Negative Numbers as Red with VBA
All the previous methods discussed are dynamic, if the numbers change so will the format to reflect the new value. But perhaps you want a static solution but without manually changing the font color for each cell. This is where you can use a VBA solution to automate the process. VBA is the scripting language baked into Excel, and it’s able to automate many tedious tasks. Press Alt + F11 on your keyboard to open the VBA code editor. Then you can go to the Insert menu and select Module from the options. This will create a new module where you can add your VBA code. Copy and paste the above code into the new module. This code will loop through the selected range in the workbook and test if the value in the cell is less than zero. When the value is less than zero, it will apply a red font format to the cell. Otherwise, it will apply a black font format. Now you can run this code at any time to apply red font color to the numbers in your workbook. The code will execute and change any font colors according to the values of the cells. The font color results will be static, so if the numbers change you will need to rerun the VBA script to update the font colors.
Display Negative Numbers as Red with Office Scripts
There is also another option for an automated static solution. Excel has a new modern scripting language based on the TypeScript language. This is a superset of the JavaScript language which runs the web. The new Office Script language is available in Microsoft 365 business plans and can be run from the online web version of Excel. Open Excel Online and go to the Automate tab, then click on the New Script button to open the Office Script editor. This will open the script editor on the right side of the Excel window with a blank script. You can click on the generic name given to the script and rename with descriptive name so you can easily find and run it later. You can then paste the above code into the Office Script editor and then click the Cloud icon to save it. There is also a Run button that will allow you to run the script. This script will loop through the selected range in the workbook and test if each value is less than 0. If the value is negative, it will change the cell font color to red. Otherwise, it will change the cell font color to black. You can run the script by going to the Automate tab and selecting the All Scripts command to open up a list of the scripts you have created. Click on the desired script. This will open up a window focused on the script containing a Run button. Select the range of numbers to run the script on then press the Run button and the negative numbers will get a red font color applied.
Conclusions
There are many ways to implement red negative numbers in Microsoft Excel. Easy options such as the number and currency formats are easy to implement but have no other color choices available other than red. Custom formats will allow you to choose different colors and number formats for positive, negative, and zero values but the available color choices are limited. If you want to choose a specific shade of red or any other custom color, using conditional formatting will be your best bet as any color can be obtained using RGB or Hex codes. These format options are all dynamic and the formats will update accordingly if the values in the cells change. If you need a static solution, you can avoid the manual cell formatting by using either a VBA or Office Scripts to automate the process. Have you ever needed to display your negative values in red? Which method do you prefer? Let me know in the comments below!
