Formatting 'X' Axis -- 2 fundamental differences

I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

In the second case, Excel has made a category axis, treating your dates as
nonnumeric labels. Perhaps the dates were imported from some other source
and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar" wrote in
message ...
I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font
Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

You have helped me before several times, and I appreciate it!

Is there a way to fix the 2nd case to arrive at the 1st case?

FLKulchar
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates as
nonnumeric labels. Perhaps the dates were imported from some other source
and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar" wrote
in message ...
I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

You can select the column of pseudo dates in the sheet, and use Data menu
Text to Columns. Choose Fixed Width in step 1, then remove any breaks into
separate columns in step 2, and in step 3, select Date in the top right, and
choose the order of date elements.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Francis L. Kulchar" wrote in message
...
You have helped me before several times, and I appreciate it!

Is there a way to fix the 2nd case to arrive at the 1st case?

FLKulchar
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates
as nonnumeric labels. Perhaps the dates were imported from some other
source and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar" wrote
in message ...
I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font
Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT
THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

Thank you!

Fiorst off, I tried what you said...and I secected my column of "pseudo
dates" which is B600 -- B1400:

B600 says 1 - Nov
B601 says =B600+1
B602 says =B601 +1

etc, etc, etc

BUT THAT EXACT SAME COLUMN IS THE EXACT SAME FOR ALL 9 OF MY CHARTS WRITTEN
ON THAT PAGE!!

ALL THE CELLS ARE FORMATTED:
CUSTOM "D -- MMM"

YET I STILL GET THE 2 DISTINCTLY DIFFERENT "FORMAT X AXIS" EXAMPLES THAT I
EARLIER REFERRED TO!

WHY CHART 1 IS DIFFERENT FROM CHART 2 ...I DO NOT UNDERSTAND!

I tried your text to columns bit...but I got somewhat "lost" in your last
step...'select Date in the top right'.

WOW!!!!! I JUST solved my problem:

All the graphs that follow my example 2, have as source datta for the date:

B600:B1109

All the graphs that have my example 1, have as source data:

B972:B1109

Furthermore, all the charts show the date such as:

