Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

How To Combine Multiple Pivot Tables Into One Table, Vlookups?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

View Answers     

Similar Excel Tutorials

Complex Structured References (Table Formulas) in Excel
How to use complex structured references, table formulas, in Excel. If you don't already understand how structured ...
Combine Values from Multiple Cells into One Cell in Excel
There are two easy ways to combine values from multiple cells in Excel. In order to do this, we need to do what is ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
Using Formulas with Tables in Excel
Easy way to reference data tables and make formulas within a data table. This allows you to create formulas that a ...

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
- This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t

Similar Topics

I'm having a bit of difficulty with this one- I have four downloads that come in large tables. The columns are similar, but not uniform. i.e. one table will have column B as JE Entry, but the next table does not have this COlumn- Column B is named something else.

My plan was to simply manually make the columns uniform by moving them around to suit my needs and combine all the data, but then I rant past the 65,000 line limit (Excel 2003). So my next shot was to try to create a pivot table from multiple consolidation ranges, but instead of looking at the columns from each table as items to populate my pivot table I got something altogether different.

Does anyone have any suggestions on how I can combine these tables somehow so I can run a pivot table?

How do I combine pivot tables?
I tried using the multiple consolidation ranges discussed here but it didn't work out the way I want it to. My data is as follows: each column is a different year, each row is a different month, and the contents of one table are dollar figures while the other is transactions. There are conveniently the same number of columns and rows in each table in the same order.

Hey all,

I'm working with large sets of data from multiple sheets / columns, and the best way I can find to consolidate it into quantifiable data is by creating a pivot table per column. The problem I have now is that I've got 3 columns per data set; thus, 3 pivot tables.

Is there a way I can create 1 "Master" Pivot Table out of three pivot tables? I want to combine the variables and the tallys into one table.


I have a problem with pivot tables becasue I need to total numbers from multiple pivot tables and it is not allowing me to do this. So basically I need to create a pivot table which takes data from existing pivot tables but not just one table, multiple tables.

The catch is that the headings will all stay the same, but the data is the only thing that needs to change. So if there is a way to create a pivot table that gathers info from multiple pivot tables or if there is any other way such as a formula that could be used to gather this information that would be of benefit to get as well.

Hopefully someone can help me out here that would be awesome!

If you have any questions please reply and ask!

Hello, I current work with three separate pivot tables. Is there a way to combine them all into one pivot table? Some of the data is the same (business sector and practice) while other information is different depending on the pivot table created. Thank you.

I have two separate worksheets and each worksheet has a pivot table. Is there a way to combine the two pivot tables. Basically the area that counts the data need to be combined.

I can do the obvious which is of course copy each for values only and merge them that way but thought there was a way to merge two pivot tables.

Hello All,

I was wondering if it's possible to combine two Pivot Tables in Excel into one Pivot. Ultimately, what I am trying to do is create a Pivot Chart (Line Chart) that displays three different sets of data derived from two fields in my Raw Data, and the only way I can think of going about doing this (without Macros and without losing data) is to create two different Pivot Tables from the same data and then combine the results of those into one Pivot table that the Pivot Chart would be based on (hopefully that made as much sense to you as it did in my head ;-) .

So far, I can't seem to find a way to accomplish this, and will probably need to write a script that reorganizes the data the way I need it, but I figured I would check here first, in case someone else found a non-macro solution.




I've created a pivot table off of data from two other pivot tables. One
pivot table has $millions by product by quarter, and the other pivot table
has volumes by product by category. The first table only has some products,
but the second one has every product. When I create a pivot table with
multiple ranges referencing these two tables and have the values multiply, if
there's no entry in the first table it assumes 1 instead of 0 and returns the
total volume. Is there any way around this, or some other tool in Excel that
would accomplish what I want? Any suggestions would be greatly appreciated.

Thank you!

Hi all. Here is my question,

I have 14 Pivot Tables on this report that I make fairly often...7 tabs, 2
Pivot Tables per tab. Basically, each tab is a region in the US, and 2
different Pivot Tables for different types of inventory. Now, I 'could' just
program a macro to do this in vba fairly easily, however, there has to be a
way to duplicate a Pivot Table easily without having to run through the Pivot
Table Wizard everysingle time...

Is there a way to create multiple Pivot Tables, or copy them quickly?

So I have 1 excel with 8 sheets of data from 8 different years. They have the same column headings on each sheet. I would combine them into one sheet, but excel won't let me (too many rows).

Is there a way to do a pivot table with all the data? That is, create a pivot table from data on multiple sheets? Is there a way to combine all the data into one sheet? I've never used Access so I have no idea what I would do.

What I am trying to do is get a 'count' of a certain attribute for all the years, not just one year.

If not, then is there a way to make 8 pivot tables and then somehow consolidate that information into one pivot graph?



I'm not too sure if this is possible to do in VBA. I currently have multiple pivot tables on a single worksheet. I'd like to be able to have a single filter that filters all the pivot tables, as opposed to separate filters per pivot table. The pivot tables are identical except for the calculation. For example, one is calculating the count and the other the sum (see the attached workbook).

I know I could probably use Slicers to do this. However, I'd like to avoid this as I prefer the filter to drop down.

I've attached an example of my problem. As you can see, there is a filter for each pivot table. I'd like to only have one filter for both pivot tables.

Any help would be appreciated.


Pivot Table Example.xlsx


