How to Split a Big Email List Into Smaller Batches

A list of 3,000 contacts is easy to keep in one spreadsheet, but it is often awkward to use in one piece. Your email account may refuse to send that many messages in a day, an app may choke on a big import, or you may simply want to try a message on a small group before sending it to everyone.

Splitting the list into smaller batches solves all of these. This guide explains when splitting makes sense, what the published sending limits are for Gmail, Google Workspace and Outlook.com, how to split a list in Excel or Google Sheets, and why a very large list probably belongs in a proper email marketing service instead.

Good reasons to split a list

• Staying under sending limits. Personal and business inboxes cap how many messages or recipients you can send per day. Batches let you spread a send over several days.
• Import limits. Some CRMs, mail merge add-ons and older apps limit how big an upload can be. If an import fails or times out, smaller files usually go through. Check your tool's help page for its exact limit.
• Testing before the big send. Send to a small batch first. If the first-name field shows up blank or a link is broken, you have embarrassed yourself in front of 50 people, not 3,000.
• Sending in waves. Spreading a campaign out lets you watch replies and bounces from the first wave and adjust before the next one.
• Sharing the work. A club or nonprofit can give each volunteer their own batch of 100 people to call or email, with no overlap.

Sending limits for personal and work inboxes

These are the limits the providers publish on their own help pages. Providers can change them without notice, and new accounts often start lower, so treat them as ceilings, not targets.

For Workspace, Google also counts messages sent from aliases and by delegated users toward the daily limit, and Gmail's built-in mail merge has its own lower cap of 1,500 per day. If you plan around these numbers, leave yourself a healthy margin. Hitting the cap in the middle of a send leaves you guessing who got the message and who didn't.

Why a personal inbox is a risky way to send bulk mail

Staying under the limit does not make bulk sending from a personal or work inbox a good idea. A few hundred identical messages leaving one account looks a lot like spam to the provider's filters, and if enough recipients mark it as spam or addresses bounce, your account can be restricted. That is the same account you use for clients, family and everything else.

A personal inbox also lacks the basics a marketing send needs. It does not add an unsubscribe link, does not remove people who bounce, and does not keep track of who opted out. Pasting hundreds of addresses into Bcc also makes it easy to slip and put them in To or Cc, exposing everyone's address to everyone else.

If you send marketing email in the US, the CAN-SPAM Act requires things like a working way to opt out and your physical postal address. This is not legal advice; the FTC's CAN-SPAM compliance guide explains the rules in plain language.

For a list of more than a few hundred people, an email marketing service is the better tool. These services handle unsubscribes, bounces, and sending at volume for you, and many have free or low-cost plans for small lists. Splitting is still useful there for testing and waves, but you won't be fighting daily caps on your personal account.

Clean the list before you split it

Splitting a messy list just gives you several messy lists. Do the cleanup once, on the whole file, before you cut it up:

1. Remove duplicates so nobody gets the same message twice because they landed in two batches. The duplicate remover can also treat Gmail dots and +tags as the same address.
2. Take out unsubscribes and anyone who asked not to be contacted. Use compare lists to remove one list from another, and see our guide to keeping a suppression list.
3. Remove dead and mistyped addresses. Bounces matter even more when you send from your own inbox, because they count against your account. Before your first wave, check the whole list with Veml; it tells you which addresses are Passed, Risky, Unknown or Invalid without emailing anyone, and you can download the results with the good ones first.

How to split a list in Excel or Google Sheets

The idea is simple: give every row a batch number, then pull each batch out into its own sheet or file. These steps assume emails in column A, a header in row 1, and data starting in row 2. The example uses batches of 250; change the number to whatever you need.

1. Decide how many batches you need

In any empty cell, enter =ROUNDUP((COUNTA(A:A)-1)/250,0). COUNTA counts the filled cells in column A, the -1 removes the header, and ROUNDUP rounds up so a partial last batch still counts. For 1,130 contacts, you get 5: four batches of 250 and one of 130.

2. Number each row's batch

Insert an empty column B and type Batch in B1. In B2, enter:

=INT((ROW()-2)/250)+1

Copy it down to the last row. Row 2 gets batch 1, row 251 is still batch 1, and row 252 starts batch 2. Because the formula is based on row position, do any sorting or shuffling before this step, or the batch numbers will shift with it.

3. Pull each batch out

Any version of Excel, or Google Sheets: turn on a filter (Excel: Data > Filter; Sheets: Data > Create a filter), click the arrow on the Batch column, and show only batch 1. Select the visible rows, copy, and paste them into a new sheet. Repeat for each batch.

Excel for Microsoft 365, Excel 2021 or later, and Google Sheets: on a new sheet, one formula does it. If your contacts are on a sheet named Contacts in columns A to D:

=FILTER(Contacts!A2:D5000,Contacts!B2:B5000=1)

Change the 1 to 2 on the next sheet, and so on. Copy the header row in above the results yourself, since this range starts at row 2.

4. Save each batch as its own file

Most tools want a CSV per upload. In Excel, go to the batch's sheet and use File > Save As (or Save a Copy), then choose CSV as the file type. Excel only saves the sheet you are looking at into a CSV, which is exactly what you want here. In Google Sheets, open the batch's tab and choose File > Download > Comma-separated values (.csv); it also downloads just the current tab. Name the files clearly, like spring-update-batch-1-of-5.csv.

The faster way: split in your browser

If you don't want to build formulas, paste your list into the free list splitter, choose the batch size, and download each batch as its own file. It runs entirely in your browser, so your contacts are never uploaded anywhere. It is handy when you need to re-split often, such as changing from batches of 250 to batches of 400 after your first wave goes well.

Plan your waves

A little planning makes batch sending smooth:

1. Send a test to yourself and a colleague using a few real rows, so merge fields fill in correctly.
2. Send batch 1 and wait. Give it a few hours to a day. Look at bounces, replies and any unsubscribe requests.
3. Fix anything that went wrong before the next batch. Remove addresses that bounced from your master list, not just from the batch file.
4. Keep a simple log of which batch went out on which date, so you never send batch 3 twice or skip it.
5. Update the master list at the end with every bounce and unsubscribe, so the next campaign starts clean.

If you are pulling contacts from several places before your next send, our guide to merging contact lists shows how to combine them first without creating duplicates.