text histogram for value in Excel

Q12. Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

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 excel text histogram for value

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

excel excel 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 excel excel 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 excel excel excel excel text histogram for value

 

You can find similar Excel Questions and Answer hereunder

1) I want to add a trend indicator symbol next to my sales data - how can I do that?

2) Remove the apostrophe cell text values in Excel

3) I am using Excel for Project Management - how can I find End Date for a task given Start Date and Task duration considering only working days?

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

5) How can I add and link a Chart Title to a cell value?

6) How do I get the principal part of loan payment for a period given Loan amount, Rate of Interest and period?

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

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

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

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

11) I am not able to use VLOOKUP as the lookup value column is to the right of the column that has the information I need. Is there an alternative option?

12) Can I apply formatting to a number concatenated with a Text?

13) I have to determine bonus for my staff based on ratings (for each rating, specific bonus amount). In Excel, how can I set up my Bonus Table?

14) How do I get the principal part of loan payment for the year given Loan amount, Rate of Interest and period?

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