Skip to main content

How to combine ranges from different worksheets/workbook into one?

Author: Sun Last Modified: 2020-09-27

In some cases, you may need to combine multiple ranges from different worksheets or workbooks to one, how can you quickly solve it in Excel? Here I will introduce the general method and the quick tricks for you to handle this task.

Combine ranges into one with Copy and Paste

Combine different ranges or same ranges into one from different worksheets with Combine

arrow blue right bubble Combine ranges into one with Copy and Paste

In Excel, the general method to combine ranges is to apply Copy and Paste features.

Select the first range and hold Ctrl key then press C to copy it, then go to a new worksheet and place the cursor at a cell, and press Ctrl + V to paste the first range. Then repeat this operation of copying and pasting to combine all ranges to the new worksheet.

This method is easy when only several ranges needed to be combined, but if there are dozens of ranges needed to be merged, this manual way will be a waste of time. Now here is a trick can quickly and correctly combine multiple ranges across sheets or workbooks into one, please read the below details.

arrow blue right bubble Combine different ranges or same ranges into one from different worksheets with Combine

If you have installed Kutools for Excel, you can use its powerful utility – Combine to quickly solve this task. If you have not installed Kutools for Excel, do no worry, you also can use Combine by free download it.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After install Kutools for Excel, please do as below:

1. Open the workbook you want to combine the ranges and click Kutools Plus > Combine to enable the Combine Worksheets dialog, then check the first option – Combine multiple worksheets from workbooks into one worksheet. See screenshots:

doc combine ranges 1

doc combine ranges 2

2. Then click Next to go to next step, and in this step you need to finish below operations:

(1)Click Add button to add the workbook(s) that you want to combine, then check the workbook and the worksheets you need, then select the range you want to combine together. See screenshot:

doc combine ranges 3

(2) If you want to combie the same rangs of each sheets, select the range of one sheet, then click Same range button to apply the range to all sheets.

doc combine ranges 4

3. Click Next to go to the last step, and in this step, you can specify the characterized setting as you need. See screenshot:

doc combine ranges 5

4. Click Finish. A dialog reminds you to save the scenario, you can click Yes or No as you need. See screenshot:

doc combine ranges 6

Now the selected ranges are merged in a new workbook.

doc combine ranges 7

doc equal
doc combine ranges 8

Kutools for Excel can help you easily handle Excel working, get it now!

In addition to this, with Combine utility, you can combine multiple sheets across workbooks into one sheets or one workbook, you can combine the same name sheets from multiple workbooks into one sheets or one workbook, you can merge sheets and do some calculation and so on.

With Combine utility, you can do this operation:

Consolidate data from multiple worksheets

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!
Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I cant even download the addin
This comment was minimized by the moderator on the site
Hi, what problem you meet when you download Kutools for Excel?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations