**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.

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:

- Select the cells you want to format.
- Display the Home tab of the ribbon.
- Click the small icon at the bottom-right of the Number group. Excel displays the Format Cells dialog box.
- Make sure the Number tab is selected. (See Figure 1.)
- In the Category list, choose Custom.
- In the Type box, enter the following format:
- Display the Alignment tab of the dialog box. (See Figure 2.)
- Using the Horizontal drop-down list, choose Right.
- Click on OK.

** 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 MoreWhile the implementation of custom formats in Excel is not terribly robust, you can still achieve some amazing results ...

Discover MoreCustom 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."

2019-04-08 20:53:12

Roy

If used for basic formatting as, say, "?" instead of, say, "?.???", there is a situation which will seem like a bug until understood. Consider "0.49" being formatted as "?"... it will display nothing, just nothing at all in the cell. It will be used by other cells as "0.49" but in its own cell, just an empty cell will show.

Why? Remember that the format is acting as if rounding (using 4/5 rounding) first, then displaying appropriately for the exact format (1 place to the left of the decimal here, all to the left and three to the right for "?.???", etc.).

So it first "rounds" (display only, really still "0.49") and the rounding takes it to "0" and then it displays that result, but suppresses trailing 0's. If the only character in the formatted result is a 0, it qualifies as a "trailing zero" and is suppressed. Hence the blank cell.

And TEXT() is consistent with that, doing the ACTUAL rounding I mention below, and then submitting the (actual, not display only) result for display.

Sorry, did not think of that when writing the below. It is off the decimal tab topic, but of interest to anyone who wishes to use it more generally.

By the way, in this case, "?." would show a lone period (decimal point) in the cell. The rounding works with negative numbers, in the appropriate manner for 4/5 rounding.

2019-04-08 13:13:41

Roy

?.??

will display "41.5, 321.85, 315, 586.1, and 1,052.658" with the decimal points tryly lined up, and no 0's padded to the end like "0.00" would do. Also, 1,052.658 shows the rounded version of "1052.66" ("?,???.?? would show that as "1,052.66"). Unfortunately, like every other custom formatting that includes decimal places, "315" shows with the decimal: "315."

This is not only a TRUE decimal tab appearance, but it can be exceedingly useful if "586.1" stands out better than "586.10" on a column of always two filled in decimal places would for your visual analysis needs (or your boss thinks so for his needs).

HOWEVER...

When it comes to using the variations on this inside the TEXT() function, do be aware that actual rounding, not just visual rounding, takes place. So for the "1,052.658" example, your result would REALLY BE "1,052.66" and not the full actual value.

That is only true in the function though, not in general cell formatting in which the "true" value is always maintained though not displayed.

Finally, you CAN append spaces to the end to force it over from the right edge of the cell. Naturally, other methods may be used also. Right Indent, for example.

The main difference between this and other techniques is the ability to line up the decimals regardless of the decimal places you allow to display and that each value has. "315, 586.1, and 1052.658" will ALL have their decimals lined up with each other even though they display varying digits to the right of those decimals. Just remember, it's different inside TEXT(). And perhaps other functions (not ROUND(), that works fine and so does MROUND(), so... maybe just TEXT() works to literally round things).

2017-05-18 09:59:47

Jim Casey

I'm dealing with a situation that includes numbers like 1 (exact) and 321.4567 (approximate). I don't want to add meaningless decimal places to the exact integers, or truncate the fractional numbers to a less precise number of digits.

2016-07-16 18:22:22

Ann

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.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2021 Sharon Parq Associates, Inc.

## Comments