Excel Formulas for Linking Data between Different Sheets

5 comments

When starting to use Excel, we use formulas to manipulate data from other cells in the worksheet. After a while, however, this is no longer enough, as we need to combine data from multiple sheets, or even from other Workbooks.

In this article, we’ll tackle this issue and see how we can access the contents of any worksheet.

Formulas for Linking Data – An Example

Imagine that your supervisor in the fictitious WYZ Widget Company needs you to calculate how much of the assigned budget was actually used (you can find the file here).

Let’s look at the budget sheet (it’s the first in the workbook):

Image1 Excel Formulas for Linking Data between Different Sheets

And compare it to the Actual Expenses sheet (the next one in the file):

Image2 Excel Formulas for Linking Data between Different Sheets

Well, comparing it by eye just won’t do. We need a new table in which the difference is computed.

We’ll start with an empty table; that is, a table with the same headings for the columns and rows, but no data (available as the third sheet in the file):

Image3 Excel Formulas for Linking Data between Different Sheets

In each cell of this table, we want to have the difference between the allocated budget and the actual spending. To do that, we’ll create a formula that deducts the amount of the second sheet (Expenses) from the first sheet (Budget).

To do this , we do the following:

  • We select cell B5
  • Type in ‘=’ (to start off the formula)
  • Click on the Budget sheet tab to display the budget sheet; If you follow these steps you’ll note that the Formula Bar now contains =Budget! – this will be explained in a moment
  • We’ll now click cell B5. The Formula Bar should now display =Budget!B5
  • Type in – , which will be added to the Formula Bar: =Budget!B5-
  • Click on the Actual Expenses sheet’s tab; The sheet’s name will be added to the Formula Bar: =Budget!B5-‘Actual Expenses’!
  • Finally we’ll click on the B5 cell of this sheet and press the Enter key; the Difference sheet will be displayed again, and the B5 cell will display a zero amount

Here’s how it should look (check out the formula bar for the formula we created for cell B5):

Image4 Excel Formulas for Linking Data between Different Sheets

Exclamation Points to Sheets

OK, what did we do here?

Let’s look at the formula we’ve created and explain the parts.

The first part of the formula we got from the Budget sheet:

Budget!B5

When we use a reference to a cell on the current sheet, we just type in the name (or select it using the mouse); when we want to refer to a cell in a different sheet in the same file, we preface it with the name of the sheet and an exclamation mark.

The second part, which we deduct from the first, is:

‘Actual Expenses’!B5

So, now we have single quotes around the name. Why? Because the name includes a space. Any sheet name that contains a non-alphanumeric character needs to be quoted, so Excel won’t get confused.

Filling better

Now we can copy this formula to the rest of the cells in the table by using the fill handle.

Grab the fill handle and drag it down to cover the whole column. Excel is smart enough to replace the row numbers in each cell, so we get the difference between the appropriate entries in the two sheets:

Image5 Excel Formulas for Linking Data between Different Sheets

You can also select the whole column and go on to fill the whole table by dragging the fill handle to the right up to the December column.

Summary

In this article, we learned how to reference data in other sheets, and that Excel’s fill handle is smart enough to work with those references. Will this technique be useful for you? Would you like to learn more about linking data between sheets? How about linking data between workbooks? Please share your thoughts in the comments…

About the Author: Joseph Reese is the founder of Excel-Formulas.com an Excel consulting firm. Head over to his website if you want to improve your performance with Excel.



style=”display:inline-block;width:336px;height:280px”
data-ad-client=”ca-pub-6771269864674384″
data-ad-slot=”8343618572″>

  • trainerslisting com 02/25/2011, 5:39 AM Reply

    The post is a very good way for someone who wants to link data in excel between different sheets. I used the post for my FRM preparation and it worked wonders.

  • Joe 02/25/2011, 10:53 AM Reply

    I am glad it helped

  • Naveed 07/28/2011, 11:58 AM Reply

    Hi
    I have a problem and I have been actually getting around it manually for past two days but its just 2 much to do…I know the solution mite be basic but I am a begginer with excel. Here is the problem.
    I have 2 workbooks.
    Work book 1 contains Purchase orders for all transactions and a column which has the goods recieved date for the particular purchase order. about 5000 such transactions.
    workbook 2 contains some of these purchase orders,about 1000, but no goods recieved column.So what I want is that a column is created in which the corrosponding goods recieved date is printed infront of the related purchase order from the data in workbook 1. hope my question is clear. Thanks.

  • Calvin 08/21/2012, 2:23 AM Reply

    Very helpful. Now I am wondering if I can do this… and utilize sorting throughout my sheets. Say we have this example… now we want to sort by which budget has the most remaining. That is what I am wondering how to do.

  • Jynn 12/01/2012, 8:28 AM Reply

    I need some help. I currently have 2 worksheets linked together. The “recap” sheets calculates the revenue, the “invoice” sheet takes the data from the “recap” tab and prepares the actual invoice. The problem is the cells have “=recap!D6″ with the amount. The system is reading “=recap!D6″ instead of the amount. Is there a way to link the worksheets without the “=recap!D6″?

Leave a Comment

*