Search Not Just Numbers

Loading...

Tuesday, 26 November 2013

Excel Tip: Making the same change to multiple worksheets

Sometimes we build an Excel spreadsheet that has a number of worksheets (or tabs) that are the same layout.

No matter how well we have designed our spreadsheet, there are times when we need to make updates. This can be further improvements or changes to reflect changing requirements.

This can be a time-taking task if the changes are to be made to multiple worksheets. Fortunately, Excel has a way to do this.

First of all, I think it is worth mentioning that some of the reasons we have multiple worksheets are due to bad spreadsheet design. For example, having a sheet for each month is not usually the best way to handle most applications. In most situations it is far better to have a sheet that contains all of the data and a report sheet that allows you to report that data for any month. For my advice on laying out data, take a look at this earlier post.

Assuming that you have decided that you do need multiple sheets (or that you already have them and are not going to spend your time changing a spreadsheet that works!), then you can make Excel apply the changes to each of the sheets that you want it to.

First of all, click on the tab name of the first of the sheets that you want to edit (as you normally would to make that the active sheet). Then, hold down the Ctrl key while you select the other sheets you want to edit too.

You should now see that the backgrounds of the tab names of the selected sheets are all white.

While these multiple sheets are selected in this way, any change you make to one of the sheets will be applied to them all.

The really important thing to remember though, is what you need to do when you've made the changes:

Right-click on the tab name of one of the selected sheets and click "Ungroup Sheets"

If you don't do this, then you can imagine the chaos you can cause when you type anything into one of the sheets and it changes them all!

This can be really useful, as long as you remember that last step!

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

