This is a simple tutorial on how to create and display a bar chart in a cell; a technique that works very well when creating management reports.
Steps:
1. In column A enter the values you want to display i.e. in cell A1 enter the value 10, in cell A2 20 etc.
2. In column B1 enter the following formula: =REPT (”n”, A1). This formula simply tells Excel to repeat the value stored in between “ “ by the number in cell A1.
3. Change the font to “Wingdings”.
4. Please refer to example 1 in the attached Excel file.
5. Should you wish to decrease the length of the bar chart simply divide “A1” in the above formula by 10 or by whatever number makes the most sense. By way of example, the formula would look like this =REPT (”n”,A1/10). See example 2 in the attached Excel file.
It should be noted that by changing the “n” in the above mentioned formula you can display different images. For example, capital “J” will display a smiling face while a capital “L” will display a sad face. See example 3 in the attached Excel file.
Dealing with Negative Values
The above formulas work well when you are dealing with positive values. However, if the value in column A is negative the graph in column B will change to a string made up of a number of different symbols thereby loosing the desired effect (See example 4 in the attached spreadsheet).
One way to overcome this limitation is by way of an IF statement like:
=IF(A21<0,REPT(”n”,ABS(A21/10)),REPT(”n”,A21/10))
Explanation of the above formula:
1. Assume the value you are trying to show in a bar graph is located in cell A21. This value is also negative.
2. The formula begins by saying if the value in A21 is less than 0 i.e. negative, then repeat “n” by the absolute value (ABS) contained in cell A21 and then divide this number by 10. By using the absolute value you are tell Excel to ignore the negative sign and treat the number as a positive value.
3. The next part of the formula tells Excel what to do if the value is greater than 0.
4. Please refer to example 4 in the attached file.
Interesting additions to the above would be to use conditional formatting to change the color of the graph to say red for negative values and to blue for positive values. Let your imagination guide you!
