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. =VALUE(HYPERLINK(CONCATENATE("[H:\Quality Hub\KW ";G1;"\";"DECONT ";D1;" ACT202011700024760- 2023.xlsm]Materiale Stoc'!R2")))
    what is not good

      • Put the CONCATENATE formula in another cell and see what the result is. That will tell if the result is correct for HYPERLINK function to use as input.

  2. thanks for your support

  3. nice

  4. Very good

  5. I'm wrote macro, where I'm using Xlookup to link data from other Workbook on same Sharepoint location.
    It works fine, until I close both files (one from which link is made and one to which link is made). After re opening them, formula sometimes brakes (I can't figure out exactly why/when), weird symptom is that it gets worksheet refference changed or even "REF!".

    Bellow is my example row, Phrase Process Checking is exact name of tab, which is geting lost to REF! (tab still there), or change to other tab existing in the workbook

    ShtTM.Range("K2:K" & lastRowTM - 1).Formula = "=Xlookup(A2&H2&G2," & PathOnly2 & "'[" & FileName2 & "]Process Checking'!A$3:A$" & LastRowPCI & "&" & PathOnly2 & "'[" & FileName2 & "]Process Checking'!g$3:g$" & LastRowPCI & "&" & PathOnly2 & "'[" & FileName2 & "]Process Checking'!Q$3:Q$" & LastRowPCI & "," & PathOnly2 & "'[" & FileName2 & "]Process Checking'!P$3:P$" & LastRowPCI & ",0)"

    I

  6. Hi, thanks for your excellent guide and for sharing your knowledge.

    I'm trying to figure out how to create an absolute reference to a cell in a table on a separate sheet.
    As soon I try to copy the formula to the next right cell, it breaks, and the reference point to Roles[CostRateOrig] (the next right cell in the separate sheet) instead of Roles[ActivitiesRoles] (which it always should). Is it possible to make the reference absolute? The $ sign does not seem to work in this instance...

    Original formula:
    =(XLOOKUP(B26;$B$8:$B$16;E$8:E$16))*(XLOOKUP($B26;Roles[ActivitiesRoles];XLOOKUP(E$24;Roles[[#Headers];[BillRateOrig]:[BillRateRev2]];BillRateArray)))*(IF($D26="Yes";1;0))

    Broken formula:
    =(XLOOKUP($B26;$B$8:$B$16;F$8:F$16))*(XLOOKUP($B26;Roles[CostRateOrig];XLOOKUP(F$24;Roles[[#Headers];[BillRateOrig]:[BillRateRev2]];BillRateArray)))*(IF($D26="Yes";1;0))

    • I solved it by setting up a "defined name" in the Name Manager and pinpointed the data as an array. Then it treated the data as absolute :-).

  7. Hi there - thanks for this. What I would love to do is use the value of one cell (using left function to pull the characters) to reference the sheet name.

    E.g., if A1 = DIB (2960452), and the sheet name is DIB, I want to have B1 =
    SUMPRODUCT(LEFT(A1, LEN(A1)-10)!$F$5:$F$35, (LEFT(A1, LEN(A1)-10)!$L$5:$L$35)/SUM((LEFT(A1, LEN(A1)-10)!$L$5:$L$35),

    Where (LEFT(A1, LEN(A1)-10)=DIB.

    This is so I can repeat the same across multiple sheets and rows, as the names change a lot and new tabs are being added all the time.

    Thanks!

  8. I have an error with this code,

    Can anyone help me why this #NAME? error is appearing.

    =IFEROOR(VLOOKUP($E3,'Follow Up'!A1:P1,MATCH('Daily Report'!F$2,'Follow Up'!A1:P1,0)),0)

    Follow Up and Daily Report are names of sheets.

  9. Hi, I'm trying to create an automated scheduling spreadsheet for equipment and personnel allocation. Currently I export data from SAP with our weekly schedule and paste it into Sheet1 of the workbook, that includes workorder number, description of works, location, date hours etc from columns A-I.

    In J-Q I then have variable drop down selections from a master list of equipment and personnel on Sheet2.

    On Sheet 3 I have have the master list of equipment in column B, with Monday-Friday dates in D-H. I want to make it so that when a piece of equipment is selected in the drop down box of a job for a day on Sheet1, that the job/workorder number on the row of that day, then appears on the same date on Sheet3 to show it is allocated, as Sheet3 has to be shared to others. Is this possible?

  10. Hi
    Let's say one workbook contains multiple worksheets and I have made a summary sheet which takes inputs from all the sheets in the workbook. Now, I have multiple workbooks for different companies. Format of each worksheet in all the workbooks are identical.
    How do I develop similar 'summary sheet' for all the workbooks, without doing it manually sheet by sheet?

  11. Hi, I have a workbook with 30 sheet named 01, 02, 03, .... , 30
    In another workbook, I type this formula: =sum('[Workbook name]Sheet name'!Cell range)
    example: =sum('[Workbook name]01'!$E$37)

    I need to call every E37 value in sheet 01 to 30. How can I do it without typing the formula one by one every cell?
    Thanks

  12. Nice

  13. I can't find the formula in which the (Registration Fee column) in the cell G20 (Sheet Apr-22) Sum of Value 1,92,420/- Amt automatically show in Cell C4 (Sheet SD).
    And same with Stamp Duty, SIC, PIBD Column.
    Means I want cell G20 (Sheet Apr-22) Value in Cell C4 (Sheet SD) with formula.

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  14. When referencing another sheet using the =SheetName!CellLocation Method, is there a way to copy the formatting as well. I have a list of numbers with a select few of them in bold to reference their significance. I want to keep this formatting in my new sheet.

  15. Hi All,

    I am referencing a date from sheet 1 onto sheet 2. When I filter sheet 1 the reference on sheet 2 has changed to the data that has been replaced into that cell.

    Is there a way that I can get the cell on sheet 2 to follow the date I had referenced on sheet 1 even as it is filtered back and forth?

    • Hello!
      I think it's not about filtering, but about sorting. When sorting data, Excel does not move cells, but only copies the data. I recommend copying all the values ​​to a new sheet using Paste Special - Values ​​and sorting them there. This will not break your formulas.

  16. I have a worksheet that I inherited. The main "sheet" references another sheet with the same exact data. It matches the columns that could be used in the main sheet. What situation would benefit pulling data from a different sheet?

  17. I have a line of code that is referencing file and sheet, but I need to specify the range in R1C1 format so I can use it in a loop. However no matter how I format it, Excel doesn't like it. I'm extracting data elements from a machine controller into excel for printing and archiving. The machine controller has an array of 100 elements, each 100 data entries long. This is what I have now (that doesn't work):

    data = DDERequest(rsichan1, "Recipe_Library_STF[" + CStr(Index) + ",0],l120,C120")
    Range("[Tilt_recipe.csv]Tilt_recipe!" + Cell(Index + 1, 1), Cell(Index + 1, 120)).Value = data

    Somehow, when adding the reference for the file and worksheet doesn't allow me to specify the cell addresses in R1C1 (or at least I don't know how to specify it).

    This works....
    data = DDERequest(coil, req)
    Range(Cells(4, Index + 3), Cells(104, Index + 3)).Value = data

    Any thoughts on how to format this properly?

    • Oh, I forgot to add, Index is a looping variable used to index through all 100 entries in the array. I inhereted this from another person who, rather than looping the lines of code that pull the data, there are 100 sections, each pulling one set of the data. So... whenever we need to make a modification, we need to modify over 1500 lines of code to get the new dataset. I'd rather tweak the 15 or so lines in the loop.

  18. I want to reference an entire workbook from an external excel file on my onedrive so that I can have one copy that is editable by several people and then another copy that is populated by the first, that a much larger group can view, but not edit. Is there a way to do this with out manually referencing every cell? When I try to drag the formula to other cells it does not change the cell references, so every cell has the same data from the A1 cell in the referenced workbook. How can I get this to copy to the rest of the cells but change the cell reference accordingly?

  19. Hi,
    I have an issue I can't find the fix for:

    My sheet1 is a summary sheet and sheet2 and beyond have data and are all formatted exactly the same. I want to use =AVERAGE on sheet1 for a range in sheet2 but am writing a dynamic formula so I can change the range I want on the sheet1 and Excel will know to find/look up the range I want on sheet2. The following formula I can get to work, but only takes an average of 2 non-adjacent cells (not a range):

    =AVERAGE((VLOOKUP($B$1,(INDIRECT($A7&"!"&"$A$4:$BB$60")),(INDIRECT($A7&"!"&"$AD$2")),FALSE)),VLOOKUP($B$2,(INDIRECT($A7&"!"&"$A$4:$BB$60")),(INDIRECT($A7&"!"&"$AD$2")),FALSE))

    where A7 is "sheet2", $B$1 is "2010" and $B$2 is "2020". This formula averages 2010 and 2020 only, not 2010 THRU 2020 like I'm trying to achieve...

    If I put a semicolon in where the comma is separating the 2 formulas, I get an error message. Basically I want to know how to write this: =AVERAGE(VlookupFormula1:VlookupFormula2)

    Any suggestions?
    Thanks!

      • I think I figured out a formula that works using the ADDRESS/MATCH/INDIRECT functions.

        Original on sheet1:

        AVERAGE(sheet2!AD6:AD16)

        New dynamic formula that worked and got the same result:

        AVERAGE(INDIRECT($A7&"!"&ADDRESS(MATCH($B$1,INDIRECT($A$7&"!"&"$A$1:$A$200"),0),30)&":"&ADDRESS(MATCH($B$2,INDIRECT($A$7&"!"&"$A$1:$A$200"),0),30)

        where $A7 = "sheet2"
        where $B$1 = 2010
        where $B$2 = 2020

  20. Hi Alexander/Ablebits Team,

    I've searched everywhere for a clear answer to this but I can't find any and it seems like such a simple problem:

    Let's say I have 6 sheets. The first is a summary page, the other 5 are named "Monday", "Tuesday" etc thru "Friday". I've set up these 5 day-of-the-week sheets with the same exact tables, only with different values in the cells from day to day.

    In A2 on the summary page I want to write a long, complicated formula where Monday occurs several times in calculating Monday's results. (i.e. =((Monday!A1*2)+(Monday!A2*3)/LN(2))-(Monday!A4/Monday!A7) etc etc...)

    To see Tuesday's results, I want Excel to change the data in that summary sheet's formula automatically so I don't have to delete Monday and type Tuesday 10 times. I'd rather type the word Tuesday into A1 and have the complicated formula reference A1 and know to change the text of Monday into Tuesday in all the instances in the formula. Does that make sense?

    It seems excel should have this function since it would save a lot of typing, especially if I have 100 different sheets..

    Thanks!
    Charles

      • Alexander,
        I got that to work, thank you very much! Btw my version of Excel didn't need the "'" around the sheet name. Less is more!
        Charles

        • Hi Charles,

          Single quotes are not needed for single-word worksheet names, but for multiple-word sheet names they are required.

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