How to handle more than million rows in Excel - Interview Question 02

#MoreThanMillionRows #ExcelInterviewQuestions #DataModelExcel
Can you handle more than a million rows in Excel? If so, how?
In this episode of Excel Interview Questions, let's review Excel Data Model and how you can use it to analyze large sets of data.
For more on topic and resources, visit
===============================
chandoo.org/wp...

[@joe21lester]

Nice to see you here Sir.......You are the reason I started developing interest in learning Microsoft Excel.........You are a living legend Sir.

[@MLFranklin]

This is a huge issue for me. Currently I'm breaking my CSV file up with a macro to make it manageable in Excel. But even with that automation, there is still a lot of manual work in getting it into the pivot tables that I need. You're offering a logical solution. I'll have to try it this week. Thanks so much for sharing your knowledge!

[@wayneedmondson1065]

Hi Chandoo.. thanks for the video. Nice to understand the technical aspects of data compression and file size. Also, was not aware that Power BI had an advantage with larger data sets.. makes sense.. now that you point it out and given the purpose and design of Power BI vs. EXCEL. Thanks for sharing your knowledge. Thumbs up!

[@MrKathayat]

Big fan of yours sir, a long time student probably more then 12 years.....

[@chandoo_]

Thank you Manoj. I feel blessed to have fans like you. :)

[@MrKathayat]

Pleasure all mine sir ji.....My bread and butter is excel....

[@pektuspektus612]

Thanks for making me awesome in Excel Purna!

[@jorgeperezcastedo2056]

Thank you so much Chandooo for sharing this great tip. I love pivots and always wondered how could anylize millions of rows. This is awesome, you may my day. :)

[@waltruiz]

This is great, I have a question, I have a query in access with more than 12 millions of records, how can I bring the data for analysis in excel???

[@cacacdt]

Excelent suggestion!! I couldn't with a xls or xslx file, but I downloaded the report with CSV and then it was possible to proceed as you suggested. Thanks!!!!!!!!

[@garylhaas2005]

I had a 10+ million row table of general ledger transactions - i imported this into Excel data model - Access file was over a gigabyte, Excel file was 40 million bytes

[@chandoo_]

Awesome to hear that...

[@robertjay4723]

Thanks for this - could you search or look up against those numbers? a large v lookup for example

[@BenRodak]

same q also...

[@wuliangye]

Amazing stuff.. Solved a big issue I have when analyzing huge amount of records. Hope my evolution will be to update my skills and tools to something more powerful thank Excel!

[@nazarkamal8831]

Hi chandoo!! If we load that data in excel cant that reflect in another sheets in this same workbook ???

[@AmandeepSingh-rh3zz]

Thank you ,it solved a big problem .

[@hirengson]

Hey I have really difficult question in my mind can u just solve this.. I was doing word column combinations in my excel. And rows are continuing loding till now. So can I save it.. for stop loading rows and catch up already loaded rows. ?????

[@garavindh81]

Hi Chandoo. Could you speak louder in the future pls. Voice is not audible.

[@chandoo_]

Thanks Aravindhan. May I recommend using headphones to listen. I will try my best to amp the audio in future.

[@garavindh81]

@@chandoo_ Yes i was using headphones

[@MuhammadArshad-gj3fy]

Hi, can use this excel model data for machine learning by importing excel model CSV file?

[@CaribouDataScience]

My best is 50 csv files containing 3,000,000 rows using power query.

[@premprakash6532]

Chandoo..what's the secret of your profound knowledge..?

[@chandoo_]

Keep learning and share what you know :)

[@AARSHEYSHAH]

Sir, Can I analysis two or more excel file on the same way as shown in video?

[@kldpmishra]

I always find your videos very helpful thanks chandoo

[@kalpeshmutha8033]

Hi, We use excel for audit analytics wherein lot of formulas (length, IF and, IF or etc are used). Only limitations we have with excel is the 1 million row restriction and calculation speed for large sheets. Can you suggest if we can do automation of analytics checks in Power BI which throws out exceptions into separate sheets once we run a query

[@vin7281]

What’s length for

[@engr.engr.7205]

Now if i want to remove some data or replace some data from +2 M rows using data model. How to save the modified data to csv after this change?

[@dorukanylmazkulas1538]

Huge Thank you sir

[@growth2509]

One challenge I note is that when an Excel file gets too large because it contains large amounts of data the file starts to lag tremendously. What about connecting a query to an Access database. Will this mitigate the processing limitations of Excel in such a case.

[@poojashah5929]

Hi Chandoo Sir, Thank you for sharing this video, I have a 3 year data (2019, 2020, 2021) in excel in Individual sheet those 3 individual sheet contain rows upto 700000 and I want to combine all those sheet below each other how am i suppose to do, I dont have a csv file as mentioned in video. Can you please guide me Best Regards, Pooja

[@kingzack2492]

Use power bi but the data has to be clean…

[@lynnsingletary]

Hi. I am trying to learn how to use power bi and I appreciate your knowledge and your work. but, I can't hear you on this video?

[@chandoo_]

Sorry about that. Please try with headphones or on a different device.

