Learn intermediate Excel functions like VLOOKUP and SUMIFs, and how to summarize data with Pivot Tables, Sort & Filter databases, and split and join text. Gain the skills needed to utilize complex Excel functions and prepare for more advanced training.
Overview
Syllabus
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 cell range and return information from another cell range
Sort & Filter
- Use Sort & Filter to find and organize data in large databases
Pivot Tables
Pivot Tables
- Create Pivot Tables to quickly summarize large databases
Pivot Tables & Grouping
- Group within Pivot Tables
Multiple Pivot Tables
- Create multiple Pivot Tables 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
Taught by
Brian McClain, Mourad Kattan, Adebayo Norman, and Garfield Stinvil