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).

excel text histogram for value

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.

excel text histogram for value

b) Select the font "Wingdings 2" for the "Below Target" column. Repeat the step for "Exceeded Target" column.

excel text histogram for value

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.

excel text histogram for value

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.

excel text histogram for value

 

You can find similar Excel Questions and Answer hereunder

1) How do I change the rating numbers to rating labels?

2) How can I prevent users from entering duplicate values in a range?

3) How can I enter a text in a cell with subscript and superscript?

4) How can I have text autocomplete by typing in a short code for the text?

5) How can I have text autocomplete based on values previously entered in Column?

6) How can I clear cell after activating a routine when there is a change in value of a cell?

7) How do I find the median salary of employees with the same skillset in my Organization?

8) Is there a way I can easily identify the duplicate values in a dataset?

9) Is there a way to create an visually impactful report with stop lights indicating automatically if Target is met?

10) Concatenate number with text but keeping in number format in Excel

 

Here the previous and next chapter