Workbook Structure, Freeze Panes, Clipboard Modes & Formatting

Who this is for

Stage 1: Grid Foundations & Reference Architecture — Designed for learners mastering freeze panes & sheet tabs, paste values & formulas, conditional formatting & data bars, multi-column sort, autofilter & auditing through interactive spreadsheet examples.

What you will practise

• Freeze header row 1 first, then freeze both row 1 and column A (freezeRows: 1, freezeCols: 1) on the Ledger sheet.
• Snapshot live quote totals D2:D4 into E2:E4 using Paste Values so E2:E4 remain static non-formula numbers.
• Copy D2 and use Paste Formulas onto pre-styled D3 and D4 so D3:D4 compute live margin while keeping their green #dcfce7 highlight.
• Apply Currency format to C2:C4 and Percentage format to D2:D4 on the Executive Summary table.
• Rename Sheet1 to Ops_Summary and add a new worksheet named Archive.
• Insert a new row at row 3 (0-based index 2), enter "Cloud Backup" in A3 and 150 in B3, and verify Total in B6 updates to 950 via =SUM(B2:B5).
• Use the pre-defined Named Range ColdChainFee (or locked $B$1) in C4:C6 to add the insulated-pack fee to every order, then sum C4:C6 in C7.
• Add a "highlight" conditional formatting rule on B2:B6 for values strictly greater than 50 (operator: "gt", val1: 50, preset: "red").

Your learning path

1. Non-Volatile Dynamic N-Period Sum & Average Without OFFSET (SUMIFS & AVERAGEIFS)

   Replace slow volatile SUM(OFFSET(B2,0,0,N,1)) rolling windows with dependency-tracked SUMIFS(B2:B7, A2:A7, "<=" & E2) and AVERAGEIFS for instant multi-sheet recalculation.

2. Sum and average the last N columns horizontally (rolling trailing months with TAKE)

   Calculate trailing 3-month or trailing N-period sums and averages across horizontal monthly P&L columns using TAKE(row, , -N).

3. Sum & Average Daily Revenue by Day of Week (WEEKDAY + SUMIF)

   Aggregate daily POS or e-commerce transactions by ISO day of week (Monday = 1 through Sunday = 7) and split weekday vs. weekend totals using WEEKDAY(A2, 2) + SUMIF.

4. Sum and average by calendar month across years

   Group dated observations by month and compare a group average with the overall sample average.

5. Sum, Average & Count Comma-Delimited Numbers Inside One Cell (TEXTSPLIT)

   Parse comma- or pipe-delimited numeric lists packed inside a single cell (such as "120, 85, 95, 140") into real numbers using --TEXTSPLIT(B2, ",") and aggregate them with SUM, AVERAGE, MIN, and MAX.

6. Sum, average, or find MAX while ignoring formula errors (#DIV/0!, #N/A)

   Keep a single #DIV/0! or #N/A cell from poisoning summary KPIs by filtering ranges with ISNUMBER before SUM, AVERAGE, or MAX.

7. Sum or average the last N entries in a column (skipping blank rows)

   Extract the final N numeric observations from an expanding log with TAKE and FILTER—keeping legitimate zeros while skipping trailing blanks.

8. Sum, Max & Average a Variable Column by Header (CHOOSECOLS + MATCH)

   Select a numeric column by a quarter header and calculate its sum, maximum, minimum and average with missing-header checks.