--- title: "INDIRECT function in Excel: syntax and examples | PracticeExcel" description: "How to use INDIRECT in Excel and Google Sheets: build a cell reference from text, pick a sheet by name and fix #REF! errors." canonical_url: "https://practiceexcelonline.com/functions/indirect" locale: en meta: "og:description": "How to use INDIRECT in Excel and Google Sheets: build a cell reference from text, pick a sheet by name and fix #REF! errors." "og:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~SU5ESVJFQ1QgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBJTkRJUkVDVCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogYnVpbGQgYSBjZWxsIHJlZmVyZW5jZSBmcm9tIHRleHQsIHBpY2sgYSBzaGVldCBieSBuYW1lIGFuZCBmaXggI1JFRiEgZXJyb3JzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~SU5ESVJFQ1QocmVmX3RleHQsIFthMV0p%2Cp_Ii9mdW5jdGlvbnMvaW5kaXJlY3Qi%2Cs_lu7ZhXkGRWUi0Mcw.png" "og:image:height": "600" "og:image:type": image/png "og:image:width": "1200" "og:title": "INDIRECT function in Excel: syntax and examples | PracticeExcel" "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~SU5ESVJFQ1QgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBJTkRJUkVDVCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogYnVpbGQgYSBjZWxsIHJlZmVyZW5jZSBmcm9tIHRleHQsIHBpY2sgYSBzaGVldCBieSBuYW1lIGFuZCBmaXggI1JFRiEgZXJyb3JzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~SU5ESVJFQ1QocmVmX3RleHQsIFthMV0p%2Cp_Ii9mdW5jdGlvbnMvaW5kaXJlY3Qi%2Cs_lu7ZhXkGRWUi0Mcw.png" --- > Index of every page on this site: [llms.txt](https://practiceexcelonline.com/llms.txt) # **The INDIRECT function** Turns text into a cell reference, such as "B3" or "Sheet2!A1", and returns what is in that cell. Use it to build a reference from other cells. = INDIRECT( ## **Syntax** ``` INDIRECT(ref_text, [a1]) ```
**ref_text **
A reference written as text, such as "B3", or built with &, such as "B"&E1.
**a1 **(optional)
TRUE or left out for A1-style text, FALSE for R1C1 style, such as "R3C2".
## **Examples** D1=INDIRECT("B3") | | A | B | C | D | | --- | --- | --- | --- | --- | | 1 | Month | Sales | | **95** | | 2 | Jan | 120 | | | | 3 | Feb | 95 | | | | 4 | Mar | 140 | | | G1=INDIRECT("B"&E1) | | A | B | C | D | E | F | G | | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | Month | Sales | | Row | 4 | | **140** | | 2 | Jan | 120 | | | | | | | 3 | Feb | 95 | | | | | | | 4 | Mar | 140 | | | | | | G1=SUM(INDIRECT("B2:B"&E1)) | | A | B | C | D | E | F | G | | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | Month | Sales | | Row | 4 | | **355** | | 2 | Jan | 120 | | | | | | | 3 | Feb | 95 | | | | | | | 4 | Mar | 140 | | | | | | ## **Read the guide** - [**How to create a drop-down list in Excel, and make it update itself**](https://practiceexcelonline.com/learn/drop-down-list-excel) ## **Questions**
### Why does INDIRECT return The text is not a valid reference. Check for typos, put sheet names with spaces in single quotes, as in INDIRECT("'Sales 2024'!B2"), and note that a reference to another workbook only works while that workbook is open.
### Does INDIRECT update when I insert rows? No. The text "B3" always means B3, so the reference stays put when rows move. That can be the point, but it also makes INDIRECT easy to break. It recalculates on every change, so use INDEX where you can.
## **Related functions** - [**OFFSET**](https://practiceexcelonline.com/functions/offset) - [**INDEX**](https://practiceexcelonline.com/functions/index) - [**ROW**](https://practiceexcelonline.com/functions/row) ```json {"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/functions/indirect#webpage","description":"How to use INDIRECT in Excel and Google Sheets: build a cell reference from text, pick a sheet by name and fix #REF! errors.","name":"INDIRECT function in Excel: syntax and examples","url":"https://practiceexcelonline.com/functions/indirect","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/functions/indirect#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/functions/indirect#/schema/question/1"},{"@id":"https://practiceexcelonline.com/functions/indirect#/schema/question/2"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/functions/indirect"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/functions/indirect#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://practiceexcelonline.com/functions","position":2},{"@type":"ListItem","name":"INDIRECT function in Excel: syntax and examples","position":3}]},{"@id":"https://practiceexcelonline.com/functions/indirect#article","description":"How to use INDIRECT in Excel and Google Sheets: build a cell reference from text, pick a sheet by name and fix #REF! errors.","headline":"INDIRECT function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~SU5ESVJFQ1QgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBJTkRJUkVDVCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogYnVpbGQgYSBjZWxsIHJlZmVyZW5jZSBmcm9tIHRleHQsIHBpY2sgYSBzaGVldCBieSBuYW1lIGFuZCBmaXggI1JFRiEgZXJyb3JzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~SU5ESVJFQ1QocmVmX3RleHQsIFthMV0p%2Cp_Ii9mdW5jdGlvbnMvaW5kaXJlY3Qi%2Cs_lu7ZhXkGRWUi0Mcw.png","@type":["Article","TechArticle"],"author":{"@id":"https://practiceexcelonline.com/#identity"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/functions/indirect#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/functions/indirect#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.com/functions/indirect#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why does INDIRECT return","acceptedAnswer":{"@type":"Answer","text":"The text is not a valid reference. Check for typos, put sheet names with spaces in single quotes, as in INDIRECT(\"'Sales 2024'!B2\"), and note that a reference to another workbook only works while that workbook is open."}},{"@id":"https://practiceexcelonline.com/functions/indirect#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"Does INDIRECT update when I insert rows?","acceptedAnswer":{"@type":"Answer","text":"No. The text \"B3\" always means B3, so the reference stays put when rows move. That can be the point, but it also makes INDIRECT easy to break. It recalculates on every change, so use INDEX where you can."}},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~SU5ESVJFQ1QgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBJTkRJUkVDVCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogYnVpbGQgYSBjZWxsIHJlZmVyZW5jZSBmcm9tIHRleHQsIHBpY2sgYSBzaGVldCBieSBuYW1lIGFuZCBmaXggI1JFRiEgZXJyb3JzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~SU5ESVJFQ1QocmVmX3RleHQsIFthMV0p%2Cp_Ii9mdW5jdGlvbnMvaW5kaXJlY3Qi%2Cs_lu7ZhXkGRWUi0Mcw.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~SU5ESVJFQ1QgZnVuY3Rpb24gaW4gRXhjZWw6IHN5bnRheCBhbmQgZXhhbXBsZXM%2Cdescription_~SG93IHRvIHVzZSBJTkRJUkVDVCBpbiBFeGNlbCBhbmQgR29vZ2xlIFNoZWV0czogYnVpbGQgYSBjZWxsIHJlZmVyZW5jZSBmcm9tIHRleHQsIHBpY2sgYSBzaGVldCBieSBuYW1lIGFuZCBmaXggI1JFRiEgZXJyb3JzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~SU5ESVJFQ1QocmVmX3RleHQsIFthMV0p%2Cp_Ii9mdW5jdGlvbnMvaW5kaXJlY3Qi%2Cs_lu7ZhXkGRWUi0Mcw.png"}]} ``` ## Sitemap See the full [sitemap](https://practiceexcelonline.com/sitemap.md) for all pages.