0569 - Advanced Microsoft Excel 365

Description

Course Details

This Microsoft Excel course covers advanced Excel skills utilizing analytical tools, including the Analysis ToolPak, forecast sheets, and various statistical methods alongside Goal Seek, Solver, and What-If Analysis tools for scenario-based analysis. Data integrity is emphasized through data validation rules, error-checking formulas, conditional formatting, and version control. You will even gain skills in automating tasks with macros and VBA.

PivotTables and PivotCharts are also explored in depth, from basic creation to advanced customization and multi-source analysis. By the end of this Microsoft Excel training program, you will be proficient in Excel's advanced features, ready to apply them to real-world scenarios.

How It Works

This course is fully online, you require internet access and an email account. The course duration is 6 weeks, followed by a 2-week period to complete the final exam (online, open book). Lessons are released on Wednesdays and Fridays of each week, for a total of 12. You are not required to be online at any specific time. In addition to the specific lesson content, there is a discussion board with each lesson and often there is an optional assignment to apply the learning. Following each lesson, there is a short multiple choice quiz. Your score on these quizzes does not count towards the final mark but completing these helps solidify your learning as well as prepare you for the final exam. The final exam is an open-book, multiple choice exam and you need to achieve a minimum of 65% on the final exam to pass the course. There is only one opportunity to pass the exam. A certificate of completion from WatSPEED is available within four weeks of successful completion of each course and can be accessed in your student portal. Many of the Ed2Go courses are eligible towards the various online certificates offered by WatSPEED.

Requirements:

Hardware Requirements:

• This course can be taken on either a PC or Mac.

Software Requirements:

• PC: Windows 10 or later
• Mac: macOS 10.6 or later.
• Browser: The latest version of Firefox, Chrome, or Safari browsers (although Firefox or Chrome is preferred)
• Microsoft Excel 365 for Windows or Microsoft Excel for Mac. Excel 365 Online can be used to study the features it includes, but you will not be able to practice certain skills. For the best experience, use Excel 365 for Windows or Word for Mac to complete this course. If that software isn't available, some exercises can be completed using Excel 365 Online in a web browser on Windows, MacOS, or ChromeOS.
• Adobe Acrobat Reader.
• Software must be installed and fully operational before the course begins

Other:

• Email capabilities and access to a personal email account

Syllabus

Lesson 1 - Looking Up Data in Arrays. This lesson introduces advanced lookup functions that help you retrieve values from large tables and arrays. You will begin with the basics of why lookup functions are important, then practice with VLOOKUP and HLOOKUP to search vertically or horizontally. You will explore their limitations and see how XLOOKUP provides more flexibility. Finally, you will learn how XLOOKUP works with dynamic arrays and discover related functions such as MATCH, INDEX, XMATCH, and FILTER, giving you a complete toolkit for looking up and returning data.

Lesson 2 - Creating Simple Excel Databases. In this lesson, you will learn to treat Excel worksheets like databases. You will start by importing data from text files, renaming tables, and using data entry forms to manage records. Then you will organize data with sorting and filtering, including slicers and advanced filter criteria. The lesson concludes with D-functions, where you will practice using DMAX and other database functions to query tables and return specific results.

Lesson 3 - Managing Data with Power Query and the Data Model. This lesson focuses on transforming and consolidating data. You will load tables into Power Query, clean and rename queries, and refresh data as it updates. You will then practice merging and appending queries, as well as adding calculated fields. Next, you will explore the Data Model, where you'll load multiple tables, create relationships between them, and use Power Pivot to combine data from different sources for more powerful analysis.

Lesson 4 - Analyzing Data. In this lesson, you will explore Excel's advanced analysis tools. You will load the Analysis ToolPak, use the Analyze Data tool, and create forecast sheets. You will also explore descriptive statistics, correlation, histograms, and rank-and-percentile analysis. Then you will move to What-If analysis, using Goal Seek for single-variable solutions, Solver for multi-variable optimization, and Scen

Lesson 5 - Ensuring Data Integrity. This lesson covers techniques for keeping data accurate and reliable. You will use Data Validation to create rules that control entries, limit input to lists, and prevent duplicates. You will also practice error checking with IFERROR, ISNUMBER, and ISDATE, as well as formulas that cross-check data. Finally, you will explore version control and tracking features to monitor edits and prevent data loss.arios for testing different input combinations.

Lesson 6 - Conditional Formatting. In this lesson, you will learn how to highlight data dynamically with conditional formatting. You will start with simple rules based on cell values and icon sets, then practice modifying and managing multiple rules. You will also apply advanced techniques such as top/bottom rules, formulas that control formatting, and rules that identify unique or duplicate values.

Lesson 7 - Creating and Using Macros. This lesson introduces automation with macros. You will learn about macro security, enabling macro recording, and preparing macro-enabled files. Then you will record and test your first macros, assigning them to keyboard shortcuts, buttons on the Quick Access Toolbar, or even custom ribbon commands. The lesson concludes with an introduction to the VBA editor, giving you a first look at how recorded macros work and how you can edit them.

Lesson 8 - PivotTable Concepts. In this lesson, you will explore the fundamentals of PivotTables. You will learn what they are, how to plan and create them, and how to navigate their interface. You will practice building PivotTables from an exercise scenario, layering and reordering fields, and then use GETPIVOTDATA to reference PivotTable values accurately in formulas.

Lesson 9 - PivotTable Design and Formatting. This lesson focuses on customizing PivotTables for readability and presentation. You will control subtotals, grand totals, and math operations. You will then practice changing layouts, applying styles, and formatting values within PivotTables. Finally, you will learn how to copy, move, and manage PivotTables while understanding how the pivot cache works.

Lesson 10 - Calculated Fields, PivotTable Options, and Field Settings. In this lesson, you will expand PivotTable functionality with customization tools. You will create calculated fields to perform custom math inside a PivotTable. You will also explore PivotTable options, including naming, formatting, filtering, display, and printing settings. Finally, you will practice adjusting field settings and value field settings to refine results.

Lesson 11 - Sorting, Grouping, and Filtering in PivotTables. This lesson explores ways to manage large PivotTables. You will practice sorting rows, columns, and values, including custom sort orders. You will also group data by categories or dates, and apply filters using labels, values, and report filters. To work more efficiently with dates, you will learn to control automatic grouping. The lesson concludes with hands-on practice using slicers and timelines to filter PivotTables interactively.

Lesson 12 - Creating PivotCharts. This final lesson shows how to visualize PivotTable data with PivotCharts. You will create PivotCharts, resize and move them, and apply filters directly within the chart. You will customize chart types, layouts, and formatting to suit your needs. The course concludes with working in the Data Model, where you will create PivotTables and PivotCharts from multiple sources to build rich, multi-dimensional reports.

Applies Towards the Following Certificates

• Microsoft Office Elements : Mandatory