Using stacked images for your Excel Charts

Create colourful, engaging charts using stacked images in this comprehensive Excel walk through.

It will come as no surprise to any regular visitors that I love finding creative ways to create different chart types.

In most cases, we’re really bending the way Excel charts handle ranges of data, and the ability to really manipulate how charts are presented and formatted to create our desired effect –and today it going to be no different, bringing a few of these techniques into one place.

The effect we’re aiming for is to display our chart columns as a set of stacked items – in this case, fruit.

Actually using an image for your chart is very simple – but to create the ‘stacked’ effect, we need to be a bit more creative in our approach.

So here’s my data;

And I can convert this into a simple column chart as normal

To get an image in, we create an image we want to use, and paste it into excel – anywhere will do, but we need to use it a few times, so somewhere close

Once you’ve pasted it into Excel, select it, and copy it [Ctrtl+C]

Now, select your chart data by selecting one of the columns. With the columns now selected, we want to paste – [CTRL+V]

So whilst this demonstrates just how easy it is to use images, it’s not quite what we had in mind, is it?

To get the stacked effect, what we actually need is a stacked column chart

We need to go back to our data and make some changes.

What we want, is to segment our sales data into clusters, which we can then stack on top of each other.

The first step is to decide your ‘segment’ size. Your segment size will depend on the size of your data values, the size (and aspect ratio) of your image, and the look you’re after. A starting point might be your max value / 10 – but we’re going to set things up so we can easily change this parameter value.

Once you’ve decided, enter that value into an empty cell, and give that cell the name ‘SEGMENT’ (do this by selecting the cell, and then entering the name into the Name box to the left of the formula bar – over-write the cell reference that will currently be in there. I’ve gone with a value of 15.

Now we have our segment value, we need to divide our data into segments based on that segment size

Your First segment will want to use the MIN formula, to find the smaller value of the SEGMENT or the SALES

Eg

=MIN(H14,SEGMENT)

Your subsequent segments will use a similar formula, but looking for the MIN value between the SEGMENT or the Sales minus the segments we’ve already defined

Eg

=MIN(SEGMENT,($H14-SUM(I14)))

Repeat this until all your segments return 0 for all dates

We are now ready to create our chart again. Select your data (you can ignore the segment with no sales left in it if you wish) and insert a stacked column chart

And you’ll be presented with a colourful chart similar to this;

You can see that the large light blue value relates to our total sales value. We no longer need this, so select one of the blue values to highlight the series, and press delete to remove.

We now have a chart made up of evenly sized stacked segments.

We can now repeat the process we learnt earlier. Copy your image again – [CTRL]+C

Select your first data series, and enter [CTRL]+V

Repeat this for each series in your chart

At this point, if you’re not happy with the way the image is being displayed, you can go back and adjust your segment size – or you can adjust the picture, or even the column widths

To do this;

Right click your data series à Format Data Series

Adjust the Gap Width to a level which provides the right shape for your image

Once you’re happy with the image layout and sizing, it’s really then a tidy-up exercise. Removing the Gridlines will give a cleaner look (unless your images have a transparent background)

To do this, select the gridlines à [DELETE] key

And as it doesn’t make much sense to this chart, remove the legend by selecting it, [DELETE]

Give your chart a title, and you’re done (for now)

You may well find that unlike my perfect set of data, your data doesn’t always fall into equally sized segments. In these cases, your picture will resize and distort;

You have a couple of options. If you’re happy with how it is, then by all means leave it! Otherwise, your best option it probably to round your last segment up or down to the segment size value, using a FLOOR or CEILING function.

=CEILING.MATH(MIN(SEGMENT,($H15-SUM($I15:J15))),SEGMENT)

(be sure to apply this formula to all records)

Alternatively, if you wanted to show a portion of the image, this is possible too, but a few more steps are required.

Firstly, use the above CEILING function to round values up to the segment value. Now, add an additional field to your data, which calculates the difference between the value in your last bucket and the SEGMENT value

=SUM(I15:O15)-H15

Now, go back to your chart and add two new data series – your new GAP value, and your original total SALES value.

You will probably having something messy like this

Now, right click an empty area of your chart, and select ‘Change Chart Type’

Select COMBO as your chart type

On the right, you want to ensure every series is set as Stacked Column

Now, for the two extra series we added (sales & gap), tick the Secondary Axis box and click OK

We are now ‘masking’ our original data, and simply need to remove the mask.

First, select the series representing your Sales data (blue in my case).

Right ClickàFormat Data Series

On the ‘Fill & Line’ section, change Fill to No Fill, and Border to No Line

Now select the ‘Gap’ series à Format Data Series à Fill & Line

This time, select WHITE as your fill colour and border colour

Finish off my removing the secondary axis scale, by selecting it [DELETE]

And there you have it! If you feel really inclined to do so, you could probably even use a transparency gradient filter to fade the last image in/out – but I’ll let you figure that one out!

And you needn’t stop with one image – use the same technique for multiple images