Text Alignment – Indent

There are different types of (horizontal) text alignment in Excel.  Most people know about (and use mainly) General, Right, Center, Left.  But are you aware of Indent, Fill, Justify, Centre Across Section and Distributed?

Excel Tips - Text Alignment

Let’s talk about Indent this week.  Before we begin, can you tell me the differences among the three tables below?

Excel Tips - Text Alignment Indent 1

Excuse me? I see no difference.

Again, looks at be deceiving, especially in Excel.  They are actually input using three different approaches.

The tables above shows part of a typical P&L.  From my work experiences, Table 1, followed by Table 2, are two commonly seen ways to input for such kind of table.  However, I would say only Table 3 is the “correct” way for Excel.  So what are the differences?

Table 1 – Unnecessary columns used

Excel Tips - Text Alignment Indent 2

Common problems:

  • Filter-unfriendly.  You will get loss when you apply Auto Filter to the table, as the the labels are sitting on three different columns indeed.
  • Formula-unfriendly.  Imagine you need to look up “COGS”, the lookup array starts with Column C; while you look up “Rent”, the lookup array starts with Column D.

 Table 2 – Leading spaces used

Excel Tips - Text Alignment Indent 3

Although all the labels are under the same column, leading spaces are added in front of the labels for “formatting” purpose.  It is absolutely not the correct way to have your text indented in a cell.

Common Problems:

  • Formula-unfriendly.  Instead of looking up “COGS”, we need to look up ”      COSG” or ”         Rent” in order to get the desired result.  The thing is, how do we know how many spaces have been input before the text??
  • Sorting-unfriendly.  With leading spaces, we may not get your  desired sorting result Excel takes space into consideration when sorting.  Normally we do not sort a P&L as they are already sorted in a logically way.  However if we get used to input leading spaces and have it become a habit, you may experience lots of problem in using Excel.  We may even complain how “stupid” Excel is although the stupidity is not coming from Excel

Table 3 – The “correct” format we should always stick to

Excel Tips - Text Alignment Indent 4

  • The labels are all under the same column
  • No leading spaces have been used
  • Exactly look the same as Table 1 and Table 2, by using the correct Text alignment with different levels of Indent (see above screenshot)

As such, Table 3 is

  • Filter-friendly
  • Sorter-friendly
  • Formula-friendly
  • and most importantly, User-friendly

I believe I have not listed all the potential problems for Table 1 and Table 2.  Please feel free to share your ideas in comment.

This entry was posted in Format and tagged , . Bookmark the permalink.

2 Responses to Text Alignment – Indent

  1. Shilpa says:

    Thanks for posting this & it is very useful. I have one question which is not related to formatting the cells but actually getting the details of the “Indentation” in an excel. I have an indented column that I need to split into multiple columns & the only way to do this is manually at the minute as you can’t filter to view only the rows with “Indent” 2 or 3 etc… is there a formula or a way to get the “Indent” value for a cell?


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s