In your dashboard, you may have several charts that show different but related data, and you’d like them to have the same axis scales to make comparisons from chart to chart possible. You could manually reset the axis scales whenever the data changes, or you could write some VBA code to keep them synchronized, but I’m going to show a simple and reliable way to handle this.
In the data below, there are two years of data for two different companies.
Here is all of the data plotted in a single chart.
Comparing data for a given company is most important, so I want to separate the data into separate charts for each company. But I still want to be able to compare the two companies. Here are my two charts.
Since Company 2’s data is higher, the maximum Y axis scale is larger. I could manually set the Y axis maximum for both charts to 120, but if the data changes, I’ll have to reset both charts again.
To determine what values to use, I add a small summary table near the main data table. Cells B10 and B11 compute the minimum and maximum of the data. Cells C10 and C11 show the values I will use. I want the minimum to be zero, so I simply type 0 in C10. I want the maximum to range with the data, so I enter =B11 into C11.
Copy C9:C11 and use Paste Special to add this data to each chart as a new series, with data in columns and series names in the first row (don’t worry about X values). Notice the two charts have the same Y axis maximum, because they have the same maximum value from the added series.
It’s a simple matter to format the added series to use no line, and the charts will magically stay in synch.
Te show the value of this approach instead of the manual approach, let’s add another year of data. Company 2 has really started to take off.
Here is all the data in one messy chart.
Here are the two original charts, with no attempt to synchronize axes.
Here is how the charts would look if the maximum had been manually fixed at 120. The 2012 data for Company 2 is cut off between Q3 and Q4.
However, if we make sure that our minimum and maximum formulas in B10 and B11 include the added data, the hidden series of both charts include the new maximum, so the axis scales are the same.
This is a very easy technique, applicable to line, column, and area charts (in all cases, change the added series to a line chart series). It can also be used for both X and Y scales of an XY chart if we determine minimum and maximum values for X and Y.