Microsoft Excel for Data & Business Analytics
- Descrição
- Currículo
- FAQ
- Revisões
Introduction:
Are you ready to take your Excel skills to the next level?
Do you want to pursue a career in Excel related work?
Are you finding that employers these days want ‘Advanced Excel Skills’?
Or maybe you’ve started a new job that requires more advanced Excel skills.
Do you struggle with Excel?
Not sure which formula to use for your situation?
Are you tired spending hours online trying to get “RIGHT” help?
When asked to make a dashboard or report, not sure where to start?
Module K – 15 Excel Functions Refresher, Module J – Exclusive Templates, Module I – Additional Learning 2 – Advanced Excel – More Scenario Examples, Module H – Additional Learning 1 – Dashboard Reporting in Excel with Tips, Module G – Modern Excel – DAX and Power Query, Module F – Pivot Table, Module E – Excel Charts and Visualizations, Module D – Data Analysis, Module C – Excel Formulas and Functions, Module B – Excel Essentials, Module A – Excel Basics (for Beginners)
Our Excel course is designed to take you from zero to hero so you can confidently list ‘Advanced Excel Skills’ on your resume/CV, or quickly get to grips with the requirements of a new job.
I have used the Excel 2021 / Office 365 to record these videos. Almost everything covered in this course will work for all the Excel versions (2010, 2013, 2016, 2019 & Office 365).
IS THIS ONLINE EXCEL COURSE FOR BEGINNERS ONLY?
This Complete Excel Course is for two types of people:
-
Excel beginners, i.e. anyone looking to learn Excel from scratch
-
Excel intermediate and Excel advanced users who want to make sure their skills cover all the essentials. This includes many Excel tricks and hidden features few data analysts know of.
The Excel Essentials for the Real World Course covers ALL the fundamentals an Excel beginner needs to know. BUT it also fills in the gaps for Excel Intermediate and Advanced users. It’s for corporate professionals who feel comfortable with Excel but not 100% confident.
This is more than an Excel Basics Course. It starts off easy and adds in tips and tricks many Excel advanced users don’t know of.
COURSE UPDATED to Include the BRAND NEW Excel Functions available in Microsoft 365 such as:
-
Excel’s new XLOOKUP function
-
New FILTER function in Excel
-
SORT, SORTBY & UNIQUE functions
PYTHON Module is added
-
12
11 - Using IF Formula, Nesting IF Formulas -
13
12 - Using IF with AND OR Formulas
-
14
13 - COUNTIFS, SUMIFS, AVERAGEIFS Formulas
-
15
14 - Excel TEXT Functions
-
16
15.1 - VLOOKUP Function, INDEX, MATCH Function
-
17
15.2 - INDEX and MATCH Function
-
18
15.3 - Match Type +1 OR -1 in Match Function in Microsoft Excel
-
19
16 - XLOOKUP, XMATCH Function
-
20
17 - Indirect Function
-
21
18 - Offset Function
-
22
19 - Formula Editing Tips & Tricks
-
23
20 - Date and Time Functions
-
24
21 - Maths Functions
-
25
22 - Dynamic Array Formulas
-
26
AI Converts Excel Examples into Formulas
AI Converts Excel Examples into Formulas
-
27
Return Multiple Values or Outputs via FILTER Function in Microsoft Excel 365
-
28
UNSTACK DATA EXAMPLE in Excel 365 | Medical Dataset, Logics Relevant for all
-
29
Comparison Between XLOOKUP and FILTER Function for wildcard contains substring
Comparison Between XLOOKUP and FILTER Function for wildcard contains substring search & Return Multiple Results in Excel 365 | Excel 365 vs. Google Sheets | #Excel #GoogleSheets #Analytics #Data #Formula
=TRANSPOSE(XLOOKUP("*"&G6&"*",Data[Motivational Books Title],Data,,2))
=TRANSPOSE(FILTER(Data,ISNUMBER(SEARCH("*"&G14&"*",Data[Motivational Books Title]))))
Example 2:
=TRANSPOSE(XLOOKUP(I4,BankDB[Region],CHOOSECOLS(BankDB,1,2,4)))
=TRANSPOSE(FILTER(CHOOSECOLS(BankDB,1,2,4),I12=BankDB[Region]))
-
30
Fuzzy Lookup in Microsoft Excel / POWER BI (2 Methods: Addin or Power Query)
Fuzzy Lookup in Microsoft Excel / POWER BI (2 Methods: Addin or Power Query) | The Fuzzy Lookup Add-In for Excel performs fuzzy matching of textual data in Excel.
VLOOKUP, INDEX+MATCH, XLOOKUP & FILTER Function does not return the results from Approximate Match of Text Values.
-
31
Two Way XLOOKUP with Multiple Conditions in Excel 365 | Human Resource (HR)
Two Way XLOOKUP with Multiple Conditions in Excel 365 | Human Resource (HR) Example | Group Health Insurance Proposal - Calculation of Premium
-
32
Connect Slicers with GROUPBY Function in Microsoft Excel 365
Connect Slicers with GROUPBY or PIVOTBY Function in Microsoft #Excel 365 | Useful Tips for Dynamic Modern Excel Dashboards | GROUPBY, LAMBDA, SUBTOTAL, UNIQUE, FILTER, SLICERS, VISUAL | #DashboardReporting #Analytics
=GROUPBY(Data[Salesperson],Data[Revenue],SUM,0,0,,BYROW(Data[Category],LAMBDA(r,SUBTOTAL(3,r))))
Dynamic Chart Title: ="Revenue based on Category: "&TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Data[Category],SUBTOTAL(103,OFFSET(Data[Category],ROW(Data[Category])-MIN(ROW(Data[Category])),,1)))))
-
33
WildCard CF "Highlight Total Row" | Quick SUM for Multiple Ranges in a Column
WildCard CF "Highlight Total Row" | Quick SUM for Multiple Ranges in a Column
-
34
23 - Conditional Formatting
-
35
24.1 - WHAT IF Analysis - Data Table
-
36
24.2 - WHAT IF Analysis - Goal Seek
-
37
24.3 - WHAT IF Analysis - Scenario Manager
-
38
25 - Linear Regression
-
39
26 - Data Analysis Feature
-
40
27 - Data Storytelling Tips
-
41
Add Symbols or Arrows in Custom Formatting in Excel
Add Symbols or Arrows in Custom Formatting in Excel
-
42
Timestamp Cells HACK! | Create Timestamp in Microsoft Excel
Timestamp Cells HACK! | Create Timestamp in Microsoft Excel
-
43
How to Find High-Value Customers in Microsoft Excel
How to Find High-Value Customers in Microsoft #Excel ? Dynamic Array Formulas or Pivot Table
=GROUPBY(data[Customer ID]:data[Customer Name],data[Order Amount],SUM,,0)
=SORT(FILTER(C7#,E7:E801>80000),3,-1)
-
44
Learn Advanced Descriptive Analytics in Excel
Learn Advanced Descriptive Analytics in Excel | Examples of Human Resources Management Dataset | 6 Questions
1. 10 Eldest Members (Old Admissions)
2. 10 Youngest Members (New Admissions)
3. 10 Eldest Members (Date of Birth)
4. 10 Youngest Members (Date of Birth)
5. Birthday in Current Month
6. Anniversary in Current month
-
45
Advanced Analytics - Dynamic Array Formulas in Microsoft Excel 365
Q1. Filter all Employees who are Reporting to Manager Rahim?
=VSTACK(HR_Data[#Headers],FILTER(HR_Data,HR_Data[Manager]=I4))
Q2. Filter all Employees who are Reporting to Manager Rahim having Salary >= 80,000 & Male?
=VSTACK(HR_Data[#Headers],FILTER(HR_Data,(HR_Data[Manager]=I49)*(HR_Data[Salary]>=80000)*(HR_Data[Gender]="Male")))
Q.3 Salaries of People Reporting to Rahim ? Descriptive Analytics
=FILTER(HR_Data[Salary],HR_Data[Manager]=B74)
Q4. Filter all Employees who are Reporting to Manager Rahim (ID, Names, Gender, Age, Salary)?
=VSTACK(CHOOSECOLS(HR_Data[#Headers],{1,2,4,5,7}),FILTER(CHOOSECOLS(HR_Data,{1,2,4,5,7}),HR_Data[Manager]="Rahim"))
Q.5 Summarize Department wise based on Age Filter?
=SORT(UNIQUE(FILTER(HR_Data[Department],HR_Data[Age]>=C163)))
=COUNTIFS(HR_Data[Department],E166#,HR_Data[Age],">="&C163)
=SORT(E166#:F166#,2,-1)
Q.6 Find Names and No of Employees Reporting to Manager?
=TEXTJOIN(", ",TRUE,FILTER(HR_Data[Full Name],HR_Data[Manager]=B188))
=COUNTA(--TEXTSPLIT(C188,", "))
-
46
Random Data Generator with PYTHON in Microsoft Excel 365
Random Data Generator with PYTHON in Microsoft Excel 365
-
47
How to do Reverse Icon Sets in Conditional Formatting in Excel ?
-
48
Courier Delivery Process Flow / Tracker | HR Hiring an Employee Tracker in Excel
Courier Delivery Process Flow / Tracker | HR Hiring an Employee Tracker in Excel
-
49
28 & 29 - Introduction to Excel Charts | Dynamic Advanced Charts
-
50
Concept of Actionable Reporting
-
51
Concept of Information Design with Examples
Information design is the practice of presenting information in a way that's easy to understand and use. It's like translating complex data into a language everyone can comprehend. By using visual elements, typography, and layout, information designers create clear, concise, and engaging content.
Key goals of information design:
Clarity: Ensuring information is easily understood.
Efficiency: Making information accessible and usable quickly.
Effectiveness: Achieving the desired outcome or goal.
-
54
32 - Top 10 Power Query Tips
-
55
33 - Introduction to Power Pivot
-
56
34 - Apply DAX in Power Pivot for Analysis
-
57
32.1 - How to Split Names and Emails from a List in Microsoft Excel FORMULA
-
58
32.2 - Extract Dates from Sentences Located anywhere in a Cell in Excel
-
59
32.3 - How to convert Stack Data into Unstack different approaches
32.3 - How to convert Stack Data into Unstack different approaches Unstack Uneven Data Excel Power Query
-
60
32.4 - Transform Data into Tabular Format - FORMULA or POWER QUERY You Decide
-
61
32.5 - An Introduction on 6 types of Joins in Power Query
-
62
32.6 - Left Outer Join & Right Outer Join in POWER QUERY
-
63
32.7 - Full Outer Join & Inner Join in POWER QUERY
-
64
32.8 - Left Anti Join & Right Anti Join in POWER QUERY
-
65
One Slicer for Two Different Datasets in Excel | Relationships | POWER PIVOT
One Slicer for Two Different Datasets in Excel | Relationships | POWER PIVOT
-
66
Working with very LARGE Datasets | 4+ Million Rows | Power Query and Power Pivot
Working with very LARGE Datasets | 4+ Million Rows | Power Query and Power Pivot | Big Data in Excel
-
67
How to Extract Dates From Text Strings in Power Query
How to Extract Dates From Text Strings in Power Query
-
68
How to Convert Multiple Headers Cross Tab Report into Tabular Data (Unpivot)
How to Convert Multiple Headers Cross Tab Report into Tabular Data (Unpivot)
-
69
How to Calculate Hours Worked for Employees on Complex Data & to Summarize
How to Calculate Hours Worked for Employees on Complex Data & to Summarize in Power Query or Dynamic Array Formula (GROUPBY & SORT) in Microsoft Excel
-
70
Import Data "Market Summary" from KSE STOCKS Website & Automate in Excel
Import Data "Market Summary" from KSE STOCKS Website & Automate in Excel
-
71
Web Scraping "Add Column from Examples" Power Query in Excel
Web Scraping "Add Column from Examples" Power Query in Excel
-
72
Normalize Data using Power Query
Normalize Data using Power Query
-
73
Combine Multiple Tables and Perform Aggregation Sum | POWER QUERY Solution
Combine Multiple Tables and Perform Aggregation Sum | POWER QUERY Solution | Data Transformation
-
74
Migrate Excel Data Model and DAX to POWER BI and Combine All Measures
Migrate Excel Data Model and DAX to POWER BI and Combine All Measures
-
75
How to Quickly Add Calendar Date Dimension Table in Microsoft POWER PIVOT
How to Quickly Add Calendar Date Dimension Table in Microsoft POWER PIVOT in Excel?
-
76
Fuzzy Lookup (POWER QUERY) Approximate Match for Text in Microsoft Excel
Fuzzy Lookup (POWER QUERY) Approximate Match for Text in Microsoft Excel
