Deprecated: mysql_pconnect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /home/content/94/10002394/html/connect_to_mysql.php on line 11
How to Reference Formulas and Data on other Excel Worksheets Worksheets Microsoft Excel 2013 Tutorial How to Reference Formulas and Data on other Excel Worksheets Worksheets Microsoft Excel 2013 Tutorial, Tweet

# How to Reference Formulas and Data on other Excel Worksheets Worksheets Microsoft Excel 2013 Tutorial

Sometimes you dont need to have all your data on one worksheet and you make multiple worksheets to organize your data. But you face problem in applying formula to all worksheets that you have applied on previous one. In this lesson we will learn how to reference data and formula to multiple worksheets with a simple trick. For example we have 12 worksheets for each month of the year and want to put total in another 13th worksheet.

In our example we have 3 worksheets and we will put total of data on three sheets on 4th worksheet. Follow the steps below to see how to achieve this goal.

Step 1: Open excel 2013 and select blank workbook, you will see that by default there is one worksheet is generated. Step 2: We need two more worksheets for our task, click two times on plus button at the most right of Sheet1 to generate two more work sheets. Step 3: Now we have 3 worksheets. Step 4: Now we will rename these three worksheets as months names like March, April and May. To do this right click on worksheet and select rename. As shown in image below.  Step 5: Now click on March worksheet and enter the following data. Step 6: Then in April and May as follows.  Step 7: Now click on + button to create new sheet on which total of three sheets will be saved. And name that sheets as yearly total. Step 8: Now we will calculate total three sheets on this sheet. To reference data from another sheets we use exclamation mark. To do this click on Cell B1 and put following in selected cell. Step 9: (=) equal sign then name of sheet we want to refer and the (!) exclamation mark, after that put the cell id you want to refer in this sheet and press enter key. You will see the value of worksheet March and cell B1 will show up in worksheet Yearly Total in cell B1. Step 10: To calculate the sum of all three cells of three worksheets just click reference them in this cell. Click on cell B1 in yearly Total worksheet and replace the formula with following one.

=May!B1 + June!B1 + July!B1 Step 11: After pressing enter key you will see that total of all three values in worksheet March, April and May will show up in that cell. Step 12: This is how we reference the formulas and data from other worksheets into a sheet we want.

Note: this (!) mark is called Bang in excel formula reference.

So when you want to include figures or formula from other worksheets, remember to include the name of the worksheet followed by a bang.    