Guide To Inserting Duplicate Worksheets in Excel

Sometimes you can spend ages getting a worksheet right in Excel; everything formatted as you want it, all the formula working properly.  If you want the same/similar layout in another worksheet in your workbook, to save time and angst the best thing to do is to make a duplicate of your original one.

If you have bought your Business Plan from BusinessPlanWiz, you will also have been able to download a Cash Flow spreadsheet.  These instructions are for duplicating a worksheet in that workbook, but can be applied to any spreadsheet that you have; simply substitute your own sheet names and data for the worksheet that you are duplicating.

Duplicating worksheets:
First, copy the existing worksheet. There are two ways of doing this:

  1. Left-click on the Cash Flow tab at the bottom of the worksheet, press and hold down Ctrl and pull the tab to the right.  You should now have another worksheet called Cash Flow (2) in your workbook.
  2. Right-click on the Cash Flow tab at the bottom of the worksheet.  From the short menu, select Move or Copy. Select Move to End, then tick the box Create a copy and click OK.  You should now have another worksheet called Cash Flow (2) in your workbook.

Second, rename the worksheets (be logical - use the year that the Cash Flow is for; i.e. 2010-2011 and 2011-2012):

  1. With your mouse, Right-click on the tab at the bottom left corner of your original worksheet (at the moment called Sheet1), select Rename from the short menu that appears and type in the name that you want to use.  When you have finished, press Enter on your keyboard.
  2. Now do the same with the new worksheet.

You should now have your original worksheet full of the figures that you have already entered and a duplicate of it, also full of figures, both worksheets with new names.

Third, change the dates and remove the figures, carefully, in the new worksheet, so that you have a blank worksheet to work with.  Once you have done this, I recommend that you copy this blank worksheet so that you have an empty worksheet to use at your financial year end (instructions to follow below).

Before you begin, a “DO NOT”! Don’t delete anything in the rows labelled Total Cash In, Total Cash Out, Closing Bank Balance and Column N as they have formula in them.   Identify which rows and columns to avoid before you begin and don’t forget you have Undo Typing (Ctrl Z) to fall back on if you think you have gone wrong!

  1. To remove the “old” Income figures, left-click and drag diagonally from B8 to highlight an area that includes the last cell of income figures, possibly M28 (it should go blue, apart from B8 which will still be white).  Press delete to erase the figures.
  2. If your income will be coming from the same sources as the year before, you can leave the text in column A as it is. If not, left-click in A8 and, staying in Column A, highlight down to the cell above Total Cash In and delete the text.
  3. Do the same with the “old” Expenses area.
  4. Delete the dates in row 4 so that it is empty.

At this stage, use the instructions for Copying and Renaming worksheets to give you a second blank worksheet. Rename this one “Template”.  At the end of your next financial year, copy the Template worksheet to give yourself a new blank one, rename the new one and leave the Template worksheet in your workbook ready to copy again the following year.

If you want your new Cash Flow to follow on from the previous one, simply type the last Closing Bank Balance figure from your original Cash Flow into cell B5 (your first Opening Bank Balance, which may be M81).  This figure will not update when you change the data in your first worksheet, for that follow the instructions below.

If you want the figures from your Closing Balance on your 1st worksheet to update dynamically in your Opening Balance on your 2nd worksheet, you need to link the two cells so that they talk to each other:

  • In your 2nd worksheet, Type = (the equals sign) into B5
  • Click on the first worksheet name (at the bottom of the workbook) to open it, then click on the cell whose contents need to be linked to B5 (this may be M81).
  • That cell will now have a “moving” broken line around it; press Enter and you will be returned to the 2nd worksheet where you should find that B5 will now contain the figure from the first worksheet.


Extra Excel Tips:
Sometimes it’s easier to keep your hands on the keyboard when you’re using Excel.  Here are some handy keyboard shortcuts (most of these will also work in Word and other MS Office programmes):

Ctrl + C    Copy

Ctrl + V    Paste (only the data in the cell, not formula)

Ctrl + X    Cut

Ctrl + Z    Undo (the last action)

Ctrl + D    Copies the data from the cell above the active one

Ctrl + F    Find (and Replace)

Ctrl + S    Save (use with caution!)

Ctrl + P    Print

Learn to use Excel – it really is one of the most useful pieces of software for businesses.


back to Free Business Plan Tips