To filter an Excel table in Power Automate, add the Excel Online (Business) action List rows present in a table, select the workbook and table, then enter a condition in its Filter Query field, such as Status eq 'Open'. If you need to filter rows after they have been returned, use the Filter array action instead.
What filtering does in a Power Automate flow
A filter narrows the rows your flow works with. For example, a flow can find open service requests, overdue tasks, or orders above a chosen value, then send an alert or update another system for those records. The key choice is where to apply the condition: in the Excel action’s Filter Query field or later in the flow with Filter array.
These are Power Automate cloud-flow actions, not Excel’s worksheet AutoFilter buttons. A table can have filters displayed in Excel while a flow still reads every row unless the flow action itself has a filter. Conversely, a flow’s Filter Query does not change which rows are visible to someone opening the workbook.
The Excel connector expects the data to be an actual Excel table. A plain range with headings is not enough. In Excel for Microsoft 365 on Windows, select the data and choose Insert > Table, or press Ctrl+T, then confirm that the table has headers. On Mac, use Insert > Table; the keyboard shortcut can differ by keyboard and Excel settings. Save the workbook in OneDrive for Business or a SharePoint document library accessible to the flow.
Set up the Excel action
- In Power Automate, create or edit a cloud flow and add the Excel Online (Business) action List rows present in a table.
- Choose the file location, document library where applicable, workbook, and table. If the workbook or table does not appear, confirm it is saved in a supported location and that the connection account has permission to it.
- Open the action’s advanced parameters if needed, and locate Filter Query. Enter a condition using a table column name and an OData-style comparison.
- Save the flow and run a test using rows that should match and rows that should not. Inspect the action’s output to confirm that the returned records are the intended ones.
Use clear, unique column headings. Simple names such as Status, Region, or Amount are easier to reference than headings containing punctuation or spaces, and are less likely to cause query or maintenance problems. If a table has been renamed or its columns changed, refresh or reselect the table in the action so the flow uses the current structure.
Filter Query syntax and practical examples
Filter Query uses an OData-style condition, not an Excel formula and not a Power Automate expression. Put text values inside single quotes; numeric values are normally unquoted. Use the table’s column name as the field. For example, these conditions illustrate common comparisons:
Status eq 'Open'returns rows whose Status matches Open.Region ne 'West'returns rows whose Region is not West.Amount gt 100returns rows where Amount is greater than 100.Quantity le 10returns rows where Quantity is less than or equal to 10.
The operator is part of the query: eq means equals, ne means not equal, gt and ge mean greater than and greater than or equal to, and lt and le mean less than and less than or equal to. Connector support and behavior can depend on the field’s data type and the Excel connector, so start with a simple comparison and check a test run before building a complicated condition.
For instance, if an order table has a Status column and a numeric Amount column, Status eq 'Open' is a straightforward first filter. A second condition may be possible using OData logical operators, but the connector’s supported query subset can be more limited than a full OData service. If a combined condition fails, test each comparison separately, then consider using Filter array to combine the criteria after retrieval.
Column names with spaces or unusual characters can be troublesome in query fields. Renaming a heading to a plain identifier such as DueDate is often a more reliable fix than guessing how to encode it. Dates also need care: Excel stores dates in a particular representation, while connector outputs may present date-time values differently. Inspect the action output and test the exact comparison rather than assuming a displayed worksheet date is the query’s expected format.
Filter Query or Filter array?
Prefer Filter Query when the condition is supported and you want the Excel action to return only matching rows. It keeps later actions focused on relevant records and can reduce unnecessary data moving through the flow. This is especially useful when a table has many rows, though it does not remove the connector’s own limits or guarantee a particular performance improvement.
Use Filter array when the required condition is not accepted by the Excel action, when you need to combine conditions using flow logic, or when you want to inspect and transform a returned set before proceeding. Add it after List rows present in a table, set its input to the action’s value array, and configure a condition against a property such as Status. The Filter array action evaluates the rows it receives; it cannot recover rows that the Excel action did not retrieve in the first place.
That last point matters when pagination is involved. If List rows returns only an initial portion of a large table, filtering that partial result with Filter array may silently miss a matching row farther down. Configure retrieval first, verify how many rows the Excel action returns, and then decide where filtering belongs.
Using values from a trigger or another action
A flow may need to filter by a value supplied at run time, such as a region from a form response or a customer ID from a previous step. Insert the appropriate dynamic content into the Filter Query field rather than typing a fixed value. The resulting condition still needs to follow the expected syntax and data type: a text value belongs in a quoted string, while a number should be treated as a number.
Test dynamic values that contain apostrophes, spaces, or characters such as ampersands. A value that works when typed directly may behave differently when assembled from user input. For a dynamic query, build and validate the condition carefully, and consider restricting input to expected choices when the flow design permits. Avoid accepting arbitrary query fragments from a user; the flow should construct the condition from a known column and a controlled value.
If the condition is awkward to construct or must handle several optional inputs, retrieving a manageable set and applying Filter array can be easier to maintain. The trade-off is that more rows may be read and processed. Choose based on both the number of records and how complex the condition needs to be.
Pagination, row limits, and large tables
List rows present in a table does not necessarily return every table row in one response by default. The action commonly returns up to 256 rows unless pagination is enabled. To retrieve more, open the action’s settings, enable Pagination, and set an appropriate threshold. The threshold should reflect the expected size of the table rather than being raised without reason.
After enabling pagination, test with a workbook that has more rows than the default response size. Check the run details and confirm that the count and the matching records are as expected. Pagination increases how many records the action can retrieve; it does not make an unsupported Filter Query valid, and it does not guarantee that a very large workbook will be fast or suitable for a flow that processes every row on every run.
For a growing dataset, consider reducing work at the source: use a supported Filter Query, process only new or changed records where the design allows, or move data to a system built for larger-scale querying. Excel is convenient for shared tabular work, but it is not a transactional database. Flow performance, workbook size, connector limits, and the number of simultaneous edits all matter.
Common filter errors and how to troubleshoot them
- The action returns no rows: Check spelling and capitalization in the column name, confirm the value exists, and compare the stored cell value rather than only its visual formatting. A displayed number or date may not be stored in the form you expect.
- The query is rejected: Simplify it to one comparison, verify the operator and quotes, and test a basic text or numeric field. Remove spaces or special characters from the table heading if possible.
- Rows appear to be missing: Check pagination and the number of rows returned before any later filtering. Confirm the file and table selected by the action are the intended ones.
- A new column is unavailable: Save the workbook, confirm the change is inside the table, and refresh or reselect the table in the flow action. A heading typed beside the table may not be part of the table at all.
- The flow behaves inconsistently during edits: Avoid having several processes or people modify the workbook at the same time as the flow reads or writes it. Review run history and workbook permissions, and test again when the file is not being edited.
Run history is the most useful first diagnostic. Open the failed or unexpected run, inspect the inputs and outputs of List rows present in a table, and compare the actual query and returned values with the workbook. That separates a query-syntax problem from a missing-row, permissions, or table-selection problem.
Good practices for reliable Excel filtering
Design the workbook and flow together. Keep one header row, use distinct and stable column names, avoid mixing dates, numbers, and text in the same column, and make sure the table includes all intended records. Use a named Excel table rather than relying on a changing cell range. A consistent data type is particularly important: a column that looks numeric but contains entries such as Unknown may not behave like a purely numeric field.
Keep the condition understandable to the next person who maintains the flow. Use Filter Query for a simple supported condition and add a later Filter array only when it solves a real need. Test boundary cases, including blank cells, values at the comparison threshold, and records that should be excluded. For flows that update rows, also verify that the record-identification step selects exactly one intended row; a correct filter is not a substitute for a reliable unique key.
Frequently Asked Questions
Can Power Automate filter an Excel table without reading every row?
Use Filter Query in List rows present in a table when the condition is supported. It asks the Excel connector for matching records, but the action still has connector behavior and limits. Confirm the result and row count in a test run.
Why does List rows present in a table return only 256 rows?
That is the default response limit commonly encountered with this action. Enable Pagination in the action’s settings and choose a suitable threshold if the flow needs more rows, then test that records beyond the first 256 are included.
Can I use an Excel formula in Filter Query?
No. Filter Query expects an OData-style condition, such as Status eq 'Open'. Use an Excel formula in the workbook for worksheet calculations, or use a Power Automate expression in an appropriate flow field; they are different mechanisms.
Does Filter Query hide rows in the worksheet?
No. It filters which rows the flow action returns. To change worksheet visibility, use Excel’s own table filters or another suitable workbook operation; the flow query itself does not set the workbook’s visible filter state.
Should I use Filter Query or Filter array for multiple conditions?
Use Filter Query if the connector accepts the combined condition and it is easy to maintain. Choose Filter array if the query is unsupported or flow-side logic is clearer. In either case, make sure the Excel action has retrieved the full set of rows the flow needs.
Related
- Excel Number Format Dropdown: What It Does and How to Use It
- Number Bullets in Excel: Add, Format, and Manage Lists
- Notepad to Excel Conversion: Steps and Common Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for All Caps: UPPER, Examples, and TipsTo make text all caps in Excel, use the UPPER function: with the text to convert in A2,…
- Excel IFS Between Two Dates: Formulas and ExamplesTo check whether a date falls between two dates in Excel, put an AND test inside IFS: IFS(AND(date>=start,date<=end),result,TRUE,other_result).…
- How to Filter Dates in Excel by Month, Year, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
- Excel CONCAT Examples: Formulas for Joining TextTo join a first name in A2 and a surname in B2 with a space between them, enter…
- Excel Weekdays Between Dates: Formulas, Holidays, and TipsTo count weekdays between two dates in Excel, use =NETWORKDAYS(A2,B2), with the start date in A2 and the…
- Excel Filter Including Wrong Data? Find the CauseIf an Excel filter includes the wrong data, first check that the filter covers the correct range and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.