Align decimal points

chiropterist · December 27, 2019, 11:53pm

Here is an brief excerpt from a column in a Calc spreadsheet. The numbers in the example are meaningless; they serve only to illustrate the problem:

1.7 g per 1/4 cup
25.9 C per 1/4 cup
4 portions per cup

These cells are formatted as follows:

0.0" g per 1/4 cup"
0.0" C per 1/4 cup"
0" portions per cup"

See a screenshot of the CELL FORMATTING and RESULT at

$C:\fakepath\Decimal Alignment Example.PNG$ .

How can I format the cells so the decimal points are aligned?

I understand that I can insert spaces of varying widths immediately after the number by using _m, _i, and so forth, but I am looking for something less klugey.

Any suggestions?

(edit: activated screenshot)

mikekaganski · December 28, 2019, 12:19am

You could do this using ? format code described on this help page:

The ? works as the # but adds a space character to keep decimal alignment if there is a hidden non-significant zero.

You might change your format strings to be:

???0.0" g per 1/4 cup"
???0.0" C per 1/4 cup"
???0" portions per cup"

(I guessed you might use 4 digit numbers, but you can adjust to your needs), align cells to the left, and use a monospace font (like Liberation Mono).

chiropterist · December 29, 2019, 2:24am

Thanks Mike, That’s just what I was looking for. Shame on me for not using a monospaced font from the get-go!

mikekaganski · July 26, 2021, 11:46am

See also this old Q&A.

pts1 · September 7, 2024, 5:16pm

The kluge I stumbled on before I knew about the ? format code was:

Highlight the cells containing the numbers and use Format Cells to set the number format (in my case, 1,234.56, no currency symbol needed).
Set the horizontal text alignment to Left, with an indent of X points (in my case, 12 pt worked well). Click OK.
Immediately reopen Format Cells and change the text alignment to Right. The Indent box will display the point value you entered in 2. above but will be grayed out. Click OK.

The numbers come out decimal-aligned, indented from the right edge of the column by the amount specified in 2. above. Works with Arial, Times New Roman, and several other randomly selected fonts. The Clone Formatting paintbrush will work to copy the formatting to other cells.