Question 7 — General integration

marks: chart type, correct data series, title, horizontal axis label, vertical axis label, legend — six separately checkable pieces of chart configuration, each added through its own step in Add Chart Element. Sectors should appear along the bottom with four coloured bars each, because a clustered column chart's category axis (the bottom) is whichever dimension of the data was treated as "groups," and series (the four colours within each group) is whichever was treated as "categories within a group" — exactly the chart-from-a-sheet logic covered in the integration notes. If Excel puts Q1–Q4 on the bottom instead, use Chart Design → Switch Row/Column, which doesn't recalculate anything, just swaps which axis is treated as which. The example's default title "Sector" and axis labels "Time frames" / "Companies" are placeholders, but the paper asks for a descriptive title, so write something meaningful: a chart's auto-generated title is usually just the name of the first data series or column, which rarely actually describes what the chart shows.

the paper says A1:F1 but lists only five names, and B2:F2 are the formula cells. So the five names naturally sit in B1:F1, directly above their results — read the cell ranges literally rather than assuming A1 must hold the first name, since the given layout (one blank-ish label cell in A1, five names from B1 onward) is what actually matches both the instruction and the five results expected in B2:F2. VLOOKUP's column index is 5 because Q4 is the fifth column of A:E (Sector, Q1, Q2, Q3, Q4) — VLOOKUP counts columns from the left edge of its own table argument, starting at 1, so the index has to match that table's layout exactly, not the worksheet's column letters. B$1 keeps the row fixed while the column moves when copied right, and the table is absolute so it does not shift: this is a mixed reference (row locked, column free) feeding the lookup value, paired with a fully absolute reference locking the lookup table — exactly the two reference types the spreadsheets notes describe, combined for a formula that's meant to be copied sideways rather than down. FALSE forces an exact match, the same safeguard against wrong approximate matches covered throughout the function notes. Marks: sheet, names, correct lookup, copy across.

marks: table created, filter correctly applied, SUBTOTAL with 109. 109 = SUM that ignores hidden rows, and SUBTOTAL recalculates when the filter changes, unlike plain SUM, which adds hidden rows too — exactly the SUBTOTAL behaviour explained in the spreadsheets notes, now applied to a live filtered table rather than a manually hidden range. Place B8 below the table with a gap row so Excel does not absorb it into the table, because an Excel Table automatically expands to include any data typed immediately adjacent to it — without the gap, your SUBTOTAL cell would become a new table row rather than staying an independent total outside it. If your Sectors sheet Q1 values match Impact, the visible total is 31 (5 + 10 + 11 + 5), which is a useful arithmetic check that the filter and SUBTOTAL are both working as intended, not just present.

marks: data source connected, merge fields inserted correctly, merged document saved with the right name — the same three-part mail merge model (template, data source, merge fields) explained in the Word notes, now carried through to its finished output. The template must be saved before merging so the fields are kept, because the merge fields themselves live inside the template file — if it's never saved, a crash or accidental close loses the field placement work entirely, with nothing to show for it. "Edit Individual Documents" creates a real document with one letter per recipient, which is what you save: the template itself never becomes the finished output, so saving the template again under the required name would produce a file that still contains unresolved merge-field placeholders rather than actual recipient data. Use Preview Results first to check that every field fills in correctly and spacing around fields is right, since a field placed with an extra or missing space around it merges correctly in terms of data but produces an oddly spaced sentence that's easy to miss without previewing.