In a perfect world, if you need to make a pivot table, the data is nicely organized in a table, and you can connect to that, quickly and easily.
Unfortunately, as you know, things aren’t always perfect, especially when it comes to data! And sometimes the data is in two or more separate tables, so you need to combine it somehow, before you can build a pivot table..
4 Ways to Combine Data for Pivot Table
There are different ways you can combine data from multiple tables in Excel. For example:
- Power Query
- VSTACK Formula
- Excel Macros
- Pivot Table Wizard
Combine Data Videos
In the sections below, there are a couple of short “Combine Data” videos that I’ve made recently.
- The first video shows how to use the VSTACK function, which is available in Excel 365. It returns multiple ranges in a vertical stack, so it’s easy to combine tables that have identical structures.
- The second video shows how to combine data using the old Pivot Table Wizard. It creates a pivot table with several limitations, but it might do what you need – if you don’t need anything fancy!
For all 4 methods, you can find detailed steps, and sample files, on my Contextures site, on the Pivot Table from Multiple Sheets page .
Video: Create Pivot Table from 2 Tables
Here’s the VSTACK function video, in which I combine the data from tables on 2 separate worksheets. It only takes one cell with a formula, to return all the data from the two tables.
I included the headings for the first table too, because pivot table data needs headings!
Video Timeline
- 00:00 Pivot Table from Multiple Sheets
- 00:20 VSTACK Function
- 00:52 VSTACK Formula
- 01:21 Combined Data
- 01:39 Named Range
- 02:11 Add Pivot Table
Pivot Table Wizard
What if you don’t have Power Query, or the Excel VSTACK function. And you don’t want to use Excel macros?
In that case, you can use the old Pivot Table Wizard to do the job. It’s well hidden in newer versions of Excel, but in the video, I’ll show you how to open it, with an Excel keyboard shortcut.
Video Timeline
- 0:00 Data on 2 Sheets
- 0:24 Open PivotTable Wizard
- 0:50 Select Sheet Ranges
- 1:08 Page Field Settings
- 1:29 Adjust the Pivot Table
- 2:04 Show Sum
- 2:15 Page Field
Get the Sample File
For all 4 methods to combine data, you can find detailed steps, and sample files, on my Contextures site.
Follow this link, to go to the Pivot Table from Multiple Sheets page .
____________________________
4 Ways to Build Pivot Table from Multiple Sheets
____________________________