How to reference formulas and data on other worksheets

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> You don't have to have all your data on one worksheet. In fact, it's common practise to create lots of worksheets in the same workbook. In this lesson, you'll see how to reference a formula that is on a different worksheet. This comes in handy if, for example, you have 12 worksheets, one for each month of the year. You can then create another worksheet that holds things like totals for the entire year. We'll do that now.</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Open up Excel (If it's already open, close the project you're currently working on and open a new blank workbook.)</li> <li>Locate Sheet1, Sheet2, and Sheet3 at the bottom of Excel (Excel 2013 users will only have one worksheet. To create two more worksheets, click the plus symbol to the right of Sheet1.)</li> <li>Rename these to May, June, July (Right click, and select <b>Rename</b> from the menu)</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Rename the Worksheet" border="1" height="40" src="http://www.homeandlearn.co.uk/excel2007/images/2bang.gif" width="267" /></div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="40" src="http://www.homeandlearn.co.uk/excel2007/images/2bang2.gif" width="233" /></div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Renamed worksheets in Excel 2013" height="67" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/rename_wk.gif" width="319" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click on the <b>May</b> sheet, and enter the same data as in the following image:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="104" src="http://www.homeandlearn.co.uk/excel2007/images/2bang3.gif" width="297" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click on the June sheet and enter the following:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="104" src="http://www.homeandlearn.co.uk/excel2007/images/2bang4.gif" width="292" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Then click on the July sheet and enter the following:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="104" src="http://www.homeandlearn.co.uk/excel2007/images/2bang5.gif" width="301" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We now need to create a new worksheet. So click on the New Worksheet icon at the bottom of Excel, the one to the right of July in the image below (Excel 2013 users should click the plus symbol again):</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Insert a new worksheet" border="1" height="39" src="http://www.homeandlearn.co.uk/excel2007/images/2bang6.gif" width="308" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The new worksheet will be called Sheet4 by default. Rename it to Annual Total, and your workbook will look like this at the bottom (If your new worksheet is not at the end, hold down your left mouse button on the worksheets name. Keep it held down and drag to the end):</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="39" src="http://www.homeandlearn.co.uk/excel2007/images/2bang7.gif" width="326" /></div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="New worksheet added to Excel 2013" height="64" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/worksheet_add.gif" width="408" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We're now going to add up the figures on the May, June and July worksheets, and put the answer on the Annual Total worksheet.</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Add a label to your Annual Total worksheet:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="104" src="http://www.homeandlearn.co.uk/excel2007/images/2bang8.gif" width="282" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Then click inside cell B1.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> To reference data on another worksheet, you use the exclamation mark (exclamation point, if you're in the USA). This is commonly called a Bang!</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So enter this in cell B1 of your Annual Total worksheet:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>=May<span style="color: blue;">!</span>B1</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So we start with an equals sign (=), and then type the Name of the worksheet we want to reference (May). After the exclamation mark (bang), we have the cell we want to reference (B1). If you just type B1 by itself, Excel would assume that you meant the current worksheet.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you press the enter key, you should see this on your Annual Total worksheet:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="103" src="http://www.homeandlearn.co.uk/excel2007/images/2bang9.gif" width="307" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> This is the same figure as the one on your May worksheet. To add up all our monthly worksheets, just reference them in the same manner:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>=May<span style="color: blue;">!</span>B1 + June<span style="color: black;">!</span>B1 + July<span style="color: blue;">!</span>B1</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So click inside cell B1 of your Annual Total worksheet and replace your formula with the one above. Press the enter key and you should see the answer:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="120" src="http://www.homeandlearn.co.uk/excel2007/images/2bang10.gif" width="436" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So when you want to include figures or formula from other worksheets, remember to include the name of the worksheet followed by a bang</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the next part, you'll learn about the LOOKUP function in Excel.</div> </div>

How to reference formulas and data on other worksheets

You don't have to have all your data on one worksheet. In fact, it's common practise to create lots of worksheets in the same work...

Read more »