# Building a panel chart by hand in Excel This is the same chart the skill generates, done manually. Follow it if you want to understand the construction, adapt it to something the builder does not cover, or rebuild it in a workbook you cannot run Python against. The worked example is a **2 rows x 3 columns grid of line panels, 12 periods, 2 elements** (Actual and Plan). Everything scales to other shapes; the arithmetic is in step 3. > **The one idea.** A panel chart is not many charts. It is one chart whose > value axis has been divided into horizontal *bands*, one per grid row, with > each panel's data mathematically squeezed into its band. Everything else - > the dividers, the panel titles, the numbers down the left - is drawn by > extra series pretending to be chart furniture. --- ## Step 1 - Lay out your data as a plain rectangle Put your real data somewhere sensible and leave it alone. This is the block you will maintain; nothing downstream should ever be edited by hand. | | A | B..M | N..Y | |---|---|---|---| | 4 | | **Actual** | **Plan** | | 5 | **Panel** | Jan..Dec | Jan..Dec | | 6 | Partner 01 | 12 values | 12 values | | 7 | Partner 02 | ... | ... | | ... | | | | | 11 | Partner 06 | | | Panels fill the grid **left to right, top row first**: row 6 is the top-left panel, row 8 is the top-right, row 9 starts the second grid row. --- ## Step 2 - Work out the shared scale Every panel must use the same scale or the grid lies. In spare cells: | Name | Formula | |---|---| | `min raw` | `=MIN(0,MIN(B6:Y11))` | | `max raw` | `=MAX(B6:Y11)` | | `step` | `=10^INT(LOG10(MAX(1E-9,max_raw-min_raw)))/2` | | `vmin` | `=FLOOR(min_raw,step)` | | `vmax` | `=CEILING(max_raw,step)` | | `span` | `=MAX(1E-9,vmax-vmin)` | | `band frac` | `0.82` — how much of each band the plot fills, leaving a gap | The `step` rounding is only there to make the tick labels read as round numbers. Forcing `min raw` through zero matters more than it looks: if you ever switch these panels to columns, columns measured from a non-zero floor are simply wrong. **If you are building stacked areas, `max raw` must be the maximum of the per-period stacked TOTAL**, not of the individual values, or the fill will overflow its band into the panel above. --- ## Step 3 - Build the plot table This is the layout the chart actually reads. One row per **category slot**. ### How many slots? ``` slots = lead + blocks x (periods x subslots) + (blocks - 1) x spacers ``` - **blocks** = grid columns (3 here). These run along the category axis. - **bands** = grid rows (2 here). These are the value-axis offsets. - **subslots** = 1 for line and area. For **column and bar it is the number of elements**, because two clustered columns need two places to stand. - **spacers** = 1 between blocks (2 for area and bar - see step 8). - **lead** = 3 empty slots at the left, reserved for the tick labels. You cannot make room by widening the axis; see step 6 for why. For the example: `3 + 3 x 12 + 2 = 41` slots. ### Helper columns For each slot `x` = 1, 2, 3...: | Column | Formula | Meaning | |---|---|---| | `pos` | `=x-lead` | position past the lead margin | | `blk` | `=IF(pos<=0,-1,INT((pos-1)/(block_slots+spacers)))` | which panel block | | `sub` | `=IF(pos<=0,-1,MOD(pos-1,block_slots+spacers))` | position inside the block | | `k` | `=IF(OR(sub<0,sub>=block_slots),-1,INT(sub/subslots))` | which period | | `e` | `=IF(OR(sub<0,sub>=block_slots),-1,MOD(sub,subslots))` | which element's slot | | `sep` | `=IF(OR(pos<=0,sub<0,sub>=block_slots,blk>=blocks),1,0)` | is this a gap? | | `cat` | `=IF(sep=1,"",IF(MOD(k,3)=0,INDEX($B$5:$M$5,k+1),""))` | axis label | The `MOD(k,3)` thins the axis labels to every third period. With three panels of twelve months side by side, labelling all 36 is unreadable. ### Value columns One column per **(band, element)** - four here. Bottom band first; the order matters once you add stacked kinds. For band `b` (0 = bottom) and element `e`: ``` =IF(sep=1, NA(), b + (INDEX($B$6:$Y$11, (rows-1-b)*cols + blk + 1, e*periods + k + 1) - vmin) / span * band_frac) ``` Reading the INDEX: `(rows-1-b)*cols + blk + 1` is the panel's row in your input block. Band 0 is the **bottom** of the chart, which is the **last** grid row - hence `rows-1-b`. > ### Use `NA()`, never `""` > This is the single most common way to get a panel chart that looks broken for > no visible reason. An empty string is not an empty cell: Excel plots it as a > **zero**, so every panel boundary drops a spike to the baseline. `NA()` is a > genuine gap. Set the chart's **Select Data > Hidden and Empty Cells > Show > empty cells as: Gaps** as well. --- ## Step 4 - Create the chart 1. Select nothing. **Insert > Line Chart** (an empty chart). For a pin or a dot grid insert **Line with Markers** instead; for column, bar or area insert the **stacked** variant of that type. 2. **Select Data > Add** one series per value column. Series values are the value column; the **horizontal axis labels** are the `cat` column, set once. 3. Give every series in the same element the same name (`Actual`, `Actual`, `Plan`, `Plan`) and the same colour. They are the same measure, drawn twice because they live in different bands. At this point you have a chart with all six panels in the right places, no furniture, and a meaningless value axis. --- ## Step 5 - Fix the axes - **Value axis**: set Minimum `0`, Maximum `bands` (2 here). Then hide it entirely - no line, no tick marks, no labels. It is scaffolding. - **Delete the gridlines.** - **Category axis**: set **Labels > Label Position: Low** so the labels stay at the bottom of the chart instead of floating at the zero line. --- ## Step 6 - Add the furniture as XY scatter series Everything else is drawn by scatter series on the **secondary** axes. Add each one via Select Data > Add, then right-click > **Change Series Chart Type** > Scatter, and tick **Secondary Axis**. Then set the secondary axes: - **Secondary X: Minimum `0.5`, Maximum `slots + 0.5`.** - **Secondary Y: Minimum `0`, Maximum `bands`.** - Hide both. > ### Do not widen the secondary X axis > Those exact numbers are what make **primary category *i* land on secondary > x = *i***. It is the hinge the whole construction turns on. If you widen the > range to make room for the tick labels, every divider, title and label slides > off its category and nothing will tell you. That is what the `lead` slots in > step 3 are for. Draw lines as **two-point polylines with a gap between segments**: put `(x, y_start)`, `(x, y_end)`, then a row whose **y is `NA()` but whose x is still a number**. One series can then carry every divider in the chart. > ### Do not let a scatter series outgrow the category count > An XY series overlaid on a category-axis chart draws only its first *n* > points, where *n* is the number of categories. Excel gives no warning: the > series holds every point, and simply stops drawing. The baselines are the > series that hits this, needing `3 x blocks x bands` points - 60 on a 5x4 grid > against 54 categories, which silently costs the top row's last two panels > their axis line. **If your baseline series needs more points than you have > categories, split it across several series** - one per band is the natural > cut, and it must fall between segments, never inside one. > ### Keep X numeric > If any cell in an XValues range is text or an error, Excel discards the > **entire range** and silently plots the series against 1, 2, 3... The gap > goes in the Y column only. | Series | Points | Format | |---|---|---| | **Dividers** | at each spacer slot, from y=0 to y=bands | thin light grey line | | **Band rules** | at `b - (1-band_frac)/2` for each band boundary, x from 0.5 to slots+0.5 | thin light grey line | | **Baselines** | one segment per panel at the band's zero position, x from block start-0.5 to block end+0.5 | thin mid grey line | | **Panel titles** | one point per panel at (block start, band top) | no line, no marker | | **Tick labels** | `nticks` points per band at x = `lead+0.35`, y = `band + f*band_frac` | no line, no marker | --- ## Step 7 - Label the furniture For the title and tick-label series, turn on data labels, then **link each label to a cell**: click the label twice to select just it, type `=` in the formula bar, and click the cell. - Panel titles link to the panel name cells in column A. Position **Right**. - Tick labels link to cells holding `=TEXT(vmin+(vmax-vmin)*f,"#,##0")` for each fraction `f`. Position **Left**. Linking rather than typing is what keeps the chart live: rename a panel or change the data and the chart follows. Finally, in the legend, delete the duplicate entries - click one, press Delete - until only one entry per element remains. --- ## Step 8 - Kind-specific extras ### Column and bar Each period expands into one slot per element (`subslots = elements`), so the elements stand side by side. The band offset needs an **invisible stacked base series** under each band's values, because Excel columns always grow from the axis zero. Use a **stacked** column chart, and order the series bottom band first: `base0, values0, base1, values1...`. A stacked group accumulates *everything*, so a band's base is not its absolute position but the **delta** from the running total beneath it: ``` top(b) = zero(b) + (that band's heights) base(b) = zero(b) - top(b-1), with top(-1) = 0 ``` Compute `top(b)` with `AGGREGATE(9,6,...)`, which sums while ignoring the `NA()`s. Keep the base numeric on **every** row including separators, so your arithmetic and Excel's stay in step. Format the base series as no fill, no line. Bar additionally runs the categories up the vertical axis, so blocks are grid *rows* and bands are grid *columns*. Excel draws category 1 at the bottom, so write the periods in reverse (`periods-1-k`) if you want period 1 at the top - reversing the axis instead would invert the alignment from step 6. ### Area Two problems, both structural. 1. **Elements stack**, so size the shared scale on stacked totals (step 2). 2. **A stacked area cannot be gapped.** Neither `NA()` nor a genuinely blank cell will break the fill - it runs straight through and wedges down to the baseline at every panel edge. There is no setting for this. The way round it: give area **two** spacer slots, **hold the neighbouring panel's edge value flat** across them so the join is a slope rather than a plunge, then cover that join with an opaque **mask column** - a clustered column series, white fill, `Gap Width = 0`, full band height at the lead and spacer slots and `NA()` everywhere else. A column is used rather than a thick white line because a column scales with the chart and a line weight in points does not. ### Pin A pin is a marker at the value with a thin stem back to the panel's zero line, and it is what you draw when the number *is* a variance. There is no pin chart type, and the obvious construction - a very narrow column - does not work: `Gap Width` caps at 500, which on a twelve-category axis bottoms out around a 9px stem where the reference draws 5. Build it as a **line chart with markers**, then: 1. **Hide the line** on every series (`Format Data Series > Line > No line`). The markers are the pins; the line between them means nothing. 2. **Add a custom Y error bar** as the stem. `Error Bars > More Options > Custom > Specify Value`, and point both the plus and the minus box at a **stem column** you compute alongside the value column: ``` stem = ABS(value) / span * band_frac ``` That is the same distance the value travelled from zero, in value-axis units. Set **End Style: No Cap**, and set the **line weight in points** - that is the whole reason this beats a narrow column. 3. **Write the stem column as `0`, not `NA()`, where the series does not own the point.** An error-bar range must be entirely numeric, the same rule that governs `XValues` in step 6. Break it and Excel discards the range without saying so. 4. **Split each element into two series, one per direction.** An error bar takes one colour for a whole series, so up-pins and down-pins cannot be coloured after the fact. Give each direction its own value column, `NA()` where the other owns the point: ``` up = IF(sep=1, NA(), IF(value="", NA(), IF(value>=0, placed, NA()))) down = IF(sep=1, NA(), IF(value="", NA(), IF(value< 0, placed, NA()))) ``` Nest the `IF`s rather than writing `OR(sep=1, NOT(value>=0))`. `OR()` evaluates every argument, so on a spacer slot the `INDEX` inside the second test reaches outside your input block and the whole cell returns `#REF!`. 5. **Print each point's own number.** A pin has a length but no scale to read it against, so add a data label per point and link it to a **label column** holding `TEXT(value, "+0;-0;0")`. Link to a cell rather than typing, and format in the *cell* rather than on the label: a linked label shows the cell's value, so `31.61057692` otherwise prints every one of those digits. 6. **Style a marker through `Marker Fill` and `Marker Border`, never through the point's line.** On a line series the point's line is the *connecting segment*; touching it draws stray lines between adjacent pins. A hollow marker head is the usual way to mark the periods that are planned rather than measured, and it is per point, so one panel can change notation part way along without splitting into another series. ### Dot A dot is a pin with the stem taken away, and the subtraction is a claim rather than a saving. A stem says *this far from zero*; a dot says only *here*, which is what you want when the reader is comparing a value against a reference rather than reading a magnitude off a length. Same construction as a pin - line chart with markers, line hidden - minus steps 2, 3 and 4 above. With no error bar there is no per-series colour to fight over, so **one series per element carries both signs** and each point is coloured individually. Two things follow from having no stem: - **Do not force the shared scale through zero.** Step 2 takes `vmin` as `MIN(0, MIN(range))` so that columns and pins start where they are measured from. A dot is not measured from anywhere, so use `MIN(range)` instead - otherwise every panel is squashed into the top of its band by a zero nobody asked about. - **Then do not draw the per-panel baseline either.** Step 6's baseline is the band's *zero* line, and if zero is not on the scale the rule lands outside the band, in the neighbour's. Guard it rather than deleting it, so it comes back by itself the day your data crosses zero: ``` =IF(OR(vmin>0, vmax<0), NA(), band + zero_offset) ``` Two mechanical notes, both established in `references/calibration/calib_dot.py`: `Marker Size` is in **points** and refuses anything outside `2..72` rather than clamping to it; and a per-point marker *shape* override sticks, which is the one distinction that survives a greyscale print. --- ## Step 9 - Page setup 1. Park the chart **below every cell the engine uses**. Anything underneath it prints through it. 2. Select the cell range the chart covers, then **Page Layout > Print Area > Set Print Area**. 3. **Page Layout > Orientation > Landscape**, and **Scale to Fit: Width 1 page, Height 1 page**. 4. **View > uncheck Gridlines** so the worksheet grid does not print behind it. --- ## Troubleshooting | What you see | Cause | |---|---| | Spikes to the baseline at every panel edge | A formula returns `""` instead of `NA()` | | Dividers and labels bunched at the left | An XValues range has a text cell, so Excel is plotting against 1, 2, 3 | | Furniture sits half a panel off | The secondary X axis is not exactly `0.5` to `slots+0.5` | | Panels overlap the band above | The scale is sized on individual values instead of stacked totals | | Everything sits on the baseline | `Show empty cells as` is Zero, not Gaps | | The last panels of one row have no axis line | A scatter series has more points than the chart has categories; split it across several series | | Columns float above the band floor | A band's base is absolute instead of a delta from the running total |