How to Reference Text in Another Cell in Excel (14 Ways)

Get FREE Advanced Excel Exercises with Solutions!

When we are dealing with a large amount of data in Excel, sometimes we want to cite/reference certain texts from one cell to another for better understanding. In this article, we will show you how to reference text in another cell in Excel.


How to Reference Text in Another Cell in Excel: 14 Ways

In this section, you will learn 14 different effective ways to reference text in another cell with formulas, Excel tools and VBA code.

1. Reference Text from One Cell to Another Cell in the Same Worksheet in Excel

Consider the following example. What we are going to do with it is, we will take cell B7 and reference the text, Australia, from it in another cell D5.

The steps to do that are given below.

Steps:

  • In cell D5, write an equal sign (=).
  • Following that, write the cell reference number, B7, that you want to cite in cell D5.

The formula will be like this:

=B7

  • Then, press Enter.

Reference Text from One Cell to Another Cell in the Same Worksheet in Excel

Look at the following image.

Result of Reference Text from One Cell to Another Cell in the Same Worksheet in Excel

As a result, cell D5 is holding the text value, Australia from cell B7.

Read More: How to Display Text from Another Cell in Excel


2. Citing Text from a Cell to Another Cell in Different Worksheet in Excel

We have a list in the worksheet named Dataset (shown in the picture below).

What we want to do here is, we will refer to a specific text, Australia from cell B7, in cell C5 of the worksheet named Reference (shown in the picture below).

Steps:

  • In Cell C5 of the Reference worksheet, write an equal sign (=).

  • Following that, click on the Dataset sheet and select B7, the cell that you want to cite.

The formula will be like this:

=Dataset!B7
  • Then, press Enter.

Look at the result shown in the picture below.

Reference Text from One Cell to Another Cell in the Different Worksheet in Excel

The text value Australia from cell B7 in the Dataset sheet is now cited in cell C5 in the Reference sheet.

Read More: How to Use Variable Row Number as Cell Reference in Excel


3. Referencing and Concatenating Texts in Another Cell

You can also concatenate while referencing texts in another cell. The steps on how to do that are given below.

For this article, we will reference and concatenate Cells B7, B12 and B16 in Cell D5.

Steps:

  • In Cell D5, write an equal sign (=).
  • Following that, write the cell reference numbers with ampersand symbol (&) between them.

The formula will be like this,

=B7&” “&B12&” “&B16

The “ “ in between the ampersand symbol (&) is for spacing out among the referenced texts in cell D5. You can omit this if you don’t want to put space between texts.

  • Then, press Enter.

Excel reference and concatenate text in another cell

Look at the result shown in the picture below.

Result of excel reference and concatenate text in another cell

The text values Australia, Bangladesh and Ireland are referenced together through the corresponding Cells B7, B12 and B16 in cell D5.

Read More: How to Reference Cell by Row and Column Number in Excel


4. Reference a Range of Texts in Another Cell in Excel

Till now, we have been referencing text from a single cell. What if we want to reference a range of texts from different cells in other cells.

Let’s see how to do that in Excel.

Steps:

  • In Cell D5, write an equal sign (=).
  • Following that, select the first cell from the range that you want to reference text from and drag through the last cell of the range. For instance, we wanted to reference text value from Australia (in Cell B7) to Afganistan (in Cell B13) in cell D5, so we select cell B7 first and drag it till cell B13.

In cell D5, the formula looks like this:

=B7:B13
  • Then, press Enter.

Reference a Range of Text in Another Cell in Excel

To find out what happened, look at the image below.

Result of Reference a Range of Texts in Another Cell in Excel

Finally, we get a range of texts – Australia to Afganistan – in cell D5 through corresponding cells B7 to cell B13 by referencing.

Read More: How to Find and Replace Cell Reference in Excel Formula


5. Cite Text in Another Cell with Named Range in Excel

If you don’t want to reference a text by its corresponding cell reference number, then you can utilize the Named Range option of Excel.

In this section, we will show you how to cite a text through Named Range.

Steps:

  • First, select the cell that you want to cite. In our case, it is cell B7.
  • Second, right-click on the selected cell. A list of options will appear.
  • Third, select Define Name… from the options.

  • After that, a New Name pop-up box will appear. In the Name box, write any name that you like (we named our cell Aussie).
  • Later, click OK.

  • We have successfully named the cell B7 Aussie (shown in the picture below).

  • After that, in cell D5, write an equal sign (=), following the newly defined name (Aussie).

The formula in cell D5 will be like this:

=Aussie
  • Then, press Enter.

Reference Text in Another Cell in Excel with Named range

