English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 94 lectures (8h 6m) | 5.35 GB
Learn the skills for the new MO-211 Excel certification. Create PivotTables, simple macros, advanced charts and formulas
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.
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
Table of Contents
Introduction
1 Introduction
2 Welcome to Udemy
3 How to get the best out of your Udemy course
4 Do you want autotranslated subtitles in more languages
5 Curriculum
6 Resources
Manage workbooks and Prepare workbooks for collaboration
7 Reference data in other worksheets
8 Reference data in other workbooks
9 Manage workbook versions
10 Protect worksheets and cell ranges and Restrict editing
11 Protect workbook structure
12 Practice Activity Number 1
13 Practice Activity Number 1 The Solution
Format and validate data
14 Create custom number formats integers
15 Create custom number formats fractions
16 Create custom number formats dates and adding text and symbols
17 Create custom number formats sections and colors
18 Configure data validation numbers
19 Configure data validation dates text and circle invalid data
20 Remove duplicate records
21 Practice Activity Number 2
22 Practice Activity Number 2 The Solution
Create and modify PivotTables and PivotCharts
23 Create PivotTables
24 Configure value field settings
25 Modify field selections and options PivotTable options and Refresh
26 Modify field selections and options the PivotTable Analyze and Design tabs
27 Group PivotTable data
28 Create slicers
29 Create timelines
30 Add calculated fields
31 PivotCharts
32 Practice Activity Number 3
33 Practice Activity Number 3 The Solution
Create and modify advanced charts
34 Create and modify dualaxis charts
35 a Box Whisker chart
36 b Combo chart and adding a dynamic chart title
37 c Funnel chart
38 d Histogram chart
39 e Sunburst chart
40 f Waterfall chart
41 Practice Activity Number 4
42 Practice Activity Number 4 The Solution
Perform logical operations in formulas
43 a n 33 IF NOT and using the Evaluate Formula button
44 l m AND and OR
45 l m Nesting AND and OR functions
46 b c IFS SWITCH
47 df SUMIF AVERAGEIF COUNTIF
48 gk SUMIFS AVERAGEIFS COUNTIFS MAXIFS MINIFS
49 o LET
50 Practice Activity Number 5
51 Practice Activity Number 5 The Solution
Look up data by using functions and use advanced date and time functions
52 bc The VLOOKUP function
53 b The difference between VLOOKUP and SUMIF
54 c HLOOKUP and looking for approximate matches
55 d MATCH
56 e INDEX
57 a XLOOKUP
58 NOW TODAY and WEEKDAY
59 WORKDAY
60 Practice Activity Number 6
61 Practice Activity Number 6 The Solution
Perform data analysis
62 Perform whatif analysis by using Goal Seek
63 b Perform whatif analysis by using Scenario Manager
64 Forecast data by using the AND IF and NPER functions
65 Calculate financial data by using the PMT function
66 Filter data by using FILTER
67 Sort data by using SORTBY
68 Practice Activity Number 7
69 Practice Activity Number 7 The Solution
Other formula topics
70 Configure formula calculation options
71 Generate numeric data by using RANDARRAY
72 Group and ungroup data
73 Calculate data by inserting subtotals and totals
74 Summarize data from multiple ranges by using the Consolidate feature
75 Trace precedence and dependence
76 Monitor cells and formulas by using the Watch Window
77 Validate formulas by using error checking rules
78 Practice Activity Number 8
79 Practice Activity Number 8 The Solution
Fill cells based on existing data and apply advanced conditional formatting
80 Fill cells by using Flash Fill
81 Fill cells by using advanced Fill Series options
82 Create custom conditional formatting rules and managing rules
83 Create conditional formatting rules that use formulas number conditions
84 Create conditional formatting rules that use formulas datetext conditions
85 Practice Activity Number 9
86 Practice Activity Number 9 The Solution
Create and modify simple macros
87 Record and name simple macros
88 Enable macros in a workbook
89 Edit simple macros
90 Copy macros between workbooks
91 Practice Activity Number 10
92 Practice Activity Number 10 The Solution
Conclusion
93 Whats Next
94 Thank you very much
Resolve the captcha to access the links!