Course Module of Advanced Excel
Session 1: Topic 1: Introduction to Advanced Excel
● Overview of Excel features and capabilities
● Understanding data organization and management
● Navigating the Excel interface efficiently
● Introduction to keyboard shortcuts and productivity tips
Topic 2: Advanced Formulas and Functions
● Working with logical functions (IF, AND, OR)
● Utilizing lookup functions (VLOOKUP, HLOOKUP, INDEX-MATCH)
● Implementing mathematical and statistical functions
● Exploring text manipulation functions
● Introduction to array formulas
Session 2: Topic 1: Advanced Data Manipulation
● Using advanced filtering techniques (AutoFilter, Advanced Filter)
● Applying formulas across multiple sheets with 3D references
● Working with named ranges and range names
● Using data tables and scenarios for "What-If" analysis
● Introduction to Power Query for data transformation and cleansing
Topic 2: Advanced Charting and Visualization
● Creating dynamic charts using named ranges and formulas
● Customizing chart elements and formatting options
● Creating combination charts and dual-axis charts
● Building interactive dashboards with slicers and timelines
● Introduction to Sparklines for data visualization
Session 3: Topic 1: Advanced Data Analysis
● Using advanced filters and criteria
● Performing advanced statistical analysis (correlation, regression)
● Analyzing data with the Analysis ToolPak add-in
● Using goal seek and solver for optimization problems
● Introduction to Power Pivot for data modeling and analysis
Topic 2: Advanced Excel Tips and Tricks
● Customizing the Excel ribbon and quick access toolbar
● Using conditional formulas and formatting for dynamic reports
● Working with data validation and drop-down lists
● Advanced data cleaning and manipulation techniques
● Time-saving shortcuts and productivity hacks
Session 4: Topic 1: Advanced PivotTable Techniques
● Grouping and ungrouping data in PivotTables
● Creating calculated fields and items
● Using slicers and timelines for interactivity
● Utilizing PivotTable data analysis features
● Advanced PivotTable formatting and customization
Topic 2: Collaborating with Excel
● Sharing and protecting workbooks
● Tracking changes and comments
● Co-authoring and real-time collaboration
● Using data linking and consolidation
● Importing and exporting data from external sources
Session 5 & 6: Topic 1: Data Analysis Tools
● Sorting and filtering data effectively
● Using conditional formatting to highlight data
● Applying data validation rules
● Utilizing pivot tables for data analysis and reporting
● Creating and customizing charts to visualize data
Topic 2: Advanced Data Validation and Conditional Formatting
● Creating custom data validation rules
● Using formulas and validation lists
● Applying conditional formatting with formulas
● Icon sets and data bars for visual indicators
● Managing conditional formatting rules and priorities
Topic 3: Advanced Excel Tips and Tricks II
● Advanced data analysis with array formulas
● Using Goal Seek and Solver for complex problems
● Introduction to Excel add-ins for specialized tasks
● Working with external data connections
● Tips for optimizing and improving spreadsheet performance
