Combining Data From Two Tables (XLOOKUP)

11.1: Final Quiz Mandatory

12.1: Transition to Online Credibility & Power BI

Dhaval Patel

Data Entrepreneur | YouTuber | Ex - Bloomberg, Nvidia

I have 17 years of experience in Programming and Data Science working for big tech companies like NVIDIA and Bloomberg. I also run a famous YouTube channel called Codebasics where I pursue my passion for teaching. Codebasics is one of the top channels on YouTube when it comes to data science, machine learning, data structures, etc. I firmly believe that “Anyone can code” and I use analogies, simple explanations, and step-by-step storytelling to explain difficult concepts in such a way that even a high school student can understand them easily.

Hemanand Vadivel

Ex- Data Analytics Manager, 8+ Years in Europe, Microsoft Certified, Certified Supply Chain Professional

I’m a mechanical engineer who transitioned to a full time Data & Analytics manager in the UK & Germany by teaching myself Power BI, excel & anything else that was required to solve the problem. I have worked with complex data across Supply Chain, Sales, Marketing, Revenue Management, Finance and HR functions over the last 8 years to deliver effective solutions. To me, Analytics is an extra sensory organ that anyone can develop to become a superhuman at work. I have conducted several workshops and internal training to help non-technical people to become superhuman at work and now, really excited for this opportunity to share all that knowledge with you in this course.

Combining Data From Two Tables (XLOOKUP)

Lecture Content Locked

Enroll in Bootcamp to Unlock

Contents

1: Welcome to The Excel Experience

1.1: How Can Learning Excel Help You in Your Career?

1.2: Course Outline

2: Excel Basics: Getting Started

2.1: Peter Pandey’s Journey and Need to Learn Excel

2.2: Installing Excel: Windows / Mac

2.3: Getting Familiar With Excel

2.4: Getting More Familiar With Excel

2.5: Introduction to Formulas

2.6: Formula Behavior

2.7: Introduction to Tables

2.8: Introduction to Charts

2.9: Other Essential Features

2.10: Peter Gets His First Assignment From His Senior

2.11: Practice Exercise: Personal Expense Tracker

2.12: Quiz

2.13: Chapter Summary

3: Excel Basics: Cleaning & Combining Data

3.1: Intro to Movies Dataset

3.2: Cleaning Bad Data

3.3: Combining Data From Two Tables (VLOOKUP)

3.4: Combining Data From Two Tables (INDEXMATCH)

3.5: Combining Data From Two Tables (XLOOKUP)

3.6: Peter’s Request To Tony

3.7: Practice Exercise

3.8: Quiz

3.9: Chapter Summary

4: Excel Basics: Applying Business Maths & Statistics

4.1: Basic Maths to Business Metrics (Bollywood movies revenue avg)

4.2: Commonly Used Business Metrics (P&L)

4.3: Commonly Used Statistic Concepts (Mean, Median, Mode)

4.4: Commonly Used Statistic Concepts (Variance, Standard Deviation)

4.5: Commonly Used Statistic Concepts (Correlation)

4.6: Peter Gets to Solve a Real Time Problem

4.7: Practice Exercise

4.8: Quiz

4.9: Chapter Summary

5: Excel Basics+: Data Transformation Using Power Query

5.1: What is Power Query and Why it is the game changer?

5.2: Cleaning Data In Power Query

5.3: Merging Data in Power Query

5.4: Adding New Columns in Power Query

5.5: Loading Power Query Data to Excel

5.6: Peter Get’s His Hand Dirty

5.7: Practice Exercise

5.8: Quiz

5.9: Chapter Summary

6: Excel Basics+: Creating Business Reports Using Pivot Table & Power Pivot

6.1: Pivot Table Introduction: Apple Product Sales

6.2: Pivot Table For Movie Analytics

6.3: Creating a Report Using Pivot Table

6.4: Pivot Table Options & Formatting

6.5: Using Power Pivot & DAX For Powerful Business Reports

6.6: Adding Targets Using Data Modelling in Power Pivot

6.7: Breaking Down Complex Problems: Thinking Process of a Highly Paid Data Analyst

6.8: More Business Metrics and Conditional Formatting

6.9: VBA Basics & How Much VBA You Should Learn

6.10: Peter Practices On His Own

6.11: Practice Exercise

6.12: Quiz

6.13: I Need a Favour

6.14: Chapter Summary

7: Atliq Hardware & Problem Statement

7.1: Atliq’s Business Model

7.2: Usage of Excel & Business Requirement in Atliq

8: Excel Advanced: Sales Analytics

8.1: ETL (Extract, Transform and Load Data) in Excel I

8.2: ETL (Extract, Transform and Load Data) in Excel II

8.3: Business Report: Solution Design Thought Process

8.4: Creating Connections Among Tables Using Data Modelling

8.5: Adding a Date Table Using Power Query

8.6: Functional Knowledge: Sales

8.7: Sales Analytics: Creating Customer Performance Report

8.8: User Empathetic Report Design

8.9: Sales Analytics: Creating Market Performance vs Targets Reported

8.10: Practice Exercise

8.11: Quiz

8.12: Chapter Summary

9: Excel Advanced: Finance Analytics