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!

202 comments

  1. Hi, I want to use the same excel for different months by making a new copy. However, I run into an issue. in the original excel, the 1st worksheet has data for April 2025 and it is linked to the 2nd worksheet to reflect April 2025 number in a table for containing all months. So, when i copy the excel and change it into May 2025, I changed the date in the first worksheet to May2025, however it is still linked to the 2nd worksheet in the month of April. Is there a better way to do this. Can it automatically change the formula linking the 1st and 2nd worksheet to May 2025

  2. Good day

    I need some assistance please, if any one can help.

    I have a drop down list of customers with there respective discounts in separate colums. in the same sheet, is it possible to select the customer in my drop down list and have the discount for said customer reflect in different colums on my worksheet.

    I don't know if I am explaining this correctly.

  3. Hi, thank you for the great article! I'm referencing cells from a different (source) workbook to my current workbook and I'm having trouble understanding what happens when I edit/relocate referenced cells in the source workbook. For example, I can edit the content of the referenced source cell and it will be updated fine in the current. But when I move it unless the current file is open, the current won't pick up the change - it will keep the reference to the old location. Is that normal? Does that mean that every time I relocate some cells in source, for example, by inserting rows or columns I have to keep the current file open?

    1. Hello Marco!
      Yes, that behavior is completely normal in Excel.
      Here’s a bit more detail to help you understand:
      Editing Content: When you edit the content of a referenced cell in the source workbook, the changes will be reflected in the current workbook, even if it is closed. This is because Excel updates the content of linked cells correctly as long as the cell reference remains the same.
      Relocating Cells: When you move or relocate referenced cells in the source workbook (e.g., by inserting rows or columns), the current workbook will only update the references if it is open at the same time as the source workbook. If the current workbook is closed, it will retain the original references, pointing to the old locations.
      This means:
      Source Workbook Open, Current Workbook Open: References update automatically.
      Source Workbook Open, Current Workbook Closed: References do not update and stay linked to the old locations.
      To ensure that your references are updated correctly when you move or relocate cells in the source workbook, you should keep the current workbook open. This way, Excel can dynamically update the references in real-time.
      If you frequently need to relocate cells in the source workbook, having both workbooks open simultaneously will save you from manually updating the references in the current workbook.

      1. Thank you Alexander so much! This is my conclusion as well. I have an idea to help with this. Let's say I know I will only insert/delete rows, not columns, so the referenced cells will move up or down the column only. I was thinking of referencing additional empty cells in that column bellow the cells with values. For example, if I want to reference cells from A1:A25, I will create references for A1:A100, so that later when rows are added/deleted, this ensures my current workbook remains updated.

  4. If both the source file and the recipient file are password protected, how does one overcome this when the source file is closed?

    1. Hello Simon!
      Unfortunately, if both the source file and the destination file are password-protected, you won't be able to get protected data in Excel using an external link when the source file is closed.
      Excel requires access to the open file to establish the link and retrieve the data.
      You can solve this problem by using a VBA macro.

  5. I'm looking for an easy way to reference data in one sheet that is in a row and have it populate in another sheet in a column. For example, sheet one has data in A1 B1 C1 etc. On sheet 2 I want a reference to the data in sheet 1 except in a column. For example, Sheet2, A1 references Sheet1, A1 but Sheet2, A2 references Sheet 1, B1, and Sheet2 A3 references Sheet1,C1.
    In other words, whatever is entered in the cells along row 1 in Sheet1 will automatically display in the cells in Column A in Sheet2. I have found a way to do this by making a reference cell in Sheet 2 then copying that formula down the column, but then I have to individually edit the data in each cell to make the reference correct.

  6. Company recently killed off the file servers and forced us into the cloud via Sharepoint/TEAMS. This, of course, has killed all VBA based tools - or at least put us on notice not to use VBA for anything new. We have a workbook where each sheet is a log related to an individual. Each sheet is names for the individual it concerns. I now need to produce a summery report that pulls from a few specific cell on each sheet.

    The problem I need to solve, without VBA, is how to reference sheets by number in a formula, since the sheet names are the names of the people.

  7. Hi,

    Let’s say I have a sheet called ‘sheet 1’ which has 2 cell addresses listed (let’s say the terms $A$1 and $Z$30 are listed in cells A1 and A2 and these terms were created by using the address formula). How can I create a vlookup or Xlookup that has a lookup value in ‘sheet 1’ but a lookup array on ‘sheet 2’ - with the array being decided from the 2 cell addresses listed in ‘sheet 1’? I would’ve thought I could set the array as indirect(A1):indirect(A2) to set the array as $A$1:$Z$30, but I am unsure how to tell it to look up this array in sheet 2 as opposed to sheet 1.

    I really appreciate the work you do to make learning excel accessible to so many people. If you can provide a solution to push me in the right direction I will be truly grateful.

    Thanks

      1. Thanks very much for your response. I have tried the above and have also tried combining the references in a separate cell using concatenation. Using concat, cell B112 of sheet 3 has a concat formula which outputs $BE$111:$BE$132 in the cell and cell B114 of sheet 3 has a concat formula which outputs $AL$111:$AL$132 in the cell. On sheet 3, I am trying to get an Xlookup which has the lookup array of $BE$111:$BE$132 on sheet 1 (indirect of B112 on sheet 3) and a return array of $AL$111:$AL$132 on sheet 1 (indirect of B114 on sheet 3), with BE10 of sheet 3 as the lookup value.

        I have attempted the following formula on sheet 3, but it returns a #ref error:

        =XLOOKUP(BE10,INDIRECT("sheet 1!"&B112),INDIRECT("sheet 1!"&B114))

        When testing, I found the following formulas also caused a #ref error:

        =INDIRECT("sheet 1!"&B112)
        =INDIRECT("sheet 1!"&B114)

        I have double-checked the spelling of the sheet names and they match, including the case. I have also tested using a standard XLOOKUP below and this worked correctly:

        =XLOOKUP(BE10,'sheet 1'!$BE$111:$BE$132,'sheet 1'!$AL$111:$AL$132)

        Thanks very much for looking into this - I really appreciate the time. Is there anything you think I can change with the combined XLOOKUP and INDIRECT formula on sheet 3 so that it correctly pulls through the required values from sheet 1?

  8. hello, how to make PENGELUARAN_INSIDENTAL reference shorter? is there any shorter formula?
    =MAIN!C4-(PENGELUARAN_INSIDENTAL!D17+PENGELUARAN_INSIDENTAL!D43+PENGELUARAN_INSIDENTAL!D56+PENGELUARAN_INSIDENTAL!D69)+MAIN!F21

    1. Hi! If you need to use external links, try renaming the PENGELUARAN_INSIDENTAL worksheet and making its name shorter. Then the formula will be shorter.
      You can also give short names to these cells and use those names in the formula. You can find the examples and detailed instructions here: How to define and use names in Excel.

  9. I have a question regarding whether you can reference multiple ranges of cells that are scattered throughout another sheet, without having to go line by line.
    This has been working so far but I have over 6,000 rows and many more cells that I need to reference.

    For example:

    ='Table 1'!A34:P34
    ='Table 1!A64:P64
    ='Table 1'!A65:P65
    ='Table 1'!A81:P81
    ='Table 1'!A86:P86
    ='Table 1'!A94:P94
    ='Table 1'!A118:P118
    ='Table 1'!A119:P119
    ........

  10. Hi,
    I have a workbook containing 2 sheets - 'Sheet A' and 'Sheet B'. In 'Sheet B' I have a value in cell F1. In 'Sheet A" I have a cell which is pointing to the value in 'Sheet B" cell F1 (='Sheet B'!F1). I always want to retrieve the value from this exact cell "F1". But daily I am inserting a new column to the left of column F in Sheet B containing a different value. This is causing the cell referenced from Sheet A to change to G1, H1, etc. whenever I insert a new column. I have tried placing a $ sign before "F" and "1" in reference from Sheet A (i.e., ='Sheet B'!$F$1), but this does not work. Do you have a solution to this problem? Thanks in advance for any help you may provide.

      1. Alexander, thanks for the resolution to my problem!

  11. I am trying to use a formula to insert the name of a sheet into the reference.
    Lets say my formula that references the other sheet is
    =+'Objective 2'!F$2
    But instead of typing 'Object 2' into the formula, I have "Objective 2" and other sheet names in other cells in the current sheet.
    So I want to replace "Objective 2" with a cell reference. Can I do this?
    Something like
    =+'B4'!F$2 Where the value in cell B4 is "Objective 2"

    Thanks

  12. Hi,

    I have a formula for a budget excel sheet and I'm trying for one month per sheet in one file. But when i duplicate the page, the formula is linked to the first page and does not show the result to the page I'm on.

    Here's the formula : =CONCATENER("Solde : "&TEXTE(@Solde,"[Vert]# ##0,00 $;[Rouge]-# ##0,00 $"))

    I tried to put the reference page at the beginning of the formula but it didn't work.

    Thank you

    1. Hi! I can't understand and test a formula that contains unique references to data that I don't have. In addition, I do not have French Excel regional settings.
      I assume that the formula uses a reference to the named range Solde, which is on the first worksheet.

  13. I want to take an excel file with many columns and data and created an additional file referring to only a select number of columns from the original file.

  14. Hello!
    I have 2 sheets (01, 02).
    Sheet 02 contains a cell with a reference to a cell on sheet 01 (='01'!D3).
    When i copy (dragging) sheet 02 and make sheet 03, reference on sheet 03 stay the same (='01'!D3)!!!!
    I need the reference to change automatically to ='02'!D3.
    Is this possible?
    OpenOffice do it automatically, but i'm trying to switch to Excel.
    Thank you very much!

      1. Hello! Thnx for answer.
        I copy relative references! But i copy sheet. And it doesnt dynamically change. When i copy (dragging) sheet 02 and make sheet 03, reference on sheet 03 stay the same (='01'!D3)!!!!
        I need the reference to change automatically to ='02'!D3.

  15. Hi.
    I created a data extraction file that is using filter function to pull data from a master workbook. It has a "list" that can be filtered and generate a list referencing to the master sheet.
    I am trying to make the external reference work while the workbook is closed. The master and extraction files sit at the same path on sharepoint and I did put the exact path to the file: "https://location/location/location/file.xlsx". So the path would be 'https://location/location/file.xls!Table1

    When I have the master file open, the function it will only indicate the file name, but when I close the master, the entire file path shows. However, the filter and file exaction won't work. Any help would be appreciated so that the filtered file will work without the master file open.

    I am wondering if it's not working because it's on sharepoint.

    1. Here is what I have:

      =CHOOSECOLS(VSTACK('https://filelocation/filename.xlsx'!Table1[#Headers],SORT(FILTER('https://filelocation/filename.xlsx'!Table1[#Data], 'https://filelocation/filename.xlsx'!Table1[Filtercondition]=C2,""),{4,7,1},1)),6,8,7,4,1,2,18,19,20)

      1. I included the brackets [ ] for the file name. But still getting #REF error. For the cell reference, can it be a table for a closed book reference? Or does it have to specify the cells?

        =CHOOSECOLS(VSTACK('https://filelocation/[filename.xlsx]Sheet Name'!Table1[#Headers],SORT(FILTER(' https://filelocation/%5Bfilename.xlsx%5DSheet Name'!Table1[#Data], 'https://filelocation/[filename.xlsx]Sheet Name'!Table1 [Selected Column name]=C2,""),{4,7,1},1)),6,8,7,4,1,2,18,19,20)

        1. Hi Shanna,
          I would advise you to build the formula having both workbooks open and selecting the ranges and cells in the second workbook using the mouse. In this case, Excel will use the appropriate references automatically. After you close that second workbook, your external reference will get updated automatically to include the entire path to the file.

  16. I want to create an active spread sheet which will pick up values from different columns in the master sheet with a condition. Can anyone help.

    E.g. Column A in active spread sheet should pick up value from column G of master spread sheet with a condition that it picks up on the open tasks to the active sheet. How to do that.

  17. Hello! Thank you for this. Do you know of a way to have just the formula linked to other workbooks? For instance, if I have a cell that calculates the sum of a list on one spreadsheet, is there a way to copy the formula to other spreadsheets such that, when I update the formula on the first (like, maybe i decide to divide the result by 2), the formula on the others also updates (so they also now divide the results of their sums by 2)?

    1. Hi! An Excel formula cannot automatically change if another formula of the same type changes. You can write the values that change in separate cells and make references to these cells from all formulas.

      1. that wouldn't work for what i'm trying to do, but thanks for the quick response! :)

  18. Hi

    I'd like to create an Excel template with the ability to attach items from another Excel file. For example, a serial number is entered into the template and all the information for this item (price, description, image, etc.) is linked from another Excel file.

  19. Hi,

    I'm trying to set up a spreadsheet of information that has multiple sheets. Sheets 1 and 2 will always be the same. Sheet 3 will be a 'review' spreadsheet. There may be multiple 'review' sheets that I need to insert weekly (an indefinite number), and so want to create a template that I can copy and pull into place. Is there a way I can set a formula to autopull information through from a cell on the previous sheet to the left hand side when I don't know what that sheet will be called?

  20. Hello ! I have a workbook with two sheets. In the first sheet (DAY TIME) I get values from the second sheet (GET VALUES) using =INDEX(GET_DATA!$M$5:$M$1004;MATCH('DAY TIME'!$G$6;GET_DATA!$L$5:$L$1004;1))
    This extends from M to AU.
    I want to dynamically change the range of my search by just changing the values of two cells.
    Meaning for example in sheet1, in cell B2 I will input the number 305 and in the cell B3 the number 519 and the formula will lookup in M305:M519 and L305:L519 of sheet 2.

    I am trying with INDIRECT but I can't seem to find a work around.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)