Visualize small change in large number - split the data

posted Dec 16, 2013, 7:28 AM by Krisztina Szabó   [ updated Sep 24, 2014, 3:29 AM ]

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.

The problem

How to visualize the growth of the proportion of women CEOs in Fortune 500 companies? This is a typical problem of large numbers: the population consists of 500 companies, while the number of women changes from 0 to 20. The change is significant, so there is a need to visualize it but also highlight that it is a only a small part of the total. The base idea is to split the total data to more rows to make the maximum bound of the chart axis lower - this way the important small values are clearly visible. The remainder range above the small value appears only in the last year.
Igo-r posted an example based on this idea in this discussion on Alberto Cairo’s site. Here is the solution made in Excel:

On the chart you can see the growth from year 1996 to 2013 but also have an impression about the total 500 if you take a look at the grey crowd.

Excel chart solutions

This chart could easily be done in Excel using a stacked bar chart type where the bars are filled with pictures.
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

Static version step by step

Let’s start the explanation with this simplified example:
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:
  • Select the red series and grey series range
  • Insert a stacked bar chart
  • Under the Select data menu of the chart set the Horizontal (category) axis labels: select the years range (no need to include the empty cells)
  • According to your needs, format the vertical axis and set Categories in reverse order

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

  • Select the lady-picture, and copy it (Ctrl+C)
  • Select the appropriate data series on the chart (the blue)
  • Go to the Format data series menu and choose the Picture or Texture fill option, then click on the Clipboard button and set the Stack and scale option to 1 as you can see on the below screenshot.

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.

Dynamic version

To change the chart to be dynamic you will only need to implement some named formulas and change the chart series to point the named formulas instead of sheet ranges.
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.

Leave a comment