Microsoft Excel enables you to create several worksheets in one file. The data on these worksheets can be linked together through a formula. For example, daily orders entered on one sheet per day can be summarised in another sheet.
Create a NEW spreadsheet file – known as a workbook in Microsoft Excel. You will need seven worksheets for this exercise. Microsoft Excel is sometimes set to start each new workbook file with only one or a few worksheets.
If you need to insert more worksheets, click on the INSERT WORKSHEET button to the right of the sheet tabs, repeat until you have seven worksheets in the file. For Google Sheets, skip to step 7.
Double click on the name of the first worksheet so it is highlighted as shown below.
Type Tuesday then press <Enter>.
Repeat to rename each of the sheets for the days of the week – except Monday – when they are closed.
Click on the first worksheet, hold down the <Shift> key and click on the sixth worksheet so the first six worksheets are selected.
Enter the following text and data, which will be entered on all worksheets because they are selected. For Google Sheets, enter the data on the first sheet then click on the arrow and click on DUPLICATE six times. Click back on the first sheet and the arrow and click on RENAME then type the new worksheet name. Continue to rename all the sheets.
Click on the last worksheet to deselect all the sheets.
Click on each worksheet and enter the following data in the Quantity column for each day’s sales.
Click on the first worksheet (Tuesday), hold down the <Shift> key and click on the sixth worksheet (Sunday) so the first six worksheets are selected. For Google Sheets, you will have to enter the formula on each sheet separately.
Enter a formula in the Total column to calculate the sales of each type of item for each day. The formula will be entered on each worksheet because they are all selected. This is a very quick way to enter formulas and data. It only works if the structure of the table is the same on each selected sheet.
Now to create a summary to calculate total sales for the week. Click on the seventh worksheet and name it ‘Summary’, then enter the following text to create a summary sheet.
Now to add the sales from each day to create a summary figure for the week. Click back on the first sheet and highlight the TOTAL figures for the day then click on the COPY button on the Home tab. For Google Sheets, click on EDIT then COPY.
Click on the ‘Summary’ worksheet and click on the cell B4 then click on the PASTE droplist button. For Google Sheets, click on PASTE – you cannot link in Google Sheets.
Click on the PASTE LINK option. This will enter data from the ‘Tuesday’ sheet into the ‘Summary’ sheet. If any changes are made on the Tuesday sales they will also feed through to the summary.
Click on the second sheet and repeat steps 13, 14 and 15, then repeat again for the third sheet and so on until all the days’ figures are copied over. The results are shown below.
Save the workbook with the name: Ice-cream sales.
Video 7.1 Working with multiple worksheets 1 (02:49)
Create a NEW workbook. You will need four worksheets for this exercise.
Click on the first worksheet then hold down the <Shift> key and click on the third sheet – this will select three sheets altogether. For Google Sheets, skip to the next step.
Enter the following text, data and formulas to create the data outlines as shown below. For Google Sheets, click on the Sheet arrow and click on DUPLICATE until you have four sheets.
Enter data into each of the sheets.
Click on the last sheet. The purpose of this sheet is to summarise all quotes for comparison. For Google Sheets, edit the text to read as below.
Use the PASTE LINK feature used in the previous exercise to copy the data from each quote to the summary sheet.
Video 7.2 Working with multiple worksheets 2 (02:16)
