Excel Intermediate: Take Your Skills Further
Enhance your Excel skills with lists, data validation, pivot tables, Power Pivot tools, and techniques for managing larger datasets efficiently.
Working with Excel Lists
Sort, filter, format lists, create subtotals, highlight duplicates, and remove duplicates.
Excel List Functions
DSUM, DAVERAGE, DCOUNT, and SUBTOTAL functions for advanced data analysis.
Data Validation
Set up rules, decimal validation, custom errors, and dynamic formulas to maintain data integrity.
Importing & Exporting Data
Bring in data from various sources and export Excel data to text files efficiently.
Pivot Tables
Create, modify, group, format, drill down, and filter pivot tables using slicers.
Power Pivot Tools
Build data models, create pivot tables and KPIs for advanced performance tracking.
Working with Large Datasets
Freeze panes, group data, print large datasets, link worksheets, and consolidate data efficiently.
Working with Excel Lists
- Understanding Excel list structure
- Sorting lists: single-level and custom multi-level sorts
- Filtering data using auto-fill options
- Creating subtotals
- Formatting lists
- Conditional formatting to highlight duplicates
- Removing duplicates
Excel List Functions
- Introduction to DSUM() with single or multiple criteria
- DAVERAGE function
- DCOUNT and SUBTOTAL functions
Data Validation
- Understanding data validation
- Creating data validation rules
- Decimal data validation
- Custom validation errors
- Using validation in dynamic formulas
Importing & Exporting Data
- Importing data from various sources
- Importing from older versions and Access databases
- Exporting data to text files
Pivot Tables
- Understanding pivot tables
- Creating and modifying pivot tables
- Grouping data
- Formatting pivot tables
- Drilling down and creating pivot charts
- Filtering data with slicers
Power Pivot Tools
- Introduction to Power Pivot
- Creating data models
- Creating pivot tables and KPIs using data models
Working with Large Datasets
- Freeze panes tool
- Grouping data
- Print options for large datasets
- Linking worksheets and 3D formulas
- Consolidating data from multiple sources