So that we all understand, what I intend to show you is a chart like the following:
The band represents the area between the maximum and the minimum for all products for any one year.
It can also be seen that it is a dynamic chart, each time a product is selected one series in the chart change (could be more if you wanted).
To build a band chart like that one we need to follow a few steps. Let's see how.
The first thing we need is obviously data. I have built a table with made-up data to show you the process. Imagine the average prices of 15 products from 1994-2010, something like the following table.
This table would be located in the range B5:S20, being the range B5:S5 where the headers of each column are.
Unfortunately we often see a very poor visual representation of that table:
It is clear that a visual representation as we just saw is just bad. It does not convey anything but confusion. So let's see how you can present that data in a simpler way to digest and make a display with bands like this.
Step 1 - add some calculations
We will create a new table that is fed from the original table but only with the required data.
The table will have five series.
1. The first will have the values of the chosen product to be shown. This product will be chosen from the drop-down list which is then introduced.
To bring chosen product values a formula will be used:
=INDEX(data!B6:B20,$C$11)
The range B6:B20 is where the list of products in the original table is set, and the C11 cell is the cell linked to the drop-down list, so that the number 13 indicates that the value in 13th position has been chosen in the drop-down list. We now need to copy that formula across to get the values for each of the years.
=INDEX(data!C6:C20,$C$11) for 1994
=INDEX(data!D6:D20,$C$11) for 1995 ...
2. The second series is the series of minimum values. We now calculate the minimum value for each year in the original table. The formula is:
=MIN(Data!C$6:C$20) for 1994
3. The third series is the series of maximum values. We now calculate the maximum value for each year in the original table. The formula is:
=MAX(Data!C$6:C$20) for 1994
4. The fourth series is the series for average values. We now calculate the average for each year in the original table. The formula is:
=AVERAGE(Data!C$6:C$20) for 1994
5. The fifth and final series will be the difference between the minimum and maximum we have just calculated and will be called min-max-range. The formula is:
=C7-C6 for 1994, with C7 maximum calculated in point 4 and C6 minimum calculated in point 3.
If you have questions about the above functions I recommend you checking the following links:
Step 2 - insert a line graph and clean it
Selecting the table created in step 1, go to Insert> Line chart
We clean the chart a bit by removing gridlines, the border line etc. although all this is more up to your liking.
Also exclude the maximum series since it is not necessary to show it.
Step 3 - change the chart type
This step is really important, what we basically do is having series in the same chart that are shown with different chart types. In our example, what we will do is show the minimum and max-min-range series as stacked area chart type and product and average series will still be shown as line chart.
To make the change, simply click on one of the series and go to Change Chart Type.
The resulting chart would be like this:
Step 4 - we format lines and areas
The next step will be to format the areas and lines, hiding the minimum area (unfilled) and changing the colour of the min-max-range area and the lines if you want.
A better result visually will look like this:
Step 5 - Position the axis (between tick marks)
The next thing to do is to reposition horizontal axis tick marks. To do this click on the axis, hit the right button, and we'll go to Format axis> Axis Options> and select Position Axis > Between tick marks.
Actually, what we are doing is to leave the chart a little nicer and clearer but this step is not as relevant.
Step 6 - axis name and legend
The sixth step is to format it even more. Now label the vertical axis and leave the relevant and beautiful legend, hiding the minimum series. To do so, click at least 2 times in series name in the legend and hit Delete.
Step 7- add the drop-down list to make it a dynamic chart
The last step is to add a drop-down list, which will be linked to the table in step 1 which is where the data feeds the chart. This step will convert the chart in a dynamic chart, since each product selection will make the chart change.
To add a dropdown list is advisable to see the following link:
In our case will be add it using form controls. The cell linked to the drop-down list will be the cell of step 1 C11. This cell will be very important to bring the chosen product data to the chart.
And that would be it. You now have a nice band chart in Excel.
Download the example file
To download a file containing examples of the theme we have just covered click on the following link:
No comments:
Post a Comment