In this intermediate Excel training, you will learn to manipulate and organize data using advanced sorting and filtering techniques, conditional formatting, PivotTables, and sophisticated charting methods. You will share data across multiple workbooks, incorporate graphics and images, and learn collaborative features for working with others.
Course Delivery Method
- Instruction from a live, experienced Excel professional
- Step-by-step demonstrations of Excel functionality
- Hands-on exercises to practice skills immediately
- One-on-one support and personalized guidance
- Compatible with Excel 2016, 2019, and Microsoft 365 versions
Course Outline and Topics
Managing Workbooks and Worksheets
- Creating and deleting worksheet tabs within a workbook
- Reorganizing worksheet order and structure
- Using hyperlinks for navigation within and between documents
- Creating links to other workbooks for data integration
- Customizing Excel settings and preferences
Named Ranges for Easier Formulas
- Incorporating named ranges in formulas and calculations
- Alternative methods and applications for named ranges
- Quick naming shortcuts and techniques
- Managing and updating named range definitions
Filtering and Sorting Data Tables
- Sorting data in ascending and descending order
- Creating custom sort orders for specialized needs
- Filtering tables to display specific data subsets
- Using AutoFilter for quick filtering operations
- Removing duplicate rows and entries
- Working with structured references in tables
- Using data validation for input control
- Transposing rows and columns for restructuring data
Summarizing and Consolidating Data
- Consolidating data from multiple sources
- Using the subtotals feature to summarize grouped data
PivotTables for Data Analysis
- Understanding PivotTable structure and functionality
- Creating PivotTables from various data sources
- Applying formatting and styling to PivotTables
- Modifying PivotTable layout and organization
- Viewing specific subsets of PivotTable data
- Adding slicers for interactive data filtering
- Creating visual PivotCharts from PivotTable data
Presentation Features for Data Visualization
- Applying conditional formatting rules to highlight data
- Using graphical conditional formatting options
- Creating custom number and cell formats
- Inserting and formatting images and graphics
- Adding SmartArt diagrams for visual representation
Advanced Charting Techniques
- Exploring specialized and non-standard chart types
- Adding trendlines to show patterns and projections
- Creating combination charts with multiple data series
- Saving and reusing chart templates
- Inserting sparklines for compact data visualization
- Using quick analysis tools for fast chart creation
Collaboration and Data Protection
- Managing access through permission controls
- Protecting and securing workbooks from unauthorized changes
- Working with shared workbooks for team collaboration
- Adding and managing comments for communication
- Tracking changes made by all contributors
- Merging workbooks from multiple contributors
Important Note
These course outlines serve as guidelines for typical class content. Topics and emphasis may be adjusted to meet the specific learning objectives of individual classes.
