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.
![](https://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.
![](https://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 a standard way, but you can create a connection to a CSV file. This works by loading data into Data Model, keeping a link to the original CSV file. This will allow you to load millions of rows.
Here’s how to do it.
- Navigate to Data >> Get & Transform Data >> From File >> From Text/CSV and import the CSV file.
- After a while, you are going to get a window with the file preview.
- Click the little triangle next to the load button.
![](https://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.
![](https://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.
![](https://officetuts.net/excel/wp-content/uploads/sites/2/2019/05/queries-and-connections.png)
- Double-click this area to open Power Query Editor.
![](https://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.
![](https://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.