42 comments:

  1. I agree that it is a powerful bit of functionality but have been burned more than once when selecting multiple (non-identical) sheets for printing and then continuing to edit the visible sheet.
    Perhaps a warning message would be a good idea for future versions of Excel?

    ReplyDelete
  2. I also agree with the premise of changing multiple sheets as changes are made to a single sheet (done by selecting multiple sheets). But it's fraught with peril, because a person will very likely make changes to one sheet that are inappropriate for another. The sheets MUST have identical layouts or changes can cause problems.

    ReplyDelete
  3. Like what you guys have going on here, what a great site. Im always on here seeing the latest news and updates.

    ReplyDelete
  4. Thanks everyone for emphasising the final point in my post (because it needs emphasising) - Don't forget to Right-click on the tab name of one of the selected sheets and click "Ungroup Sheets"!

    ReplyDelete
    Replies
    1. Hi Glen,

      I wonder if you can help me - my sheets are acting as if they're grouped (ie when I paste into one sheet, it copies into them all in the same cell). However, when I right clock they do not show as grouped/there is no option to ungroup. I have never grouped them before, except to select all for the search function. Any advice would be greatly appreciated!

      Thanks,
      Angela

      Delete
    2. Angela - could the workbook be protected?

      Delete
    3. Hi Glen,

      I might be missing it but is it possible to have for example Sheet1 A3 copied into Sheet2 I8?

      Delete
    4. you can copy stuff wherever you like, but this has nothing to with grouping sheets - or am I missing something?

      Delete
    5. John - I'm not sure what you are asking. As the commenter above said, you can paste anywhere you want. Are you suggesting grouping sheets in such a way that the linking is not between specific cells in those sheets that are not in the same place? I'm pretty sure that's not possible, not without code anyway.

      Delete
  5. The act of grouping worksheets is a powerful tool, but it requires the user to make sure to ungroup the sheets once they're done doing the universal changes that impact the entire group of sheets. I consider myself a power-user of Excel, and I've been caught a couple of times. Not fun.

    ReplyDelete
  6. Glen -- Excellent tip! I tweeted it. You and your readers might be interested in my website -- www.painting-with-numbers.com -- and book of the same name. Keep up the good work. @Randall Bolten

    ReplyDelete
    Replies
    1. Thanks Randall, I'll have to take a look at your site.

      Delete
  7. Didn't work for me

    ReplyDelete
    Replies
    1. If you post what you were trying to do, I might be able to help.

      Delete
    2. I don't know who made that comment before, but it didn't work for me, either. I wanted to begin by simply making all of my worksheets zoom out from 100% to 75%. Selected every worksheet as you instructed, then zoomed out; only the first one did it. Also, when I was holding ctrl and selecting the extra worksheets, they didn't turn white in the background, but their little tab outlines darkened. Any suggestions welcome! Thanks!

      Delete
    3. This comment has been removed by the author.

      Delete
    4. And, just to look silly, I got it to work. When the little tabs didn't turn white for me, I selected them all over again, to see if I'd messed it up. Well, you know what happened! I was actually de-selecting them! I tried it again, and it worked fine! Thank you for a great tip!!

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. I think it is worth mentioning that some of the reasons we have multiple worksheets are due to bad spreadsheet design. GetSomeDosh

    ReplyDelete
  10. I changed one sheet first to make sure it all fits and prints up after converting to pdf. Now I need to add those formatting changes to all the sheets. But I don't want words and numbers to change in those other sheets just the size of the cells. Which paste do I use after selecting the sheets? The first one is ValueV, Formulas, Transpose or Formatting? I am using Excel 2013. My biggest gripe about working with all this is that when I turn a PP or Excel into an Acrobat pdf and print in actual size, Adobe changes and cuts off parts of the page. I am so fed up with Acrobat not printing the area it shows. I now have to go through all my documents and print them up after turning into a pdf to see how off the conversion went. I choose actual size as the method to print from since some may not have the option of selecting fit to page with their printer. Most folks have Adobe but I am sick of the way Adobe is not showing a reliable print preview. I assumed what showed with the pdf is what would print up. Not so. Grrrrr.

    ReplyDelete
  11. I'd like again to repeat the reiteration once more:

      DON'T FORGET TO UNGROUP!!

    I do this by pressing shift and clicking the current sheet tab (like with cells, shift extends selection from current to clicked item, control adds item to selection)
    but Excel really could make it more obvious that multiple sheets are selected

    also NB that Undo doesn't always work satisfactorily, so don't rely on that

    Jim

    ReplyDelete
    Replies
    1. I'd agree. You can't say it too many times!

      Delete
  12. I have been using excel for many years. Strugglled with this one a lot.
    Thank you very much for this.
    Chintan

    ReplyDelete
  13. Thanks for this excellent and time saving tip-they didn't cover this on any of my excel courses...

    ReplyDelete
  14. This was always valid in 2003, but it doesn't seem to work in 2013 with some commands: eg. Group/Ungroup rows/columns; Freeze panes;... This is a pain.

    ReplyDelete
    Replies
    1. I think that's because all of those commands are not changing the worksheet - just how it is viewed. It should still work fine for changes to the worksheet itself.

      Delete
    2. i'd noticed that the Group/Ungroup doesn't work across worksheets but you can get round this by using Hide/Unhide instead; the effect is the same, just not as covenient
      as for freezing panes, I have the button on the QAT and, because these still work when ctrl-clicked, all i do is select the correct freeze point across sheets, ungroup then position the mouse pointer over the freeze icon, hold ctrl and click-PgDn-click-PgDn-click-PgDn-click-PgDn-click-PgDn-click-PgDn-click-PgDn-click-PgDn-click…

      Delete
  15. It isn't working for me. I'm trying to show staffing trends over multiple pay periods. So I have a sheet for each pay period. Each sheet has 14 identical tables. So all the sheets match, they just have different numbers in them. I want to add a sum function across all sheets, instead of applying the sum function to each individual sheet. But when I try to do what you suggested, this message appears: "Cannot make changes to a table or XML mapping when multiple sheets are selected." I also tried Format Painter and it didn't work either.

    Help please!

    ReplyDelete
    Replies
    1. Never mind. I guess one can't perform that action with tables. I would have to go through each sheet and convert each table to a range. And if that's the case, I might as well go through each sheet and add the sum function. Booooo.

      Delete
    2. Sorry I didn't get back to you in time, but that's pretty much what I was going to say anyway!

      Delete
    3. That's okay, I didn't give you much time. I just didn't want you to waste your time researching it.

      Delete
    4. not experimented doing this with tables but…
      enter your function on one sheet, select that cell across the other 13, reselect the first sheet and hold down ctrl
      now press C once then PgDn, V 13 times

      not elegant, but quick and easy

      j

      Delete
    5. but the question that really should be asked is:
      Shouldn't this be just one table with an additional "Pay Period" column?

      j

      Delete
  16. If it is just for ease of printing particular columns and/or rows for a hard copy document it may be easier to just "hide" unwanted data.

    ReplyDelete
  17. @Angela/Glen above (for some reason I can't use the Reply against that thread)
    Protecting doesn't seem to affect that; what it might be is that Angela has multiple sheets selected in the workbook she is copying from - when a single sheet is selected in the target, then a multi-sheet copy is copied across sheets in the target from that sheet (if sufficient exist)

    hope that makes sense

    jim

    ReplyDelete
    Replies
    1. Thanks Jim, that sounds a plausible explanation.

      Delete
  18. dear sir.. my question.is... (i have create something 230 sheet).. how to do this if i changing in first sheet .then automaticaly changes in all sheet.for example if i put any value in A1 then automatically all sheets A1 fill..the same value.... plzzz answer me soon..on.my gmail id.. princesandy786@gmail.com

    ReplyDelete
    Replies
    1. The technique in the post should do what you are after for a one-off change, but you will have to group the sheets first and then ungroup when you are done. You can group multiple sheets by clicking the first one and holding down the shift key and clicking the last one - this will select all of the sheets in between.

      If you need this to happen all the time, why not put a formula in cell A1 of all but the first sheet (Sheet1, say), being:

      =Sheet1!A1

      Delete
  19. I keep my login passwords in an Excel spreadsheet. I have just cleaned out 10 years' worth of these spreadsheets and have just one master sheet that I have copied in 4 different directories. Is it possible to link sheets in different directories or must they be in the same directory or workbook?

    ReplyDelete
    Replies
    1. To change multiple sheets as in this post, they must be in the same workbook.

      Delete