How to safely hide and unhide rows and columns using the Excel group rows and column tool. Easy way to create a drill down capability in Excel.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Excel Group Rows
Most people know how to hide rows and columns in Excel so I can highlight those two rows, right click and hide it. But we believe that this is slightly unsafe in the sense that it is difficult to spot when there is some needed information. So we prefer to make use of the Excel group rows (and/ or columns) tool.
So I will just show you how it works, if I highlight these two rows. If I click Group you’ll notice it puts a new area in the spreadsheet with a little plus and minus – if I close it, it hides it – it shows a plus so I can work between those two. The reason we think this is better is because it is much more obvious that there is something hidden there.
So for example, I can now click on those two – I can group it and I can now easily open and close whichever one I want. The benefit of this feature and something that you can’t do with hide and unhide is I can actually nest these groupings. So I can highlight that whole area and when I say “Group” – it now has a third level so I can either see just the gross profit or I can open it up and see Sales and Cost of Sales or I can even go further into the underlying products.
The nice thing about this feature as well –is at the top here we have got one, two, three. If I click any of them – then all the groupings are closed. So instead of me manually closing things one by one- if I push one – everything closes – if I push two – this level is closed- that is opened – if I push three they are all opened. So this might be the onscreen view and that might be the printing view.
You can do the same thing with columns – so I will highlight a few columns – say group – the same item appears.
Another common query with this particular tool is whether it is possible for these plusses and minuses instead of them appearing at the bottom –see it appears at the bottom – can it appear at the top. The answer is Yes – so where you push the group/ungroup buttons you’ll see there is outline – if I click on that little button – there are a few options so you will see it says sum rows below detail- just switch if off – when I say OK – what will happen here it now sums it to the top.
To remove the grouping it is simple as you highlight it – click on group – group – and depending on what levels you want to remove – you can do that.