Now, look at the following image to check whether it really referenced the text value Australia from cell B7 in cell D5 or not.

Result of Reference Text in Another Cell in Excel with Named range

As we can see from the picture above, Named Range can successfully reference text from one cell to another.

Read More: How to Use Cell Value as Worksheet Name in Formula Reference in Excel


6. Reference Multiple Texts with Named Range in Another Cell

In the previous section, we learned how to reference a single cell by defining a name. And in this section, you will learn how to cite a range of cells with Named Range in Excel.

Steps:

  • First, select the range of cells that you want to cite. In our case, it is from cell B7 to B13.
  • Second, right-click on the selected cells. A list of options will appear.
  • Third, select Define Name… from the options.

  • After that, a New Name pop-up box will appear. In the Name box, write any name that you like (we named our range Countries).
  • Later, click OK.

  • We have successfully named the range B7:B13 Countries (shown in the picture below).

  • After that, in cell D5, write an equal sign (=), following the newly defined name (Countries).

The formula in cell D5 will be like this:

=Countries
  • Then, press Enter.

Result of Reference Range of Text in Another Cell in Excel with Named range

Now, look at the following image to check whether it really referenced the text value from Australia to Afganistan from cell B7 to B13 in cell D5 or not.

Result of Reference Range of Text in Another Cell in Excel with Named range

As we can see from the picture above, Named Range can successfully reference a range of texts from one range to another.

Read More: How to Use OFFSET for Cell Reference in Excel


7. Reference Text from a Cell to Another Cell in the Same Worksheet with the INDIRECT Function

The INDIRECT function in Excel is used to return the reference specified by a text string.

  • Syntax:
=INDIRECT(ref_text, [a1])

  • Return Value:

Returns the reference value specified by a text string.

  • Parameter Description:
Parameter Required/ Optional Description
ref_text Required A reference of a cell containing an A1-style reference, an R1C1-style reference, a name defined as a reference, a text string as a reference.

The INDIRECT function will return the #REF! Error, if:

  • ref_text does not hold a valid reference.
  • the source workbook is not open. If ref_text refers to a different external reference workbook, then the other workbook must be open.
  • ref_text indicates a range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD).
[a1] Optional A logical value that determines the type of reference stored in the ref_text range.
  • If TRUE or omitted, ref_text is generated as an A1-style reference.
  • If FALSE, ref_text is generated as an R1C1-style reference.

 

Following from here, you will learn how to utilize the INDIRECT function to reference a text from one cell to another.

In section 1, we have learned how to execute a formula to reference a text from one cell in another. But this time, we will learn how to do that with INDIRECT. We will take Cell B7 and reference the text, Australia, from it in another cell D8 with the INDIRECT function.

Steps:

  • In cell D8, write an equal sign (=), the function name, INDIRECT, and pass the cell reference number that you want to cite the text from inside double quotes (“”).

The formula becomes:

=INDIRECT(“B7”)
  • Then, press Enter.

Reference Text from One Cell to Another Cell in the Same Worksheet in Excel with Indirect

Now, look at the following image to check what happened.

Result of Reference Text from One Cell to Another Cell in the Same Worksheet in Excel with Indirect

The INDIRECT function can successfully reference the text Australia from cell B7 in cell D8.

Read More: How to Reference a Cell from a Different Worksheet in Excel


8. INDIRECT Function to Reference Text from a Cell to Another Cell in Different Worksheet

We have seen in section 2 how to reference a text from one sheet to another cell in another sheet. In this section, we will see how to do that with the INDIRECT function.

We have a list in the worksheet named Dataset (shown in section 2) What we want to do is, we will refer to a specific text, Australia from Cell B7, in Cell C5 of the worksheet named Reference (shown in section 2).

Let’s see how to do that with INDIRECT.

Steps:

  • In Cell C8 of the Reference worksheet, write an equal sign (=), the function name, INDIRECT and click on the Dataset
  • Then, select B7, the cell that you want to cite the text from. Don’t forget to write everything inside the parentheses in double quotes (“”).

The formula in Cell C8 of the Reference worksheet will look like this:

=INDIRECT(“Dataset!B7”)
  • Then, press Enter.

Reference Text from One Cell to Another Cell in the Different Worksheet in Excel with Indirect

As you can see from the picture above, the text value Australia in Cell B7 of the Dataset sheet is now cited in Cell C5 in the Reference sheet.

Read More: How to Reference Cell in Another Sheet Dynamically in Excel


9. Referencing and Concatenating Texts in Another Cell with the INDIRECT Function

Referencing and concatenating with the INDIRECT is a bit tricky. To understand this process, read this article carefully.

