Display Plus Sign in Excel if Value is Positive

Date Published: 20 July 2011

Display Plus Sign in Excel if Value is Positive

Sometimes in Excel you may want to actually display a + character (or plus sign) in front of the cell value if the value is positive.  For instance, if you’re showing the change in a value, like this (note these are made up values):

excel_1

In this case, you can imagine that the Change columns are relative to the previous month’s report, so this gives an at-a-glance idea of the trend.  However, you probably would like the first column to show a + sign so that it’s more clear that it’s a delta value, not a raw value.  You can apply this custom formatting to do so.  First, right click on the cell(s) you want to format, and select Format Cells.  Then click Custom and type in the value shown below.

excel_formatcell

Here’s the value for you to cut and paste:

+#,###;-#,###;"On Forecast"

With that change to the two Change columns, they now look like this:

excel_2

The last thing we might want to add is some conditional formatting.  For instance, we might want to see the positive change as green and the negative as red.  You can see how to apply this change in the next screenshot (click to enlarge):

excel_3

Summary

This just shows a couple of quick ways to enhance change values in your Excel sheets so that they stand out a bit.  You can change the default rendering of the change values so that they include a plus sign prefix if the cell value is positive, or a minus sign prefix if it is negative.  You can also apply a bit of conditional formatting so that the cells are colored differently based on their values.  Hope this helps!

References

I found this forum thread helpful when researching this topic.

Steve Smith

About Ardalis

Software Architect

Steve is an experienced software architect and trainer, focusing on code quality and Domain-Driven Design with .NET.