Microsoft Excel 365 Advanced

Focused View

Simon Sez IT

6:23:37

0 View

1 - Introduction

1 - Course Introduction.mp4

03:50

2 - WATCH ME Essential Information for a Successful Training Experience.mp4

02:11

3 - Course-Files.zip

4 - Exercise-Files.zip

5 - Exercise 01.mp4

01:02

2 - Dynamic Array Formulas

6 - What are Dynamic Array Functions.mp4

02:25

7 - Introduction to Spills and Arrays.mp4

06:09

8 - Extract and Count Unique Entries.mp4

03:41

9 - Unique vs Distinct.mp4

04:10

10 - Extract Multiple Unique Columns.mp4

02:22

11 - Extract Unique Values by Column.mp4

03:36

12 - The SORT Function.mp4

05:49

13 - The SORT Function Horizontal Sort.mp4

06:06

14 - The SORTBY Function.mp4

04:21

15 - The SORTBY Function Horizontal SORTBY.mp4

03:59

16 - The SEQUENCE Function.mp4

04:51

17 - The FILTER Function.mp4

06:05

18 - FILTER with Logic Operator OR.mp4

05:14

19 - FILTER with Logic Operator AND.mp4

03:59

20 - FILTER with Logic Operator Both or Neither Criteria.mp4

03:09

21 - FILTER with Logic Operator One or the Other but Not Both.mp4

03:25

22 - RANDARRAY and RANDBETWEEN.mp4

09:22

23 - Use XLOOKUP to Perform Complex Lookups.mp4

07:28

24 - The XMATCH Function.mp4

04:10

25 - Exercise 02.mp4

01:27

3 - Advanced Lookup and Reference Functions

26 - Lookups with Multiple Criteria.mp4

06:18

27 - Lookups with Duplicate Values.mp4

05:39

28 - Exercise 03.mp4

01:48

4 - Create Your Own Functions

29 - The LET Function.mp4

09:34

30 - Create Your Own Formulas with LAMBDA.mp4

13:15

31 - Exercise 04.mp4

02:00

5 - Power Pivot

32 - Create a Data Model in Power Pivot.mp4

06:37

33 - Create Relationships Between Tables.mp4

04:24

34 - Add Calculated Columns.mp4

05:58

35 - Build a PivotTable from Multiple Sources.mp4

03:52

36 - Exercise 05.mp4

01:14

6 - Working with Power Query

37 - Import Data Using Power Query.mp4

05:25

38 - Clean Data with Power Query.mp4

09:18

39 - Combine Files from a Folder.mp4

06:28

40 - Add More Files to the Data Model and Refresh.mp4

06:40

41 - Exercise 06.mp4

04:25

7 - Advanced PivotTables and Pivot Charts

42 - Create a Custom PivotTable Style.mp4

08:33

43 - Apply Custom Number Formatting.mp4

06:40

44 - Sorting Using a Custom Lists.mp4

03:36

45 - Create Report Filter Pages.mp4

02:03

46 - Adjust Slicer Settings.mp4

09:03

47 - Lock Workbooks but not Slicers.mp4

03:11

48 - Add a Calculated Field.mp4

04:01

49 - Create a Calculated Item.mp4

04:40

50 - Use GETPIVOTDATA with Data Validation.mp4

06:54

51 - Create Dynamic Pivot Chart Labels.mp4

05:22

52 - Exercise 07.mp4

01:59

8 - Form Controls and Checkboxes

53 - Introduction to Form Controls.mp4

06:02

54 - Combo Box Select from a List.mp4

05:43

55 - Check Box CheckUncheck Option.mp4

07:00

56 - NEW Checkboxes.mp4

07:32

57 - Option Button Choose an Option.mp4

05:02

58 - Spin Button Move Up and Down a List.mp4

05:28

59 - List Box Select from a List of Items.mp4

05:52

60 - Scroll Bar Scroll Through a List.mp4

07:21

61 - Exercise 08.mp4

01:51

9 - Macros and VBA

62 - Use Macros to Automate Tasks.mp4

06:39

63 - Record Save and Run Macros.mp4

10:00

64 - Record Macros with Relative Referencing.mp4

06:58

65 - MultiStep Macros.mp4

07:09

66 - The VBA Editor.mp4

07:55

67 - Assign Macros to Buttons.mp4

06:43

68 - Create a Custom Macro Ribbon.mp4

06:34

69 - Generate VBA Code with Copilot.mp4

05:02

70 - Exercise 09.mp4

