Decimal Tab Alignment

If you have ever aligned numeric information in Word using decimal tabs, you know they can be very handy. The tabs even align text (with no decimal point) to the left of an assumed decimal point, with everything nice and tidy.

Unfortunately, Excel has no such similar feature as a "decimal tab." While it is very easy to get things lined up if they include decimals (at least if they contain the same number of digits to the right of the decimal), adding text into a cell can throw everything out of whack.

To closely approximate the behavior of decimal tab alignment, follow these steps:

1. Select the cells you want to format.
2. Choose Cells from the Format menu. Excel displays the Format Cells dialog box.
3. Make sure the Number tab is selected. (See Figure 1.)



Figure 1. The Number tab of the Format Cells dialog box.

4. In the Category list, choose Custom.
5. In the Type box, enter the following format:

       _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_._0_0_)

6. Display the Alignment tab of the dialog box. (See Figure 2.)



Figure 2. The Alignment tab of the Format Cells dialog box.

7. Using the Horizontal drop-down list, choose Right.
8. Click on OK.

The format you are setting up in step 5 allows for two decimal places and parentheses around negative numbers. In addition, for text it leaves room after the text for a period, two zeros, and the optional closing bracket. Step 7 is necessary so that Excel pushes text up to the right end of the cell. Since the format you specified leaves room for the decimal point and everything after it, the text appears to align just to the left of where the period would appear.

Understand that this is only an approximation of the decimal tab alignment offered in Word. There are still a few things you can't do. In Word, if you enter text and it is decimal aligned, and the text includes a period, then the period is aligned as if it were a decimal point. If you put a period in the text entered in a cell that is formatted as directed above, the period will not be treated as a decimal point.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2765) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Decimal Tab Alignment.