Combining 2 pivot tables into 1 chart

Ramballah

Board Regular
Joined
Sep 25, 2018
Messages
243
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
 

Some videos you may like

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
Joined
Mar 22, 2020
Messages
603
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Sep 25, 2018
Messages
243
Office Version
  1. 2019
Platform
  1. Windows
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
try Alt, D, P
add both ranges and create single Pivot then Chart
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,757

ADVERTISEMENT

maybe

chart.jpg

but you need to choose what you want to show
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
603
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
6,757

ADVERTISEMENT

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
Joined
Sep 25, 2018
Messages
243
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
6,757
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
Joined
Sep 25, 2018
Messages
243
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,084
Messages
5,526,755
Members
409,717
Latest member
Oscarsalone

This Week's Hot Topics

Top