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: Returning the indentation of a cell
Let’s assume, we have the following table (usually these tables are longer and more complicated…):
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:
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
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’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.