## Power Pivot v VLOOKUP, SUMIFS and Rearranging

Introduction

The title of this page is a bit long and a bit obscure. However, all will become clear very quickly.

I am preparing for my seminar to be held in Accra, Ghana, next month, February 2016. I was working on a good example of how to start using Power Pivot. I took the example, worked through it and created my own files.

As you read about and learn Power Pivot you will see that authors tell you that they can replace the sometimes tedious work of VLOOKUP and so on. No one shows you exactly why that might be true, though: that’s the purpose of this page: to discuss what Power Pivot can really do in terms of saving time and effort.

NOTE

If you want to see my full explanation and files, come along to the seminar in Accra! I know, that’s a long way for most people but for others, they will see me and my explanation in action first. You can have my full explanations and files in March and beyond … just write and ask!

Power Pivot

Power Pivot allows us to set up what they call an Excel Data Model in which we can put two or more Excel Tables. We can then use relationships inside Excel to ask a Pivot Table (PT) to call on some or all of those tables as you analyse your data. Here is the raw data that I am using:

For this example, we want to create PTs like this:

Study the data and the PT for a second and you will see that we need to combine both City and Region data in a table so that the PT an create the analysis for us. Power Pivot will allow you to combine the data without copying, pasting, creating VLOOKUP formulas or any such thing. It is a quick and easy process and I will demonstrate that in my seminar.

VLOOKUP, SUMIFS, Rearranging

On the other hand, if you are a spreadsheet dinosaur or have an old version of Excel and just can’t work with Power Pivot, this is what you have to do: click on the image to enlarge it!

You need to set up the third table with this formula in M8, for example =VLOOKUP(L8,\$F\$6:\$G\$17,2,0)

Then you need to create your output table by using a formula like this for example: =SUMIFS(\$J\$8:\$J\$16,\$M\$8:\$M\$16,”=”&\$O8,\$L\$8:\$L\$16,”=”&P\$7)

In order to make these formulas work, you need to switch the columns in the table in the range F5:G17 although there are alternative ways of achieving the same result.

Moreover, one of the features built into this exercise is that there are missing data in the table in the delegates’version … I will provide what’s missing in the seminar or in March!

Once you see the data, the link follows, then add that to my additional VLOOKUP etc insights, everything will become clear for you!

By the way, I will also be presenting this seminar in

• Dubai 27th – 31st March
• Almaty 2nd – 7th May
• Accra again 15th – 19th August

Duncan Williamson