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.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
