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.

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 settingsManage Excel workbooks,Manage workbook review,Apply workbook settings, properties and data options, andApply logical and statistical functionsCreate advanced charts and Excel tablesCreate and manage PivotTables,Advanced formatting,Create and manage PivotCharts,Create advanced charts,Apply advanced date and time functionsPreparing for advanced formulasTroubleshoot Excel formulas,Manage and reference defined names,Prepare a workbook for internationalisation and accessibility,Array fornulas.Automation and advanced formulasPerform data analysis and business intelligence in Excel.Visual Basic for Applications (VBA) macros and form controls.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. 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 ExcelThe 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 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!

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?

Lecture 5 Curriculum and the Official Microsoft Certifications

Section 2: Level 6, Section 1: Manage workbooks

Lecture 6 Save a workbook as a template

Lecture 7 Modifying custom templates

Lecture 8 Copy styles from template to template

Lecture 9 Using a formula to refer to another spreadsheet

Lecture 10 Reference data in another workbook

Lecture 11 Refresh and edit workbook links

Lecture 12 Reference data by using structured references

Lecture 13 Practice Activity Number 1

Lecture 14 Answers to Practice Activity 1

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

Lecture 15 Restrict editing by protecting a spreadsheet

Lecture 16 Lock and unlock cells

Lecture 17 Lock and unlock objects and hide formulas

Lecture 18 Hiding Formulas

Lecture 19 Protect Workbook

Lecture 20 Sharing Workbooks

Lecture 21 Sharing Workbooks

Lecture 22 Track Changes

Lecture 23 Manage Workbook Versions

Lecture 24 Encrypt a workbook with a password

Lecture 25 Importing and exporting XML data

Lecture 26 Practice Activity Number 2

Lecture 27 Answers to Practice Activity 2

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

Lecture 28 Perform logical operations by using the NOT function

Lecture 29 Perform logical operations by using AND and OR

Lecture 30 Perform logical operations by using nested functions

Lecture 31 Perform statistical operations by using COUNTIFS

Lecture 32 Perform statistical operations by using SUMIFS and AVERAGEIFS

Lecture 33 Using FREQUENCY

Lecture 34 Trapping errors with IFERROR and ISERROR

Lecture 35 Practice Activity Number 3

Lecture 36 Answers to Practice Activity 3

Section 5: Level 7, Section 1: Creating PivotTables

Lecture 37 Creating our first PivotTable

Lecture 38 PivotTable Options

Lecture 39 Filtering Data, including using Slicers

Lecture 40 Group PivotTable data

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

Lecture 42 Practice Activity Number 4

Lecture 43 Answers to Practice Activity 4

Section 6: Level 7, Section 2: Advanced Formatting

Lecture 44 Formatting number values

Lecture 45 Custom Formatting - number formatting

Lecture 46 Custom Formatting - dates and text

Lecture 47 Custom Formatting - sections and colors

Lecture 48 Create and manage custom conditional formatting rules

Lecture 49 Create conditional formatting rules that use formulas

Lecture 50 Practice Activity Number 5

Lecture 51 Answers to Practice Activity 5

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

Lecture 52 Create PivotCharts

Lecture 53 Manipulate options in and apply styles to existing PivotCharts

Lecture 54 Create dual-axis charts

Lecture 55 Add trendlines to charts

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

Lecture 57 View chart animations

Lecture 58 Practice Activity Number 6

Lecture 59 Answers to Practice Activity 6

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

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

Lecture 61 Extract dates and times

Lecture 62 Serialise numbers by using date and time functions

Lecture 63 Financial functions

Lecture 64 Practice Activity Number 7

Lecture 65 Answers to Practice Activity 7

Section 9: Level 8, Session 1: Troubleshoot formulas

Lecture 66 Trace precedence and dependence

Lecture 67 Monitor cells and formulas by using the Watch Window

Lecture 68 Checking for errors

Lecture 69 Enable iterative calculations and setting iterative calculation options

Lecture 70 Perform what-if analysis by using Goal Seek

Lecture 71 Enabling or disabling automatic workbook calculation

