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

  1. create a query for each Excel Table
  2. create a final query to append (combine) them
  3. 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 adventureGet & 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!

I’m a Data Analyst at McKinsey & Company.

Check out my YouTube videos and my blog posts.

Post a comment