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 16, 2016)
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you create custom cell formats, you can include codes that allow you to set the color of a cell and that specify the ...Discover More
While the implementation of custom formats in Excel is not terribly robust, you can still achieve some amazing results ...Discover More
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
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.