Often we come across workbooks that have similar formatting needs for multiple worksheets. For eg. you may have sales records spanning across 12 worksheets, one for each month. Now as a loyal reader of chandoo.org, you want to keep the formatting of all these worksheets consistent. So here is a quick tip to begin your work week.
- Just select all the different sheets (select one, then hold CTRL key and click on other sheet names).
- Now format any sheet and similar formatting will be applied to all selected sheets
- See this demo to understand:
The group & format technique is particularly useful when you,
- Want to apply same header / footer / print settings to multiple worksheets
- Want to write similar formulas in multiple worksheets (for eg. totals)
Do you use group sheets option?
I like to have consistent look & feel for all my worksheets. Especially if I am doing it for a client or for a product. So I find group sheets option pretty attractive and productive.
What about you? Do you use it? Share your tips & ideas with us.
More Quick Tips:
We have more than 60 quick excel tips that boost your productivity or introduce a new feature to simplify your work. Each of them is bite sized so you can learn quickly. Go on and consume a quick tip.
35 Responses to “Formatting Multiple Worksheets? Use Group Sheets option to Speed up [Quick Tip]”
Wow!
I've never ever heared of that really powerful tip.
Thanks (as always) Cahndoo 🙂
I use it, for sure, but I'd also throw out the caution that, as soon as you're done, UNGROUP them! I've destroyed more work by accidentally leaving sheets grouped than I care to remember.
It drives me crazy that every version of Excel has less contrast on the sheet tabs, which makes it harder and harder to tell if the sheets are grouped. I'm also not a big fan of how the grouped setting persists when the workbook is closed and re-opened.
It's a great feature for making consistency, to be sure, but people need to be aware of the dangers of sheets being grouped when you forget to ungroup them.
That is a very powerful tip, I spent hours just last week formatting things like this, except i was doing it one by one. Does anyone know if this works with formulas too?
Absolutely does, Josh. That's why my caution. Entering formulas, deleting data, inserting & deleting rows... it all takes effect on the grouped sheets.
Oh, that could be disastrous... but also very very helpful.
If you look at the file name at the top of the workbook, it will tell say Book1 [Grouped] meaning that tabs are grouped. Just a quick tip to tell if tabs are grouped or not.
Ken- I think everyone had the problem you mentioned.. I know that drove me crazy every one in awhile when I would forget to ungroup them. Microsoft finally made it easier in Excel 2007, as soon as you click on another sheet, they ungroup - AS LONG AS ALL THE SHEETS HAD BEEN GROUPED ORIGINALLY. If you had some grouped and ungrouped then yes, you need to be very cautious. Another reason for using this is that grouping holds the column and row widths which you would have lost if you had just copied everything onto other sheets.
Aha! Thanks Patricia, I missed that improvement. I guess I always deal with a few subsheets at a time. (Usually have a control panel page in my applications that is formatted differently.) I still wish the contrast on the sheet tabs was more obvious though. 😉
Conratulations Chandoo !
On your success story in MSN Education.
http://education.in.msn.com/news/article.aspx?cp-documentid=4583472
Great Job ! Keep going.
Yes very powerful feature but not without signifigant downside if you need to have some sheets slightly different and you have not ungrouped them as you point out Ken. Patricia Another way to get consistent column widths is to do copy/paste special/column widths but this doesn't help with rows - I believe there is another method to do this but I cannot recall it. Better contrast would help to alert you to the grouped selection
best trick dude::it's really awesome
Chandoo mentions that this is useful if you "Want to apply same header / footer / print settings to multiple worksheets". I've used this before for entering data on multiple sheets but I have not been able to get it to work for headers/footers. I often receive files that need to be formatted for printing and this tip would be great if I could get it to work. Any suggestions?
I love this feature when 1) I remember to un-group the sheets, and 2) it actually works. I can't even consider counting the number of times that I thought I'd reformatted all my worksheets only to move to another tab and find the formatting didn't "stick". Personally I don't like that sheets are un-grouped when I click on another sheet, I can't think of the reason why, but many times I need to go to another sheet in the middle of all the formatting and they become ungrouped. I also become very frustrated when page or print settings don't apply to all worksheets; it really seems to be a hit-or-miss thing. I must be doing something wrong somewhere 🙂
This function I would say, will only work perfectly if the data range is same in all the sheets which needs to be selected.
I am totally new to excell and computer so found this sight highly useful
It's really useful tip for everyone......
Great tool ... have used it lots!
If you get one worksheet totally formatted, easiest way to clone them is to right click, Move or Copy Worksheet, click on what order you want it inserted click the little box at the bottom left that says "Create a Copy" and click OK - it's cloned. Just do this a couple of times, then select the ones you already have cloned and do the right click, Move or Copy Worksheet and it clones that many at a time.
Brian, try this:
Select all the tabs you want to have the same footer, click on Page Layout ribbon, click the little box to the right of the Page Setup heading at the bottom of the Page Layout section and the old-fashioned Excel Page Setup option screen shows up.
Click on Headers & Footers, make the headers/footers that you want, and click OK. The headers/footers on all the selected tabs will be uniform!
Note: Make sure you unselect the tabs so any other changes you make don't show up on all those tabs!
Cheers (from snowy Seattle!)
Thank you for this lesson. I would like to learn how to apply VLOOKUP formula on a summary tab and the data are in each individual tab let's just say 20 tabs within one Excel file. Is that possible?
Thanks.
this was really a great tip .because i spent much by doing this on each sheet thanks chandoo.
First time on this site - will keep close eye on
I am a blind user of excel and this is an amazing tip, is there a way to do this with the keyboard? What about selecting non contineous worksheets in a workbook? Again using the keyboard, using the mouse makes it much simpler. Unfortunately not in a position to do so after losing eyesight.
@Firestorm
As a blind reader do you use "Reading" software to read Chandoo.org ?
Are the posts written in a style that is easily read?
Any other comments ?
Hui...
Great tip Chandoo!
I have been teaching Excel since 2003 version and never heard of this one.
Thanks a lot.
This is an awesome tip I have been searching for daily for awhile now. Not sure why or how I found this site at this time but I am so thankful for it.
Thank you all for insights, tips and pointers. It has been most helpful. I will continue to frequent this site often as the position I recently acquired uses Excel extensively.
Again, thank you everyone, You are all awesome!
awesome tip..
is it possible to do conditional formatting for multiple sheets also?
Its Helpful,
But whenever i click on other sheet the grouping get back to compatibility mode, so is there any option to keep sheet grouping even if we click on other sheets.
can you provide me Agewise based discounts tables in Excel
Hi- This is brilliant thank you! please can you help further? I have a workbook with 12 tabs (one for each month). I frequently need to make the same change to all, so I'm trying to use this method. Some cells are protected, so the group edit won't work. Is there any way of unprotecting all 12 as a group, in order to make changes, and then re-protecting them please?
I'm trying to handle something along these lines but with a twist. Backgorund, we areattempting to process logs of data in excell for debugging. e.g. 30 columns of data and hundreds or thousands of rows. Brute force approach, select column then apply 'highlight cells rules' to conditional format data in that column. Different columns get different rules to apply formatting. Ideally I could automate this with for instance a template to apply to a worksheet when I open the log file. I could define a macro but wonder if this is the recommended approach.
Hi Chandoo,
I am finding short cut key to group or ungroup sheets. Can you help me?
Wow! This helped.
You saved a lot of my time.
Thanks.
If you have lots of Excel documents to format and print http://www.rapidformat.com can help.
in Excel 2010, it's best to use 'black' theme. This way, the grouped sheets are clearly visible.
I'm trying to format multiple (110) sheets to fit on one page each, but when changing a scaling option on multiple sheets, the headers all change to the header on the first sheet selected. I want to scale each sheet (group of 110 sheets) to one page wide each without changing headers on any of the sheets. I can't find an answer anywhere. Anyone happen to know a resolution?
A very useful and comprehended page, i love it