Being able to format references is an essential aspect of working in Excel. The knowledge will allow you to calculate totals using information from a variety of worksheets in your workbook. This is particularly useful when you have multiple spreadsheets containing specific data that you need to integrate into a final figure. An example of this would be a workbook containing worksheets for each month of the year; with total sales or expenses calculated into a yearly total. This Excel tutorial will teach you how to automatically reference formulas from different worksheets to create a final value.
References can be formulated by using two methods. We have already discussed how to manually type reference formulas that Excel will recognize in another article. Now we are going to show you how to do it easily by using a simple clicking method. Excel will create the formula for you, provided you click on the right references in the correct sequence.
If you click on a cell, Excel will show you exactly what formula has been assigned to that cell in the fx bar above your spreadsheet. If no references have been created, then it will simply show the figure entered into that cell. In the above example, we have already formulated references for the sales figures from August, September and October; and added them together for the yearly total. The first step that you need to take is to click on the cell you wish to put a reference formula into.
Now we are going to discuss the sequence that you need to follow in order for Excel to formulate your references correctly:
-
=
Before clicking on anything, you need to begin the sequence by manually entering an equal sign. This is essential because it tells Excel that there are references to follow and a grand total needs to be calculated from them. The first step in the process will look like this:
-
Click on the Worksheet Tab
Now we need to tell Excel which worksheet the information required is in. After entering the equal sign, you simply click on the worksheet tab that contains the data.
The spreadsheet that you are working in will be highlighted in white once you click on another tab. You will be able to clearly see what Excel is formulating in the fx bar. Excel will automatically add the name of the worksheet tab and follow it with an exclamation mark. Your formula will now say =WORKSHEET!
-
Click on the Cell Address
You have now told Excel in which worksheet the reference is located, but you also need to tell Excel which cell has the data you require. So go ahead and click on the cell that contains your information.
Excel will surround the chosen cell with a “marching ants” border and name the cell in your reference formula. Cells are named first by the column they are in (at the top of your spreadsheet) and then by the number row they are on (on the left-hand side of your spreadsheet). In the above example, the cell is located in column B and in row 1; hence Excel named it B1. In your fx bar, your formula will now be =WORKSHEET!CELL
-
Press Enter
If you simply want to create a single reference formula then press the Enter key on your keyboard. Excel will instantly bring the data from your chosen cell and add it to your spreadsheet. You can create several single reference formulas and add them up using AutoSum, or you can include multiple references into one formula.
Referencing formulas seems complex, but it really is as easy as that. Just remember to follow the correct sequence and you will be able to create advanced worksheets that benefit you. You can even use this method to reference formulas from another workbook entirely.
After unanimous recommendation by students from our Excel training courses in the greater South Florida area, we have written this article to help you. For more information on how Excel can be used to your advantage, watch our advanced Excel training videos.
