# Combining 2 pivot tables into 1 chart

#### Ramballah

##### Board Regular
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.
Ram

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### GraH

##### Well-known Member
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€

#### Ramballah

##### Board Regular
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)
Ram

#### sandy666

##### Well-known Member
try Alt, D, P
add both ranges and create single Pivot then Chart

#### sandy666

##### Well-known Member

maybe

but you need to choose what you want to show

#### GraH

##### Well-known Member
try Alt, D, P
add both ranges and create single Pivot then Chart
TRUE, overlooked that one , however when the ranges grow/shrink it can become messy.

#### sandy666

##### Well-known Member

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

#### Ramballah

##### Board Regular
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\$

#### sandy666

##### Well-known Member
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

#### Ramballah

##### Board Regular
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!

Replies
8
Views
79
Replies
3
Views
38
Replies
1
Views
65
Replies
2
Views
105
Replies
0
Views
54