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
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
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)
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
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