ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
Excel Group & Outline Buttons | Easiest way to Hide & Unhide Rows & Columns
ฝัง
- เผยแพร่เมื่อ 5 ส.ค. 2024
- Quickly hide and unhide rows and columns with group and outline toggle buttons. Donwload the Excel example file here: www.myonlinetraininghub.com/e...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
0:00 Introduction
0:23 Manually Group Rows and Columns
2:34 Automatically Group Rows
3:18 Automatic Subtotals
5:00 Tips for working with Grouped Data
5:38 Hiding Group and Outline buttons
Love the beginner excel tips just as much as the complicated ones! Excel is so large, even the most savvy excel users can miss the most simplest functions!
Very true 😊
Thank you Mynda for this very clear explanation! I would just add that it is possible to choose to have the "+" or "-" signs at the top or at the bottom of the grouped lines as well as on the left or on the right of the grouped columns by clicking on the little arrow of the Outline box of the ribbon.
Great tip, Olivier! Thanks for sharing 😊
Hi Mynda. Great tips! I group and ungroup data frequently and so use the keyboard shortcuts: SHIFT+ALT+RIGHT ARROW (to group) and LEFT ARROW (to ungroup). Saves a little time if you do it often. Thanks for the tips! Thumbs up!!
Nice! Thanks for the tips, Wayne!
thanks Mynda nice crisp explanation of group, ungroup and subtotal features.
Glad you enjoyed it!
Very useful video as usual! Thanks Mynda! While I knew this feature, I did learn new things about them! 😊👍
Great to hear, Vijay!
Thanks Mynda, your explanations and tips are always on point.
😉
Glad you think so, Larry!
Thanks Mynda, this was very useful!
Great to hear, Chris!
You are the best in explaining the Excel option,
Glad you think so!
Thank you! Other than the useful info, really liked the calm, non-dramatized, style of presentation that's becoming a rarity on TH-cam 💚
Awesome, thank you!
that is the solution I'm looking for, thank you
Hi Mynda!Really Helpful Tutorial...Thank You :)
Great to hear, Darryl!
Thank you for the video, all of your videos are valuable. That being said In my personal day-to-day workflow when I am sent a sheet with groups the first thing I do is remove them and never look back.
Yeah, I don't blame you as they do take up a bit of space.
Same thing here!!!!!!
Was looking for the same on today. It really helped
Great to hear 😊
just great, thanks for sharing
My pleasure 😊
Thank you !!!! makes my excel more neat
Glad to hear that!
Excellent... Thanks.
Glad you liked it!
One additional not-so-obvious tip is clicking on the little call-out symbol in the lower right corner of the Outline group that contains the Group/Ungroup/Subtotal ribbon buttons. It allows you to position the outline symbols below or to the right of the grouped data if that is your preference. (I see another user already shared this same tip. See how popular it is!)
Thanks for sharing, Patrick!
Very good L :)
Many thanks!
Excellent, thank you! Is it possible to move about the different sections as you would do in Word Outlines? I don't think so, but its always better to check with the experts!
You can cut and pasted grouped rows to other locations, or you can reselect the rows/columns to include additional ones and apply the grouping again.
@@MyOnlineTrainingHub Thank you for your reply, that's very kind of you!
Like almost everybody else I’ve been using grouping like forever - can indeed be useful.
But they also have drawbacks: e.g. when you remove/add a row/column the grouping gets messed up.
And still, most of us keep using them. :-)
Yeah, they're not perfect and being a super old tool, they're not likely to ever be improved.
Thanks - I want my users to close and expand the groups so that they can drill down, or review each section of data one at a time - however I also need to protect to sheet to protect the data/formulas but this seems to disallow the group open and close - how can I allow the open and close, but protect the sheet?
Hi Janice, at the Protect Sheet dialog box check the box for 'Format rows'.
Amazing - thank you! Seems obvious now
I love this feature. Is there a way to show the level number buttons when hiding the headings?
Glad you like it! You can't change the way the group buttons display when headings are hidden.
@@MyOnlineTrainingHub Thank you very much for the reply :))
I'm looking for a way to group individual columns but have the ability to open/close them at the same time. For example, columns A,C,E,G Id like to group or have a way to make them visible or hide with one touch of a button. Is it possible?
If the technique shown in this video isn't suitable, then you'd have to write some VBA code and link it to a button to automate that task.
I use a group to hide some columns I don't need to see most of the time. It works geat, but I've added a form control checkbox to turn an option on and off. Unfortunately it doesn't hide along with the cells in the group. Is there a way to do that? I am using Excel 2021.
Unfortunately, no. These are very old tech and don't work nice with all features. However, there are new checkboxes coming that will change that for 365 users: th-cam.com/video/MB9DWvHXQLk/w-d-xo.html
Is there a way to group adjunct rows separately without the groupings combining? I've seen files that contain these separate groupings but I don't know how to do it. Thank you!!
You need to leave a row between each group to prevent them becoming one big group.
Thank you!! @@MyOnlineTrainingHub
Ctrl+8 - this is a shortcut to show/hide the group buttons. Avoids having to visit the options menu
Nice tip, Moxie! I probably won't remember it, but a good one if you use them all the time.
How to do it for rows or coloumns when it is every third row/coloumn that has to be hidden/grouped? Excel says grouping only works for one area at a time
Yes, it's a labour of love. Unless you write some VBA code to automate it for you.
Madame, Hi! I work in a hospital. may i kindly ask how do you group in excel a patients name or record number, although they have a different admission & discharge dates?
Hi Karen, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
CTRL 8, hides outlines symbols as “+”. So easier then shown in video.
Thanks for sharing, Victor. Great for those who can remember shortcut keys that aren't used very often...I'm not one of those 😁
How can I group/ungroup columns in a protected sheet?
There's no option to allow for that, sorry.
when you select row 3 - 6 for grouping how row 2 becomes the header. is there any setting we need to do
It all comes down to which rows you select to group. If you want row 1 as the header, select rows 2-6 etc.
But plus singh are showing below the header line
Once some rows are grouped, is it possible to FILTER and keep the grouped rows together?
Yes, you can apply filters to grouped data.
@@MyOnlineTrainingHub That is really good news! But I must be doing it wrong, because when I filter, the rows grouped under the main group are filtered out. How do I keep the rows together?
It's hard to visualise your scenario, Tom. I tested it on my PC, but maybe I was doing something different to what you meant. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub You are wonderful, Mynda, thank-you for your attention to this. I will modify the file and post the question and sample file in the forum. I appreciate your help!
I selected 2,3,4 and create group but that plus sign shows in 5 as header not 1 ...
Not sure what you mean, sorry. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
5:16 Also F5 🙂
Thanks for sharing. For those wondering, F5 is an alternate keyboard shortcut for opening the Go To dialog box. I prefer CTRL+G because it's easier to remember...my memory is not that good 😁
@@MyOnlineTrainingHub And I never knew about Ctrl+G 😆
They are not very useful for reporting .can be very confusing for end users
Yes, they need some user training, but once shown they're easy enough.
Hi
I’ve PQ challenge
The daily business hours:
Start from 8:00:00 AM
End time 10:00:00 PM
The challenge
1- is to change any transaction that comes between
10:00:00 PM and 11:59:59 PM
to the start of work hours (Next Day-Date) 8:00:00 AM
2- is to change any transaction that comes between 12:00:00 AM and 7:59:59 AM to be 8:00:00 AM
the reason I want to evaluate the number in business Hour also don't missing any transaction that comes after or before
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
0:33 It's not true
Yes it is! Try it. Group rows 1:5, then group rows 6:10, you'll see they end up in a single group.
@@MyOnlineTrainingHub Mynda, beg your pardon, but I didn't quite get why you chose these ranges. 😕If "Abundan Sell" range was in A7:C12, then first I would select rows 3:6 to create first group and then select rows 8:12 to create second group. This is what I meant. You select all rows except the header. For it there mustn't be any empty cells. 🙂