Create charts directly in a range

This post will be talking about how to create non-graphic chart without using the default chart templates in Excel.  This method use combination of functions to display bars in the call corresponding to the data.

In the screen-shot above, I have data containing targeted (column B) and actual (column C) number of calls made by agents  in an assessment period. Column D represents, the variance of their performance (in %). 

Functions I have used to create this are IF, REPT, FLOOR & ABS.

To re-create this chart in Excel, please have your data handy or enter the data shown in columns A through D, and then write the following formulas:

E2: =IF(D2<0,REPT("g ",FLOOR(ABS(D2*100),1)),"")

F2: =A2

G2: =IF(D2>0,REPT("g ",FLOOR(ABS(D2*100),1)),"")

I have used webdings font to cells E2 an G2, but you can play here with your imagination (can insert as well). Copy the formulas and paste till the end. Right-align the text in column E, and Left-align in column G.

To make the chart presentable specially when your data contain large variances, you will have to adjust the scaling.  That can be achieved by dividing your result of FLOOR function by any number that suits your presentation.

For example in your function;

E2: =IF(D2<0,REPT("g ",FLOOR(ABS(D2*100),1)),""),  "FLOOR(ABS(D2*100),1)" is giving you a high value such as 50 to make it presentable you can adjust the function by diving the result by 5.  So the new function will be IF(D2<0,REPT("g ",FLOOR(ABS(D2*100),1)/5),"") you can change this divisor to any number you want.

I hope you learnt something interesting with the post. "Happy Learning"