A co-worker sent you a file with subtotals. How do you get rid of them?
Why are there 3 numbered buttons to the left of column A in Excel?
Why are there minus buttons and vertical lines to the left of column A?
This video shows you a quick way to get rid of subtotals that were added by a co-worker.
Why are there 3 numbered buttons to the left of column A in Excel?
Why are there minus buttons and vertical lines to the left of column A?
This video shows you a quick way to get rid of subtotals that were added by a co-worker.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2431 - Removing Subtotals.
Or why are there 3 numbered buttons to the left of column A?
Or why are there minus buttons and vertical lines to the left of column A?
How do I get rid of those?
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
Now back in episode 2428, we talked about Subtotals.
And one of the questions from a friend of mine was, “Hey wait a second.
I hate subtotals.
I never used them but I get them from people and I don't know how to get rid of them”. This is a great point, If you've never created subtotals before, someone sends you a workbook that looks like this.
It's going to be super confusing, right?
What are these extra numbers out here?
The 1, 2 and 3 and all of these minus signs and the subtotals.
Where did all this come from?
If you've never ventured out here on the far right hand side of the Data tab to the Outline group.
These are called Group and Outline buttons.
If you've never created Subtotals yourself, how would you know what these are or how to get rid of them?
And when you go looking for the Remove Subtotals button, it's not anywhere where you can find it.
Classic Microsoft.
Let's have a very useful command and hide it where no one can ever find it.
So what we're going to do is we choose just one cell in the data.
Just one cell.
Then come out here to the Data tab on the far right hand side.
Go to Subtotal as if you are going to add more subtotals.
And that's where they hide the box for Remove All.
When you choose Remove All, everything goes away.
The group and outline buttons go away.
All the subtotals go away.
The grand totals go away.
But there are ways that this can not work.
Right here, someone is creating subtotals and then either they manually added each blank row.
And I've seen people do this before where they just go through Insert, Row, Insert row, Rnsert row.
I have a video on YouTube that shows how to add these all at one time, although it's pretty convoluted.
You definitely have to be watching the keyboard in order for that to happen.
How do we get rid of subtotals when they're not all in one contiguous group?
Because if you would just choose one cell, Subtotal, Remove All, then it only removes that top subtotal.
What you're going to do in this case is select all data.
So Ctrl+Home to get to A1.
And then Ctrl+Shift+End to select to the end of the data set.
And then Data, Subtotal.
It says that it can't find it.
Click OK.
Remove All.
Right, and then we still have the blank rows that someone is inserted.
So Ctrl+End.
Ctrl+Shift+Home.
And now we can just do a sort by any column and that'll move all of the blank rows to the bottom.
I could certainly see how this would be super confusing if you have encountered this.
Perhaps a coworker or client sends this to you or you just inherit the workbook from someone.
And you're trying to figure out how to get rid of these things.
Terribly frustrating, right?
So that Remove All button is the quick way to get rid of them.
Now that you know where they are, let's go watch another video - I'll put a link in the in the YouTube description - to learn how to use subtotals.
Check out my book MrExcel 2021.
Unmasking Excel.
Lots of different details in here.
260 pages.
All of my favorite Excel tips and tricks, including a lot on subtotals.
Hey, if you like these videos please down below Like, Subscribe and Ring the bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Or why are there 3 numbered buttons to the left of column A?
Or why are there minus buttons and vertical lines to the left of column A?
How do I get rid of those?
Hey, welcome back to MrExcel netcast.
I am Bill Jelen.
Now back in episode 2428, we talked about Subtotals.
And one of the questions from a friend of mine was, “Hey wait a second.
I hate subtotals.
I never used them but I get them from people and I don't know how to get rid of them”. This is a great point, If you've never created subtotals before, someone sends you a workbook that looks like this.
It's going to be super confusing, right?
What are these extra numbers out here?
The 1, 2 and 3 and all of these minus signs and the subtotals.
Where did all this come from?
If you've never ventured out here on the far right hand side of the Data tab to the Outline group.
These are called Group and Outline buttons.
If you've never created Subtotals yourself, how would you know what these are or how to get rid of them?
And when you go looking for the Remove Subtotals button, it's not anywhere where you can find it.
Classic Microsoft.
Let's have a very useful command and hide it where no one can ever find it.
So what we're going to do is we choose just one cell in the data.
Just one cell.
Then come out here to the Data tab on the far right hand side.
Go to Subtotal as if you are going to add more subtotals.
And that's where they hide the box for Remove All.
When you choose Remove All, everything goes away.
The group and outline buttons go away.
All the subtotals go away.
The grand totals go away.
But there are ways that this can not work.
Right here, someone is creating subtotals and then either they manually added each blank row.
And I've seen people do this before where they just go through Insert, Row, Insert row, Rnsert row.
I have a video on YouTube that shows how to add these all at one time, although it's pretty convoluted.
You definitely have to be watching the keyboard in order for that to happen.
How do we get rid of subtotals when they're not all in one contiguous group?
Because if you would just choose one cell, Subtotal, Remove All, then it only removes that top subtotal.
What you're going to do in this case is select all data.
So Ctrl+Home to get to A1.
And then Ctrl+Shift+End to select to the end of the data set.
And then Data, Subtotal.
It says that it can't find it.
Click OK.
Remove All.
Right, and then we still have the blank rows that someone is inserted.
So Ctrl+End.
Ctrl+Shift+Home.
And now we can just do a sort by any column and that'll move all of the blank rows to the bottom.
I could certainly see how this would be super confusing if you have encountered this.
Perhaps a coworker or client sends this to you or you just inherit the workbook from someone.
And you're trying to figure out how to get rid of these things.
Terribly frustrating, right?
So that Remove All button is the quick way to get rid of them.
Now that you know where they are, let's go watch another video - I'll put a link in the in the YouTube description - to learn how to use subtotals.
Check out my book MrExcel 2021.
Unmasking Excel.
Lots of different details in here.
260 pages.
All of my favorite Excel tips and tricks, including a lot on subtotals.
Hey, if you like these videos please down below Like, Subscribe and Ring the bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
