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

Hello,

hard to say without seeing your workbook (you can share it on your OneDrive and post a link here).

Since the labels and tick marks are on the secondary axis, you'll need to make sure that the secondary axis data has the correct number of data points to fit the columns that are plotted on the primary axis. 

To be more helpful I'd need to see your file to see what your settings are.

Answer 3

Thank you for your reply. I am new to OneDrive, but I have tried to post the excel file here:

https://onedrive.live.com/edit.aspx?cid=2F344F7B043572B7&resid=2F344F7B043572B7!107&app=Excel

thanks

Champskipper

Answer 4

Hey Champ .. Unable to access the file with the link you've provided ... Check for security setting of the file to make it available for all public (probably temporarily ...) 

& If you prefer using screen to explain that's a viable alternative

Answer 5

OK, I put the file in my public folder, says everyone can view it.

Champskipper