How to consolidate data in Excel

Summarize data from several worksheets

With Consolidate Worksheets Wizard you can quickly summarize data from multiple worksheets into one. This smart tool for Excel 2016 - 2007 provides a number of consolidation options to choose from. You can merge data from one or several workbooks and csv files without opening them, combine sheets by label or by position, create summary reports, and more.

See also: Merge Excel Tables wizard will help you combine data from two Excel sheets by matching columns in seconds!
Free Download
7-day fully functional version

Consolidate data from multiple worksheets into a single sheet

Use the Consolidate Sheets tool to summarize data from several Excel files. For example, if you have a table with the number of sold items from each of your regional stores, you can use data consolidation to quickly get these figures in a single report.

Step 1: Select the worksheets to consolidate

  1. Start Consolidate Sheets for Excel by clicking on its icon. Start Consolidate Worksheets for Excel by clicking on its icon You will see a list of all open workbooks in the Consolidate Worksheets window. To add a file for consolidation, click Add files… and browse for the necessary Excel file in the Select files window. You can delete a workbook from the list by selecting it and pressing the Exclude button.
  2. Select the workbooks and worksheets for consolidation by ticking the checkboxes next to their names.
    Tip. If you have a long list of sheets, take advantage of the check-box next to the Worksheets column. It lets you instantly select and deselect all tables.
    See all open workbook(s) in the list on the Consolidate Worksheets Wizard window Feel free to use the Expand all option to see all sheets in your workbooks.

    By default, the add-in pulls all data ranges from the selected worksheets. 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 the table and a dialog box allowing you to pick the correct cells.

Step 2: Choose how to consolidate records

  1. First select the function that should be applied to your numbers when they are consolidated. Pick one from a drop-down list of functions you would normally find in Excel: sum, count, average, max, min, product, count numbers, StdDev, StdDevp, Var, and Varp.
  2. Specify if you want to summarize numbers by their position or if you prefer to use labels:
    • If your tables have the same headers, but the columns may be in a different order, choose to Consolidate by label. You can tick off Use header label to consider the top row, and/or Use left column label to look at the records in the leftmost column as well.
    • If all your sheets have the same structure, select the Consolidate by position radio button and disregard the labels in the source ranges. Select consolidation options
  3. Click Next to choose the destination for the consolidated data in Excel.

Step 3: Specify where to place the results

You can select one of the following locations for the resulting sheet:

  • Pick New workbook to create a new file with the summary.
  • Select New worksheet to add a resulting sheet to the current open workbook.
  • Or choose the destination for the summarized data manually by picking Custom location.
Specify where you want to place the results

If you prefer to place the results to a custom location, you can select the top left cell for your resulting table. To do this click the Select range icon or type the cell address in the Custom location field.

Click Consolidate to summarize data from multiple Excel files into one.

Featured customers featured customers
Contact us
  Publish your message on our support forum

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
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
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
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
Sheila Blanchard
Excel add-ins and Outlook tools -