Teams
Forum List
Toggle sidebar

Excel Help - Clustered Stack Column Chart

SCNCY

SCNCY

Well-Known Member
SuperFanatic
SuperFanatic T2
Sep 11, 2009
11,524
9,791
113
38
La Fox, IL
I am having an issue trying to develop a chart that I want in excel. I want to develop a clustered stacked column chart. I want my X-axis to be the months of a year, and for each month there will be two categories (actuals and budget). The actuals and budget will have the same categories within them (salary, overtime, bonus, etc.) that I want to compare each month side by side. Does anyone know how to do this, or show me a link to a source where I can build this in Excel?

Thanks in advance!
 
This felt like a good question for AI, so I copy pasted your post into gemini.google.com and this is what it gave me. I don't know excel, so this might be crap, but i bet you could poke around with it and get it to tell you how to do it with your actual sheet structure


Creating a clustered stacked column chart in Excel is a classic "it should be easier than this" problem. By default, Excel makes you choose between Clustered or Stacked, but it doesn't like doing both simultaneously.
To get those two stacks (Actual vs. Budget) sitting side-by-side for every month, you have to use a specific data layout trick involving "dummy" rows or clever spacing.

Step 1: Structure Your Data​

The secret lies in how you arrange your table. You need to group your categories under the "Actual" and "Budget" headings for each month, but leave a blank row between months to create the "cluster" effect.
MonthTypeSalaryOvertimeBonus
JanActual5000200100
Budget4800150100
FebActual50003000
Budget48001500
Pro Tip: Keep the "Month" cell blank for the Budget row (as shown above). This tells Excel that the Actual and Budget rows both belong to the January group.
Click to expand...

Step 2: Create the Chart​

  1. Highlight your entire data range (including headers).
  2. Go to the Insert tab.
  3. Select Stacked Column Chart (the second option in the Column Chart menu).

Step 3: Refine the Visuals​

Initially, your bars might look a bit thin or spaced out strangely. Here is how to fix the "clumpiness":
  • Remove the Gaps: Right-click any bar and select Format Data Series.
  • Gap Width: Reduce this to around 50%–70%. This makes the Actual and Budget bars wider and closer together within their month.
  • Fix the Legend: If "Actual" and "Budget" aren't appearing correctly, you may need to use Select Data to ensure your series names are pulling from the correct columns/rows.

Why this works​

By putting the Actual and Budget data on separate rows, Excel treats them as distinct data points. Because they are grouped under one month label (with a blank row separating the next month), Excel naturally clusters them together before moving to the next month's gap.
 
SCNCY said:
I am having an issue trying to develop a chart that I want in excel. I want to develop a clustered stacked column chart. I want my X-axis to be the months of a year, and for each month there will be two categories (actuals and budget). The actuals and budget will have the same categories within them (salary, overtime, bonus, etc.) that I want to compare each month side by side. Does anyone know how to do this, or show me a link to a source where I can build this in Excel?

Thanks in advance!
Click to expand...
@cowgirl836 pretty sure this is in your wheelhouse.
 
  • Haha
  • Like
Reactions: cowgirl836 and SCNCY
SCNCY said:
I am having an issue trying to develop a chart that I want in excel. I want to develop a clustered stacked column chart. I want my X-axis to be the months of a year, and for each month there will be two categories (actuals and budget). The actuals and budget will have the same categories within them (salary, overtime, bonus, etc.) that I want to compare each month side by side. Does anyone know how to do this, or show me a link to a source where I can build this in Excel?

Thanks in advance!
Click to expand...
You looking for something like this? Which variable are you trying to compare side by side?
IMG_7545.png
 
SolterraCyclone said:
You looking for something like this? Which variable are you trying to compare side by side?
View attachment 166840
Click to expand...

I figured out how to do it based on one of the other responses. I ws hoping that I was missing something in the excel options.

But to your question, I want the orange and green lines to be on top of the blue ones for both Actual and Budget in each month.
 
  • Like
Reactions: SolterraCyclone
You must log in or register to reply here.

Latest posts

Help Support Us

Become a patron
Top Bottom