Data Import & Export

How to Use Power Automate to Fill Blank Excel Cells from Another Workbook

Elise Williams Sep 30, 2026 871 views

Question details

The user needs to automatically populate blank cells in an Excel table with values from a secondary Excel table using a matching key via a Power Automate flow.

How to Use Power Automate to Fill Blank Excel Cells from Another Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data synchronization and filling missing column values between two Excel workbooks stored on SharePoint.
Observed behavior: One table contains a key and a blank value column, while another table contains the matching key and the required values needed to fill those blanks.

Before you start

Ensure both Excel files are saved in a cloud location like SharePoint or OneDrive for Business, and that the data in both files is formatted as an official Excel Table (Insert > Table) with clear column headers.

Solution 1Recommended

Create a Flow with 'List Rows' and 'Update a Row' Actions

Use this method to automatically match keys between two Excel tables and update the blank cells in your primary workbook.

To accomplish this, you will need to retrieve data from both tables, apply a condition to check for blank cells, filter for the matching key, and update the target row.

Set up the flow trigger

Log into Power Automate and create a new flow. You can use an 'Instant cloud flow' with a manual trigger or a 'Scheduled cloud flow' to run it automatically at set intervals.

Add the 'List rows present in a table' actions

Add two 'List rows present in a table' actions. Connect the first one to your primary Excel workbook (the one with blank cells) and the second one to your secondary workbook (the one with the source data). Select the correct SharePoint site, Document Library, File, and Table name for both.

Apply an 'Apply to each' loop and check for blanks

Add an 'Apply to each' action and use the 'value' output from the first table. Inside this loop, add a 'Condition' to check if the column you want to fill is equal to 'null' (blank).

Filter the secondary table for the matching key

In the 'If yes' branch of the condition, add a 'Filter array' action. Set the 'From' field to the 'value' of the second table. Set the condition to check where the Key Column from the second table is equal to the Key Column from the current item in the first table.

Update the blank row

Add the 'Update a row' action for the primary workbook. Use the Key Column from the first table for the 'Key Value' field to identify the exact row. Finally, map the target blank column to the corresponding dynamic content retrieved from your 'Filter array' results.

Create a Flow with 'List Rows' and 'Update a Row' Actions

OData Filter Query Optimization: For better performance on large datasets, skip the 'Filter array' action and directly use an OData filter query within the second 'List rows present in a table' action, formatting it like: KeyColumnName eq 'DynamicKey'.

WPS Spreadsheet Solution

Fill Blank Cells Across Workbooks using VLOOKUP in WPS Office

If you do not strictly require automated cloud flows, you can instantly populate blank cells from another workbook using the built-in VLOOKUP or XLOOKUP functions in WPS Spreadsheet. It provides a fast, hassle-free way to merge data.

1. Open both files in WPS Spreadsheet: Launch WPS Office and open both the primary workbook containing the blank cells and the secondary workbook containing your source data.
2. Start the VLOOKUP formula: Select the first blank cell in your primary table. Type '=VLOOKUP(' and click the cell containing the matching key in that same row.
3. Select the source data array: Type a comma, then switch to the secondary workbook. Highlight the entire data range containing both the keys and the values, and press F4 to lock the reference.
4. Specify the column index and match type: Type a comma, enter the column number containing the target value, type ', FALSE)' to ensure an exact match, and press Enter.
5. Apply the formula to all blank cells: Double-click the small square fill handle at the bottom-right corner of the selected cell to copy the formula down, instantly filling the remaining blank cells.

Instantly match keys and pull data without setting up complex automation tools100% compatible with Microsoft Excel file formats and advanced formulasFree, lightweight, and fast alternative to Microsoft OfficeUser-friendly interface with robust built-in data processing features

microsoft office alternative - wps office

Frequently Asked Questions

Why is Power Automate failing to find my Excel table on SharePoint?

Power Automate can only read data that is formally defined as an Excel Table. Open your Excel file in SharePoint, highlight your data range, navigate to the Insert tab, and click 'Table'. Name the table appropriately so the flow can recognize it.

What is an OData filter query and why should I use it?

An OData filter query filters records on the server before they are downloaded to your flow. Instead of downloading thousands of rows to check for a match using a 'Filter array' action, an OData query retrieves only the exact row you need, making your flow significantly faster and less prone to timeout errors.

How can I make this flow run automatically without manual triggers?

You can replace the manual trigger with a 'Recurrence' schedule trigger. This allows you to configure the flow to run automatically at specific intervals, such as once every day or every hour, ensuring your blank cells are populated continuously in the background.

Excel & Data Problems