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.

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

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)

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.

Solution 2: 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.

---

This function is included in our Excel Add-In 'Professor Excel Tools'

Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.

---