Power Query Problems

Automatically Populate an Excel Planning Sheet from a Budget Workbook

Elise WilliamsElise Williams Nov 18, 2026 868 views

Question details

Connect a budget workbook to a planning sheet so that shared information can be populated despite different layouts.

Automatically Populate an Excel Planning Sheet from a Budget Workbook
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A structured budget workbook is the source for a presentation-oriented planning sheet with a different arrangement of the same information.
Observed behavior
The desired result is a planning sheet that can be updated from the budget source without manually reentering the data.
Before you start

Identify the budget table, its header row, and the fields that match the planning sheet, such as department, date, and amount. Check that the source values use consistent names and dates so they can be matched reliably.

Solution 1Recommended

Transform the budget data with Excel Power Query

Use this repeatable approach when the source and planning layouts differ or the budget data changes regularly.

1
Import the budget workbook

In the planning workbook, select Data > Get Data > From File > From Workbook. Choose the budget file, select its table or sheet in Navigator, and click Transform Data.

2
Reshape the source

In Power Query Editor, remove unneeded columns and set appropriate data types. Use Transform > Unpivot Columns for values spread across columns, or Transform > Pivot Column when the planning layout needs categories across columns.

3
Load the transformed table

Select Home > Close & Load > Close & Load To, then load the result as a table on a separate worksheet. Select the table and use Table Design > Table Name to name it PlanData.

4
Populate the planning layout

On the planning sheet, reference the loaded table with formulas suited to your layout. For example, if PlanData has Amount, Department, and Date columns, enter =SUMIFS(PlanData[Amount],PlanData[Department],$A2,PlanData[Date],B$1) to total matching records.

5
Refresh after changes

Save changes to the budget workbook, then select Data > Refresh All in the planning workbook to update the query output and dependent formulas.

Transform the budget data with Excel Power Query
Match the planning fields: The example formula requires department names and dates to match exactly. Adjust the criteria or transform the dates if the planning sheet summarizes by month.
Free Microsoft Office alternative

Work with budget and planning spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and continue working with budget and planning data; Excel Power Query connections may require Microsoft Excel.

Review and edit budget and planning sheets in WPS SpreadsheetsOpen and save Microsoft Excel formats, including XLSXUse a familiar interface to make the transition from Microsoft Office easierWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I update the planning sheet after the budget changes?

Save the source budget workbook, open the planning workbook, and select Data > Refresh All. The query output and formulas that reference it can then update.

Can Power Query rearrange budget months into rows?

Yes. In Power Query Editor, select the month columns and use Transform > Unpivot Columns to turn them into attribute and value rows. Rename and set the data types as needed.

Why does my planning formula return zero?

Check that the formula references the correct table and columns, and that its department and date criteria match the source values. An exact date criterion will not match every date in a month.

Should I load the query directly onto the formatted planning sheet?

A separate worksheet is often safer for the query output because refreshed tables can expand. Reference that table from the formatted planning sheet with formulas instead.