Replicating your spreadsheet layout…surely there must be a quick way?

This week’s hint and tip is about replicating your spreadsheet layout in Excel. These are topics mentioned on both our Basic Excel and Intermediate Excel courses and are firm favourites with attendees. Spreadsheets in businesses often contain several worksheets and so being able to replicate the spreadsheet layout can save a lot of time for you. Below we are going to look at 2 ways in which you can replicate the layout of a worksheet in Excel. This will save you having to create it from scratch each time you insert a new blank worksheet.

Group Sheet Selection

The first way to do this is by creating all the layouts to begin with in your spreadsheet. This can be done through Group sheet selection.

To do this route, firstly you will need to select all the worksheets in your workbook. Make sure you have 12 worksheets in your spreadsheet, this is going on the assumption that it will have one for each month. Then start off by selecting Sheet1, hold down the Shift key on your keyboard and select Sheet12. This will have now selected all the worksheets in your spreadsheet.

Now when you create your layout in Sheet1 it will copy across all the worksheets. Once you have finished creating your layout, hold down the Shift key again and select Sheet1. This will deselect all the worksheets and allow you to individually click on each worksheet. Notice how each one will have the same layout.

Move or Copy Option

The second way is to use the Move or Copy option. This option is often the most popular out of the 2 as you can use it on an existing worksheet or if you forgot to select all the worksheets to begin with.

To use this option, select the worksheet that you want to replicate the layout of and right hand click onto the name. From the list, select the move or copy option, select ‘move to end’ or the worksheet which you want it inserted before and tick the ‘create a copy’ box. This last step is very important as it will create a copy rather than just moving the worksheet. Click OK and you will now see the copied worksheet.

To create a copy of more than one worksheet at once, select the multiple worksheets that you want to copy and repeat the steps above.

The video below shows you a couple of ways in which you can replicate your spreadsheet layouts.

Watch the video below to find out more and then try it out on your own computer!

We hope you have enjoyed this hint and tip, why not take a look at our previous one on Borders and Fill Colour?

We carry out Excel training Warwick as well as other cities around the UK. Both of these features are found on our Basic and Intermediate Excel courses!

If you are interested in Excel training Warwick or Excel training near you, then please do get in touch!