Consolidate numeric data in Excel

Use the Consolidate Sheets wizard to summarize data from multiple Excel worksheets 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 quickly get these figures in a single report.

Video: How to consolidate several files in Excel

How to use the Consolidate Sheets wizard

Start Consolidate Sheets

To run the add-in, click the Consolidate Sheets icon on the Ablebits Data tab in the Merge group:
Run Consolidate Sheets by clicking on its icon on Excel's ribbon.

Step 1: Select the worksheets to merge and calculate

You will see a list of all open workbooks in the Consolidate Worksheets window:
See the list of all open workbooks in the Consolidate Worksheets Wizard window.

There are 3 buttons at the bottom of the window to help you pick out the needed Excel files:

  • To add a file for consolidation, click Add files... and browse for the necessary Excel workbook in the Select files window.
  • To delete a workbook from the list, click on its name and press the Exclude button.
  • Feel free to use the Collapse all/Expand all option to see all sheets in your files.
Tip. If you have a long list of sheets, take advantage of the checkbox next to the Worksheets column. It lets you instantly select and deselect all tables.

By default, the add-in pulls all data ranges from the chosen 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:
A dialogue window where you can pick the needed data range.

When the worksheets are added and the ranges are selected, click Next to proceed to consolidation options.

Step 2: Decide how to consolidate records

A couple of consolidation options will help you achieve better results:
Set the consolidation options.

Choose the function

Select the function to consolidate your numbers with. A drop-down list offers 11 summary functions you would normally find in Excel: sum, count, average, max, min, product, count numbers, StdDev, StdDevp, Var, and Varp.

Summarize by rows and columns

If your tables have the same headers but the columns are in a different order, choose to Consolidate by label.

  • You can tick off Use header label to consider the top row:
    Consolidate files by column headers.
  • Or choose Use left column label to look at the records in the leftmost column instead:
    Summarize values by rows.
  • Feel free to check both option boxes to summarize by both, rows and columns:
    Combine and calculate data by row and column labels.

Merge and calculate by position

If all your sheets have the same structure, choose the Consolidate by position radio button and disregard the labels in the source ranges:
Summarize data by position in the worksheets.

Click Next to choose the destination for the consolidated data in Excel.

Tip. You can return to a previous step and pick out other worksheets if you click the Back button.

Step 3: Choose a place for the result

Select one of the following locations for the resulting sheet:
Specify where you want to place the results.

  • 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. Just click the Select range icon or type the top left cell address for your resulting table in the Custom location.

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

Responses

I need to Combine reports Sales by Customer, then by year. I have two columns on each report - by year.

What I would like to do is combine 3 reports into one - leaving the first column as the customer and not have duplicate customers - adding the details from 2016, 2017 and 2018 on one line, one customer name with their sales in the columns below by year. Is this possible?

Customer | 2016 | 2017 | 2018 as columns -

Reply

Hi, Denise,
Thank you for your question.
Your task can be easily solved with the help of two Ablebits tools, both are part of Ultimate Suite for Excel: Combine Sheets and Merge Duplicates.

Please take the following steps:
1. Open all the reports you want to combine: 2016, 2017, 2018.
2. Go to one of the reports, say, 2016. Find the Combine Sheets icon in the Merge group under the Ablebits Data tab.
3. On the first step, check all the sheets you need to combine: 2016, 2017, 2018.
4. Select the columns to combine: Customer and 2016, 2017, 2018.
5. Step 3: Select additional options or simply click Next.
6. Select the place to locate your combined data.
As a result, you will get a new worksheet with duplicate customers in the first column and data from your reports on different lines in the second, third, and fourth columns.
To get the sales data combined by the names of your customers, use the Merge Duplicates tool:
1. Select your new table and run Merge Duplicates, you can find its icon right near Combine Sheets in the same Merge group.
2. On step 2, choose only one key column with duplicate records: Customer.
3. On step 3, check columns 2016, 2017, 2018 and click the Finish button.
Thus, you will get one sheet with the list of customers and their sales by years 2016, 2017, 2018.

Reply
Natricia Drake says:
January 17, 2019 at 7:15 pm

I need to combine different data from 2 sheets. The student ID is the common field on both sheets.

Reply

Hi, Natricia,
Thank you for your comment.
Your task can be easily solved with the help of two Ablebits tools, both are part of Ultimate Suite for Excel: Combine Sheets and Merge Duplicates.
Please take the following steps:
1. Open the sheets you want to combine.
2. Find the Combine Sheets icon in the Merge group under the Ablebits Data tab.
3. On the first step, check both sheets you need to combine.
4. Select the columns: ID and other columns you want to see combined.
5. Step 3: Select additional options or simply click Next.
6. Select the place to locate your merged data.
As a result, you will get a new worksheet with duplicate IDs and data from your other columns.
To get rid of the duplicate IDs and get your data combined, please use Merge Duplicates:
1. Select your new table and run Merge Duplicates, you can find its icon right near Combine Sheets in the same Merge group.
2. On step 2, choose only one key column with duplicate records: ID.
3. On step 3, check columns with data you want to combine and click the Finish button.

Thus you will get the sheets combined the way you need!

Reply

Natricia, hi again,
Actually, there is an even simpler solution for your task: the Merge Two Tables wizard!
1. On step 1 and 2 select your sheets.
2. On step 3 pick the ID column (it is “matching column”).
3. Skip step 4, i.e. do not pick any columns on this step and just click the Next button.
4. On step 5 select the column(s) you want to add.
5. On step 6 you may leave the default setting.
I hope you will find this solution helpful.

Reply
Pamela Pierce says:
February 13, 2019 at 7:17 pm

I have two worksheets I need to combine into one.
The first worksheet has the following headings:
Client ID
Invoice no.
Invoice Date
Invoice Amount

The second worksheet has the followiing headings:
Client ID
Client Name
Invoice No.
Invoice Date
Invoice Amount
Gross Invoice Amount

I want to combine the two so that where Invoice No., Client ID, and invoice amount match, then Client Name and Gross Invoice Amount get populated on the same line.

Any ideas?

Reply

Hi Pamela,
Thank you for contacting us. The tool to solve your task is Merge Tables Wizard.
Open your sheets, go to the Ablebits Data tab and click on the Merge Two Tables icon.
1. On step 1 and 2 select your sheets.
2. On step 3 pick the following columns:
Invoice No.
Client ID
invoice amount
These are your matching columns.
3. Skip step 4, i.e. do not pick any columns on this step and just click the Next button.
4. On step 5 select the column(s) you want to add:
Client Name
Gross Invoice Amount
5. On step 6 read the additional options and decide which of them to tick.
Please do not hesitate to contact us with any other questions!

Reply
Brandy Prosch says:
August 7, 2019 at 1:59 pm

Is there a way to include the source cell formatting when using the consolidate sheets function? The numbers that I am consolidating have their cells filled with a color based on job location...

Reply

Hello Brandy,
Thank you for contacting us.

I'm sorry, but our Consolidate Sheets doesn't preserve formatting at the moment. If however, you use Combine Sheets to group your data, there is an option to preserve the formatting of the original data. Please try it, maybe it will work even better for you.

Reply

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-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
Awesome!!!
Sheila Blanchard