Combination Stacked-Clustered Chart in Excel - Align data with horizontal tick marks

Question

I have created a combination bar stacked/clustered and line chart in Excel 2010 on Windows 7 using the technique described at this link:

http://alesandrab.wordpress.com/2013/01/28/create-combination-stacked-clustered-charts-in-excel/

The chart has tick marks and data for every month in 2014 (12 of them) but I cannot align the stacked bars with the horizontal tick marks on the x-axis. January is OK, but the bars for the following months are increasingly short of the monthly tick mark on the x-axis as the year goes on.

I have tried adjusting everything with no success. How do I align the bars with the tick marks?

thanks in advance

Answer 1

Never mind, I can see it.

Format the data series "8" with a line and markers and put in some values, so you can see what's going on.

The data series on the secondary axis has 13 data points.

Reduce the source data to have only 12 data points (and 12 labels) 

=SERIES(,Example!$L$56:$W$56,Example!$I$77:$T$77,8)

Then set the secondary X axis to plot the data "Between tick marks".  Then the chart will look like this:

Now set Series 8 back to no line and no markers.

As a general tip: if you use hidden series, clearly mark out a section in your spreadsheet that identifies what that data is for, so that you can easily find it when you need to make changes. In your case, finding the value area for the Series 8 involved a bit of detective work.  The labels and the values were disjointed and harder to troubleshoot than if they had been in a clearly marked table on the sheet.

Answer 2

You'll need to get a link for that file and post the link here.

Answer 3

Teylyn,

You fixed my problem, thank you for your time and effort!

Champskipper

Answer 4

Champskipper, that's great to know. Please mark the answer, so the question can help others.

Answer 5

OK, I did that, thanks again............................Champskipper