Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Decimal Tab Alignment.
by Allen Wyatt
(last updated November 17, 2012)
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:
Figure 1. The Number tab of the Format Cells dialog box.
_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_._0_0_)
Figure 2. The Alignment tab of the Format Cells dialog box.
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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you want to format currency values so that Excel uses periods between groups of thousands and commas as a decimal ...Discover More
If you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it manually. ...Discover More
Have you ever entered information in a cell only for it to appear as hash marks? This tip explains why this happens, how you ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.