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?

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

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

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

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

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