Don't use VLOOKUP. Use Merge Table or Data Model. Power Query and Excel.

Рет қаралды 3,972

Күн бұрын

Learn how to eliminate misuse of VLOOKUP.
Excessive use of VLOOKUP slows down your work, increases file size or forces you to paste special values repeatedly.
There are two great alternatives.
1. Power Query Merge - does exactly what VLOOKUP does but much better and faster
2. Use Data model and relationships.
Download sample files
efficiency365....
Contents
0:00 Intro
2:11 Power Query Merge solution
9:00 Data Model solution
Buy Dr Nitin's latest book
-----------------------------------------
Efficiency Best Practices for Microsoft 365
130 best practices everyone must know
Work Smarter, Grow Faster
s.efficiency36...
Dr Nitin Paranjape is the pioneer in maximizing work efficiency in a modern work scenario.
Dr Nitin's Office Masterclass
----------------------------------------------
hi.switchy.io/mms
Online Courses by Dr Nitin Paranjape
--------------------------------------------------------------
Pivot Table Advanced - s.efficiency36...
Excel to Power BI - swiy.io/uxlpbi
Learn more from 1000+ blog articles by Dr Nitin Paranjape
Efficiency 365 blog: efficiency365....
LinkedIn : / nitinparanjape
Twitter : / drnitinp
Production
--------------------
Music and Effects: elements.envat...
Created using Adobe Premiere: www.adobe.com/...
AI based captions: videoindexer.ai (Microsoft service)

Пікірлер: 28

@JohnYoga 5 ай бұрын

Absolutely excellent. I have already subscribed and liked. This is one of the biggest needs in Excel, in my view: We are often stuck with disparate data from different locations that we must combine via "connectors" between the tables. THEN, we can attack one data table and do analytics. Of course, you were trying to make this presentation efficient, but often the data needs to be cleaned up a bit before any merging can occur. I will see if you have a video on how to clean up all sorts of formatting/data problems. Just great.

@efficiency365 5 ай бұрын

Thanks @JohnYoga Here is how you do data clean up - 11 rules for clean data - input data - for Excel , Power BI or any other analytical tool - kzfaq.info/get/bejne/fdtxhbJ3yLHVfWQ.html Data Cleanup - Cross-tab and Multiple headers - Rules 1, 5, 6, 10 - kzfaq.info/get/bejne/qL5oZqWhnNPSlHk.html Cleaning hierarchical data - Rules 6 and 8 - kzfaq.info/get/bejne/np-ehtKA0ZfOmY0.html Crosstab cleanup kzfaq.info/get/bejne/o7qkgteLrZPSfoE.htmlsi=DTvp68hJ5qJuxwCx Multiple header crosstab data clean up - Excel - Power Query - kzfaq.info/get/bejne/hcimi8imucvSXaM.html Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@meenasudhanshu 3 ай бұрын

Very informative and precise to the point.

@efficiency365 2 ай бұрын

Thanks @meenasudhanshu Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@syedaneesdurez8766 9 ай бұрын

Sir Explained in very gud manner and easy to understand. Looking forward for more tips on excel to make work more easier and work efficiently.

@efficiency365 9 ай бұрын

Thanks @syedaneesdurez8766 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@Sumanth1601 9 ай бұрын

Excellent content 👌 especially for beginner's..most of these concept are based on PBI, but Excel has this functionality too. Kudos ❤

@efficiency365 9 ай бұрын

Thanks @Sumanth1601 Yes. PBI and Excel should be taught, learnt and used together. They are adjuncts. Not separate compartments. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@mcegirl4 9 ай бұрын

Thank you for your fabulous instruction on these concepts - tremendous help!

@efficiency365 9 ай бұрын

Thanks @mcegirl4 Check out my other best practices videos as well. Efficient Tasks Management - kzfaq.info/get/bejne/rLJ2o7qHy7vKgWw.html Task Apps Comparison: kzfaq.info/get/bejne/jM-Hfayrsra1ips.html Teams Meeting - Agenda, Action Points, Notes - kzfaq.info/get/bejne/oZaka8Kes9GxdKs.html Microsoft Teams - 15 Best Practices - kzfaq.info/get/bejne/jZ6FmduQ39SvqH0.html OneDrive Best Practices Part 1 - kzfaq.info/get/bejne/ep2Kpcmhzb2YpqM.html Part 2 - kzfaq.info/get/bejne/pKyoabyC3cmRkmg.html Smart and Effective Email - 5 powerful ways - kzfaq.info/get/bejne/bLR1ebZ-1q7LgXk.html Outlook Calendar Best Practices : Part 1 - kzfaq.info/get/bejne/feCjhNGWyNClgaM.html and Part 2 - kzfaq.info/get/bejne/pseUfqtp3tmZc6c.html OneNote - Best Practices - kzfaq.info/get/bejne/o5NkdLxiyLPNaKc.html Microsoft 365 Best Practices - kzfaq.info/get/bejne/obxzkryUsZuzkok.html Cheers. Doc.

@GauravGupta-tp6ve 9 ай бұрын

Explained very well! Got more clarity on how to mange data in data model.

@efficiency365 9 ай бұрын

Thanks @GauravGupta-tp6ve Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@ariewibowo4951 11 күн бұрын

THANKSS SIR !!

@efficiency365 10 күн бұрын

Thanks @ariewibowo4951 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@azwarmzafar 9 ай бұрын

Absolutely amazing demonstration, I am in the middle of learning both and this video is really helpful for my understanding of both tools better. Many thanks.

@efficiency365 9 ай бұрын

Thanks @azwarmzafar Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@sachindotel6005 9 ай бұрын

Loved the video, thank you for making 🙏

@efficiency365 9 ай бұрын

Thanks @sachindotel6005 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@rupchandlohana 9 ай бұрын

If we use standard merge query, it results in nested join which is very slow, especially if both tables to be merged are large. It is better to switch to simple "Table.Join" than "Table.NestedJoin" if both tables are large sized.

@efficiency365 9 ай бұрын

Sure. Table.Join will get all fields. We need control over which fields are merged. Hence nested join is better. Secondly, in this type of scenario, one table - the master table - is usually small. Thirdly, to use Table.Join properly, you must choose the appropriate join algorithm. This is difficult to expect at a regular end user level. However, if both tables are very large, trying Table.Join is certainly a good idea.

@jaswinderbhatti3352 9 ай бұрын

Thanks again …. sir

@efficiency365 9 ай бұрын

Thanks @jaswinderbhatti3352 Check out my other best practices videos as well. Efficient Tasks Management - kzfaq.info/get/bejne/rLJ2o7qHy7vKgWw.html Task Apps Comparison: kzfaq.info/get/bejne/jM-Hfayrsra1ips.html Teams Meeting - Agenda, Action Points, Notes - kzfaq.info/get/bejne/oZaka8Kes9GxdKs.html Microsoft Teams - 15 Best Practices - kzfaq.info/get/bejne/jZ6FmduQ39SvqH0.html OneDrive Best Practices Part 1 - kzfaq.info/get/bejne/ep2Kpcmhzb2YpqM.html Part 2 - kzfaq.info/get/bejne/pKyoabyC3cmRkmg.html Smart and Effective Email - 5 powerful ways - kzfaq.info/get/bejne/bLR1ebZ-1q7LgXk.html Outlook Calendar Best Practices : Part 1 - kzfaq.info/get/bejne/feCjhNGWyNClgaM.html and Part 2 - kzfaq.info/get/bejne/pseUfqtp3tmZc6c.html OneNote - Best Practices - kzfaq.info/get/bejne/o5NkdLxiyLPNaKc.html Microsoft 365 Best Practices - kzfaq.info/get/bejne/obxzkryUsZuzkok.html Cheers. Doc.

@208935 9 ай бұрын

Best explained ,Thanks sir

@efficiency365 9 ай бұрын

Thanks @208935 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc

@canirmalchoudhary8173 9 ай бұрын

If tables are clean, then we can just create relationship which will add tables in Data model automatically. Coming thru Power Query keeps original files intact. 👍

@efficiency365 9 ай бұрын

Rights. Usually input data is never really clean. So Power Query needs to be used. But even input tables are clean, I always prefer to leave original data alone. If data is in Excel sheet and you add it to Data Model, it is occupying space twice. (Not exactly twice the amount of space. Data Model compresses it well. But still it is wasting space). Keep original files separate, do whatever you want in Power Query - a more modular and less error prone approach. In some cases, Excel itself encourages people to add local table to data model. That is not an optimal approach. Insert Pivot - From Range - It asks you to add to data model. Very bad idea. Learning Power Query is empowerment. Adding local data to data model is a disastrous bad habit. Of course in certain cases, there is justification for adding local data to Data Model. Small lookup tables which do not change often, for example. The key concept here is - Power Query gives you control over what goes inside Data Model. Add to Data Model does not give you any control at all. Therefore, always have PQ as a mediator.

@mogarrett3045 9 ай бұрын

Power Query is the answer

@efficiency365 9 ай бұрын

Thanks @mogarrett3045 Yes. Power Query is the solution to enormous amount of time wasted every day in data clean up. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzfaq.info/get/bejne/qNp0n7KDsdPOnHk.html Excel Green Marks - Error Checking - Best Practices - kzfaq.info/get/bejne/jst8p8-ryZ_Qgo0.html Excel Best Practices - Part 1 of 3 - Data Management - kzfaq.info/get/bejne/qNiPZMuSm82anJc.html Excel Best Practices - Part 2 of 3 - Formulas - kzfaq.info/get/bejne/oeCPraSnsq-ycZ8.html Excel Best Practices - Part 3 of 3 - Analytics - kzfaq.info/get/bejne/gLSCaNypxLbLZHU.html 10 Excel Settings You Must CHANGE! - kzfaq.info/get/bejne/rL6ipbuVsN-udp8.html Automatic data clean up with Excel Flash Fill - kzfaq.info/get/bejne/hJmgktuQ0b64j5c.html Instant Excel Audit, Comparison and Analysis - Inquire - kzfaq.info/get/bejne/maqUqbiL3bW5mac.html Six powerful Excel Navigation Shortcuts - kzfaq.info/get/bejne/mLhdrLCTrLbVZWQ.html Handle millions of rows in Excel - Slow to fast - kzfaq.info/get/bejne/b5mYatWD2LKqZ40.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzfaq.info/get/bejne/o7qkgteLrZPSfoE.html Cheers. Doc