Creating Charts - Avoiding autofill of dates, adjusting width, and stopping overlap

[sheranic]

I was at work the other day, coincidentally creating a combo column/line chart on the migration from Office 2010 to 2013, and had some issues creating a chart that looked decent. This was on Win 7 x64 Enterprise with Office 2013 Pro Plus x86.

Example data below

[TABLE="width: 320"]
<colgroup><col width="64" style="width:48pt" span="5"> <tbody>[TR]
[TD="width: 64, bgcolor: #5B9BD5"]Version[/TD]
[TD="width: 64, bgcolor: #DDEBF7"]2010[/TD]
[TD="width: 64, bgcolor: transparent"]2013[/TD]
[TD="width: 64, bgcolor: #DDEBF7"]None[/TD]
[TD="width: 64, bgcolor: transparent"]Total[/TD]
[/TR]
[TR]
[TD="bgcolor: #5B9BD5"]4-Sep[/TD]
[TD="bgcolor: #DDEBF7, align: right"]312[/TD]
[TD="bgcolor: transparent, align: right"]5767[/TD]
[TD="bgcolor: #DDEBF7, align: right"]130[/TD]
[TD="bgcolor: transparent, align: right"]6209[/TD]
[/TR]
[TR]
[TD="bgcolor: #5B9BD5"]11-Sep[/TD]
[TD="bgcolor: #DDEBF7, align: right"]213[/TD]
[TD="bgcolor: transparent, align: right"]5862[/TD]
[TD="bgcolor: #DDEBF7, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]6225[/TD]
[/TR]
[TR]
[TD="bgcolor: #5B9BD5"]18-Sep[/TD]
[TD="bgcolor: #DDEBF7, align: right"]124[/TD]
[TD="bgcolor: transparent, align: right"]5992[/TD]
[TD="bgcolor: #DDEBF7, align: right"]150[/TD]
[TD="bgcolor: transparent, align: right"]6266[/TD]
[/TR]
[TR]
[TD="bgcolor: #5B9BD5"]25-Sep[/TD]
[TD="bgcolor: #DDEBF7, align: right"]23[/TD]
[TD="bgcolor: transparent, align: right"]6093[/TD]
[TD="bgcolor: #DDEBF7, align: right"]152[/TD]
[TD="bgcolor: transparent, align: right"]6268[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 314"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col></colgroup>[/TABLE]

I had three problems

1. The columns were all very skinny with a lot of white space between the dates. Tweaking the Series Overlap and Gap Width did not correct this.
2. Excel autofilled all of the days between 9/4 to 9/11 and 9/11 to 9/18, etc. further exacerbating the issue above.
3. When I plot 2013 and Total (line) on a secondary axis, leaving 2010 and None on the primary, the 2013 column overlaps the 2010 and None columns.


I just tried to repeat these issues on my home system (Win 10 Home x64 with Office Pro Plus 2013 x64), but 1. and 2. were not issues on my personal laptop.

I also found a work around that has you add 2 empty columns between the data columns to act as "Padding" so the columns you want to see overlaps the blank data. Then just delete the legend entries for the padding columns and tweak the Overlap and Gap settings for the series. The table would then look like below. It works, but seems like that can't be the REAL solution.

[TABLE="width: 679"]
<tbody>[TR]
[TD]Version[/TD]
[TD]2010[/TD]
[TD]padding 2010[/TD]
[TD]Padding 2013[/TD]
[TD]2013[/TD]
[TD]Padding 2013[/TD]
[TD]Padding None[/TD]
[TD]None[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]4-Sep[/TD]
[TD="align: right"]312[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5767[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]6209[/TD]
[/TR]
[TR]
[TD]11-Sep[/TD]
[TD="align: right"]213[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5862[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]6225[/TD]
[/TR]
[TR]
[TD]18-Sep[/TD]
[TD="align: right"]124[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5992[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]6266[/TD]
[/TR]
[TR]
[TD]25-Sep[/TD]
[TD="align: right"]23[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]6093[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]6268[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="2"><col><col><col><col span="2"></colgroup>[/TABLE]

[TABLE="width: 485"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col><col span="4"></colgroup>[/TABLE]