How to create external reference in Excel to refer to another sheet or workbook

This short tutorial explains the basics of an external reference in Excel, and shows how to reference another sheet and workbook in your formulas.

When calculating data in Excel, you may often find yourself in a situation when you need to pull data from another worksheet or even from a different Excel file. Can you do that? Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.

External reference in Excel is a reference to a cell or a range of cells outside the current worksheet. The main benefit of using an Excel external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.

Although external references in Excel are very similar to cell references, there are a few important differences. In this tutorial, we'll start with the basics and show how to create various external reference types with detailed steps, screenshots and formula examples.

How to reference another sheet in Excel

To reference a cell or range of cells in another worksheet in the same workbook, put the worksheet name followed by an exclamation mark (!) before the cell address.

In other words, in an Excel reference to another worksheet, you use the following format:

Reference to an individual cell:

Sheet_name!Cell_address

For example, to refer to cell A1 in Sheet2, you type Sheet2!A1.

Reference to a range of cells:

Sheet_name!First_cell:Last_cell

For example, to refer to cells A1:A10 in Sheet2, you type Sheet2!A1:A10.

Note. If the worksheet name includes spaces or non-alphabetical characters, you must enclose it in single quotation marks. For example, an external reference to cell A1 in a worksheet named Project Milestones should read as follows: 'Project Milestones'!A1.

In a real-life formula, which multiplies the value in cell A1 in 'Project Milestones' sheet by 10, an Excel sheet reference looks like this:

='Project Milestones'!A1*10

Creating a reference to another sheet in Excel

When writing a formula that refers to cells in another worksheet, you can of course type that other sheet name followed by an exclamation point and a cell reference manually, but this would be a slow and error-prone way.

A better way is point to the cell(s) in another sheet that you want the formula to refer to, and let Excel take care of the correct syntax of your sheet reference. To have Excel insert a reference to another sheet in your formula, do the following:

  1. Start typing a formula either in a destination cell or in the formula bar.
  2. When it comes to adding a reference to another worksheet, switch to that sheet and select a cell or a range of cells you want to refer to.
  3. Finish typing the formula and press the Enter key to complete it.

For example, if you have a list of sales figures in sheet Sales and you want to calculate the Value Added Tax (19%) for each product in another sheet named VAT, proceed in the following way:

  • Start typing the formula =19%* in cell B2 on sheet VAT.
  • Switch to sheet Sales, and click on cell B2 there. Excel will immediately insert an external reference to that cell, as shown in the following screenshot:
    Creating a reference to another sheet in Excel
  • Press Enter to complete the formula.

Note. When adding an Excel reference to another sheet using the above method, by default Microsoft Excel adds a relative reference (with no $ sign). So, in the above example, you can just copy the formula to other cells in column B on sheet VAT, the cell references will adjust for each row, and you will have VAT for each product correctly calculated.

In a similar manner, you can reference a range of cells in another sheet. The only difference is that you select multiple cells on the source worksheet. For example, to find out the total of sales in cells B2:B5 on sheet Sales, you would enter the following formula:

=SUM(Sales!B2:B5)
Creating a reference to a range a cells in another worksheet

This is how you reference another sheet in Excel. And now, let's see how you can refer to cells from a different workbook.

How to reference another workbook in Excel

In Microsoft Excel formulas, external references to another workbook are displayed in two ways, depending on whether the source workbook is open or closed.

External reference to an open workbook

When the source workbook is open, an Excel external reference includes the workbook name in square brackets (including the file extension), followed by the sheet name, exclamation point (!), and the referenced cell or a range of cells. In other words, you use the following reference format for an open workbook reference:

[Workbook_name]Sheet_name!Cell_address

For example, here's an external reference to cells B2:B5 on sheet Jan in the workbook named Sales.xlsx:

[Sales.xlsx]Jan!B2:B5

If you want, say, to calculate the sum of those cells, the formula with the workbook reference would look as follows:

