Combining 2 pivot tables into 1 chart

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
292
Office Version
  1. 2019
Platform
  1. Windows
Hello everyone,

My question sounds simple but I do not know the answer.
I have 2 pivot tables and 2 charts that go with this. However I want to merge them into only 1 chart.
I have no intention of combining the raw data together because we are resetting the data from both tables weekly.
So what we want is the 2 tables and 1 chart consisting of the 2 tables. Here is my example (sorry its dutch)
Lets call the table on the left table A and the table on the right table B.
The top chart belongs to table A and the bottom chart belongs to table B.
I want to see both table A and B but only have one chart. The design for the chart im not entirely sure what looks best.
Currently im thinking about having table A go upwards in the chart and table B downwards. Or have both tables next to each other so I can see which table is higher.
I hope I didnt confuse anyone and that someone can help me with this.
Thanks in advance,
Ram
1600180316763.png
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
HiRamballah,

Simply combining both pivot charts can't be done. However you can create an intermediate table for the single chart and use a standard chart. Can you share the data from these pivots via XL2BB? (retyping from a picture ain't fun... :))

Some tips for the pivot charts:
- reduce the noise by removing the field buttons
- when the legend has no purpose, delete it
- axis labels, set them to "low" so the labels are not crossing your chart
- dim the grid and label colour
- format the axis in K€
 
Upvote 0
HiRamballah,

Simply combining both pivot charts can't be done. However, you can create an intermediate table for a single chart and use a standard chart. Can you share the data from these pivots via XL2BB? (retyping from a picture ain't fun... :))

Some tips for the pivot charts:
- reduce the noise by removing the field buttons
- when the legend has no purpose, delete it
- axis labels set them to "low" so the labels are not crossing your chart
- dim the grid and label color
- format the axis in K€
Hello,
I am unfamiliar and probably unable to use XL2BB. So I transferred some of our raw data to an excel file and uploaded it (I hope this works for you too).
I left some columns empty due to privacy reasons.
The tabs creditor and debtor are our raw data generated by some big formulas (I only posted the values in this file).
In the end, we want to see a pivot table with the week numbers and total open amount $. But we want one chart matching the debtors and creditors.
Could you perhaps also show me how you did this for future projects?
And feel free to add in the pivot tips you gave me since I'm not entirely sure on what you meant with those (My native language is not English I am sorry)
Thank in advance,
Ram
This is the link for my Excel file uploaded on easyupload
 
Upvote 0
TRUE, overlooked that one :) , however when the ranges grow/shrink it can become messy.
another solution is to use Power Query, transform data into one table then create Pivot Table (and PT chart)
or
use Power Pivot, create relationship then create flat Pivot Table (and PT chart)

neither of these solutions affect the original data
 
Upvote 0
another solution is to use Power Query, transform data into one table then create Pivot Table (and PT chart)
or
use Power Pivot, create relationship then create flat Pivot Table (and PT chart)

neither of these solutions affect the original data
I'd love to try your solutions but I am afraid that I am not skilled enough to understand how to perform these actions.
Also let me clarify, these are basically our invoices and thus new ones get added and some get deleted every week when I refresh the data.
Could you show me how to perform these steps that you mentioned?
I should mention that I want to see the differences in the chart between my two tables. I don't want to sum them up.
If creditor week 40 = 21034$
If debitor week 40 = 42314$
I want to see them both ina chart and not week 40 = 63348$
 
Upvote 0
see this (example)
you can do what you want with Pivot or Chart
if you add or remove any data from source tables just Refresh Pivot Table then you will get "new" data

note: I changed your ranges to Excel Tables which has no effect on the data and is easier to use
 
Upvote 0
see this (example)
you can do what you want with Pivot or Chart
if you add or remove any data from source tables just Refresh Pivot Table then you will get "new" data

note: I changed your ranges to Excel Tables which has no effect on the data and is easier to use
Yes this end result is exactly what I was looking for. Could you explain how you did this? I have more of these charts that I need to make so that be really nice! thanks already!
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top