CODEWITHSUNDEEP
excelchartsvba
Woody_1983
Woody_1983

Reputation: 159

stacked bar chart custom format

I have a stacked bar chart in excel. This is made up of positive and negative comments. I can get the negative topic names down one side of the axis but can't work out how to get the positive down the right hand side of the chart?

From the example attached I want the axis titles on both the left and right of the graph? I've been stuck on this for a few days and have researched this.

enter image description here

Thank you in advance for any help!

Upvotes: 0

Views: 897

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6073

Alternative that may be more robust because it doesn't mess with primary and secondary axes...

Here is the data, with bad label captions, bad percentages, good percentages, and good label captions. I've inserted a column of zero values between good ercentages and good label captions.

I selected A1:D5, that is, the whole data range excluding the good label captions, and then inserted a stacked bar chart, shown below the table. I've reversed the direction of the categories on the vertical axis and changed where the horizontal axis crosses to the maximum category, so the items are in the same order as in the worksheet. I also deleted the legend and adjusted the plot area to provide room in the right margin of the chart for the labels.

I selected the "blank" series (by clicking the green series, then clicking the up arrow key), and added data labels, shown in the second chart.

I formatted these data labels in the third chart so that they are in the Inside Base position, which moves them outside the plot area.

In the fourth chart I used the Values from Cells option to the data labels, and selected the range of labels in column E. This isn't available before Excel 2013, but you can change the labels in a number of ways:

  1. Select each label individually (click once to select all labels, click again after a slight pause to select each specific label), double click on the text of the label, and overtype it with the text you want to show.

  2. Select each label individually (click once to select all labels, click again after a slight pause to select each specific label). Don't go into edit mode: you don't want the cursor flashing in the label. Type = in the formula bar, then click in the cell with the desired label. This is a link to the cell, so changing the cell contents will change the displayed text in the label accordingly.

  3. Use Rob Bovey's Chart Labeler, a free Excel add-in from AppsPro.com.

enter image description here

Upvotes: 0

Rory
Rory

Reputation: 34075

What you can do is:

  1. Add a new series of data plotting a value of 0 for each category, and using your "good" labels for the category axis data.
  2. Format the new series to plot on a secondary axis
  3. Add a secondary vertical axis, and format it to display the categories in reverse order

Demo file here: http://1drv.ms/1xrnjBg

Upvotes: 1

Related Questions