Menu
Menu

    LIVE Classes

    VLSI Design Programme
    VLSI Design Programme New

    IIT Delhi certified

    AI & Machine Learning
    AI & Machine Learning New

    Intel & IIT-M Pravartak certified

    Data Science
    Data Science

    IIT-M Pravartak certified

    Full Stack Development
    Full Stack Development

    IIT-M Pravartak certified

    UI/UX Design
    UI/UX Design

    HCL GUVI certified

    AI DevOps Program
    AI DevOps Program

    HCL GUVI certified

    Courses

    Practice

    CodeKata

    CodeKata

    Sharpen your coding skills, prepare for interviews

    WebKata

    WebKata

    Build basic frontend & backend development skills

    SQLKata

    SQLKata

    Master SQL concepts in relational database management

    FixTheCode

    FixTheCode

    A series of programs curated by industry experts

    IDE - Online Compiler

    IDE - Online Compiler

    Run & test your code in any programming language

    Resources

    Free Resources

    Free Resources

    Referral

    Referral

    Success Stories

    Success Stories

    Blogs

    Blogs

    Learn Hub

    Learn Hub

    Get The App

    Get The App

    Rewards

    Rewards

    Become an Affiliate

    Become an Affiliate

    Leaderboard

    Leaderboard

    What's New?
    Resume Builder

    Resume Builder

    Community Hub

    Community Hub

    Our Products

    HackerKID

    HackerKID

    Coding classes platform for K-12 children

    GUVI For Corporates

    GUVI For Corporates

    Meet your Hiring & Training Needs

    Placement Preparation

    Placement Preparation

    Ace Up your Aptitude

    Play StoreApp Store
Still Confused?WhatsApp

Welcome to HCL GUVI

Hey there! Welcome to HCL GUVI—Grab Your Vernacular Imprint—where tech learning is easy, fun, and curated specially for you. Incubated by IIT Madras & IIM Ahmedabad in 2014 and now part of HCL Group, we're making quality tech education accessible to all.

Join 3M+ learners breaking barriers and upskilling for a brighter future. We're here to guide you every step of the way! 🚀

LIVE Classes

Zen Classes are HCL GUVI's most refined and flagship product—live, expert-led tech programs for beginners and pros. With IITM Pravartak affiliations, master Full-Stack, Data Science, DevOps, UI/UX, and more in multiple languages!

Explore More

Courses

Looking for flexibility? HCL GUVI's 200+ self-paced courses let you learn anytime, anywhere! From free lessons to IIT-M & Autodesk-certified programs, gain in-demand skills in your preferred language.

Explore More

Practice Platforms

Enhance your coding skills with HCL GUVI's Practice Platforms—interactive, structured, and designed to help you master programming effortlessly.

CodeKata:

A structured coding practice platform with 1500+ coding problems designed by industry experts. Ideal for beginners and professionals preparing for tech interviews with real-world coding challenges.

Try Now >

WebKata:

An interactive platform to master HTML, CSS, JavaScript, and Bootstrap with a live coding environment. Perfect for hands-on web development practice without any setup.

Try Now >

SQLKata:

A practice ground for mastering SQL queries used in real-world applications. Write, optimize, and refine your queries to build strong database skills.

Try Now >

FixTheCode:

Hone your bug-fixing skills with real-world debugging challenges in Python, C++, JavaScript, and Golang. More languages coming soon!

Try Now >

IDE:

A free online compiler supporting 20+ programming languages with auto-complete, debugging, and AI-powered code generation—all in the cloud!

Try Now >

Leaderboard

Climb the leaderboard as you earn Geekoins by learning and practicing! The top scorers get featured, making learning competitive and rewarding. Keep going—you could be next!

Explore More

Rewards

Earn Geekoins by watching videos and practicing problems, then redeem them for exciting rewards. The more you engage, the more you win!

Explore More

Referral

