Excel – How to display series name inside bars of stacked bar chart

chartsmicrosoft excelmicrosoft-excel-2010

I have a very busy stacked bar chart, some 20 or so series over time

I am not interested in the actual numbers, I just want to show the overall trend over time.

The default colours in Excel 2010 are so similar that it makes it almost impossible to pick out the individual series

The only option appears to be to add 'data labels' i.e. the underlying numerical values – this is not what I want

I want to show the series name (a short 3 digit code) which should easily fit inside (or next to) a section of a stacked bar

This also means I will then be able to remove the legend, freeing up more space for the plot area

Best Answer

Assuming your data is set up correctly, you should be able to add accomplish this by:

Add Data Labels
Format Data Labels
- Check Label Contains to Series Name
- Uncheck Label Contains Value
- Check Label Position to Inside End
Repeat for each series
Delete the legend (which is almost always a good practice).

But it sounds like your chart is trying to do too much-in regards to how the reader will be able to comprehend it. Have you considered alternates? For showing trends over time, often Line Charts are better suited than Column Charts. You could also consider resorting the Stacking in your chart (e.g. stack by date, rather than series). You could also consider small multiples/panel charts, or having most series similarly formatted (e.g. gray scale), with areas of interest highlighted in color.

Related Solutions

Mac – Chart Series name referencing cell address Excel 2010 VBA

I checked the Excel Developer Reference in my copy of Excel 2007 and in the documentation for the Series.Name Property it says this:

Remarks

You can reference using R1C1 notation, for example, "=Sheet1!R1C1".

I've tested this and it works for me: I set it to a particular cell, and when I changed the text in that cell, the series title updated automatically.

In your case you should use

ActiveChart.SeriesCollection(1).Name = "='" & ActiveSheet.Name & "'!" & SeriesName.Address(,,xlR1C1)

Excel – How to setup stacked bar chart to graph medication doses

I would suggest a different tack altogether (similar to what Raystafarian suggested) and use an XY Chart.

You can easily create a series for each med, with the X Axis being date/time (since time is continuous, using individual columns doesn't provide a full picture between the columns). The Y Value can be the same (arbitrary) number for each series, while the markers provide the indication of med administration.

Also, since you're trying to track the relationship between pain and the meds, I would add a series for pain and use that for the Y Axis at each med administration (and perhaps a regular interval in-between).

So here's what it could look like (based upon some fictional pain data):

Here's another example with date/time axis and periods of sleep (which will likely affect pain and when meds are administered) if you track it.

Best Answer

Related Solutions

Mac – Chart Series name referencing cell address Excel 2010 VBA

Excel – How to setup stacked bar chart to graph medication doses

Related Question