Dr. Frederick Frankenstein: For the experiment to be a success, all of the body parts must be enlarged.
Inga: In other vords: his veins, his feet, his hands, his organs vould all have to be increased in size.
Dr. Frederick Frankenstein: Exactly.
Inga: He vould have an enormous schwanzstucker.
by The FrankensTeam
We would like to show some tricks in Excel charts how to focus on a small change in a large population. The target is visualize the trend without overwhelming it by the total value.
Igo-r posted an example based on this idea in this discussion on Alberto Cairo’s site. Here is the solution made in Excel:
We created two versions, you can download both, and below you can read a step-by-step guide about the static version.
Download the static chart
Download the dynamic chart
The total data is 500: it will be splitted by 100, so we will have 5 rows and 100 elements in each at the bottom of the chart. This represents the total. The data of the last year is combined with the first 100 element to make the comparison easier.
Here is the data table and the series data used on the chart:
If you take a look at the numbers, you will understand how the red and grey series data was calculated. Using this data table you can easily create the chart:
Now you can see something similar:
The next step is to fill the lines with pictures. We used these two pictures:
(In Excel 2013 we realized that the chart displaying is extremely slow if you use large sized pictures, so we suggest to use relatively small ones. The above size worked well for us.)
Do the same steps with the second series and the grey man picture. You will have to change the Gap Width of the series to a relatively low number to have wider bars and better-looking pictures.
(You can observe that using this fill technique, Excel is able to illustrate decimals by cutting the last small picture within the bar.)
Now the static version is ready to use, but you can download our version too.
Using this version you can add new data and can change the total and split by values. (In our file all the yellow cells are input cells.) You may need to change the horizontal axis maximum bound according to the Split by data.
On top of that, we added an extra feature: the possibility to create gridlines only for unique values. It looks like:
Now, instead of going into details about the dynamic technique, you can download the file and learn it.
If you plan to use this chart in your workbook, here you will find how to copy it from our example file to your own file.