How do I align a column of numbers by decimal point in Microsoft Excel when some of those numbers are negative?

Question

Let's say I have a list of the following numbers:

0.02
-0.25
-4.13
5.24
-11.36
40.61
-141.20

How would I align them in a column such that they are left-justified in the cell but the decimal points align?

For example,

|   0.02    |
|  -0.25    |
|  -4.13    |
|   5.24    |
| -11.36    |
|  40.61    |
|-141.20    |

I was tempted to use the "Accounting" category, but this transformed my negative values by adding parentheses.

I'm using Excel for Mac 2011.

Answer 1

After you left-justify the cell, try this number format:

_-???0.0??;-???0.0??;

To apply it, right-click on the cell, select "Format Cells", go to "Number" tab, go to "Custom" format option, and paste the format string into the "Type:" field.

How it works:

• left most _- inserts whitespace equal to width of a "-" character;
• ? takes up space equal to that of a number if the number is not present;
• semicolon divides the format for the positive number (first part) from the format for the negative number (second part).

More in Excel number formats: Number format codes.

Note that this format will work only for numbers with up to 4 digits to the left of the decimal point. If you'll have larger numbers, you'll need to add more question marks.

Answer 2

I suggest:

Right click on cells and select Format Cells

On the Number tab:

Under Category select "Custom"

Under Type enter 0.0?;-0.0?

Select Alignment tab:

Set Horizontal to "Right (Indent)"

Set Indent to 1   (you can adjust later as desired)

Click OK to leave format tabs box.

This will make text look as follows

-12.0

 -1.25

  2.23

123.78

  Test

Notice that the minus sign is always next to the first digit and that the 2nd decimal blank if it is zero, and that the word Test is right aligned with the 2nd decimal.

I hope this helps.

Answer 3

Use a "Tab" there is a special one under Alignment there is one called Decimal. This will align the decimal according to the position of the tab.

Answer 4

If I understand correctly, you should just be able to Right Align the column (to line up the decimal) and then Increase Indent until your largest possible number is "aligned" along the left side. This way you won't have to worry about special formatting.

Answer 5

I wasn't sure if I should have just commented on my other answer but I figured since this is a new train of thought I'd add a new one....

What about Conditional Formatting? It will require a bit of initial setup but it will provide you the opportunity to have a seperate Number Format for 2 digit numbers, 3 digit numbers, etc as well as their negative counterparts. This way you'd be avoiding the blank spaces between the negagive sign and the number. Plus then you could just copy the formatting for use wherever you need it.

Here's a Conditional Formatting rule that would check if the value in the current cell is between 0 and 9 [1 digit num]:

=(INDIRECT(ADDRESS(ROW(),COLUMN()))>0)*(INDIRECT(ADDRESS(ROW(),COLUMN()))<10)

Note that with conditional formatting, =AND() doesn't work as nicely as one would think so multiplying the 2 boolean values provies you the equivelent expression (Thanks to this post for that tip)

Answer 6

Here's the understanding proccess, but at the end there's a single formula, you can jump to it if you like.

First you make a column containing the position of the separator (that's the point symbol ".")

Supposing your numbers are in the A column:

B1 = find("."; A1)
B2 = find("."; A2)
...

At the end of this column, let's say B20, you put the maximum value of the point position:

B20 = max(B1:B19)

Now, let's calculate how much spaces you need for each number. That's the difference between max point position and current point position:

C1 = $B$20 - B1
C2 = $B$20 - B2
...

And finally, add that spaces. Here's an important thing: use a equally spaced font, like "Lucida Console".

D1 = rept(" "; C1) & A1
D2 = rept(" "; C2) & A2
...

Of course you can try to get it all together in a single formula, but I tried to let it well explained. You could, for example, take a fixed max point position, say 10, and do:

= rept(" "; 10 - find("."; A1)) & A1