Love learning with HCL GUVI? Share it with friends! Invite them using your unique link or code and unlock exciting rewards—Amazon vouchers, iPhones, and more. A Win-Win.

Explore More

Profile

Your HCL GUVI profile is your digital portfolio! Track progress, showcase skills, add projects, and build a resume. Keep it updated—opportunities await!

Explore More

That's It! You Are Ready!

You're all set to dive into your learning journey with HCL GUVI. Explore, upskill, and make each step count—exciting possibilities awaits!

Thank you for Reaching us out

Our team will reach you out
within the next 24 hours.

Explore all Programs

Lookups and Pivot Tables

Lesson 5: Lookups and Pivot Tables

Why connect and summarise data?

Tool

Purpose

Analogy

LookupBrings a value from a related tableLike a SQL join for one column
Pivot tableGroups and aggregates recordsLike SQL GROUP BY

Together, they turn detailed transactions into useful summaries without manual copying.

Lookup example

Orders table: OrderID, ProductID, Quantity Products table: ProductID, ProductName, Category

To bring Category into the orders table:

Function

Formula

Notes

XLOOKUP (Excel 365/2021+, Google Sheets)=XLOOKUP(B2, Products!A:A, Products!C:C, "Not found")Flexible, exact match by default, handles "not found"
VLOOKUP (all versions)=VLOOKUP(B2, Products!A:C, 3, FALSE)Key must be in the first column; use FALSE for exact match
INDEX + MATCH=INDEX(Products!C:C, MATCH(B2, Products!A:A, 0))Works in older versions; key can be in any column

The lookup only works if the identifier matches exactly, including data type and spaces.

Pivot table example

Pivot area

Field

Result

RowsRegionOne row per region
ColumnsMonthOne column per month
ValuesSum of RevenueTotal revenue per region per month
FiltersProduct CategoryFocus on one category at a time

Check: the Values area should use SUM for total revenue. Pivot tables sometimes default to COUNT when a column contains text or blanks.

Useful pivot features:

  • Show values as % of total – each region's share of revenue
  • Group dates – by month, quarter, or year
  • Slicers – clickable filters for reports

How it works

  • A lookup relies on a matching key.
  • A pivot table groups rows by selected fields and aggregates a measure.
  • Pivot tables don't update automatically. Refresh them after the source data changes.

Common mistakes

Mistake

Result

Fix

Duplicate keys in the lookup tableReturns only the first matchCheck uniqueness with COUNTIF
Approximate match in VLOOKUPWrong values returned silentlyUse FALSE or XLOOKUP
Key stored as text in one table, number in the other#N/A errorsMake data types consistent
Pivot not refreshedOutdated summaryRefresh after every data update
Source range too smallNew rows excludedUse a formal table as the source

Pivot tables can hide detail, so keep the source data and spot-check a few totals against the original records.

Share

You can share this topic with your family and friends on Social Media.

Share in

Data Analytics Tutorial

Data Analytics Tutorial

