Can You Still Do Analysis In Excel If It Has Too Many Rows? Is It Possible? -

As many people know Excel spreadsheets have a limitation of approximately million rows so you may be wondering what to do if you need to do an analysis in Excel with a dataset that is larger than this is it even possible?

It is possible to complete an analysis in Excel with more than a million lines however you cannot directly import a file with more than a million lines you have to use the getdata function within excel to get to the large datasource. This function will connect the data to the spreadsheet though it cannot display it but it will allow a pivot table to be used to then analyze the data.

How To Connect A Large Dataset To Excel

In this example will be connecting Excel to a CSV file containing more than a million rows of data however It is possible to connect up types of data as you’re see from the options available in the get data function.

Step 1: Select the data tab on Excel’s ribbon and then getdata which is located on the left-hand side of the tab then select file and From Text/CSV.

Step 2: Once the data has been connected which sometimes will take a while depending on your data size it will display sample data for the first 200 lines. The next step is to select the load button which is located at the bottom of the window and then select Load To…

Step 3: To import data select Only Create Connection and check the Add data to the Data Model box and click ok.

Step 4: Once the connection is established a query and connections tab will appear on the right hand side of your screen with the file. If you hover your mouse above the file excel will provide a preview of the data that has been connected to Excel.

Analyzing The Data In Excel

Once the connection is created between your source file and Excel it is possible to analyze the data using a pivot table or power query. to create a pivot table in Excel that is connected to the data Is fairly straightforward.

Select the insert tab and select pivot table and then From Data Model.

Then select the location where you want to pivot table to appear and click OK.

Once the pivot table appears it will then operate in a slightly different way to normal pivot tables because only the file name will appear. To make the fields appear click on the file name however, the column titles do not appear in the list instead each field is named generically as Column1, Column2, Column3 etc…

To see the data with an edge column you can either drag the column into the pivot table as you normally would or alternatively click the arrow on the right-hand side of a column name to see the values.

The only limitation to this is that if there is over a million lines of data the values will not be displayed and will be required to be reduced before they can be displayed.

It is also important to note that even though the column title is not displayed in the name of the field, it will not appear in the pivot table as it is excluded from the data.