How To Use Power Pivot Instead of VLOOKUP
In this post I will explain how you can use Power Pivot instead of VLOOKUP to save time and analyze your data in new ways.
What is PowerPivot?
Power Pivot is a free business intelligence tool from Microsoft that allows you to process and analyze large data sets in Excel. It has a ton of great features, and here are some of the highlights of Power Pivot.
- Work with large data sets (over 1 million rows) right in Excel.
- Quickly create relationships between data tables, similar to a database.
- Analyze your data with Pivot Tables in Excel, an environment you are familiar with.
- Create advanced formulas and measures to analyze data like never before.
- Link Excel files directly to your databases and data sources to quickly refresh your reports.
- Go beyond Pivot Tables and retrieve data with the CUBE functions, great for custom reports, charts, and dashboards.
There are many more features of Power Pivot, and this list is just meant to give you an idea of what it can do.
Why Do I Need Power Pivot?
You might be thinking… “All those features you listed sound great, but do I really need Power Pivot? It looks like a lot to learn and I’m not sure how it will benefit me.”
So I’m going to give an example of how you can use PowerPivot instead of VLOOKUP, and let you be the judge. This example only scratches the surface of Power Pivot’s capabilities. But you will see how Power Pivot makes it much faster and easier to create and maintain relationships between data sets in your Excel files.
The good news is that Power Pivot is a free add-in for Excel 2010 and 2013 for Windows. So there is no risk in trying it out.
A Common Reporting Process
Does the following process look familiar to you?
This is a common process we go through to summarize and analyze a data set:
- Copy or import a set of data into Excel. In this example we are going to use a set of sales data.
- Add additional columns to the data set to pull in additional information, usually with VLOOKUPS.
- Summarize the data with a Pivot Table or formulas (SUMIF, COUNTIF) for reporting.
If you do this process regularly, then I think you’re going to like Power Pivot.
The VLOOKUP Approach
In this example we have a set of sales data that we need to do produce some reports for.
The data table does not contain the Product Category information, so we need to lookup the category from a Category table using VLOOKUP formulas. Then we will be able to create a report using a Pivot Table that shows the sales by Category.
With the VLOOKUP formula we are basically creating a relationship between the data table and lookup tables. The question then becomes, how much is this relationship costing you?
Do You Have a Love-Hate Relationship with VLOOKUP?
There is nothing wrong with the VLOOKUP approach, but this relationship does require maintenance. Here are a few examples of things you might find yourself doing with the VLOOKUPs:
- Extending the lookup range argument (table_array) when you add more rows to your lookup tables. (Note: Excel Tables and structured reference formulas resolve a lot of these issues and I highly recommend learning about Tables)
- Adding an additional column to lookup table means the column index argument needs to be updated.
- Copying VLOOKUP formulas down to extend to the end of the data set after updating with new data.
I could probably list 10 more issues we encounter with VLOOKUP maintenance, but this post isn’t about complaining. It’s about solving the problem. 🙂
VLOOKUPs Require Maintenance
I like to think of the VLOOKUP as that old green truck. It’s usually reliable, and you’ve depended on it for years, but it does break down from time-to-time. You either know it so well that you keep a checklist of potential problems (along with your toolbox) in the back of the truck, just in case something goes wrong (errors). Or, you have to take it to the mechanic (the Excel expert in your office) because it broke and you’re not sure how to fix it.
Again, there is nothing wrong with the VLOOKUP approach. It can be a love-hate relationship, but VLOOKUP will typically get the job done.
Power Pivot to the Rescue
Here’s an example of how quickly you can create the same relationships in Power Pivot.
Click here to see the screencast animation in your browser.
Power Pivot allows you to create relationships between tables with a simple drag-and-drop. This means there is NO formula to maintain here, and it is much faster and easier to build relationships between your tables.
In the screencast above, I am basically doing the same thing as creating a VLOOKUP formula. But all I have to do is drag-n-drop the field names between tables, and the relationship is created.
This is not to say that Power Pivot is completely maintenance free, but the relationships between tables are automatically updated when rows are added/deleted from the tables. You can also insert/delete columns to the tables and the relationships will remain intact.
The PowerPivot Pivot Table
Once we create the relationships between the Data table and lookup tables, we can then summarize the data with a Pivot Table.
The image above is an example of a Pivot Table that uses a PowerPivot data model as it’s source. As you can see, it looks very similar to a normal Pivot Table. This makes it easier to understand and work with.
Power Pivot Brings a Whole New World (Inside Excel)
One of the major benefits of the Power Pivot Pivot Table is that you can write Measures (DAX Formulas) to further analyze your data. Some examples of these measures could include: daily average sales, year-over-year sales growth, sales vs industry metrics, and much more. The possibilities are seemingly endless, and it’s really only limited by your imagination.
I like to think of Power Pivot as that sleek new sports car. It’s fast, powerful, and fun. Once you learn how to drive it, you’ll begin to realize what it’s really capable of. It will take you to new places that you only dreamed of before.
I am NOT advocating that you should ditch the VLOOKUP method at all. It is still very useful, and one that every analyst should know. There are times when the VLOOKUP will suit your project just fine.
However, if you are constantly updating data sets with copy/paste, maintaining formulas, or finding yourself at the limits of your pivot tables, then Power Pivot is a great option. This new technology will really take your work, and career, to the next level.
How Do I Learn PowerPivot?
Just like Excel, Power Pivot is not something you are going to master overnight. Personally, I have read books, taken courses, and done a ton of Google searches to learn Power Pivot. I will definitely be writing more in-depth articles in the future.
Fortunately there are some great resources available that are making Power Pivot easier to learn. I’m excited to share that Mynda Treacy at MyOnlineTrainingHub has a brand new Power Pivot Course. She is also offering a new course on Pivot Tables if you want to learn the basics first.
For a limited time I am offering a huge bonus when you sign-up for any of her courses through me. You will receive a package of free gifts that is valued at over $70. This includes my new Tab Hound add-in, a book on Excel Tables, and more.
Click here learn more about this incredible deal.
This offer is also available for Mynda’s highly acclaimed Dashboard Course.
What Do You Think?
Could Power Pivot help you with some of your financial models? Please leave a comment below with any questions. If you have used Power Pivot, please let us know how your journey is going.