Consolidate Data from Multiple Reports Quickly Using Pivot Tables

March 17, 2012 5 Comments by Tom Fang

In a previous Excel tutorial on pivot tables, I showed you how you can easily analyze data when you have one large complicated table. What if, however, you had 2 tables?

As marketers, we work with multiple networks, tracking platforms, and traffic sources. They each generate their own reports. It’s always been a hassle to try to line up multiple reports. For those that are not familiar with Excel formulas, it can be pretty frustrating.

This tutorial will make that task a lot simpler. I show you, using POF and Prosper202 reports, how to quickly join the two reports so you can see the all the performance metrics by ad. This technique, of course, can be applied to any traffic source and, in reality, to any task that involved combing data tables.

Learn to Consolidate Multiple Reports

If you haven’t checked out our previous Excel tutorials, I highly recommend you do so before beginning this one, at least the previous pivot table tutorial. As a marketer, I’ve found Excel to be an instrumental tool for data analysis. It’s a skill that will surely benefit you in the long run.

Without further ado, please enjoy the video!

  • Walter Reade

    I’ve been using Excel for a long time, but I always learn something new with your videos. Thanks!

    • http://www.ipyxel.com/ Tom Fang

      Appreciate it, Walter.

    • http://www.ipyxel.com/ Tom Fang

      Appreciate it, Walter.

  • Mike

    Another good tutorial Tom, one question in the previous pivot table tutorial you used “insert field” but in this one you used “insert item” when you had created another column of calculated values.  What is the difference and when do you use one over the other.

    Thanks
    Mike

    • http://www.ipyxel.com/ Tom Fang

      Thanks Mike!

      Insert Item are essentially the same as Insert Field.  When you use Insert Field, you can use other fields as inputs in your formula.  When you use Insert Item, you can use other items, which are a part of one field, inn your formula.  

      With consolidating multiple tables, for some reason it creates kind of like a giant field, so the stuff inside the field are “items”.

      Basically, they’re the same, and it’s just specific to the multiple consolidation tables that you have to use Insert Items.

      -Tom