Microsoft Excel 2019 - Advanced 

Duration: 2 Days

Course Overview:

This advanced Microsoft Excel course is designed for individuals who already possess a solid understanding of Excel fundamentals and wish to deepen their proficiency in using the software for data analysis, automation, and advanced functions. Through hands-on exercises and practical applications, participants will learn advanced techniques, functions, and features to effectively manage, analyze, and visualize data in Excel 2019.

Course Objectives:

1. Enhance proficiency in data analysis using advanced Excel functions and tools.

2. Automate tasks and streamline workflows through macros and advanced automation techniques.

3. Master data visualization techniques for presenting insights effectively.

4. Develop skills in managing large datasets efficiently.

5. Explore advanced data manipulation and analysis methods.

Course Outline:

Advanced Formulas and Functions

• Review of basic functions

• Nested functions and logical functions

• Lookup and reference functions (VLOOKUP, HLOOKUP, INDEX, MATCH)

• Array formulas and advanced calculation techniques

Data Validation and Protection

• Advanced data validation techniques

• Protecting worksheets and workbooks

• Managing permissions and sharing workbooks

• Workbook security best practices

PivotTables and Pivot Charts

• Advanced PivotTable features and options

• Calculated fields and items

• Slicers and Timelines

• Customizing Pivot Charts for data visualization

Session 4: Data Analysis Tools

• What-If Analysis tools (Scenario Manager, Goal Seek, Data Tables)

• Advanced charting techniques

• Sparklines for visualizing trends within data

• Trendlines and forecasting

Advanced Data Manipulation

• Power Query basics and advanced features

• Merging and appending queries

• Cleaning and transforming data with Power Query

• Introduction to Power Pivot and Power View

Macros and Automation

• Recording and running macros

• Editing and debugging macros

• Introduction to VBA (Visual Basic for Applications)

• Automating repetitive tasks using VBA

Conditional Formatting and Data Visualization

• Advanced conditional formatting techniques

• Icon Sets and Data Bars

• Creating interactive dashboards

• Data visualization best practices

Collaboration and Sharing

• Collaborating in real-time using Excel Online

• Co-authoring workbooks

• Reviewing and tracking changes

• Sharing workbooks via OneDrive and SharePoint

Advanced Data Analysis

• Descriptive statistics and data summarization

• Regression analysis

• Goal Seek and Solver for optimization problems

• Data analysis add-ins and tools

Managing Large Datasets

• Data model overview

• Introduction to Power Pivot

• Managing relationships in Power Pivot

• Creating calculated columns and measures

Advanced Charting Techniques

• Customizing charts with advanced options

• Combination charts and dual-axis charts

• Interactive charting with form controls

• Dynamic charts using named ranges

Final Project and Review

• Application of learned concepts in a real-world scenario

• Review of course material and Q&A session

• Final assessment and certification

Prerequisites:

• Basic proficiency in Microsoft Excel (comfortable with basic functions, formatting, and data entry)

• Understanding of fundamental data analysis concepts

Target Audience:

• Business professionals

• Data analysts

• Financial analysts

• Anyone seeking to enhance their Excel skills for advanced data analysis and reporting.

Conclusion:

This advanced Microsoft Excel course offers a structured approach to learning essential skills for data analysis and visualization using Microsoft Excel. By the end of this course, participants will have the knowledge and confidence to leverage Microsoft Excel effectively in their professional roles, enabling them to manage, analyze, and visualize data in Excel 2019.