Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot

CHAPTER 7Creating a Pivot Table from Multiple Spreadsheets

So far, all the pivot tables used in this book have come from one list of data, and in many cases, that is typical. But there will be times when different sheets have similar data or related data, so you will want to create the pivot table from multiple sheets or from multiple ranges within the same sheet. In this chapter, you will explore two different ways to generate a pivot table from more than one list of data. First, you will learn how to make a pivot table from lists that are similar to each other. Then, you will learn how to make a pivot table from lists that have different types of data but share a common field.

NOTE  For the examples presented in this section of the chapter, you can use the sample file called MultipleSheets.xlsx that is included with this book. You can find the sample files at www.wiley.com/go/GGRXL_PivotTables. Each section of this chapter will indicate which sheet to use from this workbook.

Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard

Let's say you have multiple sheets or multiple ranges on the same sheet that are structured in a similar way. Maybe the data on each sheet is a budget for each month, or for each district, for example. You may want to add these sheets together to get totals for a certain time period, for a region, or for something similar. ...