[SOLVED] but why? - Formatting X or horizontal axis labels

This is for Excel 2010 though image it applies to other versions

I've found a, and perhaps the, solution to my issue but don't understand why it worked. I started to write up an explanation of the issue I had been facing and in the middle of setting up the example spreadsheet I found the fix. Thus the first part of what's below is the explanation of the problem and then I'll bring up the item that fixed it.

I have ascending dates in column A and data in in column B. I also have a chart that uses the date for the X or horizontal axis and shows the data trend over time.

I'm able to control everything I desire in the chart except one thing which is the format of the horizontal axis labels. My chart is showing the "ddd m/dd/yyyy" format that I'm using in the A column. I want to use something more compact than "ddd m/dd/yyyy" in the charts.

I can right click on the horizontal axis area, select Format Axis, and there's tabs for Axis Options, Number, Fill, etc. All of the fields on those tabs seem to work as expected except the Number tab. I've tried setting the number format to General, Number, Date, and Custom (with m/d/yy) but the axis always displays as "ddd m/dd/yyyy".

Under the Axis Options tab there's Axis Type. It defaulted to Automatically select based on data. I tried both Text axis and Date axis without success.


While setting up the example spreadsheet for this post I found out how to control the horizontal axis format but would like to know if there's another solution.

The production spreadsheet has two charts both positioned near the end of the data. The two charts covering the same time range but with different vertical axis. I'm already using two vertical axis on one chart, could not add a third, and so have two charts. I have the charts arranged, one above the other, and lined up so that I can view all of the trend lines over time as though it is a three vertical axis chart.

Four times a year I add another three months of rows and would need to rearrange the charts. I knew that when you create a chart that it's anchored to a cell and Excel records the chart's position relative to that cell. If you drag the chart to another spot Excel just updates the relative position to the original anchor cell. In my case I'd created the chart long ago and the anchor cell was somewhere up in the chart's data. As moving and rearranging the charts every few months was time consuming at one point I deleted the charts, added an extra cell below the table that I called "chart anchor", selected that cell, and re-created both charts. Now when I do insert-row a few times a year to add new rows the charts get shoved down while keeping them visually aligned.

My chart's anchor cell used to have the value "chart anchor" so that I knew that it was my anchor cell. When setting up the example spreadsheet I did not think the anchor cell was an important part of the example and so did a delete-row on that row. Boom, the chart started displaying the desired horizontal axis format! I undid the delete-row and found that if I have a number or date in that cell, or it's empty, that the chart's horizontal axis label formatting overrides the format used in the anchor cell. If I have text in this cell then the labels use the format from the source cells in column A.

In the attached example you'll see that I used "ddd m/dd/yyyy" formatting for 2015 and "m/d/yyyy" formatting for 2016. In the chart the formatting of the labels also changes. If you delete the contents of cell A550 or replace the text "chart anchor" with a number (or date) the labels will change to instead be the raw date numbers because for this example the axis label format is "#,##0". If you put any text back in call A550 the labels again use the formatting from the source data cells. You can drag the chart around but regardless of where it is on the sheet cell A550 is a key part for how the axis labels are formatted. If you cut/paste from A550 to somewhere else you'll find that A550 is still the key cell for the axis label format. If you delete-row row 550 then the anchor is now A549.

Here are the questions:

1) Is this expected Excel behavior or am I missing something? I've never seen the concept of the anchor cell documented by Microsoft.

2) Is "anchor cell" the correct name for the thing? I searched the forums for "anchor cell" and found many of the people that use that term don't get their question answered.

3) In this case I happened to know exactly which cell was the chart's anchor cell. I can imagine I'd really be pulling my hair out had the anchor cell instead been some random cell in the sheet. Is there a way to discover what cell is the anchor cell for a chart other than by insert/deleting rows/columns here and there trying to pin down which cell is the one that causes the chart to move?

4) Can I switch the anchor to be another cell?