Microsoft Excel for Mac 2: Intermediate to Expert advanced

Focused View

Phillip Burton,I Do Data Limited

10:38:15

156 View

1 - Introduction

1 - Introduction.mp4

01:45

2 - Welcome to Udemy.html

3 - Introduction to Udemy.mp4

02:00

4 - Do you want autotranslated subtitles in more languages.html

2 - Level 6 Section 1 Manage workbooks

5 - Level-6-Session-1-Start.xlsx

5 - Save a workbook as a template.mp4

05:41

6 - Modifying custom templates.mp4

02:23

7 - Copy styles from template to template.mp4

04:02

8 - Using a formula to refer to another spreadsheet.mp4

08:12

9 - Reference data in another workbook.mp4

04:06

10 - Refresh and edit workbook links.mp4

08:17

11 - Reference data by using structured references.mp4

08:15

12 - PracticeInvoice1.xlsx

12 - PracticeInvoice2.xlsx

12 - Practice Activity Number 1.html

13 - Answers to Practice Activity 1.mp4

05:28

3 - Level 6 Section 2 Protect documents and Manage workbook review

14 - Level-6-Session-2-Start.xlsx

14 - Restrict editing by protecting a spreadsheet.mp4

04:24

15 - Lock and unlock cells.mp4

03:44

16 - Lock and unlock objects and hide formulas.mp4

02:23

17 - Hiding Formulas.mp4

03:15

18 - Protect Workbook.mp4

04:10

19 - Sharing Workbooks.mp4

05:04

20 - Track Changes.mp4

07:11

21 - Encrypt a workbook with a password.mp4

04:42

22 - PracticeActivity.xlsx

22 - Practice Activity Number 2.html

23 - Answers to Practice Activity 2.mp4

03:05

4 - Level 6 Section 3 Using Logical and Statistical Functions

24 - Level-6-Session-3-Start-Mac.xlsx

24 - Perform logical operations by using the NOT function.mp4

04:35

25 - Perform logical operations by using AND and OR.mp4

05:09

26 - Perform logical operations by using nested functions.mp4

08:25

27 - Perform statistical operations by using COUNTIFS.mp4

07:05

28 - Perform statistical operations by using SUMIFS and AVERAGEIFS.mp4

05:27

29 - Using FREQUENCY.mp4

02:34

30 - Trapping errors with IFERROR and ISERROR.mp4

03:06

31 - PracticeActivity.xlsx

31 - Practice Activity Number 3.html

32 - Answers to Practice Activity 3.mp4

05:01

5 - Level 7 Section 1 Creating PivotTables

33 - Creating our first PivotTable.mp4

07:41

33 - Level-7-Session-1-Start.xlsx

34 - PivotTable Options.mp4

11:09

35 - Filtering Data including using Slicers.mp4

07:46

36 - Group PivotTable data.mp4

06:43

37 - Reference data by using GETPIVOTDATA and add and utilise calculated fields.mp4

08:14

38 - PracticeActivity.xlsx

38 - Practice Activity Number 4.html

39 - Answers to Practice Activity 4.mp4

01:57

6 - Level 7 Section 2 Advanced Formatting

40 - Advanced-Formatting-Mac.xlsx

40 - Formatting number values.mp4

05:09

41 - Custom formatting in graphs and fractions.mp4

07:50

42 - CustomFormatting.pdf

42 - Custom Formatting number formatting.mp4

09:22

43 - CustomFormatting.pdf

43 - Custom Formatting dates and text.mp4

13:28

44 - CustomFormatting.pdf

44 - Custom Formatting sections and colors.mp4

11:44

45 - Create and manage custom conditional formatting rules.mp4

09:23

46 - Create conditional formatting rules that use formulas.mp4

09:00

47 - PracticeActivity.xlsx

47 - Practice Activity Number 5.html

48 - Answers to Practice Activity 5.mp4

03:10

7 - Level 7 Session 3 Creating PivotCharts and advanced charts

