Analyse million plus records in Excel

Now we often hear that on Excel you cannot work on more than 1 million rows.

This sounds logical as an Excel worksheet has 1048576 rows only, so practically its not possible to import data files with more than 1million rows in Excel to carry out any analysis or report generation.

So, what to do when data size is bigger. The size of data is becoming bigger and the need to analyse bigger data files to draw meaningful conclusions is a reality for every function and industry.

The answer lies in creating and working on Data Models in Excel.

The next immediate questions are-

What is a data model?

Is there any limitation on file size while working on Data Models?

Do Data Models require any specific programming skill?

How to work with a Data Model?

We will answer all these questions in this article-

What is a data model?

Microsoft introduced Data Model in Excel 2013. It allows you to work on large data files (No constraint of Size limit) and easily create summaries, reports and charts.

Is there any limitation on file size while working on Data Models?

There is no size limit when you are working with Data Models. The reason here is that when you working on Data Model, the data is not being stored in Excel worksheet cells rather it is being stored in your computer memory. The speed of processing of data in Data Models will be dependent on your system hardware.

Do Data Models require any specific programming skill?

No, if you have been working on Excel, you can easily learn to create and work with Data Models.

How to work with a Data Model?

You can create and work with Data Model by following the steps explained here-


Import large Data Files using Power Query

When you click on Data Tab and the ribbon which opens has Get and Transform Data as the first Group, as shown in screenshot below.

Next, we click on Get Data and from the dropdown choose From Text/CSV. Here we can see that Excel has options to import data from lots of databases and allows many file types.


Load Large Data Sets to Data Model

Once we select file type as From Text/CSV, a window opens and allows us to choose the file location on our system where we have kept our file with Big Data and click on open.

The window opens up and we get options to Load and Transform Data. At this stage we can perform secong step of ETL (Extraction, Transformation and Loading). In this article we will be focussing only on creating a Data Model. Transformation of Data will be discussed separetely.

Now as we click on Load To, a new window props up.

In this window we carefully need to make two selections-

First is Only create connection and

Second is Add this Data to the Data Model

Our Data Model is created and we have seen how we can import data of any size in our Data Model with these simple steps.

Once this data model is created now we will go ahead and see how we can use Pivot table and perform any analysis with this Big Data.

We can go to the Insert tab and select Pivot Table and Pivot Table window opens up. Here we select Use this workbook’s Data Model and select cell location and click on OK.

The Pivot table is created and it is showing the table which we have imported 2016 Data3, and now we can create our reports and do any analysis with this Data in our Pivot Table.

Let me know if this article was helpful.

We at Excel Career Hub provide

  • Excel Training for MBA Students

  • Advance Excel course for professionals

  • Online Excel Course for students

3 views0 comments

Recent Posts

See All