How to Categorize Locations by Province and Assign Codes in WPS Office Spreadsheets
Question details
The user needs to organize a list of national parks and landmarks by adding a Province column, filtering by province, and assigning unique province-based visitor codes such as EC001–EC100.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing a location list of national parks and landmarks and needs to group them by province for filtered code assignment.
- Observed behavior
- The list is not yet organized by province, and visitor codes are not assigned by province. The goal is to filter locations by province and generate codes such as EC001–EC100.
Confirm your location list includes a column for site names and enough empty columns for Province and Visitor Code. If you plan to use SharePoint later, keep the Province column name consistent between the spreadsheet and the SharePoint list.
Add a Province Column and Assign Visitor Codes Using AutoFilter
Use this method when you need to organize a location list by province and create unique province-based visitor codes without restructuring the whole workbook.
This manual method is ideal for small to medium location lists. It uses a dedicated Province column and AutoFilter so you can work with only the selected province at a time, reducing the chance of assigning a code to the wrong row.
In WPS Office Spreadsheets, click Menu > Open and select the workbook that contains the national parks and landmarks list.
Right-click the column header next to the location names, choose Insert, and type Province in the new header cell.
In the Province column, type the correct province for each location, such as Eastern Cape. Use copy and paste or the fill handle for repeated province names.
Select the data range, go to the Home tab, click Filter, then click the Province filter arrow and choose the desired province, such as Eastern Cape.
In the visitor code column, enter EC001, EC002, EC003, and so on. You can type EC001 and drag the fill handle down to continue the series through EC100.
Apply the Same Province Filter in a SharePoint List
Use this alternative when the location list is stored in SharePoint and you want to filter the list view by Province before assigning visitor codes.
Use WPS Office Spreadsheets to Categorize Locations by Province and Assign Visitor Codes
Open your Excel-compatible worksheet in WPS Office Spreadsheets, add a Province column, use AutoFilter to isolate each province, and fill province-based visitor codes without paying for Microsoft Excel.
- 1. Download and install WPS Office: Click Download WPS Office Free, install the application, and launch WPS Office Spreadsheets.
- 2. Open your Excel workbook: In WPS Office Spreadsheets, click Menu > Open and select the location list file you need to organize.
- 3. Add and fill the Province column: Right-click a column header, choose Insert, type Province, and enter the correct province for each location.
- 4. Filter and assign visitor codes: Select the data range, click Filter on the Home tab, choose a province from the Province filter arrow, and enter EC001 through EC100 in the visitor code column.
Frequently Asked Questions
Can I automatically assign province-based codes instead of typing them manually?
Yes. Type the first code such as EC001, then drag the fill handle down to create EC002, EC003, and so on. For more control, you can use a formula like ="EC"&TEXT(ROW()-1,"000") after filtering, but copy and paste the results as values before removing the filter.
How do I repeat the process for each province without mixing the codes?
Clear or change the Province filter after finishing one province, then select the next province from the filter arrow and fill the next code series. Always work with one province visible at a time to avoid assigning codes to hidden rows.
Does this method work when the file is opened in Microsoft Excel later?
Yes. WPS Office Spreadsheets saves .xlsx files that Microsoft Excel can open. The Province column, filters, and visitor codes remain intact when you switch between WPS Office and Excel.
Can I use this approach in SharePoint instead of Excel?
Yes. Add a Province column to the SharePoint list, apply a list-view filter on Province, and enter the visitor codes in the visible items. Repeat the filter process for each province.
What is the quickest way to enter the same province for many locations?
Type the province in the first cell, then use the fill handle or copy the cell and paste it into the remaining cells. You can also use AutoFill to quickly fill repeated province names.