=SUM([Sales.xlsx]Jan!B2:B5)

External reference to a closed workbook

When you reference another workbook in Excel, that other workbook does not necessarily need to be open. If the source workbook is closed, you must add the entire path to your external reference.

For example, to add up cells B2:B5 in the Jan sheet from Sales.xlsx workbook that resides within the Reports folder on drive D, you write the following formula:

=SUM(D:\Reports\[Sales.xlsx]Jan!B2:B5)

Here's a breakdown of the reference parts:

  • File Path. It points to the drive and directory in which your Excel file is stored (D:\Reports\ in this example).
  • Workbook Name. It includes the file extension (.xlsx, .xls, or .xslm) and is always enclosed in square brackets, like [Sales.xlsx] in the above formula.
  • Sheet Name. This part of the Excel external reference includes the sheet name followed by an exclamation point where the referenced cell(s) is located (Jan! in this example).
  • Cell Reference. It points to the actual cell or a range of cells referenced in your formula.

If you've created an reference to another workbook when that workbook was open, and after that you closed the source workbook, your external workbook reference will get updated automatically to include the entire path.

Note. If either the workbook name or sheet name, or both, include spaces or any non-alphabetical characters, you must enclose the path in single quotation marks. For example:

=SUM('[Year budget.xlsx]Jan'!B2:B5)

=SUM('[Sales.xlsx]Jan sales'!B2:B5)

=SUM('D:\Reports\[Sales.xlsx]Jan sales'!B2:B5)

Making a reference to another workbook in Excel

As is the case with creating an Excel formula that references another sheet, you don't have to type a reference to a different workbook manually. Just switch to the other workbook when entering your formula, and select a cell or a range of cells you want to refer to. Microsoft Excel will take care of the rest:
Making a reference to another Excel workbook

Notes:

  • When creating a reference to another workbook by selecting the cell(s) in it, Excel always inserts absolute cell references. If you intend to copy the newly created formula to other cells, be sure to remove the dollar sign ($) from the cell references to turn them into relative or mixed references, depending on your purposes.
  • If selecting a cell or range in the referenced workbook does not automatically create a reference in the formula, most likely the two files are open in different instances of Excel. To check this, open Task Manager and see how many Microsoft Excel instances are running. If more than one, expand each instance to view which files are nested there. To fix the issue, close one file (and instance), and then open it again from the other file.

Reference to a defined name in the same or another workbook

To make an Excel external reference more compact, you can create a defined name in the source sheet, and then refer to that name from another sheet that resides in the same workbook or in a different workbook.

Creating a name in Excel

To create a name in Excel, select all the cells you want to include, and then either go to the Formulas tab > Defined names group and click the Define name button, or press Ctrl + F3 and click New.

In the New Name dialog, type any name you want (remember that spaces are not allowed in Excel names), and check if the correct range is displayed in the Refers to field.

For example, this is how we create a name (Jan_sales) for cells B2:B5 in Jan sheet:
Creating an Excel name

Once the name is created, you are free to use it in your external references in Excel. The format of such references is much simpler than the format of an Excel sheet reference and workbook reference discussed earlier, which makes the formulas with name references easier to comprehend.

Note. By default, Excel names are created for the workbook level, please notice the Scope field in the screenshot above. But you can also make a specific worksheet level name by choosing a corresponding sheet from the Scope drop-down list. For Excel references, the scope of a name is very important because it determines the location within which the name is recognized.

It's recommended that you always create workbook-level names (unless you have a specific reason not to), because they significantly simplify creating Excel external references, as illustrated in the following examples.

Referencing a name in another sheet in the same workbook

To reference a global workbook-level name in the same workbook, you simply type that name in a function's argument:

=Function(name)

For example, to find the sum of all the cells within the Jan_sales name that we created a moment ago, use the following formula:

=SUM(Jan_sales)

To reference a local worksheet-level name in another sheet within the same workbook, you need to precede the name with the sheet name followed by an exclamation mark:

