Data Import & Export

Create an Excel Template to Separate Monthly Data by City

Adam DavisAdam Davis Nov 18, 2026 869 views

Question details

Create a reusable Excel workbook that separates monthly imported records into worksheets by city name or unique identifier.

Create an Excel Template to Separate Monthly Data by City
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.
Before you start

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.

Solution 1Recommended

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.

1
Import the monthly records

Place the monthly data on the Main worksheet, keeping the same headers and city column each month.

2
Create a city worksheet

Add a worksheet for a city, enter its exact name in B1, and copy the Main worksheet headers into A2:D2.

3
Filter matching records

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.

4
Repeat for other cities

Copy the city worksheet for each remaining city and change B1 to the corresponding city name or identifier.

5
Save the reusable layout

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 FILTER to populate each city worksheet
Check the source range: The formulas only include rows within the specified range. Expand both ranges if an import exceeds row 1000. FILTER requires a supported Excel version, such as Excel for Microsoft 365 or Excel 2021 and later.
Separate monthly records in WPS

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. 1. Open the workbook: Open WPS Spreadsheet and choose File > Open to select your Excel workbook.
  2. 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. 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. 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.
Filter monthly records by city or unique identifierOpen and save Microsoft Excel XLSX workbooksOrganize city records on separate worksheetsUse a familiar spreadsheet interface for manual data separation
microsoft office alternative - wps office

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.