Budgeting is a long and tedious process which involves collating the numbers from the cost and profit centres. The conventional way of collating the numbers is by putting each submission into one worksheet per centre and using a summary sheet to add up the same cell from multiple worksheets. This way of collating the budget is not only tedious. It also sacrifices the details required for analysis. You cannot pull out any budget line item and identify the cost centres which have budgeted for
this line item with Pivot Table. This is no fault of Pivot Table. It is because the data in the budgeting template is organized in the wrong way and is not suitable for pivoting.
With Excel 2016, the above problem no longer exists. Using the new Get and Transform function, you can pull the data from each cost centre, clean up the empty rows, remove all the unnecessary formatting and prepare the budget numbers in a Pivot Table friendly layout for analysis. This can all be done with a one time setup which takes about a few minutes to setup (with practice). With the user friendly layout, you can combine all the cost centres’ data into one huge Excel database. This Excel database can then be used to create a Pivot Table and allow you to analyse the budgets by cost centre and budget line items.
In the video below, I have made use of the following functions
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft