Jump to content


Photo

Stacked Clustered Chart

Chart Stacked Clustered

  • Please log in to reply
5 replies to this topic

#1 MikeyMike

MikeyMike

    Member

  • Members
  • PipPip
  • 28 posts

Posted 12 September 2015 - 12:33 AM

Hello,

 

How can I make a clustered chart where the bars are also stacked? Let's say I want to graph the soft candy I ate from 2000 -2005. I want to graph amounts two types of candy eaten every year, one being soft candy, the other being hard candy. Also, say I want the bars split or stacked to represent daytime eating of candy and night time eating of candy. How might I do this?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 852 posts

Posted 12 September 2015 - 10:46 AM

Hi MikeyMike,

 

There are two ways I can think of that you could do this. The first would be to use a standard Stacked Column chart and arrange your data so that each row contains an indicator of the type. You'll then get a chart that looks like this:

 

CandyStackedColumn.png

 

You can see how to create a simple chart like this one in the video lesson: Essential Skills Lesson 5-2 Create a simple chart with two clicks.

 

A more complicated, but possibly more elegant solution would be to create a combination chart that uses a different chart type for Type B. Here's how it looks when using a Stacked Column for Type A and a Stacked Area for Type B:

 

CandyCombination.png

 

You can see a walkthrough of how to create a combination chart like this one in the video lesson: Essential Skills Lesson 5-24 Create a combination chart containing different chart types.

 

I have also attached a copy of my example workbook:

 

Attached File  CandyCharts.xlsx   18.42KB   100 downloads

CandyCharts.png

 

I hope this is what you were looking for, but please feel free to reply if you're having any trouble getting the chart you need. If you'd like me to take a look at your workbook, you can attach it to your reply. You can also find instructions for attaching workbooks by clicking here.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 MikeyMike

MikeyMike

    Member

  • Members
  • PipPip
  • 28 posts

Posted 14 September 2015 - 10:12 AM

Thanks. Going with the first option, how can we make it so that the day/night breakdown is represented by the total bars if you will and the type is represented by the difference in colors on the bars?



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 852 posts

Posted 14 September 2015 - 12:36 PM

Hi MikeyMike,

 

That should just be a matter of rearranging the data as shown in the attached workbook:

 

Attached File  CandyChartsRearranged.xlsx   13.88KB   73 downloads

CandyChartsRearranged.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 MikeyMike

MikeyMike

    Member

  • Members
  • PipPip
  • 28 posts

Posted 14 September 2015 - 09:55 PM

Rearranging the first table basically worked. If my bars extend lower than my horizontal axis and looks weird? How can I fix? I tried changing the minimum, maximum, and etc.



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 852 posts

Posted 15 September 2015 - 08:58 AM

Hi MikeyMike,

 

The most likely reason that the bars extend below the horizontal axis is that you have negative numbers in your data. Having these appear below the horizontal axis is Excel's default behaviour, but you can change this by formatting the vertical axis and changing the Horizontal axis crosses setting:

 

HorizontalAxisCrosses.png

 

You can see a more detailed explanation of how to format an axis in the video lesson: Essential Skills Lesson 5-21 Add gridlines and scale axes.

 

I have also attached another copy of the example workbook with some negative values inserted and the horizontal axis moved so that it crosses the vertical axis at -150.

 

Attached File  CandyChartsNegativeAxis.xlsx   13.89KB   69 downloads

CandyChartsNegativeAxis.png

 

I hope this is the answer you were looking for, but please feel free to reply if you're still having trouble with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






Also tagged with one or more of these keywords: Chart, Stacked, Clustered

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users