Calculating Outline Levels in Excel

[quigley6]

Hello,

I have set of data in excel that is organized in nested groups (using the "Data, Group and Outline, Group" step).

Does anyone have a clever way to determine the “outline Level” of this set of data? As I understand it, outline level is simply a numeric reference for how many indents there are in each row (1, 2, 3, 4, etc).

Thank you all.

 

[GlennUK]

Or, what about collapsing your nesting, and selecting only the visible cells for pasting formulae into? You'd use Edit/Goto/Special/Visible Cells Only.

But, I'd guess you'd want the formulae to reference only the collapsed sections, in which case it won't be any good to you.

 

[quigley6]

Is there a way to temporarily "hide" rows

Hi,

I have a nice column that shows the detail level (thanks to you Glenn). The detail level value in this column varies from 0 (fully outdented) to 5 (fully indented). These values are contained in column A. Is there a way to temporarily hide all values but "0", then paste the unique formula in all "0" items; hide all the values but "1", then paste the unique formula in all "1" items; hide all the values but "2", then paste the unique formula in all the "2" items; and so on through level 5.

This would work really well in my set-up.

Many thanks for all your help.

 

[Michael Avidan]

Hello,

I have set of data in excel that is organized in nested groups (using the "Data, Group and Outline, Group" step).

Does anyone have a clever way to determine the “outline Level” of this set of data? As I understand it, outline level is simply a numeric reference for how many indents there are in each row (1, 2, 3, 4, etc).

Thank you all.

Altough this post is 7 years old - I found it right to suggest a simple Macro for Calculating the total of Outline Levels per sheet.

Sub Micky()
    LR = ActiveSheet.UsedRange.Rows.Count
    For R = 1 To LR
        If ActiveSheet.Rows(R).OutlineLevel > C Then C = ActiveSheet.Rows(R).OutlineLevel
    Next
    MsgBox C & " levels found."
 End Sub

Michael (Micky) Avidan
“Microsoft^® Answers" - Wiki author & Forums Moderator
“Microsoft^®” MVP – Excel (2009-2014)
ISRAEL

 

[TrashmanL]

In case anyone else comes across this post like me, I just wanted to comment that Glenn's trick still works in Excel 2013. The only thing is that even though it doesn't directly involve creating a macro, in order for it to work, Excel requires you to save in the "Macro-Enabled (.xlsm)" Excel format, or in the legacy (.xls) format. It requires this for "Excel 4.0 formulas in named spaces."

I used the outline level as a conditional format on my sheet, and it required more than 3 conditional formats, so I had to use the newer, macro-enabled format (.xlsm).

 

[Mitrix82]

Re: That was truly brilliant!

[h=2]

That was truly brilliant![/h]

Glenn,

That was truly brilliant! It worked like a charm in Excel. Sorry to belabor you with another question. The “Outline Level” column in MSProject will not let me paste the values calculated in Excel. I tried tricking it by creating a “Number1” Column in MSProject, pasting the values calculated in Excel, and then trying to enter a formula (Outline Level= Number1) in the “Outline Level” column (using Tools, Customize, Field). Can’t get anything to work. Still trying.

Thanks, Burt​

I just had the same issue and though I would share
Paste outline level from excel into project in the Number 1 column as you have done
press alt+F11 to open code window, add new module, add the following code and run, (It takes a little while but works great)

Public Sub updateOutlines()    
    Dim t As Task
    For Each t In Application.ActiveProject.Tasks
        t.OutlineLevel = t.Number1
    Next
End Sub

 

[dschaefer]

Here's a quick and dirty way to do it:
select cell A1 of the sheet,
do menu command Insert/Name/Define, and create a name called DetailLevel, with a formula ( in the Refers To box ) of:

=GET.CELL(28,Sheet1!A1)

( alter the sheet name Sheet1 to whatever your sheet is called )
Now in any blank column of the sheet type the formula =DetailLevel and copy down.

That will give you 1 for no indent, 2 for level 2, 3 for level 3. That might be 1 out for your purposes. If so, just make the formula you type in the cells of the blank column be:
=DetailLevel-1

Hope that does it.

I know this is old, but I'm using this in Excel in Office 365 for conditional formatting for an outlined sheet. It works great, except if the outline level of a row changes the values don't automatically update. If I select the top cell and copy down the column they will then update, but they don't do so automatically.

How can I get it to automatically update or how do I force Excel to re-evaluate the cells?