Free Download 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.


Homepage

https://www.udemy.com/course/mo-211-microsoft-excel-expert-microsoft-365-apps/