Excel Alignment Tips

For perfectionists.

One of the things that always bothered me about Excel was the way it handled right alignment in cells with certain types of number formatting applied.

The Problem

For example, in the following illustration you’ll see that some cells containing values have currency formatting and other cells containing values have comma formatting applied. The line up nicely in a column. But when right-align the column headings as I’ve done here, the headings don’t line up with the numbers:

Excel Alignment Example

This is the case in all recent versions of Excel that I’ve worked with, including Excel 2004 for Macintosh and Excel 2007 and Excel 2003 for Windows. (The illustrations here are from Excel 2007 running on Windows Vista.)

Why It Happens

Before we can fix this problem, we should have a good understanding of why it occurs.

You may have noticed that when you apply currency or comma formatting (for example) the numbers shift to the left. Ever wonder why?

Excel Alignment ExampleWell, you can thank Excel for thinking ahead about something that you might not have in a column of numbers: negative values. As shown here, certain number formatting options place parentheses around negative numbers. To make sure all the numbers in a column line up, positive numbers are shifted by the width of a parentheses character to the left. So every number formatting with currency or comma formatting actually has a tiny bit of space after it.

The Solution

Since you don’t normally apply currency or comma formatting to text, cells containing text are truly aligned against the right side of the cell when you use the right alignment formatting option.

But guess what? You can apply currency or comma formatting to a cell containing text. And when you do, the contents of the cell will shift to the left by the width of one parentheses character. The result: everything is nicely aligned:

Excel Alignment Example

3 thoughts on “Excel Alignment Tips

  1. my excel sheet starting from right to left,just like arabic write from right to know how can i change my sheet as normal like left to right

Questions? Comments?
Share them here!

Comments are moderated. I encourage comments that further the discussion, but will not tolerate rudeness or spam. Your email address is never shared.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>