Look at the following example. In cell D8, do as you already know that after an equal sign (=) we have to write INDIRECT and pass the reference inside the parentheses inside double quotes (“”). Now look closely at how we passed the reference here.

  • First, we wrote “B” inside double-quotes (“”). It means we are passing column B.
  • Second, as we are concatenating here, so we put an ampersand symbol (&).
  • Third, we passed 7. It means we are passing row number 7.
  • Now, the whole procedure means is, we are concatenating column B and row 7 inside the INDIRECT
  • Cell B7 carries Australia. So, let’s see after pressing Enter, cell D8 really reference the text from B7 or not.

Excel reference and concatenate text in another cell with Indirect

  • So, press Enter and look at the image below for the result.

Result of Excel reference and concatenate text in another cell with Indirect

As a result, cell D8 is now carrying the referenced text from cell B7, Australia with the help of the INDIRECT function.


10. INDIRECT Function to Reference a Range of Texts in Another Cell in Excel

It is also possible to reference a range of texts from different cells in other cells with INDIRECT.

Let’s see how to do that in Excel.

Steps:

  • In cell F5, write an equal sign (=), the function name, INDIRECT and pass the range of cell reference numbers that you want to cite the text from inside double quotes (“”).
  • The above step means, inside the double quotes (“”) of the parentheses, select the first cell from the range that you want to reference the text from and drag through the last cell of the range. For instance, we wanted to reference text value from Australia (in cell B7) to Afganistan (in cell B13) in cell F5, so we select cell B7 first and drag it till cell B13.

In cell F5, the formula becomes:

=INDIRECT(“B7:B13”)
  • Then, press Enter.

Reference a Range of Text in Another Cell in Excel with Indirect

Consequently, you can see from the above image that we get a range of texts – Australia to Afganistan – in cell F5 to F11 through corresponding cells B7 to cell B13 by referencing with INDIRECT.


11. INDIRECT Function to Cite Text in Another Cell with Named Range in Excel

In above section 5, you have learned how to reference text by Named Range with formula. You can execute the same task with the INDIRECT function in Excel.

Steps:

  • As shown in section 5, select the cell (B7) that you want to cite the text from and right-click on it.
  • From the appeared option list, select Define Name…
  • Then, insert any name that you want the cell to have (we named the cell Aussie).
  • Later, click OK.
  • After that, in Cell D8, run an INDIRECT function with the defined name (Aussie) inside double quotes (“”) of the parentheses.

The formula will look like this:

=INDIRECT(“Aussie”)
  • Lastly, press Enter.

Reference Text in Another Cell in Excel with Named range with Indirect

The picture shown above tells us that the text value Australia from Cell B7 is successfully referenced in Cell D8. So now we know that Named Range can successfully reference text from one cell to another with the INDIRECT function.


12. Reference Multiple Texts with Named Range in Another Cell with the INDIRECT Function

In above section 6, you have learned how to reference multiple texts by Named Range with formula. You can execute the same task with the INDIRECT function in Excel.

Steps:

  • As shown in section 6, select the range of cells that you want to cite and right-click on it. In our case, it is from Cell B7 to B13.
  • From the appeared option list, select Define Name…
  • Then insert any name that you want the cells to have (we named the cell Countries).
  • Later, click OK.
  • After that, in cell D8, run an INDIRECT function with the defined name (Countries) inside double quotes (“”) of the parentheses.

The formula will look like this:

=INDIRECT(“Countries”)
  • Lastly, press Enter.

Reference Range of Text in Another Cell in Excel with Named range with Indirect

The picture shown above tells us that the text values from Australia to Afganistan from cell B7 to B13 are successfully referenced in cell F5:F11. So now we know that Named Range can successfully reference multiple texts from one range of cells to another with the INDIRECT function.


13. Reference Text from a Cell to Another Cell with Paste Special in Excel

This time we will learn how to utilize Excel’s Paste Special tool to reference text from one cell to another. The steps to do that are shown below.

Steps:

  • First, select the cell that you want to cite the text form (in our case, it is cell B7).
  • Then, right-click onto the cell. A list of options will appear.
  • From there, select Copy from the options or you can press Ctrl + C from your keyboard to copy a selected cell.
  • Cell B7 is copied now.

  • Now, go to another cell where you want to reference the text Australia from cell B7. We picked cell D5 to store that.
  • Right-click onto the cell (D5) and from the appeared list, select Paste Link (N).

Excel reference text in another cell with paste special

  • This will paste the reference value of the copied cell in the target cell. Look at the following image where cell D5 is holding the text value Australia of the cell reference B7 ($B$7 shown in the formula bar).

