OmniDocSplit a Pivot Table Into Separate Sheets
Learn how to split a pivot table into separate sheets, one per item, using Excel's built-in option or a manual copy, plus checks to confirm the results.
When you have one pivot table that summarizes a large dataset, you often need to send each part to a different person. A regional manager wants only her region, a department head wants only his team, and a client wants only their account. Copying the pivot by hand for each group is slow and easy to get wrong. The better route is to split the pivot so Excel creates one sheet per item in a single step.
This guide shows how to split a pivot table into multiple sheets in Excel. The main method uses a built-in option that works from the report filter area of the pivot. You will also see two fallback methods, a manual copy and a macro, for cases where the built-in option does not fit. The checks at the end help you confirm that each sheet shows the right numbers.
How do you split a pivot table into multiple sheets? The short answer
Place the field you want to split by in the report filter area, then use the built-in option that shows report filter pages, so Excel creates one sheet per item in that field.
| Your situation | What to do | Why |
|---|---|---|
| Pivot is filtered by one field and you want a sheet per value | Use the built-in option that shows report filter pages | Excel builds each sheet with its own working pivot, filtered to one item |
| You need only the numbers, with no pivot layout | Filter to one item, then copy and paste the values to a new sheet | Pasted values are plain numbers, so they will not change when someone refreshes the pivot |
| You have many items and repeat the job each month | Run a short macro that loops through the items | A macro repeats the same steps without manual errors |
| Your source data changes often | Refresh every pivot after you update the source table | Split sheets keep their own pivot links, so they show old data until refreshed |
Before you start
Check these items first. Each one prevents a problem that is hard to spot after the sheets are created.
- Make sure the source data is a clean table. Give every column a header and remove blank rows and merged cells. Excel builds the pivot from the range it reads, so gaps in the data can drop rows from your totals.
- Choose the split field carefully. Each item in that field becomes its own sheet. A field with three or four items is easy to manage, while a field with several hundred items creates a workbook that is slow to scroll and search.
- Standardize the labels. Entries such as "North" and "North " (with a trailing space) or "NY" and "New York" appear as separate items. Each variant then gets its own sheet, which splits your data into pieces that do not add up.
- Work on a copy of the workbook. Splitting creates many new sheets, and an undo may not reverse every change. Keep the original file untouched so you can start over.
- Plan the sheet names. Excel uses the item names as sheet names, and sheet names are limited to 31 characters. They also cannot contain these characters: \ / ? * [ ] and colons. Long or unusual item names need a cleanup in the source data first.
Splitting a pivot table with the built-in option, step by step
This method works when the field you want to split by can sit in the report filter area of the pivot. The steps below assume your data is already in a clean table.
- Select the source table and create a pivot table. Start from any cell in the data, then insert a pivot table into a new worksheet. This gives you a pivot that reads the full table.
- Set up the rows and values. Drag the fields you want to summarize into the row and value areas. Build the pivot as you would for any report, so the layout is ready before you split it.
- Drag the split field into the report filter area. The report filter sits above the pivot. Only fields placed there can be used to create separate sheets.
- Open the pivot table settings and find the report filter pages option. This built-in option is in the pivot table's options. It asks which filter field to use for the split.
- Select the field and confirm. Excel creates one new sheet for each item in that field. Each sheet holds a copy of the pivot, filtered to that single item.
- Review the new sheets. Click through a few of them to confirm that each one shows the right item in its filter cell and that the layout matches your original pivot.
- Save the workbook as an .xlsx file. Saving in this format keeps the pivots and the sheet structure intact. Once saved, you can share the file or export parts of it to other formats as needed.
When the built-in option does not fit
Two fallback methods cover the cases where you need a different output.
Copy the values by hand. Filter the pivot to one item, select the visible values, and paste them as values onto a new sheet. Repeat for each item. This gives you plain numbers with no pivot link, which works well when you send the file to someone who should not change the structure. The downside is that you must repeat the steps for every item, and the copies will not update when the source data changes.
Use a macro. A macro can loop through each item in the filter field, apply the filter, and copy the results to a new sheet. This suits a report you rebuild every month with many items. Write or record the macro in a copy of the workbook, run it on a small sample first, and check the output before you use it on the real file.
📊 At a glance
| Approach | Sheets stay linked to the pivot | Good fit when |
|---|---|---|
| Built-in report filter pages option | Yes, each sheet holds a working pivot | You want live pivots on each sheet and a moderate number of items |
| Filter and copy values | No, each sheet holds plain values | You are sending numbers only and do not need a pivot layout |
| Macro | Depends on how the macro is written | You repeat the same split often and have many items |
Check the result
Once the sheets exist, check them before you share the file. A quick review catches most problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Sheet count | Compare the number of new sheets with the number of items in the filter list | Missing sheets usually mean hidden items. Show all items in the filter list and repeat the split |
| Totals | Add up the grand total on each sheet and compare it with the original pivot's figure for that item | A mismatch often means the source range was too small or a row was blank. Fix the source table and refresh |
| Filter value | Look at the filter cell at the top of each sheet | A wrong or missing item means the filter was not applied. Delete the sheet and split again |
| Stray items | Scroll through the filter list for blank or misspelled entries | Clean the labels in the source table, then refresh and split again |
| Values or formulas | Click a cell to see whether it holds a formula or a plain number | If you wanted static values but got formulas, copy the cells and paste them as values |
Refresh every pivot after you change the source data. Split sheets do not update on their own, so the totals stay frozen until you refresh each one. If you use the copy or macro method, you need to repeat those steps after each data change.
❓ FAQ
1. Can I split a pivot table if my split field is not in the report filter?
Yes, but you need to move it there first. The built-in option works only with fields in the report filter area. If you prefer not to change the layout, use the manual copy method instead.
2. Why does one of the new sheets show the total for every item?
The filter on that sheet was never applied, so it shows the unfiltered pivot. Check the filter cell at the top of the sheet. If the filter is blank, delete that sheet and split the pivot again.
3. Will the new sheets update when I change the source data?
Not automatically. Refresh each pivot after you update the source table. If you copied the values by hand, you need to repeat the copy steps to update them.
4. What if my field has a very large number of items?
A large number of items creates a large number of sheets, which slows the workbook and makes it hard to navigate. Consider splitting by a broader field, such as region instead of store, or send each group as a separate file.
📝 Summary
To split a pivot table into multiple sheets in Excel, place the split field in the report filter area and use the built-in option that shows report filter pages. Clean the source data first, check the totals on each sheet, and refresh the pivots after any change to the data.