Lecture 72 Practice Activity Number 8

Lecture 73 Answers to Practice Activity 8

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

Lecture 74 Display data in multiple international formats

Lecture 75 Apply international currency formats

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

Lecture 77 Modify worksheets for use with accessibility tools

Lecture 78 Practice Activity Number 9

Lecture 79 Answers to Practice Activity 9

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

Lecture 80 Name cells and reference a named formula

Lecture 81 Name data ranges and tables

Lecture 82 Modify named ranges by using Names Manager

Lecture 83 Navigate across worksheets by using named ranges

Lecture 84 Practice Activity Number 10

Lecture 85 Answers to Practice Activity 10

Section 12: Level 8, Section 4: Formula Arrays

Lecture 86 Using an array formula and SUMing the result

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

Lecture 88 Using Min and Max in Array Formulas

Lecture 89 Using the TRANSPOSE function

Lecture 90 Limitations of array formulas

Lecture 91 Bonus - Adding/deleting rows and array formulas

Lecture 92 Practice Activity Number 11

Lecture 93 Answers to Practice Activity 11

Section 13: Level 9, Section 1: Perform data analysis and business intelligence

Lecture 94 Choosing data sets from external data connections

Lecture 95 Manage external data connections

Lecture 96 Power Query - installing in Excel 2010 and 2013

Lecture 97 Importing Data, and Display and connect to data

Lecture 98 Managing Queries

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

Lecture 100 Combine (or Append) data sets

Lecture 101 Merge data sets

Lecture 102 Consolidate data

Lecture 103 Consolidating data using PivotTables

Lecture 104 Practice Activity Number 12

Lecture 105 Answers to Practice Activity 12

Section 14: Level 9, Section 2: The Data Model and PowerPivot

Lecture 106 Introduction to the Data Model

Lecture 107 Activating PowerPivot on Excel 2013 and 2016

Lecture 108 Activating PowerPivot on Excel 2010

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

Lecture 110 Manage relationships

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

Lecture 112 Power Pivot Advanced - Valerie's Question Part 1

Lecture 113 Power Pivot Advanced - Valerie's Question Part 2

Lecture 114 Practice Activity Number 13

Lecture 115 Answers to Practice Activity 13

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

Lecture 116 Populate cells by using advanced Fill Series options

Lecture 117 Create custom colour formats

Lecture 118 Create and modify cell styles

Lecture 119 Create and modify custom themes

Lecture 120 Practice Activity Number 14

Lecture 121 Answers to Practice Activity 14

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

Lecture 122 Look up data by using the VLOOKUP function

Lecture 123 Look up data by using the HLOOKUP function

Lecture 124 The difference between VLOOKUP and SUMIF or SUMIFS

Lecture 125 Using VLookup for appropriate matches

Lecture 126 Look up data by using the MATCH function

Lecture 127 Look up data by using the INDEX function

Lecture 128 Using the INDIRECT function

Lecture 129 Create a dynamic named range

Lecture 130 Use the Solver add-in

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

Lecture 132 Practice Activity Number 15

Lecture 133 Answers to Practice Activity 15

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

Lecture 134 Inserting form controls

Lecture 135 Insert Scroll bars and Setting form properties

Lecture 136 Insert List and Combo boxes

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

Lecture 138 Practice Activity Number 16

Lecture 139 Answers to Practice Activity 16

Section 18: Level 10, Session 2: Visual Basic for Applications (VBA)

Lecture 140 Record a macro and Running a macro

Lecture 141 Running a macro when a button is clicked

Lecture 142 Creating a custom macro button on the Quick Access Toolbar

Lecture 143 Opening the VBA Editor

Lecture 144 Running a macro when a workbook is opened

Lecture 145 Copy macros between workbooks

Lecture 146 Assign a shortcut key to an existing macro

Lecture 147 Creating forms and Modify tab order among workbook elements and objects

Lecture 148 Create a user-defined function (UDF)

Lecture 149 Practice Activity Number 17

Lecture 150 Answers to Practice Activity 17

Section 19: Conclusion

Lecture 151 Where next?

Lecture 152 Thank you very much

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