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.

Decimal Tab Alignment

by Allen Wyatt
(last updated July 16, 2016)

4

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. Display the Home tab of the ribbon.
  3. Click the small icon at the bottom-right of the Number group. Excel displays the Format Cells dialog box.
  4. Make sure the Number tab is selected. (See Figure 1.)
  5. Figure 1. The Number tab of the Format Cells dialog box.

  6. In the Category list, choose Custom.
  7. In the Type box, enter the following format:
  8.          _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_._0_0_)
    
  9. Display the Alignment tab of the dialog box. (See Figure 2.)
  10. Figure 2. The Alignment tab of the Format Cells dialog box.

  11. Using the Horizontal drop-down list, choose Right.
  12. Click on OK.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Peculiar Font Differences

Have you noticed page layout differences when you open a document on different systems? There are a number of reasons why ...

Discover More

Copying Formats to a New Worksheet

Do you want to copy formats from one worksheet to another? You can do so easily by using the Format Painter. It even ...

Discover More

Displaying the "Last Modified" Date

Want to know when a workbook was last modified? Want to put that date within the header of your worksheet? Here's how to ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Custom Formats for Scientific Notation

Excel allows you to format your numeric values in a wide variety of ways. One such formatting option is to display ...

Discover More

Understanding Color and Conditional Formatting Codes

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

Adding a Custom Format to those Offered by Excel

Adding a custom format to Excel is easy. Having that custom format appear in all your workbooks is a different story ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 0 + 2?

2019-04-08 20:53:12

Roy

I should mention at least one last thing about using the "?" for formatting:

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

To get the decimals to truly line up, and to suppress decimal place characters after a certain length, one can use the "?" formatting. For instance:

?.??

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

Thanks. This is a good article and taught me a lot about Excel formatting. However, it adds two decimal places to number that otherwise don't have a fractional component.

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

Thank you for this great tip. It will make it much easier to line up figures (and dashes in some cases) when I am typing financial accounts. I will use it often.


This Site

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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.