=Function(Sheet_name!name)

For example:

=SUM(Jan!Jan_sales)

If the sheet names includes spaces or mon-alphabetic chars, remember to enclose it in single quotes, e.g.:

=SUM('Jan report'!Jan_Sales)

Referencing a name in another workbook

A reference to a workbook-level name in a different workbook consists of the workbook name (including the extension) followed by an exclamation point, and the defined name (named range):

=Function(Workbook_name!name)

For example:

=SUM(Sales.xlsx!Jan_sales)

To reference a worksheet-level name in another workbook, the sheet name followed by the exclamation point should be included as well, and the workbook name should be enclosed in square brackets. For example:

=SUM([Sales.xlsx]Jan!Jan_sales)

When referencing a named range in a closed workbook, remember to include the full path to your Excel file, for example:

=SUM('C:\Documents\Sales.xlsx'!Jan_sales)

How to create an Excel name reference

If you have created a handful of different names in your Excel sheets, you don't need to remember all those names by heart. To insert an Excel name reference in a formula, perform the following steps:

  1. Select the destination cell, enter the equal sign (=) and start typing your formula or calculation.
  2. When it comes to the part where you need to insert an Excel name reference, do one of the following:
    • If you are referring to a workbook-level name from another workbook, switch to that workbook. If the name resides in another sheet within the same workbook, skip this step.
    • If you are making a reference to a worksheet-level name, navigate to that specific sheet either in the current or different workbook.
  3. Press F3 to open the Past Name dialog window, select the name you want to refer to, and click OK.
    Creating a reference to an Excel name in another workbook
  4. Finish typing your formula or calculation and press the Enter key.

Now that you know how to create an external reference in Excel, you can take a benefit from this great ability and use data from other worksheets and workbooks in your calculations. I thank you for reading and look forward to seeing you on our blog next week!

Table of contents