[@we3605]

Hi Chandoo, This video is really very helpful, but the voice is low. And at 05:04 mins, you switch to option of Importing the file. That step is little too fast. Is it possible to correct and redo this? Thanks.

[@chandoo_]

Sorry about that We360. The audio quality is so much better in videos from late 2019. I have an article with the same info. May be refer to that - chandoo.org/wp/more-than-million-rows-in-excel/

[@cisca_techdiva9454]

Thanks for this

[@manojkumar-pp5zb]

I have 7 lakhs rows in excel , which has 10 columns, in the first column I have a data which is nothing but ID and the entire data is associated with this ID.But I have duplicates in ID and am trying to remove duplicates through Data Tab> Remove duplicates, but it is deleting only the duplicates in the first column ( i. e 7 lakhs to 6 lakhs ) but the other columns are not getting deleted.Now the first column has 6 lakhs rows and the other columns has same 7 lakhs. I couldn't remove the entire rows based on the duplicates value found in the first column.Kindly help me out of this

[@HarpreetSingh1991]

Joined Today. Very good and precise way of making us understand the complex work of excel in a very easy way. I am e-commerce and my work is in excel. My i3 pc takes too much time to process. Looking forward to your new videos and will watch old ones in free time 👍🙂

[@chandoo_]

Thanks and welcome

[@santanuroy571]

I want to do something else ..we know that in a single tab it can display 1048576.. so for 3.5 million records i want as soon as it reaches that limit the next rows creates new tabs and moves there and so on .. untill it reaches to the count.. how can we do it in excel?

[@drkesavsarma_singer]

Can we extend rows from 10 lacs to 20 lacs in Excel ?

[@dalerdaler7728]

thanks Chandoo you help me

[@mustafabohra2652]

Sir I suggest you to use a good microphone for recording the voice

[@chandoo_]

Thanks Mustafa... Please listen to one of the newer videos and let me know if this problem persists.

[@DuApril]

Thank you for the video! When I go to "get external data", I only see "text" option but no ".csv". If I select it, it takes me to a text import widzard. Is it because I don't have PowerBI set up? Any help is appreciated!

[@chandoo_]

Hi April Du... You are welcome. Do you know the version of Excel you are using? You may want to click on "Get data" from "Get data & Transform" area of the DATA ribbon. This is available in Excel 2016 or above. It is also available in certain versions of Excel 2013 by default. If not available, you can download the FREE add-in from Microsoft - www.microsoft.com/en-us/download/details.aspx?id=39379

[@DuApril]

@@chandoo_ Thank you for the reply! I am using 2016. I think I figured it out: I needed to select "New query" instead of "get external data".

[@karishmasharma1258]

Hi Chandoo,I am trying importing the doc in .CSV format but the 'Only create Connnection' is disabled.Please suggest

[@chandoo_]

Hmm.. What version of Excel are you using? Are you using the import option in Power Query or old "connection" options?

[@weasamalaa9737]

dear thank you for your great effort , but excuse me i have 2 questions 1) when i download your sample file its size (csv file) is 69 mega bytes not 706 mega byte as you said . 2) how can i create by my own csv file that exceeds 1 million rows to retrieve it later and export it to excel by the power query tool thanks in advance

[@chandrashekharreddy5623]

Thank you for the video. I have been looking for this. Could you please let me know how to find a string from more then 1 million records in Excel.

[@chandoo_]

Thank you. While you can use formulas like vlookup OR xlookup to do this, you are better off handling such searches thru Power Query.

[@saleemhussain7799]

Can you please share some leave schedules. Actually we have people working 1 month work 1 month leave. It's called rotation work and leave cycle. Thanks

[@chandoo_]

You can use simple formulas to calculate this. Refer to other videos on the channel and give it a try.

[@sanjayvyas1063]

Thank you so much for sharing this info, Sir!

[@chandoo_]

My pleasure!

[@ricog8067]

very helpful. thank you sir!

[@jamespyle6398]

A lot of my files have 800k rows and even those won't work properly..when I try to do pivots thinsg go all to heck. For instance I added a table with 800k rows in data model, then added a calculated formula with CONCENATEX to turn a value into text, and it will not display. If I cut my data in half then it will. How can I get around this limitation?

[@chandoo_]

Power Pivot is the fastest way to get results with such large data. If it is still slow, I would optimize my data model and calculations. 1) For ex: The X functions run for every row in the table and hence they can be slow. Can you reduce the size of the table thru slicers or get the results in some other way? 2) Adding more resources to Excel. closing unnecessary programs, adding memory or upgrading your computer 3) Try the same in Power BI and use the profiler to see which part of the calculation is taking more time. Then optimize the flow. All the best.

[@wesszep9865]

Hi Mr Chandoo, if my excel starts to exceed the number of columns and rows, is there anything I can do about it?

[@chandoo_]

I think it is best to keep data in a database rather than in Excel file. If you prefer keeping data in Excel, split the file in to multiple files (by day or someother criteria) and use Power Query to merge data for analysis. See this for a case study - chandoo.org/wp/combine-excel-files-using-power-query/

