Formulating references is an integral part of working in Excel. This process is often used in workbooks where figures need to be calculated for a total value. For example, if you have a workbook that contains sales spreadsheets for every month of the year; you will be able to formulate cells that will add them up. Any changes you make in the referenced cells will automatically be updated in the final formula. This Excel tutorial is going to teach you exactly how to reference formulas in your spreadsheets.
As you can see in the above example, the highlighted cell been formulated with references. I added the totals for the May, June and July spreadsheets into the yearly total. In the fx bar (where the red arrow points), Excel will show you exactly how it has been referenced. There are two methods that you can use. You can either type out the formula in the cell where it is required, or you can instruct Excel to create the reference for you via a simple clicking process. This Excel guide is going to show you how to manually formulate references:
Referencing Formulas Manually
To manually enter the formula for the cells you want to reference, you will need to know how Excel understands these commands. We are going to explain it to you in sequence, which is exactly how you will enter it into Excel, but first you will need to go to the cell you want to formulate with a reference:
-
=
The first thing you will always type is an equal sign (=). This will instruct Excel to total whatever information you enter after it.
-
Worksheet Name
Now you need to type in the name of the worksheet where the information comes from. Your formula should be =WORKSHEET
If the name of your worksheet contains two or more words with spaces between them, then you will need to enclose the name with single quotation marks such as =’WORKSHEET NAME’
-
!
Once you have your equal sign and worksheet name, you need to type in an exclamation mark. So the formula will now be =WORKSHEET!
-
Cell Address
So far, you have instructed Excel to equal the worksheet. This is not enough and you have to also type in the name of the cell where the information comes from. Cells are named first by the column letter at the top of your spreadsheet, then by the row number along the left-hand side. Now your formula should be =WORKSHEET!CELL
-
Press Enter
Now you have a single reference formula. Press Enter on your keyboard and Excel will retrieve the data from its location and put it into your spreadsheet. You can create several single reference formulas and add them up using AutoSum. For an uncluttered worksheet, you can create multiple reference formulas within a single cell and Excel will calculate the value from them.
Referencing formulas may seem complicated, but once you understand the process it is extremely easy. Now that you know how to manually type a reference formula, you will be able to use it most effectively in your spreadsheets. You can even use this method to obtain data from another workbook.
This article was written on recommendation by our Excel training course students in Fort Lauderdale, Miami and the entire South Florida area. We discuss how to perform the same function by clicking on the information you need in another article. To learn Excel the easy way, feel free to watch our advanced Excel video tutorials.
