Mo-211: Microsoft Excel Expert (Microsoft 365 Apps)

Learn the skills for the new MO-211 Excel certification. Create PivotTables, simple macros, advanced charts and formulas

What you'll learn

Manage workbook options and settings, including preparing workbooks for collaboration

Manage and format data

Fill cells based on existing data, format and validate data and apply advanced conditional formatting

Create advanced formulas and macros

Logical functions include IFS, SWITCH, SUMIF, SUMIFS, AND, OR, NOT and LET

Lookup functions are XLOOKUP, VLOOKUP, HLOOKUP, MATCH and INDEX

Other functions include WEEKDAY, WORKDAY, NPER, PMT, FILTER and SORTBY

Manage PivotTables, PivotCharts and advanced charts, including g Box & Whisker, Combo, Funnel, Histogram, Sunburst, and Waterfall charts

Requirements

Before beginning, you will need to know how to use a Windows computer, including a mouse.

You also need to have all of the skills covered by the MO-210 Excel exam.

You should have MS Excel (or Office) from 2022 or later installed on your PC computer. This program is also known as a Microsoft 365 App.

Description

The MO-211 certification is the new Expert certification for Excel, launched in April 2023. Microsoft says that this certification demonstrates that you have the skills needed to get the most out of Excel by earning a Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps) certification.This course has been created using Udemy's Accessibility guidelines. It follows on from your preparations for the MO-210 Excel certification. This content is available in "MO-210: Microsoft Excel (from beginner to intermediate)", which is published on Udemy.In this MO-211 course:We’ll start with managing workbook options and settings. We’ll reference data in other workbooks, manage workbook versions, and prepare workbooks for collaboration by protecting their content and structure. We'll also format and valid data, including creating custom number formats.We’ll then manage advanced charts and tables. We’ll create PivotTables, configuring value field settings and adding slicers, with create and modify PivotCharts, and create and modify advanced charts, including Box & Whisker, Funnel, Histogram, Sunburst and Waterfall charts.We’ll then create advanced formulas and macros. We’ll use functions for performing logical operations, looking up data, use advanced date and time functions, perform data analysis and troubleshoot formulas. This will also include newer Excel functions, such as XLOOKUP, LET, FILTER and SORTBY.Finally, we’ll look at other Excel Expert topics. We’ll fill cells based on existing data, we'll create custom conditional formatting rules, and create and manage simple VBA macros.No prior knowledge is required. And there are 10 Practice Activities, 10 quizzes and a Practice Test to help you remember the information, so you can be sure that you are learning.Once you have completed this course, you will have an expanded knowledge of Microsoft Excel. With some practice, you could even take the official Microsoft MO-211 exam, which gives you the "Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps)" certificate. This would look good on your CV or resume. It is also the first stage to you getting the "Microsoft Office Specialist: Expert (Microsoft 365 Apps)" certification.

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Welcome to Udemy

Lecture 3 How to get the best out of your Udemy course

Lecture 4 Do you want auto-translated subtitles in more languages?

Lecture 5 Curriculum

Lecture 6 Resources

Section 2: Manage workbooks and Prepare workbooks for collaboration

Lecture 7 Reference data in other worksheets

Lecture 8 2. Reference data in other workbooks

Lecture 9 4. Manage workbook versions

Lecture 10 5, 6. Protect worksheets and cell ranges, and Restrict editing

Lecture 11 7. Protect workbook structure

Lecture 12 Practice Activity Number 1

Lecture 13 Practice Activity Number 1 - The Solution

Section 3: Format and validate data

Lecture 14 12. Create custom number formats - integers

Lecture 15 12. Create custom number formats - fractions

Lecture 16 12. Create custom number formats - dates, and adding text and symbols

Lecture 17 12. Create custom number formats - sections and colors

Lecture 18 13. Configure data validation - numbers

Lecture 19 13. Configure data validation - dates, text, and circle invalid data

Lecture 20 16. Remove duplicate records

Lecture 21 Practice Activity Number 2

Lecture 22 Practice Activity Number 2 - The Solution

Section 4: Create and modify PivotTables and PivotCharts

Lecture 23 39. Create PivotTables

Lecture 24 44. Configure value field settings

Lecture 25 40. Modify field selections and options - PivotTable options and Refresh

Lecture 26 40. Modify field selections and options - the PivotTable Analyze and Design tabs

Lecture 27 42. Group PivotTable data

Lecture 28 41. Create slicers

Lecture 29 41. Create timelines

Lecture 30 43. Add calculated fields

Lecture 31 45-48. PivotCharts

