Often spreadsheets require information that is contained elsewhere. Sometimes data needs to be retrieved from other worksheets and even other workbooks. We can type the information where we need it but if the original value is changed then we need to update every worksheet where it is used. In addition to this, we will need to manually perform calculations every time we need a total value. Excel has a function that allows you to reference formulas from another spreadsheet and it updates each reference where changes have been made. It also calculates values according to your instructions; making spreadsheets clutter-free, professional and easy to maintain.
There are two methods that can be used to create reference formulas from another workbook. Either way, Excel needs to be told where to find the information you require. One involves manually typing each reference into the formula and the other involves simply pointing and clicking on the relevant instructions. This Excel tutorial is going to teach you exactly how to instruct Excel to automatically configure a reference formula from another workbook, using the point-and-click method.
The above example explains where to find your fx bar (above your spreadsheet). If you click on a cell that has a reference formula, Excel will show you exactly what the formula consists of. Each reference will have a workbook name, worksheet name and cell address; which is how Excel finds the data. With the point-and-click method, you will not need to type any of that but you must follow the sequence correctly so that Excel understands the location of the reference being formulated. We are going to explain the sequence to you, and you need to follow it exactly; but first you should open the workbooks you require and click on the cell you want to add a reference formula to.
-
=
Whenever you are working with formulas in Excel, you need a to begin with an equal sign. This simply tells Excel that data is coming that needs to be formulated. So type in an =
-
Workbook Name
Now that you have begun the sequence, you will need to direct Excel to the workbook where the information is contained. Click on the workbook you need.
-
Worksheet Name
Immediately after clicking on the workbook, click on the worksheet tab that holds the data Excel needs to find. If you look in your fx bar; you will see that the workbook name has automatically been added, along with the name of the worksheet.
-
Cell Address
Now you need to tell Excel which cell is the one with the required information. Simply click on the cell Excel must use. It will immediately become surrounded with a “marching ants” border and will be referenced at the end of the formula. Note that cells are named by Excel according to the column they are located in and the row they are situated on. The currency in my screenshot is simply because the cell has a currency formula attached to it.
-
Enter
Your first reference formula has now been created. The next step is determined by whether you wish to add additional references to the formula or not. If you just want Excel to use the information from that cell alone, then press Enter on your keyboard. Your reference formula will come into immediate effect. If you need to add multiple references, then we continue the sequence in another article.
Now that you know exactly how easy it is to reference formulas across workbooks, you can use them in your spreadsheets for the ultimate professionalism. Oftentimes, they are used for accurate accounting purposes. Several of our Excel training course students in South Florida recommended we write this article to help you learn Excel easily. For more ways that Excel can make your life a pleasure, feel free to watch our Excel video tutorials.
