Excel sheets happen, but only if one knows the hidden menus upon which to click.

One of the most useful Excel tricks is copying spreadsheets.  Copying and moving a sheet doesn’t show up a lot in the spreadsheet tricks that are commonly available.  This knowledge is valuable for:

• Making an easy and immediate backup copy of downloaded data, which simplifies starting over without going back to the original source.
• Additional working backup copies can be made on-the-fly as analyses are being created.  Periodic working backups allow experimentation (new directions) without needing to start from the original data sheet, potentially compromising progress if the experimental innovation proves unhelpful.
• Copying sheets allow primary data to be aggregated into new levels of analysis.  In the recent Biomass Rules, residential solar data analysis, daily sheets were copied and aggregated into monthly sheets.  These were then easily aggregated and copied into an annual sheet.
• Copying sheets to a ‘new book’ creates a new file from which to seed more analyses.

Copying sheets is intuitive if one knows where to start.

Step #1.  Place curser over the sheet that needs to be copied.  This is the source object to be copied.  Right-click on the source sheet.  This brings up a popup menu.

Step #2.  Select the ‘Move or Copy…’ menu item (left mouse click).  This brings up the Move or Copy dropdown menu.

Step #3.  Check the ‘Create a copy’ box near the bottom.  Checking this box keeps the original source sheet in the current worksheet file.  Not checking the box will turn the ‘copy’ action into a ‘move’ action.  Without the box checked, the sheet gets moved from the source file to the destination, or target, location.

If simply making a duplicate sheet within the same spreadsheet file, the name of the existing sheet can be selected/highlighted.  When an existing sheet is highlighted in this Move or Copy menu in the ‘Before sheet’ section, the duplicate copy will be placed just above the highlighted sheet.

If this step is not taken, the duplicate sheet is placed in the left-most position of the spreadsheet file.  It is easy to drag the duplicate sheet to a different location once it is copied.

Step #4. Selecting a different open file name from the top-most ‘To book’ menu, an existing source sheet can be copied to any other file by simply selecting the target file name from the menu.  To appear in this dropdown To book menu, the target file must be open.

Even with no other Excel files open, a (new book) option will appear.  Selecting this option allows the source sheet to be copied into a new, standalone file.  This is very powerful.  Though copying the source sheet to a new target location still requires the new book to be saved as a new file name once the sheet has been copied.

After completing all the required option selections, the “OK” button must be clicked to complete the action.

Good Habits:  Good spreadsheet habits include copying active sheets early and often.  Anytime a new dataset is downloaded, entered, or imported, make a copy.

Excel will add an increasing numeric reference at the right-most character of the sheet name.  A protocol used at Biomass Rules it to add my own ending characters, naming the original sheet with the right-most characters, ‘-00’.

Excel by default adds the new copied sheet to the left most position of the worksheet.  Because of this our protocol when simply making backup copies as models evolve, is to allow the newest copies to remain on the left.

When copying a sheet of a different role or task, it may make more sense to direct the sheet to a different location than the default position at the left.

The frequency required for making backup copies works best with some experience.  But making an error in the current sheet and starting over from scratch only needs to happen once or twice, before making occasional back copies becomes very cost effective.