Pull Same Cell from Multiple Sheets into Master Column in Excel

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show you how to pull the same cell from multiple sheets into a master column in Excel.

Pull Same Cell from Multiple Sheets into Master Column in Excel: 2 Ways

Before starting, let’s discuss the sample dataset we are going to use here.

We have order details for the first 6 months of a year (January, February, March, April, May, and June) in 6 consecutive worksheets. We have named the sheets Jan, Feb, Mar, Apr, May & Jun.

The following image shows the data for January.

sample order details

Now, we will create a master column in a separate sheet (you can name it a master sheet). Then we will pull data from the same cell from multiple sheets.

Depending on the situation, you may need to just extract data, or you may need to not only extract data from the same cell of multiple worksheets but also add them up. So, we will discuss these two cases.

1. Pull Data of Same Cell from Multiple Sheets and Sum Them into a Master Column

In the 1st example, we will see how to pull data for the same cell from multiple worksheets and sum them into a master column in another sheet. We will use 4 methods to perform this: using the Consolidate feature, using cell reference, a formula using 3D reference, and using a formula with SUM, INDIRECT, and CELL functions.

i. Use of Consolidate Feature

  • First off, create a table like the following. We will keep all the cells empty so far.

  • Now, click on cell B5 and go to the Data tab → Data Tools group → click on Consolidate option.

navigating to consolidate feature of Excel

  • The Consolidate window will be there. Now choose Function as Sum → Mark Left column in Use labels in section.
  • Now proceed to create a reference. Click the arrow icon as shown in the following image in the 3rd step.

The consolidate window of MS Excel

  • Go to the first sheet Jan → select the range B5:C12 → again click on the arrow in Consolidate – Reference: window.

Pull Data for Same Cell from Multiple Sheets and Sum Them into a Master Column

  • Now, we will be back to the Consolidate window.
  • This time, click on the Add button.

  • After pressing Add button, click on the next sheet, i.e. Feb.
  • Then again click on the Add button.

  • Repeat the last two bullet points until the last sheet, i.e. Jun is also added to the All references: list.
  • Then click OK.

  • By now, you will discover that you are done with the task. See the image below of what we have got in our case.

Read More: How to Pull Data from Multiple Worksheets in Excel

ii. Using Cell Reference

Well, this is a less complex but tedious approach.

  • You just have to create a suitable table with a master column and write the following formula in the master column. Here our first cell is C5.
  • The formula is:

Pull Data for Same Cell from Multiple Sheets and Sum Them into a Master Column

  • You can directly copy it and use it, or put an equal sign first → then go to the first sheet → click on the data cell → then put the plus sign → then go to the next sheet, and repeat all these until you reach the last sheet.
  • Then press ENTER. You will get the result.

Tiresome approach and we do not recommend this for workbooks with a huge number of sheets.

iii. Using Formula with 3D Reference

This approach is easier than using merely cell references and adding them up. Look how we use this here.

  • Keep all the source sheets one after another serially.
  • Then, go to the Qty column and type =SUM(, i.e. type an equal sign, write SUM, then type a parenthesis.
  • Then go to the first sheet, i.e. Jan.

  • Now, press the SHIFT key, keep a hold on it and go to the last sheet directly (you can ignore the sheets between them).
  • Click on the last sheet name.
  • Now, click on cell C5 (Choose the cell according to your dataset).
  • Finally, close the first brackets and press ENTER.

Pull Data for Same Cell from Multiple Sheets and Sum Them into a Master Column

  • Drag the fill handle icon to copy the formula down.

copying an Excel formula using fill handle tool

iv. Using Formula with SUM, INDIRECT & CELL Functions

Lastly, we can also use a formula so that we don’t have to hover across all these sheets.

  • First of all, we have to make a vertical list of sheet names.
  • In our case, the list is in G5:G10.
  • We have used a VBA code to create this, but we will not explain that here right now. That will be out of context. You can use the code from the file we have attached here. By the way, Excel has a new update, and the macro will be disabled in a downloaded XLSM file. You have to add our site to the trusted list in the internet options of your computer to enable the macros in it.
  • However, after creating the vertical list, enter the following formula in your master column.

Combine INDIRECT and CELL Functions to Pull the Same Cell from Multiple Sheets and Sum into Master Column

  • Now, drag the fill handle to copy the formula.


Use absolute reference for the range that has the sheet name, here G5:G10.

2. Combine INDIRECT and CELL Functions to Pull the Same Cell from Multiple Sheets into Master Column

In the 2nd example, we will use the INDIRECT and CELL functions to pull the same cell from multiple sheets into a master column of a separate sheet.

I will not only give you the formula, but I will explain it too so that you can use it for your case.

  • First, let’s know what the INDIRECT and CELL functions do.
  • The INDIRECT function returns the reference specified by a text string.
  • On the other hand, the CELL function returns information about the location (and formatting+contents), according to the sheet’s reading order, in a reference.
  • Now, we have created 6 master columns for 6 months, and we want to extract the order quantity of each customer. These quantities are now located in 6 different sheets along with the customer names. We just want them together now.
  • First, we have to create a list of all sheet names serially in a horizontal manner.
  • We have created a list using a suitable VBA code and have added a button so that you can get the list of sheet names easily. Open the Excel file, and you will see it. To make the discussion shorter, I am not going to elaborate on those steps.

Combine INDIRECT and CELL Functions to Pull the Same Cell from Multiple Sheets into Master Column

  • Now, write the following formula in cell C5 and drag it to right and down directions.
  • Now, look at your dataset. We have pulled order details from the same cells into separate columns.

🔎 Formula Breakdown:

  • K$5&”!”&CELL(“address”,$C5)

Output: “Jan!$C$5”
>> CELL(“address”,$C5), this part returns the address of the cell referenced with $C5. The output is $C$5. Note that, we have used $C5 as a reference. We have used the dollar sign before the column number because we will drag the formula to the right, but we don’t want to change the column reference.
Now, why have we used C5 as a reference?- It’s because all the source sheets have data (that we want to extract) in the C5:C12 range.

>> Cell K5 has the name of the first sheet. The rest of the sheet names are right on it. So we have used $ before the row number, since we don’t want to change the row number but the columns.

>> The &, ampersand symbol will join the sheet name, ! mark (needed to reference an Excel sheet) and cell reference within the INDIRECT function.
So the formula becomes: INDIRECT(K$5&”!”&CELL(“address”,$C5))INDIRECT(“Jan!$C$5”)

  • INDIRECT(“Jan!$C$5”)

Output: 1000
Now the INDIRECT function simply extracts the referenced value from cell C5 of the Jan sheet.

Download Practice Workbook

You can download the following practice workbook to practice along with it while reading this article.


In this article, we have shown 2 cases to pull the same cell from multiple sheets into the master column in Excel. If you have any queries regarding them, please leave us a comment.

Related Articles

<< Go Back To Extract Data Excel | 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.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF