ThisCalculator

Everyday

Excel Split Table into Multiple Tables

Updated · Standard formula, shown below

To split an Excel table into multiple tables, you can use the built-in Filter feature to copy rows for each category, or use Power Query to dynamically divide data based on column values. This article explains both methods, when to use each, and how to avoid common pitfalls.

Why Split a Table in Excel?

Splitting a large table into smaller ones is useful for reporting, sharing specific subsets with team members, or reducing file complexity. For example, a sales manager might split a yearly sales table into separate tables per region or per quarter. Excel does not have a one-click “split table” button, but the techniques below achieve the same result without losing data.

Method 1: Using Filter and Copy

This is the simplest approach for one-off splits and works in all recent Excel versions.

  1. Select any cell inside your table and go to Data > Filter (or press Ctrl+Shift+L).
  2. Click the dropdown arrow on the column you want to split by (e.g., “Region”).
  3. Uncheck “Select All” and check only one value (e.g., “North”).
  4. Select all visible rows (including headers), copy, and paste into a new sheet or range.
  5. Repeat for each unique value in that column.

This method is manual but quick. For a table with 5 regions, you repeat the steps 5 times. Be careful to copy only visible cells—if you select the whole table, hidden rows may be copied too. To copy only visible cells, press Alt+; after selecting the range.

Method 2: Using Power Query (Get & Transform)

Power Query is a more powerful tool that lets you split a table into multiple tables dynamically. It is available in Excel 2016 and later (including Microsoft 365).

  1. Select your table and go to Data > From Table/Range to open Power Query Editor.
  2. In the editor, right-click the column you want to split by and choose Split Column > By Delimiter if you need to split a single column into multiple columns (e.g., splitting “First Last” into two columns).
  3. To split rows into separate tables, you can use the Group By feature or write a simple M query that filters by each distinct value.
  4. Load each resulting query to a new worksheet by using Close & Load To and selecting “New worksheet” for each.

Power Query is ideal when you need to refresh the split tables automatically when the source data changes. However, it requires learning a bit about queries and may be overkill for a one-time task.

Method 3: Using VBA Macro (For Advanced Users)

If you frequently split tables, a VBA macro can automate the process. A simple macro can loop through unique values in a column, filter the table, and copy each subset to a new sheet. This saves time but requires enabling macros and trusting the code. Always test on a copy of your data first.

How to Choose the Right Method

  • Filter + Copy – best for one-off splits, small tables, or when you don’t need automation.
  • Power Query – best for recurring splits, large data, or when you want the split tables to update automatically.
  • VBA – best when you need to split many tables regularly and are comfortable with macros.

Common Mistakes and How to Avoid Them

  • Copying hidden rows – Always use Alt+; to select only visible cells after filtering.
  • Losing formatting – When pasting, use “Keep Source Formatting” to preserve column widths and styles.
  • Breaking formulas – If your table contains formulas, they may reference cells outside the split range. Check and adjust references after splitting.
  • Not refreshing Power Query – If you use Power Query, remember to refresh the queries after updating the source data.

Alternatives to Splitting

Sometimes you don’t need to physically split a table. Consider using a PivotTable to summarize data by category, or use Excel Tables with slicers to filter interactively without creating separate tables. These alternatives keep your data in one place and reduce duplication.

Frequently Asked Questions

Can I split a table based on multiple columns?

Yes. In the Filter method, you can apply filters on multiple columns before copying. In Power Query, you can add multiple conditions in the filter steps.

Will the split tables update automatically if I change the original data?

Only Power Query will update automatically (after refresh). Filter+Copy and VBA produce static snapshots.

How do I split a table into separate sheets for each unique value?

Use the Filter+Copy method for each unique value and paste to a new sheet. For automation, use VBA or Power Query to load each group to a new worksheet.

What if my table has merged cells?

Merged cells can cause issues with filtering and copying. Unmerge them before splitting, or use Power Query which handles unmerged data better.

Is there a limit to how many rows I can split?

Excel’s row limit is 1,048,576 rows per sheet. As long as your split tables fit within that, there is no specific limit for splitting.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.