MS Excel 2019 Advanced VLOOKUP Formulas and Pivot Tables

Complete LOOKUP's and References and Pivot Tables with Charts in Microsoft Excel 2019 with Real time Scenarios.

Created by : Rated :4.6 68084 views

Last Updated: 2022-04-25 11:04:58 English Course Type: Self Learning

Complete Vlookup & References Functions
Vlookup Functions in Excel
Hlookup Function in Excel
Choose Function in Excel
Match Function in Excel
Complete Data Validation With Examples
Large Table Lookup in Excel
Nested Vlookup in Excel
Dynamic Vlookup in Excel
Index Function in Excel
Combination of Index Match in Excel
Offset Function in Excel
Dynamic Chart using OFFSET & MATCH in Excel
Indirect Function in Excel
Complete Pivot Tables in Excel
Writing Formulas in Pivot Tables
Pivot table with Multiple Sheets and Slicers & Timelines
Creating Dynamic Dashboards using Pivot Charts

Mastering the use of most popular LOOKUP'S and Pivot Tables will allow you to manipulate, extract and Analyze information like never before! The learners becomes experts after following this Video Course. This Complete course is About LOOKUP and References from Formulas and Complete Pivot Tables in Excel. Explained by Industry experts with all their experience here.

This course is recorded with the latest version of Excel 2019. All the lectures are explained with the examples and you can download all these excel files and practice the formulas.

This Course improves your Excel workflow faster with the true power of Excel lookup-and-reference functions.

Course Overview

Pivot Tables

90% of Excel users do not use Pivot Tables but those that do save hours of time and become valuable assets in their companies.You learn Excel Pivot Tables & Pivot Charts quickly and effectively, download the companion exercise files so you can follow along with the instructor by performing the same actions he is showing you on the videos.

By the end, you will be an Expert user of Pivot Tables, able to create reliable analyses which are able to be drilled-down quickly, and you'll be able to help others with their data analysis.

Look up Function

Excel makes it easy to search for values, both in rows and columns. While there are advanced functions also that enable Excel users to search for data "database-style", the HLookup and VLookup functions are their simpler-yet-powerful Functions.

VLOOKUP was launched in 1985. It has been with Excel from the beginning; it was included in Excel 1 for Macintosh released in 1985. For 34 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE).

Topics Covered

VLOOKUP to the Left
VLOOKUP to Replace Nested IF Formula
VLOOKUP Multiple Tables
VLOOKUP Multiple Criteria
VLOOKUP Different Sheets with Same Formula
VLOOKUP Auto Update Column Number
Use of wildcard in VLOOKUP
Nested VLOOKUP Trick
3D LOOKUP with VLOOKUP
2D LOOKUP with VLOOKUP
HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECT

Learning Outcomes

Be an Expert using Powerful Excel functions: VLOOKUP, HLOOKUP, INDEX, MATCH, TRANSPOSE, and INDIRECT
Consolidate data from multiple sheets with the INDIRECT function
Create automatically-expanding references with the OFFSET function
Create flexible lookups for Excel dashboards with drop-down lists
Create range-based lookups with the "approximate match"
Create two-dimensional lookups by combining VLOOKUP and MATCH
Find specific items using the "exact match"
Overcome the limitations of VLOOKUP by using INDEX and MATCH
Recognize the value of lookups and references to your specific workflow
Use named ranges and Excel tables as reference lists
Use TRANSPOSE function to flip your data 90 degrees with links to the original cells

Basic Knowledge of Excel

Data analysis and reporting professionals who work with Excel
Students who want to improve their skill set for MNC's
Excel knowledge workers looking to advance their skillset