358 comments

  1. I want to refer a closed worksheet cell.

    ='D:\10187\CSS\[=BW169]PipCost'!$D$2.

    How to put filename with using formula?

  2. Hi,

    I am facing another issue in referecning using Index & Match. When I pass a reference of one sheet in the second sheet, the value becomes visible and my formula works on it. But if I change the position of the column by inserting another column in the first excel sheet, the formula stops working. I have tried it to resolve by passing the "Name" of teh list as a reference in the 2nd sheet instead of Fixed column, but the issue is not resolved. Can anyone help on this?

    Aftab

  3. Hi, i have an excel spreadsheet with office sales open all day and its linked to each departments excel workbook which has their sales on it. Currently i have to select update everytime i want upto date sales figures. Id like my excel book to update each time a department updates their sales without my telling the spreadsheet workbook to do it. Is this possible? The workbooks are all saved in a public drive and each team opens their workbook from the public drive on their pc and adds sales as they get them. Thanks

  4. Hello Svetlana Cheusheva,
    Your article is so useful and it helped me a lot learning insights of excel.

    How I can reference data from online uploaded excel sheet?

    Your quick reply would be highly appreciated.

  5. I am working with 32 sheets. need to draw information from different sheets 6 times in each column. is there a way to name the sheet 1 time instead of naming the sheet 6 times? there is only one other sheet for each column that data is drawn from. I tried "sheet name" in c5 and tried to write the formula =c5'!b23 with no luck.

  6. Thanks a million! Note that it is much safer to use named references when linking to external sheets.

  7. Is there a way to set a value in an external reference. All of the examples I see so far are retrieving references from an external. I would like to be able to update the external value.

  8. so efficient

  9. Hello

    Is there any way to have a variable within the file name of an external link?

    For example:

    File A has cells that link to File B.

    The next month it needs to change from April to May. Rather than change the linking in each cell to point to the new file I would like to have a cell in file A where I just Type "May" instead of "April" and the links automatically update the file direction to May.

    Below is the example- Ideally I would love to have a cell in File A where I just put in "05 May" and "May" and it updates the changes below in all formula so I don't have to manually go through and change everything. (There are hundreds of linked sheets... they all change by only the month in naming and saving convention)

    April link
    ='\\2016 Sheets\04 Apr\[April 1 2016.xlsm]Purchases-Sales'!$K$13
    May Link
    ='\\2016 Sheets\05 May\[May 1 2016.xlsm]Purchases-Sales'!$K$13

    • Kelly,

      Please try using the nested function
      INDIRECT(CONCATENATE()).

      For example put your variables in A1 and A2
      =INDIRECT(CONCATENATE("'\\2016 Sheets\",A1,A2,
      "Purchases-Sales'!$K$13")

  10. Good morning,
    I am working with 2 sheets. One is taking all the information to the other.

    But, when I try to put in different order, it goes wrong.

    How can I do to have different SORT in both tables or sheets?

    • If you're having issues when sorting the source data, i'm suspicious you're using VLOOKUP.
      try switching to Index/Match and you'll never look back!
      =INDEX("Range with values you want",MATCH("Specific Value","From this Range",0))
      you can move those columns anywhere you want and the formula still works. You can use cell references, structured table references, or even named ranges

  11. How about if you are working on excel online? Thank you.

  12. Hi Svetlana,

    I have a unique problem, we created a [Planner.xlsx] template to enter our weekly sales. So instead of creating a new excel sheet every time, we save the template as a [Planner 25-04-16.xlsx]. We also have 3 other sheets to Planner.xlsx and save those sheets similarly as well. I recently started to externally reference the 3 sheets to Planner.xlsx and everything works fine. But when I save all the sheets as Planner 25-04-16.xlsx, Book1 25-04-16.xlsx, Book2 25-04-16.xlsx, and Book3 25-04-16.xlsx; the formulas still reference to PLanner.xlsx, but I want all of them to reference to the weekly sheets. Is there a way to do this without manually changing the references on a weekly basis.

    Thanks in advance.

  13. I have 4 tabs (1stwk, 2ndwk, 3rdwk, 4thwk) wherein I need the info from B2 of each tab to be totaled and entered on my final tab. I tried =SUM(April25!Sheet1!Sheet1!Sheet1!A7D9+April18!b2+April11!b2+April4!B2) but didn't work. What am I doing wrong?

    • You have to try this simple one,

      =SUM(Sheet1!B2,Sheet2!B2,Sheet3!B2,Sheet4!B2)

  14. I would like to reference an entire tab in one worksheet as a tab in another worksheet.
    is that possible? how?

  15. Hi, great, helpful posts. I was wondering if you could help me with an INDEX/MATCH problem where all the information I am referencing in sheet A is actually in sheet B, so only the cell/reference in sheet A in "MATCH" is actually in the sheet where I am building my function, but I cannot figure out a way to reference the column number correctly for the INDEX function as it resides in a different sheet simply typing the column number clearly does not work... Thank you!

  16. Svetlana Cheusheva
    I receive a number of excel files (on a weekly basis) and one file could have up to 1000 entries of items purchased.Information of 1 particular item is contained in 1 file.
    When i receive items, am supposed to extract information of particular items from the files i received. However, not all items received in a particular consignment are contained in one file, i would be forced to go through 10 files to get all the information.
    I would wish to come up with a file where i will only have to type in product code and container number and information is extracted from the weekly sheets stored in one folder.

  17. I have come up with a cell reference using the ADDRESS formula. Is there anyway to use this when referencing another sheet in a workbook?

    So instead of:
    ='Ex workbook'!A1

    Something along the lines of:
    ='Ex workbook'! & ADDRESS(1,MATCH(WEEKNUM(F1),'Ex Workbook'!$1:$1,0))

    I added the & to show where I need to join the two halves of the reference. Thanks!

  18. Below is an example of my formula.

    ='All-Pr X Wght'!EF$3/'All-Pr X Wght'!$B$3*100

    I cannot get 'EF$3' to automatically change to 'ef$4' when copying down a column?

    Why?

    • Hi Anna,

      Because you fixed the row reference with the dollar sign. When you add $ in front of a column and/or row coordinate, it locks that coordinate(s), and the reference does not change no matter where the formula is moved or copied. Just change it to EF3 and the reference will adjust based on the relative position of a cell where the formula is copied. For more information about absolute and relative cell references, please check out the following tutorial: Why use dollar sign ($) in Excel formulas - absolute and relative cell references.

      • hii....i just want to know whether i can get all the values of a data in a page by making any reference in a dropdown box...ie..... if i select any name created in a dropdown box, it should show all the values related to that name in another page.

  19. Hi Felipe,

    You can download the free add-in "Power Query" if you have Excel 2013. Once the link is established between the two excel sheets, workbooks within the network or even an external location, the excel workbook reading the data will automatically update once the source excel sheet is saved [whether open or closed].
    Try this as this is very effective to produce reports for data flowing from other departments/braches etc., and even from the website.

    Regards,
    Ramki

  20. Hi Svetlana Cheusheva!

    Awesome post!

    I was wondering to link tables "formated as table" or named ranges in another workbooks! Is there a way of doing it?!

    • Hi Felipe,

      Of course, you can do this.

      To link to an Excel table located in another workbook, you can create a so-called "structured reference" consisting of the workbook name, table and column names, e.g.:

      =SUM(Book1.xlsx!Table1[Sales])

      Where [Sales] is the name of the column you want to sum. If the workbook name contains spaces, remember to enclose it in single quotes, e.g.:

      =SUM('2016 sales.xlsx'!Table1[Sales])

      In fact, you don't have to memorize the syntax of the structured reference. Simply select the cells in the table when typing your formula as you usually select ranges, and those names will appear in the formula automatically.

      As for a reference to a named range in another workbook, please see the following example in this tutorial: Referencing a name in another workbook.

      • WOW, nice!

        Is there any workaround while referencing closed workbooks? Cos, SUMIF and COUNTIF and Named Ranges isnt working, returning an error. That's the matter!

        • Felipe,

          When referencing a closed workbook, you have to include the full path, like:

          =SUM('D:\Documents\Book1.xlsx'!sales)

          Where "sales" is a named range in Book1.

          • Hi Svetlana Cheusheva,

            I did all this things, but none of them works fine! It might be something Office versions. I'm using 2016 version! And, does not work, even in another computer. If I use =SUM('D:\Documents\Book1.xlsx'!B:B) instead of =SUM('D:\Documents\Book1.xlsx'!sales), works!! Really weird!

            Ramki,

            I have downloaded PowerQuery and it works! But, this SUM will be performed by another person, which may not like to use it. But I'll try to show this feature.

            • Felipe,

              Yep, this is really weird. Your first reference should not work by any means, because the sheet name is missing. You probably meant something like:

              =SUM('D:\Documents\[Book1.xlsx]Sheet1!'B:B)

              And I can think of only one reason for the named range reference not working - you've created a worksheet-level name (please check the Scope parameter of your named range). If it is the case, then add a worksheet name to the reference.

              Or, open the other workbook and create a reference to your named range by selecting it using the mouse. Then close that other workbook, and Excel should make the required corrections to the reference automatically. (For what it's worth, I'm using Excel 2013.)

              • heloo admin ...please contact me on my email ...i have some questions about excel formulas ..thanks and regards

      • I just need to retrieve those data without sum function. Is that possible

    • Hello: I have two worksheets. I have followed your naming convention in referncing another worksheet cell contents so that it shows in the 2nd worksheet. I have names and addresses, etc. in the first sheet. When I complete the formula, any blank cell in the first sheet is showing a "0" in the cell instead of keeping it blank. Is there something to put in the formula, or a way that will not show the zero unless there is actually a zero in the cell that I want to show?
      Thank you.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)