Do you know that there exist another way of having micro charts in one cell. This is called an incell chart. It is neat, clean and pretty fancy. It is basically another thing that will elevate you to excel stardom.
When you need to compare several sets of data, everybody can make some bar charts,pie charts,etc. But how do you make one chart that gives all the info at once, looks clean, eye pleasing and easy to understand?
Incell chart is a nice way to answer the question. Incell chart is not a built in chart or anything of that sort. It is a hack made using rept function with | character to make recurring |’s to give a bar impression.
REPT(text;number of times) function is used for having a text value repeated for the number times specified. If you write =REPT(“a”;5), you will get aaaaa.
Now lets see how it is done over an example:
We are going to prepare a chart comparing the results of an 8 question poll asnwered by 1500 participants. Our data looks like this:
We are going to compare yes and no answers for each question with a chart. Here is how it is done in traditional way:
Lets admit that this chart does a pretty ok job for the task at hand. It shows the yes/no distribution for each question and how they fare against eachother. But it is somewhat not eye pleasing even after this much formatting.
Here are the steps for incell chart version of same visualisation:
1. Place your yes values and no values side by side, with 2 columns between them (yes -> C10:C17, no -> F10:F17).
2. In the empty column next to yes values(D10), input a rept formula and copy it down for all 8 values.
=REPT(“|”;C10/10) ( /10 part is for scaling)
3. In the empty column next to yes values(E10), input a rept formula and copy it down for all 8 values.
=REPT(“|”;F10/10) ( /10 part is for scaling)
4. Now set font color for yes side(D10:D17) to green and no side(E10:E17) to red. Set font for both sides to “Playbill”.
5. Allign green side to right and red side to left.
6. Set width for both D and E columns to same value for better display.
Here is how it will look like at this stage:
Now put some formatting and indicators around to complete the look. I added:
- A title
- Numbers of questions as vertical axis.
- yes/no titles for horizontal axis.
- Removed set cell fill color to white to hide gridlines.
- Put a bold border and set cell fill inside to light gray.
Here is the finished chart:
If you liked this tutorial, you may download the incell chart example and check it for yourself.