Section 1 : Course Introduction 3 Lectures 00:04:22
Lecture 1 :
Introduction Preview
Lecture 2 :
Introduction
Lecture 3 :
Exercise Files
Section 2 : MS Excel Complete Vlookup Function with Different Scenarios 4 Lectures 00:28:03
Lecture 1 :
VLOOKUP Function in Excel
Lecture 2 :
Exact Match in Excel VLOOKUP Function
Lecture 3 :
Large Table Vlookup in Excel
Lecture 4 :
NESTED VLOOKUP IN EXCEL
Section 3 : Microsoft Excel Complete HLOOKUP Function 2 Lectures 00:15:22
Lecture 1 :
Complete HLOOKUP Function in Excel
Lecture 2 :
Dynamic HLOOKUP in Excel 2019
Section 4 : Microsoft Excel Choose Function 4 Lectures 00:19:29
Lecture 1 :
Choose Function in Excel
Lecture 2 :
Dynamic Choose Function in Excel
Lecture 3 :
Creating Interactive Chart with CHOOSE Function
Lecture 4 :
Excel CHOOSE Function in List Box
Section 5 : Microsoft Excel Match Function 2 Lectures 00:12:04
Lecture 1 :
MATCH Function in Excel
Lecture 2 :
MATCH Function using IFERROR in Excel
Section 6 : Microsoft Excel Data Validation 1 Lectures 00:12:03
Lecture 1 :
Data Validation in Excel
Section 7 : Microsoft Excel Index and Index-Match Functions 3 Lectures 00:20:02
Lecture 1 :
Index Function in Excel
Lecture 2 :
Combination of Index Match in Excel
Lecture 3 :
Reverse LOOKUP Using INDEX and MATCH Functions
Section 8 : Microsoft Excel Dynamic Vlookup (Match) Function 2 Lectures 00:14:26
Lecture 1 :
Dynamic Vlookup in Excel
Lecture 2 :
VLOOKUP with Non Printable Characters Data
Section 9 : Microsoft Excel 2019 Offset and Offset(MATCH) Chart 2 Lectures 00:24:31
Lecture 1 :
OFFSET Function in Excel
Lecture 2 :
OFFSET Chart (Match in Offset) in Excel
Section 10 : Microsoft Excel Indirect Function 2 Lectures 00:13:47
Lecture 1 :
INDIRECT Function in Excel
Lecture 2 :
Excel 20 Powerful Shortcut Keys
Section 11 : Microsoft Excel Vlookup with Multiple Columns & Two Columns 2 Lectures 00:12:57
Lecture 1 :
VLOOKUP With Multiple Columns in Excel
Lecture 2 :
Excel VLOOKUP - Two Column Lookup Value
Section 12 : VLOOKUP With Multiple Sheets AND Multiple Workbooks 2 Lectures 00:09:43
Lecture 1 :
VLOOKUP with Multiple Sheets in Excel
Lecture 2 :
VLOOKUP With Multiple Workbooks in Excel
Section 13 : Reverse LOOKUP & Compare Two Lists using VLOOKUP in Excel 2 Lectures 00:15:17
Lecture 1 :
Reverse LOOKUP using CHOOSE and MATCH in Excel
Lecture 2 :
How to Compare Two Lists using VLOOKUP in Excel
Section 14 : Microsoft Excel Analysis With Complete Pivot Tables 6 Lectures 00:56:09
Lecture 1 :
Why Excel Pivot Tables?
Lecture 2 :
Complete Pivot Tables in Excel
Lecture 3 :
Pivot Table Consolidation in Excel
Lecture 4 :
Applying Advanced Conditional Formatting to Pivot Tables
Lecture 5 :
How to Write Formulas in Pivot Table
Lecture 6 :
Slicers and Timeline in Excel Pivot Tables
Section 15 : Visualizing Data with Excel Pivot Charts 5 Lectures 00:32:47
Lecture 1 :
Introduction to Excel Pivot Charts
Lecture 2 :
Column Charts in Pivot Table
Lecture 3 :
Pie & Doughnut Charts in Pivot Table
Lecture 4 :
Clustered Bar Charts in Pivot Table
Lecture 5 :
Building Dynamic Dashboards With Pivot Tables & Charts
Section 16 : Data Visualization with Dashboards in Excel 3 Lectures 00:21:11
Lecture 1 :
Creating Custom Image Overlay Charts
Lecture 2 :
Using Binary Values to Highlight Time Periods
Lecture 3 :
Automating Charts with Name Ranges (OFFSET & COUNTA)

How do i access the course after purchase?
It's simple. When you sign up, you'll immediately have unlimited viewing of thousands of expert courses, paths to guide your learning, tools to measure your skills and hands-on resources like exercise files. There’s no limit on what you can learn and you can cancel at any time.
Are these video based online self-learning courses?
Yes. All of the courses comes with online video based lectures created by certified instructors. Instructors have crafted these courses with a blend of high quality interactive videos, lectures, quizzes & real world projects to give you an indepth knowledge about the topic.
Can i play & pause the course as per my convenience?
Yes absolutely & thats one of the advantage of self-paced courses. You can anytime pause or resume the course & come back & forth from one lecture to another lecture, play the videos mulitple times & so on.
How do i contact the instructor for any doubts or questions?
Most of these courses have general questions & answers already covered within the course lectures. However, if you need any further help from the instructor, you can use the inbuilt Chat with Instructor option to send a message to an instructor & they will reply you within 24 hours. You can ask as many questions as you want.
Do i need a pc to access the course or can i do it on mobile & tablet as well?
Brilliant question? Isn't it? You can access the courses on any device like PC, Mobile, Tablet & even on a smart tv. For mobile & a tablet you can download the Learnfly android or an iOS app. If mobile app is not available in your country, you can access the course directly by visting our website, its fully mobile friendly.
Do i get any certificate for the courses?
Yes. Once you complete any course on our platform along with provided assessments by the instructor, you will be eligble to get certificate of course completion.
For how long can i access my course on the platform?
You require an active subscription to access courses on our platform. If your subscription is active, you can access any course on our platform with no restrictions.
Is there any free trial?
Currently, we do not offer any free trial.
Can i cancel anytime?
Yes, you can cancel your subscription at any time. Your subscription will auto-renew until you cancel, but why would you want to?

SRINIVAS REDDY EXCELMAP,

111958 Course Views

2 Courses

Corporate Training Skills: Corporate Trainer for Many Start Ups and MSME Companies. Certified with Highest Qualified Marks in MOS Certifications - Microsoft Certified Trainer (MCT) - Microsoft Office Master 2019 - Microsoft Office Expert 2019 - Microsoft Office Specialist 2016 - Microsoft Office Expert 2016 - Microsoft Office Master 2016 - Microsoft Python Certified Working Experience:- 10+ Years of experience in Advanced Excel Automation ,VBA Developer and Excel Corporate Training â€¢ Microsoft Certified Professional in Excel â€¢ Hands on experience in Building Desktop Automation using VBA / Excel macros â€¢ Coding development in VBA (Excel) (Procedures, Functions & Classes) â€¢ Exposure to Pivot table & charts, VLOOKUP & HLOOKUP and Nested formulas â€¢ Developed user heavy screens with complex reporting requirements â€¢ Good knowledge in creating and maintaining add ins for Excel â€¢ Ability to create COM-addin and Automation add ins for Excel when required â€¢ Experience with SQL Server, Oracle, or other databases â€¢ Expert Knowledge of Excel Object Model â€¢ Working Knowledge of SQL (Procedures & Functions)