Lab 4 Walkthrough, FSA Excel Skills

This lab makes the Excel formula toolkit automatic. You work a short drill of each concept on a small, clean dataset of 14 firms. Almost every cell you fill in is highlighted amber. Most tabs give no formula hints. You bring the formula. That is the whole loop.

1. Get the workbook

Everything happens in one file. Download it, save it, and open it in Excel:

Download lab_04_template.xlsx

2. Read the reference tabs first

Three tabs up front teach you everything you need. Keep them open while you work:

0-Formula Reference: the core functions the lab uses, worked live, with a small lookup dataset.

0-Logic Lab: change a TRUE/FALSE and watch AND, OR, and IF react.

0-Errors: what each Excel error means and how to handle it.

3. Work the numbered tabs, in order

Tabs 1 through 9 build from simple to applied. Each tab tells you what to compute; you write the formula. They cover, in order:

data types and text cleanup,
the logic functions IF and AND (IFS and OR are on the reference tabs),
conditional aggregation, SUMIFS and friends,
lookups, VLOOKUP, XLOOKUP, and INDEX/MATCH,
error handling,
the structural skills: turn a range into a Table and fill its margin column with structured references, then convert a Table back to a range,
and finally Bring it All Together, which summarizes one sector at a time. Pick a sector in cell B4 and every number in the grid should update.

The one rule: the cells you need to fill in are highlighted amber on the numbered tabs. In each amber cell, type the formula (it starts with =). Do not paste a value or type the answer in by hand. One exception: on 7 - Tables, the margin column (H7:H20) is not amber. Fill it with a formula after you convert the range to a Table.

The two amber cells on 0-Logic Lab are TRUE/FALSE switches to play with, not answers. Submit as <uID>_lab_04.xlsx. If you downloaded the workbook before September 14, its START_HERE tab lists old tab names; follow this page instead. Nothing you fill in changed.

How many formulas to expect

The total cell count looks large, but most of it is the same formula repeated down a column. You write one good formula per task and fill it down, so the actual thinking is a handful of formulas per sheet, not hundreds. Here is the per-sheet breakdown so nothing catches you by surprise:

Sheet	Formula cells	What that really means
1-Types	5	One per data-type task.
2-Cleanup	5	One per text-cleanup task.
3-Logic	42	A few distinct formulas, filled down the columns.
4-Aggregate	25	`SUMIFS` and friends, repeated across the grid.
5-Lookup	252	Three grids, one formula each, dragged across and down. The `VLOOKUP` grid needs the column number in row 5: type `2` in `B5` and `=B5+1` in `C5`, then fill it across to `G5`. Row 5 is not graded.
6-Errors	64	One error-handling pattern, filled across the cases.
7 - Tables	14	The margin column, with structured references on the Table you make. These cells are not amber.
8 - Ranges	0	No formula to write. The margin formulas in `H7:H20` are already there; this tab is a single convert-Table-to-Range action.
9 - Bring it All Together	20	Sum, count, average, min, and max for the sector picked in `B4`. `B8` is filled in for you, so you type 19.
Total	~427	Almost all fill-down. A handful of real formulas per sheet.

4. Stuck on a formula?

Open the FSA Formula Toolkit. It covers the core formula moves with an AI tutor that helps you reason it out without handing over the answer. The How to Read an Excel Formula trainer is a good warm-up if the syntax feels shaky. For more practice, the optional Excel skills bootcamp workbook still has its hints and worked examples. It is not graded.

5. Turn it in

When every amber cell on the numbered tabs and the 7 - Tables margin column hold a formula, save the file as <uID>_lab_04.xlsx and upload it to the Lab 4 assignment in Canvas. Lab 4 is due Sunday, September 20, at 11:59 PM MT. You may resubmit; the latest attempt is kept.