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:


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

Reference to a range of cells:


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:

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:


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


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


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:


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 name or 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

Note.  When creating a reference to another workbook by selecting the cell(s) in it, Excel always inserts absolute cell references. Therefore, 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.

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:


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:


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:


For example:


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):


For example:


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:


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


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!

You may also be interested in


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

  1. dubravko says:


    I use several VLOOKUP with table in another workbook.
    =VLOOKUP($E2;'C:\Users\daev\Desktop\Andreja\Rokovi\[Ispitni rokovi BAZA.xlsx]Sheet1'!$E$2:$V$4000;3;FALSE)

    everything working good, but if I want to copy this two workbook to another PC it is not possible to work.
    how can I write formulas to open workbook in folder they are saved.

  2. Aung Thiha says:

    how to make the increment of sheet reference when drag down from different file .
    My point is to auto update in one worksheet with different position of cell from another multiple sheets with fixed cell in arrangement of sheet.

  3. Nicole Barnum says:

    To increment the cell you'll need to use the CELL() function.
    Dynamic Sheet: =INDIRECT(sheet&"!"&CELL("address",A1))
    Dynamic Workbook: =INDIRECT("'["&workbook&"]"&sheet&"'!"&CELL("address",A1))

    For me it looks like this: =INDIRECT("'["&$D$1&"]"&$B$1&"'!"&CELL("address",B6))
    In D1 i have the file/workbook reference and in B1 I have the sheet name.

  4. Mr Bartaby says:

    Is there a way to replace the filename with the value of another cell

  5. jenzra says:

    How do take the formula that brings over information from one worksheet to another.
    So if I have Sheet2!A1 in sheet one in C3 then in D3 I want Sheet2!A19 so on and so forth. I want to find the formula I can drag over to each column so I don't have to go into the 4 separate work books and click every month to bring over my totals to combine everything. I tried some different things but can't seem to get the wording correct to get exactly what I need done.

  6. Barbara says:

    I have done it as described. It works across workbooks. However, the moment I make a change to workbook B, sheet B - and if it is only entering a value in any cell - and then save and close B.xlsx, I lose my value in A.xlsx/A-sheet-cell. The cell shows a #Bezug (in German) where the sheetname B should be. The rest of the link still shows correct, but the sheet-name got lost and was replaced by #Bezug.
    What is going on????

  7. jo says:

    how to copy particular one cell value to another sheet

  8. Robert Clark says:

    Hello helping,
    I am not educated in Excel but always looking into it possibly me. I have an instance where I want to tell the sheet:
    I have a drop down with 5 different module names(CMA1, CMA2,CMA3,CMA4, CMA5)
    when I select one from the drop down, say if CMA2 is selected and displayed in A3 then I want C3 - C26 to display the information on the following tab (CMA Cell Sheet) that is in the workbook sheet K4 - K26. I have tried:
    =IF(A3="CMA2",'CMA cell sheet'!K4:K26)
    but I get a "VALUE" error pop up.
    I would like to the same for CMA1, CMA3, CMA4, CMA5 but with different rows of information for each on the same CMA Cell sheet.
    I hope that you can help me with this.

  9. lander says:

    How to make cell to be a search engine??
    with a thousands of data you want to search

  10. PTimlin says:

    i use something like =IF(OR(A43="",C$42=""),"",INDIRECT("'" & A43 & "'!" &C$42))
    This allows me to in the A43 to Type the tab name Say Tab1 Tab2 Tab3 (Spelled the same) and in the C42 spot point the cell i want data from C(Column) 42(Row)
    This allows me to pull total from any page and any location. Without alot of Copy paste link
    This all checks if the Sheet name exists returns an error if does not

  11. Brendan says:

    Hi All,

    Please assist with a question of mine. I have it set up as Svetlana described and it works BUT doesn't automatically update between sheets when data is changed. If I want the main workbook (the one pulling data from other workbooks) to have the correct/current info...EVEN AFTER EVERY OTHER WORKBOOK IS SAVED...I have to double click on the cells then click the workbook its referencing from a file explorer type window. I want the data to auto-update on the main workbook as soon as the "source workbooks" are edited and saved b/c I have MANY lines of calculations being made and I don't want to have to double click on every one everytime I open the main file just to make data current.

    Is there any way to solve this problem?

    • Hello Brendan!
      First off, please check your Excel settings: Excel - File - Options - Advanced - Update links to other documents. Also, please go to Data->Edit Links and make sure the autoupdate for each link is enabled.

      Hope it'll help, otherwise please do not hesitate to contact me back.

      • Brendan says:

        Thank you for replying, but bad news, I tried both and its still not working! Please see below for more info, as well as another question on something totally different (obviously you seem to be an excel expert, doesn't hurt to ask while I have you):

        - The Excel-file-options-advanced-update things was already checked. The data-edit links thing was checked as "automatic" not manual, so good there too.
        - Not sure if its helpful, but on that edit links area it gives options to the right (update values, change source, open source, break link and check status). The "Sources" listed to the left shows the correct filename (I've used "closedworkbook.xlsx" and "openworkbook.xlsx"), type: worksheet, Update: A, and Status: Unknown. When I click on Check Status the status for each instantly changes to Error: Source not found (which I think is wierd...both files are saved on my PC, both saved right next to each other on the desktop, have not been deleted, etc. When I click on Update Values it opens the file explorer type box for me to assign the file again, which I do, then the status changes to "OK". Then when I click check status again it goes back to error source not found.

        OK, second totally separate issue. I'm developing a somewhat elaborate spreadsheet (for me as a novice) that has formulas pulling data from other sheets within the same file. Please let me give you an example of what I'm trying to accomplish and then the issue (b/c it actually works fine until the issue):
        - Sheet 1 has the base data. There are 12 columns and 30 rows worth of it. One column (D) is "Jan" and has 4 separate peoples names, in no particular order (Ex: 1 Tom Smith, 3 Sue Jones, 6 Nick Thomas and 20 Lisa Brown). Tom, Sue, Nick, Sue, Lisa, Lisa, Lisa, Sue, Lisa, etc.
        - Sheets 2 through 5 are named Tom, Sue, Nick and Lisa, and I'm using a formula to auto-populate data from Sheet Jan, bringing over all of the data pertaining to that specific person. On Sheet Tom, the formula I'm using is =FILTER(Jan!A:L,Jan!D:D="Tom Smith"). Works PERFECTLY! Then when I email the spreadsheet to a colleague it changes the formula to =_xlfn._xlws.filter($Jan.A:L,$Jan.D:D="Tom Smith") and doesn't work. Tried it on different comps, different versions of excel, tried it on libre office, etc.

        • Hello Brendan!
          I have looked into the recommendations from Microsoft and made an experiment with my own files. I have found out, as stated by Microsoft support service (, if Excel uses an external reference to another file, it tries to recalculate it to get accurate final data. If Excel can’t do this for some reason, it doesn’t consider the file version as final and doesn’t get anything from it.

          Thus, if any additional actions are required from a user to recalculate the data in the source file, you won’t get the data from this file until the user opens it and allows to update the data or run macros. Most likely, the file from which you pull the data contains some external references as well. Unless the file is open, the references can’t be updated. Therefore, it is not possible to automatically recalculate the data. Besides, there may be macros in the source file that also require an additional permit to be run. While Excel is waiting for this action to take place, it doesn’t transfer the data from this file to other files.

          It happens when the source file is not open in Excel. If you have opened the workbook which you refer to, there shouldn’t be any problems and the data from that file can be used in your table.
          I hope it’ll be helpful.

        • Hello Brendan!
          _xlfn normally shows up if an Excel workbook contains functions that do not exist in the version of Excel you work with. For example, there is the FILTER function in OFFICE365, but it doesn’t exist in OFFICE2013.
          This function is currently available to Office 365 subscribers in the Monthly channel. It will be available to Office 365 subscribers in the Semi-Annual channel starting in July 2020.
          If there is anything else I can help you with, please let me know.

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard