• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Pivot Chart question|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Pivot Chart question|Dashboards & Charts|Excel Forum|My Online Training Hub

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsPivot Chart question
sp_PrintTopic sp_TopicIcon
Pivot Chart question
Page: 12Jump to page
Avatar
Michael Denton
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
1
January 11, 2017 - 8:00 am
sp_Permalink sp_Print

Hi,

I have a question about how to create a bar / stacked bar chart in a pivot chart. I'm having problems so i'm sure it has something to do with adding a different series or the way I have my data prepared. I'm not a complete beginner at charts, but don't have a lot of experience either.

Basically I want to compare a quantity of an item that was scheduled to be worked on in the 1st Quarter (Oct - Dec) of 2015 as a single solid bar, and compare that to what was actually worked on. I would like to display what was actually worked on as a stacked bar chart (planned / unplanned) compared to the scheduled solid bar.

I am calling it planned if it was scheduled to be worked on and was actually worked on in that QTR, and unplanned if it was not scheduled to be worked on in that QTR but was worked.

I'd like to figure out the best way to display this data in order to use a pivot chart and slicers.

Basically it would be 2 bars for each QTR in the year. One bar for the plan, and one stacked bar for the execution (planned / unplanned)

I tried to upload an image example, but don't have permissions.ย 

Appreciate any help you can offer!

Mike

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4352
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
January 12, 2017 - 1:37 pm
sp_Permalink sp_Print

Hi Mike,

I haven't tested as it's tricky without your data, but I don't think you can create this as a combo Pivot chart. Plus Pivot charts are buggy when you make lots of changes to their default format in that they tend to lose then changes on refresh.ย 

You're better off creating a regular chart from the PivotTable as described here: https://www.myonlinetraininghu.....ivottables

Kind regards,

Mynda

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
January 13, 2017 - 12:58 pm
sp_Permalink sp_Print

Hi Mike

To further add to Mynda's reply, the combo chart you wanted (a stacked bar next to a normal bar) will require that the chart's data source be arranged in a special way. Pivot Table will not be able to do that.

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
January 16, 2017 - 6:41 pm
sp_Permalink sp_Print

Hi Mike

See if this is what you are looking for.

Although the data is not from a Pivot Table, you will need to ensure that your chart's source data is arranged like this.

Unless you are able to get the Pivot Table to give you this arrangement, I would suggest you use formulas to extract the data and then create a normal chart from it.

Hope this helps.

Sunny

sp_AnswersTopicAnswer
Answers Post
Avatar
Michael Denton
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 5, 2016
sp_UserOfflineSmall Offline
5
January 17, 2017 - 6:02 am
sp_Permalink sp_Print

Mynda / SunnyKow,

Thank you for the replies. Thank you for the detailed example SunnyKow! That is exactly what I was trying to do.ย 

I appreciate the help!

Michael

Avatar
Steve L B
Germany
Member
Members
Level 0
Forum Posts: 7
Member Since:
February 17, 2022
sp_UserOfflineSmall Offline
6
February 18, 2022 - 12:40 am
sp_Permalink sp_Print

Hello!

After five years, maybe a way has been found? What Mike wants is exactly what I need, too. SunnyKow, your solution works, and Iโ€™ve found one that works with filler columns. But neither out of a pivot table. But the chart must come from a pivot table since that is where my data is.

Could Power Query be the answer? But I have no idea!

Have a great day, all.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4352
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
February 18, 2022 - 10:12 am
sp_Permalink sp_Print

Hi Steve,

Welcome to our forum! Power Query won't be the answer. Please share a sample file containing a subset of your data including a PivotTable so we can help you further.

Mynda

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
February 19, 2022 - 1:04 am
sp_Permalink sp_Print

Hi Steve

Frankly I can't recall how I created this chart 5 years ago (sign of old age I guessFrown)

I was able to recreate the chart via a Pivot Table (using Excel 2019) although it is not that elegant.

I guess it will again depend on your data.

Like Mynda said, do attach a sample of your data and pivot table.

It will help us understand your requirements better.

Hope this helps.

Sunny

Avatar
Steve L B
Germany
Member
Members
Level 0
Forum Posts: 7
Member Since:
February 17, 2022
sp_UserOfflineSmall Offline
9
February 26, 2022 - 6:33 pm
sp_Permalink sp_Print

Thank you Mynda, thank you Sunny.

I will try to prepare something to clear up what I need.

This will, however take some time because I am not able to work on that all the time and I may have to "translate" from Excel 2016, German version.

Also, I must go to hospital for a few days next month (nothing very serious, I hope).

Have a great weekend!

Steve

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4352
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
10
February 27, 2022 - 10:42 am
sp_Permalink sp_Print

Hi Steve,

You shouldn't need to translate your file as Excel will automatically convert formulas to whatever language settings we have. It might be helpful to put your column labels in English so it's easier for us to follow.

All the best for a speedy recovery.

Mynda

Avatar
Steve L B
Germany
Member
Members
Level 0
Forum Posts: 7
Member Since:
February 17, 2022
sp_UserOfflineSmall Offline
11
March 17, 2022 - 4:04 am
sp_Permalink sp_Print

