Calculations in multiple sheets:

In this example, we calculate the total sales per salesperson, month-wise, for a period of four years (one sheet per year).

We first insert a new sheet after the last sheet.  So, behind the “2010” sheet, we do not need to add a new sheet but, we can copy the sheet with the largest data area (the data areas differ in size, the sheets 2007 and 2008, have 12 salesmen, sheet 2009, has 10 vendors and sheet 2010, has 11 vendors).

Click on the tab of the “2007” sheet, press the “Ctrl” key and drag it towards the right, behind the tab of the “2010” sheet. (1)
(See copying sheets: Course Excel 2007 Lesson 6 and Course Excel 2010 Lesson 9)

Release the mouse button and the “Ctrl” key and we have a copy of the worksheet (2) with the name “2007 (2)”.

Right-click on the sheet “2007” (2) tab and rename it to “Total” (3).

In the “Total” sheet , select the cells B2-M13 and press “Delete”.

With the cells still selected, type = SUM ( Then click on the tab of the “2007” sheet, click in cell B2, press the Shift key, click on the tab of the “2010” sheet, release the Shift key and type the closing parenthesis ) The formula is = SUM (‘2007:2010’!B2). Now press Ctrl + Enter.

The result is now displayed in the cells B2-M13.

You may also calculate other values such as average, largest, smallest, etc.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats