Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 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 into one from different worksheets with Combine

Combine same ranges into one from different worksheets with Combine


Combine Columns or Rows (combine multuple cells based on row/column and separated by comma or space, or combine a range of cells into a single cell with specific separator.)

doc combine cells
(1.)Select Select Combine columns under To combine selected
cells according to following options
;

(2.)Specify a separator for the combined data, here I select Space;

(3.))Specify the cell you want to place your combined result;

(4.)Specify how you want to deal with the combined cells.

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


Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.

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 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 is free for 60 days.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After install Kutools for Excel, please do as below:

1. Open the workbook you want to combine the ranges and click Enterprise > 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 arrow down

doc combine ranges 2

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

(1) Check the workbook and the worksheets you need. See screenshot:

doc combine ranges 3

(2) Then you need to select ranges you need from the sheets by clicking doc select button. See screenshot:

doc combine ranges 4

Tip: If you want to combine ranges from multiple workbooks, you can click Add to add workbooks to the Workbook list, then select the ranges from each worksheets.

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

If the combined ranges are in the same cell range, such as Sheet1!A2:A10, Shee2!A2:A10 and so on, you can more quickly merge them with Combine of Kutools for Excel.

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


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

There is a feature in Combine that can quickly select the same ranges across multiple sheets and combine them into one.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

1. Click Enterprise > Combine to enable Combine Worksheets dialog, and check the first one option -- Combine multiple worksheets from workbooks into one worksheet. See screenshots:

doc combine ranges 1

doc arrow down

doc combine ranges 2

2. Click Next, and in this step, you need to check the workbooks and worksheets you need, then select the range from one sheet, and then click Same range button, you can see all sheets have the same range selected. See screenshots:

doc combine ranges 9

doc arrow down

doc combine ranges 10

3. Click Next and specify the setting you need in Step 3 dialog, then click Finish, then save the scenario or not. See screenshots:

doc combine ranges 5
doc combine ranges 6

Now the same range of selected sheets are merged.

doc combine ranges 11

doc arrow down

doc combine ranges 12

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.

Free download Kutools for Excel, you will be amazed at its convenient and powerful.

With Combine utility, you can do this operation:

Consolidate data from multiple worksheets

pay attention1If you are interested in this addi-in, click here to download the 60-days free trial.


Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: 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!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.