How to Return the Indentation of a Cell in Excel
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:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
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 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.
