Indentation in Excel: How to Return the Indent Level of a Cell

indent, level, indentation, excel, guide, how-to, return

How to Return the Indentation of a Cell in Excel

posted on 11 Apr 16

comments 1

Sometimes, you receive data which is organized by indentations. For example in an Excel table, the Total has no indentation whereas all single values are indented. This could be (typically…) the case for tables provided by some controlling tools. How to deal with that? What do you do if you want to sum up all values with the indentation level 1?

Problem: Getting the indentation of a cell

Let’s assume, we got the following table (usually these tables are longer and more complicated…):

Table with cost data

One way to evaluate the cost data is to manually type the formula as in B6: “=SUM(B3:B5)”. But for the sum in row 12, you’d have to be careful not to sum up Cost 5.1 and Cost 5.2 twice.

Therefore, the indentation levels are useful.

Hold on a second. Was this information helpful so far?

If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:

newsletter The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.

Your welcome gift: Our big 45 pages keyboard shortcuts package.

Learn more

Subscribe now!

In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.

Solution 1: Returning the indentation with an easy VBA code

There are basically two possible solutions. The first solution is using VBA. The function is quite straight forward:

Function PROFEXIndentLevel(Cell As Range)
    'This function returns the indentation of a cell content
    
    Application.Volatile
    'With "Application.Volatile" you can make sure, that the function will be recalculated once the worksheet is recalculated
    'for example, when you press F9 (Windows) or press enter in a cell
    
    PROFEXIndentLevel = Cell.IndentLevel
    'Return the IndentLevel
    
End Function

Function PROFEXIndentLevel(Cell As Range)

'This function returns the indentation of a cell content

Application.Volatile

'With "Application.Volatile" you can make sure, that the function will be recalculated once the worksheet is recalculated

'for example, when you press F9 (Windows) or press enter in a cell

PROFEXIndentLevel = Cell.IndentLevel

'Return the IndentLevel

End Function

Copy and paste this code into a new VBA module. Therefore (as usual) go to the Developer ribbon. Then click on Editor, right click on Microsoft Excel Objects. Insert a new module. Paste the code into this module.

Next, you just have to type the formula =PROFEXIndentLevel. For example in cell C3:

=PROFEXIndentLevel(A3)

1	=PROFEXIndentLevel(A3)

Using the VBA function “=PROFEXIndentLevel()”

In this example you’ll get 2 as the return.

Please note that if you use this formula on a large amount of cells it can slow down your workbooks performance.

Excel too slow? Speed it up. Get the book now!

Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!

Learn how Microsoft Excel performs calculations
Use the simple and effective step-by-step guide to master each method
Get to know the impact each method will have on performance

Learn more or get it on Amazon!

Solution 2: Professor Excel Tools

Return, Indent, level, indentation, professor, excel, tools

Return the level of indentation with Professor Excel Tools

If you don’t feel comfortable using a VBA macro, you can go with the second solution:

As the problem of getting the indentation level arises more often than expected, we included it in our Excel add-in ‘Professor Excel Tools’. You can download the free trial version with the link below. Just download it, activate it within Excel (the simple steps will be described after you click the download button) and type this formula:

Let’s say you want to get the indent level of cell A1, just type =PROFEXIndentLevel(A1). Press F9 for refreshing.