Hello Mynda, hello SunnyKow!

I hope I am giving you something you can work with.

In the attachment, โ€œOriginal Dataโ€ is basically what I get from our Software.

Iโ€™ve trimmed it down a lot, there are around 100 Objects and more accounts to each object.

Then I would unpivot it and build a pivot table, โ€œPQ unpivotedโ€ and โ€œpivot tableโ€ respectively.

โ€œchartโ€ is what I would like to achieve. (OK, it could be improved!) As you can see, there is no connection from โ€œchartโ€ to the rest.

And that is my problem! How can I build a chart like that from that pivot table? And also use slicers to change the object shown in the chart. All costs are of course negative, Iโ€™ve multiplied them by -1 so the primary and secondary axis are the same, but is that necessary?

I hope you have enough information. Iโ€™ll try to provide should anything be missing.

Thank you for your help!

Steve

Avatar
Steve L B
Germany
Member
Members
Level 0
Forum Posts: 7
Member Since:
February 17, 2022
sp_UserOfflineSmall Offline
12
March 17, 2022 - 4:08 am
sp_Permalink sp_Print

Sorry, I'll try again

Where is the start button?

Avatar
Steve L B
Germany
Member
Members
Level 0
Forum Posts: 7
Member Since:
February 17, 2022
sp_UserOfflineSmall Offline
13
March 17, 2022 - 4:16 am
sp_Permalink sp_Print

once again, I'm a dunce

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4352
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
14
March 17, 2022 - 9:13 pm
sp_Permalink sp_Print

Hi Steve,

Thanks for sharing your file and example chart. Unfortunately, Pivot Charts are not flexible enough to enable you to build this chart. If you want your chart connected to a PivotTable so you can use Slicers etc. then you can build a regular chart from a PivotTable.

Hope that points you in the right direction.

Mynda

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
15
March 18, 2022 - 1:01 am
sp_Permalink sp_Print

Hi Steve

I managed to create the Pivot Chart and included a Slicer as well (using Excel 2019).

Please note that I have to use a dummy series (using the Profit or Loss values) to create the effect for the gap between the column and stacked chart.

The dummy Profit or Loss column chart color is set to No Fill.

Hope this is what you are looking for.

Regards

Sunny

Avatar
Steve L B
Germany
Member
Members
Level 0
Forum Posts: 7
Member Since:
February 17, 2022
sp_UserOfflineSmall Offline
16
March 18, 2022 - 2:12 am
sp_Permalink sp_Print

Hello Sunny!

At first glance, this seems to be exactly what I wanted!

I opened it in Excel 2016 and the selecting the slicers also changed the chart as should be.

Now I just have to figure out just exactly what you didSmile

I guess the dummy series you used replaces the "Filler" columns in my PVT.

Thank you!

I'll keep you posted.

Steve

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4352
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
17
March 18, 2022 - 10:34 am
sp_Permalink sp_Print sp_EditHistory

Sneaky trick, Sunny ๐Ÿ˜‰

The only problem I see is that the primary and secondary vertical axis aren't to the same scale for Riviera school and Franklin school, so the column heights are misleading. e.g. for Riviera school if you take the sum of Jan's labor, material and overheads it comes to 795. Whereas the total revenue is only 740, yet it's column is higher than the costs column.

You'd have to manually set the axis heights, but this wouldn't be ideal for all schools.

Mynda

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
18
March 18, 2022 - 5:26 pm
sp_Permalink sp_Print

Hi Mynda

The total revenue column (740) is lower than the total overhead (795) on my chart.

Am I missing something?

Sunny

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4352
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
19
March 18, 2022 - 8:07 pm
sp_Permalink sp_Print sp_EditHistory

Interesting...when you display both vertical axes the scale for the two series changes so that the 740 column is higher than the 795 column. I turned the axis labels on so I could check they were plotting data on the same scale. I guess they change to share a common scale when only one or no axes are displayed to avoid this issue, but I've never noticed this before. Probably because I typically plot percentages on one axis and whole numbers on another, so having different scales is the primary reason for using the secondary axis. Anyhow, crisis averted, great job.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1431
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
20
March 19, 2022 - 12:02 am
sp_Permalink sp_Print

Hi Mynda

You are correct. If one of the axis is deleted it will follow the scale of the other axis (common scale).

In this case I deleted the secondary axis first so it uses the primary axis.

I then deleted the primary axis.

Page: 12Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Ivan Kulubya
Guest(s) 47
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1431
Anders Sehlstedt: 845
Velouria: 574
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 213
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 185
Newest Members:
Vicky Otosnika
Abhishek Singh
Kevin Sojourner
Kara Weiss
And Woox
Armani Quenga
moshood bello
annelies b
James1989
lucy gilmour
Forum Stats:
Groups: 3
Forums: 24
Topics: 6045
Posts: 26523

 

Member Stats:
Guest Posters: 49
Members: 31492
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We wonโ€™t spam you.

x