This plan includes
- Limited free courses access
- Play & Pause Course Videos
- Video Recorded Lectures
- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Email & Chat Support
What you'll learn?
- 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
Course Overview
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
Pre-requisites
- Basic Knowledge of Excel
Target Audience
- 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
Curriculum 45 Lectures 05:12:13
Section 1 : Course Introduction
Section 2 : MS Excel Complete Vlookup Function with Different Scenarios
- 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
- Lecture 1 :
- Complete HLOOKUP Function in Excel
- Lecture 2 :
- Dynamic HLOOKUP in Excel 2019
Section 4 : Microsoft Excel Choose Function
- 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
- Lecture 1 :
- MATCH Function in Excel
- Lecture 2 :
- MATCH Function using IFERROR in Excel
Section 6 : Microsoft Excel Data Validation
- Lecture 1 :
- Data Validation in Excel
Section 7 : Microsoft Excel Index and Index-Match Functions
- 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
- Lecture 1 :
- Dynamic Vlookup in Excel
- Lecture 2 :
- VLOOKUP with Non Printable Characters Data
Section 9 : Microsoft Excel 2019 Offset and Offset(MATCH) Chart
- Lecture 1 :
- OFFSET Function in Excel
- Lecture 2 :
- OFFSET Chart (Match in Offset) in Excel
Section 10 : Microsoft Excel Indirect Function
- Lecture 1 :
- INDIRECT Function in Excel
- Lecture 2 :
- Excel 20 Powerful Shortcut Keys
Section 11 : Microsoft Excel Vlookup with Multiple Columns & Two Columns
- 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
- 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
- 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
- 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
- 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
- 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)
Our learners work at
Frequently Asked Questions
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?
Instructor
111954 Course Views
2 Courses