Numbers won't right-align

  • Thread starter Thread starter LurfysMa
  • Start date Start date
L

LurfysMa

I have a fairly simple spreadsheet with 30-40 calculatrions.

Some of the results appear to have a space on the right hand end. Most
of these are calculations, but some are literal numbers. Some are
formatted as currency and some as numbers.

The right justify button doesn't help.

I have checked all of the formatting parameters I can think of.

Can anyone suggest what is going on here?

Thanks

--
 
CURRENCY format places a slight indent on the right hand end of a
number. The NUMBER format does the same thing. GENERAL format shoves
the number hard up against the right hand end, but it is possible to
move this using the INDENT option under the ALIGNMENT tab under the
FORMAT option for that cell.
I'm not sure which result you're after, but if you have currency
formats in there, then it sounds as if you'd prefer everything to have
the slight indent. If this is the case, then format everything else to
NUMBER and they should line up

Cheers
Jon
 
CURRENCY format places a slight indent on the right hand end of a
number. The NUMBER format does the same thing. GENERAL format shoves
the number hard up against the right hand end, but it is possible to
move this using the INDENT option under the ALIGNMENT tab under the
FORMAT option for that cell.
Click to expand...

All of the cells are formatted with either Currency or Number. None of
them are General. Most of the Currency-formatted cells are indented,
but some are flush right. Most of the Number-formatted cells are flush
right, but some are indented. The indenting is consistent across a
row.

Is there a way to get the Currency and Number formats to omit that
right hand space?
I'm not sure which result you're after, but if you have currency
formats in there, then it sounds as if you'd prefer everything to have
the slight indent. If this is the case, then format everything else to
NUMBER and they should line up
Click to expand...

I want everything to be flush right -- no extra spaces.

Here is one column of my table. For all cells, the alignment is
"General". I also tried flush right -- same result.

Use a monospace font to see alignment:

1,000 Number, 0 decimal places, comma separator
$20,000 Currency, 0 decimal places, $ symbol
$1,250 Currency, 0 decimal places, $ symbol
2,000 Number, 0 decimal places, comma separator
$2,000 Currency, 0 decimal places, $ symbol
0.2 Number, 1 decimal place, no separator
$6 Currency, 0 decimal places, $ symbol
$3,256 Currency, 0 decimal places, $ symbol
$16,744 Currency, 0 decimal places, $ symbol
$200,928 Currency, 0 decimal places, $ symbol

As you can see, some of the Currency-formatted cells are indented and
some are flush right. I can't find any difference in any of the
formatting settings.

All of the Number-formatted cells are flush right in this table, but
there is another table where a couple of them are indented.

--
 
The Number format has no right space
The Accounting format (what you get with the $ tool) has a right space
The Currency format (Format|Cells|Currency has no right spaces)

Make a cell Accounting with the $ (or your currency tool)
Select it and use Format | Style. Can you see where the space comes from?
Look at _-$* #,##0.00_; its that final underscore that makes the space.
You could modify your Currency tool style to get rid of the spaces in all
three parts of the format specs

best wishes
 
The Number format has no right space
Click to expand...

On this spreadsheet, sometimes it does and sometimes it doesn't.
Unless there is something that I don't understand -- always a good
possibility.
The Accounting format (what you get with the $ tool) has a right space
The Currency format (Format|Cells|Currency has no right spaces)
Click to expand...

Again, some (cells) do and some don't. It doesn't seem to matter
whether I use "Format | Cells | Currency" or right-click on the
cell(s), select Format Cells, and then select Currency.
Make a cell Accounting with the $ (or your currency tool)
Select it and use Format | Style. Can you see where the space comes from?
Look at _-$* #,##0.00_; its that final underscore that makes the space.
Click to expand...

OK. I selected a cell, clicked on the "$" tool, entered "123", and hit
Enter. It formatted as "$123.00".

I then clicked on Format|Style and got the Style panel. The Style name
is "Currency". The Number option is checked and there is a complicated
template with four parts. They are (unless I made a typo):

_($* #,##0.00_);_($* #,##0.00_);_($* "-"??_);_(@_)

The first 3 have a trailing "_", which is what I think you are talking
about. Right?

As an aside, I thought the formatting had just 3 parts: >0, <0, =0.
What's the 4th part "_(@_)" for?

So that looks like what you are describing.

Now I repeated this on one of the cells in my spreadsheet that is
acting up. When I clicked on Format|Style and get the Style panel, the
Style name is "Normal" and there is no format string as above.
You could modify your Currency tool style to get rid of the spaces in all
three parts of the format specs
Click to expand...

How do I do that? I tried the Modify button in the Style panel, but I
couldn't find anywhere to get at the format string.
best wishes
Click to expand...

Thanks.

PS: I am using Excel 2000 (SP3) if that makes a difrference.
 
I have a fairly simple spreadsheet with 30-40 calculatrions.

Some of the results appear to have a space on the right hand end. Most
of these are calculations, but some are literal numbers. Some are
formatted as currency and some as numbers.

The right justify button doesn't help.

I have checked all of the formatting parameters I can think of.

Can anyone suggest what is going on here?

Thanks
Click to expand...

I discovered the problem. It really has nothing to do with some format
styles adding a trailing space and others not. It has to do with which
format is selected for negative numbers.

Somehow, some of my cells were changed so that negative numbers were
to be formatted in parentheses "(1234.56)". Since that involves a
character (the closing parenthesis) after the number, the style adds a
trailing space for positive numbers -- I presume so that they will
align in nice columns.

I didn't notice that difference when I was comparing cells that did
have the space with those that didn't.

All I had to do was select the cells with trailing spaces and select
the first negative number option (-1234.56) and all of the trailing
spaces disappeared.

My copy of Excel seems to have the default formatting style for
negative numbers set to (1234.56) for both the Number and the Currency
formats. Is there some way that I can change that default so that when
I select either Number or Currency formatting, negative numbers
default to being formatted as "-1234.56"?

Thanks

I suppose one could argue that
 
Back
Top