49 - Create PivotCharts.mp4

06:38

49 - Level-7-Session-3-Start.xlsx

50 - Create dualaxis charts.mp4

04:36

51 - Add trendlines to charts.mp4

07:56

52 - Save a chart as a template and create custom chart templates.mp4

05:00

53 - View chart animations.mp4

04:30

54 - PracticeActivity.xlsx

54 - Practice Activity Number 6.html

55 - Answers to Practice Activity 6.mp4

05:02

8 - Level 7 Session 4 Apply advanced date and time and financial functions

56 - Level-7-Session-4-Start.xlsx

56 - Reference the date and time by using the NOW and TODAY functions.mp4

06:48

57 - Extract dates and times.mp4

07:46

58 - Serialise numbers by using date and time functions.mp4

08:26

59 - Financial functions.mp4

07:36

60 - PracticeActivity.xlsx

60 - Practice Activity Number 7.html

61 - Answers to Practice Activity 7.mp4

01:36

9 - Level 8 Session 1 Troubleshoot formulas

62 - Level-8-Session-1-Mac.xlsx

62 - Trace precedence and dependence.mp4

07:06

63 - Checking for errors.mp4

07:11

64 - Enable iterative calculations and setting iterative calculation options.mp4

04:43

65 - Perform whatif analysis by using Goal Seek.mp4

08:25

66 - Enabling or disabling automatic workbook calculation.mp4

04:35

67 - PracticeActivity.xlsx

67 - Practice Activity Number 8.html

68 - Answers to Practice Activity 8.mp4

02:37

10 - Level 8 Section 2 Prepare a workbook for internationalisation accessibility

69 - Display data in multiple international formats.mp4

07:26

69 - Level-8-Session-2-Mac.xlsx

70 - Apply international currency formats.mp4

03:17

71 - Manage multiple options for Body and Heading fonts.mp4

03:06

72 - Modify worksheets for use with accessibility tools.mp4

05:15

73 - PracticeActivity.xlsx

73 - Practice Activity Number 9.html

74 - Answers to Practice Activity 9.mp4

01:07

11 - Level 8 Section 3 Manage and reference defined names

75 - Level-8-Session-3-Start-Mac.xlsx

75 - Name cells and reference a named formula.mp4

04:34

76 - Name data ranges and tables.mp4

03:48

77 - Modify named ranges by using Names Manager.mp4

07:26

78 - Navigate across worksheets by using named ranges.mp4

03:32

79 - PracticeActivity.xlsx

79 - Practice Activity Number 10.html

80 - Answers to Practice Activity 10.mp4

01:51

12 - Level 8 Section 4 Formula Arrays

81 - Level-8-Session-4-Start-Mac.xlsx

81 - Using an array formula and SUMing the result.mp4

06:58

82 - Using an array formula and outputting the result to a range.mp4

05:57

83 - Using MINFS and MAXIFS and equivalent Array Formulas.mp4

08:42

84 - Using the TRANSPOSE function.mp4

05:45

85 - Limitations of array formulas.mp4

04:56

86 - Bonus Addingdeleting rows and array formulas.mp4

06:59

87 - PracticeActivity.xlsx

87 - Practice Activity Number 11.html

88 - Answers to Practice Activity 11.mp4

01:46

13 - Level 9 Session 3 Create and modify custom workbook elements

89 - Level-9-Session-3-Start-Mac.xlsx

89 - Populate cells by using advanced Fill Series options.mp4

09:10

90 - Create custom colour formats.mp4

04:39

91 - Create and modify cell styles.mp4

05:04

92 - Create and modify custom themes.mp4

02:19

93 - PracticeActivity.xlsx

93 - Practice Activity Number 14.html

94 - Answers to Practice Activity 14.mp4

02:23

14 - Level 9 Session 4 Create advanced formulas

95 - Level-9-Session-4-Start-Mac.xlsx

