Align numeric values on the decimal point

In Excel I want to align the numbers to the last unit and when there are no decimals, I don't want to display any zeros after the decimal point. I want to align the cells as follows:

1234      -->    1,234
1234.12   -->    1,234.1200
1234.123  -->    1,234.1230
1234.1234 -->    1,234.1234
12.1      -->       12.1000
123       -->      123
-123,1234 -->     -123.1234

I have Excel 2013.

Solution 1:

I can’t figure out how to do this in one simple format.  Here is a two-step solution:

1. Give the cells a custom format of #,##0.0000.  Alternatively, just format them as Numbers with 4 decimal places and check (✓) the “Use 1000 Separator (,)” checkbox:

     

   This will cause numbers to be displayed as follows:

   1234      →  1,234.0000
   1234.12   →  1,234.1200
   0         →      0.0000
   0.12      →      0.1200
   -123.1234 →   -123.1234
   

2. Then, apply Conditional Formatting to the cell(s).  Letting Q42 represent the designation of the top-left cell in the range,

   • Select “Use a formula to determine which cells to format”.
   • Under “Format values where this formula is true:”, enter =INT(Q42)=Q42.  This is true for numbers that are integers; i.e., where there are no decimals.
   • Click on “Format…” and enter a Custom format of #,##0_._0_0_0_0.  This will cause integers to be displayed as integers (with no decimal point or decimal digits), but followed with blank space equal to the width of .0000, for alignment.  (In an Excel format, _X, where X is any character, displays as a space with the same width as X, so _._0_0_0_0 is a space as wide as .0000.)  And, of course, numbers whose absolute value is larger than 999 are displayed with commas as thousands separators.
3. Of course, Excel right-justifies numbers by default.  As long as you don’t change that, Excel will automatically provide enough whitespace to the left of the number to get the one’s digits of all the numbers to line up.

The below screenshot demonstrates this answer.  The first column shows the numbers from your question (and a few others) unformatted (i.e., in “General” format).  The second column shows the same numbers, formatted as described above.

Solution 2:

1. Select the cells that contain the numbers (or the entire column).
2. On the Home tab Click Format and choose Format Cells.
3. In the Format Cells dialog switch to the Number tab.
4. In the Category list on the left, choose Custom.
5. In the Type textbox enter ??,???.????. If your numbers have more than 5 numbers on the left of the decimal period or more than 4 numbers on the right, use more question mark characters.