Tuf Security

Advanced Excel Course Syllabus

1. Module 01: Introduction to Advanced Excel

   Understanding the advanced features of Excel, including a quick recap of basic functions and formulas, interface navigation, and setting up a professional workspace.

2. Module 02: Advanced Functions and Formulas

   Working with complex functions such as VLOOKUP, HLOOKUP, INDEX & MATCH, and array formulas to automate tasks and data manipulation.

3. Module 03: Conditional Formatting

   Using conditional formatting to highlight trends, analyze data, and apply color scales, data bars, icon sets, and custom rules.

4. Module 04: Pivot Tables and Pivot Charts

   Mastering Pivot Tables for summarizing, analyzing, exploring, and presenting data. Creating Pivot Charts for interactive data visualization.

5. Module 05: Data Validation and Protection

   Ensuring data integrity using data validation techniques, drop-down lists, custom validation rules, and protecting sheets and workbooks from unauthorized editing.

6. Module 06: Advanced Charting Techniques

   Creating and customizing advanced charts like combination charts, sparklines, waterfall charts, and interactive charts to visualize complex data.

7. Module 07: Power Query for Data Transformation

   Using Power Query to import, clean, transform, and automate repetitive tasks with data from various sources like CSV, databases, and web.

8. Module 08: Power Pivot and DAX (Data Analysis Expressions)

   Creating data models with Power Pivot, working with relationships, and using DAX functions for complex calculations and analysis.

9. Module 09: Automating Tasks with Macros

   Using Excel VBA (Visual Basic for Applications) to create Macros that automate repetitive tasks, and introducing basic programming concepts to enhance efficiency.

10. Module 10: Data Analysis and Forecasting

    Utilizing advanced data analysis tools like Solver, Goal Seek, and Forecasting functions to make predictions and optimize decisions.

11. Module 11: Dashboard Creation and Reporting

    Designing interactive dashboards with slicers, timelines, and dynamic reports to help stakeholders make informed decisions with real-time data.

12. Module 12: Collaborating and Sharing Excel Files

    Working collaboratively with Excel, sharing workbooks, using comments, and tracking changes. Introduction to Excel Online for real-time collaboration.

13. Module 13: Excel Best Practices and Tips

    Learning Excel best practices to improve productivity, including keyboard shortcuts, customizing the ribbon, and efficient use of formulas and functions.