Working with Spreadsheets

Data Analysis Using MS Excel

This course teaches participants how to review and organize data with MS Excel.  Participants will practice cleaning, exploring, analyzing, and visualizing data.  

Course Objectives

After completing this course, participants will be able to:

  • Use Excel to clean, explore, analyze, and aggregate data

  • Work with a variety of functions for data analytics 

  • Work with a variety of functions to prepare and manage reports

  • Build charts to organize statistics and visualize data for presentation

Graphic Spiral

Module 1:  Intro to Excel

  • Discuss what can be accomplished using spreadsheet applications

  • Open, create, and save spreadsheets

  • Determine types of data points

  • Working with Functions

  • Build out basic numerical, date, and string functions

  • Write functions that create summary statistics

  • Use absolute and relative cell references

Graphic Spiral

Module 2:  Using Advanced Functions

  • Working with Conditional Functions

  • Build out conditional functions using if else statements across multiple cells

  • Create conditional functions such as if statements, and/or statements, and nested if statements

  • Create functions that work across multiple values

  • Vlookups, hlookups, xlookups and Index Matching

  • Use lookups and index matching to find values from corresponding columns

  • Learn advanced lookup capabilities

  • Creating functions to solve specific problems (e.g. supply and demand)

Graphic Spiral

Module 3:  Sorting and Summarizing Data

  • Sort and filter data according to specific questions

  • Basic sorting and filtering 

  • Advanced sorting and filtering

  • Pivot Tables

  • Build a pivot table that summarizes data according to categories and statistics

  • Learn advanced pivot table functionalities

Graphic Spiral

Module 4:  Charts and Data Validation

  • Building basic charts in Excel

  • Build charts that summarize data according to categories and statistics

  • Formatting charts

  • Data Validation and Formatting

  • Find and remove unnecessary duplications

  • Restrict value ranges

  • Highlight, format, and create borders/tables for best presentation of data