AI Excel Data Analysis: Turn Spreadsheet Data into Insights (Certificate of Proficiency)

Upon completion of the final exam challenge, you will receive the Technology Training Proficiency in AI Excel Data Analysis certification.

Prerequisites:
- This class is for PC (Windows) users.
- Access to Excel through Microsoft 365 subscription license. (Visit http://ess.stanford.edu to download)
- Access to Stanford AI Playground, or other AI models (ChatGPT or Microsoft Copilot Chat)
- Basic understanding of Microsoft Excel.

Excel is one of the fastest and most flexible tools available for analyzing data and producing reports.

However, many day-to-day Excel workflows still rely on manual cleanup, repeated steps, and reports that become fragile when the underlying data changes.

This workshop focuses on a powerful set of Excel capabilities that many users overlook: Power Query, the Excel Data Model, and Power Pivot. Together, these tools support structured data preparation, scalable analysis, and refreshable reporting inside Excel.

Artificial intelligence is used alongside Excel throughout the course. AI tools help with tasks such as exploring formulas, drafting Power Query steps, refining DAX calculations, troubleshooting issues, and thinking through analysis approaches. The goal is not to replace Excel¿s features, but to use AI as a practical assistant while working with them.

Across the sessions, participants work through an end-to-end workflow: importing data, transforming and reshaping it, modeling it for analysis, and presenting results through refreshable reports and dashboards.

The course also examines how to interact effectively with AI tools. This includes framing useful prompts, providing the right context, and recognizing when AI assistance is helpful versus when Excel¿s built-in capabilities are the better choice.

The overall focus is a repeatable analysis and reporting approach in Excel ¿ one that can be applied to new datasets so that updates rely on refreshing a workflow rather than rebuilding reports from scratch.

You will have the opportunity to:
1. Explore core concepts related to Excel-based data analysis.
2. Use AI tools while working with Excel features and analytical workflows.
3. Work with Power Query to import and transform data.
4. Create data models using Power Pivot.
5. Explore DAX (Data Analysis Expressions) within the Power Pivot environment.
6. Use AI tools to assist with writing and refining DAX calculations and measures.
7. Work with PivotTables and PivotCharts connected to the Excel Data Model.
8. Create interactive dashboards for presenting and communicating insights.
9. Use AI tools to inform design and presentation decisions in reports and dashboards.

Breakdown of topics:

Session 1: Essential Skills and Knowledge
- Use AI tools within Excel data analysis workflows.
- Explore concepts related to effective analysis, including clean data, useful questions, and meaningful summaries.
- Examine AI fundamentals related to Excel, including LLMs, prompting, and context building.
- Compare AI tools used alongside Excel, including ChatGPT, Copilot, and AI playground environments.
- Follow a structured process for data management, analysis, and reporting.
- Identify Excel tasks that align well with AI support and those that do not.
- Practice AI conversations for planning, troubleshooting, and explaining Excel tasks.
- Work with Excel data structures such as ranges, named ranges, tables, and dynamic arrays.
- Explore modern Excel formula and calculation features.
- Create PivotTables for summarizing data.

Session 2: Transform Data with Power Query and AI
- Explore Power Query as a tool for importing and preparing data in Excel.
- Use Power Query to convert manual data cleanup steps into a repeatable process.
- Import data from common sources such as files, folders, web sources, and databases.
- Clean, reshape, and transform data using Power Query tools.
- Manage queries and connections within a workbook.
- Reuse queries for recurring data preparation tasks.
- Combine data from multiple sources using merge and append operations.
- Explore foundational concepts of the M language used in Power Query.
- Use AI tools when drafting and refining Power Query steps and M expressions.
- Create PivotTables from Power Query results for refreshable reporting.
- Explore the connection between Power Query and the Excel Data Model.

Session 3: Data Analysis with Power Pivot
- Explore Power Pivot and the Excel Data Model environment.
- Load and manage data in the Data Model using Power Query and other sources.
- Examine concepts related to data modeling within Power Pivot.
- Use AI tools when planning data models, including tables, keys, relationships, and measures.
- Work with table relationships in the Excel Data Model.
- Create structured data models for analysis and reporting.
- Compare traditional PivotTables with Data Model PivotTables.
- Explore Data Analysis Expressions (DAX) within Power Pivot.
- Examine how DAX supports calculations and measures in analytical models.
- Use AI tools when drafting and refining DAX expressions.

Session 4: Building Interactive Dashboards
- Explore characteristics of effective Excel dashboards, including purpose, audience, and metrics.
- Select visualizations that address common analytical questions.
- Design dashboard layouts that support clear communication of results.
- Create dashboards in Excel using charts, PivotTables, and supporting elements.
- Work with dashboards that reflect refreshed and updated data.
- Add interactivity using slicers, timelines, and other filtering tools.
- Use dynamic arrays within responsive dashboard elements.
- Use AI tools when making design and presentation decisions.
- Apply formatting approaches that support clarity and consistency.
- Reuse dashboards as templates for new reporting cycles or datasets.