Excel for data analysis

Structure, calculate, summarize, and check changing data

Excel remains widely used in professional data analysis. In the Analytics Institute’s 2025 member survey, 75% of respondents listed Excel among their main tools at work.1

Excel lets people turn their knowledge of a task into a working calculation. You can enter records, express a rule as a formula, and inspect the result in the same worksheet. Start with a small calculation, change an assumption, and examine its effect. As requirements become clearer, you can extend the workbook. Colleagues can inspect the inputs, discuss the assumptions, and use the results in their work.

Reliable spreadsheets require careful design. A copied formula can refer to the wrong cell, and a summary can remain unchanged after its source data have been updated. The checking sequence from Module 1 applies here. State the required result, implement the procedure, and compare the output with an expected result. Tables organize the records, formulas perform calculations, and queries repeat the steps used to import and clean data.

The required path in Table 1 starts with the received patient workbook and ends with an independent warehousing analysis. Keep the listed starting files unchanged. Save working copies in the locations specified in each chapter.

Table 1: Module 2 chapters, starting files, and expected results.
Page Workbook and data Starting state Result
2.1 Excel basics and data tables Patients.xlsx and Patients_working.xlsx Received surgery workbook Structured workbook with baseline and control checks
2.2 Formulas, functions, and lookups Patients_working.xlsx Tables prepared in 2.1 Checked calculations, lookups, and spill formulas
2.3 Import and clean data with Power Query Patients_live_refresh.xlsx, 2 CSV rounds, and 2 JSON rounds Blank workbook and supplied source files Repeatable import, cleaning, merge, and refresh process
2.4 PivotTables, charts, and dashboards Patients_working.xlsx, Patients_live_refresh.xlsx, and routing results Checked surgery workbook, refresh queries, and supplied synthetic results Checked PivotTables, charts, and a refreshable dashboard
2.P1 Practice: warehousing sales analysis Warehousing sales workbook Complete independent case Cleaned, augmented, summarized, and exported analysis

After the required path, use Additional Excel questions for the preserved 27-question bank and Additional Excel practice for 4 transfer exercises.

Learning objectives

By the end of this part, you will be able to:

  • organize records in Excel Tables and explain what each row and field represents;
  • translate calculation and classification rules into formulas, then test their references, inputs, and results;
  • import, clean, and combine CSV and JSON data while preserving received files and recording corrections;
  • build PivotTables and charts, choose appropriate summaries, and check them against independent calculations;
  • refresh a dashboard when its sources change and verify the queries, PivotTables, and reported results;
  • apply these steps independently to another dataset and deliver a readable workbook and a correctly structured CSV export.

  1. Analytics Institute, Data Salaries & Job Sentiment Analysis 2025, p. 26. Participants were Institute members, with a relatively senior professional profile. The percentage describes survey respondents rather than worldwide Excel adoption.↩︎