Excel Workbook Tips

Copy existing Excel workbooks, add spacer tabs, and more Excel workbook tips

Video: Create Workbook Copy Without Save As

Instead of creating an Excel file from scratch, you can save time by basing the new workbook on an existing file. Open the old file, then use the Save As command, to create a copy.

However, it is easy to forget that step, and you end up making unwanted changes to the original file. To be safe, use one of the tips in this video, to open a copy of the original, and keep it safe from changes.

Watch this video to see the steps for creating a copy of the original file, without using the Save As command.

Copy Worksheet to New Workbook

With just a few clicks, you can create a new workbook, from an existing worksheet.

To move or copy worksheet(s) to a new workbook:

  1. Right-click on the sheet tab (or, select a group of sheets, and right-click on one of the selected sheet tabs)
  2. Click Move or Copy>
  3. In the Move or Copy window, click the arrow in the drop down list at the top
  4. Select (new book) as the option
  5. To copy the sheet, check the create a copy box (leave the check box blank if you want to move the sheet)
  6. Click OK

worksheet to new book

Workbook Navigation Tips

It can be difficult to find a specific sheet, if an Excel file has many worksheets. This video shows how to use navigation arrows, worksheet list, coloured tabs, and spacer tabs, to make it easier to find a specific sheet. The written instructions for spacer tabs are below the video.

Add Spacer Tabs in Workbook

Thanks to AlexJ for sending this Spacer Tab tip. See more of his work on the AlexJ Sample Files page.

In a large workbook, you can colour the sheet tabs, to identify the sections, such as data entry and reports.

change sheet tab colour

To add visual separation between the coloured sheet groups, add a spacer tab:

  1. To insert a blank sheet, select the last sheet in a group, then click the New Sheet button.

    insert a new sheet to use as spacer tab

  2. To rename the new sheet, double-click the sheet tab, and type a single space character.
  3. rename the spacer tab

    NOTE: If inserting additional spacer sheets, use an additional space character for each new spacer sheet, because sheet names cannot be duplicated.

    rename the spacer tab

  4. Leave the spacer sheet tab with its default "No Fill" colour, or change it to White fill colour, to create a bit of "white space" between the tab groups.
  5. rename the spacer tab

Spacer Tab Tips

  1. Don't use too many spacer tabs, because each one will be slightly wider than the previous one. A limit of 2 or 3 spacer tabs works best.
  2. If you are concerned that people might click on the spacer tabs, and be confused by the blank sheets, you can add a message on that sheet, or a hyperlink that takes them back to the main sheet.
  3. hyperlink on spacer shet

  4. When you right-click the Excel Naviation arrows, the popup list of sheets will be clearly separated into groups, due to the blank sheet names. This will make it easier to scan a long list of sheet names.
  5. worksheet navigation list

  6. If you view the sheets in the VBA Project Explorer, change the sheet code names, so they are grouped at the end of the list of worksheets. For example, use zSpace01, zSpace02, and so on.
  7. sheet code names in vba

More Excel Tips and Tutorials

Worksheet VBA

Save File with Preview Picture

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started

Search Contextures Sites




30 Excel Functions in 30 Days




Last updated: December 20, 2015 11:43 AM