CODEWITHSUNDEEP
excelgraphexcel-2007worksheet-function
gordon613
gordon613

Reputation: 2952

Excel Chart which takes the difference between two columns for one of the series

I have a simple Excel Stacked Column chart in Excel 2007

enter image description here

I would like to display however the difference between the Exposures and Hedging Column as the blue bar

Now obviously I can do it by adding a new column and taking the chart on this new column and the Hedging Column

enter image description here

However I wondered whether there was any neat way of doing it without adding an extra column.

For instance, I wondered whether there was any way of setting one of the series to some kind of dynamic range, where this dynamic range consisted of the difference between the two columns.

Or perhaps, there is a setting in Excel which does this. However I don't think so.

Upvotes: 0

Views: 21442

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6073

Well actually, it is possible to plot data like this, which is not explicitly present in the worksheet. I'll show you how to do it, then tell you why you shouldn't.

In the screenshot below, I have some X values in column B, and some arbitrary numerical values in columns C and D (labeled "a" and "b"). I want to plot the difference between the two visible columns.

To prepare the data, on the Formula tab of the Ribbon, I clicked on Define Name. I typed a name for this Name, "Delta" and a formula defining the name:

=Sheet1!$C$2:$C$5-Sheet1!$D$2:$D$5

Since the two input ranges are 4x1 columnar ranges, the defined Name "Delta" is essentially a 4x1 columnar range, with its elements being the differences between corresponding elements in the input ranges.

I selected a blank cell removed by a few columns from the data range, and inserted a chart. With no data selected, a blank chart was created with no data.

I right clicked the chart, clicked Select Data from the pop up menu, then clicked Add in the dialog, typed "Delta" in the Series Name box, and "=sheet1!delta" in the Series Values box. Then under Horizontal Axis Labels, I clicked Edit, clicked in the Axis Label Range box, and selected B2:B5 in the worksheet (containing "A" through "D").

The result is a chart that plots the calculated values in the Name "Delta". The series formula shows this:

=SERIES("Delta",Sheet1!$B$2:$B$5,NameInChartData.xlsx!Delta,1)

Chart Data Setup

This is a great technique, isn't it? You can plot calculations without messing up your worksheet, and without showing the formulas.

And of course, this is why it is not such a great technique:

  1. Someone else will not have any idea what "Delta" is, even if they find it in the SERIES formula. The Name definition itself is not transparent.

  2. You may forget about this yourself within a few days.

  3. Worksheet ranges and formulas like those needed here are reliable and easy to understand and fix if necessary.

  4. Charts can be a little flakey, and so can defined Names. In conjunction, Charts using Names are doubly so.

The most reliable thing to do is admit that it makes great sense to calculate what you want to plot out in the open, display the formulas and results proudly, and use the distinct worksheet ranges in the chart.

Upvotes: 1

Related Questions