Today, Bill has data where the amount of indent is telling him some data. He shows how to use VBA and IndentLevel to add a column of data showing the indent level. Then, a second technique, using the Group button on the Data Tab to make an outline view in Excel.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2411.
Making sense of indent levels in Excel and manually assigning groups.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by me.
I'm working on updating some books for Microsoft Press.
And the data that they give me is the old Table of Contents for the book.
So if I copy that from Word and then come back here to Excel and paste.
Look, there's three different types of data here.
“CH” is the chapter heading.
“H1” is major heading.
“H2” is minor headings.
But the only way that I can tell that is by counting the indent on all of those cells. Terrible, but here's a quick way to actually get the data so I can sort it or do something with it.
I'm going to switch over to VBA, alt+F11.
Here I am in this workbook.
I'll insert a module.
It's Get Indents.
We’ll say for each cell in selection.
Cell dot offset, I am going to use zero rows down and minus one cell to the right, which is the cell to the left.
Dot Value is equal to Cell dot IndentLevel.
That's where they store that.
Next Cell.
Something like that.
And we can run it.
This is a great little macro for putting your personal macro workbook.
And then we have 10, 11 and 13 which allows me to figure it out.
And actually, if it wasn't just a one-time thing, if I had to do this over and over again.
Then I do something like this where I use Select Case and depending on when it is 10, 11, 13, or 15, I would put the code over there.
And then.
If it's something I'm not expecting, it just puts the indent level as a number.
Alright, that's a little bit better.
Alright, so you know, I hate when people use colors to try and denote data.
They ask to add up all the red cells.
I have a video on that.
This time, it's Microsoft Press essentially using indent to try and tell me the difference between these.
Now look that was a really fast trick.
Let me show you one more awesome thing.
This was another question that came up last week.
I was doing a seminar at Creative Pro Week.
These are people that use Indesign.
They are text people.
They worry about text a lot.
And they wanted some text tricks and I said, yeah, we have something awesome back here on the Data tab.
In the far right-hand side called Group where you can manually group things.
But in order to use the trick that I'm about to show you, you have to come here to the dialog launcher.
And uncheck Summary Rows Below Detail.
Click OK.
And what I have essentially.
Is all of these cells here belong to the heading of “Transforming Data”.
So, Group.
By Rows.
And then all of these cells here belong to that H1 level called “Using Power Query”.
So Group, again in Rows.
And then here these cells belong to the item above it.
Group.
Click OK.
And then finally these cells belong to the H1 above it.
Group rows.
They what I get over here are three Group and Outline buttons.
If I go back to the number one, I just get the chapter heading.
If I expand that, I see all of the H1.
And then I can expand individual H1-s and see the H2s.
So setting up some sort of a an outline view in Excel.
Of course, that's easy in Word.
But, you know… Word.
My new book MrExcel 2021, Unmasking Excel.
Click the I in the top right hand corner for more information about that.
If you like these videos, please Like, Subscribe and Ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Making sense of indent levels in Excel and manually assigning groups.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's questions sent in by me.
I'm working on updating some books for Microsoft Press.
And the data that they give me is the old Table of Contents for the book.
So if I copy that from Word and then come back here to Excel and paste.
Look, there's three different types of data here.
“CH” is the chapter heading.
“H1” is major heading.
“H2” is minor headings.
But the only way that I can tell that is by counting the indent on all of those cells. Terrible, but here's a quick way to actually get the data so I can sort it or do something with it.
I'm going to switch over to VBA, alt+F11.
Here I am in this workbook.
I'll insert a module.
It's Get Indents.
We’ll say for each cell in selection.
Cell dot offset, I am going to use zero rows down and minus one cell to the right, which is the cell to the left.
Dot Value is equal to Cell dot IndentLevel.
That's where they store that.
Next Cell.
Something like that.
And we can run it.
This is a great little macro for putting your personal macro workbook.
And then we have 10, 11 and 13 which allows me to figure it out.
And actually, if it wasn't just a one-time thing, if I had to do this over and over again.
Then I do something like this where I use Select Case and depending on when it is 10, 11, 13, or 15, I would put the code over there.
And then.
If it's something I'm not expecting, it just puts the indent level as a number.
Alright, that's a little bit better.
Alright, so you know, I hate when people use colors to try and denote data.
They ask to add up all the red cells.
I have a video on that.
This time, it's Microsoft Press essentially using indent to try and tell me the difference between these.
Now look that was a really fast trick.
Let me show you one more awesome thing.
This was another question that came up last week.
I was doing a seminar at Creative Pro Week.
These are people that use Indesign.
They are text people.
They worry about text a lot.
And they wanted some text tricks and I said, yeah, we have something awesome back here on the Data tab.
In the far right-hand side called Group where you can manually group things.
But in order to use the trick that I'm about to show you, you have to come here to the dialog launcher.
And uncheck Summary Rows Below Detail.
Click OK.
And what I have essentially.
Is all of these cells here belong to the heading of “Transforming Data”.
So, Group.
By Rows.
And then all of these cells here belong to that H1 level called “Using Power Query”.
So Group, again in Rows.
And then here these cells belong to the item above it.
Group.
Click OK.
And then finally these cells belong to the H1 above it.
Group rows.
They what I get over here are three Group and Outline buttons.
If I go back to the number one, I just get the chapter heading.
If I expand that, I see all of the H1.
And then I can expand individual H1-s and see the H2s.
So setting up some sort of a an outline view in Excel.
Of course, that's easy in Word.
But, you know… Word.
My new book MrExcel 2021, Unmasking Excel.
Click the I in the top right hand corner for more information about that.
If you like these videos, please Like, Subscribe and Ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