[@martamelo66]

Great

[@vv9730]

but how to proceed if analysing the data involves calculation based on every rows with database > 10M rows, & then select few rows based on certain calculated parameters... is Power BI is the only option remains in that case..???

[@chandoo_]

Yes, or use SQL. If you have the data in DB, you can use SQL to extract what you want. You can even use SQL with Power Query to run the Query against your DB and return the raw data to Excel.

[@vv9730]

@@chandoo_ thnx for the response,but have no knowledge of SQL...

[@fathimar6208]

What is the limit of unique items in pi ot

[@saeedahmed4651]

i cant deal with the large volume data using rows 600,000 cells. the rows are 100,000 rows.

[@joaozinhobc2015]

connection in power bi?

[@riteshmathur9221]

Hi Sir, Assume this data have 20 Location and i need 10 location data (10Lakh rows) with macro, how will i do?

[@chandoo_]

You can use Power Query to extract partial data. Just connect the file to Power Query, set up filter and extract as table. See my PQ tutorial for more. kzbin.info/www/bejne/hpqpcpSVn8-Ue6s

[@vinitdoshi4433]

Hi! I am bit confused. If my excel file can accommodate only 1 million rows, how did u get 2 million rows single file. I am sure it has to come from.multiple sheets or files. How do we do that in data model?

[@chandoo_]

That file is a CSV file. It can have any number of rows. You just can't open it in Excel if it has more than 1mn rows, but you can connect to it thru Power Query and load contents to data model.

[@vinitdoshi4433]

@@chandoo_ Thank You for your prompt responses.. I will try with csv file now.

[@junedshaikh2964]

I have 2 spreadsheets with a million of rows in each spreadsheet, how can I merge them in one which power query.. Please help!

[@chandoo_]

See the video where I demo how to use Power Query to handle such large volumes. You cannot create a final spreadsheet, but you can load the data in to "Data model" for pivot table analysis.

[@sandeepkumar-cx4or]

I m very big fan of you

[@chandoo_]

Thank you Sandeep :)

[@Pivot___]

incredible work. thank you. your mic broken af tho lmao

[@chandoo_]

Sorry about that

[@ahmedabdelhafiz9717]

Dear Excell Data send to me in daily basis and I collect it in last of month, data becoming more than 1 million rows so How can I collect it in one sheet ? I using excell 2013

[@chandoo_]

Hi Ahmed, As shown in the video, there is a limit of what you can keep in the spreadsheet. With such large data Excel will be slow. I suggest either daily files (as CSV or Text) and using Power Query to combine data for analysis purpose. See this for tips on how to combine files - chandoo.org/wp/combine-excel-files-using-power-query/

[@MrRamaeri]

Sir, How to edit the rows, I have 2305746, rows, and I want to calculate P value,

[@chandoo_]

With such large volume of data, you should use other methods to calculate things. I would consider sampling a 1000 rows.

[@shagunsahani6722]

When clicked upon the data, only 1000 rows is shown. How to see the entire data in excel

[@chandoo_]

You can't "see" more than 1 million rows of data in Excel. You can only "analyze" the data.

[@suhindia-only]

Video has good stuff. Sound or audio needs to be more clear and load. Good job though.

[@chandoo_]

Thank you. I have been improving my videos. Check out the recent ones too.

[@rakeshsahoo16]

How to view the table after creating model. ???

[@chandoo_]

The model stays in Excel's memory. You can only view pivoted results, not actual data

[@arunkhanal7208]

In place of "Sum of A" show the Count

[@bigmoist2112]

if we need to add a custom column in the raw data, how do you suggest we go about it? Lets say its a vlookup where i have to refer to another excel.

[@mirrrvelll5164]

Well yeah, but unfortunately not all versions have Power Pivot.

[@chandoo_]

Starting Excel 2013, both Power Query and Power Pivot are part of all versions of Excel (except Home & Student). If you are at a work place, chances are both these amazing tools are available to you already.

[@balakrishnanp7190]

Thanks. But audio recording is poor

[@chandoo_]

Sorry about that Bala... Please try with headphones..

[@sujitrajput6609]

where is your voice brother.. u talking to yourself?

[@khalidmumtaz7295]

voice is not clear

[@robertnoyes]

STOP MOVING THE MOUSE! Point and leave till your next point!

[@chandoo_]

Sorry, my mouse sensitivity used to be way high few years ago. Now, it is lower and I smooth cursor movements when recording the vids. :)

[@sm2973]

Volume is low

[@chandoo_]

Sorry about that. Please use headphones to enjoy the video.

[@bhaskarmukherjee7889]

Volume is less in dis Video

[@chandoo_]

Sorry about that. Please use headphones to enjoy the video.

[@sudhirverma5403]

Lots of information you give but sorry to say video sounds is very poor

[@chandoo_]

Sorry about that... I will probably re-record this topic.

[@bilalzahid4015]

Poor Voice quality

[@chandoo_]

Sorry about that. Please use headphones.

[@ParadiseRS1234]

your voice is not clear

[@chandoo_]

Thanks Raouad for letting me know. I will do my best in next videos :)