[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Excel formulae allow users to perform basic calculations such as addition, subtraction, or multiplication, as well as find out the average or percentage of a cell range. Not only is it possible to apply these formulae to numeric values, but they also function with other data types, such as text, date, or time.<\/p>\n
The Excel INDIRECT function allows you to lock a reference to a single cell, a cell range, or a cell from another sheet. This means that the references won\u2019t change even if you edit the rows or columns in your spreadsheet – either by adding new ones or deleting existing ones. This is because the INDIRECT function converts a text string into a valid reference. You avoid returning values from a broken formula or simply #REF! errors<\/a>.<\/p>\n In this article, you\u2019ll learn what the INDIRECT function in Excel is, the INDIRECT formula syntax, and how to lock a cell reference to effectively manage formulae and level up your spreadsheet skills.<\/p>\n [\/et_pb_text][et_pb_text module_id=”what-is-the-excel-indirect-function” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The Excel INDIRECT function works like any other formula in your spreadsheet. Unlike the majority of formulae, this function is aimed at converting a text string into a valid reference. Moreover, the "&" operator can be included in the formula to create text strings. Let\u2019s take a look at the INDIRECT syntax to understand how it works.<\/p>\n Below is the syntax for the INDIRECT formula and the meaning behind each parameter.<\/p>\n Now that you know what the Excel INDIRECT function is and its formula syntax, let\u2019s see how it can be applied to various examples.<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-use-the-indirect-function-in-exce” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n We can use the Excel INDIRECT function to lock a specified cell in a formula; this allows you to change the cell reference within a formula without affecting it. Whether you add or delete rows or columns, it does not affect indirect references.<\/p>\n Let\u2019s start by using the INDIRECT formula for simple conversion from text to a valid cell reference.<\/p>\n 1.<\/strong> Open your spreadsheet and enter a numeric value in any cell. Here, I will type in \u201c56\u201d in cell A1.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-function-numeric-value-a1.webp” alt=”Indirect function Excel \u2014 the value 56 typed in cell A1″ title_text=”The value 56 typed in cell A1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Enter numeric value<\/p>\n 2.<\/strong> Enter the INDIRECT formula in any cell you wish. Remember that, as soon as you start typing, Excel will prompt you with the full formula. Here, I will enter the formula in cell B1 and refer to cell A1 to pull the numeric value: =INDIRECT("A1").<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-function-formula-cell-b1.webp” alt=”Indirect function Excel \u2014 INDIRECT formula pointing to A1 typed in cell B1″ title_text=”INDIRECT formula pointing to A1 typed in cell B1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Enter INDIRECT Formula<\/p>\n 3.<\/strong> Your spreadsheet should now show the same values in both cells.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-function-returns-cell-value.webp” alt=”Indirect function Excel \u2014 cells A1 and B1 both showing 56″ title_text=”Cells A1 and B1 both showing 56″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Returns cell values<\/p>\n As you can see, the INDIRECT function converts a text string (here, \u201cA1\u201d) into a valid cell reference to return the value of the referenced cell. Let\u2019s explore further basic examples using the INDIRECT function in Excel for an array of numbers using the "&" operator.<\/p>\n The "&" is the CONCATENATE operator in Excel<\/a>. It\u2019s helpful when you want to join the results of two text strings. It\u2019s normally used in conjunction with other functions such as SUM, but you can also use it for a single cell reference, as shown below.<\/p>\n 1.<\/strong> Go to your spreadsheet and type in the INDIRECT formula as before, and include the "&" operator between the column and the row reference. Here, we have used \u201c=INDIRECT("E"&3)" to return the value from cell E3, which is "5".<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-function-ampersand-operator.webp” alt=”Indirect function Excel \u2014 INDIRECT with the ampersand operator returning 5 from cell E3″ title_text=”INDIRECT with the ampersand operator returning 5 from cell E3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Use & operator<\/p>\n 2.<\/strong> Try to add or delete a column to check how the formula behaves. Here, we will insert a column between D and E.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-function-insert-column.webp” alt=”Indirect function Excel \u2014 right-click menu on column E with Insert highlighted” title_text=”Right-click menu on column E with Insert highlighted” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Add or delete column<\/p>\n 3.<\/strong> As you can see, instead of returning an error message, Excel still returns a valid reference. Here, the valid reference is \u201c0\u201d, as the cells are empty.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-function-empty-cell-reference.webp” alt=”Indirect function Excel \u2014 A1 shows 0 after the new column shifts the data to column F” title_text=”A1 shows 0 after the new column shifts the data to column F” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Valid reference to empty cell<\/p>\n Let\u2019s see what happens if I combine the ROW function with the INDIRECT function in Excel.<\/p>\n Using the "&" operator, you can combine the ROW and INDIRECT functions to return a reference based on the row where you enter the formula. In this example, I want to return the cell reference from the row where I add my formula – Row 1.<\/p>\n 1.<\/strong> Type =INDIRECT("ref_text"&ROW()) in any of the available cells, only specifying the ref_text parameter.<\/p>\n 2.<\/strong> Here, we will reference column D and enter the formula in row 1.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-row-function.webp” alt=”Excel INDIRECT with ROW \u2014 formula in A1 returning 4 from cell D1″ title_text=”Formula in A1 returning 4 from cell D1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – ROW function<\/p>\n As you have seen, the formula now returns the value based on the formula\u2019s row number; here, the numeric value is \u201c4\u201d, corresponding to cell D1.<\/p>\n The SUM function is used to add numeric values together. When combined with the INDIRECT function, it will return the sum of referenced cells.<\/p>\n 1.<\/strong> Type in the formula =SUM(INDIRECT("ref_text")) in the cell you wish to return your SUM. While the INDIRECT function returns the referenced cells, the SUM function will be in charge of returning the sum.<\/p>\n 2.<\/strong> Here, we will use =SUM(INDIRECT("D1:D5")) to return the sum of the cell range, including all numeric values in column D.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-sum-function.webp” alt=”Excel INDIRECT with SUM \u2014 sum of D1:D5 returning 39 in cell A1″ title_text=”Sum of D1:D5 returning 39 in cell A1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – SUM function<\/p>\n As you can see, the formula returns the sum of all numbers by referring to the cell range. Here, the valid reference is \u201c39\u201d, and the text value used to refer is \u201cD1:D5\u201d.<\/p>\n If I delete row 4 completely, the INDIRECT function acknowledges this change in the cell range reference and will return a new total.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-sum-after-deleting-row.webp” alt=”Excel INDIRECT with SUM \u2014 total drops to 34 after row 4 is deleted” title_text=”Total drops to 34 after row 4 is deleted” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – New value for SUM function<\/p>\n Similarly, when we combine the AVERAGE and INDIRECT functions, it first refers to the cell range and then performs the calculation, in this case, the average.<\/p>\n 1.<\/strong> Type in the formula =AVERAGE(INDIRECT("ref_text")) in any \u200ccell.<\/p>\n 2.<\/strong> Here, we will use =AVERAGE(INDIRECT("D1:D5")) to return the average of the cell range used in the previous example.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-average-function.webp” alt=”Excel INDIRECT with AVERAGE \u2014 average of D1:D5 returning 7.8 in cell A1″ title_text=”Average of D1:D5 returning 7.8 in cell A1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – AVERAGE function<\/p>\n If I add a new row, the values within the data range change. The INDIRECT function will recognize this, and so the data range will no longer include the value \u201c15\u201d. Instead, it returns the average of the first 5 rows (as specified in the formula). Here, it returns a new value of \u201c5.4\u201d.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-average-after-inserting-row.webp” alt=”Excel INDIRECT with AVERAGE \u2014 result changes to 5.4 after a new row is inserted” title_text=”Result changes to 5.4 after a new row is inserted” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – New value for AVERAGE function<\/p>\n The usefulness of Excel\u2019s INDIRECT function is not just limited to locking a cell reference for the correct functioning of formulae. We will now see how it can be used to refer to a cell in another sheet.<\/p>\n Let\u2019s see how the INDIRECT function can return cell values from another sheet.<\/p>\n 1.<\/strong> Open a new sheet where you will use the formula. To refer to a cell in another sheet, we will include the sheet name and the cell in the \u201cref_text\u201d parameter.<\/p>\n 2.<\/strong> It\u2019s important to include it between speech marks and add an exclamation mark after the sheet name. For example, =INDIRECT("JANUARY!A1").<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/excel-indirect-reference-another-sheet.webp” alt=”Excel INDIRECT function \u2014 reference to A1 on the JANUARY sheet returning 4″ title_text=”Reference to A1 on the JANUARY sheet returning 4″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Excel INDIRECT Function – Reference another sheet cell<\/p>\n As you can see, it has returned the same value \u200cin the referenced cell from another sheet. The formula will behave in the same way regardless of the number of sheets or information held in your spreadsheets.<\/p>\n [\/et_pb_text][et_pb_text module_id=”conclusion” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, the Excel INDIRECT function is essential for users who want to perform more complex tasks on their spreadsheets. By locking references to cells, you can return valid results from different formulae, regardless of whether you change the rows or columns within your spreadsheet. Overall, you have more control over your dynamic data whilst avoiding the time-consuming alternative of copying and pasting values across your spreadsheet.<\/p>\n This guide has shown you what the INDIRECT function is and its syntax. By the end of this article, you should know how to use the INDIRECT function to lock a reference to a cell, how to lock a reference to a cell range combined with another formula, and how to reference a cell from another sheet.<\/p>\n If you would like to know more about referencing, read our article on how to reference another sheet or workbook in Excel<\/a>. If you prefer using Google Sheets, then you\u2019ll find linking Google Sheets: How to reference another sheet<\/a> of great help.<\/p>\n Google Sheets has the same function. See how to use the INDIRECT function in Google Sheets<\/a> for the syntax there. To build a reference that moves a set number of rows and columns from a starting cell, use the Excel OFFSET function<\/a>. If the data you reference lives in separate Excel workbooks, Sheetgo<\/a>, an AI-powered data automation platform, can bring it into one file on a schedule, so it stays current and ready for AI.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" Use a fun\u00e7\u00e3o INDIRECT do Excel para transformar texto em uma refer\u00eancia de c\u00e9lula. Veja a sintaxe e exemplos com SUM, AVERAGE, ROW e outras planilhas.<\/p>","protected":false},"author":2,"featured_media":265601,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[],"class_list":["post-265588","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/265588","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=265588"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/265588\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/265601"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=265588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=265588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=265588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}What is the Excel INDIRECT function?<\/h2>\n
INDIRECT formula syntax<\/h3>\n
\n
How to use the INDIRECT function in Excel?<\/h2>\n
How to lock a cell reference using the INDIRECT function in Excel?<\/h3>\n
How to use the "&" operator with the INDIRECT function in Excel?<\/h3>\n
How to use the ROW function with the INDIRECT function in Excel?<\/h3>\n
How to use the SUM function with the INDIRECT function in Excel?<\/h3>\n
How to use the AVERAGE function with the INDIRECT function in Excel?<\/h3>\n
How to reference another sheet using the INDIRECT function in Excel?<\/h3>\n
Conclusion<\/h2>\n