How to use rectangular range as chart data

posted Sep 13, 2013, 3:54 PM by Krisztina Szabó   [ updated Aug 27, 2014, 3:38 PM ]
Dr. Frederick Frankenstein: Well it seems as if our mysterious violinist has disa...
[sees something]
Dr. Frederick Frankenstein: puh.
Inga: Disa what?
Igor: -ppeared.
Dr. Frederick Frankenstein: Shh.


by The FrankensTeam

The problem

Sometimes we would like to use rectangular (2-dimension, 2D) range as chart data especially when the input data is not / could not be placed in one row or one column.
Those charts which designed to plot only one value data (column, line, area chart types, etc) always use the horizontal (x) axis as a category axis, where data is distributed evenly. Only the vertical axis is a value axis and represents values.
In this case Excel will plot the rectangular range by series. You could choose if you would like to use the rows or columns as series, so you will have as many series as many rows (or columns) you have in the data table. The visual result depends on the chosen chart type. The most important common point is that all the series will be colored different.

So for these charts 2D range is not a problem, however you may want the data to be plotted as one series. It could be useful when you would like to use only one color on the chart, without setting it manually for all the series. In this article we will show you an easy trick how to do it, then we will give some illustrated examples to show you the usefulness of this trick.

The real problem of 2D ranges come out in case of charts with two value axes (xy scatter, bubble). Excel Help tells us about xy scatter charts:
“To arrange data on a worksheet for a scatter chart, you should place the x values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.”
http://office.microsoft.com/en-us/excel-help/available-chart-types-HA001233737.aspx#BMscattercharts

That means we have to find a workaround if the data looks like:


If we try to create a blank xy chart and add a new data series, Excel will allow to select x values as a rectangular sheet range with more rows and columns, but this is just an illusion.
(Without selecting any data: Insert / Chart / Scatter, then right click on the chart, choose Select data and push Add)


X axis could be treated as category axis in xy charts too. If Excel is not able to recognize the x values as plottable values, it will use your data as concatenated text: you can see on the picture 2 3 4 is separated by space character. This text string will be distributed evenly along the axis - similarly to a line chart - so the result will not be correct!

(Fortunately) when you try to do the same with y values - you will get an error message:


The sheet range for y values could definitely be only one row or one column.

Solution with defined names

The key of the solution is that charts work with array of numbers, but Excel does not automatically convert 2D ranges to arrays. We have to create named formulas to do this conversion “manually”. So go to the name manager (Ctrl+F3) and create two names:
x_array=Sheet1!$B$3:$D$7*1
y_array=Sheet1!$B$11:$D$15*1

Important that it is not enough to use names for the data ranges: an operation must be done to force Excel converting the data from range to array of numbers, that is why we use multiplication by 1.

After defining the two names, we can use it as series data. Simply go to the Edit series menu, and write the names to the x and y values fields as you see below and the chart will work as we wanted:


Of course you can use different references for x and y values: you can use a single row for x values and a single column for y values - the important thing is the number of elements. Also you can combine named formula and range reference similarly.

If you would like to connect the data point with line, you need to know that Excel will draw one continuous line row by row, so the last data of a row will be connected with the first data of the next row:


You will have exactly the same chart if the data comes from one row:


In general, we can say that the above mentioned named formula is valid for all the chart types. If it makes sense, you can create other chart types using named formulas. The data will always appear as it was placed in one row. This way you have only one data series on the chart, so only one color.

Example

The array technique could be for simple cases too: you can easily and clearly visualize trend of data from 2D range.
For example you have this data:


If you create a column chart on the usual way, using the rows as data series, and set the column gap, you can have this chart:


And this is how it looks like using only one data series as named formula:
data=Sheet1!$C$3:$F$7*1
and also a named formula for axis labels:
axislabels=Sheet1!$B$3:$B$7&" "&Sheet1!$C$2:$F$2
Simply the row and column headers are concatenated.


The difference is more visible using a line chart. Using this chart you can not put the quarters side by side, only above each other.
Using the same named formulas as above, it is possible to create a trend line:



But you can not do it with more data series:


Update - solution for XY scatter charts without names

We received the below explanation from Jon Peltier of http://peltiertech.com - many thanks for the supplement!

When you select the block of data in the Select Data dialog, select the first row of the block, then hold Ctrl while selecting the rest of the blocks.
The Series X and Y Values in the dialog will appear as:

=(Sheet1!$B$3:$D$3, Sheet1!$B$4:$D$4, Sheet1!$B$5:$D$5, Sheet1!$B$6:$D$6, Sheet1!$B$7:$D$7)

and

=(Sheet1!$B$11:$D$11, Sheet1!$B$12:$D$12, Sheet1!$B$13:$D$13, Sheet1!$B$14:$D$14, Sheet1!$B$15:$D$15)

and the series formula will look like:

=SERIES("testing",(Sheet1!$B$3:$D$3,Sheet1!$B$4:$D$4,Sheet1!$B$5:$D$5,Sheet1!$B$6:$D$6,Sheet1!$B$7:$D$7),(Sheet1!$B$11:$D$11,Sheet1!$B$12:$D$12,Sheet1!$B$13:$D$13,Sheet1!$B$14:$D$14,Sheet1!$B$15:$D$15),1)

This works great with the XY example, not so well with the bar chart.