Your company, an ice-cream retailer, does business in several cities. In an Excel workbook, you recorded your company’s first quarter’s sales from all business locations, broken down by ice cream varieties. To obtain a clearer overview, you listed the sales for each month on a separate worksheet. That is, there is a worksheet for January, another for February and one for March, as well.
Now, in a separate file, you want display all three months (January – March) of sales data for just the Madrid location. Because you want this new table always to reflect changes, if any should be made to the original data, you must use excel cell references to dynamically link the data display in your new table to the source data .
This learning module will show you how to work with excel cell references that link the cells in an Excel table to cells in another file.
HOW DOES EXCEL CELL REFERENCES WORK?
Creating a excel cell reference between cells of separate Excel files is very similar to linking cells between different worksheets. However, before you can link cells from one Excel file to cells in another Excel file, both files must already be open.
To enter a excel cell reference to a cell in another file, first click the cell in which you want to enter the excel cell reference. Then enter an equal sign (=). Now position your mouse pointer over the Excel program icon at the bottom edge of your screen (in the taskbar). Move your mouse pointer to the thumbnail preview of the file with the source data and click on it. That workbook now appears on your desktop. If necessary, select the appropriate worksheet in that Excel workbook. If you now click on the cell that contains the source data and press [Enter], Excel will again display the spreadsheet where you began creating the excel cell reference, which now is complete, showing the data from the ‘other’ file.
Here is a specific example:
To create the summary table for the Madrid location, first open both files, the file containing the original tables and the file in which you want to display just the Madrid data. Start with the target file. In our example, you can see that, in preparation for the Madrid data, the row and column headings have already been entered. To enter a cell reference that will link the first target cell in the new table to the one that contains the sales number for chocolate ice cream in January, click cell B4 of the new table and type in an equal sign (=). To continue creating the cell reference to the other file, use the taskbar to go the other workbook.
To create a link between cells of separate Excel files, all involved files must be open.
That workbook will now appear on your desktop. Make sure you are positioned on the January worksheet. There, click the cell to which the excel cell reference is to refer, in this case, cell C4.
Now press [Enter]. Excel cell reference completes and returns focus to the table in which you began entering the cell reference.
As the excel cell reference is being constructed, you can observe it in the formula bar. The complete cell reference appears here, which in our example should now read: . The part within the square brackets shows the reference to the ‘other’ file. The next part, up to the exclamation mark (January!) indicates the worksheet. After that, the specific cell is identified – here, C4. It is important to note that the cell coordinates are formed as an absolute reference: $C$4.
In our example, when you finish entering the cell reference by pressing the [Enter] key, Excel automatically returns the new Madrid workbook to the front of the desktop.
Now, in the example, you can use the AutoFill function to enter the remaining cell references for the other ice cream sorts in the January column. However, before you can do that, you must first change the absolute cell reference in B4 to a relative cell reference. (For more information, refer to the learning module: Relative and absolute cell references.) To do this conversion, click on the cell reference $C$4 in the formula bar. Then press the [F4] key as often as necessary, until both dollar signs have been removed from the cell reference. In the end, the cell reference should look like this:
When you are satisfied with the excel cell reference, press [Enter]. Now you can use AutoFill to fill out the rest of the column. Position your mouse pointer over the lower right-hand corner of cell B4 until it turns into a black cross: . Then click and hold the left mouse button to drag the pointer down to cell B7. When you release the mouse button, you will see that the excel cell references for the covered cells have been filled in and that the values for the other ice cream varieties and the total value will be displayed.
For the table to retain its pre-set format, click the SmartTag that appears below the filled-in range of cells. In the brief selection list that appears, activate the Fill Without Formatting option .
You can now proceed in the same fashion to link to the February and March data in the remaining columns. Use your mouse to enter a cell reference in cell C4 of the Madrid worksheet to link to February’s ice cream sales data in the source worksheet.
Then use the AutoFill function to fill in the remaining cells in the February column. Don’t forget to convert the absolute excel cell reference ($C$4) to the relative excel cell reference (C4) first.
Finally, create the reference =[Icecream_Sales_3_8_3_3.xlsx]March!C4 in cell D4 for the month of March, and then fill in the remaining cells as described above.
This guarantees that the spreadsheet in the Madrid file will always display the current data for the first three months of the year. As long as the saved path to the ‘other’ file remains valid – i.e., the file remains in the same name and location, and the relevant data cells stay in the same positions – the data displayed in the new worksheet will always reflect the most recent data in the ‘other’ file.