Task 1 — Setting up the dropdown and the two new columns

What this task is really about

Task 1 is the groundwork for everything that follows. The BIKE table on the Bike sheet has 731 rows of daily bike-share numbers from Washington, DC — one row per day, with three columns that matter to us: casual, registered, and cnt (the total of the two). The exam wants a single user-facing switch; a dropdown on the Questions sheet that controls which of those three numbers downstream calculations will use. Task 2 and Task 3 both read from what you build here, so if Task 1 is wrong, nothing else works.

The three pieces are: a cell with a dropdown (B12 on Questions), a name for that cell (userType), and two new columns on the BIKE table (MonthYear and Users). Nothing clever yet — just wiring.

How to read the exam wording

The exam uses a few terms that are easy to misread. A named item (also called a named range) is a label Excel remembers — after you do the naming, writing userType in a formula is the same as writing Questions!$B$12. It makes formulas readable. Data validation is Excel's way of restricting what someone can type into a cell; the "list" kind of validation is what gives you a dropdown arrow. A table in the Excel sense (capital-T Table) is a range that Excel tracks by name — BIKE in this workbook, and its columns can be referenced by name inside formulas using [@columnName] syntax. Once you've got those three in your head, the task is three sentences long.

The output you're aiming for: cell B12 on Questions has a dropdown with casual, registered, total; it's named userType; and the BIKE table has grown two columns on the right, MonthYear (each cell showing the first of the month matching its row's date) and Users (each cell showing whichever of casual/registered/cnt matches the dropdown).

The minimum-viable, click-through version

READMEFIRST says up to 50% is available without any ExcelScript. For Task 1 the manual version is completely reasonable as a fallback: select B12, go to Data → Data Validation, choose List, type casual, registered, total, click OK. Then with B12 still selected, type userType into the Name Box (the little box to the left of the formula bar; the formula bar is the long box at the top of the sheet that shows the active cell's contents). Then on the Bike sheet, right-click the last column header and insert two new columns, type headers, type the formulas in row 2, and Excel's table will fill them down automatically.

That works. What the script adds is reproducibility — if the marker (or you) opens a fresh copy of the starter, one click puts everything back. That's what the exam is really marking.

Building the dropdown with a script

The official script breaks Task 1 into two helper functions, Q1dataValidation and Q1table, both defined at the top level of the file alongside main. main itself is the entry point Excel looks for when you run the script — it always takes a workbook argument, and inside main the helpers are called in order.

Here's the first chunk of Q1dataValidation:

let validationCriteria: ExcelScript.ListDataValidation = {
    inCellDropDown: true,
    source: "casual, registered, total"
};
let validationRule: ExcelScript.DataValidationRule = {
    list: validationCriteria
};

Two objects are being built. An object is a bundle of named fields — here, validationCriteria has two fields, inCellDropDown (turns on the little arrow you see in the cell) and source (the list of allowed values, as a comma-separated string). validationRule wraps it: the DataValidationRule type can describe several kinds of rule, so you tell it "this is the list kind" by putting your criteria under the list field. The : ExcelScript.ListDataValidation bit just tells TypeScript what type the object is — ignore it while reading, it's only there for autocomplete and error-checking.

Methods used: none yet — this chunk is plain variable setup. See 05ExcelScripts.pdf and 06ExcelScriptAPI.pdf for how the ListDataValidation and DataValidationRule interfaces fit together.

Next, the rule gets applied and the cell gets its name:

let cell = Qs.getCell(11, 1);
cell.getDataValidation().setRule(validationRule);
workbook.addNamedItem("userType", "=" + cell.getAddress(), "User Type");

Qs.getCell(11, 1) is where the 0-indexed vs 1-indexed thing bites you the first time. ExcelScript's getCell is 0-indexed: row 11, column 1 means the 12th row and the 2nd column, which is B12. Excel itself uses 1-indexed addresses when you type B12 by hand, so there's always this mental step. If you ask for getCell(0, 0) you get A1.

cell.getDataValidation() returns the validation object attached to that cell; setRule pastes your rule onto it — after this line, B12 has the dropdown.

workbook.addNamedItem takes three things: the name, a formula string that defines what the name points to, and a comment. cell.getAddress() returns something like Questions!$B$12; the "=" in front makes it a valid formula string. After this line userType exists as a workbook-level name, and any formula in any sheet can write userType instead of Questions!$B$12.

Adding the two columns

The second function, Q1table, adds two new columns to the BIKE table. Tables in ExcelScript have an addColumn method — passing -1 as the index means "add it at the end", and the third argument is the column header name.

let dateCol = bike.addColumn(-1, null, "MonthYear");
dateCol.getHeaderRowRange().getOffsetRange(1, 0).setFormula(
    "=DATE(YEAR([@dteday]), MONTH([@dteday]), 1)"
);
dateCol.getRangeBetweenHeaderAndTotal().setNumberFormat("mm/yyyy");

Note: you only write the formula once, in the first data cell, and Excel fills it down the whole table column. You get that first cell by asking for the header row range (the cell containing "MonthYear") and then getOffsetRange(1, 0) — one row down, zero columns across which is the cell directly under the header. That's the first data row.

The formula uses three Excel functions you might not have bumped into: DATE(year, month, day) builds a date from three numbers; YEAR(d) and MONTH(d) extract those parts from an existing date. Pinning day to 1 collapses every day of January 2011 into the same value, which is what Task 2's monthly averaging needs. [@dteday] is the table's way of saying "the dteday cell on this row"; the @ means "this row".

getRangeBetweenHeaderAndTotal returns the body of the column (everything below the header, above any total row), and setNumberFormat("mm/yyyy") tells Excel to display those date values as "01/2011" rather than "01/01/2011". The underlying value is still a full date; only the display changes. That distinction — value vs format — matters: Task 3's chart will read the value, not the format.

Methods used: addColumn, getHeaderRowRange, getOffsetRange, setFormula, getRangeBetweenHeaderAndTotal, setNumberFormat; see table_chart.pdf and range.pdf.

Now the Users column:

let userCol = bike.addColumn(-1, null, "Users");
userCol.getHeaderRowRange().getOffsetRange(1, 0).setFormula(
    '=IF(userType="casual", [@casual], IF(userType ="registered", [@registered], [@cnt]))'
);
userCol.getRangeBetweenHeaderAndTotal().setNumberFormat("General");

This is a nested IF. IF(condition, value_if_true, value_if_false) is Excel's if-statement — when you nest, you put another IF where value_if_false would go. Read it as: if the dropdown says casual, use casual; otherwise if it says registered, use registered; otherwise (i.e. "total") use cnt. Three branches, two IFs — that's the usual nesting pattern.

Two things go wrong with this formula if you're not careful. First, the strings on the right-hand side of each = must match the dropdown options exactly, including case — "Casual" would silently never fire. Second, the final else branch quietly handles "total" by using cnt (the total column) — there's no explicit IF(userType="total", ...). If you add a fourth option to the dropdown later, this formula will misbehave, because anything that isn't casual or registered falls through to cnt. Noted, not fixed; the exam only has three options.

The outer number format is set to "General" because the column holds plain counts like 985, not dates or percentages. If you skipped this the column would inherit the table's default and usually be fine; setting it explicitly just removes one thing that can surprise you.

Methods used: addColumn, setFormula, setNumberFormat.

If this still feels shaky

For the dropdown and named-item mechanics, workbook_worksheet.pdf documents addNamedItem and range.pdf covers getDataValidation/setRule. For the table-column side, table_chart.pdf shows addColumn and the header/body range helpers. Lecture 05ExcelScripts.pdf walks through the overall structure of a main function; if the ExcelScript.ListDataValidation type annotation still feels mysterious, lecture 04TypeScript.pdf covers interfaces and object-literal types.