Automatically Populate an Excel Planning Sheet from a Budget Workbook
Question details
Connect a budget workbook to a planning sheet so that shared information can be populated despite different layouts.

- 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.
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.
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.
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.
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.
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.
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.
Save changes to the budget workbook, then select Data > Refresh All in the planning workbook to update the query output and dependent formulas.

Link fixed planning cells directly to the budget workbook
Use direct workbook links for a small, stable range that does not need substantial reshaping.
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.

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.




