When constructing large spreadsheets it is likely that you will need to cross-reference data from different worksheets.
An example of this could be monthly figures on individual worksheets and a total or yearly worksheet.
You can use a simple formula to automatically update the total figures based on the monthly worksheets but this means referencing cells in multiple worksheets in one formula. So how do you do this?
It is actually very simple. In this example I will show you how to add together two cells from separate worksheets into another worksheet using employee sales figures.
As you can see we have January, February & Total worksheets.
We want to combine Rachel’s January and February sales figures to give us her total in 2012.
1. Select the cell you want the total to appear in (in this case B2) on the ‘Total’ worksheet.
2. Type the = sign
3. Now click on the January worksheet and click on the first cell you want to add (B2)
4. Type the + sign
5. Click on the February worksheet and click on the second cell you want to add (B2)
6. You now have Rachel’s total sales! Press enter to close the formula and view the result.
7. To get the total sales for all employees, simply drag down from the bottom right hand corner of B2 to fill out the column.
The same process applies for any formula. Just type the formula as you normally would but select the cells in the relevant worksheet.
You may have noticed that the cells are referenced with the worksheet name and a !
Excel references cells in other worksheets as “nameofworksheet!cell”.
This is how Excel determines that you want the information from B2 in January and not B2 in February or Total.
See more Excel tips on our Excel blog or read our most recent tutorials: