Excel Training – Tips, Tricks and Shortcuts for Duplicating Worksheets in Excel

Heres a taste of some of the amazing top tips, tricks and shortcuts included in our training sessions.

Hi guys, it’s Eoin here from InvestinU again with some great tips tricks and shortcuts for you today. We’re going to be using excel again, obviously, as you probably know at this stage, my favourite Microsoft Office application. Today’s tip again is probably one of my favourites, a really, really cool kind of trick to have in your armoury when it comes to working with excel. So, rather than talking about it, let’s get stuck straight in.

So, I’m just going to go over to my Excel spreadsheet here. So you can see I’ve got a very basic spreadsheet, no data in at the moment for our sales breakdown for InvestinU for 2020. Now, the first thing I’m going to say here is, well look I have this, I’m now going to start putting all my figures in for 2020. 2021 rolls around okay so, I now need the exact same spreadsheet with the exact same formatting all my lovely formatting, you can see I spent hours designing this obviously, my lovely formatting my logos and you can see I’ve got formulas in here too. 

So, obviously now I want to duplicate this now the most common way that people will do this when it comes to Excel and I’m sure a lot of you can relate to this one is you’ll highlight the data, yeah and copy and paste now as a lot of you know and I’m sure you’ve all done it at some stage is, when you copy and paste from one sheet to another, what happens well actually you find a lot of times you’re format doesn’t come over correctly. You’re calling with some natural fact what you do, you find yourself, you know, when you’ve copied and pasted it over you’re resizing everything again, so you’re spending five or ten minutes trying to tidy it up so it looks like we’re just coming across.

So, the tip I’m going to give you, an absolutely super one to have, Duplicating – what is the best way to duplicate, I mean and as I said there are lots of different ways you can do it. You can do the highlight, copy and paste that I mentioned. There’s also the other one that a lot of people would use and that’s come down to the worksheet tab 2020 here, right click and use the option here called “move or copy”, but then you’ve got to tick the boxes and scroll the copy, tell it where you want it to move to. So, obviously the table I’m going to give you is making it much easier and here it is.

Here if you select the worksheet, so I’m coming down to the worksheet, left click on it you can see the white sheet appears okay so I’m left clicking on the worksheet tab now just before I drag this. I’m going to move this, I’m going to hold down my control key at the same time now you might just be able to see that there’s a little plus sign appeared on that sheet now so I’m left clicking holding down my control key at the same time I’m going to drag this across one place. I’m going to keep the finger on the control key and let go of the left mouse button and look what it does. Absolutely, superbly duplicates the worksheet. So, all I now have to do is rename this one, double click 2021 I mean how brilliant is that? A simple look I’ll do it again.

Now, I need 2022 so I left click hold down the control key drag and drop and you can see now I can call that one 2022 and again you can just save yourself a whole heap of time when it comes to these things.

Here’s the other thing just to consider and this is one thing I talk to a lot of my clients about if I just get rid of these two sheets here I’m just going to delete those there so I’m going to go back to 2020. Now, I’m going to put in 2020 here and I’m sorry, I’m going to start putting in all my figures and formulas will start working away now 2021 rolls around so what happens if I then duplicate 2020? Which means I’m duplicating all the figures which means, I now have to go in and start deleting all these figures so I can start putting in my 2021 figures. So, my recommendation here just to add to this tip to make this tv a bit better, is to take that first sheet to create it with all your lovely formatting and logos and formulas and call that your template sheet. So, I’m going to call this one my template sheet watch so every time now watch I want to start putting my fingers in for 2020, left click control from the template look drag and drop call that 2020. Brilliant now, when I need a 2021 don’t duplicate 2020, go back to your template sheet left click control drag and drop and you’ve got your 2021. I mean how good is that believe it or not guys this tip gets even better okay I’m going to open up a brand new workbook. I’m just going to use my lovely shortcut key control and you can see completely unrelated now a couple of months, a couple of weeks, couple of days go by and you now need a nice formatted formulated little table and you’re thinking this up. You know what I’ve already done the hard work in a previous workbook so again what do most people do they’re back in here and they’re right-clicking they’re maybe moving the move or copy to choose the workbook and someone support is trying to copy and paste it or you can simply do this watch.

If I go into my view tab, I use a button here called arrange all. Now what arrange all those is any workbooks that I’ve currently open II can ask to tile them. I always go with the tile, the default and I click ok and it sits the two current workbooks that I have open side by side and just be careful here guys that if you had 10 or 20 workbooks open at the time of doing that guess what all 10 or 20. Look we’ll get  tiled in this case, last month’s or last year’s workbook this year’s one watch. This come over to my template left click hold on a control key, watch this you can drag and drop from one workbook to another I mean how good is that?

Microsoft Excel is an extremely critical tool in the world of business, education and home offices, but are you using it as efficiently as possible? Even if you are familiar with Microsoft Excel, the number and variety of keyboard shortcuts you can use to speed up your work may shock you.

If you find yourself constantly reaching for your computer mouse or your laptop’s trackpad, you are slowing yourself down and hobbling your typing speed, costing yourself time and energy in the long run. Learning how to correctly use keyboard shortcuts can save you a lot of this time and energy, while boosting your overall productivity. You can find out more about these shortcuts and much more about business, websites and Microsoft Office in our helpful Blog section.

If you found this tip on learning about Microsoft Excel helpful, InvestinU offers a range of training supports, including Webinar Excel Training, for Microsoft 365 which can be found here.

You can check out our Facebook, Instagram, or Linkedin pages for more updates and to get in touch today!