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
Free 15-day trial version, 14.64 MB

Consolidate data from multiple worksheets into a single sheet

Use the Consolidate Worksheets tool to summarize data from separate sheets into one. 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 roll up these figures into a single report.

  1. Start Consolidate Worksheets for Excel by clicking on its icon. Start Consolidate Worksheets for Excel by clicking on its icon
  2. You will see all open workbooks in the list on the Consolidate Worksheets window. To add a file for consolidation, click the Add files… You will get the Select fileswindow where you can browse for the necessary workbook. See all open workbook(s) in the list on the Consolidate Worksheets Wizard window
  3. 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. Take advantage of the checkbox next to the Worksheets column
  4. You can delete a workbook from the list on the add-in window by selecting it and pressing the Exclude
  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 rangeicon next to it. It will open the selected sheet and you will see a dialog box allowing you to pick the correct cells. Click on the Select range icon
  6. Select the function to consolidate your data. Just click on the small down arrow 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.
  7. On the Select consolidation options step you can choose one of the two alternatives:
    • Press the Consolidate by label radio button to summarize data depending on the location that contains headers. You can tick off Use header label, Use left column label, or select both
    • Select the Consolidate by position radio button to disregard the labels in the source ranges.
    Select consolidation options
  8. Click Next to choose the destination for the consolidated data in Excel. You can select one of the following radio buttons:
    • 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 destination for the summarized data manually by picking Custom location.
    Specify where you want to place the results If you choose a custom location for the consolidated spreadsheets, you can select the top left cell for your resulting table. To do this click the Select range icon or type the cell's address in the Custom location field.
  9. Click Consolidate to summarize data from multiple Excel worksheets into one.

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish your message on our 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
 
 
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
Awesome!!!
Sheila Blanchard