Course Curriculum
Build a reliable analyst workflow: workbook structure, data types, references, and auditable calculations.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Turn messy operational exports into consistent, analysis-ready tables using text, numeric, and quality-control functions.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Build decision rules and segmented metrics with IF-family functions and criteria-based aggregation.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Retrieve exact and approximate matches confidently with XLOOKUP, VLOOKUP, HLOOKUP, INDEX, and MATCH.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Create dependable calendar logic, aging, working-day metrics, and period comparisons.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Use modern spill formulas, named logic, and reusable functions to reduce manual work and model risk.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Summarize, compare, and explore large datasets with PivotTables, grouping, calculations, and slicers.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Turn analysis into truthful, accessible, decision-oriented charts and dashboards.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Describe distributions, quantify relationships, test scenarios, and create appropriately cautious forecasts.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Replace fragile manual cleaning with refreshable queries, joins, appends, profiling, and controlled outputs. Integrate robust modeling, performance, automation, documentation, and a complete analytics project.
Outcome: Complete the four lessons and pass the chapter checkpoint before continuing.
Lesson 1: The Analyst Workflow and Excel Interface
The Analyst Workflow and Excel Interface
Estimated study time: 30 minutes
Learning objectives
- Navigate the workbook, ribbon, formula bar, name box, and status bar
- Translate a business question into inputs, transformations, checks, and outputs
- Organize a workbook so another analyst can audit it
Concept and business use
Analytics is not a collection of formulas; it is a controlled path from a question to evidence. Separate raw data, calculations, assumptions, and presentation. Preserve an untouched source sheet and document units, refresh dates, and owners.
Formula anatomy
=SUM(B2:B13)
SUM adds numeric cells in the inclusive range B2:B13. The equals sign starts a formula; SUM is the function; parentheses contain arguments; the colon means through. Text and blanks are ignored.
Worked example
A monthly revenue column in B2:B13 contains 12 values. =SUM(B2:B13) returns annual revenue. Compare the result with the status-bar sum and the source-system control total.
Exercise context
The attached workbook is the authoritative dataset for this lesson. It contains the exact sheets, columns, source records, and formula references used below. The same file supports guided practice and assessment.
Workbook and field map
Attachment: chapter1_lesson1_data.xlsx
- README: source documentation and control values
- Raw_Data: twelve monthly source records
- Analysis: formulas and reconciliation
- Dashboard: linked output values
Step-by-step follow-along procedure
- Download and open chapter1_lesson1_data.xlsx. Do not create a blank workbook; all four required sheets already exist.
- Open Raw_Data. Row 1 contains Month, Revenue, Orders, and Budget. Rows 2–13 contain January–December 2026. Revenue is in B2:B13 and Budget is in D2:D13.
- Open README. Source is in B2, Refresh Date in B3, Row Count in B4, Control Total in B5, and the business question in B6. Source means where the data came from; Refresh Date is the date through which the extract is complete; Row Count is the number of source records; Control Total is an independently recorded amount used to verify calculations.
- Open Analysis. Select B2 and inspect
=SUM(Raw_Data!B2:B13). This adds the twelve monthly revenue values. B3 adds budget, B4 calculates revenue minus budget, B5 totals orders, and B6 divides revenue by orders. - Format Analysis!B2:B4 as Currency with zero decimals and B6 as Currency with two decimals. Confirm Analysis!B7 displays PASS because Analysis!B2 equals README!B5.
- Open Dashboard. Cells B2:B4 link to the completed Analysis results. Change one revenue value temporarily in Raw_Data, observe the linked updates, then undo the change so the supplied data remains unchanged.
- Record the final annual revenue, budget, and variance. Use the business question in README!B6 to write a one-sentence conclusion.
Practice task
The Analyst Workflow and Excel Interface
Expected completion evidence
- The source rows remain unchanged.
- The Practice sheet contains the requested formula, analysis, or feature.
- The row-count and control-total checks reconcile.
- The learner can explain which source columns feed the result.
Common mistakes
- Editing raw values to make results look right
- Mixing assumptions with imported data
- Using color without labels or documentation
Mastery check: You can trace a dashboard number back to its source and explain each transformation.