An ounce of prevention is worth a pound of cure but how does this apply to Microsoft Excel? In this post I will show you a common example. Too late for prevention? I’ll show you two solutions.
( Disclaimer: as of January 2018 I have enrolled in Power Query Academy. I’m also an affiliate )
Bad Excel Habit
People like to spread out a single data set across several sheets based on a field (i.e. Region). It might be easier to read the data but it’s challenging to use band-aid techniques to analyze it as a single data-set. 3D ranges, indirect function, etc might work but it’s not ideal.
Prevention
It’s much easier to work with a single data-set. This is from Debra Dalgleish’s newsletter:
Last week, someone sent me a question about calculating recipe costs. They had hundreds of recipes to calculate, and planned to make a separate worksheet for each recipe, with combo boxes to select ingredients. Yikes! I said that many sheets with combo boxes would make Excel explode! Never make a separate sheet for each recipe, or region, or anything else you’re keeping track of. Put all the info on one sheet, in a named Excel table, with a column that has the recipe name, or region, or whatever. Then, with everything in one list, you can filter, or sort, or create a pivot table to summarize the data. …Keep it simple — you’ll be happy that you did!
If you really need to dynamically combine data across multiple tables here are two solutions.
Dynamically Consolidating Data
Watch my video to see the difference. It’s a big difference!
Get & Transform (Power Query) is an amazingly tool that few people know about. It’s a built-in feature in Excel 2016. Click ‘Data’ on the ribbon. It’s just a few steps!
- create a query for each Excel Table
- create a final query to append (combine) them
- put the combined data in a new table! Done!
Dynamically combining data from multiple Tables using formulas is CRAZY! You might have a better dynamic formula solution but Get & Transform is definitely easier!
My video explains how to use my formula solution. Download my Excel file to audit my long formulas!
Learn With Me!
Last week I signed up for Power Query Academy ! Join me in this adventure! Get & Transform can do so many amazing things to fix your data and save you time! And it’s a free tool!
About Me
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.
In 2017 I watched several Power Query videos. It’s an amazing tool! This year I’ve enrolled in Power Query Academy!
Check out my YouTube videos and my blog posts.