How to combine ranges from different worksheets/workbook into one?
In some cases, you may need to combine multiple ranges from different worksheets or workbooks into one. How can you quickly solve this 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
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 quick method to correctly combine multiple ranges across sheets or workbooks into one. Please follow the steps below.
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.
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:
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:
(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.
3. Click Next to go to the last step, and in this step, you can specify the characterized setting as you need. See screenshot:
4. Click Finish. A dialog reminds you to save the scenario, you can click Yes or No as you need. See screenshot:
Now, the selected ranges are merged into a new workbook.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
In addition to this, with the Combine utility, you can combine multiple sheets across workbooks into one sheet or one workbook. You can also merge sheets with the same name from multiple workbooks into one sheet or workbook, perform calculations, and more.
With the Combine utility, you can do this operation:
Consolidate data from multiple worksheets
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