Two tables with common column displayed in one chart

In this example we have data from some water pump. We were observed 2 variables (kW and water volume). So there are two data series measured in time, but in different time intervals. First measurement started at 10:00 and continued at 5 minute intervals (left table). Second measurement started at 10:13:30 and continued at half minute intervals (right table). We want Excel to show relationship between these two data series in one chart.

How to show these tables in one chart?

Solution

Step 1

Make a XY Scatter chart with smooth lines. Data source is table one (A1:B11).

Step 2

Add new data series into this chart.

Go to the Chart Tools (Options), choose Select Data

and then choose button “Add” to add new series (Legend Entries).

Now you must edit series window.

The first item (Series name) is cell “Water volume”, the second item (Series X values) is column with time and the third item (Series Y values) is column with volume values.

Now we have two data series in one chart.

This is not very useful view. Both of data series share one Y-axis. In the next step we split them into two Y-axes.

Step 3

Right click on one of the data series (line) and choose “Format data series”.

Now switch “Plot Series On” to Secondary Axis.

Excel adds another Y-axis to the chart. Now you can see the relationship between data. Values from the first table are on the right Y-axis and the line of them is blue. Values from the second table are on the left Y-axis and the line is red.

Watch this in video