Sometimes we need to pull the same cell from multiple sheets into the master column in **Excel**. This is very important if you want to analyze your data in one sheet. In this article, we are going to show you some approaches to pulling the same cell from multiple sheets into the master column in **Excel** including the **Consolidate **feature, **SUM** function, **INDIRECT** function, and **CELL** function. So, let’s get started.

Suppose, a renowned company maintains its sales record on daily basis through **Excel**. We have collected some of the data from them. Using this data we will try to pull the same cell from multiple sheets into the master column.

## 📂 Download Excel File

Download free Excel workbook from here:

## Learn to Pull Same Cell from Multiple Sheets into Master Column

While writing this article, we’ve considered a dataset that contains approximately **3** columns and **11** rows. In this dataset, we added **Name**, and **Quantity** to make you better understand. With the help of these data, we will perform our required operation. As there are several datasets, we have taken the same fields with different values. But if you want, you can change the entities and put your own values.

**APPROACH**

### 1. Utilizing Consolidate Feature to Pull Same Cell from Multiple Sheets into Master Column in Excel

There is a very unique feature to pull the same cell from multiple sheets into the master column in **Excel**. The feature uses the **Consolidate **option. The **Consolidate **feature combines all the sheets you want to unite. Then you can also choose the function you need to perform. So, the **Consolidate **feature is very simple to work on.

__Overview of Consolidate Feature in Excel__

You can combine your data from various worksheets into one master worksheet using data consolidation. In other terms, the **Data Consolidation** function compiles data from a number of sheets or textbooks into a single, easily editable worksheet. Dealing with data consolidation might be challenging, which is the problem.

- A very helpful tool for understanding and presenting your data quickly and simply is
**Data Consolidation**. The screen could seem intimidating, but as with all great things, preparation is the key. - The worksheets may be located in other workbooks or in the same workbook as the master worksheet. By assembling data, you can more readily update and combine it as needed when you consolidate data.
- When the worksheets contain the same row and column names but the rows and columns aren’t all in the exact sequence, consolidate by category we utilize. The labels are used by Excel to match the data. If you want to combine worksheets by category, you must ensure that each worksheet’s title spelling and emphasis are the same.

You can easily obtain the method if you carefully follow the steps given below for you.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, you need to create a new table like below.
- Secondly, select the
**Data**tab. - Thirdly, click on
**Data Tools**and then choose the**Consolidate**option.

- Now you will get a dialogue box name
**Consolidate**. - Select
**Sum**for the**Function**option. - After that, check the
**Left column**and then click on the**arrow**sign.

- Belatedly, give the input for the
**Consolidate-Reference**:

`Sunday!$B$6:$C$11`

- Afterward,
**Add**the**Reference**.

- Then again go to the
**Monday**sheet and similarly**Add**to the**Reference**.

- Repeat the procedure until you add the last sheet (
**Thursday**). - Lastly, click on the
**OK**button.

- Finally, you will find out the result you wanted to see.

Undoubtedly, this procedure is very handy if you want to pull the same cell from multiple sheets into the master column in **Excel**.

**📕 Read More: Extract Data from Table Based on Multiple Criteria in Excel**

**APPROACH**

### 2. Using Formula with 3D Reference to Pull Same Cell from Multiple Sheets into Master Column

In the previous step, we used the **Consolidate **option to pull the same cell from multiple sheets into the master column in **Excel**. Now we are going to use the **3D Reference **along with the formula. Basically, we will utilize the **SUM **function to perform the operation.

__How to Use 3D Reference in Excel__

A **3D** **Reference** is one that points to the exact cell or range on various sheets. A **3D Reference** is a helpful and practical way to refer to many spreadsheets that have the same structure and share the same kind of data in their cells, such as when you combine financial data from various organizational departments.

The actions you take with worksheets supplied in a **3D Reference** are described in the samples that follow. The samples add the elements **B2** through **B5** on sheets 5 through 9 using the formula **=SUM(Sheet5:Sheet9!B2:B5)**.

- The data in cells
**B2**to**B5**from the new worksheets are taken into account in the computations if you insert or copy pages between Sheet5 and Sheet9 (the clarity’s endpoints). - The data of worksheets within
**Sheet5**and**Sheet9**that are moved outside of the specified worksheet range are not included in the calculation by**Excel**. - In the event that
**Sheets5**through**Sheet9**are deleted, Excel takes their values out of the equation. - Unless you change the order of the terminals in the workbook,
**Excel**modifies the computation to include the new sheets within**Sheets5**and**Sheet9**if you relocate them to a different position within the same workbook. The**3D Reference**alters the terminal worksheet when the endpoints are reversed. Consider the following scenario: You have a connection to**Sheet5:Sheet9**, the formula will refer to**Sheet6:Sheet10**if you insert**Sheet10**in the spreadsheet after Sheet9 does. The formula will change to link to**Sheet5:Sheet9**if you shift**Sheet6**in front of**Sheet5**. **Excel**eliminates the data on**Sheet5**or**Sheet9**from the analysis if you delete those worksheets.

