Formatting 'X' Axis -- 2 fundamental differences

  • Thread starter Thread starter F. Lawrence Kulchar
  • Start date Start date
F

F. Lawrence Kulchar

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
 
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
 
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
 
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 <[email protected]>
 
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-06-ACNJ/AdvExcelConf200906ACNJ.html
_______
 
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
 
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-06-ACNJ/AdvExcelConf200906ACNJ.html
_______
 
Back
Top