chart horizontal axis interval month?

Question

I have an Excel 2010 workbook with one worksheet and one chart.

The data is a series of decimal values in columns headed by a Year.

The first column of each row is a date displayed as dd/mm.

The chart which I am trying to modify shows the values as a 2d line chart.

The horizontal axis is the date fields and the vertical axis isd the values.

It forms lines which show an increasing value from the beginning of the year to the end of a year with the year series labelled.

The horizontal axis has gridl;ines at the beginning of each month 01/01 01/02 01/03 ... 01/11 01/11.

Everytime I try to modify the chart it returns the horizontal major gridline to the individual days.

How do I get the format horizontal axis to show the intervals so that 1 month can be selected for the major gridlines?

Answer 1

With a text axis, the labels are just what you specify in the source data, you can't set the scale (min ,max etc.)

With a date axis (which is what Excel displays automatically) you can change the scale, but you mentioned that you had no control over the gridlines. (I don't know why, by the way).

Unless someone else has a better suggestion, I fear you'll have to choose...

Answer 2

I have an Excel 2010 workbook with one worksheet and one chart.

The data is a series of decimal values in columns headed by a Year.

The first column of each row is a date displayed as dd/mm.

The chart which I am trying to modify shows the values as a 2d line chart.

The horizontal axis is the date fields and the vertical axis isd the values.

It forms lines which show an increasing value from the beginning of the year to the end of a year with the year series labelled.

The horizontal axis has gridl;ines at the beginning of each month 01/01 01/02 01/03 ... 01/11 01/11.

Everytime I try to modify the chart it returns the horizontal major gridline to the individual days.

How do I get the format horizontal axis to show the intervals so that 1 month can be selected for the major gridlines?

This would be a great addition to Excel Charts.

As a work-around, create a data series with X values equal to the dates of the first of each month, Y values constant, maybe zero if that works for your chart.  Create a picture in PAINT that is just a 1 x 100 (or whatever size works) black line, no extra white space.  Then use this as a marker for the data series and this will look like vertical gridlines on the first of each month.  Then use data labels that are the "X" values for the data series.  This will closely mimic axis values, that you will want to turn off.

Good Luck.

Answer 3

Thanks for that.

Is it possible that the workbook was created under Excel 2007 and converted to 2010 and the options, which can be displayed on the original chart, have been removed when the Workbook was converted to 2010?

I think I might have an old version of Excel somewhere and could check.

Another possibility is there still the same options to format a chart in VBA which may not have been changed since 2007. I am going to check that.

Thanks for the help anyway.

Answer 4

I think you may have misunderstood or more likely I did not explain the problem clearly., sorry if that is the case.

Your solution works as it should but it does not do what I want.

The original workbook has a chart which when you follow those instructions shows a much larger choice of options namely

Minimum Auto or fixed and a date value for the fixed setting, Auto selected (01/01/2010)

Maximum Auto or fixed and a date value for the fixed setting, Auto selected (31/12/2010)

Major Unit Auto or fixed  and a fixed setting, fixed selected value 1 units Months

Minor Unit Auto or fixed, auto chosen

Base Unit Auto or Fixed, fixed selected and a value of Days chosen

the rest of the options appear to be the same.

My problem is that when I modify the data and want to modify the chart the Axis Option are very much less (no Minimum , Maximum, Major Unit, Minor Unit or Base Unit.

What do I select in the charts to get this expanded list of options?

Answer 5

Right-click any x-axis label and select Format Axis... from the context menu.

Under Axis Type, select Text Axis, then click Close.

Excel will no longer treat the x-axis values as dates.