Skip to content Skip to site navigation Skip to service navigation

Data Analysis Fundamentals with Excel

Class Sessions

Date Delivery Method Cost
  • Thu Aug 18, 9:00 am to 12:00 pm
  • Tue Aug 23, 9:00 am to 12:00 pm
  • Thu Aug 25, 9:00 am to 12:00 pm
  • Mon Aug 29, 9:00 am to 12:00 pm
Live Online - 4 sessions $800

Class Code

ITS-1297

Class Description

Effective immediately in response to COVID-19, most Technology Training classes will be delivered online until further notice.

In advance of each session, Tech Training will provide you with a Zoom link to your class, along with any required class materials.


This course takes you through a solution for collecting, cleaning, importing, managing, and sharing data for operational and reporting purposes using Excel.
 
A story-based approach is used to teach you the data and analytics lifecycle. Learn the how and why behind the options available to you in Excel. These best practices go beyond just tips and tricks; learn pros and cons of the techniques to make you a more proficient analyst.

Learn the fundamentals of data analysis with Excel and gain skills to design, build, and share Excel solutions.

Skill Level: Beginner-Intermediate

Opportunity to learn how to:

  • Get and transform data
  • Structure data for analysis in Excel
  • Design data models
  • Write formulas
  • Create PivotTables and use PivotTable commands
  • Create and format PivotCharts
  • Build a reusable interactive dashboard
  • Present meaningful, accessible analysis for effective decision making

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

Table of Contents

Introduction

Lesson 1: Getting Started with Data Analysis with Excel
Learning objectives

  • 1.1 Discuss course style
  • 1.2 Introduce the Data Lifecycle
  • 1.3 Understand Excel versions

Lesson 2: Using Get and Transform Data
Learning objectives

  • 2.1 Introduce Get and Transform data
  • 2.2 Load an Excel spreadsheet
  • 2.3 Load a folder of data
  • 2.4 Load data from a website
  • 2.5 Understand data source settings
  • 2.6 Introduce the Power Query Editor
  • 2.7 Remove columns
  • 2.8 Filter rows
  • 2.9 Rename columns and set data types
  • 2.10 Add new columns
  • 2.11 Append queries
  • 2.12 Merge queries
  • 2.13 Transform and cleanse data
  • 2.14 Unpivot data
  • 2.15 Better manage Get and Transform

Lesson 3: Structuring and Referencing Data for Analysis within Excel
Learning objectives

  • 3.1 Review cell reference styles and types
  • 3.2 Use names in Excel
  • 3.3 Structure data within Excel tables
  • 3.4 Review formulas, functions, and compatibility
  • 3.5 Spill data with dynamic arrays
  • 3.6 Troubleshoot spill errors
  • 3.7 Understand implicit intersection
  • 3.8 Apply concepts for a data analysis solution

Lesson 4: Utilizing Best Practices for Preparing and Analyzing Data
Learning objectives

  • 4.1 Design complex Excel models
  • 4.2 Author formulas for complex models
  • 4.3 Implement and maintain data integrity
  • 4.4 Create a reusable data quality dashboard
  • 4.5 Design a Budget and Forecast model
  • 4.6 Identify required data structures and functions
  • 4.7 Populate data within the model
  • 4.8 Highlight results and data insights

Lesson 5: Authoring Interactive Reports
Learning objectives

  • 5.1 Create and understand basic PivotTables
  • 5.2 Interact with a PivotTable
  • 5.3 Understand layout options
  • 5.4 Format PivotTables
  • 5.5 Use PivotTable commands
  • 5.6 Filter and sort data in a PivotTable
  • 5.7 Review chart types
  • 5.8 Create PivotCharts
  • 5.9 Format PivotCharts
  • 5.10 Create an interactive dashboard

Lesson 6: Setting up for Collaboration and Distribution
Learning objectives

  • 6.1 Inspect a document for issues
  • 6.2 Check for accessibility and compatibility
  • 6.3 Use collaboration and distribution features
  • 6.4 Protect sheet contents and workbook structure
  • 6.5 Setup a printable package


University IT Technology Training classes are only available to Stanford University staff, faculty, students and Stanford Hospitals & Clinics employees. A valid SUNet ID is needed in order to enroll in a class.