Working with Multiple Worksheets Tutorial
Excel 2010 -
Free Online Microsoft Excel Tutorial
* Create and Delete Worksheets
Copy and Move Worksheets
Format Multiple Worksheets Simultaneously
Calculate Formulas Across Worksheets
Excel workbooks can have hundreds of worksheets. You could have a worksheet for each month, or each location, or each person. You need to know how to add and delete worksheets, format multiple worksheets at the same time, insert data into multiple worksheets and calculate formulas across worksheets.
Test your Excel skills with the corresponding FREE Online Multiple Choice
Working with Multiple Worksheets Excel Test
* Create and Delete Worksheets
Worksheets and worksheet tabs in Excel moves organizing data up to the next level. Each worksheet, located on a separate tab in a single workbook, can contain separate data sets pertaining to the same category. For example, you can create a tab for every month of the year, product types that your company sells to the different subjects you are taking at school. It is an extremely handy and powerful tool for ensuring data is carefully organized and easy to access. The number of worksheets you can insert in a single workbook is limited only by the available memory of your computer system.
To view the contents of worksheets in a workbook, click on the worksheet tabs (circled in yellow in the screenshot below) located at the bottom left-hand side of the screen:
The white background of a tab indicates the worksheet is currently displayed/selected. Use the arrows to the left side of the screen to navigate through worksheets or to jump to the last or first worksheet.
To insert a new worksheet:
1. Click on the Insert Worksheet tab (circled in yellow in the screenshot below) located after the last worksheet tab:
2. A new blank worksheet with the generic name Sheet and a number will appear in the worksheet tab area.
To insert multiple worksheets at the same time, select a number of existing worksheets by holding down the Ctrl key on your keyboard while selecting the sheets, before clicking on the Insert Worksheet tab. This will insert the same number of new sheets as the sheets you selected before clicking on the Insert Worksheet tab.
A shortcut key for quickly inserting worksheets is: Shift+F11.
To rename a worksheet:
1. Right-click on the worksheet tab you wish to rename.
2. Select Rename from the menu list. This will highlight the text on the worksheet tab and allow you to retype a name for the sheet.
To change the color of a worksheet tab:
1. Right-click on the worksheet tab you wish to change the color for.
2. Select Tab Color from the menu list and then select a color from the Theme Colors box:
Change the color for multiple worksheets by holding down the Ctrl key on your keyboard while selecting worksheets before applying a new color.
To delete a worksheet:
1. Right-click on the worksheet tab you wish to delete.
2. Select Delete from the menu list and the click on Delete again in the confirmation box, to delete the sheet.
Alternatively, position your cursor in the worksheet you wish to delete and select Delete Sheet from the Delete menu list located in the Cells group under the Home tab.
Be careful when deleting a worksheet – once deleted it is very difficult and sometimes impossible to restore the deleted worksheet. Undo will be of no help here!
* Copy and Move Worksheets
1. To copy a worksheet, click on the relevant worksheet tab to open the sheet.
2. Right-click on the worksheet tab copy and select Move or Copy from the menu list.
3. This will launch the Move or Copy dialogue box. Select where to insert the copied worksheet by clicking on the worksheet in the list ahead of which you wish to insert the copied worksheet.
4. Tick the Create a Copy box (circled in yellow in the screenshot below) and press OK to insert the copied worksheet.
An alternative method for copying worksheets quickly is to select the worksheet to copy by clicking on the tab to open the worksheet. Hold down the Ctrl key on your keyboard whilst clicking and dragging the worksheet tab of the worksheet you wish to copy. This will create a copy of the worksheet.
To move a worksheet:
1. Click on the relevant worksheet tab to open the sheet.
2. Right-click on the worksheet tab to move and select Move or Copy from the menu list.
3. This will launch the Move or Copy dialogue box. Select where to insert the moved worksheet by clicking on the worksheet in the list ahead of which you wish to move the selected worksheet to.
An alternative method for moving worksheets quickly is to select the worksheet to move by clicking on the tab to open the worksheet. Click and drag the worksheet tab of the worksheet you wish to move to a new position.
* Format Multiple Worksheets Simultaneously
There is a freakishly useful function in Excel that allows you to apply formatting and even insert lines and text, to multiple worksheets simultaneously. Imagine you have created 12 worksheets, one for each month of the year, to which you added data and formatting. Somewhere down the line you realize the format needs to change, or you need to insert an additional row or column, a heading column with the same wording for all sheets need to be inserted or you simply need to change border and shading options.
To apply formatting, insert text or add new rows or columns to multiple worksheets at the same time:
1. Hold down the Ctrl key on your keyboard while selecting worksheet tabs to which you wish to apply the above elements.
2. After selecting the worksheets to add the additional line of text to, click in the sheet area of any of the worksheets.
3. Add the formatting, insert text, new rows and/or columns as normal into the worksheet.
4. The changes made above will be effected into all the worksheets you selected.
5. To deselect the worksheets, right-click on the worksheet tabs and select Ungroup from the menu list.
* Calculate Formulas Across Worksheets
Imagine you have created price lists for different groups of items separated across multiple worksheets and you wish to add totals for all worksheets together, or if you have divided data across months, years or some other category, but need to consolidate the data. Depending on how your data is organized, you can use formulas to calculate the totals of multiple worksheets.
For example, in the workbook below we wish to insert the total sales for each sales rep per month taken from the monthly sales worksheets labelled January, February and March:
To use a formula that calculates across multiple worksheets:
1. Select the cell to insert the formula into.
2. Insert the formula. In our example this will be: =SUM(
3. Click on the tab of the worksheet and the cell containing the value you wish to include in the total, to add to the formula:
4. Continue clicking into each worksheet and the relevant cell to add to the formula. Your formula may end up looking something like this: =SUM(January!B2, February!B2,March!B2).
5. The above will calculate the total for a specific value for all the sheets and cells selected. If a value on one of the original sheets is changed, the formula will automatically recalculate the total to include the revised amount.
Another method for calculating formulas across worksheets is to use the Consolidate function:
1. In the worksheet where you wish the results of your calculations to appear, select the cell or cells you wish to display the consolidated totals in.
2. Click on the Data tab on the Ribbon and in the Data Tools group click on Consolidate. This will launch the Consolidate dialogue box.
3. In the Consolidate dialogue box, select the function you wish to apply to the values you will select, for example, SUM.
4. Click in the Reference box and the click in the first cell or cells you wish to add to the formula on the first worksheet. Click on the Add button (circled in yellow in the worksheet below):
5. Repeat the process above for each reference you wish to add to the formula by clicking on each worksheet and each total and then press OK.
This will automatically apply the formula (SUM in the example above) and insert the total for the calculation into the worksheet and cells you selected before clicking on Consolidation. You can expand the numbers you consolidated by clicking on the plus symbol in the left-hand margin:
Clicking on the minus symbol above will collapse the numbers and display only the totals again.`