Excel is excellent at so many day-to-day tasks. But every now and then, you encounter one of its shortcomings: the size of a spreadsheet. If you need to know how to make an Excel spreadsheet smaller or how to split a large CSV, we have you covered.

Hitting the spreadsheet row limit is a pretty serious task in and of itself. The current Excel spreadsheet row limit is 1,048,576. That’s right — over 1 million rows, and the same amount of columns, too.

1. Use a Program

CSV Chunker is an open source CSV splitter. It can handle massive files, rapidly splitting them into chunks of your choosing. It took about 3 seconds to split our Hospital Compare CSV into 106 chunks, containing 2,500 rows each.

CSV Splitter is our second tool. It offers much the same functionality of CSV Chunker, albeit with a slightly sleeker design. CSV Splitter took about 4 seconds to split our Hospital Compare CSV into 106, again containing 2,500 rows each.

@echo off
setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=HCAHPSHospital.csv
REM Edit this value to change the number of lines per file.
SET LPF=2500
REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=HosptialSplitFile
REM Do not change beyond this line.
SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
)
)
endlocal
Pause

You’ll need to configure the batch file before running. I’ll tell you what each command does, and you can alter it to suit the size of your batch file, as well as the required output.

“SET BFN=” should point to the CSV you need to break down

“SET LPF=” is the number of rows you’d like to limit your new file to

“SET SFN=” is the new naming scheme for your split files

Once you’ve entered your variables, head to File > Save As. Choose filename, and select Save. Then, select your newly saved text file and press F2 to rename it. Replace the TXT extension with BAT, and press OK when the warning appears. Now you can split your large CSV!

I warn you though, this method does take some time. You’re probably better off using one of the aforementioned splitters.

3. Use a PowerShell Script

Batch files are handy. You can use them for a wide range of day to day tasks. But PowerShell scripts are by-and-large faster, especially for this type of processing and division.

The following script will quickly cut your large CSV into smaller files. Again, I’ve used the Hospital Compare CSV for my example. It took about 3 seconds for the command to run. Open the Windows PowerShell ISE. If you’re unsure on how to do this, type powershell ise into your Start menu search and select the corresponding option. When the PowerShell ISE is open, select the Show Script arrow, located in the top right corner (outlined in red in the below image).

Copy and paste the following command into the script panel, at the top of your screen.

4. Load It in a Data Model

Our final solution isn’t actually splitting the large CSV into smaller parts. You see, Jose Barreto, a member of the OneDrive team, recently illustrated exactly how you can load a large CSV file in Excel. In this case, large means over the 1,048,567 row limit. He explains in a tutorial that instead of actually loading the file, Excel 2016 can create a data link to the CSV file.

Microsoft Excel 2010
Open Microsoft Excel.
Click on the Data tab.
In the Get External Data group, click From Text.
Double-click the text file that you want to import in the Import Text File dialogue box.
Click Import. The Text Import Wizard will begin.
Select Delimited and click Next.
Uncheck Tab and select Comma.
Click Next.
Click Finish.
Select to save the information in either an existing worksheet or a new worksheet.
Note: The contacts should now be in Excel, split into columns.
On the Excel toolbar, select File > Save As. A dialog box pops up.
Type a new name for the file in the File Name text field.
Under Save As Type, choose Excel Workbook (*xlsx), Excel 97-2003 Workbook (*.xls), or CSV (Comma Delimited)(*.csv) and click Save.
If you saved as a CSV file:

Click OK for the message "The selected file type does not support workbooks with multiple sheets".
Click Yes for the message that the file "may contain features that are not compatible with CSV".

CSV files have no limit of rows you can add to them. Excel won't hold more that the 1 million lines of data if you import a CSV file having more lines. Excel will actually ask you whether you want to proceed when importing more than 1 million data rows.

I’m working on 10 million records email addresses..I have to verify it and remove invalid emails, bounces, spam trap... then create 200 CSV files. Which is the best software for clean huge databases? I need more tools? I need help please.

Hi Diana, I have the same problem. I'm working on 10 million records email addresses..I have to verify it and remove invalid emails then create 200 CSV files. Which is the best software for clean huge databases? You can help me please?

I actually ran into this issue a few days ago, and I used the same tool. One thing that also helped, in not needing to have it split it up into as many separate files, is that Excel 2007 can handle 2^20 rows (1,048,576) as opposed to 2003's 2^16 (65,536).

Also, once you have all these separate files, you can put them all in one XLS file by adding them each as separate worksheets to the same file (the row limit is per worksheet).

Gavin is a Senior Writer, focusing predominantly on security. He has a BA (Hons) Contemporary Writing with Digital Art Practices pillaged from the hills of Devon, as well as 10 year's professional writing experience. He enjoys copious amounts of tea.