MODULE 1 : Foundations of Data Analytics
lessonIntroduction - Foundations of Data Analytics
lessonWhat is Data Analystics
lessonTypes of Data Analytics
lessonThe Data Analytics Workflow
lessonData Types, Sources, and Structure
lessonTurning a Business Request into an Analytical Question
MODULE 2 : Spreadsheets and Practical Data Handling
lessoniIntroduction - Spreadsheets and Practical Data Handling
lessonUnderstanding Spreadsheet Structure
lessonFormulas and Cell References
lessonEssential Functions for Analysis
lessonSorting, Filtering, and Data Validation
lessonLookups and Pivot Tables
MODULE 3 : SQL for Data Analytics
lessonSQL for Data Analytics - Introduction
lessonDatabases and Your First SQL Query
lessonFiltering, Sorting, and Limiting Results
lessonAggregate Functions and GROUP BY
lessonJoining Related Tables
lessonCASE, Subqueries, and CTEs
MODULE 4 : Python for Data Analytics
lessonPython for Data Analytics Introduction
lessonPython Basics for Analysts
lessonConditions, Loops, and Functions
lessonReading and Inspecting Data with pandas
lessonCleaning and Transforming Data
lessonGrouping, Summaries, and Visualisation
MODULE 5 : Statistics for Data Analytics
lessonStatistics for Data Analytics Introduction
lessonDescriptive Statistics
lessonDistributions, Percentiles, and Outliers
lessonSampling and Uncertainty
lessonHypothesis Testing and Confidence Intervals
lessonCorrelation, Regression, and Causation
MODULE 6 : Data Visualization Reporting and Dashboards
lessonData Visualisation, Reporting, and Dashboards Introduction
lessonChoosing the Right Chart
lessonVisual Design and Avoiding Misleading Charts
lessonKPIs and Dashboard Planning
lessonBuilding Reports in BI Tools
lessonPresenting an Insight and Recommendation
MODULE 7 : Exploratory Data Analysis and Data Cleaning
lessonExploratory Data Analysis and Data Cleaning - Introduction
lessonStarting an Exploratory Data Analysis
lessonMissing Values and Their Meaning
lesson Duplicates, Inconsistent Labels, and Data Types
lesson Outliers and Validation Rules
lessonA Reproducible Cleaning Pipeline
MODULE 8 : Business Product and Marketing Analytics
lessonBusiness, Product, and Marketing Analytics Introduction
lessonSales and Revenue Analytics
lessonCustomer Retention and Churn
lessonMarketing Funnel and Campaign Analytics
lessonProduct Analytics and User Journeys
lessonOperational Analytics and Root-Cause Investigation
MODULE 9 : Advanced Analytics - Forecasting and Machine Learning Basics
lessonAdvanced Analytics, Forecasting, and Machine Learning Basics - Introduction
lessonForecasting and Time-Series Basics
lessonRegression and Classification
lessonClustering and Segmentation
lessonModel Evaluation and Choosing Metrics
lessonOverfitting, Leakage, and Responsible Modelling
MODULE 10 : Data Workflows - Warehousing and Professional Practice
lessonData Workflows, Warehousing, and Professional Practice - Introduction
lessonOperational Databases and Data Warehouses
lessonETL, ELT, and Data Pipelines
lessonAutomation, Scheduling, and Monitoring
lessonDocumentation, Version Control, and Collaboration
lessonPrivacy, Governance, and Ethical Practice
MODULE 11 : Final Lesson: Bringing It All Together
lessonFinal Lesson: Bringing It All Together

Learn via Video Course

Data Analysis and Processing with Python using Pandas - Data Science Course Logo

Data Analysis and Processing with Python using Pandas - Data Science Course

5 hrs

Get in Touch For Details

Data Analytics Tutorial

