|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine multiple tables into one pivot table in order to do trend analysis
The headline is a bit involved... I have 12 months' worth of expense reports:
12 tables (one table per month) with a list of transactions, ordered by date, category, subcategory & amount. I want to see the total amounts relating to each payment subcategory. I already have a pivot table for each table, summarizing the totals per sub-category, but I want another table that summarizes all 12 transaction tables into one pivot table. I.e. I want one pivot table that shows the total amount spent on each subcategory for each month. I want this in order to do a line graph analysis to see how totals spent on each subcategory has fluctuated over the year. If anyone can give tips for either the consolidated pivot table or the line graph afterwards, or both, I would appreciate it a lot!! |
#2
|
|||
|
|||
Hi
When you insert a pivot table tick the box to add it to the Data Model. In the pivot table field list chose all. This will make all your tables available. You will need to link these tables by a unique (Primary) field. It is like a database with a one to many relationship. Look under the Analyse tab and you will see relationships. This is where you link the fields. Eg. if you have a job number list, the job number is a unique number. Many people will work on any of these jobs so the primary field is the job number in the job number list and the linked job number is in the workers list. I hope this helps. TabH |
#3
|
|||
|
|||
Another possibility:
Union Query Macro -- Sheets in One File Instead of manually setting up a union query, you can use the code in a sample file from Excel MVPs, Kirill Lapin (KL), with amendments by Hector Miguel Orozco Diaz at the below link: http://www.contextures.com/xlPivot08.html (About 5/6 down the page) |
Tags |
line graph, pivot table, trend analysis |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot Table from multiple sheets | hunter1751 | Excel Programming | 0 | 04-01-2017 05:08 AM |
multiple charts in pivot tables | olybobo | Excel | 1 | 05-06-2016 12:26 AM |
Multiple Label Criteria - Pivot Table | philipq | Excel Programming | 4 | 11-27-2014 09:06 AM |
Pivot Table Question- Can the table display in the order the boxes were checked/selected? | blackjack | Excel | 0 | 09-13-2014 08:57 PM |
Break Pivot Into Multiple Tables | Pramalot | Excel | 0 | 05-22-2012 05:21 AM |