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

Подписаться 630 тыс.

Просмотров 90 тыс.

50% 1

#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/more-than-mill...

Наука

Опубликовано:

16 сен 2019

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 115

@joe21lester 4 года назад

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 4 года назад

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 4 года назад

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!

@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.

@wuliangye 2 года назад

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!

@MuhammadArshad-gj3fy 2 года назад

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

@jorgeperezcastedo2056 2 года назад

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. :)

@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

@kldpmishra 4 года назад

I always find your videos very helpful thanks chandoo

@nazarkamal8831 7 месяцев назад

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

@growth2509 3 года назад

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.

@DuApril 4 года назад

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_ 4 года назад

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 4 года назад

@@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".

@hirengson 2 года назад

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. ?????

@ricog8067 3 года назад

very helpful. thank you sir!

@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!!!!!!!!

@pektuspektus612 3 года назад

Thanks for making me awesome in Excel Purna!

@AARSHEYSHAH Год назад

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

@svenmolter655 Год назад

helped immensely - thanks.

@MrKathayat 4 года назад

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

@chandoo_ 4 года назад

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

@MrKathayat 4 года назад

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

@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???

@sanjayvyas1063 3 года назад

Thank you so much for sharing this info, Sir!

@chandoo_ 3 года назад

My pleasure!

@AmandeepSingh-rh3zz 7 месяцев назад

Thank you ,it solved a big problem .

@robertjay4723 Год назад

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

@BenRodak 4 месяца назад

same q also...

@engr.engr.7205 8 месяцев назад

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?

@wesszep9865 4 года назад

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

@chandoo_ 4 года назад

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/

@garylhaas2005 4 дня назад

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_ 4 дня назад

Awesome to hear that...

@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?

@dorukanylmazkulas1538 6 месяцев назад

Huge Thank you sir

@jamespyle6398 2 года назад

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_ 2 года назад

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.

@weasamalaa9737 4 года назад

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

@cisca_techdiva9454 2 года назад

Thanks for this

@karishmasharma1258 2 года назад

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

@chandoo_ 2 года назад

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

@poojashah5929 2 года назад

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 2 года назад

Use power bi but the data has to be clean…

@junedshaikh2964 3 года назад

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_ 3 года назад

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.

@riteshmathur9221 3 года назад

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

@chandoo_ 3 года назад

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-PiFAa_jjaEI.html

@garavindh81 4 года назад

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

@chandoo_ 4 года назад

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

@garavindh81 4 года назад

@@chandoo_ Yes i was using headphones

@martamelo66 4 года назад

Great

@drkesavsarma_singer Год назад

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

@vinitdoshi4433 4 года назад

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_ 4 года назад

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 4 года назад

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

@saleemhussain7799 3 года назад

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_ 3 года назад

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

@ahmedabdelhafiz9717 4 года назад

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_ 4 года назад

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/

@we3605 2 года назад

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_ 2 года назад

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/

@chandrashekharreddy5623 4 года назад

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_ 4 года назад

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

@fathimar6208 2 месяца назад

What is the limit of unique items in pi ot

@lynnsingletary 3 года назад

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_ 3 года назад

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

@MrRamaeri 2 года назад

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

@chandoo_ 2 года назад

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

@sandeepkumar-cx4or 4 года назад

I m very big fan of you

@chandoo_ 4 года назад

Thank you Sandeep :)

@CaribouDataScience 2 года назад

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

@premprakash6532 4 года назад

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

@chandoo_ 4 года назад

Keep learning and share what you know :)

@HarpreetSingh1991 3 года назад

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_ 3 года назад

Thanks and welcome

@joaozinhobc2015 3 месяца назад

connection in power bi?

@saeedahmed4651 Год назад

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

@mustafabohra2652 3 года назад

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

@chandoo_ 3 года назад

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

@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...

@rakeshsahoo16 3 года назад

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

@chandoo_ 3 года назад

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

@Pivot___ 3 года назад

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

@chandoo_ 3 года назад

Sorry about that

@arunkhanal7208 Год назад

In place of "Sum of A" show the Count

@suhindia-only 3 года назад

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

@chandoo_ 3 года назад

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

@mirrrvelll5164 3 года назад

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

@chandoo_ 3 года назад

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.