1 / 20

Using PowerPivot instead of VLOOKUP

Blog. Using PowerPivot instead of VLOOKUP. Dr Nitin Paranjape Office System MVP. The Scenario: Two tables. We have two tables We want to analyze data from both tables. Transactions Table. Master tables: Products and Countries. We need this report.

inara
Download Presentation

Using PowerPivot instead of VLOOKUP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Blog Using PowerPivot instead of VLOOKUP Dr Nitin Paranjape Office System MVP

  2. The Scenario: Two tables • We have two tables • We want to analyze data from both tables

  3. Transactions Table

  4. Master tables: Products and Countries

  5. We need this report • Total sales by Country (in columns) and Products (in rows) • Problem: Pivot Table cannot be created on three separate blocks of data. • Solution: We have to combine all three tables into a single table • But how?

  6. Traditional Solution: VLOOKUP

  7. Disadvantages of this approach • File size increases • If there are many transactions, the performance slows down • Workaround?Paste as values to solve the above problems…But it is additional work every time you add more data

  8. Solution: Use PowerPivot • This demo is based upon Excel 2013. • For 2010 • menu names are different • the procedure is also different

  9. Prepare the data • Each block of data must be a Table • Each table must have a legible name • Transactions • Countries • Products

  10. Add tables to Data Model (PowerPivot) • Click inside each table • Open PowerPivot tab • Click Add to Data Model • Repeat this for all three tables

  11. Now all tables are visible in PowerPivot • A separate window opens to show PowerPivot data • Three sheets contain three tables • The link sign indicates these are connected to the Excel Tables

  12. Now we have to create relationships • Relationship means informing PowerPivot about linkages between data tables • It is like a database relationship • The only difference is that you are doing it WITHIN Excel • This eliminates the need to use an external database

  13. Create relationship between Transactions and Countries

  14. Create relationship between Transactions and Countries Choose the table Many table first. Many table contains more than one rows for a single row in master table.

  15. Create relationship betweenTransactions and Products

  16. Create a Pivot Table

  17. Notice that this Pivot Table is different • It shows all Tables

  18. Create the report

  19. Summary • PowerPivot allows you to create relationship between two tables • This eliminates the use of VLOOKUP • PowerPivot can handle millions of rows with very good speed • The file size is also reduced by using PowerPivot • Try this with your data and see the differenceAlways work on a copy of your data to prevent damaging the original file while learning

  20. Thank you Blog • Read my daily bloghttp://efficiency365.wordpress.com • Post your queries on my FB pagehttps://www.facebook.com/groups/117666161675053/ • Follow me on Twitter @drnitinp

More Related