How to Reference Cells from Other Worksheets in a Formula

This tutorial is covered on our Beginners Excel course, if you want to learn some more complex techniques then take a look at our Advanced Excel course outline.

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.

Reference cells  different worksheets in forumula excel tutorial

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

reference worksheet cells excel tutorial enter

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.

total from different worksheets excel formula

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.

sales totals excel formula tutorial

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:

Tags

, , , , , , ,
Andy Trainer


Connect with Andy on Google.

View all posts by Andy Trainer

Leave a Comment