Advanced Microsoft Excel 2019 (EXC301)

This Advanced Microsoft Excel 2019 course is designed to enhance your skills in utilizing Excel’s powerful features for data analysis and collaboration. Targeted at companies aiming to upskill their employees and individuals looking to work upskill themselves, this course delivers a deep dive into Excel’s advanced functionalities, providing practical tools that will prove beneficial in streamlining your data management processes.

We start the course with Using Pivot Tables, where you learn how to create pivot tables by preparing your data, inserting a pivot table, and creating a pivot table timeline. You'll uncover additional functionalities, such as inserting slicers and utilizing the multi-select option in slicers. Further, you’ll master pivot table enhancements, grouping data, using PowerPivot, and managing relationships. The lesson also covers inserting pivot charts and creating standalone pivot charts, culminating in hands-on exercises to reinforce your pivot table knowledge.

Next, we explore Auditing Worksheets. This section covers tracing precedents and dependents, with practical exercises to solidify these concepts. You'll also learn to show formulas, ensuring your worksheets are accurate and easy to understand.

The Data Tools lesson introduces converting text to columns, linking to external data, and controlling calculation options. You’ll practice data validation and consolidating data, alongside utilizing Goal Seek through targeted exercises, sharpening your ability to manage and manipulate data efficiently.

When it comes to Working with Others, this lesson teaches you to protect worksheets and workbooks by password protecting a workbook, removing workbook metadata, and restoring previous versions. You’ll engage in exercises to apply password protection to both worksheets and specific ranges, ensuring confidential data remains secure. Additionally, you'll learn to mark a workbook as final, indicating completion to your collaborators.

In the Recording and Using Macros lesson, you will gain proficiency in recording macros and copying them from workbook to workbook. You’ll practice running, editing, and adding macros to the Quick Access Toolbar while managing macro security, all through hands-on exercises meant to automate and streamline repetitive tasks.

The course covers various Random Useful Items that boost your productivity, such as inserting and customizing sparklines, using Microsoft Translator, and preparing a workbook for internationalization and accessibility. You'll learn to display data in multiple international formats, modify worksheets for accessibility tool use, and handle international symbols. The lesson also includes importing and exporting files, with practical guidance on importing text files, exporting worksheet data, and charts to Microsoft Word.

By the end of this course, you will be well-versed in leveraging Excel for advanced data analysis, collaboration, and automation. You’ll be equipped to create dynamic pivot tables and charts, secure and share workbooks confidently, audit data accurately, and utilize macros for efficiency. These skills will empower you to maximize Excel’s potential, enhancing your productivity, whether you're opting for individual upskilling or company-wide training.