Linking Cells in Excel: Creating a Dynamic Spreadsheet

Linking Cells in Excel: Creating a Dynamic Excel Spreadsheet

Working with a lot of data can be a daunting task. However, knowing a few hacks and tricks can be useful for someone who is dealing with extensive data in Excel on a daily basis. One great example is how to link cells, worksheets, or workbooks in Excel. Linking in Excel will save you time and help you have more polished spreadsheets. 

There are different ways to link cells in Excel. In this tutorial, we are going to cover certain aspects of the matter, including several solutions and methods. 

The two main ways of linking cells in Excel are:

  • External reference formula (Link)
  • Excel hyperlink function

It is important to note that hyperlinks and external references are both very useful forms of linking different worksheets or workbooks; nevertheless, they have different purposes or uses. Here, we intend to focus on the external reference in more detail. 

Linking Cells Between Sheets and Workbooks in Excel: External Reference

In Excel, you can basically use an external reference formula to refer to the content of cells on a worksheet in another workbook. This is also called linking, which is a more commonly used term. In other words, linking is referencing to:

  • Another cell 
  • Range of cells 
  • A defined name

But, why is referencing useful, and how does it save time and effort?

Why Link Cells in Excel

Linking cells in Excel is beneficial when you have large volumes of data, and it is impractical to keep large worksheets in the same workbook.

Merging data

  • You can use external references to integrate relevant data into a summary workbook. 
  • You can link several workbooks which belong to different members of the team or different departments. 
  • Once the source workbooks are changed, you won’t need to manually change the summary worksheet. 

Giving different views of data

  • You can generate a report workbook containing links to only the pertinent data.
  • You can have all your other data on other source workbooks.  

Organize large complex models

  • You organize a complicated model by breaking it down into multiple interdependent workbooks.
  • You can work on any of the sheets without opening all the related sheets. 
  • You can have smaller workbooks which are easier to manage.
  • Smaller workbooks need less memory. They are faster to open, save and calculate

Linking Cells in Different Worksheets

Let’s start with the most basic form of external reference, which is to create a link to another worksheet (Tab) in the same workbook. Follow these steps:

  1. Select the cell where you want to create the link.
  2. Type an equal sign ‘=’.
    Select a cell in an Excel sheet and type the equal sign
  3. Go to the other worksheet that contains the cell you want to link to.

    Select the cell you want to link to in an Excel sheet
  4. Select the cell that you want to link to, and press Enter.
  5. Excel will automatically return to the original worksheet and display the value of the cell you selected.
    linking a cell in a different worksheet by using the cell's path

Note: If you make any changes to the figures in the original worksheet, the value will be automatically updated in the target sheet. 

What does the path represent? In this example, =Sheet2!D5, Sheet2 is the number of the sheet, and D5 is the cell that’s been linked to. The exclamation ‘!’ mark acts as a separator.

Linking a Range of Cells Using a Function

In this part, we are going to see how we can link a range of cells between worksheets in Excel. In other words, we are referencing data from a different worksheet. This is particularly useful when you have a large volume of data and want to have the results in another worksheet. 

In our example, we have sales volume by quarter. We want to get the averages of sale volume for 2018, 2019, and 2020; however, we don’t want to display any of the raw data. So, we open a separate worksheet or tab. Now, follow these steps:

Linking cells in excel, table of sales volume by quarters from 2018 to 2021
  1. Select the cell where you want to display the average.
  2. Type in the AVERAGE formula. Start with an equal sign, then type in AVERAGE and then open parentheses like so:

    =average (

    Related post: How to Calculate Average in Excel

    Linking cells in Excel, typing the average formula in a cell
  3. Now, go to the original sheet containing the raw data by clicking on the sheet tab. We want the data for 2018. So, select the data or the range of cells you want, and press ENTER to complete the path.
    Linking cells in Excel, By selecting a data range from a different sheet for the formula, the path of the data range appears instead of raw data.
  4. When you press ENTER, Excel takes you back to the sheet where you entered the formula. And it’s going to have all the formulas there. 
    linking cells using the average function, the formula is linking to another sheet

    Note: As you can see, the formula includes the name of the sheet.
    This is the formula: =AVARAGE(Sheet2!D2:D5)

  5. We will do the same for the rest of the data, and this is the result.
    linking cells using the average function, average for a range of cells in a different sheet

Linking to a Defined Name in Another Workbook

This time we want to link different workbooks instead of worksheets. Our aim is to create an external reference to a defined name in another workbook. 

Here, we have a workbook that contains budget data, and it is defined as Budget. Let’s quickly go through how to define a name in Excel. If you’re familiar with the process, skip this part. 

Creating an Excel Name Range

  • Select the range of cells you want to name.
  • Type the name in the Name Box.
  • Press, Enter. 
linking cells in Excel, Selecting a range of data, and typing a name in the name box, in the top left corner of the sheet

Now, let’s go through the steps to link two different workbooks. Follow these instructions to create an external reference to a defined name in another workbook.

  1. Open the workbook containing the Budget data and the workbook that will contain the external reference.
  2. Select the cell or a range of cells you want to link to.
  3. Type = (equal sign). 
    Linking cells in Excel, Typing an equal sign in an Excel cell
  4. Switch to the other workbook and the sheet with the data you want to link. To do this: Go to View in the Menu bar. Then, find the Switch Windows option. When you click on it, you’ll notice a list of all the open workbooks. Select the workbook you want to pull data from. 
    Linking cells in Excel, go to the view tab and select the Switch Windows option from the Window group to switch to the source workbook.
  5. Press F3, the Paste Name window will pop up. Select the name you want to link, click OK, and press ENTER
    Linking cells in Excel, press F3, the Paste Name window will pop up. Select the name you want to link, click OK, and press ENTER.
  6. Excel will automatically return to the other workbook and display the values from the range you selected. 
    Linking cells in Excel, values of the source workbook are displayed in the destination workbook. You can view the path by clicking on the data range.

Voila! This is how you can link two different workbooks with no sweat. 

Bottom Line

In this tutorial, we covered 3 different methods, linking cells between sheets, linking a range of cells using the Average Function, and linking to a defined name in another workbook. The essential element here is the Equal sign ‘=.’ Every path you create for external referencing starts with an = (equal sign).

Did you find this tutorial useful? If so, check out our blogs and youtube channel. We offer customized Microsoft Excel solutions for businesses. If you find yourself backlogged and doing the same task over and over again, we are here to help. Our Excel programmers and consultant can automate and minimize your task. 

FAQ

How do I link data from one Excel sheet to another?

You can simply type in = (equal sign) in a cell. Then click on the sheet, select the cell or range of cells you want and press enter. If you are familiar with formulas or paths, you can directly type the formula.

What is the Average formula? 

Average is calculated by adding a group of numbers and then dividing the total sum by the total number of items. The Average formulas used in Excel are:
 =AVERAGE(number1, [number2], …) …
=AVERAGE(B2:B11) 

What are links in Excel?

External reference or a more commonly used term ‘linking’ is when you use, display or pull in data that is stored in another cell, worksheet, or even workbook. In other words, by creating external references, you can access the content of cells in another workbook, sheet, etc.

 

Subscribe to our Newsletter

Share this post with your friends

Leave a Reply

Your email address will not be published.