Excel in Microsoft Excel 2: Intermediate to Expert advanced
4.5 (140 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
859 students enrolled

Excel in Microsoft Excel 2: Intermediate to Expert advanced

Get Expert certified! Levels 6-10 for Microsoft Excel 2010, 2013, 2016 and Excel 2019. Covers 77-728, 77-427 & 77-428.
Bestseller
4.5 (140 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
859 students enrolled
Created by Phillip Burton
Last updated 1/2020
English
English
Current price: $80.99 Original price: $124.99 Discount: 35% off
14 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 11 hours on-demand video
  • 18 articles
  • 35 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Manage workbooks and workbook review
  • Create and manage PivotTables
  • Present data visually, using PivotCharts and advanced charts
  • Perform data analysis and business intelligence
  • Troubleshoot formulas
  • Manage and reference defined names and custom workbook elements
  • And learn lots of formulas, including VLOOKUP, MATCH, INDEX, TRANSPOSE and INDIRECT
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

This course series has been recorded on Excel 2016 and Excel 2010, but the content also covers Excel 2013 and 2019.

Building on the skills learnt in "Excel in Microsoft Excel 1", here's levels 6-10. 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 10?

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

  • Perform data analysis and business intelligence in Excel.

  • Visual Basic for Applications (VBA) macros and form controls.

  • 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. Specifically, they are the skills required to undertake the Microsoft Certificate 77-851 (for Excel 2007 Expert), 77-888 (for Excel 2010 Expert), 77-427 and 77-428 (for Excel 2013 Expert) and 77-728 (for Excel 2016 Expert) 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 2007, Excel 2010, Excel 2013, Excel 2016 or Excel 365, 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).
Course content
Expand all 151 lectures 11:04:18
+ Introduction
4 lectures 14:44
Welcome to Udemy
00:35

Before we start, let's have a quick look around Udemy, and find out how you can get in touch with me.

Preview 02:00
+ Level 6, Section 1: Manage workbooks
9 lectures 38:20
Save a workbook as a template
04:55
Modifying custom templates
03:57
Copy styles from template to template
03:19
Using a formula to refer to another spreadsheet
05:52
Reference data in another workbook
03:15
Refresh and edit workbook links
07:15
Reference data by using structured references
05:08
Practice Activity Number 1
00:27
Answers to Practice Activity 1
04:12
+ Level 6, Section 2: Protect documents and Manage workbook review
12 lectures 50:57
Restrict editing by protecting a spreadsheet
04:28
Lock and unlock cells
06:07
Lock and unlock objects and hide formulas
03:02
Hiding Formulas
02:32
Protect Workbook
03:49
Sharing Workbooks
04:36
Track Changes
05:46
Encrypt a workbook with a password
04:14
Importing and exporting XML data
05:56
Practice Activity Number 2
00:23
Answers to Practice Activity 2
03:27
+ Level 6, Section 3: Using Logical and Statistical Functions
9 lectures 40:24
Perform logical operations by using the NOT function
03:53
Perform logical operations by using AND and OR
05:09
Perform logical operations by using nested functions
09:49
Perform statistical operations by using COUNTIFS
06:03
Using FREQUENCY
02:03
Trapping errors with IFERROR and ISERROR
02:43
Practice Activity Number 3
00:44
Answers to Practice Activity 3
04:52
+ Level 7, Section 1: Creating PivotTables
7 lectures 35:06
Creating our first PivotTable
07:12
PivotTable Options
07:59
Filtering Data, including using Slicers
05:01

group by dates, group by numbers, group by text

Group PivotTable data
04:57
Reference data by using GETPIVOTDATA, and add and utilise calculated fields
06:26
Practice Activity Number 4
00:17
Answers to Practice Activity 4
03:14
+ Level 7, Section 2: Advanced Formatting
8 lectures 53:43
Formatting number values
03:58
Custom Formatting - number formatting
11:45
Custom Formatting - dates and text
09:06
Custom Formatting - sections and colors
08:26
Create and manage custom conditional formatting rules
07:29
Create conditional formatting rules that use formulas
09:17
Practice Activity Number 5
00:22
Answers to Practice Activity 5
03:20
+ Level 7, Session 3: Creating PivotCharts and advanced charts
8 lectures 29:26
Create PivotCharts
02:34

including drill down into PivotChart details

Manipulate options in and apply styles to existing PivotCharts
02:54
Create dual-axis charts
05:37
Add trendlines to charts
06:36
Save a chart as a template and create custom chart templates
03:07
View chart animations
04:18
Practice Activity Number 6
00:24
Answers to Practice Activity 6
03:56
+ Level 7, Session 4: Apply advanced date and time and financial functions
6 lectures 22:22
Reference the date and time by using the NOW and TODAY functions
04:50
Extract dates and times
04:36
Serialise numbers by using date and time functions
05:15

PMT

Financial functions
05:58
Practice Activity Number 7
00:17
Answers to Practice Activity 7
01:26
+ Level 8, Session 1: Troubleshoot formulas
8 lectures 34:28
Trace precedence and dependence
05:42
Monitor cells and formulas by using the Watch Window
03:26
Checking for errors
04:37
Enable iterative calculations and setting iterative calculation options
04:17
Perform what-if analysis by using Goal Seek
06:51

We'll also look at a solution to a problem of a big workbook slowing the rest of the computer (other programs) down.

Enabling or disabling automatic workbook calculation
06:21
Practice Activity Number 8
00:22
Answers to Practice Activity 8
02:52
+ Level 8, Section 2: Prepare a workbook for internationalisation + accessibility
6 lectures 25:08
Display data in multiple international formats
11:34
Apply international currency formats
03:19
Manage multiple options for +Body and +Heading fonts
03:54
Modify worksheets for use with accessibility tools
04:43
Practice Activity Number 9
00:10
Answers to Practice Activity 9
01:28