How do I align a column of numbers by decimal point in Microsoft Excel when some of those numbers are negative?
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.
Solution 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.
Solution 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.
Solution 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.