Lining up the decimal places is the desired structure for some applications, especially in a finance context. With this, you can make the decimal portion of numbers start from the same place, regardless of how many digits the consist of. You can do this using accounting format, but you will get zeroes for each missing decimal digit. Let us show you how to line up decimals in Excel using Number Formatting in a clean fashion.

 

Number Format Syntax

We’re going to select the custom number format syntax like below.

??,??0.0???;−?,??0.0???

 

Steps

  1. Select the range of numbers.
  2. Press Ctrl + 1 to open the Format Cells dialog.
  3. Go to the Number tab.
  4. Select Custom in the Category input.
  5. Enter #,##0.0???;-#,##0.0??? into the Type option (to always show at least one 0).
  6. Click OK to apply number formatting.

 

How to line up decimals in Excel

You can alter display of numeric values without changing value itself using Number Formatting. Excel also allows creating your own custom formats (like the string we are using in this example). This feature can be found under Cell Format > Number.

You need to select Custom in the Category list and enter the syntax for your custom code. The syntax has 4 optional sections for positive and negative numbers, as well as zeroes and text values. Each section is separated with semicolon (;) characters. This article shows a syntax for only positive and negative values. The default options are used for omitted sections.

Number format syntax uses 3 main characters as placeholders for numbers:

  • # to display only the significant digits in a number
  • 0 to display non-significant zeros
  • ? to add spaces for non-significant zeros on either side of the decimal point

For example, if you want to align decimals while displaying at least one zero (0), the syntax should be:

??,??0.0???;−?,??0.0???

Notice that positive section has one more question mark than the negative section. The first question mark is placed to compensate for the minus character (−) in the negative section.

For more information about number formatting options please see: https://www.spreadsheetweb.com/number-formatting-excel/