Assuming you have a table like this one:
| A | B | |
| 1 | Member | FoodUnitUsed |
| 2 | Frodo Baggins | 58 |
| 3 | Merry Brandybock | 67 |
| 4 | Peregrin Tuk | 65 |
| 5 | Samwise Gamdschie | 42 |
| 6 | Aragorn | 32 |
| 7 | Legolas | 11 |
| 8 | Gimli | 39 |
| 9 | Boromir | 28 |
| 10 | Gandalf | 9 |
Now we want to visualize the figures in column B. We will use the REPT function for this. The syntax of this function is as follows:
=REPT(text, number_of_times)
I think, this is pretty self-explanatory. The first element is the one to be repeated, the second one is the number of repititions. To produce a nice little in-cell-bar-chart we will use the pipe-symbol "|" as text and the value in column B as number of repititions.
=REPT("|",B2)
You now can format this with font-size 6 to 8 and bold to have a nice little equalizer-like bar chart. So far, so good. This tip has been spread widely across the Internet. To show that Hobbits (yes, this is a Lord of the Rings example) use more food than anyone else, we will additionally utilize some conditional formatting. The MSXL2007 conditional formatting manager looks like this:
So the final table is set up like this one ...
| A | B | C | |
| 1 | Member | FoodUnitUsed | Visualization |
| 2 | Frodo Baggins | 58 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||| |
| 3 | Merry Brandybock | 67 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| |
| 4 | Peregrin Tuk | 65 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| |
| 5 | Samwise Gamdschie | 42 | |||||||||||||||||||||||||||||||||||||||||| |
| 6 | Aragorn | 32 | |||||||||||||||||||||||||||||||| |
| 7 | Legolas | 11 | ||||||||||| |
| 8 | Gimli | 39 | ||||||||||||||||||||||||||||||||||||||| |
| 9 | Boromir | 28 | |||||||||||||||||||||||||||| |
| 10 | Gandalf | 9 | ||||||||| |
| Formulas in the table | ||||||||||||||||||||
|
Display Excel tables online >> Excel Jeanie HTML 4
... and the view will show up like this (because of the strange display of the above image):
You can of course use this function with some other - more symbolic - fonts like Wingdings. Just imagine you want to visualize telephone calls, simply use the left parenthesis, which is a little telephone icon in the Wingdings font, and you get something like the following:
This function is pretty nice, isn't it? What purposes do you / would you use it for? Simply join the discussion and leave a comment below.
Discuss this blog post in the forums (click the blue button)
