+ Reply to Thread
Results 1 to 2 of 2

Issue with creating a clustered column combo graph

  1. #1
    Registered User
    Join Date
    07-10-2017
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    8

    Issue with creating a clustered column combo graph

    Hi there, Iím looking to create a bar graph on 2 axis that has a separate column for 2 different variables, that sit side by side and are grouped by Ďtime periodí.

    For example, Iím looking to produce a graph that shows the sales for apples 2 months pre- and post-pandemic in a column and have the percentage that this value is of the total value of sales of their category (fruit), in a column beside the one for sales.

    So, Iím looking for fruit sales on the left y axis and % of average sales on the right y axis and for these to be grouped by pre- and post-pandemic. I have tried creating a combo graph, however, the only outcome I have managed to produce is a bar and line graph Ė but this doesnít properly represent the values I want to display. When Iíve tried representing both variables as a bar, they sit on top of one another, and Iím wanting them to be side-by-side.

    Screenshot 2021-04-11 at 15.08.26.png

    I've played about with several different chart types, and the closest I've got to what I'm looking for is using a 3d bar graph - the only problem is I've not managed to plot the % values on a secondary axis:

    Screenshot 2021-04-11 at 15.20.27.png

    Can anyone point me in the right direction for creating this as required? If, for whatever reason, this isnít possible, does anyone have any suggestions on how to display these figures in an aesthetically pleasing way that allows a quick and easy understanding of both variables?

    Thanks

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: Issue with creating a clustered column combo graph

    It can be difficult to debug or understand something like this based solely on pictures of charts. I find that a lot (often most) of chart design in Excel is how the data are arranged in the spreadsheet (which a picture of a chart cannot show).

    I don't know if I have understood, but here's a wild guess. Assuming that you want something similar to the 2nd, but in 2D (because 3D is hard to read) and with the orange columns next to the blue columns, but having the orange columns on a secondary axis so they can be scaled to be visible.

    1) As you've already discovered, when you move a clustered column data series to the secondary axis, Excel plots the columns in the same horizontal position -- losing the clustered effect. We need to arrange the data in the spreadsheet so that we can tell Excel where to place each column. I would use an arrangement like this:
    Please Login or Register  to view this content.
    In effect, instead of 2 categories for the horizontal axis, we are creating 4 categories. Then spacing the data for each category in the spreadsheet so that every other category is blank. Sometimes I will add a blank row/category before and/or after each grouping so that, when I format the column chart to have no gap, there is still a gap between groupings.
    2) Select this table and insert a clustered column chart. Then format one of the data series to be on the secondary axis. The orange columns should stay adjacent to the blue columns (instead of behind/in front).

    I'm not sure if that helps, because I'm not sure if I understood what you wanted. In any case, it should illustrate that most things are possible if you figure out the right arrangement in the spreadsheet then build the chart.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a Combo Chart with Clustered and Stacked Data
    By RogerL in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-23-2019, 06:17 PM
  2. Combo chart - Clustered Column & Line for goal
    By suudlej in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-05-2018, 03:03 PM
  3. Replies: 3
    Last Post: 10-29-2015, 10:58 AM
  4. Clustered Column Bar Graph - Change Groups/Pairs
    By DavidIII in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-05-2015, 11:18 AM
  5. Clustered Column Graph Labelling
    By hamhains in forum Excel General
    Replies: 1
    Last Post: 04-05-2011, 04:41 AM
  6. [SOLVED] How do I set up a clustered stacked column graph? Is it possible?
    By BradT in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-17-2005, 11:05 PM
  7. [SOLVED] How to mix a stacked column and a clustered one on same graph?
    By Armelle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-05-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1