Harnessing the Power of Excel’s Power Pivot for Multiple Data Sets

Excel is a versatile tool, but when working with multiple data sets, challenges can arise. Let’s delve deeper into these challenges and explore the solutions that Power Pivot offers.

The Challenge with Multiple Data Sets

Imagine working with three distinct sets of data, each stored in separate sheets within a single Excel file. While consolidating all data into one sheet is possible, it’s often more organized to keep them segregated. However, when you attempt to create a pivot table using data from all three sheets, you’ll hit a roadblock. Excel’s default pivot table tool only extracts data from a single, contiguous range, making it inadequate for multiple sheets.

Enter Excel’s data model. This isn’t just another spreadsheet; it’s a dedicated storage area within Excel. By importing multiple data sets into this model, they can be integrated, allowing for more comprehensive pivot tables. Here’s how you can leverage this feature:

  1. Select Your Data: Highlight the data you wish to add to the Power Pivot model.
  2. Activate Power Pivot: Click on the “Power Pivot” tab and choose “Add to Data Model”. Repeat for each data set.

If the Power Pivot tab isn’t visible, you can enable it by:

  • Going to “File” > “Options” > “Add-ins”.
  • In the “Manage” drop-down, select “Com Add-ins” and click “Go”.
  • Check “Microsoft Power Pivot for Excel” and click “OK”.
Adding Power Pivot to Microsoft Excel

Once your data sets are in the Power Pivot model, you can manage them by selecting the “Manage” option under the “Power Pivot” tab.

Option to Manage Data in a Data model
Option to Manage Data in a Data model

To maximize the data model’s potential, establish connections between tables. This is done in the “Diagram View” by dragging columns from one table to another, creating relationships. For example, if two tables both have an “Office” column, link them by dragging one “Office” column to the other.

Establishing connections between tables
Establishing connections between tables

Building a Pivot Table from the Data Model

With interconnected data sets, pivot table creation is a breeze:

  1. Click on a blank Excel cell.
  2. Go to “Insert” > “Pivot table” > “From Data Model”.
  3. Choose your desired location for the pivot table and select “OK”.
  4. Expand the tables in the data model and drag columns to design your pivot table.

When using the Power Pivot data model, be aware that data is stored in both the spreadsheet and the data model, leading to increased file sizes. While it might be tempting to delete spreadsheet data to save space, remember that the data model is read-only. Any updates must be made in the spreadsheet and then refreshed in the data model.

A Streamlined Approach with Power Query

To avoid data duplication, Power Query, integrated into Excel 2016 and later, can be a game-changer. It fetches data from various sources, including external files. For instance, to load data from an external file named “lists” into another Excel file “data model two” without placing it in the spreadsheet:

  1. Go to the “Data” tab and select “Get Data”.
  2. Choose “From File” > “From Excel Workbook”.
  3. Browse and select your source file.
  4. Instead of “Load”, choose “Load To”.
  5. In the dialog box, select “Only Create Connection” and check “Add to the Data Model”.
  6. Click “OK” to transfer the data.
Creating a Data Source Connectiona
Creating a Data Source Connectiona

By harnessing the capabilities of Power Pivot and Power Query, managing and analyzing multiple data sets in Excel becomes a seamless experience.

Written by TheExcelTrainer
Mike Thomas has worked in the IT training business since 1989. He is a subject matter expert in a range of technologies, his primary focus and passion being Microsoft Office (especially Excel) and Power BI. In 2012 Mike founded The Excel Trainer where he has produced nearly 200 written and video-based Excel tutorials. He has recorded several Excel training courses for Pluralsight and in his career delivered thousands of courses and webinars on a wide variety of technology-related topics. Mike is a Fellow of The Learning and Performance Institute and has worked with and for a large number of global and UK-based companies and organizations across a diverse range of sectors. In addition to training, he also designs and develops Microsoft Office-based solutions that automate key business tasks and processes.
Share This Post
Have your say!

Leave a Reply