Prev: How do I chart contours in Excel 2007?
Next: Change background / color of chart worksheet?
From: David on 4 Mar 2010 08:09
I have a line chart with a (horizontal) date axis along the bottom.
Currently I have data from Sep-2002 to Sep-2009. All of this data is shown
on the chart and the axes also go from Sep-2002 to Sep-2009.

My problem is that I want to set the maximum of the date axis to an earlier
date (say Jun-2008) hence hiding the later part of the data (without deleting
the original data). But Excel 2007 will not let me do this (apparently).

I have selected the axes and fixed the "maximum" date at an earlier date
(like Jun-2008). Surprisingly, Excel leaves the axis exactly the same (it
still ends at Sep-09) but all the date labels beyond Jun-2008 have
disappeared. Weird!

The other weird thing is that if I try and fix the "minimum" date then it
works as I want it to. For example, I can fix the "minimum" to Sep-03 and
Excel will hide the first year of data. But for some reason it won't give me
the same level of control over the maximum!?!

Any suggestions?

Usually I can find a work-around for these sorts of things, but I am really
stumped so would appreciate insight from the experts.

p.s. I should note that I am using a line graph with a secondary vertical
axis. I think this may be part of the problem. It seems that if I eliminate
the data on the secondary vertical axis then Excel gives me back control over
"maximum" dates.

From: Bernard Liengme on 4 Mar 2010 09:21
When I first started using Excel 2007, I had the same problem: could not
type a date into the boxes setting the axis scale. The solution is to type
the serial number of the date. On the worksheet enter the required date in
the normal way; format the cell General so that you see a 5-digit number
like 40123. Enter this value into the Format Axis box.
I do not use dates that much but I now find that I can enter a date into the
scale boxes. I cannot recall when this changed. Do you have the second
service pack (SP2) installed?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"David" <David(a)discussions.microsoft.com> wrote in message
news:2C48C9E5-B3FC-44BE-B3B5-2D34EBF4F529(a)microsoft.com...
> I have a line chart with a (horizontal) date axis along the bottom.
> Currently I have data from Sep-2002 to Sep-2009. All of this data is
> shown
> on the chart and the axes also go from Sep-2002 to Sep-2009.
>
> My problem is that I want to set the maximum of the date axis to an
> earlier
> date (say Jun-2008) hence hiding the later part of the data (without
> deleting
> the original data). But Excel 2007 will not let me do this (apparently).
>
> I have selected the axes and fixed the "maximum" date at an earlier date
> (like Jun-2008). Surprisingly, Excel leaves the axis exactly the same (it
> still ends at Sep-09) but all the date labels beyond Jun-2008 have
> disappeared. Weird!
>
> The other weird thing is that if I try and fix the "minimum" date then it
> works as I want it to. For example, I can fix the "minimum" to Sep-03 and
> Excel will hide the first year of data. But for some reason it won't give
> me
> the same level of control over the maximum!?!
>
> Any suggestions?
>
> Usually I can find a work-around for these sorts of things, but I am
> really
> stumped so would appreciate insight from the experts.
>
> p.s. I should note that I am using a line graph with a secondary vertical
> axis. I think this may be part of the problem. It seems that if I
> eliminate
> the data on the secondary vertical axis then Excel gives me back control
> over
> "maximum" dates.
>
From: Jon Peltier on 4 Mar 2010 10:45
You could always use dates in a line chart's dialog, but never in an XY
chart's (still can't in 2007 SP2). Microsoft did finally enable date
entry in XY chart axis dialogs in 2010.

I don't know what is the user's problem, but installing SP2 often fixes
things like this.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/4/2010 9:21 AM, Bernard Liengme wrote:
> When I first started using Excel 2007, I had the same problem: could not
> type a date into the boxes setting the axis scale. The solution is to
> type the serial number of the date. On the worksheet enter the required
> date in the normal way; format the cell General so that you see a
> 5-digit number like 40123. Enter this value into the Format Axis box.
> I do not use dates that much but I now find that I can enter a date into
> the scale boxes. I cannot recall when this changed. Do you have the
> second service pack (SP2) installed?
> best wishes
From: David on 4 Mar 2010 20:01
Thanks for the replies guys.

I do have SP2 installed for Office 2007. I am also able to enter in dates -
as I mentioned, I am able to set the minimum date to whatever I want. But
not the maximum date. Note that I have also used the 5-digit number approach
and I still can't set the maximum date.

By the way Jon, your website is an excellent resource of charting tips and
techniques. Whenever I need to produce a tricky graph I go to your website
first. I want to thank you for creating it.

Cheers
David

From: David on 7 Mar 2010 22:43
I have done some further exploration of my problem. I think this may just be
a glitch with secondary axes line charts in Excel 2007.

I can show this using some "made-up" data. I entered the following data
into Excel 2007:

Date Series 1 Series 2
1 43 10
2 45 11
3 45 14
4 67 16
5 89 13
6 90 #N/A
7 89 #N/A
8 88 #N/A

I then used this data to create line chart. I made the axis a date axis.
And I put the second series on the second vertical axis.

I then changed the maximum date to "5" and Excel 2007 merely removes the
labels. It does not actually change the maximum date!

I note that if I just create the chart with Series 1 on it then I can change
the maximum date.

So it looks to me like it is a glitch! Or am I doing something wrong? Its
quite frustrating because I often use graphs with secondary axes.

 |  Next  |  Last
Pages: 1 2
Prev: How do I chart contours in Excel 2007?
Next: Change background / color of chart worksheet?