How to reference same cell from multiple worksheets in Excel?
When working with Excel, it’s very common to organize related data across multiple worksheets within a single workbook. Sometimes, you may need to combine or compare data by pulling the same cell—such as B8—from all these worksheets into a single summary or master sheet. This approach can help you consolidate consistent information, like monthly results or departmental totals, into a comprehensive overview for easier analysis and reporting. For instance, you might want to extract monthly figures stored in cell B8 from several sheets (Sheet1, Sheet2, Sheet3, Sheet4, and so on) and list them together in a master sheet, as shown below.
 |  |  |  |  |  |
Excel offers several ways to reference the same cell from multiple worksheets and collect results into a master worksheet. Depending on your comfort with formulas, VBA, or third-party tools, you can choose the method that suits your specific needs. Below are practical approaches to achieve this task efficiently. Additionally, you will find two other recommended solutions in the table of contents that can handle related scenarios:
Reference same cell from multiple worksheets into one master sheet with formula
Reference same cell from multiple worksheets into one master sheet with VBA code
Reference same cell from multiple worksheets into one master sheet with an amazing feature
Reference same cell from multiple worksheets into one master sheet with formula
If your worksheet names follow the default pattern (Sheet1, Sheet2, Sheet3, etc.), Excel formulas provide a fast way to reference the same cell from a series of worksheets. This approach is simple, dynamic, and does not require any code, making it especially suitable if you frequently add or remove sheets that follow the same naming convention.
1. Start by creating a list of helper numbers in a column, such as entering 1, 2, 3, 4, and so on. Each number corresponds to one of the worksheet suffixes you want to reference. This step helps automate the reference process and avoids manual formula writing for each sheet. See screenshot:
2. In the cell where you want to display the extracted value (for example, in cell B2 of your master sheet), enter the following formula. Then, drag the fill handle down the column to automatically reference each corresponding worksheet. All target cell values (here, B8 from each sheet) will be retrieved at once.
Note: In this formula, E2 refers to your helper number (1,2,3, …) which matches the worksheet name suffix, and B8 is the address of the cell you want to pull. Adjust E2 and B8 as needed for your actual layout and cell. This method only works directly when your worksheets follow the naming pattern "Sheet1", "Sheet2", etc.
This formula-based solution is quick and easy for structured workbooks but can become cumbersome if sheets have non-numeric names or are added/removed frequently. Double-check for any #REF! errors, which may indicate a mismatch between your helper values and existing sheet names.
Reference same cell from multiple worksheets into one master sheet with VBA code
If your workbook contains many worksheets and their names do not follow a predictable pattern (i.e., the sheet names are customized or not sequential), using VBA provides a direct approach to extract the same cell reference from all sheets into your master worksheet. This method enables batch processing regardless of sheet names and is helpful for consolidating large or non-uniform workbooks.
1. In your master worksheet, click the cell (e.g., B8) where you wish to start displaying extracted data from other worksheets. Ensure there are enough empty rows beneath this cell to accommodate the results that will be extracted from each worksheet.
2. Press the ALT + F11 keys together to open the Microsoft Visual Basic for Applications window.
3. In the VBA editor, click Insert > Module. Copy and paste the following code into the module window:
VBA code: reference same cell from multiple worksheets
Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub4. After pasting the code, press the F5 key or click the Run button to execute the code. All values from cell B8 of each worksheet (excluding the master sheet itself, if so coded) will be collected and populated into the master worksheet, starting from your selected cell downwards. Results will be listed vertically, making them easy to review or further analyze.
Note: This VBA solution works for the cell you select before running the code. For example, if you click cell A1, it will pull all corresponding A1 values from the other sheets.
Reference same cell from multiple worksheets into one master sheet with an amazing feature
For users looking for a more convenient solution without formulas or coding, Kutools for Excel provides an intuitive feature called Dynamically Refer to Worksheets. With this tool, you can extract the same cell reference from dozens or even hundreds of worksheets with only a few clicks, regardless of how sheets are named. This is ideal for users who frequently consolidate data and prefer a visual, step-by-step process over writing formulas or editing VBA code.
After installing Kutools for Excel, follow these steps to consolidate data from multiple worksheets:
1. In your master sheet, select the cell (for example, B8) where you want to display the references to be filled from other worksheets.
2. Go to the Kutools tab, select More, and then choose Dynamically Refer to Worksheets as shown below:
3. In the Fill Worksheets References dialog box, choose Fill vertically cell after cell from the Fill order, and click the little lock beside the formula text box, and the grey lock will become yellow lock, it means the formula and cell reference has been locked, then you can click any a cell to extract the cell B8 references from other worksheets, in this example I will click cell B2. Then, check the worksheets which you want to extract the cell references from. See screenshot:
Note: Always click the lock to fix your chosen cell reference. If the lock is not set, the cell reference may shift based on your selections, potentially leading to incorrect results.
4. Click the Fill Range button. The program will instantly extract the chosen cell values (e.g., B8) from all selected worksheets and list them down your master worksheet, each in its own row. Once done, you can close the dialog box and adjust your summary sheet formatting as needed.
This approach is particularly useful when dealing with a large number of sheets or when sheet names are irregular and frequent manual referencing or formula adjustments are impractical. The process is visual and easy to follow, making workbook consolidation tasks much more manageable.
Click to Download Kutools for Excel and free trial Now!
More articles:
- Copy Rows From Multiple Worksheets Based On Criteria Into A New Sheet
- Suppose you have a workbook with several worksheets, each sharing the same format. If you want to copy all rows where column C contains the text “Completed” into a new worksheet, this guide will help you achieve this efficiently—no need to copy and paste manually, saving significant time.
- Create A List Of Unique Values From Multiple Worksheets
- Looking for a fast way to compile a list of unique values from all worksheets in a workbook? For example, if multiple sheets contain lists of names that overlap, this technique helps you extract each unique name into a single new list for analysis.
- Countif A Specific Value Across Multiple Worksheets
- If you have several worksheets containing similar data and want to count the number of times a particular value appears (for example, “Excel”) across all sheets, this article demonstrates effective approaches you can use.
- Insert The Same Image Into Multiple Worksheets
- Inserting an image into one worksheet is easy, but what if you want the same image to appear in every worksheet of your workbook? This guide demonstrates a method to efficiently achieve this in a few steps.
- Run The Same Macro On Multiple Worksheets At Same Time In Excel
- By default, running a macro processes only the active sheet, but you might need to apply it across multiple sheets at once. This article reveals methods to run the same macro on several worksheets simultaneously, improving efficiency.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in