Create an Excel Template to Separate Monthly Data by City
Question details
Create a reusable Excel workbook that separates monthly imported records into worksheets by city name or unique identifier.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Monthly records are imported into a main worksheet and need to be allocated to separate city tabs.
- Observed behavior
- Each city worksheet should show only records matching its city name or identifier.
Identify the column containing the city name or unique identifier, and make sure its values are consistent across monthly imports. Check whether your Excel version supports FILTER and UNIQUE; Power Query is another option for recurring imports.
Use FILTER to populate each city worksheet
Use dynamic array formulas to display matching records on each city tab when your Excel version supports FILTER.
This example assumes the source worksheet is named Main, its headers are in A1:D1, its records are in A2:D1000, and column B contains the city name. Adjust the ranges and column to match your workbook.
Place the monthly data on the Main worksheet, keeping the same headers and city column each month.
Add a worksheet for a city, enter its exact name in B1, and copy the Main worksheet headers into A2:D2.
In A3 of the city worksheet, enter =FILTER(Main!A2:D1000,Main!B2:B1000=$B$1,"No matching records"). Leave the cells below and to the right clear so the results can spill.
Copy the city worksheet for each remaining city and change B1 to the corresponding city name or identifier.
Choose File > Save As and select Excel Template (*.xltx). For each month, create a workbook from the template and place the new records on Main.

Use Power Query to refresh separate city tables
Use Power Query for recurring imports when you want repeatable filtering and refreshable output tables.
Filter and copy records manually in Excel
Use this method when dynamic array formulas are unavailable or you need a one-time separation.
Separate City Records with WPS Spreadsheet
Open your Excel workbook in WPS Spreadsheet, filter the imported records by city or identifier, and copy each group to its own worksheet.
- 1. Open the workbook: Open WPS Spreadsheet and choose File > Open to select your Excel workbook.
- 2. Filter the source data: Select the header row on the main worksheet, choose Data > Filter, and select a city or identifier from its column filter.
- 3. Copy records to a city sheet: Copy the visible matching records and paste them into a new or existing worksheet for that city.
- 4. Repeat and save: Change the filter for each remaining city, copy its records to the appropriate sheet, and choose File > Save As to save the workbook.

Frequently Asked Questions
How can I list all the city names in the imported data?
In a supported Excel version, enter =UNIQUE(Main!B2:B1000) in an empty cell to list distinct values from the city column. Adjust the range to match your data.
Will FILTER create a worksheet for every new city?
No. FILTER returns matching rows within an existing worksheet. Create or copy a city worksheet and enter the new city name in its criteria cell.
Why does my city worksheet show no matching records?
Check that the value in the city worksheet's criteria cell matches the source city or identifier and that the FILTER formula references the correct column and rows. Extra spaces or inconsistent names can prevent matches.
How do I update Power Query results after the next import?
Replace the records in the source table while keeping its headers, then choose Data > Refresh All. Each configured city query reloads its filtered results.
Can I use an identifier instead of a city name?
Yes. Point the FILTER condition or Power Query filter to the identifier column, and use the corresponding identifier as the criterion for each worksheet.




