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 down A2:D2 Error values will appear from row 119: ummm.... blood dipping? or nerve impulses? :-) 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 labelThe 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.Apply the filter, so you can see only the rows with #REF! error: Delete all these formulas: Delete Label series name from legend. 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 structureThe 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. 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. |