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:
=PROFEXIndentLevel(A3)
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.
Fantástica solución
superb! thanks
You are welcome!
Very useful and simple to implement
Yes, that’s what I think 🙂
Thanks for sharing.
It’s easy to use and very helpful with the mix of other excel functions.
Thanks a lot.
Thanks for the feedback – I’m glad you like it! 🙂
This VBA code is causing my excel to freeze. The spreadsheet still works but the screen goes blank or the mouse cruiser no longer highlights or moves cells. For example when I type, the text will be entered in the cell I’m on, but the spreadsheet will not reflect this until I exit the sheet and reopen it. Any idea how to stop this?
Additionally, this code also freezes any other spreadsheet I have open simultaneously. The only way to fix this is to Force Close the Excel application, then reopen, until the next time it freezes and then repeat the process.
This worked beautifully for me.