In this self-paced intermediate Excel course, you’ll build on your foundational skills by working with more powerful tools and functions. You’ll learn how to use VLOOKUP and SUMIFS, summarize data with Pivot Tables, sort and filter databases, and manipulate text using split and join functions. The course includes access to an Excel video suite, allowing you to revisit lessons anytime and reinforce concepts at your own pace.
You’ll also have the option to enroll in this course as part of the Excel Bootcamp, which includes the Fundamentals, Intermediate, and Advanced Excel courses at a discount. Throughout the course, you’ll practice splitting and joining text, adding data validation, using named ranges, applying database and logical functions such as VLOOKUP, HLOOKUP, AND, OR, and IF, analyzing large datasets with Pivot Tables, creating advanced combo charts, and reinforcing key concepts through a final project.
Course Outline
Worksheet Management
Navigation
-
Keyboard shortcuts that facilitate quick and easy navigation within cells
Formula Review
-
Review various methods for completing calculations
Working with Text
Splitting Text
-
Use Text to Columns to split text into multiple cells
Joining Text
-
Join text from separate cells
Cell Ranges
Paste Special
-
Apply formats and perform calculations on selected cells
Paste Special Values
-
Hardcode the answer to a formula or function
Named Ranges
-
Assign a name to a range of cells to make it easier to reference those ranges in calculations
Database Functions
VLOOKUP & XLOOKUP
-
Use VLOOKUP and XLOOKUP to find information in a cell range and return information from another cell range
Sort & Filter
-
Use Sort & Filter to find and organize data in large databases
PivotTables
PivotTables
-
Create PivotTables to quickly summarize large databases
PivotTables & Grouping
Multiple PivotTables
-
Create multiple PivotTables on a single worksheet
Logical Functions
IF statements
-
Use IF statements to return output based on the contents of another cell
AND, OR
-
Tests to see whether multiple conditions are true
Math Functions
SUBTOTAL
-
Use SUBTOTAL function to sum/average/count values based on what is not filtered
Statistical Functions
SUMIFS
-
Use SUMIFS function to sum cells based on one or more conditions
COUNTIFS
-
Use COUNTIFS function to count cells based on one or more conditions
Improve Data Quality
Data Validation
-
Restrict the type of data that can be allowed in a cell
Remove Duplicates
-
Eliminate duplicate row data
Advanced Charts
Combo Charts
-
Combine two or more charts into a single chart, with the option of adding a secondary axis
End of Class Project
Project
-
End of class project to review key concepts from the class
This course is available for 30 days. You can choose when to start your access period. Once you activate, you will have 30 days to complete it (access the course materials, quizzes, projects and videos). You may request one extension of seven (7) days. Other extension requests will be evaluated on a case-by-case basis. Videos are not downloadable.
