Microsoft Excel For Mac 2: Intermediate To Expert Advanced

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

Requirements

Before beginning, you need to use Microsoft Excel to an intermediate standard.

This includes Conditional Formatting, Tables, filtering, sorting and creating charts

If you have taken the course "Excel in Microsoft Excel - Beginner to Specialist certificate", or taken a Microsoft Office Specialist exam in Excel, then you will have the relevant prerequisites.

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, andApply 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 elementsLook 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, oryou 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!

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Welcome to Udemy

Lecture 3 Introduction to Udemy

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

Section 2: Level 6, Section 1: Manage workbooks

Lecture 5 Save a workbook as a template

Lecture 6 Modifying custom templates

Lecture 7 Copy styles from template to template

Lecture 8 Using a formula to refer to another spreadsheet

Lecture 9 Reference data in another workbook

Lecture 10 Refresh and edit workbook links

Lecture 11 Reference data by using structured references

Lecture 12 Practice Activity Number 1

Lecture 13 Answers to Practice Activity 1

Section 3: Level 6, Section 2: Protect documents and Manage workbook review

Lecture 14 Restrict editing by protecting a spreadsheet

Lecture 15 Lock and unlock cells

Lecture 16 Lock and unlock objects and hide formulas

Lecture 17 Hiding Formulas

Lecture 18 Protect Workbook

Lecture 19 Sharing Workbooks

Lecture 20 Track Changes

Lecture 21 Encrypt a workbook with a password

Lecture 22 Practice Activity Number 2

Lecture 23 Answers to Practice Activity 2

Section 4: Level 6, Section 3: Using Logical and Statistical Functions

Lecture 24 Perform logical operations by using the NOT function

Lecture 25 Perform logical operations by using AND and OR

Lecture 26 Perform logical operations by using nested functions

Lecture 27 Perform statistical operations by using COUNTIFS

Lecture 28 Perform statistical operations by using SUMIFS and AVERAGEIFS

Lecture 29 Using FREQUENCY

Lecture 30 Trapping errors with IFERROR and ISERROR

Lecture 31 Practice Activity Number 3

Lecture 32 Answers to Practice Activity 3

Section 5: Level 7, Section 1: Creating PivotTables

Lecture 33 Creating our first PivotTable

Lecture 34 PivotTable Options

Lecture 35 Filtering Data, including using Slicers

Lecture 36 Group PivotTable data

Lecture 37 Reference data by using GETPIVOTDATA, and add and utilise calculated fields

Lecture 38 Practice Activity Number 4

Lecture 39 Answers to Practice Activity 4

Section 6: Level 7, Section 2: Advanced Formatting

Lecture 40 Formatting number values

Lecture 41 Custom formatting in graphs and fractions

Lecture 42 Custom Formatting - number formatting

Lecture 43 Custom Formatting - dates and text

Lecture 44 Custom Formatting - sections and colors

Lecture 45 Create and manage custom conditional formatting rules

Lecture 46 Create conditional formatting rules that use formulas

Lecture 47 Practice Activity Number 5

Lecture 48 Answers to Practice Activity 5

Section 7: Level 7, Session 3: Creating PivotCharts and advanced charts

Lecture 49 Create PivotCharts

Lecture 50 Create dual-axis charts

Lecture 51 Add trendlines to charts

Lecture 52 Save a chart as a template and create custom chart templates

Lecture 53 View chart animations

Lecture 54 Practice Activity Number 6

Lecture 55 Answers to Practice Activity 6

Section 8: Level 7, Session 4: Apply advanced date and time and financial functions

Lecture 56 Reference the date and time by using the NOW and TODAY functions

Lecture 57 Extract dates and times

Lecture 58 Serialise numbers by using date and time functions

Lecture 59 Financial functions

Lecture 60 Practice Activity Number 7

Lecture 61 Answers to Practice Activity 7

Section 9: Level 8, Session 1: Troubleshoot formulas

Lecture 62 Trace precedence and dependence

Lecture 63 Checking for errors

Lecture 64 Enable iterative calculations and setting iterative calculation options

Lecture 65 Perform what-if analysis by using Goal Seek

Lecture 66 Enabling or disabling automatic workbook calculation

