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 combine multiple worksheets into one. This smart tool for Excel 2013 - 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 Excel Tables wizard will help you combine data from two Excel sheets by matching columns in seconds!
Free Download
Free 15-day trial version, 12.26 MB

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 checkbox next to the Worksheet column. It lets you instantly select and deselect all items 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 Exclude button. Delete a workbook from the list by pressing the Exclude button
  5. By default, the add-in automatically pulls all data ranges from your selected worksheets. However, you can change them if necessary. To alter the highlighted area, select the sheet name in the list and click on the Select range icon next to it. You will see a dialog box allowing you to pick the correct data. To pick a range, select the sheet name and click on its Select range icon Use this dialog box to pick the correct range
  6. Pick the consolidation type that suits you:

Consolidate data from multiple worksheets in a single worksheet

Choose the option 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. Just click on the small down arrow next to Use function and pick the correct option from the drop-down list. This is the standard list you would normally find in Excel: 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 this step you can choose one of the two options:
    • Press the Consolidate by label radio button if your task is to combine data depending on the location that contains headers. You can tick off Use header label, Use left column label, or select both checkboxes. Press this radio button to combine by the location of the headers
    • Select the Consolidate by position radio button if you need to disregard the labels in the source ranges. Press this radio button to combine regardless the location of the headers
  5. Click Next to select the destination for the consolidated data. You can choose 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 Custom location field. Select the top left cell for the resulting table
  6. Click Finish to merge multiple spreadsheets into one.

Copy contents from several worksheets into one

You may keep workbooks that have identically or differently 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 according to the headers.

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 the options for pasting 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
    • Now you can specify if your tables have headers by selecting the correct checkbox. Specify if your tables have headers by selecting this checkbox
      Note. If you tick the checkbox My tables have headers, the data from identically named columns will be joined even if the column order is not the same.
      Join data from identically named columns even if their order is not the same You can tick the checkbox Copy data only for the matching columns in my first table in the list. Say your first sheet in a workbook is a template. It contains the columns Name and ID. The other sheets contain these columns plus additional ones, like PO and Delivery. You need to join data only from those columns that appear in your template without adding non-matching headers to the end. Copy data only for the matching columns in the first table in the list
    • 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 Finish.

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 Finish.

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 2013, 2010-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. Copy the selected spreadsheets to one workbook radio button
  4. On the Consolidation options step choose how you prefer to combine data in Excel. Select the consolidation options
  5. Default destination for the consolidated data is New workbook. Default destination for the consolidated data is New workbook
  6. Click Finish.

Featured customers

Ablebits.com featured customers
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