Formatting in service of reporting

In several previous posts I wrote about the formatting of the contents of the cell. In addition to the standard ways of displaying its content, users can also define custom views. User-defined formats can be greatly used to enrich the appearance of the report. Using them, we achieve effects similar to the conditional formatting technique, and we can easily copy them or use them to create formatting styles.

We will show their implementation on a simple example, which is an analysis of planned and realized costs on a project. We created a table with three columns: PLANNED (planned costs),  ACHIEVED (realized costs) and DIFFERENCE (difference between planned and realized), and in types we listed several categories of project costs. In order to highlight what happened to the cost, our goal is to display the values in the third column in green, if the costs are lower than planned, yellow if they are equal, and red if we exceeded the planned costs.

First, we will mark the range of cells for which we want to create custom formats. Then, using the CTRL + 1 shortcut, we open the Format Cells dialog box, and clicking Number selects the formatting method for displaying the contents of the cell. In the end we select the last of the offered, the Custom category, and in the Type field we enter a new format using the formatting symbol.

When we create custom formats consisting of multiple variants, it is necessary to know that the first variant is always a way of displaying positive numbers, the other one is negative, and the third one refers to zero. The variants are separated by the sign “;”. Therefore, using the symbols “#”, “0”, “, and”. ” we will define the way the number is displayed, using the color name between corner brackets to determine the color of the text, and using the “_” sign, we can determine how much content is moved relative to the left edge of the cell. In short, the format to be created should look like this:

 [Green]_##0.00;[Red]_-##0.00;[Yellow]_##0.00

By clicking OK, we will apply the format to the selected cell range. We can copy these formats to other cells as well. Also, by choosing New Cell Style from the range of styles, we can create a new style that we will use in the future when we need to format the contents of a cell in a similar way.