Initially, it may look a little bit difficult for you. But gradually, you will learn this technique. We have made this process easy to understand by giving these steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, go to the sheet where you put the same cell value.
- Secondly, select cell
**C6**and write:

`=SUM(`

- Thirdly, go to the sheet for
**Sunday**.

- After going to that sheet, press and hold the
**Shift**key. - You need to click on the last sheet (
**Thursday**) while pressing the**Shift**key. - Afterward, select cell
**C6**. - Belatedly, close the formula with the
**parenthesis**“**)**” - Lastly, press the
**Enter**key.

- So, the formula is:

`=SUM(Sunday:Thursday!C6)`

- Now use the
**fill handle**to drag the formula to the entire column.

Indeed, this method is very simple to work on.

**APPROACH**

### 3. Applying Simple Cell Reference

If you want to learn to pull the same cell from multiple sheets into the master column in **Excel**, then we have another way to execute this. In the above part, we used the **3D Reference **which is a little bit complex. But in this part, we will apply simple cell reference to pull the same cell from multiple sheets into the master column. But we would recommend you not to use this method as it is kindly tiresome. Follow the given steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, select cell
**C6**. - Secondly, write the following formula on that cell.

`=Sunday!C6+Monday!C6+Tuesday!C6+Wednesday!C6+Thursday!C6`

- Thirdly, tap on the
**Enter**key.

- Afterward, you need to use the
**fill handle**to drag the formula to the entire column. - Finally, your result will appear to you as well.

Though we are not recommending you use this method, still you can do it if you are okay with it.

**APPROACH**

### 4. Using Formula with SUM, INDIRECT & CELL Functions

In the previous sections, we have used manual procedures to pull the same cell from multiple sheets into the master column in **Excel**. This time we want to do something different. We want to utilize **Excel **functions. Here, we will apply the **SUM**, **INDIRECT**, and **CELL** functions to complete the approach.

__Overview of SUM Function__

The sum of the given values is what the **SUM** function shows.

- Any combination of numbers, cell references, ranges, arrays, and constants can be used as these elements.
- The
**SUM**function can process 255 different arguments at once. The**SUM**function accepts up to 255 variables total in the manner of number1, number2, number3, etc. A predefined constant, a cell reference, or a range are all acceptable arguments. The sum of all the figures in the arguments is given. - The
**SUM**function is helpful for adding cells that might contain text values because it automatically disregards empty cells and text entries. Both numbers generated by formulas and predefined quantities will be added together using the**SUM** - The
**SUM**function simply disregards empty and text-valued cells.

You can use the syntax of the **SUM **function like the below:

**=SUM(number1, [number2], [number3], …)**

__Overview of INDIRECT Function__

From a provided text string, the **INDIRECT** function compares a legal cell reference. If you need to combine various text strings into a single text element that can be used as a legitimate cell reference, **INDIRECT** can be helpful.

**=INDIRECT(ref_text, [a1])**

The two arguments for **INDIRECT** are **ref_text** and **a1**. The text phrase to be evaluated as a reference is called **ref_text**. The reference format for the receiving text value is shown in **A1**. The style is “**A1**” while** a1** is **TRUE** (the standard value). The pattern is “**R1C1**” if **a1** is **FALSE**. Although the intent of **INDIRECT** may initially appear puzzling, there are numerous instances in which being able to generate a reference from text is helpful, such as:

- A formula that requires the name of a variable sheet
- A formula that can put together a cell reference out of text parts
- A constant reference that remains constant even after deleting rows or columns

The basic syntax of the **INDIRECT **function is like

__Overview of CELL Function__

For a comprehensive list of data on a citation, use the **CELL** function. The primary form of the **CELL **function is:

**=CELL(info_type, [reference])**

- The info type parameter, which needs to be wrapped in double quotes, indicates the type of data returned (“”).
**CELL**may return a workbook’s filename and directory, the address of a cell, and details about the formatting applied to the cell. - A complete list of info categories and format codes can be seen below.
- Info type and reference are the two inputs the
**CELL**function accepts. - The text string info type identifies the kind of information that has been requested. For a complete list of information kinds, check out the table below. A cell reference is a link. A reference normally consists of one cell.
- The
**CELL**function provides data regarding the first cell in reference if the reference refers to more than one cell. - The cell location used for referencing is arbitrary and can be left out for some types of information (such as filenames). The name of the current “active sheet,” which may include or exclude the sheet where the formula occurs and may even be in a separate workbook, will be returned by
**CELL**if a reference is not provided. Use**A1**as a point of reference to prevent confusion.

You need to maintain the following instructions accordingly.

