Use Excel FILTER to Split Customer Records by Area Code
Question details
Display customer names, addresses, states, and area codes on separate sheets according to 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.
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.
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.
Add a worksheet, copy the headers from Sheet1!A1:D1 to A1:D1, and enter the desired area code in F1.
Select A2 on the new sheet and enter =FILTER(Sheet1!A2:D1000,(Sheet1!D2:D1000=F1)*(Sheet1!D2:D1000<>""),"No matches").
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.
Update customer records on Sheet1. Extend the formula ranges if records are added beyond row 1000.

Filter and copy records when FILTER is unavailable
Use Excel's column filter to create separate, static copies of records in versions without the FILTER function.
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. Open the customer list: Open your Excel workbook in WPS Spreadsheet and select a cell in the customer list.
- 2. Enable the filter: Choose Data > Filter, then open the filter arrow in the area-code header.
- 3. Choose an area code: Select the area code you want to display and apply the filter.
- 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.

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.




