0

I would like to create a two-dimensional column chart in Excel. On the x-axis I would like to plot % of Net Sales, and on the y-axis I would like to plot Gross Margin %. Here's an example:

example

You can see on the x-axis, percentage of Net Sales for the top 10 customers is just over 25%, and the gross margin on those ten customers is just under 30%. Net sales for the next 20 customers compose about 15% and the gross margin on those sales are around 26%

Here is my data:

Customer segment          Revenue   %of Net Sales   Gross Margin %
Customers 1-10          6.96 mill           20.7%            37.1%
Customers 11-30         5.68 mill           16.9%            42.2%
Customers, remaining   20.98 mill           62.4%            47.5%
  • 1
    Select the data, then Insert -> Charts then pick the kind of chart you want. For more details, see this – cybernetic.nomad Oct 15 '18 at 21:19
  • 2
    @cybernetic.nomad the problem as I see it, is the OP wants to vary the width of the bars and have them stack to a total of 100 percent. Last I checked excel bar graphs did not do that. Or at least its not a straight forward option – Forward Ed Oct 16 '18 at 18:26
  • Woah! totally did not see that (and I guess I'm not alone since someone upvoted my comment) What a non-intuitive chart layout. I know of know way to automatically get that from Excel. – cybernetic.nomad Oct 16 '18 at 19:05
  • @cybernetic.nomad convoluted option I posted below but I think it works...your thoughts? – Forward Ed Oct 16 '18 at 20:45
  • @Forward Ed - you, sir, are totally insane in the best possible way. I would just have done a couple of bar charts and redrawn the whole thing in Illustrator. This answer gets one upvote from me for thinking out of the box. – cybernetic.nomad Oct 16 '18 at 20:48
2

Bar Charts in Excel do not vary their width as you are illustrating in your graph to the best of my knowledge. The best I can do and its labour intensive is to outline the bars using an X-Y line scatter plot. you have to create all the points of your bar and then plot them. In my example I plotted them as a single series so it all the same colour, but you could plat it as individual series to get different colour or line pattern to the bar out line. Unfortunately I do not know of a way fill in the bar. (you could technically do it with a bunch of side by side lines).

my approximate solution created the points of interest in column E and F. E being your X axis and F being your Y axis. E is a running total based on your values from column C. F is your Y value starting at 0 for the axis and the your value from D going across repeating and then back down to zero. This results in the graph on the left below. You can add text to your graph using text boxes.

For separate series to get different line colour the same process was followed except I separated the information in to separate columns in H through O. Technically speaking you could have done this all from the info in columns E and F but you would need to be more careful grabbing the information for the start and end of your series data. Separate columns just makes for easier reading.

POC

And here is an example of filling in the column by drawing a line back and forth and only increasing the the Y value by 1/100 of the max column value until the top of the column was reached. Play with the 1/100 increase to find something that suits your needs. In addition I turned the point markers off.

POC FILLIN

The reason you want to play with the 1/100 height step is if you move the chart from being and part of the sheet to being a page on its own, the lines start to show their gap.

POC GAP

  • I thought I was good at Excel, but this is amazing. The person who sent me the screenshot in the example isn't that good at Excel, but it looks like it's from Excel. Anyway, thanks for you reply. Very helpful. – dan Oct 17 '18 at 14:07
  • @dan I am admittedly limited to version 2013. There is a possibility that there is a bar chart option that will allow you to vary the widths across the base in new versions. – Forward Ed Oct 17 '18 at 15:30
  • @Dan looking at your picture, I just noticed the edges of the bars. They seem to be a 3D edge effect. As such I am guess those are manually drawing n objects sitting on top of the graph. – Forward Ed Oct 17 '18 at 15:32
  • @ScottCraner is this a possible bar chart option in 2016? – Forward Ed Oct 17 '18 at 15:35
  • Across my company, I'm pretty sure most of us are using version 2016. On my machine, I am using Microsoft Excel for Office 365. I tried a bunch of chart options, and none of them allow to vary column width. The best I could get is using a scatterplot, but it doesn't fill in easily. The person who sent me this isn't that great at Excel, so it leads me to believe the chart has to be automatic in some way. There's a possibility, they were using PowerBI, but I'm not that well-versed in BI and don't know for sure. – dan Oct 17 '18 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.