01:25

10 - AI Tools Microsoft Copilot and ChatGPT

71 - Introduction to Copilot.mp4

03:18

72 - Generate Sample Excel Datasets with AI.mp4

06:03

73 - Ask Questions and Get Help.mp4

02:05

74 - Copilot for Excel 365 requires licence.mp4

08:18

75 - Exercise 10.mp4

02:01

11 - Course Close

76 - Course Close.mp4

03:48

Description

Unlock the power of advanced Excel 365 features and boost your data analysis and productivity skills.

What You'll Learn?

Extract and count unique entries using Dynamic Array Functions.
Differentiate between unique and distinct values in Excel spreadsheets.
Utilize SORT and SORTBY functions for data organization and analysis.
Employ FILTER functions with logical operators for precise data filtration.
Generate random arrays and values with RANDARRAY and RANDBETWEEN functions.
Perform complex lookups efficiently using XLOOKUP and XMATCH functions.
Develop custom formulas with LET and LAMBDA functions for tailored analysis.
Create and manage data models with Power Pivot, including table relationships.
Clean and manipulate data effectively using Power Query functionalities.
Automate repetitive tasks and customize Excel with macros and VBA.

Who is this for?

Users with intermediate knowledge of Excel and those coming from an older stand-alone version of Excel.

Finance professionals who wants to improve financial modeling and analysis capabilities.

Users who have Microsoft Excel foundation and seeking to advance their Excel knowledge.

What You Need to Know?

An active Microsoft 365 account to access Excel is beneficial to follow along.

An intermediate-level knowledge of Excel. This is an advanced course.

More details

Description
This course includes downloadable course instructor files and exercise files to work with and follow along.

Welcome to the Microsoft Excel 365 Advanced course. In this course, you will delve into advanced features of Excel 365 to enhance your data analysis and productivity skills.

You'll explore dynamic array formulas, unlocking the power of functions like SORT, FILTER, and XLOOKUP to manipulate data efficiently. Learn to perform complex lookups and reference functions, even with multiple criteria and duplicate values. Moreover, you'll discover how to create your own functions using LET and LAMBDA, empowering you to tailor formulas to your needs.

Master Power Pivot and Power Query to manage and analyze vast datasets effortlessly. Gain insights into advanced PivotTable and Pivot Chart techniques, including customization and dynamic labels, and harness the potential of form controls and checkboxes to create interactive Excel applications.

Then, delve into Excel macros and VBA to automate repetitive tasks and customize Excel functionalities. Finally, experience the future of Excel with AI tools like Microsoft Copilot and ChatGPT, enabling you to generate datasets, seek assistance, and streamline your workflow efficiently.

By the end of this course, you should have the proficiency to utilize the advanced features of Microsoft Excel 365, empowering you to tackle complex data analysis tasks with confidence. Join us in this comprehensive journey and unlock new productivity levels in your spreadsheet endeavors.

In the course, you will be able to:
Extract and count unique entries using Dynamic Array Functions.
Differentiate between unique and distinct values in Excel spreadsheets.
Utilize SORT and SORTBY functions for data organization and analysis.
Employ FILTER functions with logical operators for precise data filtration.
Generate random arrays and values with RANDARRAY and RANDBETWEEN functions.
Perform complex lookups efficiently using XLOOKUP and XMATCH functions.
Develop custom formulas with LET and LAMBDA functions for tailored analysis.
Create and manage data models with Power Pivot, including table relationships.
Clean and manipulate data effectively using Power Query functionalities.
Automate repetitive tasks and customize Excel with macros and VBA.

This course includes:
6+ hours of video tutorials
73 individual video lectures
Course and exercise files to follow along
Certificate of completion
Who this course is for:
Users with intermediate knowledge of Excel and those coming from an older stand-alone version of Excel.
Finance professionals who wants to improve financial modeling and analysis capabilities.
Users who have Microsoft Excel foundation and seeking to advance their Excel knowledge.

User Reviews

Rating

average 0

Total votes0

Focused display

Since 2008, individuals, small businesses, and Fortune 500 companies with thousands of employees have benefited from the easy and hands-on software training offered by Simon Sez IT. With over 8,000 video tutorials on a range of software programs, Simon Sez IT ensures stress-free eLearning and enhanced employee productivity - no matter whether you are implementing new software or a technological upgrade for your workplace. With over 630,000 Udemy students in over 180 countries, Simon Sez IT is the preferred online learning choice for individuals and businesses everywhere.

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.