Have you ever had to show relative net changes in MS Excel and was looking to make it more visual by using up and down arrows or colorful symbols that updates as the numbers change? There is a very simple way to add symbols to MS Excel cells using custom formatting.
The first step is to get the symbols we would like to use in custom formatting. There are two ways to get these symbols:
1) Click on Insert on the top bar; click on Symbols and select the symbol to be used; insert it as a hard code symbol in the sheet.
Use the UNICHAR(number) function in MS Excel and find the numeric value that corresponds to the desired symbol. In our case, we would like to use the up arrow and down arrow and a flat line to show up and down movements in the relative changes of daily stock price with respect to its 50-day moving average on each day. The numeric values for these symbols are shown below.
There are up to 128,767 numeric values that can be used as an input to UNICHAR() function. For more details about this function read Microsoft UNICHAR() function description. A list of symbols and their corresponding numeric values can be found through a simple googlsearch.
The second step is to choose the colors. There are 57 colors that can be generated by ColorIndex Property. For instance, to make the arrows red in custom formatting, we can either: 1) type the color in square brackets [red], or 2) enter the color code in square bracket [color4]. In our case we used three colors: red, green and orange. The corresponding color codes for these three colors are displayed below. Refer to Microsoft website for a list of ColorIndex values and colors.
The third step is to write the proper syntax in the Format Cells window.
In the example below, we are comparing Apple stock close prices to the 50-day moving average (“MVAG”) price of the stock on a daily basis. The net change relative to the moving average price or the return is calculated as [Close Price/MVAG – 1] on each day. If this % value is positive, we would like to show an up green arrow (▲) indicating that the close price is above its 50-day moving average price. If this percentage is negative, we would like to display a red down arrow (▼) indicating the close price is below its 50-day MVAG price. If the net change percentage is 0%, we would display an orange flat line (▬) indicating flat or no change.
We implemented this approach under 5 Methods to show the different ways Custom Formatting can be used.
Hover over the cell that is to be custom formatted with your mouse. Press Ctrl+1 to get to the Format Cells window. Click on Custom under Category.
As shown above, the argument in this box has four parts:
Positive numbers; Negative numbers; Zeros; Text
- The first segment of the argument is how positive numbers are formatted followed by a ;
- The second segment of the argument is how negative numbers are formatted followed by a ;
- The third segment of the argument is how 0 is formatted followed by a ;
- The fourth segment of the argument is how text is formatted
In this example, we only used the numeric segments as there are no text inputted.
Method 1: Use up green arrow for positive numbers, down red arrow for negative number, a flat orange line for no change
Type the following syntax in the box under Type:
[Green]"▲";[Red]"▼";[Color46]"▬"
Method 2: Show the % change followed by an up green arrow for positive numbers, the % change followed by a down red arrow for negative number, and 0% followed by a flat orange line for no change
Use the following syntax and type it in the box:
[Green]0%" ▲";[Red](0%)" ▼";[Color46]0%" ▬"
This is similar to Method 1, except the 0% is added between the arrow and the color in the square brackets to include the number format. In such case, 13% will be shown as 13% ▲.
Method 3: Show the % change followed by an up green arrow for positive numbers greater than 10%, the % change followed by a down red arrow for negative numbers less than -3%, and % change followed by a flat orange line for any number outside this range
Use the following syntax and type it in the box:
[Green][>0.1]0%" ▲";[Red][<-0.03](0%)" ▼";[Color46]0%" ▬"
Follow the same format as Method 2, except we add the conditions in square brackets. For instance, if we would like to show that green numbers must be greater than 10%, the condition is shown as [>0.1] for positive numbers. For negative numbers, the condition is shown as [<-0.03] in order to format percentages less than 3% red.
Method 4: Show the % change followed by an up green arrow for positive numbers greater than 10%, the % change followed by a down red arrow for negative numbers less than -3%, and % change followed by a flat orange line for any number outside this range; change the display such that there is enough space between the %change and symbols with symbols showing at the end of the cell and %’s at the beginning of the cell.
This is the exact same formula as Method 3 except we add * and space before the “symbols”. This repeats the space between the % change and the symbol until we reach the end of the cell.
[Green][>0.1]0%* "▲";[Red][<-0.03](0%)* "▼";[Color46]0%* "▬"
Method 5: This method presents the same result of Method 4 but in a more dynamic way, by using nested if formulas in the cell which allows for user controlled upper and lower bound inputs.
Using a nested if function, if the % value in the cell is above the lower bound and below the upper bound, we show the value in the cell as 0; if it is above the upper bound, we display a 1; and if it is below the lower bound, we display a -1.
=IF(AND(I18<Upper_Bound,I18>Lower_Bound),0,IF(I18>Upper_Bound,1,-1))
Hence, the values become a combination of 1, 0, -1. We would then custom format these values using the syntax in Method 1: [Green]"▲";[Red]"▼";[Color46]"▬"
Formatting numbers with text included:
One can also format a number to show the desired text in the cell while the actual value in the cell remains as a number. This allows for using the cell as input in calculations without having to change the cell type from a number or a percentage to Text or General. For instance, we can custom format the upper and lower bound percentages as shown below by using the following syntax, while the value in the cell remains as a %. This is done in Format Cell window, Custom, with the following syntax used in the box below Type:
"Upper Limit: "* 0%
"Lower Limit: "* 0%;"Lower Limit: "* (0%);"Lower Limit: "* 0%;
The * and space after the “Lower Limit: ” in the above syntax would just repeat the space between the text and %, to make the values more presentable. The first argument is how positive percentages are formatted. The second argument is how negative percentages are formatted. The third argument is how 0% is formatted.
The percentages would then display as the following in the Cells:
Sources:
Comentarios