Lecture 67 Practice Activity Number 8

Lecture 68 Answers to Practice Activity 8

Section 10: Level 8, Section 2: Prepare a workbook for internationalisation + accessibility

Lecture 69 Display data in multiple international formats

Lecture 70 Apply international currency formats

Lecture 71 Manage multiple options for +Body and +Heading fonts

Lecture 72 Modify worksheets for use with accessibility tools

Lecture 73 Practice Activity Number 9

Lecture 74 Answers to Practice Activity 9

Section 11: Level 8, Section 3: Manage and reference defined names

Lecture 75 Name cells and reference a named formula

Lecture 76 Name data ranges and tables

Lecture 77 Modify named ranges by using Names Manager

Lecture 78 Navigate across worksheets by using named ranges

Lecture 79 Practice Activity Number 10

Lecture 80 Answers to Practice Activity 10

Section 12: Level 8, Section 4: Formula Arrays

Lecture 81 Using an array formula and SUMing the result

Lecture 82 Using an array formula and outputting the result to a range

Lecture 83 Using MINFS and MAXIFS, and equivalent Array Formulas

Lecture 84 Using the TRANSPOSE function

Lecture 85 Limitations of array formulas

Lecture 86 Bonus - Adding/deleting rows and array formulas

Lecture 87 Practice Activity Number 11

Lecture 88 Answers to Practice Activity 11

Section 13: Level 9, Session 3: Create and modify custom workbook elements

Lecture 89 Populate cells by using advanced Fill Series options

Lecture 90 Create custom colour formats

Lecture 91 Create and modify cell styles

Lecture 92 Create and modify custom themes

Lecture 93 Practice Activity Number 14

Lecture 94 Answers to Practice Activity 14

Section 14: Level 9, Session 4: Create advanced formulas

Lecture 95 Look up data by using the VLOOKUP function

Lecture 96 Look up data by using the HLOOKUP function

Lecture 97 The difference between VLOOKUP and SUMIF or SUMIFS

Lecture 98 Using VLookup for appropriate matches

Lecture 99 Look up data by using the MATCH function

Lecture 100 Look up data by using the INDEX function

Lecture 101 Using the INDIRECT function

Lecture 102 Create a dynamic named range

Lecture 103 Use the Solver add-in

Lecture 104 Perform what-if analysis by using Scenario Manager (create + compare scenarios)

Lecture 105 Practice Activity Number 15

Lecture 106 Answers to Practice Activity 15

Section 15: Level 10, Section 1: Link form controls to cells

Lecture 107 Inserting form controls

Lecture 108 Insert Scroll bars and Setting form properties

Lecture 109 Insert List and Combo boxes

Lecture 110 Insert check boxes, Insert labels and other form controls

Lecture 111 Practice Activity Number 16

Section 16: Conclusion

Lecture 112 Thank you very much

Section 17: Level 9, Section 1 (PC only): Perform data analysis and business intelligence

Lecture 113 Welcome to Level 9 Sections 1 and 2

Lecture 114 Choosing data sets from external data connections

Lecture 115 Manage external data connections

Lecture 116 Power Query - installing in Excel 2010 and 2013

Lecture 117 Importing Data, and Display and connect to data

Lecture 118 Managing Queries

Lecture 119 Transform, and re-displaying (re-loading) data

Lecture 120 Combine (or Append) data sets

Lecture 121 Merge data sets

Lecture 122 Consolidate data

Lecture 123 Consolidating data using PivotTables

Lecture 124 Practice Activity Number 12

Lecture 125 Answers to Practice Activity 12

Section 18: Level 9, Section 2 (PC only): The Data Model and PowerPivot

Lecture 126 Introduction to the Data Model

Lecture 127 Activating PowerPivot on Excel 2013 and 2016

Lecture 128 Activating PowerPivot on Excel 2010

Lecture 129 Utilise PowerPivot and create a PivotTable based on the Excel data model

Lecture 130 Manage relationships

Lecture 131 Use cube functions to get data out of the Excel data model

Lecture 132 Practice Activity Number 13

Lecture 133 Answers to Practice Activity 13

Section 19: Bonus Section

Lecture 134 Bonus Lecture

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).