Microsoft Excel :  Organizing Sheets

by Karyn Stille

By default, Excel adds three worksheets to each new workbook opened.  What if you need more?  Can you change the default?  How do you move or copy a sheet?  Can you move or copy more than one sheet at a time? How do you delete a sheet?  Read on to answer these questions and more.  

Changing the Default Setting

If you normally create workbooks that need a specific number of sheets (other than 3) you can change the default setting for the number of sheets in a new workbook.  From the Tools menu, choose Options and move to the General tab, as shown below.

Change the number for Sheets in new workbook, and choose OK.  Also notice that this is where you can change your default file location.  If you normally use a directory other than My Documents, you can type in the path here and save time when opening and saving files.

Naming Sheets

The easiest way to rename a sheet, is to double-click on the sheet tab, type the name, and press Enter.

You can also rename a sheet by right-clicking on the sheet tab and choosing Rename, as below.

Inserting Sheets

Remember that when you insert a sheet in an Excel workbook, the default is to insert the sheet before the current active sheet.  So, to insert a sheet:

  1. Select the sheet tab for the worksheet you want to be after the inserted sheet.

  2. From the Insert menu, choose Worksheet.

Alternatively, you can right-click on the sheet tab and choose Insert.  This brings up the Insert dialog box which gives a few more options.

To insert a worksheet, select Worksheet and choose OK.  Another option here is to insert a chart.  Selecting Chart and choosing OK brings up the Chart Wizard to help you make a chart.

When you need to insert a new worksheet at the end of your sheet tabs, you will need to select the last sheet tab, insert the sheet, and then move the sheet to the end.  For example, I may have a workbook with data for four quarters of the fiscal year with each quarter being displayed on a separate tab.  At the end of the year, I want to include a sheet at the end for yearly totals.  So, I have inserted a sheet before the last sheet tab, as below.

I now need to move that sheet to the end.

Moving and Copying Sheets

The easiest way to move a sheet, is to select the sheet, then holding the mouse button down, drag the sheet to the new location.  Excel provides a small black arrow to help you place the sheet in the proper location.

Release the mouse when the black arrow is in the right place.

If you have a sheet that you need to copy - which is often time saving when you need a sheet with the same format - you can use this same method to copy a sheet.  Just press and hold the Ctrl key while dragging the sheet to a new location.  This copies rather than moves the sheet.  When copying, Excel shows a "+" sign on the sheet icon.

Grouping Sheets

Using the Shift or Ctrl keys on your keyboard help you to group sheets so that you can move or copy more than one sheet at a time.

If the sheets are all in a row:

  1. Select the first sheet tab.

  2. Press and hold Shift.

  3. Select the last sheet tab in the group.

  4. Release the Shift key.

If the sheets are not all in a row:

  1. Select the first sheet tab.

  2. Press and hold Ctrl.

  3. Click once on all sheet tabs to be included in the group.

  4. Release the Ctrl key.

Use one of the methods above or below to move or copy the group of sheets.  

Incidentally, if you need the same general formatting in several sheets, grouping the sheets and changing or adding formatting and data in one also adds it to the others - an fast and easy way to create matching sheets.

Important: Once you are finished, you need to remember to ungroup the sheets!  If you do not ungroup the sheets, any work you do in one sheet will be duplicated in all the others.  To ungroup the sheets, right-click on one of the sheet tabs and choose Ungroup.  

The Move or Copy Dialog Box

You can also move and copy sheets with the Move or Copy dialog box which offers more options.

  1. Select the sheet tab for the sheet to be moved or copied.

  2. From the Edit menu, choose Move or Copy Sheet.  You can also right-click on the sheet tab and choose Move or Copy.  Either method gets you to the Move or Copy dialog box shown below.

Open workbooks are listed in the To book field, so that you have the option of copying it or packing up the sheets belongings and moving it to an entirely different workbook.  Also listed in this field is (new book).  This allows you to create a new workbook with the selected sheet inserted.  In the graphic above, I first selected my Quarter4 worksheet, accessed the Move or Copy dialog box, and have chosen to create a copy of it at the end.  Here is the result:

I can now rename the sheet and change the data as necessary.

Deleting Sheets

It is sometimes necessary to delete a sheet from a workbook.  This is most commonly used when one or two of the three default sheets in a workbook are not needed.  Just one caution:  If you delete a worksheet that contains data that other sheets in your workbook or other workbooks refer to in formulas, the formulas (obviously) will no longer work. 

To delete a sheet:

Right-click on the sheet tab and choose Delete.

OR

Select the sheet tab and from the Edit menu, choose Delete Sheet.

I have, from time to time, had users try to delete a sheet by selecting the sheet tab and pressing Delete on the keyboard.  While it may seem like a no-brainer, the Delete key only deletes the information in the active cell in the sheet whether or not you have clicked on a sheet tab.

 

Free Microsoft Office Tutorials & MS Office Tips Newsletter
http://tutorials.esmartweb.com