Excel as the Analyst’s Workbench

Give the workbook a structure

Start where the analyst starts: with one file and no structure. DS2_appointments_clean.csv holds 36,000 rows, one per scheduled visit, and opening it in Excel gives you a single grid of raw data. The temptation is to begin calculating on that grid directly. Resist it. The moment you edit a raw value or drop a formula beside the data, you lose the ability to say where a number came from.

A disciplined workbook separates its work into four layers, each on its own sheet:

• Raw: the imported data exactly as it arrived, never edited by hand.
• Cleaning: the transformations that fix and reshape the data.
• Analysis: the PivotTables and formulas that answer questions.
• Output: the charts and final numbers a reader sees.

Figure 2 shows the flow, and it runs one way only: data moves from raw to output, never back. Because the raw sheet is never touched, any result downstream can be traced to the rows it came from. This calculation traceability, showing how a number was produced, is what makes a workbook auditable, even though a spreadsheet does not by itself record who changed what and when. Organizing a spreadsheet so both people and software can read it is a discipline in its own right (Broman & Woo, 2018).

The file you start with, DS2_appointments_clean.csv, has already been cleaned, so this chapter builds the raw, analysis, and output layers around data that is ready to use. Chapter 7 builds the reusable import that feeds a workbook, and Chapter 8 builds the cleaning layer, so you will meet the full four-layer architecture in stages rather than all at once.

Two small habits protect this structure. The first is a one-sentence note on each sheet stating its purpose, placed in a cell to the side of the data or on a small documentation sheet, so the next reader does not have to guess. The second is a file name that means something. A workbook called final.xlsx tells you nothing, and a later final_v2_REAL_final.xlsx tells you less. A name like Clinic_NoShow_Analysis_2026-04-01.xlsx states what the workbook is and when it was made. Structure keeps a result traceable, but only if the data inside each layer can be referred to cleanly. That is the next problem to solve.

Make the columns addressable: Tables and structured references

You now have the raw appointment data on its own sheet. Left as a plain range, it is fragile. Formulas point at coordinates like AJ2:AJ36001, and the day someone inserts a row or the file grows to next quarter’s data, those coordinates quietly stop covering everything. The fix is to convert the range into an Excel Table.

Once the appointment data is a Table named Appointments, a structured reference lets you write Appointments[attendance_outcome] in place of AJ2:AJ36001. A formula that reads COUNTIF(Appointments[attendance_outcome], "No Show") says what it does; the same formula written against a cell range does not. That readability is not cosmetic. When the reference is a column name, the formula keeps working as the Table grows, because the name always means the whole column. A fixed range covers only the rows you selected the day you wrote it.

One detail matters for beginners. Inside the Table you can use the short forms [attendance_outcome] for the whole column or [@attendance_outcome] for the current row. But in a formula written anywhere else, you must include the Table name, as in Appointments[attendance_outcome].

A named range solves the same problem for a single value. If your no-show analysis flags any clinic above a chosen threshold, store that threshold in one named cell called Threshold rather than typing 0.16 into a dozen formulas. Change the threshold once, and every formula that refers to Threshold updates.

Clean design also means avoiding merged cells, labeling consistently, and keeping the transformation logic visible rather than hidden three sheets away. A workbook should not read like a puzzle. With the columns now addressable by name, a small set of functions can answer most of the questions an analyst is asked.

A small core of functions, and four tools

New analysts often believe they need to memorize hundreds of functions. They do not. A first year of healthcare analytics runs on a small core: SUM, AVERAGE, COUNT, COUNTIF and COUNTIFS, SUMIF and SUMIFS, IF, XLOOKUP, ROUND, TEXT, and a handful of date functions. The goal is not to collect functions. The goal is to know which function answers a real question. Table 1 maps the core to the appointment data in front of you.

The no-show rate the director asked for needs only one of these. Counting no-shows and dividing by the expected visits is a job for COUNTIFS, and structured references make the formula readable.

Formulas cover a great deal, but four larger tools do the heavier lifting. It helps to see them as one connected stack rather than unrelated features, shown in Figure 3:

• Power Query prepares the data. It records import and cleaning steps you can refresh when new data arrives, so the cleaning is repeatable instead of manual. On a Mac, its full experience requires a Microsoft 365 subscription, and Chapter 7 builds the reusable import that relies on it.
• PivotTables summarize the data. Put visit_type in rows, filter attendance_outcome to Completed and No Show, average no_show_flag, and you have the no-show rate for every visit type in seconds.
• The Analysis ToolPak runs the statistics. It is an add-in that ships with Excel and provides the linear regression Chapters 11 and 12 use.
• Solver optimizes. Chapter 13 uses it to fit a logistic regression by maximizing likelihood.

You do not need the last two yet; know that they exist and that the workbench you are learning now is the same one those chapters build on. Power without a check on it, though, is exactly how spreadsheets go wrong.

Check the work: validation and auditing

You can now produce the no-show rate. The next question is whether to believe it. Spreadsheet errors are common precisely because spreadsheets are easy to change and easy to misread, and a wrong number that looks tidy is more dangerous than one that looks wrong. Reviews of operational spreadsheets have repeatedly found that a large share contain at least one error, which is a strong argument for building checks in rather than trusting a result on sight (Panko, 1998; Powell et al., 2008).

Three habits catch most of these errors, and Figure 4 shows how each catches a different kind.

Prevent bad values with data validation, a rule that restricts what a cell will accept. If a field should hold only zero or one, a validation rule rejects a stray two typed into the cell before it corrupts a count. Validation mainly governs what someone types directly, though. Values that arrive by import, paste, or fill can slip past it, and Circle Invalid Data only marks cells once a validation rule is in place.

Detect what validation missed with an audit formula. For imported data, count the values that are neither 0 nor 1, blanks included, with =ROWS(Appointments[no_show_flag]) - COUNTIF(Appointments[no_show_flag], 0) - COUNTIF(Appointments[no_show_flag], 1). It should return zero.

Verify the result with reasonableness checks. When you filter the Table to completed and no-show visits, the rows that remain should equal the denominator your formula used, 30,571 here. That check reuses the formula’s own logic, so pair it with an independent one: confirm the five outcome categories sum to 36,000. If either number drifts in a way you cannot explain, stop and find out why. Tracing a formula’s precedents with Formulas > Trace Precedents turns a suspicious result into a visible chain back to the data.

A clean, checked workbook is trustworthy, but being trustworthy is not the same as being unlimited. It is worth knowing where the workbench ends.

Where Excel stops

Excel is an excellent environment for visible, entry-level analysis, and it is especially strong for learning because you can see the rows, the formulas, the Table, and the chart in one place. With Power Query and PivotTables it does far more than manual spreadsheet work: it imports files, reshapes them, refreshes queries, summarizes results, and drives a dashboard. The 36,000-row appointment file sits well within its comfortable range. Figure 5 contrasts where Excel is a strong fit with the kinds of work that call for a larger tool.

Excel is not the right tool for every problem, and pretending otherwise does a beginner no favors. A single worksheet holds at most 1,048,576 rows, so a file with several million records will not even open in full; the data model behind Power Pivot can hold more, but the worksheet grid cannot. Very large data, automated pipelines that must run without a person present, machine learning beyond the regression this book covers, and enterprise reporting to hundreds of users usually belong on other platforms: a SQL database, a language such as Python or R, or a reporting tool such as Power BI.

The point of this book is not that Excel solves everything. It is to use Excel as a serious environment for analytic thinking, so the habits you build here, traceable structure, readable references, and checked results, carry directly to those larger tools when the work outgrows the spreadsheet. To see them in one place, build the workbook end to end.