Advanced Excel Course

Learn Advanced Excel through practical and job-oriented training at Acrosys Technologies.
This course covers advanced formulas, data cleaning, PivotTables, dashboards, Power Query,
Power Pivot, DAX, Macros and VBA. Students will work with real-time datasets and complete
a practical final project.

Advanced Excel Course Syllabus

Module 1: Excel Essentials Review

• Advanced workbook and worksheet management


• Customizing the Excel interface, Quick Access Toolbar and Ribbons


• Absolute, relative and mixed cell references


• Named ranges and dynamic named ranges

Module 2: Advanced Formulas and Functions

• Logical functions: IF, IFS, AND, OR and NOT


• Lookup and reference functions: VLOOKUP, HLOOKUP, XLOOKUP, INDEX and MATCH


• Text functions: LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE and TEXTJOIN


• Date and time functions: TODAY, NOW, EOMONTH, DATEDIF and NETWORKDAYS


• Statistical functions: AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF and SUMIFS


• Error handling functions: IFERROR, ISERROR and ISNA


• Advanced formula nesting

Module 3: Data Management and Cleaning

• Data validation, drop-down lists and custom validation rules


• Removing duplicate values and handling blank cells


• Flash Fill and Text-to-Columns


• Find and Replace using wildcards


• Advanced sorting and multi-level filtering


• Using Excel Tables and structured references

Module 4: Data Analysis Tools

• What-If Analysis using Scenario Manager, Goal Seek and Data Tables


• Solver Add-in for optimization problems


• Forecasting using Forecast Sheets and the TREND function


• Descriptive statistics using the Data Analysis ToolPak


• Best practices for working with large datasets

Module 5: PivotTables and PivotCharts

• Creating and formatting PivotTables


• Grouping, filtering and sorting data in PivotTables


• Calculated fields and calculated items


• Creating PivotCharts and using slicers


• Power Pivot basics, Data Models and table relationships

Module 6: Advanced Charting and Visualization

• Creating combination charts such as line and column charts


• Using secondary axes


• Conditional formatting using formulas


• Using Sparklines for trend analysis


• Creating interactive charts using drop-down lists and slicers


• Building KPI dashboards using charts and shapes

Module 7: Power Query – Get and Transform Data

• Importing data from CSV files, databases and web sources


• Cleaning and transforming data using Power Query


• Merging and appending queries


• Creating reusable data pipelines


• Automating report updates using Power Query

Module 8: Power Pivot and DAX

• Understanding Data Models in Excel


• Creating relationships between multiple tables


• Introduction to DAX – Data Analysis Expressions


• Calculated columns and measures


• Time intelligence functions including YTD, QTD and MTD


• Building advanced PivotTables using DAX

Module 9: Automation with Macros and VBA

• Recording and editing Macros


• Relative and absolute Macros


• Introduction to the VBA Editor


• VBA coding basics


• Automating repetitive tasks using VBA


• Creating custom functions using VBA and UDFs


• Building simple UserForms

Module 10: Collaboration and Security

• Protecting worksheets and workbooks


• Using Excel with OneDrive and SharePoint for co-authoring


• Tracking changes and using comments


• Digital signatures and password protection


• Excel integration with Outlook, Word and PowerPoint

Final Project and Assessment

• Clean and analyse a large business dataset


• Build an interactive dashboard using PivotTables, slicers and charts


• Use Power Query for automated reporting


• Apply advanced formulas for business logic


• Create a macro-enabled Excel automation tool