Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Decimal Tab Alignment.
by Allen Wyatt
(last updated July 8, 2021)
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 6 allows for two decimal places and parentheses around negative numbers. In addition, it leaves room after the text for a period, two zeros, and the optional closing bracket. Step 8 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 (12318) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel 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!
Custom formats are great for defining how a specific value in a cell should look. They aren't that great at doing complex ...Discover More
Excel allows you to format your numeric values in a wide variety of ways. One such formatting option is to display ...Discover More
Creating custom formats is a very powerful way to display information exactly as you want it to appear. Most custom ...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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.