Function Problems

Use Excel FILTER to Split Customer Records by Area Code

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Display customer names, addresses, states, and area codes on separate sheets according to area code.

Use Excel FILTER to Split Customer Records by Area Code
Product
Microsoft Excel
Device & OS
not provided
Scenario
A customer list needs to be viewed on separate worksheets for each area code while keeping the source records easy to maintain.
Observed behavior
Each area-code sheet should show the matching customer records from the consolidated list.
Before you start

Keep the customer records together on a source sheet with one header row and an area-code column. Check that area codes in the source and criteria cells use consistent text or number formats, especially if leading zeros matter.

Solution 1Recommended

Use FILTER to create a dynamic sheet for each area code

Use this method in Excel 2021, Microsoft 365, or later to display matching records that update when the source data changes.

This example assumes the source sheet is named Sheet1, columns A through D contain name, address, state, and area code, and row 1 contains headers. Adjust the sheet name, columns, and range to match your workbook.

1
Prepare an area-code sheet

Add a worksheet, copy the headers from Sheet1!A1:D1 to A1:D1, and enter the desired area code in F1.

2
Enter the FILTER formula

Select A2 on the new sheet and enter =FILTER(Sheet1!A2:D1000,(Sheet1!D2:D1000=F1)*(Sheet1!D2:D1000<>""),"No matches").

3
Create sheets for other codes

Copy the worksheet for each additional area code and change the value in F1 on each copy. Leave the area below and to the right of A2 clear so the results can spill.

4
Maintain the source list

Update customer records on Sheet1. Extend the formula ranges if records are added beyond row 1000.

Use FILTER to create a dynamic sheet for each area code
Keep one source of truth: The FILTER results are dynamic views, not independently editable copies. Edit the original customer record on Sheet1.
Work with customer lists in WPS

Filter customer records by area code in WPS Spreadsheet

Use WPS Spreadsheet's column filter to find customers by area code and copy matching records to separate sheets. The copied sheets are static and must be refreshed manually.

  1. 1. Open the customer list: Open your Excel workbook in WPS Spreadsheet and select a cell in the customer list.
  2. 2. Enable the filter: Choose Data > Filter, then open the filter arrow in the area-code header.
  3. 3. Choose an area code: Select the area code you want to display and apply the filter.
  4. 4. Copy results to a sheet: Copy the visible headers and matching rows, add a worksheet, and paste the records. Repeat the filter and copy process for other area codes.
Filter customer records by area code without changing the source listOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet controls to organize customer dataWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Which versions of Excel support FILTER?

FILTER is available in Excel 2021, Microsoft 365, and later versions. In older versions, use Data > Filter to find and copy matching records.

Why does my FILTER formula return no matches?

Check that the area code in the criteria cell matches the source column's value and format. Area codes stored as text may not match numbers, and leading zeros can affect the result.

Why does Excel show a #SPILL! error?

Clear any cells blocking the formula's output area below and to the right of the FILTER formula. The results need an unobstructed range to expand.

Will the separate sheets update when a customer record changes?

Sheets using the FILTER formula update when values in the referenced source range change. Records copied using Data > Filter are static and must be copied again.