Pull Same Cell from Multiple Sheets into Master Column

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.

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:

• 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

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:

• 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:

• Now use the fill handle to drag the formula to the entire column.

Indeed, this method is very simple to work on.

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.

• 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.

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:

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.

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:

• 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.

• 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

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.

• 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.