How to consolidate data in Excel

Consolidate data into a single Excel sheet

Merge multiple Excel files into one workbook

With Consolidate Worksheets Wizard you can quickly combines multiple worksheets into one. This smart tool for Excel 2010, 2007 and 2003 provides you with a number of consolidation options to choose from. You can merge data from one or several workbooks and csv files without opening them, create summary reports, consolidate worksheets by name and more.

See
also

Merge matching data from 2 worksheets

Merge Excel Tables wizard will help you combine data from two Excel sheets by matching columns in seconds!

Free Download
Free 15-day trial version, 06/30/2014

Select Excel workbooks and sheets for consolidation.

  1. Start Consolidate Worksheets Wizard for Microsoft Excel by clicking on its icon.
    Consolidate Worksheets Wizard icon
  2. You will see all open workbook(s) in the list on the Consolidate Worksheets Wizard window. See your open workbooks in the list on the add-in window To add a file for consolidation, click the Add files button. You will see the Select files window where you can browse for the necessary workbook. Add a file for consolidation by clicking the Add files button
  3. Select the workbooks and worksheets for consolidation by ticking the checkboxes next to their names. Check the worksheets you need to combine

    Tip

    If you have a big list of workbooks, take advantage of the Select All and Unselect All buttons to instantly tick or untick all the checkboxes in the list.

    Instantly tick or untick all the checkboxes in the list
  4. You can delete a workbook from the list on the add-in window by selecting it and pressing the Remove button. Delete a workbook from the list by pressing the Remove button
  5. Now you need to select the range for consolidation in each of the checked worksheets. To do this, select the sheet name in the list and click on the Select range icon next to it. To pick a range, select the sheet name and click on its Select range icon Click the Next button to proceed
  6. Pick the consolidation type that suits you:

Consolidate data from multiple worksheets in a single worksheet

Choose the option to Combine data from the selected ranges into one worksheet if you need to create a summary report or combine Excel data from multiple worksheets into a single one

  1. Choose the files for consolidation.
  2. On Step 2 of the wizard select the Combine data from the selected ranges into one worksheet radio button. Combine values from multiple ranges into one new range
  3. On Step 3 you can select the necessary function from the standard Excel list in the Use function drop-down list: sum, count, average, max, min, product, count numbers, StdDev, StdDevp, Var or Varp. The summary data will be created based on this function. Select the necessary function from this list
  4. On the Use labels in section select the options that indicate where the headers are located in the source ranges: the Top row, the Left column, or both. Define where the labels are located in the source ranges
  5. Check the Create links to source data option if you want the consolidation to be updated automatically when the source data are changed. Check the Create links to source data option
  6. Select the destination for the consolidated data by picking one of the following options:
    • Pick New workbook to create a new file with the results
    • Select New worksheet to add a resulting sheet to the current open workbook
    • Or choose the location manually by picking Custom location.
    Select the destination for the consolidated data If you choose custom location for the consolidated data, you can select the top left cell for your resulting table. To do this click the Select range icon or type its address in the Select the top left cell for the resulting table field.
    Select the top left cell for the resulting table
  7. Click Start to merge multiple spreadsheets into one.

Copy contents from several worksheets into one

You may keep workbooks that have identically structured data in each sheet. Choose the Copy data from the selected ranges to one worksheet if you need to combine multiple spreadsheets into a single, large table and place them one under the other.

Copy contents from the selected ranges to one table
  1. Select the files for consolidation.
  2. On Step 2 of the wizard select the Copy data from the selected ranges to one worksheet radio button. Choose to copy data from the selected ranges to one spreadsheet
  3. On the Consolidation options step choose how you prefer to paste the data. Below you can see how to consolidate data in Excel and paste it to a new location:
    • Paste all. If the copied ranges contain formulas, then the Excel formulas will be pasted along with the values;
    • Paste values only. Select this radio button if you don't want to copy formulas into the resulting table.
    • Paste link to data. Pick this option if you want the consolidation results to be updated automatically when the source data are changed. Choose how you prefer to paste the data
    • Tick the Preserve formatting checkbox to keep the format of the copied ranges. Keep the formatting of the copied ranges
    • Select Separate the copied ranges by a blank row to add an empty row between the joined tables. Separate the copied ranges by a blank row
  4. Select the destination for the consolidated data by picking one of the following options:
    • Pick New workbook to create a new file with the results
    • Select New worksheet to add a resulting sheet to the current open workbook
    • Or choose the location manually by picking Custom location.
    Select the destination for the consolidated data
  5. Click Start.

Combine multiple worksheets within each workbook and place them to one file

You can use Consolidate Worksheets Wizard to see that combining multiple Excel files into one is a really easy-to complete task.

  1. Select the files for consolidation.
  2. On Step 2 of the wizard select Copy sheets in each workbook to one sheet. Place the resulting worksheets to one workbook. Combine sheets in each book and place them to one file
  3. On the Consolidation options step choose how you prefer to paste the Excel data. You can select one of the following options: Choose how you prefer to paste the data
  4. Default destination for the consolidated data is New workbook. Default destination for the consolidated data is New workbook
  5. Click Start.

Consolidate sheets with the same name to one

You have several Microsoft Excel workbooks and some of them have worksheets with the same name. Your task is to merge several Excel files into one by combining the identically named worksheets and placing all resulting sheets to one workbook.

Consolidate worksheets with the same name to one sheet

Please follow the steps below to join multiple Excel worksheets into a single file in Excel.

  1. Select the files for consolidation.
  2. On Step 2 of the wizard select the Consolidate worksheets with the same name to one sheet radio button. Consolidate sheets with the same name to one
  3. On the Consolidation options step choose how you prefer to paste the data. Choose how you prefer to paste the data
  4. Default destination for the combined data is New workbook. Default destination for the consolidated data is New workbook
  5. Click Start and the add-in will easily consolidate several Excel files into one.

Combine multiple workbooks into one workbook

Do the following to combine several Excel 2010, 2007, 2003 files into one:

  1. Select the files for consolidation.
  2. On Step 2 of the wizard select the Copy the selected spreadsheets to one workbook radio button.
  3. On the Consolidation options step choose how you prefer to combine data in Excel. Define how you want to paste the data
  4. Default destination for the consolidated data is New workbook. Default destination for the consolidated data is New workbook
  5. Click Start.

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish this message on the forum
 

Please remember that we work from 8am to 4pm GMT,
this is 4:00am to 12:00pm EST, 1:00am to 9:00am PST, and our public holidays may differ from yours.

Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard