Best Practice to aggregate data from several tabs in excel
Having data spread over multiple tabs can be quite annoying when you want and aggregated summarized view. You need to make sure the formatting of all the tabs is the same, that the number of rows and column exactly match etc, and then you end up with links between tabs all over the place.
What I am offering in this tool is a solution to this nightmare. Simply import your source tabs (or alternatively import the "aggregation" tab of this best practice into your own excel file), map all your source tabs following the instructions in the video and then indicate the names of the source tabs and drag the formula. That's it, just like that you can have a nice aggregated summarised view of the key elements you want to focus on. Could be Revenue / Gross Profit / Ebitda for 20 different entities. Could the sales and volume of product A, B and C for each of the 30 countries you operate in, could be anything else basically!
I have used this technique a LOT at PwC and that was a game changer and a real time saver.
To make it even easier for you, I have included in the file for download a short VBA macro that allows you to extract all the sheet names in one place. The Macro does not need to be used for this best practice to work but it definitely helps not to have to write down all the tab names by hand!
Hope you enjoy it, please feel free to reach out with any feedback!
