MS Excel 2019 Advanced VLOOKUP Formulas and Pivot Tables
What you'll learn
- Complete Vlookup & References Functions
- Vlookup Function 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
- Basic Knowledge of Excel
- Access to Microsoft Excel version 2010 or newer
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.
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).
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
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
Who this course is for:
- 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
- Excel spreadsheet creators, maintainers, or users.
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
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)