⬇️⬇️ **STEPS** ⬇️⬇️

- First, you have to write the name of your sheets on the active worksheet (in column
**E**). - Secondly, you need to select cell
**C6**. - Thirdly, you need to write the following formula on that cell.

`=SUM(INDIRECT($E$6:$E$10&"!"&CELL("address",C6)))`

**🔨 Formula Breakdown**

Here:

👉 **CELL(“address”,C6) **refers that the value will be addressed according to the cell number which starts from cell **C6**.

👉 The** INDIRECT** function will take the referred value and simply extracts it from the active sheet’s cells **E6** through **E10**. And the **! **mark is required to access on the **Excel** sheets as well.

👉 Finally, the **SUM** function will sum up all the values extracted from the previously used functions and provide us with the final result.

- Then tap the
**Enter**key.

- Fourthly, use the
**fill handle**and drag down the formula. - Consecutively, you will get the desired result in front of you.

Truly, this approach is convenient to pull the same cell from multiple sheets into the master column in **Excel**.

**📕 Read More: 6 Ways to Extract Data from Excel Based on Criteria**

**APPROACH**

### 5. Combining INDIRECT and CELL Functions to Pull Same Cell from Multiple Sheets into Master Column

You can use the **INDIRECT **and **CELL** functions together to pull the same cell from multiple sheets into the master column in **Excel**. The **INDIRECT** function will retrieve data from the other cell and the **CELL** function will provide the value from the same of another worksheet. This method is not very difficult. You can achieve this if you follow the given steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, you need to create a horizontal table to write the sheet name consecutively.
- Secondly, select cell
**C6**and write down the following formula as well.

`=INDIRECT(J$6&"!"&CELL("address",$C6))`

**🔨 Formula Breakdown**

Here:

👉 This section of **CELL(“address”,$C6″)** returns the address of the cell specified by **$C6**. The result is **$C$6**. Keep in mind that we utilized **$C6** as a reference. Because we intend to drag the equation to the right but do not want to modify the column reference, we put the dollar symbol before the column number.

👉 The first sheet’s name is located in **CELL** **J6**. The contents of the remaining sheets are directly there. Since we don’t want to modify the number of rows but rather the columns, we utilize **$** before the row number.

👉 Within the **INDIRECT** function, the **&**, **ampersand symbol** will connect the sheet name,**!** mark (required to access an **Excel** sheet), and cell value.

Thus, the equation is **INDIRECT(“J$6&”!”&CELL(“address”,$C6)** → **INDIRECT(“Sunday!$C$6”)**

- Afterward, you need to drag the formula in the right direction.

- After that, you have to drag the formula down to the entire column using the
**fill handle**icon. - Eventually, your result will appear to you indeed.

Finally, you must say that this process is very easy to work on if you follow the instructions properly.

**📕 Read More: 4 Ways to Pull Values in Excel from Another Worksheet**

## How to Pull Data from Multiple Workbooks in Excel

Sometimes users may want to pull data from multiple workbooks in **Excel** because it will be easier to study any data if they are in one sheet. That’s why users sometimes want to do this. Suppose, we have four different workbooks containing different data (given below). We want to pull those data in one sheet in one workbook. So how we can do this? To get the answer to this question, you need to stick to the end of this section from the beginning. So let’s get started.

We have made this section easier for you by adding the below instructions.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, go to the
**Data**tab - Secondly, choose the
**Get Data**option and click on**From File**. - Thirdly, select the
**From Folder**option.

- Afterward, you will get a dialogue box. You need to choose the above-mentioned files and import them.
- Fourthly, you need to click on the
**Combine & Load**feature.

- Next, you will get a dialogue box name
**Combine Files**. - Fifthly, select the
**Dataset**feature and then check the**Skip files with errors**. - Lastly, click on the
**OK**button.

- Finally, your result will appear to you.

Truly, this method is very handy to pull data from multiple workbooks in **Excel**.

**📕 Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet**

## 📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️ You need to be careful while using the **Consolidate **feature in **Excel**.

🖊️ You have to be conscious when you intend to use the Excel functions to execute the above methods as well.

## 📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌 Initially, we used the **Consolidate **feature to pull the same cell from multiple sheets into the master column in **Excel**.

`📌`

Secondly, we used the formula with **3D Reference**.

📌 Thirdly, we applied simple cell reference to pull the same cell from multiple sheets into the master column in **Excel**.

📌 Fourthly, we utilized the formula with the **SUM**, **INDIRECT**, and **CELL** functions.

`📌`

Afterward, we applied the **INDIRECT** and **CELL** functions together.

📌 Finally, we tried to show you the way to pull data from multiple workbooks in **Excel**.

## Conclusion

We wish that you would be able to pull the same cell from multiple sheets into the master column in **Excel** using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website **www.ExcelDen.com**.