How to Split Data Into Multiple Sheets in Excel

To split data into multiple sheets in Excel, such as one sheet per region, rep or month, you have three good options: a FILTER formula on each sheet, the PivotTable command Show Report Filter Pages, or filtering and copying by hand. This guide walks through all three on the same order list, so you can pick the one that fits: live sheets that update, a fast one-off split, or a method that works in any version.

Which method should you use?#

Use FILTER formulas if you want the split sheets to stay up to date. Use a PivotTable if you want many summary sheets in one click. Use filter and copy for a one-time split in older Excel.

All the examples use this order list on a sheet called Data:

Step 1: List the groups with UNIQUE#

Before you add sheets, find out how many groups there are. On a spare sheet, type:

=UNIQUE(Data!B2:B8)

It spills North, South, East: three regions, so three sheets. On a real list with hundreds of rows, this step stops you from missing a group or making a sheet for a typo like "Nrth". The UNIQUE function guide explains the options.

To add a sheet, click the + next to the sheet tabs, or press Shift+F11 (Mac: Fn+Shift+F11 on most keyboards). Double-click the tab to rename it North, South or East. If you'd rather start from a copy with the same headers and column widths, see how to copy a sheet in Excel.

Method 1: Split data into sheets with FILTER formulas#

FILTER returns every row that meets a condition, so one formula per sheet gives you a live split. On the North sheet, type the headers in row 1, then in A2:

=FILTER(Data!A2:D8,Data!B2:B8="North","No orders")

The result spills three rows: SO-201, SO-204 and SO-206, worth 2,900 in total. Data! tells Excel the ranges are on the Data sheet. The third argument is what to show if no row matches, so an empty region shows "No orders" instead of #CALC!.

A better version reads the region from the sheet itself. Put the region name in a cell, say G1, and use:

=FILTER(Data!$A$2:$D$500,Data!$B$2:$B$500=$G$1,"No orders")

Now you can copy the sheet, change G1 to South, and the formula still works. Making the ranges longer than the data (row 500 here) leaves room for new orders. Better still, format the data as an Excel table so the ranges grow on their own.

FILTER needs Excel 2021 or Microsoft 365. To split by two conditions at once, such as North orders over 1,000, see FILTER with multiple criteria, and the full FILTER function guide covers the rest. For the syntax in brief, see the FILTER reference.

Method 2: Use a PivotTable to create one sheet per item#

A PivotTable can create a separate sheet for every item in a field with one command. It works best when each sheet should show totals rather than every row.

1. Click inside the data and choose Insert > PivotTable, then OK.
2. Drag Region to the Filters area.
3. Drag Rep to Rows and Amount to Values.
4. On the PivotTable Analyze tab, click the small arrow next to Options and choose Show Report Filter Pages.
5. Pick Region and click OK.

Excel adds a sheet named North, one named South and one named East, each with a PivotTable filtered to that region. When the data changes, refresh with Data > Refresh All (Windows: Ctrl+Alt+F5).

If you need the raw rows, double-click a PivotTable's grand total. Excel creates another new sheet listing every row behind that number. If PivotTables are new to you, start with the beginner's guide to PivotTables.

Method 3: Filter and copy each group by hand#

For a quick one-time split, or in Excel 2019 and earlier, use the filter buttons and copy:

1. Click in the data and turn on filters with Ctrl+Shift+L (Mac: ⌘+Shift+F).
2. Open the Region filter and tick only North.
3. Select the visible rows with the headers and copy them (Ctrl+C, Mac: ⌘C). Excel copies only the visible rows.
4. Paste into a new sheet named North.
5. Repeat for each region, then clear the filter.

The copies are fixed values. If an order changes on the Data sheet, the North sheet won't change. That's fine for a report you send once, but not for a file people keep using.

Tips that keep split sheets reliable#

A split works best when the master table stays the single source of truth.

• Edit only the master data. Make changes on the Data sheet, never on the split sheets. FILTER results can't be typed over anyway: you'd get #SPILL!.
• Watch the spelling. "North " with a trailing space is a different group. Clean the column with TRIM before you split.
• Quote sheet names with spaces. A sheet called North Sales is written 'North Sales'!A1 in formulas.
• Check the totals. The sheet totals should add up to the master total. Here, North 2,900 + South 2,350 + East 1,740 = 6,990, the same as the Data sheet.
• Going the other way? To stack many sheets back into one list, see how to combine sheets in Excel.

For very large or repeated splits, a macro (VBA) or Power Query can automate the whole job, but the three methods above cover most everyday needs without code.

Practise it#

Try the exercise on this page to check how many rows a region's sheet will get. Then practise FILTER in a live spreadsheet, learn report filters in the PivotTables 2 lesson, and get comfortable with Data! references in the worksheets and cross-sheet references lesson. Each one checks your formula instantly.