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


Homepage

https://www.udemy.com/course/microsoft-excel-for-mac-2-intermediate-to-expert-advanced/