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.

score 16 · Answer 1 · edited Jan 12 '17 at 13:09

16

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.

edited Jan 12 '17 at 13:09

neXus

103
4

answered Mar 14 '13 at 18:10

ikh

261
1
4

That's awesome! Now there's this weird space between the number and the negative sign, though. Any advice for fixing that? – Mar 14 '13 at 19:28
I guess, alternatively, is there a symbol like "?" that can include characters as well as digits (so that we can make excel treat the negative sign as part of the number)? – Mar 14 '13 at 19:49
@Atticus29 hmm, I'll have to take a look. To be honest, it's not something I've done before, I just happened to know how to find the Excel number formatting guide. – ikh Mar 14 '13 at 20:33
Any help would be greatly appreciated. Thanks for bringing the guide to my attention. I've been toying around with it a little and stil can't quite get what I want. – Mar 14 '13 at 20:42
Beautiful. Worked like a charm! – Dan Barron Oct 18 '16 at 15:04
1

If Excel is in a different language and this solution does not work, you might have to replace the decimal dot by a decimal comma: `_-???0,0??;-???0,0??;` – neXus Jan 12 '17 at 13:03

score 3 · Answer 2 · answered Jun 11 '15 at 21:33

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.

score 2 · Answer 3 · answered Jun 25 '14 at 09:26

2

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.

answered Jun 25 '14 at 09:26

Anton

21
1

score 0 · Answer 4 · answered Mar 25 '13 at 16:50

0

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.

answered Mar 25 '13 at 16:50

CEN7272

1

CEN7272, this is the conclusion I arrived at, too. It requires a bit of manual work with whitespace, though. I was hoping there was a more automatic way? – Mar 25 '13 at 17:05

score 0 · Answer 5 · edited May 23 '17 at 12:41

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)

Hmmm. I like this. Let me play with it. – Mar 25 '13 at 19:20 — , Mar 25 '13 at 19:20

score 0 · Answer 6 · answered Mar 25 '13 at 21:05

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

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

6 Answers6