Excel Data Analysis: From Data Management to Dynamic Reporting

Most Technology Training classes will be delivered online until further notice.

Before each sesson, Tech Training will provide a Zoom link for live online classes, along with any required class materials.

Prerequisites:

1. This class is for PC (Windows) users.

2. All learners must upgrade the software to the latest Microsoft 365 subscription license by visiting http://ess.stanford.edu. A full SUNet ID is required.

3. Basic understanding of Microsoft Excel, including menus and navigation, workbook components, and formulas.

It is hardly a secret that Excel conceals powerful tools that, when discovered, can immediately ramp up productivity and prowess of a savvy data analyst. 

You can find these incredible tools right in Excel - straight at your fingertips. 

Here’s what you need to get ahead of the game with these powerful tools:

1. Become aware of what is possible,

2. Invest time to wrap your mind around the superior techniques,

3. Rethink your current workflow.

Therefore, this class can be extremely valuable and help your Excel-data-analysis skills literally explode. 

This training program will help you get an extra edge for working with data and build up your analytical muscle as you sharpen your Excel knowledge and skills.

This session will walk you, step by step, through  an entire lifecycle of data-analytics -- from importing to reporting.

Along the way, you will explore the crucial essentials that give you critical know: how to collect, clean, import, manage, and share data for operational and reporting purposes.

It all begins with a comprehensive review of must-know concepts and techniques for working with data. These concepts and techniques are paramount to achieve success in data analysis with Excel.

By the end of this class, you will have the opportunity to learn how to build beautiful, dynamic dashboard reports for presenting actionable insights hidden in your data to facilitate decision-making.

Here are some things you will have the opportunity to learn:

• The essential concepts, tools, and techniques you need to succeed with Excel data analysis.

• A comprehensive review of cell-reference styles with a focus on data analysis.

• What’s in a name? Reduce errors and simplify your spreadsheets with Excel's names system.

• Get a grip on dataset structure with Excel tables.

• Meet all-new dynamic arrays and say goodbye to legacy array formulas.

• The latest Excel features that make data management a breeze.

• How to import, transform, and prepare data for analysis in Excel.

• Develop a reusable workflow that starts with data import and produces stunning, actionable reports.

• Make data analysis more efficient by eliminating redundant tasks.

• How to use Power Query, Power Pivot and Data Model — the three cornerstones that define Excel’s new gold standard for data management.

• Get to know the Power Query editor.

• Get to know Power Pivot.

• Clean datasets automatically with Power Query without performing repetitive tasks.

• Seamlessly integrate diverse data sources with Power Query and Power Pivot.

• Skillfully create pivot tables and pivot charts using the Excel Data Model.

• Forge interactive dashboards to share and present valuable insights for superior decision making.

The following is a more detailed breakdown of the topics covered during the four training sessions.

Session 1: Essential Skills and Knowledge

Learning objectives:

• Getting started with data analysis in Excel.

• The key concepts, tools and techniques required to succeed in data analysis with Excel.

• Data analysis and reporting workflow and its lifecycle.

• A comprehensive review of cell-reference styles—with data analysis in mind.

• What’s in a name? Use Excel’s names system to simplify your spreadsheets and reduce chances of errors.

• Use Excel tables to take control of data structure.

• Understand implicit and explicit intersections in cell references.

• Discover the latest developments in Excel’s calculation engine.

• Using dynamic arrays in calculations.

• Understand implicit intersections

• Create pivot tables.

Session 2: Transform Data with Power Query

Learning objectives:

• Import data into Excel from any source.

• Explore the Power Query editor window.

• Explore the Power Pivot window.

• Get and transform data with Power Query.

• Extract data from a folder of files.

• Extract data directly from websites.

• Managing existing queries and connections.

• Reuse existing queries and eliminate repetitive tasks.

• Combine datasets with Power Query.

• How to pivot and unpivot data—and why.

• Options for loading query results.

• Create pivot tables based on datasets loaded from Power Query.

• Load datasets from Power Query into Power Pivot data model.

• Prepare pivot tables.

Session 3: Power Pivot and Data Modeling

Learning objectives:

• Get started with Power-Pivot data modeling skills.

• Quickly get comfortable with Excel’s Power-Pivot and Data-Model tools.

• Integrate data from disparate sources in Excel’s Data Model.

• Managing data connections.

• Key relational-database concepts for data modeling in Power Pivot.

• Working with Data View and Diagram View in Power Pivot.

• How to structure data for analysis.

• How to create, change, and manage table relationships in a data model.

• How to build pivot tables based on Excel’s data model.

• Important differences between classic and data-model-based pivot tables.

• Working with dates in Power Pivot.

• How to prune and simplify a data model.

• Simplify data-model based pivot tables by creating data hierarchies. 

• Introduction to measures and calculated columns in Power Pivot.

• How to keep a data model shipshape and maintain its integrity.

Session 4: Building Interactive Dashboards

Learning objectives:

• What’s a good dashboard?

• What to display on a dashboard?

• Design a perfect dashboard layout.

• Build a professional-looking dashboard.

• How to display dynamic, refreshable information on dashboards.

• Create dynamic tools for filtering dashboard reports.

• Slicers and timelines on dashboards.

• Drop-down lists on dashboards.

• Dynamic arrays for dashboards.

• Formatting Excel dashboards

• How to reuse dashboard reports.