Very often we reference the data of a Cell in another Cell in Excel. We can use equals to sign (=) then enter the cell address to display Cell contents in another Cell. Let us see how to display cell contents in another cell in Excel in this topic.
Referencing the Cell Contents in Another Cell
Double click on any Cell in Excel Sheet to make the Cell editable. Then enter the equals to sign (=) and enter the address of Cell which you wants to refer. You can refer a single Cell or a Range using this approach. Here are the examples on referencing the content of a Cell and displaying in another cell in Excel. Let us see the detailed examples on referencing the cells in the following scenarios.
- Referencing a specific Cell
- Relative Reference
- Absolute Reference
- Referencing a Range (multiple cells)
- Referencing and Concatenating Cells
- Referencing Cells from another Sheet
- Referencing a Named Range
- Showing Cell values on a Shape
- Showing Cell value in Chart Title
It is very important to know the types of cell references we can use while referencing the cells in Excel. There are two types of references in Excel, Relative and Absolute References.
Referencing a specific Cell
We can enter ‘=’ sign and specify the cell address in any cell in the Excel Sheet. For example, you can enter ‘=A1’ at Range B1 to refer the Cell content of A1 in B1, you can enter ‘=AB25’ at C5 to refer the content of range AB25 in Range C5.
Relative Reference in Excel
Relative reference is used to change the Cell Column or Row relatively while copying the cells with formula to another range. Row number and Column Names will changes relatively when we use relative cell reference.
For example, when you have =A1 reference in Cell B1, it will change to A2 while copying down to the next row (i.e; A2), it will chnage to B1 when copying to the next Column (i.e; C1). The change in number of columns and rows depending to the relative number of columns and rows which you are pasting the cells.
For example, if you copy the same ‘=A1’ reference in Cell B1, it will change to A5 when you paste at cell B1, it will become D5 when you paste in E5.
Display Text From Another cell in Excel
We can display the text of another cell using Excel Formula. We can use ‘=’ assignment operator to pull the text from another cell in Excel.
For example, the following formula will get the text from Cell D5 and display in Cell B2.
You can enter =D5 in Range B2. You can use ‘$’ symbol to make the cell reference as absolute (=$D$5).
Display Value From Another cell in Excel
We can display the Value of another cell using Excel Formula. We can use ‘=’ assignment operator to pull the value of another cell in Excel.
For example, the following formula will get the value from Cell C6 and display in Cell A3.
You can enter =C6 in Range A3. You can use ‘$’ symbol to make the cell reference as absolute (=$C$6).
Referencing a Range (multiple cells)
We can refer multiple Cells in Excel using Excel Formula. We can get the cell contents and display in a cell or refer the range to perform calculations.
Getting Values From Multiple Cells in Excel
Often we refer the multiple cells in put into once Cell. We can refer the multiple Cells and Ranges in Excel to combine the text or to perform the calculations.
Getting Text from Multiple Cells
The following formula will refer the text from multiple cells and combine them to display in one Cell. This will get the contents form Cell E2 and F2 and display the combined text in another Cell.
Combine Text of Multiple Cells:
We can use & operator to concatenate the text from multiple cells. We can provide a space or – to separate the text to form multiple words. The following Example shows you how to get the data from multiple cells and display the combined text in one cell.
Getting Values from Multiple Cells:
Sometimes, we need to pull the values of another cell and perform some calculations in a cell. For example, We have Cost and Quantity values in two different cells (B2,C2) and we can find the total cost in another Cell (A2).
Combining Values of Multiple Cells:
We can also combine the numerical data of multiple cells using & operator. We can concatenate both Text and numerical data and display in required Cell. The following example shows you how to get the different formats of data from multiple cells and display the combined information in a cell.
Referring Data from a Range of Cells
The following example shows you referring a range and perform mathematical calculations. It will reference Range D1 to E5 and put the Sum of the data in Range A1
Referencing Cells from another Sheet
Referencing the Cells from one sheet is very easy in Excel. We need to pass the Sheet Name in the Formula followed by ‘!’ symbol. Exclamation symbol is used to refer the Worksheet in the Excel Formula.
The following example will refer the Cell content form another worksheet (Data) and display in a Cell.
We need to put the sheet name between single quotes when the Sheet name is having multiple words. For example the following formula will refer the Cell form another sheet named with multiple words (Data Sheet)
Referencing and Concatenating Cells
We can concatenate the Cells using Concatenate operator or using CONCATENATE Function in Excel. Let us see the both the methods to concatenate Cells in Excel.
Using Concatenate Operator: You can refer the cells using the Cell address and use the & (ampersand) operator to concatenate the Cells. For example, the following formula will concatenate cells (D1 and E1).
Using CONCATENATE Function: We can use the Excel Function to refer and concatenate multiple cells and ranges in Excel. You use the , (comma) t0 specify the reference of each Cell in the formula.
Referencing a Named Range
We can refer the Named Ranges in Excel using Formula. Name of Range should follow the = operator to refer a named range in Excel. The following Formula refer the named range called ‘TotalSales’.
The will display the Cell value which is named as ‘TotalSales’.
The following examples shows you, how to refer the range of cells in Excel. The Range (‘Sales Column’) is referred in SUM function to return the total value of ‘Sales Column’ Named Range.
Showing Cell values on a Shape
Follow the below instructions to display the cell contents on a Shape.
- Select the Shape
- Click on the Formula bar
- Enter the = (assignment) operator
- And put required Cell Address (For example: =$A$3)
Showing Cell value in Chart Title
Follow the below instructions to display the cell contents in Chart Title.
- Select the Chart which you want to display the Cell Value
- Go to ‘Chart Design’ Tab in the Ribbon
- And Show the Chart Title from Chart Elements
- Click on Chart Title of the Selected Chart
- Click on the Formula bar
- Enter the = (assignment) operator
- And put required Cell Address (for example: =Sheet2!$K$8)
I’d like to learn a formula that will identify a character, for example “_”, and return the contents of the next cell. The results are pulled from a data sheet and compiled into a list/column.
For example, if d:d contains “_” show contents of f:f
Any help would nmbe appreciate!
How would you format a cell to show the content of another selected cell in the same sheet?
I’m thinking of a text search function that actively translates the content of a selected cell in a column, maybe using the Googletranslate function, but I’m currently stuck on the first part of getting the content.
I have a cell that contains the address of another cell. How can I get the value of this cell?
Let’s say your Cell B1 contains the address of Cell A1, and you want to get the value of Cell A1?