Power Query Problems

How to Automatically Add Transfer Entries to Multiple Excel Check Registers

Khadija Khan Oct 13, 2026 869 views

Question details

The user needs to automatically generate a corresponding positive entry in a receiving account's check register when a negative transfer entry is recorded in the sending account.

Automatically Add Transfer Entries to Multiple Excel Check Registers

Product: Excel
Device & OS: not provided
Scenario: Managing multiple check registers where transfers between accounts (like Checking to Savings) need to be synced automatically to avoid manual data entry.
Observed behavior: Manual entry is currently required because basic IF formulas fail to dynamically populate the transferred data and reverse the transaction signs across different worksheets.

Before you start

Ensure your source check register is formatted as an Excel Table, as this allows dynamic updates and seamless integration with Power Query or dynamic array formulas.

Solution 1Recommended

Automate Transfers Using Power Query

Power Query provides a robust, dynamic way to link registers, filter transactions by account, and automatically adjust transaction signs without complex formulas.

By setting up a Power Query connection, you can create a linked register that pulls data from your main checking account, isolates specific transfer entries, and automatically changes negative withdrawal amounts into positive deposit amounts.

Convert Checking Data to a Table

Select any cell in your checking data and press Ctrl+T to convert it into an Excel Table.

Load to Power Query

Keep a cell in the table selected, then navigate to the Data tab and choose 'From Table/Range' to open the Power Query Editor.

Duplicate and Rename the Query

In the Power Query Editor, right-click the query on the left pane, select 'Duplicate', and rename this new query for the receiving account (e.g., Savings).

Filter for the Receiving Account

Click the filter dropdown arrow on the Transferee column and select only the relevant receiving account to isolate those transactions.

Reverse the Amount Sign

Select the Amount column, go to the Transform tab, click 'Standard', select 'Multiply', enter -1 in the prompt, and click OK. This changes negative outgoing transfers into positive incoming deposits.

Load to the Receiving Worksheet

Go to the Home tab, click 'Close & Load To...', select 'Existing Worksheet', and place it in the receiving account's tab.

Refresh Data

Whenever you add new entries to the original checking table, simply right-click the receiving table and click 'Refresh' to update the entries.

Power Query Automation: Once configured, Power Query easily handles thousands of rows seamlessly and requires only a quick click of the Refresh button to sync all your accounts.

Solution 2

Sync Transfers Using the FILTER Function

If you are using a modern version of Excel that supports dynamic arrays, you can use the FILTER function to automatically pull matching transfers.

Manage Check Registers in WPS Spreadsheet

Automate Data Transfers Easily with WPS Office

WPS Spreadsheet supports advanced dynamic array formulas like FILTER, allowing you to easily link check registers and sync transferred amounts between accounts without complex setups. It's a lightweight, fully compatible solution for managing complex financial documents.

1. Open Your Check Register: Launch WPS Spreadsheet and open your existing .xlsx check register file.
2. Apply Dynamic Formulas: Navigate to the receiving account tab and use the built-in FILTER function to instantly query the main checking table.
3. Automatically Reverse Signs: Include the *(-1) multiplier directly within your array formula in WPS to change withdrawals into positive deposits instantly.

Fully compatible with Microsoft Excel formats (.xlsx)Supports advanced dynamic array formulas for automatic data syncingLightweight architecture that runs smoothly on all devicesFree and easy-to-use spreadsheet solution

microsoft office alternative - wps office

Frequently Asked Questions

Why is the transferred amount showing as a negative number in the receiving account?

By default, pulling data from the sending account brings the exact negative withdrawal value. You must multiply the amount column by -1 in your formula or within the Power Query steps to convert it into a positive deposit for the receiving account.

Will the receiving account update automatically when I add new transfers?

If you use a dynamic array formula like FILTER, the data updates instantly. If you choose the Power Query method, you will need to right-click the loaded table in the receiving worksheet and select 'Refresh' to see the newly added transactions.

Can I use standard IF formulas instead of Power Query or FILTER?

While possible, standard IF formulas require you to drag them down multiple rows manually and often leave blank spaces if the condition isn't met. Power Query and dynamic FILTER formulas automatically adjust and spill the exact number of matching records, making them much more efficient for register management.

Why are my dates displaying as 5-digit numbers after the transfer?

Spreadsheet software stores dates as sequential serial numbers. You need to select the date column in the receiving worksheet, right-click, choose 'Format Cells', and apply a standard Date format to render them correctly.

Excel & Data Problems