Charts without cells, part II
- Jun. 09, 2014
 | Example files with this article: |
Introduction
This is the second part of the article on how to plot data in a chart without storing the data in cells on a worksheet. Please refer back to part I of the article to understand the situation and which chart we are creating. I will now show you all necessary steps to set up such chart and testing your formulas while you are creating them.
Step-by-step instructions
Save your file from part I. Remove the cells A7:E40 by selecting them and clearing data and formats. Your beautiful chart will now look empty, what a pity of all your hard work! ;-)
Select cell B2 and give it the name n_A. This stands for the number of category A.
Repeat for categories B (name is n_B for cell B3) and C (name is n_C for cell B4).
On our scatter chart, let's plot 2 series for category A, 2 series for category B and again 2 series for category C. For category A, the first series will be as follows:
| X-values | Y-values |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
Again for category A, the second series will be as follows:
| X-values | Y-values |
| 2.5 | 1 |
| 2.5 | 2 |
These values were the values in the cells in part I of the article. You see that we have 5 data points for category A: (1, 1), (1, 2), (1, 3) is the left bar for A, containing 3 boxes/rectangles (2.5, 1), (2.5, 2) is the right bar for A, containing 2 boxes/rectangles
As such, we create 4 defined names:
| Defined name | Contents |
| x_1 | {1, 1, 1} |
| y_1 | {1, 2, 3} |
| x_2 | {2.5, 2.5} |
| y_2 | {1, 2} |
That's right, we are talking about arrays. Each set of X coordinates is an array, just as each set of Y coordinates. We will create the arrays using formulas and when you plot them in the scatter chart, it will work! Stay tuned if you don't believe me.
Setting up the defined name y_1 is not too difficult. Press F3 to go to the defined names. Create a new name (y_1) and use this formula: =ROW(OFFSET('Sheet1'!$A$1,,,CEILING(n_A/2,1)))
Important remark: for users with a non-English Excel installation, the formula could look different. Download the file at the top of the page to know the correct formula.
We are telling Excel to generate an array containing (in this case 3) numbers. From the inside to the outside:
- n_A/2 is 5 divided by 2
- we use the CEILING function to round up the result of 2.5. Intermediate result is 3.
- OFFSET starting in cell A1 and using 3 rows gives us the range A1:C1 as intermediate result.
- We use the ROW function to convert range A1:C1 to an array containing the row numbers: {1, 2, 3}
There we are, a dynamic array of in this case 3 numbers: 1 t(w)o 3! It happens to be 3 since category A has a value of 5: we split that 5 into 3 + 2. If category A would be changed to 9, it would be 5 + 4 and y_1 would be {1, 2, 3, 4, 5}. If category A would be changed to 8, it would be 4 + 4 and y_1 would be {1, 2, 3, 4}. You get the picture :-) Do you appreciate the dynamic nature of the arrays thanks to the functions OFFSET and ROW?
Hold on. We are writing formulas, but how you check them? Here are a couple of possibilities:
- In an empty cell, type the formula: =COUNT(, then press F3, select defined name y_1 and hit Enter twice. The result should be 3 as y_1 contains 3 numbers.
- If the result is not 3, select the cell again and go to Formulas > Evaluate Formula. Go in the formula and inspect the contents of array y_1.
- To bring array y_1 to cells on the worksheet, select 3 cells vertically. Type an = sign, then press F3, select defined name y_1 and hit Enter. Then hit Ctrl-Shift-Enter
When you understand this principle of creating arrays, let's move on to array x_1. We need again 3 values in the array, now all have the value 1. This formula will do:
=y_1*0 + 1
The first part of the formula (the one where we multiply with 0) tells Excel to use as many elements as we have in the array y_1. This is important. All values in y_1 are taken but set to 0. Last part is to add 1 and we effectively get an array of 3 elements, all having a value of 1. Similar tests as above can demonstrate this.
Plotting the 2 arrays
Setting up formulas to calculate arrays is meaningless if we don't plot them on the chart. Right-click the chart area, choose "Select data…" and add a new series as follows:
Don't be fooled by the picture which shows the filename! Here is what you need to type for the series X values:
- an = sign
- then the sheet name instead of the filename (save your file first)
- then the exclamation mark !
- then x_1 (the defined name)
- for y_1, proceed in a similar way but use y_1 instead of x_1, obviously
Other chart series
The next task is repeating the above steps for the second series of category A, and for 2 series of categories B and C. The formulas will be different, but analogous:
| Defined name | Formula |
| x_1 | =y_3*0 + 1 |
| y_1 | =ROW(OFFSET(Sheet1!$A$1,,,CEILING(n_A/2,1))) |
| x_2 | =y_2*0 + 2.5 |
| y_2 | =ROW(OFFSET(Sheet1!$A$1,,,INT(n_A/2))) |
| x_3 | =y_3*0 + 4 |
| y_3 | =ROW(OFFSET(Sheet1!$A$1,,,CEILING(n_B/2,1))) |
| x_4 | =y_4*0 + 5.5 |
| y_4 | =ROW(OFFSET(Sheet1!$A$1,,,INT(n_B/2))) |
| x_5 | =y_5*0 + 7 |
| y_5 | =ROW(OFFSET(Sheet1!$A$1,,,CEILING(n_C/2,1))) |
| x_6 | =y_6*0 + 8.5 |
| y_6 | =ROW(OFFSET(Sheet1!$A$1,,,INT(n_C/2))) |
I bet good money that you will understand it without any further explanations from my side :-)
Again, add the (5) series to the scatter chart. The result should now something like this:
Copy in the shapes for our custom markers and the end result is as follows:
Additional exercise
Rather than using 6 series (12 defined names), try setting up the (more difficult) formulas for defined names such that we can suffice with 3 series (6 defined names).
End of story
Thank you for following along during this exciting exercise to create a chart without storing its data in worksheet cells!