95 - Look up data by using the VLOOKUP function.mp4

07:39

96 - Look up data by using the HLOOKUP function.mp4

02:36

97 - The difference between VLOOKUP and SUMIF or SUMIFS.mp4

06:19

98 - Using VLookup for appropriate matches.mp4

05:01

99 - Look up data by using the MATCH function.mp4

04:56

100 - Look up data by using the INDEX function.mp4

06:17

101 - Using the INDIRECT function.mp4

04:29

102 - Create a dynamic named range.mp4

08:41

103 - Use the Solver addin.mp4

08:07

104 - Perform whatif analysis by using Scenario Manager create compare scenarios.mp4

07:00

105 - PracticeActivity.xlsx

105 - Practice Activity Number 15.html

106 - Answers to Practice Activity 15.mp4

04:05

15 - Level 10 Section 1 Link form controls to cells

107 - Inserting form controls.mp4

02:05

108 - Insert Scroll bars and Setting form properties.mp4

06:31

109 - Insert List and Combo boxes.mp4

06:15

110 - Insert check boxes Insert labels and other form controls.mp4

06:44

111 - Practice Activity Number 16.html

16 - Conclusion

112 - Thank you very much.mp4

01:02

17 - Level 9 Section 1 PC only Perform data analysis and business intelligence

113 - Welcome to Level 9 Sections 1 and 2.html

114 - Choosing data sets from external data connections.mp4

07:16

114 - Level-9-Session-1-Start.xlsx

115 - Manage external data connections.mp4

09:06

116 - Power Query installing in Excel 2010 and 2013.mp4

05:59

117 - Importing Data and Display and connect to data.mp4

05:10

118 - Managing Queries.mp4

04:09

119 - Transform and redisplaying reloading data.mp4

05:36

120 - Combine or Append data sets.mp4

04:41

121 - Merge data sets.mp4

04:43

122 - Consolidate data.mp4

07:06

123 - Consolidating data using PivotTables.mp4

06:06

124 - Level9Section1PracticeActivity.xlsx

124 - Practice Activity Number 12.html

125 - Answers to Practice Activity 12.mp4

05:47

18 - Level 9 Section 2 PC only The Data Model and PowerPivot

126 - Introduction to the Data Model.mp4

05:07

126 - Level-9-Session-1-Start.xlsx

127 - Activating PowerPivot on Excel 2013 and 2016.mp4

02:43

128 - Activating PowerPivot on Excel 2010.mp4

04:05

129 - Utilise PowerPivot and create a PivotTable based on the Excel data model.mp4

04:17

130 - Manage relationships.mp4

04:28

131 - Use cube functions to get data out of the Excel data model.mp4

06:24

132 - PracticeActivity.xlsx

132 - Practice Activity Number 13.html

133 - Answers to Practice Activity 13.mp4

02:01

19 - Bonus Section

134 - Bonus Lecture.mp4

07:08

134 - idodatabonuslecture230113.zip

Description

Get Expert certified! Levels 6-9 for Microsoft Excel for Mac. Covers 77-728, 77-427 & 77-428.

What You'll Learn?

Manage workbooks and workbook review
Create and manage PivotTables
Advanced formulas
Advanced formatting

Who is this for?

This is for you if you want to develop your Excel skills to an Expert level.

This is also for you if you want to take the Microsoft Office Specialist Expert Exams.

This may not be for you if you are not already at least at an Intermediate level (already done levels 1-5 or equivalent).

More details

Description
Building on the skills learnt in "Microsoft Excel for Mac 1", here'sÂ levelsÂ 6-9. By the end of this course, you willÂ have the skills needed for the official MicrosoftÂ ExcelÂ Expert exam. How would that look on your resume?

Reviews

"Well thought out and explained in a way which makes it easy to learn & understand." - Arnold Schwartz