Now consider the following gif.

Every time you change the value in cell B7, the result cell D5 will automatically be updated according to the text reference.

Read More: Excel VBA Examples with Cell Reference by Row and Column Number


14. Embed VBA to Reference Text from a Cell to Another Cell in Excel

If you are an advanced Excel user, then this section is for you. Because you can implement VBA code to reference text in another cell. Implementing VBA is the most effective, quickest and safest method to run any operation in Excel.

Here, we will see how to auto-update the reference every time the source data is changed with VBA code in Excel. Meaning, if we change the text value of cell B7 from Australia to Canada, the result cell D5 will automatically be updated.

Let’s see how to do that with VBA macro.

Steps:

  • In the beginning, right-click on the sheet where you want to implement the referencing.
  • Then, select View Code from the appeared list. As shown below, we right-clicked on the sheet named VBA where our dataset is stored and selected View Code from the options.

  • Then, copy the following code and paste it into the auto-generated code window.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Worksheets("VBA")
        If .Range("B7").Value <> "" Then
            On Error Resume Next
            Range("B7").Copy (.Range("D5"))
        End If
    End With
End Sub

Your code is now ready to run.

VBA excel reference text in another cell

  • Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the gif below to see the result.

After the successful code execution, when we changed the text value in cell B7 from Australia to Canada, the result cell D5 is automatically be updated.

Read More: Excel VBA: Cell Reference in Another Sheet 


Download Workbook

You can download the free practice Excel workbook from here.


Conclusion

To conclude, this article showed you 14 effective methods on how to reference text in another cell in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


<< Go Back to Cell Reference in ExcelExcel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

2 Comments
  1. Hello,
    I have 2 things that i can’t seem to find how to do in excel.
    1- How to use custom sort in a sort function?
    – Ex: I have a new table with data that aggregates the values of some columns of the raw data table (different sheet), using formulas like, unique, filter, countif, etc.. As i understand it’s not possible to use the sorting methods of the filter button or Data, on the aggregation table because the cells there have formulas. Using the function sort i only have the options of ascending or descending. But one of the columns is Severity and i need to sort by High, then Medium, then Low. How would i do this?

    2- How to make the contents of a cell in a column, follow another cell in the same row but on a different column, dinamicaly?
    – Ex: In column A i have a list of unique names as the result of index(match(contif)) where the range is in another sheet, in the raw data table. Now in column B i want to write some observations for each name (row) in column A. The raw data sheet will change every time i collect the data. Which means, it will change the order of the names in column A. How to i make sure that the observation that i wrote the first time, remains in the same row as the name?
    I appreciate your help.

  2. Thanks, RICARDO SERRÃO for your two amazing queries. Let me help you out in solving your problems.
    First, we are going to discuss your first question. The problem you faced is about custom sorting.
    At the very beginning, we arranged a dataset of change in percentage.
    Dataset
    Then, as you wished we have used the COUNTIF function in this dataset.
    Formula Use
    As a result, we have found the values.
    Result
    Now, select Dataset> go to Data>Sort options.
    Sort Option
    After that, in the Sort window, select Change% in Sort By option, Cell Values in Sort On option, and Custom List in the Order option. Press OK to execute it.
    Sorting Window
    Furthermore, click on the NEW LIST option and write the condition manually in the List Entries area and press Add option.
    New List Add
    Because of that, you will get the desired condition in the Custom Lists section and press OK.
    New option in Custom
    Afterward, if you can see the desired condition in the Order box then press OK.
    Order
    Lastly, you will get the result accordingly. As in the condition, you have entered less than zero at first then zero to ten and at last greater than zero that’s why in the Excel section you will get the same order accordingly.
    Result
    So, that’s the solution to your first query. Now, Let’s go through your second problem.
    First, by reading your example, at first by using a formula, we created a Uniques Names list.
    Dataset
    Second, you have to write down the same unique names list in another column. The behind this is, in the array, you can’t use sorting.
    Write down unique names
    Third, then enter the Observations you made next to the new column.
    Observations
    Forth, as we want to sort, so we mark each name with a unique Id number in a new column.
    Id column
    Fifth, go to select the table>Data>Sort options.
    Sort Option
    Sixth, select the Id option in the Sort by option, and Cell Values in the Sort On option, click on the Largest to Smallest option in the Order section, and press OK.
    Sort Window
    Finally, you will get the desired result.
    Result
    So, this is the solution to your second query.
    Therefore, our journey comes to an end. The problems were very fun to solve and I really feel amazed by helping you. Thank you once again. Best of luck.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo