Handle millions of rows in Excel - Large slow files? - use Data Model

[@Pelasomma]

You sir, are a saviour! My dashboard was getting slow and clunky due to how much data it had, now it runs quicker than it ever had before!

[@efficiency365]

Thanks @Pelasomma
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ruclips.net/video/rtDlORLmjlE/видео.html
Excel Green Marks - Error Checking - Best Practices - ruclips.net/video/XeLtlzd9lRY/видео.html
Excel Best Practices - Part 1 of 3 - Data Management - ruclips.net/video/rr_1ha6g6lc/видео.html
Excel Best Practices - Part 2 of 3 - Formulas - ruclips.net/video/kz_zAvMINAk/видео.html
Excel Best Practices - Part 3 of 3 - Analytics - ruclips.net/video/JNR5yx_Pg4A/видео.html
10 Excel Settings You Must CHANGE! - ruclips.net/video/vXrrXdKyJFk/видео.html
Automatic data clean up with Excel Flash Fill - ruclips.net/video/N3p_x_lXT_c/видео.html
Instant Excel Audit, Comparison and Analysis - Inquire - ruclips.net/video/cDdvUZxOUis/видео.html
Six powerful Excel Navigation Shortcuts - ruclips.net/video/bR-yMbGPq50/видео.html
Handle millions of rows in Excel - Slow to fast - ruclips.net/video/93h7rRsLF7Y/видео.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ruclips.net/video/mTtOtZH-nNM/видео.html
Cheers.
Doc

[@niteeshmishra2790]

Powerquery and Powerpivot is Cornerstone of Excel..... These two powerful tools make my job a lot easier.

[@efficiency365]

Yes. But many Excel users do not know about power pivot and power query. They are still cleaning up and analysing the data the old way.

[@jillwilliams2147]

@@efficiency365 I have done data models in other software but I am struggling building my models in power pivot and power query. Much of my data is being pulled from PDF and recognizing two tables instead of one. When building models in the other software I was able to create 'rules'. Which helped to avoid data or copy/fill data as needed. Any suggestions or videos to watch would be greatly appreciated! I'm currently trying to combine 4,000 pages of timesheet data to pivot and analyse.

[@RameshTim]

Excellent presentation. Exactly the solution I was looking for.

[@efficiency365]

Thanks @RameshTim
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ruclips.net/video/rtDlORLmjlE/видео.html
Excel Green Marks - Error Checking - Best Practices - ruclips.net/video/XeLtlzd9lRY/видео.html
Excel Best Practices - Part 1 of 3 - Data Management - ruclips.net/video/rr_1ha6g6lc/видео.html
Excel Best Practices - Part 2 of 3 - Formulas - ruclips.net/video/kz_zAvMINAk/видео.html
Excel Best Practices - Part 3 of 3 - Analytics - ruclips.net/video/JNR5yx_Pg4A/видео.html
10 Excel Settings You Must CHANGE! - ruclips.net/video/vXrrXdKyJFk/видео.html
Automatic data clean up with Excel Flash Fill - ruclips.net/video/N3p_x_lXT_c/видео.html
Instant Excel Audit, Comparison and Analysis - Inquire - ruclips.net/video/cDdvUZxOUis/видео.html
Six powerful Excel Navigation Shortcuts - ruclips.net/video/bR-yMbGPq50/видео.html
Handle millions of rows in Excel - Slow to fast - ruclips.net/video/93h7rRsLF7Y/видео.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ruclips.net/video/mTtOtZH-nNM/видео.html
Cheers.
Doc

[@fernandogaudenzi7631]

Dear Dr Nitin. Thank you very much for your explanation. It was finally the solution I needed to consolidate the data from historical data in separated excel files (abou 3 million records) and analyze them all together. Thanks again!

[@efficiency365]

Thanks @fernandogaudenzi7631.
Check out my other best practices videos as well.
Efficient Tasks Management - ruclips.net/video/vLFpWVfUfQ8/видео.html
Microsoft Teams - 15 Best Practices - ruclips.net/video/W8Ufx_znKxI/видео.html
Smart and Effective Email - 5 powerful ways - ruclips.net/video/6NEFSMqHgQE/видео.html
Outlook Calendar Best Practices : Part 1 - ruclips.net/video/GzsQnecjAQo/видео.html and Part 2 - ruclips.net/video/padKH8ys5Cs/видео.html
OneNote - Best Practices - ruclips.net/video/m-4AY1cMi8s/видео.html
Microsoft 365 Best Practices - ruclips.net/video/kVC_YcL5ObU/видео.html
Word Tables Best Practices - The ultimate guide - ruclips.net/video/ueKCHxNUOkA/видео.html
Cheers. Doc.

[@captainchannel8210]

If you can provide dataset. It can be helpful

[@CalebHarrison-fz6lu]

This is awesome. Thank you for the video

[@efficiency365]

Thanks @CalebHarrison-fz6lu
Check out my popular Excel videos
How to enter and edit Excel Formulas - Back to Basics - ruclips.net/video/rtDlORLmjlE/видео.html
Excel Green Marks - Error Checking - Best Practices - ruclips.net/video/XeLtlzd9lRY/видео.html
Excel Best Practices - Part 1 of 3 - Data Management - ruclips.net/video/rr_1ha6g6lc/видео.html
Excel Best Practices - Part 2 of 3 - Formulas - ruclips.net/video/kz_zAvMINAk/видео.html
Excel Best Practices - Part 3 of 3 - Analytics - ruclips.net/video/JNR5yx_Pg4A/видео.html
10 Excel Settings You Must CHANGE! - ruclips.net/video/vXrrXdKyJFk/видео.html
Automatic data clean up with Excel Flash Fill - ruclips.net/video/N3p_x_lXT_c/видео.html
Instant Excel Audit, Comparison and Analysis - Inquire - ruclips.net/video/cDdvUZxOUis/видео.html
Six powerful Excel Navigation Shortcuts - ruclips.net/video/bR-yMbGPq50/видео.html
Handle millions of rows in Excel - Slow to fast - ruclips.net/video/93h7rRsLF7Y/видео.html
Convert crosstab to tabular - Unpivot - Excel Power Query - ruclips.net/video/mTtOtZH-nNM/видео.html
Cheers.
Doc

[@ghargettCarolina]

I need to filter rows by evaluating whether a text field used for notes has a particular text string in it. How might be best to do that?

[@efficiency365]

In Power Query, you can filter on the column.
In Power Pivot, you can use interactive "Contains" filter in the pivot.
OR you can write a DAX formula to filter on the text and use that measure as a filter condition.

[@vijayarjunwadkar]

Thanks a lot Dr. Nitin! While I am already using these tools, it's first time, I could see large data model in action and the way you have explained and demonstrated is excellent! Looking forward to your inputs on using DAX soon. 🙂👍

[@efficiency365]

Thanks. DAX coming soon.
I still don't see many people using Data Model.
In the Excel user community,
1. there is lack of awareness
2. reluctance to move to data model
3. active resistance to use data model because it cannot be used with regular Excel formulas

[@Kuttypisaasu]

hi sir, what if i have a big file for every day and i created a final file with the above instructions, and then i wanna add new files into it, will the final file automatically pull data from the new file i added in that folder, example, i already have 29 days file and i added 30th day file after creating the data model file?

[@efficiency365]

When you add the new file in the folder, go to the consolidated file - right click in the data and Refresh.
Refresh will reprocess all the files. Not just the new one. So as you add more files, it takes more time. But it is automatic. So no other disadvantage.

[@sivaluxt.8575]

Very much appreciated!

[@efficiency365]

Thanks @sivaluxt.8575 for the feedback.
You may also like these Word related videos:
Word top 10 settings - ruclips.net/video/_8r7vNrqh34/видео.html
Word Tables - 8 ways to create: ruclips.net/video/X-G8bZVCGBk/видео.html
Word Tables - 13 problems solved: ruclips.net/video/y5IblpvN_Jg/видео.html
Word Styles - ruclips.net/video/7f_aOgsvKA0/видео.html
Handing long, complex word documents - ruclips.net/video/9jrYJHQ7YmQ/видео.html
Thanks.