CODEWITHSUNDEEP
vbaexcel
supertrue
supertrue

Reputation: 2099

Excel - Only show multiples of 10 in axis, regardless of min and max

Suppose I have a line graph showing data from every year between 1957 and 2003.

If I Format axis and change the Interval between labels to 10, the resulting labels will be

1957

1967

1977

1987

1997

What I would like is to have labels like this:

1960

1970

1980

1990

2000

How can I show the latter labels without changing the actual range of the graph? That is to say, I want the graph to show all the data, from 1957 to 2003 (not just 1960 to 2000); but I want the labeling to start 3 years in, at 1960.

Is there a way to achieve this in Excel? (This is an issue with dozens of graphs I deal with, so a programmatic or non-ad hoc solution is ideal, rather than, for example, manually setting each range label. Sometimes the preferred interval is 5, sometimes it is 10, sometimes 20, etc.)

Upvotes: 6

Views: 5573

Answers (4)

Excellll
Excellll

Reputation: 5785

Use scatter plots rather than Excel line graphs. They look the same, but with a scatter plot, you aren't pigeon-holed into having uniformly spaced data along the x-axis. You can start the x-axis at 1950 (or 1960), and your line will still start at 1957.

enter image description here

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149305

The easy way to do this would be to insert one more row below your 'Year Row' and change the font color to match the cell background (or simply reduce the height of the row) and then add 3 to the above year. (See snapshot)

Next simply base your chart on the new row.

enter image description here

FOLLOWUP

This may not have been clear in my original question—the data is from every year—not just 1957, 67, 77 but 1957, 58, 59, 60, 61, etc. – supertrue 7 mins ago

I have taken a sample for 1957 to 1990 for illustration purpose. The above concept will work for this data as well. Format the axis and change the Interval between labels to 10. See snapshot below.

enter image description here

MORE FOLLOWUP

With this solution, the tick marks would not correspond to the correct dates. For example, the 1970 label would point to the 1967 data. I need the 1970 label to point to the actual 1970 data. – supertrue 19 mins

Follow the above concept but use the below formula instead of A1+3 in A2

=IF(MOD(A1,10)=0,A1,"")

See snapshot. I have deliberately kept the row height of Row 2 big for illustration purpose.

enter image description here

HTH

Sid

Upvotes: 4

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

If you absolutely must use a line chart, then the quickest way to solve your problem is to add sham data down to the previous multiple of 10, which is 1950 in this case. The sham data can be empty cells or, even better, #N/A to be explicit about the fact that these data are not really available. Here's an example (only goes up to 1973 for brevity).

enter image description here

To be honest, I don't like line charts. It can be really difficult to make them look just right. Scatter plots are better in most situations. Much easier to customize. In Excel 2003, you just edit the axis' Minimum, Maximum, and Major unit to be 1950, 1980, and 10, respectively. No need for sham data.

To get the code that does this, just record a macro while you do it manually.

enter image description here

Upvotes: 1

Jirka Kopřiva
Jirka Kopřiva

Reputation: 3099

What about creating extra column and applying ROUND. Then this column may be set for labels of axe.

Upvotes: 0

Related Questions