Minus and plus

When, as a result of subtracting two values in an Excel table, you get a negative result, sometimes you don’t want the negation sign to be seen. This requirement can be solved in several ways: by formatting the display of the number using the Accounting style, by using function that calculates the absolute value of the number or by creating custom format that will display number in a manner that suits you better.

We will start from the table, which shows a simple variance analysis. We have columns PLANIRANO (BUDGET) and OSTVARENO (ACTUAL), and we could calculate the variance as the difference between these two values. It can also be done in the opposite direction, it all depends on what you want to get as a result! If the plan is ambitious, budgeted values will always be greater than the actual ones. If it was achieved more than planned, it is either the result of great growth or bad planning. However, the easiest way to always get positive values is to include the expression within the ABS function:

=ABS(C3-B2)

After entering the formula, copy it to the other cells in the column.

What if you don’t want to change the formula, but only the way the value is displayed in the cell? Select all the cells to which this formatting should apply, then use the CTRL+1 key combination to open Format Cells window. Position yourself in the Number tab. Select Custom. Select the format of the number #.##0,00 and copy it to the clipboard (CTRL+C), then in the field where you specify custom format, add this expression twice, separated by semicolons:

#.##0.00; #.##0.00

After clicking OK, you will get a display of the results as if there were no negative numbers among them – we hid the negative signs!

My recommendation is that when you do such things always create custom format using the following expression:

#.##0.00;[Row] #.##0.00

As a result, you will get negative values displayed in red color!