Multiple lines across categories - simple step by step guide

posted Feb 12, 2014, 3:08 PM by r   [ updated Feb 12, 2014, 3:09 PM ]

Medical Student: Well sir, I'm not sure I understand the distinction between reflexive and voluntary nerve impulses.
[...]


by The FrankensTeam

We started by reading the tutorial by Jon Peltier and the initial question on HelpmeViz. The basic problem is to add panel-lines representing a new data series to a column chart where the columns are grouped to 12-month series by different countries.
As you can see in Jon's tutorial, the original data table is not applicable for the combined column-line or line-line chart.
Jon presented how the tables could be done but had not shared a file ... so we downloaded the original, and have worked on it.
Below we will show you the whole process from the first step: how to the create the support tables (based on two different data table structures), then continue by showing how we created the charts: some (small and large) differences compared to the tutorial by Jon.
With just 10 simple steps we can create these beautiful graphics.

Here is our step-by-step "how we did it" guide.

Add names

You can see the first table structure below (as in the downloaded file: countries are in rows)
Define the below names on the data:

rng_1 =$C$4:$N$12
rng_2 =$C$14:$N$22
states_1 =$B$4:$B$12


Add formulas

You can find the pre-written formulas on sheet Support_1 - as you can see below:
(The first row contains error - it is normal, after copying the formula, you will see the structure of the data.)


Formulas are:
A2 =IF(E2=INT(COLUMNS(rng_1)/2),INDEX(states_1,F2)," ")
B2 =INDEX((rng_1,rng_2),$F2,$E2,COLUMN(A1)) and drag in C2
D2 =0
E2 =MOD(ROW(A1)-2,COLUMNS(rng_1)+1)+1
F2 =INT((ROW(A1)-2)/(COLUMNS(rng_1)+1))+1

note:
If you have 3 or more blocks of data ... You can drag the formula of B2 to more columns ...  then add the range in the first argument of the INDEX function.

Drag formulas

Drag down A2:D2


Error values will appear from row 119:


So let's stop at line 119 including last line with error:


Now select the range: start from D119:


and with Ctrl+Shift+Left arrow and Up arrow to select A1:D119.

Add the chart

Insert an XY Scatter chart: lines and no markers:


Now you can see this:

ummm.... blood dipping? or nerve impulses? :-)

Setting x axis and vertical gridline

Set the X axis boundaries from 1 to 9 ×13 + 1 = 118 and units to 13 so it has a tick label (and vertical gridline) in every 13th unit, .

You can get 13 by this formula
=COLUMNS(rng_1)+1
and 118 by this formula
=COUNTA(rng_1)+ROWS(rng_1)+1



Add vertical gridline.
Delete x axis.

This is the result:

Add the axis label

The series "label" serves as holder of labels for the groups, so as x axis labels.
Select series "label".
Set the color of the line to No line.
Add datalabels to the series with Below position.


The label should show the x values.



Filter and clear errors value

Add Autofilter to the formulas and filter only the #REF! error values in column B:


Apply the filter, so you can see only the rows with #REF! error:


Delete all these formulas:



Remove the filter, so you can see empty rows in the table:


And the result for the chart: gaps appear between the country-groups:



Final clean up

Delete Label series name from legend.
Zoom the chart and choose colors and styles you like. The final result can be something similar:


Column and line chart

Select series "blu" and change the chart type to Column chart (in Italian: Istogramma)



The result is this:


Set the x axis options: write 13 to Interval between tick marks and also to Specify interval unit. Set the Position Axis to On tick marks.


Decrease the gap between the bars  to 0 and add a white border to the bars, so this can be the result:


Step 1 and 2 for different data table structure


The second data structure that we consider (which I prefer) is a table with the states in the column header, you can then append new data blocks


Define the names of the ranges:

rng_3 =Table_2!$B$5:$J$16
rng_4 =Table_2!$B$17:$J$28
states_2 =Table_2!$B$4:$J$4

Add formulas in sheet Support_1



A2 =IF(F2=INT(ROWS(rng_3)/2),INDEX(states_2,1,E2)," ")
B2 =INDEX((rng_3,rng_4),$F2,$E2,COLUMN(A1)) and drag to C2
D2 =0
E2 =INT((ROW(A1)-2)/(ROWS(rng_3)+1))+1
F2 =MOD(ROW(A1)-2,ROWS(rng_3)+1)+1


The formulas are very similar to the previous ones ... from this point you can repeat the steps already described above.

Download

You can download the template file with formulas.
The first file contains 2 different tables, names and first row with formulas on support sheet. You can use it to practice with the creation of graphics.
The second file contains both charts too.



Ĉ
r,
Feb 12, 2014, 3:08 PM
Comments