Best Practice to aggregate data from several tabs in excel
Originally published: 18/12/2019 16:56
Last version published: 13/04/2020 09:24
Publication number: ELQ-83054-2
View all versions & Certificate
certified

Best Practice to aggregate data from several tabs in excel

Looking to aggregate the data of multiples tabs into one clean tab? Look no further, this allows you to do just that!

Description
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!

This Best Practice includes
1 excel file + one short VBA code

Pierre-Alexandre HEURTEBIZE offers you this Best Practice for free!

download for free

Add to bookmarks

Discuss

Further information

Save you time aggregating key data, help you drive insight from multiple tabs, help you have a cleaner excel file.

If you have several tabs with consistent info you would like to extract to present an aggregated view

Reviews

  • Be the first to review this Downloadable Best Practice

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Pierre-Alexandre HEURTEBIZE

See all
keyboard_arrow_leftkeyboard_arrow_right

Any questions on Best Practice To Aggregate Data From Several Tabs In Excel?

The user community and author are here to help. Go ahead!


0.0 / 5 (0 votes)

please wait...