OpenCourser
Learn skills that shape your future
Visit OpenCourser.com
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?
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 (
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
AI Converts Excel Examples into Formulas
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]))
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.
Two Way XLOOKUP with Multiple Conditions in Excel 365 | Human Resource (HR) Example | Group Health Insurance Proposal - Calculation of Premium
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)))))
WildCard CF "Highlight Total Row" | Quick SUM for Multiple Ranges in a Column
Add Symbols or Arrows in Custom Formatting in Excel
Timestamp Cells HACK! | Create Timestamp 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)
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
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,", "))
Random Data Generator with PYTHON in Microsoft Excel 365
Courier Delivery Process Flow / Tracker | HR Hiring an Employee Tracker in Excel
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.
32.3 - How to convert Stack Data into Unstack different approaches Unstack Uneven Data Excel Power Query
One Slicer for Two Different Datasets in Excel | Relationships | POWER PIVOT
Working with very LARGE Datasets | 4+ Million Rows | Power Query and Power Pivot | Big Data in Excel
How to Extract Dates From Text Strings in Power Query
How to Convert Multiple Headers Cross Tab Report into Tabular Data (Unpivot)
How to Calculate Hours Worked for Employees on Complex Data & to Summarize in Power Query or Dynamic Array Formula (GROUPBY & SORT) in Microsoft Excel
Import Data "Market Summary" from KSE STOCKS Website & Automate in Excel
Web Scraping "Add Column from Examples" Power Query in Excel
Normalize Data using Power Query
Combine Multiple Tables and Perform Aggregation Sum | POWER QUERY Solution | Data Transformation
Migrate Excel Data Model and DAX to POWER BI and Combine All Measures
How to Quickly Add Calendar Date Dimension Table in Microsoft POWER PIVOT in Excel?
Fuzzy Lookup (POWER QUERY) Approximate Match for Text in Microsoft Excel
Sales Analytical Dashboard using Data Analysis Expressions (DAX) & Visualizations + Tips in Excel
SaveOpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.
Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.
Find this site helpful? Tell a friend about us.
We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.
Your purchases help us maintain our catalog and keep our servers humming without ads.
Thank you for supporting OpenCourser.
