Updated: Feb 20
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.