"This course is very well structured and the teachers knowledge is very good. His teaching style is capturing and he is very structured. The small tests after each section is great, and even greater is that he takes time to show his thoughts on how is should have been done, not just leaving one to wonder if it was right or not. The resources used in this course is also very suitable. Highly recommend this one" - Daniel Sandberg

------------

Most people who use Excel are up to Level 3 in some aspects, and Level 2 in others. Why not go all the way to Level 9?

In this course, learn how to:

Manage workbook options and settings

Manage ExcelÂ workbooks,
Manage workbook review,
Apply workbook settings, properties and data options, and
Apply logical and statistical functions

Create advanced charts and ExcelÂ tables

Create and manage PivotTables,
Advanced formatting,
Create and manage PivotCharts,
Create advanced charts,
Apply advanced date and time functions

Preparing for advanced formulas

Troubleshoot ExcelÂ formulas,
Manage and reference defined names,
Prepare a workbook for internationalisation and accessibility,
Array fornulas.

Automation and advanced formulas

Create and modify custom workbook elements
Look up data by using functions (VLOOKUP, HLOOKUP, MATCH, INDEX, TRANSPOSE and INDIRECT). translated into 15 different languages.

This course teaches allÂ the skills that Microsoft want you to know for the Expert examsÂ 77-728Â (for Excel 2016 Expert), including those aspects which are PCÂ only (sadly), and will be useful if you wish to take the exam, or which to learn more about Microsoft Excel

The core skills which are taught are those tested by Microsoft in the exam. There are topicsÂ thatÂ it wants you to learn aboutÂ Microsoft Excel,Â and this course teaches you all of them.

Each module is taught in order, and is divided in sub-topics, and generally each sub-topic will have an individual lecture lasting 5-7 minutes.

The course will take about 8Â hours to complete, plus will you need additional time to test yourselves to ensure that you have learned the necessary skills.

You should take this course if:

you want to learn more about Microsoft Excel, or
you want to learn the Excel skills you need toÂ become a certified Microsoft Office Specialist Expert.

Regardless whether you have Excel 2016 or Excel 365 (or even Excel 2011), this course will help you get to an advancedÂ level, and maybe even want you to get more!
Who this course is for:
This is for you if you want to develop your Excel skills to an Expert level.
This is also for you if you want to take the Microsoft Office Specialist Expert Exams.
This may not be for you if you are not already at least at an Intermediate level (already done levels 1-5 or equivalent).

User Reviews

Rating

average 0

Total votes0

Focused display

Hi. I'm Phillip, and I'm proud to Teach with Udemy as a Udemy Instructor Partner. I have been teaching on Udemy since 2015, and I have over 500,000 students and more than 40 video courses, including over a dozen Bestseller courses.My courses include Power BI, Tableau, SQL Server, Power Platform (Power Apps, Power Automate, etc.), Azure, Microsoft Office, and more. Many of my courses follow requirements for official Microsoft certifications - so with a bit of practice, you could enter the exam and put it on your CV or resume.I have been a Computing Consultant providing expert services in the development of computer systems and data analysis. I am one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards. My interests are working with data, including Microsoft Power BI, Excel, Access, SQL Server, and the Power Platform.

I Do Data Limited

Instructor's Courses

I Do Data is dedicated to creating video courses on Udemy in a variety of computer-related topics, such as SQL in its different dialects and Microsoft Office.Are you wanting to learn a new skill from the beginning? Or are you wanting to build on an existing skill? In either case, welcome.Our courses are available on Udemy at any time, so you can watch them whenever you like, as often as you like.For many of our courses, if you master the skills taught, you are able (if you wish) to take (and hopefully pass) the official certification offered by Microsoft, Tableau or other companies. That would look good on your CV or resume? It could make the difference between getting a promotion, or a more rewarding job. I Do Data courses are dedicated to enabling you to succeed.

Udemy

View courses Udemy

Students take courses primarily to improve job-related skills.Some courses generate credit toward technical certification. Udemy has made a special effort to attract corporate trainers seeking to create coursework for employees of their company.