MODULE 1 : Foundations of Data Analytics
lessonIntroduction - Foundations of Data Analytics
lessonWhat is Data Analystics
lessonTypes of Data Analytics
lessonThe Data Analytics Workflow
lessonData Types, Sources, and Structure
lessonTurning a Business Request into an Analytical Question
MODULE 2 : Spreadsheets and Practical Data Handling
lessoniIntroduction - Spreadsheets and Practical Data Handling
lessonUnderstanding Spreadsheet Structure
lessonFormulas and Cell References
lessonEssential Functions for Analysis
lessonSorting, Filtering, and Data Validation
lessonLookups and Pivot Tables
MODULE 3 : SQL for Data Analytics
lessonSQL for Data Analytics - Introduction
lessonDatabases and Your First SQL Query
lessonFiltering, Sorting, and Limiting Results
lessonAggregate Functions and GROUP BY
lessonJoining Related Tables
lessonCASE, Subqueries, and CTEs
MODULE 4 : Python for Data Analytics
lessonPython for Data Analytics Introduction
lessonPython Basics for Analysts
lessonConditions, Loops, and Functions
lessonReading and Inspecting Data with pandas
lessonCleaning and Transforming Data
lessonGrouping, Summaries, and Visualisation
MODULE 5 : Statistics for Data Analytics
lessonStatistics for Data Analytics Introduction
lessonDescriptive Statistics
lessonDistributions, Percentiles, and Outliers
lessonSampling and Uncertainty
lessonHypothesis Testing and Confidence Intervals
lessonCorrelation, Regression, and Causation
MODULE 6 : Data Visualization Reporting and Dashboards
lessonData Visualisation, Reporting, and Dashboards Introduction
lessonChoosing the Right Chart
lessonVisual Design and Avoiding Misleading Charts
lessonKPIs and Dashboard Planning
lessonBuilding Reports in BI Tools
lessonPresenting an Insight and Recommendation
MODULE 7 : Exploratory Data Analysis and Data Cleaning
lessonExploratory Data Analysis and Data Cleaning - Introduction
lessonStarting an Exploratory Data Analysis
lessonMissing Values and Their Meaning
lesson Duplicates, Inconsistent Labels, and Data Types
lesson Outliers and Validation Rules
lessonA Reproducible Cleaning Pipeline
MODULE 8 : Business Product and Marketing Analytics
lessonBusiness, Product, and Marketing Analytics Introduction
lessonSales and Revenue Analytics
lessonCustomer Retention and Churn
lessonMarketing Funnel and Campaign Analytics
lessonProduct Analytics and User Journeys
lessonOperational Analytics and Root-Cause Investigation
MODULE 9 : Advanced Analytics - Forecasting and Machine Learning Basics
lessonAdvanced Analytics, Forecasting, and Machine Learning Basics - Introduction
lessonForecasting and Time-Series Basics
lessonRegression and Classification
lessonClustering and Segmentation
lessonModel Evaluation and Choosing Metrics
lessonOverfitting, Leakage, and Responsible Modelling
MODULE 10 : Data Workflows - Warehousing and Professional Practice
lessonData Workflows, Warehousing, and Professional Practice - Introduction
lessonOperational Databases and Data Warehouses
lessonETL, ELT, and Data Pipelines
lessonAutomation, Scheduling, and Monitoring
lessonDocumentation, Version Control, and Collaboration
lessonPrivacy, Governance, and Ethical Practice
MODULE 11 : Final Lesson: Bringing It All Together
lessonFinal Lesson: Bringing It All Together

ZEN CLASS

LIVE CLASS

Popular Courses

Self-Paced Courses

Practice Platforms

Community

Products

Resources

Company

ZEN CLASS

LIVE CLASS

Popular Courses

Self-Paced Courses

Practice Platforms

Community

Products

Resources

Company

app store
play store
Refer & Earn

HCL GUVI (Grab Your Vernacular Imprint) Geek Network Private Limited is a leading online learning and skills development company, incubated by IIT Madras and IIM Ahmedabad.

Established in 2014 and acquired by the HCL Group in 2022, HCL GUVI is dedicated to providing effective and high-quality learning and skilling programs that transcend language barriers in technology education. HCL GUVI today is trusted by over 4.8 million users, 20 languages and 2500+ corporate partners.

app storeplay store
Refer & Earn

HCL GUVI (Grab Your Vernacular Imprint) Geek Network Private Limited is a leading online learning and skills development company, incubated by IIT Madras and IIM Ahmedabad.

Established in 2014 and acquired by the HCL Group in 2022, HCL GUVI is dedicated to providing effective and high-quality learning and skilling programs that transcend language barriers in technology education. HCL GUVI today is trusted by over 4.8 million users, 20 languages and 2500+ corporate partners.

app storeplay storeRefer & Earn
Terms and ConditionsPrivacy Policy

HCL GUVI Geek Network Pvt. Ltd.

Terms and ConditionsPrivacy Policy

HCL GUVI Geek Network Pvt. Ltd.