If you like keyboard functions: (I use this ALL the time) Alt-Shift-(right arrow) = Group highlighted rows/columns Alt-Shift-(left arrow) = UnGroup highlighted rows/columns. Even ungroups selected row/column from the current group. Alt-Shift-(up arrow) = Collapse Group this selected row/column is in. Alt-Shift-(down arrow) = Expand Groups which are selected.
Oh. my. goodness. I had no idea I could be one of those "nerds" who loves spreadsheets, but here I am LOVING having gained a skill programing a spreadsheet to save me time and make something look professional. Thanks for sharing this video, it really helped. I have no idea what a "function number 9" is, but I used it and it's working perfectly!
In case anyone is experiencing the +/- signs appearing at the bottom of the group, right click the grey area below the sign and select "move the button +/- up".
THANK YOU DEAR SIR. Its been months I was looking for something that will efficient my work. that was EXACTLY what I needed. I was going back and forth from using sheet VS docs (since on docs you have the "title" feature which allows you to see all titles only without the date inside. made it easier to navigate through your data. ) but it was not working for me regards of the tables.. sheets is just much convenient for tabling content. I knew there must be something in sheets that will allow be to navigate easier and show all "title" without the data inside. but didn't know how its called.. NOW I DO ! youre the best. thank you.
I spent way too long trying to figure out how to hide rows. Some clunky methods coding within the sheets and then in the script editor. I didn't realize it was this easy, I feel like an idiot, haha. At least I found my answer.
If you then sorted these A-Z ascending or descending, would it mess up the groupings? I'm creating an inventory master list for my piano books. Each book contains multiple songs or movements. I want to list all of the songs in an individual cell so I can look it up in the future, but I want to collapse all the song titles into the books. I'm not sure I'm making sense. Basically, I want to search by book first, then be able to look at the title info next, but make it hidden until I need it. Any help would be most beneficial.
Thanks for the video! I've been using this group on my columns however I have a problem when sorting all the information to A-Z as the group columns stay where they are but the information moves leaving the group where it originally was. I want it to move with the info inside it all together when I use the A-Z but I cant figure a way to do it, is there anything I can do?
Great Video!! Thanks for sharing your knowledge! Is there perhaps a way to do this faster if the data in one field is the same? In excel we were using the Data > Sort & Filter > Advanced > to combine unique records. When we convert our excel to sheets to sheets we can see the rows combine but not in the same Group Rows ability so not as easy to expand & collapse rows
@1:06 the group option doesn't appear for me when right-clicking 😢anyone else know how to do this in a different manner? Thanks for sharing your method!
For some reason, after I group the first batch of rows, upon grouping the second , it adds it to the first group. Example: I can group rows 2-20 just fine. If I try to then group 21-40 as a separate group, I end up with only on group from 2-40.
Humph. Great question. I was thinking search would work, but it appears it doesn't! I am going to look into it and, if I find a cool answer, that'll be a new video for this channel!
@@ProlificOaktree when I add a chart to a range and then group the range to minimize... the chart does not minimize it's not tied to cells it floats on top of the rows/columns. How can I fix a chart to he cells so that when I minimize a set of columns that the chart along with other data is hidden?
Google Docs wouldn't have this built into it. If you are doing something in a table that is involved enough to have the need to group rows or columns, you should consider doing it in Google Sheets and then embedding the table into docs. See how to do that here: ruclips.net/video/x2lBkAhmeEA/видео.html
undo? This doesn't really work well unless you sort within ranges, not entire columns as the totals and the individual amounts in the cells are 'different' and shouldn't be part of a rank (one is a value to be counted, another is a sum). Select the rows you want to sort, then go to Data > Sort Range.
Why haven't I been doing this.... why didn't I think of the possibility sooner. All my hours and hours of course writings, all neatly grouped, but without using this hide/show feature.......................RRRRRRRR
![BAK Jay - Find Out (For Your Love) [Official Music Video]](http://i.ytimg.com/vi/10FJO_XBCvY/mqdefault.jpg)
Great ! Exactly what I was looking for. Thanks !
Except that now, 2022, Sheets has moved that option to "right click->More row options->Group rows"
Thanks, good find. I pinned your comment hoping that it will help others.
If you like keyboard functions: (I use this ALL the time)
Alt-Shift-(right arrow) = Group highlighted rows/columns
Alt-Shift-(left arrow) = UnGroup highlighted rows/columns. Even ungroups selected row/column from the current group.
Alt-Shift-(up arrow) = Collapse Group this selected row/column is in.
Alt-Shift-(down arrow) = Expand Groups which are selected.
Whoa, cool. Thank you!
my hero
Oh. my. goodness. I had no idea I could be one of those "nerds" who loves spreadsheets, but here I am LOVING having gained a skill programing a spreadsheet to save me time and make something look professional. Thanks for sharing this video, it really helped. I have no idea what a "function number 9" is, but I used it and it's working perfectly!
The solution was as I wanted. Thank you!
Exactly what I was looking for. Thank you!
In case anyone is experiencing the +/- signs appearing at the bottom of the group, right click the grey area below the sign and select "move the button +/- up".
Thanks for this tip. I fiddled around for 10 minutes without finding this!
Thank You VERY MUCH!!!! I was starting to go batty trying to figure this out!
Thank you so much !
This is what I was really looking for. Thank you!
Thank you, it was driving me crazy until I scrolled down to the comments
Ive been thinking about programming this forever lol. This is amazing!
Sweet!
Exactly what I was looking for. Thank you! Great video.
Thank you so much! Exactly what I needed and it was a very quick and on point explanation! 🙏
You're a life-saver! Thank you very much 🥰🥰❤️
So helpful, exactly what I needed.
So glad!
I have been looking for a way to make a clickable button to jump to an specific cell, but this also works for my goals. Thanks
THANK YOU DEAR SIR.
Its been months I was looking for something that will efficient my work.
that was EXACTLY what I needed.
I was going back and forth from using sheet VS docs (since on docs you have the "title" feature which allows you to see all titles only without the date inside. made it easier to navigate through your data. )
but it was not working for me regards of the tables.. sheets is just much convenient for tabling content.
I knew there must be something in sheets that will allow be to navigate easier and show all "title" without the data inside. but didn't know how its called..
NOW I DO !
youre the best.
thank you.
Well, glad I could help you out. Sheets will almost always be easier if you have a lot of tabular data.
When i right click, the options does not appear for me on one computer but does on another (both times with my account)...how can i fix it?
Thanks for this!
Great video, quick and informative
Appreciate it!
Very helpful - thank you!
Thank you! You solved my issue 🙃
Glad I could help!
Thank You!
Perfect - thank you
Glad you like it!
Thank you so much, exactly what I was looking for
Great, glad it helped.
Thank you for posting this video - very clear guide!
Thank you!
0:55 for the answer
select the rows you want to group > right click > group rows
great !! useful!
thank you
Thanks for this....
best vid on youtube lol, thank you so much
Glad you liked it!
Awesome
thank you! this was a huge help!!
Awesome, I'm glad it helped.
Well, well, well.... Very helpful. Had no idea you were a youtube star. :). B
Hey, good to hear from you! I hope all is well.
I spent way too long trying to figure out how to hide rows. Some clunky methods coding within the sheets and then in the script editor. I didn't realize it was this easy, I feel like an idiot, haha. At least I found my answer.
This is sooooo good. I was looking for an option to group under heading for each months calendar.
Awesome, glad it helped.
This was the main feature in SmartSheet that I was using and now I don't have to pay $16/mo for it anymore. Thanks!
You're welcome, glad it helped.
super helpful. Thank you !!
You're welcome!
Great video! Thank you
Perfect
If you then sorted these A-Z ascending or descending, would it mess up the groupings? I'm creating an inventory master list for my piano books. Each book contains multiple songs or movements. I want to list all of the songs in an individual cell so I can look it up in the future, but I want to collapse all the song titles into the books. I'm not sure I'm making sense. Basically, I want to search by book first, then be able to look at the title info next, but make it hidden until I need it. Any help would be most beneficial.
Thank you! That was really helpful.
Thanks, glad to hear it helped.
Good information, thank you.
Great! Thank you!
You're welcome!
Thanks for the video! I've been using this group on my columns however I have a problem when sorting all the information to A-Z as the group columns stay where they are but the information moves leaving the group where it originally was. I want it to move with the info inside it all together when I use the A-Z but I cant figure a way to do it, is there anything I can do?
Anyway, to group by automatically by value?
THANK U !!!!
U welcome!!!!
Great Video!! Thanks for sharing your knowledge! Is there perhaps a way to do this faster if the data in one field is the same?
In excel we were using the Data > Sort & Filter > Advanced > to combine unique records. When we convert our excel to sheets to sheets we can see the rows combine but not in the same Group Rows ability so not as easy to expand & collapse rows
Maybe the UNIQUE function would help you out?
sheetshelp.com/filter/unique
@1:06 the group option doesn't appear for me when right-clicking 😢anyone else know how to do this in a different manner? Thanks for sharing your method!
helpful
This is great! Is is possible to label the column
groups so you know what is hiding?
Not that I know of
When I open one group, is there a way to make all other groups to automatically collapse?
Not that I'm aware of.
can you still expand and collapse those rows if it is protected range?
Yes
I can not make two separately adjacent group in google sheet!
if we have 50 years data, any shortcut to group all at once year wise or have to do manually one by one???
I don't know of a shortcut to do this kind of grouping. If there was one, it would be something you would have to code with Apps Script.
gracias!!!!
When i right click the mouse i don't have the expand or collapse options that you have. What can i do?
You have to be over the top row of letters or left column of numbers to get the option when you left click.
For some reason, after I group the first batch of rows, upon grouping the second , it adds it to the first group.
Example:
I can group rows 2-20 just fine. If I try to then group 21-40 as a separate group, I end up with only on group from 2-40.
its because every grouping must have a header man.
What would be the best way to add the totals for ALL Warehouse Supplies for the year?
Using the SUBTOTAL function works well as it ignore the totals from other SUBTOTAL functions. The formula in cell E32 is =SUBTOTAL(9,E2:E31).
I exactly get what I was looking for and more ! How I did not know about SUBTOTAL() !!! #LifeChanger
Yeah. Use it like a boss now!
Can you show how to group alternate columns?
great it help me
Cool, glad to hear it.
Is there a way to allow access to expand and collapse to people that can only view?
I don't think so.
how you made this table & placed a total of specific cells at the end? any video you have? .....I can see you did that without pivot table!
I just made it manually and then added the subtotals. Here is a page with the link at the bottom. www.prolificoaktree.com/group-rows-and-columns/
Is there a way to automatically group and subtotal in Sheets? Group by day, month, year, etc. that would be great. I'm sure i've done it in excel.
Yes, if you create a pivot table, you can do it inside of the PT. See here ruclips.net/video/K0pf2s4o3Hg/видео.html.
is there a way to Put a lebel next to the +/- sign?
I don't think so.
anyway to create groups dynamically based what is in the first cell?
Not that I can think of.
Actually the grouping option is not working like this anymore.
To group, Select the rows and click on (View) at the top then select Group.
It's still in the right click menu, but now you have to go to "View more row actions" to see it.
is this an add on? I dont have the group option in my right click menu
It's a new option that is being rolled out over the next few weeks.
Nice! it actually just showed up when I refreshed the page
How to find out/search data after the collapsed row group?
Humph. Great question. I was thinking search would work, but it appears it doesn't! I am going to look into it and, if I find a cool answer, that'll be a new video for this channel!
excellent
Thanks!
@@ProlificOaktree when I add a chart to a range and then group the range to minimize... the chart does not minimize it's not tied to cells it floats on top of the rows/columns. How can I fix a chart to he cells so that when I minimize a set of columns that the chart along with other data is hidden?
@@THE_CHOAS_ENGINE I don't know of a way that you can do that.
@@ProlificOaktree thank you for considering the inquiry.
How can I group/ungroup columns in a protected sheet?
Depends what the protected settings are. If it doesn't seem like you can do it, it's probably turned off by the Sheet owner.
@@ProlificOaktree I am the sheet owner. how can I check that?
@@emiliaalejandrakuschnir5199 Maybe see here: ruclips.net/video/snL40k9-698/видео.html
How do you do this in google docs?
Google Docs wouldn't have this built into it. If you are doing something in a table that is involved enough to have the need to group rows or columns, you should consider doing it in Google Sheets and then embedding the table into docs. See how to do that here: ruclips.net/video/x2lBkAhmeEA/видео.html
How to filter grouped data?
No great way to do it that I can think of.
This worked great for me...and then I sorted another column, and it all got messed up and no way to get it back to the way it was.
undo? This doesn't really work well unless you sort within ranges, not entire columns as the totals and the individual amounts in the cells are 'different' and shouldn't be part of a rank (one is a value to be counted, another is a sum). Select the rows you want to sort, then go to Data > Sort Range.
Jordan Young said it best. Scratching my head on the 21 thumbs-down?
Yeah, I typically save my thumbs down for videos that are downright misleading. Oh well!
Why haven't I been doing this.... why didn't I think of the possibility sooner. All my hours and hours of course writings, all neatly grouped, but without using this hide/show feature.......................RRRRRRRR
Just to think of all of the wasted hours of your life that you'll never get back. Ugh!
Exactly what I was looking for. Thank you! Great video.
Thank you, I'm glad it helped.