### 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.

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`

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:

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

Insert an XY Scatter chart: lines and no markers:

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`

Delete x axis.

This is the result:

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.

Add Autofilter to the formulas and filter only the #REF! error values in column
Apply the filter, so you can see only the rows with #REF! error:

Delete all these formulas:
And the result for the chart: gaps appear between the country-groups:

### Final clean up

Delete Label series name from legend.

### Column and line chart

Select series "blu" and change the chart type to Column chart (in Italian: Istogramma)
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`

`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.