I'm trying to make pivot tables from an external database: Datatrac, and I'm having a difficult time properly combining multiple tables in query. Any help in explaining how to combine tables, and then once combined how to exit query without the pivot table not containing any data would be appreciated.

Thank you

XL: 2003

Have large dataset beyond 65K rows so forced to use two worsheets. Now have 2 pivot tables and want to combine into one so have one grand total. Tried "multiple consolidation range" but all columns just count and cannot figure out how to make look "normal". I would appreciate any help and thanks in advance.

Hello All,
I have searched the boards and have not been able to locate exactlly what I need. I am hoping that once again I can learn for the best. I apoligize up front if this topic has already been covered. I have 3 pivot tables in the same worksheet. Each pivot table pulls from the same pool of data and is layed out exactly the same. Each pivot table keys off of different row lables. I am utilizing 2 report filter options. What I am trying to accomplish is for 2 of the pivot tables to update to the same report filter options that I enter in the first pivot table. I have forud code that works with all pivot table in all worksheets with one report option. I just want the 3 pivot tables in this one sheet to work together. Any and all help will be greatly appreciated. THANKS!!

Right now I have 3 months of ticketing data (in 3 seperate sheets) arranged like this:

issue type, application
bug, excel
task, powerpoint

I want to make a chart that displays the count of different applications from month to month.

My plan to do this:
Create a pivot table for each month to count the number of tickets per application Make a pivot table to combine the 3 monthly pivot tables

I'm having problems with #2. I know to use the pivot chart wizard to select multiple can get the pivot table to use, and this will work as long as I don't include any whitespace in my final pivot table. If I have whitespace I get a count of 1 for everything.

I don't know how many applications there will be. So I need a way to either only select cells that aren't blank, or a way to get the pivot table to work fine with whitespace.

I have several pivot tables that I use to summarize the data I need and then, currently, I am using vlookups to transfer the pivot table data to reports. This creates error sometimes - becuase the pivot tables can change each time I run the report - so I have to make sure that the vlookup is searching the entire pivot table range.

I think I understand the GetPivotData function, but what confuses me is that my pivot tables have only calculated data. I have attached an example of a pivot I use. I am not sure how to retrieve the data. I need to retrieve the region data for each category and I also need to retrieve the grand total for each category.

I think if this was a basic pivot then I would be able to write a working GetPivotData function - but of course...

Hello all,
I currently have a report that has multiple pivot tables within multiple worksheets. I am trying to upate my pivot tables, and I am receiving an error message informing me pivot tables cannot overlap an existing pivot table. I'm not entirely sure which pivot table is the cause of this error. When searching online for a solution I came across code for VB that would list the pivot tables causing my error. I'm unsure as to how I would go about adding this macro to my report and running it. Here is the code I found:

Please Login or Register  to view this content.

My question is, do I need to replace the word Worksheets with the name of the file I am working on? And would this be the only change I need to make to the coding in order for it to run successfully?

Thanks for the assistance!

Hi All,

I have a four pivot tables and I want to merge all the four pivot tables into one Pivot tables.How can I do this ?As enclosed in attachment,I have a sheet named 'p' where I have formed four pivot tables with the data next to it.However the respective heading of Pivot tables are mentioned in A2,D2,G2 and J2 respectively.I want to merge all these into one.

With Best Rgds

I am trying to write a macro that changes the source data for multiple pivot tables on multiple sheets. here is what I have so far. However it seems to only update 1 Pivot table per worksheet and crashes once it hits a sheet without any pivot table. Any help is appreciated.

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
Next pt
Next ws
End Sub

I am making a button that will print multiple pivot tables with a single click. But the tables will update periodically. So I am looking for a way to 'name' the ranges to which the pivot tables belong in the macro, then print the named range, rinse and repeat.

I think I can do most of it by simply recording the macro... but, Can someone help me in finding out how to set a range to reference a pivot table as a whole, rather than manually select the range to which the pivot table belongs each time?

I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?

I have two issues with some pivot tables.

Firstly, I want to place a pivot table directly underneath another Pivot table. The upper table has grouped items (double-clicking on the group name causes the table to show the grouped items and expand). However, this causes an error because there is a pivot table right below the top table and the expansion of the the top table would cause the two to overlap. Is it possible for the bottom pivot table to be shifted down whenever the top table is expanded, and shifted back up when the top table is minimizes?

Secondly, I want all of my pivot tables to be refreshed (Alt-D-R) whenever I run a macro. The macro already exists and performs other tasks. The refreshing of the pivot tables would just be an additional task for the macro to perform.

Any help would be appreciated.

I am using a pivot table as an efficient way to aggregate text responses from
a large data set. However, the pivot table cuts off the text after the first
255 characters (similar to when you copy a worksheet by using the move/copy

How can I overcome this? I have tried putting the pivot table on the same
sheet as the dataset, but that does not work.

I should also note that these pivot tables are then fed into an automated
report through a complicated set of VLOOKUPs, etc. The pivot table
aggregates several questions and responses from many areas of the datset into
one discrete, defined area.

My aim is to insert multiple pivot tables on one sheet (BELOW) each other for project expense reporting.
All works fine except for the positioning of the second pivot table insertion point as the top pivot table has different number of output rows per project.
Would a macro running each pivot table be the answer- then to establish the last row of data, insert a blank row before running the next pivot table
Your suggestions are appreciated. I am not very well trained in macro's and would like some inputs please.
Thank you