Making different Excel Charts on the same scale

[wpk2200]

Hi-


I am working on creating a series of charts highlighting energy use over time. Each chart is for a different sector (residential, commercial, government) and shows energy use over the span of 10 years.


By default, Excel has each chart as zoomed in as possible, so any changes in overall energy use look drastic. While this may look fun, it distorts the reality of the changes in energy.


What I want to do is have each of the 3 charts be on the same scale so you can compare the changes between them.I don't want them to neccessarily have the same numbers on the y-axis. What I do want is for the intervals on the y-axis to be the same.


Is there a way to automate this? Basically what I have been doing is finding the greatest percent change among the 3 charts, and then figuring out the intervals for that chart, and using it for the rest. This is pretty tedious, and it would be great if it could be done with a click!

 

[Hui]

wpk2200


Firstly, Welcome to the Chandoo.org Forums


To solve your problem you will need to add 2 Dummy series to each chart


Near your data you will have to setup a formula to calculate the Maximum and Minimum of the 3 ranges which are charted and put that in each cell of your dummy range


The two series will be for the lower and upper range

The two series should have a line color of None and also no Markers

 

[keymaster]

Also see this tutorial by Jon for detailed idea on how to do this:


http://peltiertech.com/WordPress/consistent-axis-scales-across-multiple-charts/

 

[wpk2200]

Thanks for the advice-


I guess I am trying to preserve the scaling of the y-axis intervals, not the min and max values (the range of values across the data sets is simply too great).


Any ideas on how to apply the above idea to the issue of scaling intervals?

 

[Hui]

Wpk2200


Setting the Min and Max data sets as we discussed above and using the same data sets between various charts is the way to go.

Once all the charts have the same dummy data then set the Major Axis Units to Automatic and Excel will give the same scale between all the charts.


If you want a predefined Major Axis Unit you will need to use some VBA to manually set them.

 

[wpk2200]

Hi, thanks for the followup but either I am not understanding correctly, or am not explaining very well (both very likely).


Here's a hopefully specific example. I have 3 sets of data, each having its own chart (1 chart, 1 series). The 3 total charts will be placed next to each other on a website. So 3 unique charts, each with its own single series. The charts are as follows:


Residential Electricity Use

2003- 50,000

2004- 55,000

2005- 34,000

etc.


Commercial Electricity

2003- 150,000

2004- 165,000

2005- 148,000

etc.


Public Facilities

2003-4,000

2004- 4,100

2005- 4,002

etc.


If they all have the same y axis (4,000 to 165,000), then there will be lots of white space. What I'm looking to do is have a y-axis scale that has the same percentage difference between the minimum and maximum for each chart (calculated among the 3). However, each chart will likely have a different y min and y max from the other 2 charts reflecting the actual range of data in that series. So I have a formula entered where I find the percent difference for each series, then take the max percentage and calculate the min and max axis points for each series. I can then manually update.


My newest challenge is understanding the rounding functions to get nice, clean intervals that also fit these requirements. Also, I have yet to automate the above formulas to automatically update the chart, but can just manually enter it for now.

 

[NARAYANK991]

Hi ,


Can you upload your workbook ? From what you mention , all the calculations can be done by Excel ; what is the 'manual' part you are talking about ?


Narayan

 

[wpk2200]

Here's a sample workbook. I have to make charts for about 20 different cities, and would prefer automating this process so when the data is changed or added, all the charts don't have to be remade.


https://www.dropbox.com/s/aa1aync375anndy/energy_demo.xlsx


The end goal is to make a set of 3 charts (there are 2 sets shown on the workbook), that are scaled to one another. The viewer should be able to compare the charts- each vertical change is the same percent change for all 3 charts. I don't necessarily want them to start at 0 for the y-axis either.


Here's where I am. I have figured out how to automate the basic process of figuring out the range for each y-axis. There are now 2 challenges that I can see:


1. Is there a way to have the y-axis reflect the y-min and y-max values more precisely (right now, they just influence the automate function in the excel chart maker device) without manually entering the values in the chart formatting window?


1b. If so, is that possible to be automated over time, so that if the y-min or y-max changes, it will be automatically reflected in the chart.


2. In terms of the major unit intervals:

a) Is it possible to define the number of intervals (I would like 4 or 5 horizontal lines) automatically for each of the charts? Right now, this is the trickiest. I have to divide the difference between y-min and y-max by 4, and enter that value, then i try to make it look pretty....

b) I'm a little confused by the rounding functions. What is the formula to have the y-min and y-max round either up or down to the nearest useful multiple (like have 99,305 round to 100,000 and 1816487 round to 1800000)?