Text histogram for value in Excel
For example in the table below instead of reading individual "Actual vs Target" values, can I represent them in a histogram fashion so that it is easy to identify who has exceeded target and who has not met target (also by how much).
To do it in Excel, here is the answer:
a) As a first step, create a column for capturing "Salesperson" name. To the left of "Salesperson" column add a column "Below Target". To the right add a column "Exceeded Target".
Enter the formula =C5 & " " & D5 to get the name of the Salesperson in the first row of "Salesperson" column. Copy the formula for the rest of the rows.
b) Select the font "Wingdings 2" for the "Below Target" column. Repeat the step for "Exceeded Target" column.
c) Enter the formula =IF(I5<0,REPT(CHAR(162),-1*I5/1000),"") in first row in "Below Target column. This formula repeats (REPT function) Windings 2 CHAR 162 (-1*I5/1000) times in the cell if I5 value is negative signifying "Below Target"
Copy the formula for the rest of the rows.
d) Enter the formula =IF(I5>0,REPT(CHAR(162),I5/1000),"") in first row in "Exceeded Target" column. This formula repeats (REPT function) Windings 2 CHAR 162 (I5/1000) times in the cell if I5 value is positive signifying "Exceeded Target".
Copy the formula for the rest of the rows. The table will look like the screenshot below. It is now easy to infer and compare performance of different Salespersons.