Formula to Split Excel Data into Multiple Excel Tabs (No VBA)

By Chris Newman •  Updated: 01/20/24 •  7 min read
Split Data Into Sheets Using Excel Formulas Only and Dynamically

Sending Data To Different Sheets Based On Column Value

Often financial business partners are faced with gathering data centrally (consolidating) and then distributing that data to various teams in a segmented fashion. This can be very time-consuming, especially if the process requires splitting up Excel data on a monthly or weekly basis.

An example might be that you gather compensation data for functions within your organization from your payroll database, but need to send each functional leader their own team's pay data.

Split Excel Data to Individual Tabs

Built-in Excel tools like Power Query are great for consolidating data from various sources, however, Power Query has no current functionality to dynamically "explode out" column values into multiple tables.

In this article, I will show you how to use Excel's Dynamic Array functions to easily dice up your raw data and pull the respective pieces into individual tabs with your spreadsheet workbook.

Setting Up Your Data In A Table Object

To make our solution as dynamic as possible, we will want to first store the spreadsheet data in a Table Object.

To do this, either select your data and use the keyboard shortcut Ctrl + t or go to your Insert tab and select the Table button.

After you have placed your data inside an Excel Table, you should see the format change and a colored border will outline the range included in the Table.

Renaming Your Table Object

To keep organized and increase the ease of reading my Excel formulas, I like to give my Excel Tables meaningful names.

To do this, simply

  1. Select a cell within your Table
  2. Navigate to the Table Design tab in Excel's Ribbon menu
  3. In the Table Name field (far left) enter a meaningful name with no spaces (I like to start my tables with the prefix "TBL_")
  4. Hit your Enter key on the keyboard to execute the change
Rename Excel Table Object

List Out The Sheets You Need

As a check to ensure you create enough tabs in your Excel spreadsheet, it might be handy to incorporate a list of all the unique values in your target column. In this article's example, we will be pulling individual data for each Department within the data set.

Off to the side of the table, I am going to use the UNIQUE function to create a dynamic listing of all the departments that are in my dataset.


Create Unique List Excel Formula

Also, it might be wise to incorporate some sort of indicator to catch if a new department gets added in the future. You might want to incorporate a fancy formula to indicate if the department tab has been created, however, if departments are added infrequently, there might be more simplistic ways to go about flagging for new values.

One method I came up with was to just draw a line at the bottom of my list of unique department values. If the list grows beyond my line, I know that a new unique value was added (could even catch spelling errors in the raw data). Obviously, you'll need to manually move your line down as new values are added in the future.

Creating A Dynamic Lookup Table

Creating Dynamic Headers

You can dynamically pull in all your data's heading by utilizing the following formula referencing your Excel Table Object.


Excel Dynamic Array Formula Headers

This formula will automatically expand/contract with your table headings and also pick up any name changes to the headers in the future.

Creating Dynamic Data Ranges With FILTER

You can use the FILTER function to pull in only the data associated with a particular value in a column within your source table. In the below example, the formula is looking in the "Department" table column and only outputting the table rows that have a value of "Finance" within that column.

I recommend utilizing the optional Is_Empty input that the FILTER function offers to account for the occurrence of no results being returned from your source data table. I typically like to output a blank value in these instances using double quotes.

=FILTER(TBL_DATA, TBL_DATA[Department] = "Finance", "")

Instead of manually entering the Sheet Name into the formula (like above), you can incorporate another formula to automatically pull the tab's name into your overall dynamic array formula. Here's the formula that can do that (the Excel file must be saved to your computer before it will work).

=TEXTAFTER(CELL("filename", A1), "]")

The final more dynamic formula looks like this:

=FILTER(TBL_DATA, TBL_DATA[Department] = TEXTAFTER(CELL("filename", A1), "]"), "")

Excel Filter Formula Based on Sheet Name

Rinse And Repeat

After you have created the first tab, make sure to format it. You will likely want to

  • Format your header row
  • Incorporate number formatting rules (make sure you apply to entire column)
  • Integrate row banding (alternating row colors)
  • Adjust column widths

After your formatting is just the way you'd like, all there is left to do is create copies of the tab so that you have one for each of your split values. Make sure to change the tab name to reflect each desired filter value. In this article's example, each tab name represents a department we want to isolate.

Splitting Data Based On Multiple Column Intersections

What if you'd like to have a tab for each combination of data columns? Let's take a look at how we can tweak the original solution to accommodate this sort of task.

In the following example, I would like to create a dedicated tab for each Department + Region combination that exists. I have renamed this Table Object to TBL_REGIONDATA.

Create Unique List Excel Formula Based On Multiple Criteria

To store both pieces of information (Department + Region), I will concatenate them together while also using a delimiting underscore symbol (eg. IT_EMEA, IT_AMER, etc...)

My unique department list formula becomes a little more complicated as I now need to pass multiple rows of data through the TEXTJOIN function to get a nice spill range output:

=UNIQUE(BYROW(TBL_REGIONDATA[[Department]:[Region]], LAMBDA(List, TEXTJOIN("_", 1, List))))

I also added a neat little formula to go alongside the list to evaluate if the sheet name exists in the workbook. You can see in the above screenshot that I have only built out the first 3 tabs so far.

That formula looks like this (with J3# referencing the Dept List spill range formula):

=BYROW(J3#, LAMBDA(List, IF(ISREF(INDIRECT("'"& List &"'!A1")), "✔", "✖")))

Creating Dynamic Data Ranges With FILTER (Multiple Criteria)

For this use case, the formula is going to have some extra parts.

=LET(TabName, TEXTAFTER(CELL("filename",A1),"]"), FILTER(TBL_REGIONDATA, (TBL_REGIONDATA[Department]=TEXTBEFORE(TabName,"_")) * (TBL_REGIONDATA[Region]=TEXTAFTER(TabName,"_")),""))

LET Function - I'm using the LET Function to store the tab name retrieval formula so I don't need to restate it multiple times. In the above formula, I am creating my own variable called "TabName" which stores the current sheet's tab name. This variable is then referenced throughout the remainder of the formula.

TEXTBEFORE/TEXTAFTER Functions - In order to easily extract the two criteria values from my tab name (separated by an underscore), I am utilizing the TEXTBEFORE function to obtain the Department value and the TEXTAFTER function to retrieve the Region value.

FILTER Function - In the filter function, I now need to filter based on only rows that meet both my Department and Region column values. To filter on multiple criteria, you multiply (*) each logical test with one another.

The final output as you create each tab, looks something like this:

Excel Filter Formula Based on Sheet Name Multiple Criteria

Download the Example Spreadsheet

If you need further assistance getting this setup, you are welcome to download the example file I created based on this article. Just click the download button below for instance access.

Download Example Excel File

Additional Articles For Splitting Data

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can use dynamic array functions to create formulas that will automatically split up your data into individual Excel sheets. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.