Posted on by Tomasz Decker
Excel contains over one million rows – 1,048,576 to be exact. If your file is larger than that and you will try to open this file in Excel, you are going to get the following message.
![Open Large CSV in Excel (1) Open Large CSV in Excel (1)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/file-not-loaded-completely.png)
After you click OK, you can move to the last row (Ctrl + Down Arrow) and see that the worksheet is filled to the last row.
![Open Large CSV in Excel (2) Open Large CSV in Excel (2)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/last-row.png)
Open large CSV
There is a solution in Excel. You can’t open big files in astandard way, but you can create a connection to a CSV file. This works byloading data into Data Model, keeping a link to the original CSV file. Thiswill allow you to load millions of rows.
Here’s how to do it.
- Navigate to Data>> Get & Transform Data >> From File >> From Text/CSVand import the CSV file.
- After a while, you are going to get a windowwith the file preview.
- Click the little triangle next to the load button.
![Open Large CSV in Excel (3) Open Large CSV in Excel (3)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/load-csv-file.png)
- Select Load To…
- Now, we need to create a connection and add data to the Data Model. This won’t load data to an Excel sheet because we have a limit of about a million rows there.
![Open Large CSV in Excel (4) Open Large CSV in Excel (4)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/import-data-1.png)
- Click OK. This will take a while to load.
- On the right side, you have the name of our file and the number of rows. If you save the file, you will notice that its size increased significantly.
![Open Large CSV in Excel (5) Open Large CSV in Excel (5)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/queries-and-connections.png)
- Double-click this area to open Power Query Editor.
![Open Large CSV in Excel (6) Open Large CSV in Excel (6)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/power-query-editor.png)
- Now, if you scroll down, you will notice that the new rows are added on the go.
- To change a value, right-click a cell and select Replace Values.
![Open Large CSV in Excel (7) Open Large CSV in Excel (7)](https://i0.wp.com/officetuts.net/excel/wp-content/uploads/sites/2/2019/05/replace-values.png)
- It’s going to replace all “builders” with “roofers” under the “profession” column.
Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.
Post Views: 132,692