5/02/09 (For my example 1 -- what I'm after

All others, i.e. the charts from B600:B1109 show the X axis's date as:

12-Nov

SO FINALLY, I have fixed all my charsts...by "limiting" my opening date to
12/31/08
and source data B972:B1109

Now , my charts start earlier, and my charsts whos "X Axis" was B18:B1109
always came out "wrong" (my snd example.

I CANNOT UNDERSTAND WHY...ALL OB MY CELLS...B18:B1109 ARE FORMATTED EXACTLY
THE SAME!!!

So, again, thank you for the help...BUT WHY WAS THERE A
DIFFERENCE?????????????????

Please RSVP.

Thamk you,

FLKulchar





Jon Peltier
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates as
nonnumeric labels. Perhaps the dates were imported from some other source
and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar" wrote
in message ...
I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

No need to shout.

All it takes is one cell with a text value, not a numeric value, to screw up
an axis. All the numeric cells can be formatted the same, and the text cell
have text that looks like the rest of the dates or numbers, and you'll never
know. The trick is to overcome your natural inclination to assign a
horizontal alignment (most people insist on centering data in cells), and
leave it as default. This way it becomes obvious: text is left-aligned and
numbers (including dates) are right aligned. Error values are centered.

Another point is to check how the axis is defined. In 2003 & earlier, go to
Chart menu Chart Options Axes. Under Category (X) Axis, if Auto is
selected, Excel decided how to set up the axis. You can overrule Excel by
selecting Category or Time-Scale. One text cell will cause Excel to
automatically select Category.

In 2007, these settings are found on the Format Axis dialog, main tab, and
the two axis types are Text and Date, but the inner workings are the same.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Francis L. Kulchar" wrote in message
...
Thank you!

Fiorst off, I tried what you said...and I secected my column of "pseudo
dates" which is B600 -- B1400:

B600 says 1 - Nov
B601 says =B600+1
B602 says =B601 +1

etc, etc, etc

BUT THAT EXACT SAME COLUMN IS THE EXACT SAME FOR ALL 9 OF MY CHARTS
WRITTEN
ON THAT PAGE!!

ALL THE CELLS ARE FORMATTED:
CUSTOM "D -- MMM"

YET I STILL GET THE 2 DISTINCTLY DIFFERENT "FORMAT X AXIS" EXAMPLES THAT I
EARLIER REFERRED TO!

WHY CHART 1 IS DIFFERENT FROM CHART 2 ...I DO NOT UNDERSTAND!

I tried your text to columns bit...but I got somewhat "lost" in your last
step...'select Date in the top right'.

WOW!!!!! I JUST solved my problem:

All the graphs that follow my example 2, have as source datta for the
date:

B600:B1109

All the graphs that have my example 1, have as source data:

B972:B1109

Furthermore, all the charts show the date such as:

5/02/09 (For my example 1 -- what I'm after

All others, i.e. the charts from B600:B1109 show the X axis's date as:

12-Nov

SO FINALLY, I have fixed all my charsts...by "limiting" my opening date to
12/31/08
and source data B972:B1109

Now , my charts start earlier, and my charsts whos "X Axis" was B18:B1109
always came out "wrong" (my snd example.

I CANNOT UNDERSTAND WHY...ALL OB MY CELLS...B18:B1109 ARE FORMATTED
EXACTLY
THE SAME!!!

So, again, thank you for the help...BUT WHY WAS THERE A
DIFFERENCE?????????????????

Please RSVP.

Thamk you,

FLKulchar





Jon Peltier
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates
as nonnumeric labels. Perhaps the dates were imported from some other
source and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar" wrote
in message ...
I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font
Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT
THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

Jon:

I cannon overstate how invaluable your help to me has been over the previous
3 years or so; I absorb, and I follow everything that you say; it is just,
in this case, ALL OF MY 7 CHARTS COME FROM THE SAME PAGE SPREADSHEET...AND
ALL OF THEM HAVE THE "B" COLUMN CHOSEN AS THE X AXIS, BECAUSE CELLS B1
THROUGH B1400 ALL HAVE THE SAME FORMAT...(CUSTOM
-- DATE ... == M-DDD").....AND THE CELLS ARE GENERATED VIA:

B2=B1+1
B3=B2 +1
B4=B3 +1
. ..
...
...
down to
B1400=B1399

The rest of my charts include 7 day averages
14 day averages
30 day averages

And, since the X axix is identical
in ALL the charts, I fail to see why all my X Axis formatting "Scale"
options are NOT the same in the 7 chaets...remember, I fixed the problem,via
changing all the X values from B972:B1109...if I try to go back into time,
such as B400:B1109 for the X axis, my formatting option for formatting the
'SCALE' under X Axis reverts to my example 1 -- a formatting option I do
not aspire to.

Thanks, FLKulchar






eltiertech.com wrote in message
...
No need to shout.

All it takes is one cell with a text value, not a numeric value, to screw
up an axis. All the numeric cells can be formatted the same, and the text
cell have text that looks like the rest of the dates or numbers, and
you'll never know. The trick is to overcome your natural inclination to
assign a horizontal alignment (most people insist on centering data in
cells), and leave it as default. This way it becomes obvious: text is
left-aligned and numbers (including dates) are right aligned. Error values
are centered.

Another point is to check how the axis is defined. In 2003 & earlier, go
to Chart menu Chart Options Axes. Under Category (X) Axis, if Auto is
selected, Excel decided how to set up the axis. You can overrule Excel by
selecting Category or Time-Scale. One text cell will cause Excel to
automatically select Category.

In 2007, these settings are found on the Format Axis dialog, main tab, and
the two axis types are Text and Date, but the inner workings are the same.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Francis L. Kulchar" wrote in message
...
Thank you!

Fiorst off, I tried what you said...and I secected my column of "pseudo
dates" which is B600 -- B1400:

B600 says 1 - Nov
B601 says =B600+1
B602 says =B601 +1

etc, etc, etc

BUT THAT EXACT SAME COLUMN IS THE EXACT SAME FOR ALL 9 OF MY CHARTS
WRITTEN
ON THAT PAGE!!

ALL THE CELLS ARE FORMATTED:
CUSTOM "D -- MMM"

YET I STILL GET THE 2 DISTINCTLY DIFFERENT "FORMAT X AXIS" EXAMPLES THAT
I
EARLIER REFERRED TO!

WHY CHART 1 IS DIFFERENT FROM CHART 2 ...I DO NOT UNDERSTAND!

I tried your text to columns bit...but I got somewhat "lost" in your last
step...'select Date in the top right'.

WOW!!!!! I JUST solved my problem:

All the graphs that follow my example 2, have as source datta for the
date:

B600:B1109

All the graphs that have my example 1, have as source data:

B972:B1109

Furthermore, all the charts show the date such as:

5/02/09 (For my example 1 -- what I'm after

All others, i.e. the charts from B600:B1109 show the X axis's date as:

12-Nov

SO FINALLY, I have fixed all my charsts...by "limiting" my opening date
to
12/31/08
and source data B972:B1109

Now , my charts start earlier, and my charsts whos "X Axis" was B18:B1109
always came out "wrong" (my snd example.

I CANNOT UNDERSTAND WHY...ALL OB MY CELLS...B18:B1109 ARE FORMATTED
EXACTLY
THE SAME!!!

So, again, thank you for the help...BUT WHY WAS THERE A
DIFFERENCE?????????????????

Please RSVP.

Thamk you,

FLKulchar





Jon Peltier
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates
as nonnumeric labels. Perhaps the dates were imported from some other
source and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar" wrote
in message ...
I have many Line-type charts, all of which have as the X axis, the DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font
Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT
THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE, BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar

Hokay, I don't volunteer so much anymore, but why don't you send me the
worksheet? Something funny's going on, and you've been around here long
enough that I know it's not one of the obvious things that trips up the
inexperienced. Remove the capital letters from the email in my profile.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Francis L. Kulchar" wrote in message
...
Jon:

I cannon overstate how invaluable your help to me has been over the
previous 3 years or so; I absorb, and I follow everything that you say; it
is just, in this case, ALL OF MY 7 CHARTS COME FROM THE SAME PAGE
SPREADSHEET...AND ALL OF THEM HAVE THE "B" COLUMN CHOSEN AS THE X AXIS,
BECAUSE CELLS B1 THROUGH B1400 ALL HAVE THE SAME FORMAT...(CUSTOM
-- DATE ... == M-DDD").....AND THE CELLS ARE GENERATED VIA:

B2=B1+1
B3=B2 +1
B4=B3 +1
. ..
...
...
down to
B1400=B1399

The rest of my charts include 7 day averages
14 day averages
30 day averages

And, since the X axix is identical
in ALL the charts, I fail to see why all my X Axis formatting "Scale"
options are NOT the same in the 7 chaets...remember, I fixed the
problem,via changing all the X values from B972:B1109...if I try to go
back into time, such as B400:B1109 for the X axis, my formatting option
for formatting the 'SCALE' under X Axis reverts to my example 1 -- a
formatting option I do not aspire to.

Thanks, FLKulchar






eltiertech.com wrote in message
...
No need to shout.

All it takes is one cell with a text value, not a numeric value, to screw
up an axis. All the numeric cells can be formatted the same, and the text
cell have text that looks like the rest of the dates or numbers, and
you'll never know. The trick is to overcome your natural inclination to
assign a horizontal alignment (most people insist on centering data in
cells), and leave it as default. This way it becomes obvious: text is
left-aligned and numbers (including dates) are right aligned. Error
values are centered.

Another point is to check how the axis is defined. In 2003 & earlier, go
to Chart menu Chart Options Axes. Under Category (X) Axis, if Auto is
selected, Excel decided how to set up the axis. You can overrule Excel by
selecting Category or Time-Scale. One text cell will cause Excel to
automatically select Category.

In 2007, these settings are found on the Format Axis dialog, main tab,
and the two axis types are Text and Date, but the inner workings are the
same.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"Francis L. Kulchar" wrote in message
...
Thank you!

Fiorst off, I tried what you said...and I secected my column of "pseudo
dates" which is B600 -- B1400:

B600 says 1 - Nov
B601 says =B600+1
B602 says =B601 +1

etc, etc, etc

BUT THAT EXACT SAME COLUMN IS THE EXACT SAME FOR ALL 9 OF MY CHARTS
WRITTEN
ON THAT PAGE!!

ALL THE CELLS ARE FORMATTED:
CUSTOM "D -- MMM"

YET I STILL GET THE 2 DISTINCTLY DIFFERENT "FORMAT X AXIS" EXAMPLES THAT
I
EARLIER REFERRED TO!

WHY CHART 1 IS DIFFERENT FROM CHART 2 ...I DO NOT UNDERSTAND!

I tried your text to columns bit...but I got somewhat "lost" in your
last
step...'select Date in the top right'.

WOW!!!!! I JUST solved my problem:

All the graphs that follow my example 2, have as source datta for the
date:

B600:B1109

All the graphs that have my example 1, have as source data:

B972:B1109

Furthermore, all the charts show the date such as:

5/02/09 (For my example 1 -- what I'm after

All others, i.e. the charts from B600:B1109 show the X axis's date as:

12-Nov

SO FINALLY, I have fixed all my charsts...by "limiting" my opening date
to
12/31/08
and source data B972:B1109

Now , my charts start earlier, and my charsts whos "X Axis" was
B18:B1109
always came out "wrong" (my snd example.

I CANNOT UNDERSTAND WHY...ALL OB MY CELLS...B18:B1109 ARE FORMATTED
EXACTLY
THE SAME!!!

So, again, thank you for the help...BUT WHY WAS THERE A
DIFFERENCE?????????????????

Please RSVP.

Thamk you,

FLKulchar





Jon Peltier
"Jon Peltier" wrote in message
...
In the second case, Excel has made a category axis, treating your dates
as nonnumeric labels. Perhaps the dates were imported from some other
source and were pasted into Excel as text.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"F. Lawrence Kulchar"
wrote in message
...
I have many Line-type charts, all of which have as the X axis, the
DATE.

When I right click on the "X" axis, I get 2 distinctly different type
options under "SCALE" as follows:

1) FORMAT AXIS
Patterns Scale Font Number Alignment

minimum

maximum

base unit

major unit

minor unit

value (Y) axis
crosses at

Date in Reverse Order Value (Y) axis crosses between dates

Value (Y) axis crosses at maximum value


OR...

I get:

2) Patterns Scale Font
Number
Alignment

Value (Y) axis crosses
at category number

Number of categories
between tick-mark labels

Number of categories
between tick marks

IN ANY EVENT, I FAIL TO UNDERSTAND WHY, UNDER "SCALE", WHEN I FORMAT
THE
"X" AXIS,
I GET THESE 2 OPTIONS DESCRIBED ABOVE,
BECAUSE..

"I BELIEVE THE CHARTS ARE IDENTICAL!"

Can someone please help me???

Thank you,

FLKulchar