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 have a master data file with multiple sheets of data, each sheet with a different weeks sales data in it. Each sheet uses the format WEEK then week no as its name ie WEEK 1,WEEK 2, WEEK 3

    I then want to lookup from another workbook to figures in theses sheets but want to be able to easily change which Weeks sales data it's looking up to

    Is there a way to use a number in a cell to change the worksheet the formula looks up to

    Ie type 1 into cell A1, and the formula will look up to the WEEK 1 sheet, change the cell to a 23 and the formula will change to look up to WEEK 23 sheet

  2. Hi,

    I'm using the latest Excel version via Office365, so I can use data ranges converted to tables.
    My first workbook contains several sheets, each with a specific table. Data is referenced via the x.lookup function to table fields instead of row/col ranges. Works great !
    This workbook is used as a data container and is read-only (can only be edited by myself).

    A second workbook is pulling data from the first one , also by using the x.lookup function and extracting data from the different sheets based on specific parameters.

    Both workbooks are stored on OneDrive in the same folder.

    It all works fine as long as the first workbook (the data container) is opened on my PC. When I close this file, The second workbook gives error messages as result for the x.lookup referring to the tables. It seems that the references in the x.lookup function are not recognized any more.

    Is this normal behaviour ? Can I extract data from (dynamic) tables in other workbooks when these files are closed ? I hope this can be done without the use of PowerQuery (which is also a great tool).

    Looking forward to your reply.

    • Hello!
      You can extract data from a closed workbook with a VBA macro, ActiveX Data Objects, or with Power Query. You can't do this with a regular reference.

  3. Nice article but what if i have worksheet named "Index", "TEST01" and "TEST02". In Index Worksheet in column A i have the values A1=TEST01, A2=TEST02. Now against B1 under Index sheet i want to capture the data from TEST01!A1 but instead of giving the direct formula i want Index.B1 to refer to the value present in index.A1 and then fetch the data.

  4. Hello, I have an excel workbook(1) that references sheets and cells in an external workbook(2), this works fine. My question is how can I easily redirect workbook(1) to the same cells but in a different external workbook?
    So I want to be able to reference the same sheets and cells, but just in a different workbook without having to manually change the references. I want changes in workbook(2) to be reflected in workbook(1).
    Hope this makes sense.

    Paul

  5. Question for some
    I have a spread use used daily and saved on every dales Fuel Sales December but I have to take figures for December and go to my folder create January 2022 Master and input these numbers and start Jan Fressh

    is the a formula i can put in January 2022 Master so December would auto carry to January 2022 Master

    Let me know

  6. Just finished an article by Svetlana Cheusheva - "How to create external reference in Excel to refer to another sheet or workbook'. I think it is one best I have read in a few months / years. However, I have ONE major complete for both her and your organization. I have spent several minutes n her's and your website(s) attempting to find where i can register for your daily, weekly, or monthly Excel article / messages / life updates. Have yet found where that form is located. A few of your competitors maintain such a thing & I would appreciate the ability to read more of her and / or your entity's emails. Thoughts Please advise

    • Hi Gregg,

      Thank you so much for your wonderful feedback! At the moment, we don't have such a form, sorry. I will talk to our tech guys and we'll implement a subscription to our weekly digest as soon as we can. Thank you for pointing out that omission!

  7. Hi,

    I want to use external references from one workbook (source) to another (destination). I want to share the destination workbook with my colleagues. I do not want to share the source workbook with them.

    My question: Does someone need access to the source workbook to see the data that is externally referenced in the destination workbook? Or is access to the destination workbook enough?

    Thanks.

  8. Hi, I am needing to pull data from one tab (or worksheet) titled Training Records into another tab to create statistics from. I am needing to nest 3 conditions. (If cell = assigned, then appear as Incomplete. If cell = a date 9-Dec-21, then appear as complete. If cell = not required, then appear as NR.)

    This is the formula that I have with a #NAME? error. I am using Microsoft 365.

    =IFS('Training Records'!J2=assigned,Incomplete,'Training Records'!J2=DATE,Complete,'Training Records'!J2=not required,NR)

    • I also tried this formula. I know I am so close.

      =IFS('Training Records'!J2=assigned,Incomplete,'Training Records'!J2=DATE,Complete,'Training Records'!J2=not required,NR)

    • Hello!
      Always use text values with double-quotes. Use the DATE function to specify the date.

      =IFS(J2="assigned","Incomplete",J2=DATE(2021,12,9),"Complete",J2="not required","NR")

      I hope my advice will help you solve your task.

      • Your suggestion worked. This is the formula that I have now.

        =IFS('Training Records'!J3="assigned","Incomplete",'Training Records'!J3=DATE(2021,1,1),"Complete",'Training Records'!J3="not required","NR")

        My last remaining question is how to configure the DATE formula (2021,1,1) to represent any date. Not just a specific date. Is the format listed above correct?

        Thanks, Again!

  9. Hi,
    I have a sheet with Construction project details. On the other summary sheet, I need to select the project and few relevant date of selected project should appear. How can i do that? can you please help.
    Thanks

  10. Hello Svetlana!

    Suggestion -
    In the section "External reference to an open workbook", add a troubleshooting note, that if selecting a range in the referenced file is not automatically generating the reference in the formula, then perhaps the excel files are open in different instances of excel. To confirm, open Task Manager, and see if all open Excel files are nested under a single instance of Excel. If not, close one file and open it again from the other file.

    I just ran into this issue today. Found the solution here: https://answers.microsoft.com/en-us/msoffice/forum/all/cant-reference-a-cell-in-another-file/ce05b277-5b67-4fdf-ab8b-e63a1e7a610d

    Thank you for your thorough educational articles. They have been an oasis for me over the years.

    Regards,
    - Sergey

    • Hi Sergey,

      Thank you so much for this useful information and for your kind words! The tip about different instances is added. Thank you :)

  11. Hello,

    I have tried the reference to a range of cells but that didn’t work:
    =sheet1!a1:b8 on sheet2 in cell a1 results in only A1 from Sheet1, not the range.
    I would like to have all referenced data A1:B8 from sheet1.

    What’s going wrong, i am using excel 2010.

    A1:B8 is a table, is it posible to reference to a whole table?

    • Solved.
      Doesn’t work in excel 2010.
      Tried 365 ‘21 there it works: awesome!

  12. Hello!

    Thank you for sharing, but I'm curious, because I work with a lot data, how to take address from other cell to be a task , for example if I want to take data from worksheet name A in cell B5, then I can type = A!B5, but I will do this in many times, so I think I can create colom wich fill with A (in cell A5) A A A A then I make another collom with B5 (in cell B27) B6 B7, then I don't want to type it one by one but want to drag it so it will be automatically take data, do u know how to do that, so I just type =A5!B27 , then the excel with take data from sheet A cell B5, but I can't just type that, would you know the formula? Thank you

      • hello, sorry my question is not clear, so here it is, I want to retrieve data from another worksheet, if typed manually it will take a long time because the data is a lot, so I want to ask if the =worksheetname!cellname command can be written by clicking on a cell another, so there is already another cell containing the worksheet and cellname in a different cell, so I made 2 columns, the first column contains the worksheet name, the second column contains the cellname name, if I can fill in the command to retrieve data by clicking on the cell in the worksheet name column and the cell in the cell name column, I don't need to write one bye one "address of the data to be retrieved but only need to drag it down. . is this possible? so the =worksheetname!cellname command is filled from 2 other cells.

        • Hello Susanto,
          You may have better luck using a lookup (my favorite is the INDEX-MATCH method).
          But if you really need it as you described - one possible solution for you may be the INDIRECT() function. So if your addresses are listed in column A, you can write the following formula in B1, and drag (or copy) it down: =INDIRECT(A1)

          Regards,
          Sergey

  13. I have a several excel files in a folder. Each file has the same 1st sheet named movement. I want to list each filename in a column and next to it the value from cell B79 in the movement sheet from each corresponding file. How can i do this without opening each file.

  14. I have several sheets within a file. Each sheet has a formula to get data from the 'INFO' sheet. =INFO!I1
    When the file gets sent to others and then the sheets are edited by different people and then all the sheets are copied back into the master file, this formula is messed up as it is trying to reference a sheet from a different file. How can I make this formula always pick the data from the 'INFO' sheet within this same file rather than going looking for an external file?
    ='[Sub Inspection Forms_R12_20210816.xlsx]INFO'!I1

  15. Hi,

    I have a workbook but some of the data is sensitive, whereas a lot of it is very useful for another department in my company. Is there a way to reference the relevant data in workbook 1 to a (supposed) workbook 2 and to have it update whenever we change data in workbook 1 without allowing file access to workbook 1?

    Workbook 1 is saved in a SharePoint Library that only my department has access to for confidentiality reasons, and we cannot allow access to this file to anyone outside my department but we really don't want to duplicate work by copying and pasting data that already exists for our other department.

    I hope this makes sense!

    Thanks in advance.

  16. I have a master workbook saved on my desktop that pulls totals from several workbooks that are on my companies sharedrive. Several people work within the workbooks being referenced and they often need to add rows within the worksheets. This unfortunately is messing up the reference every time. I have removed the "$" so they should be relative refences. It does not appear to be happening when I have the master workbook opened at the time of the row addition. I am not sure what I am missing. Below is an example of what the reference looks like. F48 is where the total sits when I create the sheet. When a row is added it still pulls from F48 although the total is now in F49.

    ='\B\2020\Timesheets\project\[07-2020.xlsx]NAME'!F48

    Do you need more information?

    Thanks!

  17. IF functions between workbooks

    I wish to use the IF function by referencing cells in an external workbook. I have one cell in an external workbook that is to be used as the true or false test, and if true, I want to pull the contents of another cell in the same external workbook through to my current workbook.

    I have used the link to the cell in the external workbook with the formula in the current workbook and it is not returning the result I want. The formula is

    =IF('[Crabbet Park House_Project WorkBook_v1.0.xlsx]Actions'!$K$9="Open",'[Crabbet Park House_Project WorkBook_v1.0.xlsx]Actions'!$C$9,"")

    Can anyone identify what is wrong?
    Thanks
    Chris

    • Hello!
      I don't have your files. Therefore, I cannot check the work of the formula. You have not written what exactly does not work.
      For a formula with external references to work, the external workbook must be open.

  18. some diferente problem.
    Have about 10 workbooks that i need to refer, Each workbook has the same range like .=

    =MÉDIA('D:\Users\local\[name1.xlsx]name1'!$GP$4:$XFD$4)
    =MÉDIA('D:\Users\local\[name2.xlsx]name2'!$GP$4:$XFD$4)

    The range change every day (so tomorrow my range will be GQ$4:$XFD$4) for each workbookl

    How can I change the range GP$4:$XFD$4 to GQ$4:$XFD$4 for the multiples cells. I mean, how can I change only one istead 10?

      • And thats something I never tried. And Works fine

        Thanks

  19. Hi - I have a colleague who is trying to extract data from one workbook for use in another. Some functions work when the source workbook is closed (direct references, look-ups, transpose), while an indirect function does not. Is there a guide or list that indicates which functions will update correctly from a closed workbook, and which ones will not?

    Cheers,

    Dean

  20. Hey Seniors
    I am facing the issue of Hyperlink or link. When I shared my files to another pc the link is not working?
    Is there is any solution to one workbook hyper link to another workbook sheets?

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