Customising Combined Charts

Overview

In this post, I'm going to show you how to combine two types of chart from one data source and then customise the result to place images above the values for each category.

My spreadsheet records the number and species of birds that visit my garden each day, and produces a summary by day and by week. 

The data is recorded in this sheet, which is named "Tracker": 

I've converted the range of cells from column C:H to a table, and use data validation drop down lists to populate everything but the date column. This ensures I can use a range of data analysis techniques without the need to change formulas or pivot table references

The "Reports" tab of my workbook contains two charts; the Daily Visitors chart is a simple stacked column chart, while the Visitors This Week chart is a combination of an original clustered column chart and a customised line chart with markers. This is what we'll be looking at in detail.

Between these two sheets, I have a tab named Data, which contains the behind-the-scenes tables and worksheet functions that are the source data for the charts we are going to make. This ensures that the report tab retains a clean look with no visible reference data. As you can see, it's not very tidy, but that doesn't matter as it's not going to be seen:

There are two tables in the Data tab. The table on the left contains a count of each species by day, and the table on the right contains the total for each species for the whole week.

In the left table, I have used a series of COUNTIFS functions that look at the data in the Tracker sheet and calculate the number of species for each day of the week. This table is used by Chart 1.

In the formula bar, you can see the COUNTIFS function that is in cell O7 (where the cursor can be seen). The function uses the date in cell N5 to determine the day to look at, and checks for each species in the list in cells N7:N15.




If you don't know how to use the COUNTIFS function, then simply type the number of each species into the table.

The right table contains the weekly sum for each species in the first column of figures (you can use a SUM function to add the data from the left table, or type this in manually) and then a second column with all the values set at 10. The reason for this second column will become clear later. This table is used by Chart 2.

Getting Started

The first thing that we to do is search online for suitable images for each of the species of bird that we are going to report and then save them. Try to ensure that they are of the same size and style, and make sure that they have a white background. 

Now, in your Data tab, create a table in the same format as my left table (as seen above), and then calculate or manually enter the figures for each species by day.

Next, we'll create the first chart to show how many of each species has visited on each day of the week.

In your Reports tab, click on the Home tab, and then click on Insert; from the Charts options, select Column Chart, and then define this a Stacked Column:

You'll now have an empty chart in your worksheet. Right click anywhere inside the border and from the quick access options choose Select Data. Now, go back to the Data tab, and select everything that's inside the table that we created for Chart 1. Before you click OK, switch the Rows/Columns so  that the chart displays in the format we want. Once you have clicked OK, you'll have a chart that looks like mine, which you can now format as you like.

Getting to grips with Chart 2

Now that Chart 1 is finished, we can start Chart 2 by creating a table in the Data tab that will provide the source data. As you have already seen, the format of this table is slightly different in that it doesn't record days of the week (this chart is a weekly summary), and that there are two columns of figures:

The first column of figures is the weekly total for each species which is added up from the table for Chart 1. The second column of figures, set at 10 for each species, is what we will use to make Chart 2 behave in exactly the way we want it to - you'll see how this works later.

Now, in the same way that we inserted Chart 1, add a chart to your Reports tab, but this time select Column Chart, Clustered. It should look like this when you've selected all of the data in the table for Chart 2:

The basic Chart 2

Note that unlike Chart 1, Chart 2 has two series; that's because we have two columns of figures. Series 1 is the first column of figures in our table (which is the sum of of each of the species recorded during the week), and our chart shows this as blue bars with the name of each species at the base (this is called the Horizontal Category Axis). Series 2 is the second column of figures in our table (which we set at 10 for every species) and our chart shows this as blue bars sharing the species name with Series 1 in the Horizontal Axis.

Making the switch

We now need to change the type of Chart Type that applies to Series 2 of our chart while maintaining the Chart Type for Series 1. Here's what we need to do:

Click in the chart area to activate it, and then right click on one of the red (Series 2) bars. You'll be shown a series of options, and the one we want is Change Series Chart Type:

Select Change Series Chart Type from these option











The type of chart that we want for Series 2 is a Line Chart with Markers:

Select the Line with Markers chart type






Click OK, and you will see that your chart now looks like this:

We now have a combined bar and line chart: Series 1 as a Bar Chart for each of our bird species, and Series 2 as a Line Chart with Markers. As all of the values in the table for Series 2 equal 10, the line chart is completely flat, and positioned above Series 1 bars. To finish our chart, we'll format it by following these steps:

Series 1

Remove the Legend that shows the Series names (Series 1 and Series 2) by clicking in it to activate it and then using your keyboard Delete key to remove it

Click on the Home Tab in the Excel Ribbon, and then click on one of the blue Series 1 bars to activate the Series. You can now choose the colour fill of the Series 1 bars by using the fill colour palette in the Font area of the Home tab.

Series 2

Click anywhere on the line chart for Series 2 and first of all, remove the line itself:

 Right click one of the markers that are all that is left of our line chart, and make sure that this has activated all of the markers (a single click will activate the whole series of markers, whereas a double click will activate the selected marker only).

Remove the marker line:

Remove the marker line

Select a square marker from the inbuilt selection, and increase the size to 18 points:

Now we've applied this format to all of the markers in the Series, we need to give each one an individual image.

Click anywhere outside the chart area to deactivate the markers, and then go back and double click on the first marker in the Series.

With this marker still selected, right click inside it and choose Picture Fill, and then click Insert From: File

Browse to the folder where you saved the bird images that you downloaded, and select the one that matches the species that is first in the Series 1 bar chart. Click OK, and you will see that the image appears in place of the marker. Because we removed the marker line, and the image has a white background, the only thing we see is a colorful profile of the species. Repeat this for each marker in the Series.

When you have formatted all of the markers, your chart should now look something like this:

I hope you enjoyed the post - if you have any questions or need any help, get in touch. 

In the upcoming posts, we're going to look at how to get the birds to hover and perch, and then we're going to introduce some worksheet functions and even some Visual Basic to make a really dynamic report!