Excel Guides

Displaying Negative Percentages in Red in Excel

When it comes to displaying negative percentages in red in Excel, there are a few different ways that you can go about doing this. One way is to use the conditional formatting feature within Excel. This is a quick and easy way to format your cells based on certain criteria, and it can be used to highlight negative percentages in red. Another way to do this is by using a custom number format. This method gives you a bit more control over how your numbers are displayed, and it also allows you to change the color of negative percentages to red. Finally, you can use a simple VBA code to format your cells. This method requires a bit more work than the other two, but it gives you even more control over how your data is displayed.

Using Conditional Formatting

To use conditional formatting to highlight negative percentages in red, first select the cells that you want to format. Then, go to the Home tab and click on the Conditional Formatting button. From there, select Highlight Cell Rules and then Greater Than. In the next dialog box, enter "0" in the first field and choose Percentage from the second drop-down menu. Then, click the Format button and choose Red from the Font color drop-down menu. Finally, click OK twice to apply the conditional formatting rule.

Using a Custom Number Format

To use a custom number format to display negative percentages in red, first select the cells that you want to format. Then, right-click on one of the selected cells and choose Format Cells from the context menu. In the Format Cells dialog box, go to the Number tab and select Custom from the Category list. In the Type field, enter this code:

[Red][<=0]#0%;[Black][>0]#0%

This code will display all negative percentages in red and all positive percentages in black. You can also change the color codes to any other color that you want. To do this, simply replace "Red" and "Black" with the appropriate color codes (e.g., "FF0000" for red or "000000" for black). Once you've entered the code into the Type field, click OK to apply the custom number format.

Using VBA Code

If you want even more control over how your data is displayed, you can use a simple VBA code to format your cells. To do this, first select the cells that you want to format. Then, press Alt+F11 on your keyboard to open the Visual Basic Editor (VBE). In the VBE window, double-click on Sheet1 in the Project Explorer pane (on the left side of the window). This will open up the code window for Sheet1. In this code window, enter this code:

Sub HighlightNegativePercentages() 

    For Each cell In Selection 

        If cell.Value < 0 And cell.NumberFormat = "0%" Then 

            cell.Font.ColorIndex = 3 

        End If 

    Next cell 
    
End Sub

This code will loop through all of the selected cells and check if their values are less than zero AND if their number formats are set to percentage (i.e., if they end with "%"). If both conditions are met, then those cells will have their font colors changed to red (color index 3). You can change this color index number to any other color that you want.

Once you've entered this code into Sheet1's code window, click anywhere outside of that window (to make sure that no changes have been made) and then press F5 on your keyboard or click on Run > Run Sub/UserForm from Excel's menu bar at top.


Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.