Function Problems

Use Excel FILTER Instead of Macros to Select Contractors in WPS Office

Muhammad Talha Nov 13, 2026 869 views

Question details

The user needs a formula-based alternative to a fragile contractor-selection macro that returns all rows associated with a selected bidder.

Use Excel FILTER Instead of Macros to Select Contractors in WPS Office

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: Managing bid data in an Excel Table and selecting a contractor in a single cell to display matching rows.
Observed behavior: The existing macro is fragile and does not work reliably in Teams, SharePoint, or Excel for the web.

Before you start

Verify that bid data is stored in an Excel Table with a clear contractor column such as Bidder, and put the selected contractor in an empty cell such as B1 before creating the formula.

Solution 1Recommended

Create a Dynamic FILTER Formula from an Excel Table

Use this when you want a single selection cell to control which contractor rows appear, without macros.

The FILTER function returns every row in the Bids table where the Bidder column equals cell B1. Because the result is a dynamic formula, it refreshes automatically when the selection changes.

This approach avoids macro restrictions and works in WPS Office Spreadsheets and in compatible Microsoft Excel environments, including Teams, SharePoint, and Excel for the web.

Convert the bid range into an Excel Table

Select your bid data in WPS Office Spreadsheets, go to the Insert tab, click Table, confirm the range and header row, then rename the table to Bids in the Table Tools tab.

Enter the contractor selection cell

Type the selected contractor's name into a blank cell such as B1. This cell will act as the filter control.

Insert the FILTER formula

Click an empty result cell, type =FILTER(Bids,Bids[Bidder]=B1,"No matching contractor"), and press Enter.

Test the selection change

Change B1 to another bidder and confirm that the returned rows update automatically.

Create a Dynamic FILTER Formula from an Excel Table

Dynamic formula result: If no contractor matches the selection, the formula returns "No matching contractor" instead of an error.

Solution 2

Return Only Selected Columns with CHOOSECOLS

Use this when the full Bids table has more columns than you need and you only want specific contractor fields in the result.

WPS Office Spreadsheets

Use FILTER in WPS Office Spreadsheets to Select Contractors Without Macros

Open your existing bid workbook in WPS Office Spreadsheets and use the same Excel Table, FILTER, and CHOOSECOLS formulas to return matching contractor rows. WPS Office preserves .xlsx compatibility and familiar spreadsheet tools, so your formulas remain usable in Microsoft Excel, Teams, SharePoint, and Excel for the web.

1. Open the workbook in WPS Office Spreadsheets: Download and install WPS Office, then open the .xlsx file that contains the Bids table.
2. Verify the Excel Table and column names: Click inside the bid data and check the Table Tools tab to confirm the table name is Bids and the relevant column header is Bidder.
3. Enter the FILTER formula: Click the result cell, type =FILTER(Bids,Bids[Bidder]=B1,"No matching contractor"), and press Enter.
4. Confirm automatic updates: Change cell B1 to another contractor and verify that WPS Office recalculates and displays the matching rows without a macro.

Macro-free contractor selection avoids security restrictionsDynamic formula updates whenever the selected bidder changesFull compatibility with .xlsx and Excel Table structured referencesFree, lightweight spreadsheet editing in WPS Office

microsoft office alternative - wps office

Frequently Asked Questions

Does the FILTER function work in WPS Office Spreadsheets?

Yes. Current versions of WPS Office Spreadsheets support the FILTER function and dynamic array formulas in .xlsx workbooks. If FILTER is not recognized, update WPS Office to the latest version.

Why should I use FILTER instead of a macro to select contractors?

FILTER avoids macro security prompts and works more reliably when the workbook is opened in Microsoft Teams, SharePoint, and Excel for the web. It also recalculates automatically whenever the selection cell changes.

What should I do if the FILTER formula returns a #SPILL! error in WPS?

Make sure the result area has enough blank cells below and to the right. Dynamic array formulas cannot spill over existing data, so clear the blocked cells and the formula will return the full list.

Can I return only some columns from the Bids table?

Yes. Wrap the FILTER result in CHOOSECOLS with the column numbers you need, or use a structured table reference such as Bids[[Project]:[Amount]] inside FILTER.

How do I create an Excel Table from existing contractor bid data?

Select the data range, go to the Insert tab, click Table, confirm that My table has headers is selected, and rename the table to Bids in the Table Tools tab.

Excel & Data Problems