Data analysis with Excel

Structure, analyze, and summarize tabular data, and refresh it when the source files change

Operational data, such as surgery records and simulation results, often arrives as a spreadsheet or a CSV file, and a new export can come every week. Businesses commonly analyze such data in Excel. Module 5 and Module 6 use Excel to inspect results that Python programs export.

After setting up Excel, you work through a tutorial on a workbook of surgery records and do each step yourself in Excel. You then import the same records from CSV files with Power Query and refresh the workbook when the files change. Look up functions, keyboard shortcuts, websites, and YouTube channels in the Excel reference.

Learning objectives

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

• set Excel's display language to English (United States) and read the sum, average, minimum, and maximum of a selection in the status bar;
• move through a large worksheet and select ranges with keyboard shortcuts;
• write formulas with relative, absolute, and mixed references, and copy them to other cells;
• use functions such as IF, COUNTIF, SUMIFS, and XLOOKUP to flag records, calculate conditional counts and sums, and look up values;
• convert data to an Excel Table, sort and filter it, and restrict entries with a data validation list;
• summarize data with PivotTables and PivotCharts, and communicate results with charts and conditional formatting;
• import CSV files with Power Query and refresh the workbook and its dashboard when the source files change; and
• trace the precedents and dependents of a formula to see which cells it uses and which cells rely on it.