Lecture 32 Practice Activity Number 3

Lecture 33 Practice Activity Number 3 - The Solution

Section 5: Create and modify advanced charts

Lecture 34 37. Create and modify dual-axis charts

Lecture 35 38a. Box & Whisker chart

Lecture 36 38b. Combo chart, and adding a dynamic chart title

Lecture 37 38c. Funnel chart

Lecture 38 38d. Histogram chart

Lecture 39 38e. Sunburst chart

Lecture 40 38f. Waterfall chart

Lecture 41 Practice Activity Number 4

Lecture 42 Practice Activity Number 4 - The Solution

Section 6: Perform logical operations in formulas

Lecture 43 20a, n, 33. IF(), NOT(), and using the Evaluate Formula button

Lecture 44 20l, m. AND() and OR()

Lecture 45 20l, m. Nesting AND() and OR() functions

Lecture 46 20b, c. IFS(), SWITCH()

Lecture 47 20d-f. SUMIF(), AVERAGEIF(), COUNTIF()

Lecture 48 20g-k. SUMIFS(), AVERAGEIFS(), COUNTIFS(), MAXIFS(), MINIFS()

Lecture 49 20o. LET()

Lecture 50 Practice Activity Number 5

Lecture 51 Practice Activity Number 5 - The Solution

Section 7: Look up data by using functions, and use advanced date and time functions

Lecture 52 21b-c. The VLOOKUP() function

Lecture 53 21b. The difference between VLOOKUP() and SUMIF()

Lecture 54 21c. HLOOKUP(), and looking for approximate matches

Lecture 55 21d. MATCH()

Lecture 56 21e. INDEX()

Lecture 57 21a. XLOOKUP()

Lecture 58 22, 23. NOW(), TODAY() and WEEKDAY()

Lecture 59 23. WORKDAY()

Lecture 60 Practice Activity Number 6

Lecture 61 Practice Activity Number 6 - The Solution

Section 8: Perform data analysis

Lecture 62 25. Perform what-if analysis by using Goal Seek

Lecture 63 25b. Perform what-if analysis by using Scenario Manager

Lecture 64 26. Forecast data by using the AND(), IF(), and NPER() functions

Lecture 65 27. Calculate financial data by using the PMT() function

Lecture 66 28. Filter data by using FILTER()

Lecture 67 29. Sort data by using SORTBY()

Lecture 68 Practice Activity Number 7

Lecture 69 Practice Activity Number 7 - The Solution

Section 9: Other formula topics

Lecture 70 8. Configure formula calculation options

Lecture 71 11. Generate numeric data by using RANDARRAY()

Lecture 72 14. Group and ungroup data

Lecture 73 15. Calculate data by inserting subtotals and totals

Lecture 74 24. Summarize data from multiple ranges by using the Consolidate feature

Lecture 75 30. Trace precedence and dependence

Lecture 76 31. Monitor cells and formulas by using the Watch Window

Lecture 77 32. Validate formulas by using error checking rules

Lecture 78 Practice Activity Number 8

Lecture 79 Practice Activity Number 8 - The Solution

Section 10: Fill cells based on existing data, and apply advanced conditional formatting

Lecture 80 9. Fill cells by using Flash Fill

Lecture 81 10. Fill cells by using advanced Fill Series options

Lecture 82 17, 19. Create custom conditional formatting rules and managing rules

Lecture 83 18. Create conditional formatting rules that use formulas - number conditions

Lecture 84 18. Create conditional formatting rules that use formulas - date/text conditions

Lecture 85 Practice Activity Number 9

Lecture 86 Practice Activity Number 9 - The Solution

Section 11: Create and modify simple macros

Lecture 87 34, 35. Record and name simple macros

Lecture 88 3. Enable macros in a workbook

Lecture 89 36. Edit simple macros

Lecture 90 1. Copy macros between workbooks

Lecture 91 Practice Activity Number 10

Lecture 92 Practice Activity Number 10 - The Solution

Section 12: Conclusion

Lecture 93 What's Next?

Lecture 94 Thank you very much

This course is for you if you want to develop your Microsoft Excel Expert skills.,This course will use the latest version of Excel, but is also for you if you use any modern version - Excel 2007, 2010, 2013, 2016 or 2019.,This course is for you if you want to become certified in Microsoft Excel, as by the end you should the knowledge to because certified as a Microsoft Expert, if you wish to do so.,You will cover the content tested in Microsoft Office Specialist Exams MO-211 (for Excel 2022 and later),This course may not be for you if you want to learn Microsoft Excel for Macintosh computers.