Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Numbers won't right-align
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 -- |
#2
|
|||
|
|||
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 -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=398492 |
#3
|
|||
|
|||
On Wed, 24 Aug 2005 04:59:59 -0500, Jon Quixley
wrote: 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. 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 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. -- |
#4
|
|||
|
|||
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "LurfysMa" wrote in message ... 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 -- |
#5
|
|||
|
|||
On Thu, 25 Aug 2005 12:57:57 -0300, "Bernard Liengme"
wrote: The Number format has no right space 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) 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. 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 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 Thanks. PS: I am using Excel 2000 (SP3) if that makes a difrference. Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "LurfysMa" wrote in message .. . 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 -- -- Running Excel 2000 SP-3 on Windows 2000 |
#6
|
|||
|
|||
On Tue, 23 Aug 2005 23:14:24 -0700, LurfysMa
wrote: 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 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 -- Running Excel 2000 SP-3 on Windows 2000 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I align numbers where one number has a dollar sign? | Charts and Charting in Excel | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
comma align numbers | Excel Discussion (Misc queries) | |||
Align $ sign next to numbers | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions |