Excel 2016 saves you 50% of time wastage in budget consolidation and analysis

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

  • Unpivot Columns
  • Delete Columns
  • Autofill empty rows
  • Transpose
  • Use the first row as headers

New! Comments

Have your say about what you just read! Leave me a comment in the box below.