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, check the books and sheets you want to consolidate. Besides, you can use the additional options:

See the list of all open workbooks in the Consolidate Worksheets Wizard window.

  1. Check Use the same range on all selected sheets to get the same range selected automatically across all the chosen sheets.
  2. If you tick the Use the same sheet names in other workbooks option and then start selecting sheets for consolidation, the tool will automatically tick sheets with the same name in other workbooks.
  3. 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.

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.

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

I'm trying to combine multiple sheets into one work book.I was able to do in older version but not on this version. I need each sheet from separate files combined into one workbook.

Reply

Hello Aaron,
Thank you for contacting us.

The thing is, we have rearranged and renamed some of the tools in newer versions of our add-in. The option you're looking for is now located in the Copy Sheets tool. We've replied your original request via email as well, please check your Inbox. Thank you.

Reply
Екатерина says:
May 22, 2020 at 5:35 am

Добрый день,
Скажите пожалуйста, как можно перенести данные из нескольких тысяч таблиц в одну? структура у всех файлов одинаковая. пример столбцов: компания, инн, сайт, тел.. т.е. значения в столбцах разные, а структура одна.

Reply

Добрый день, Екатерина.

Спасибо за вопрос. Наш Copy Sheets может Вам помочь, однако, из-за большого обьема данных мы рекомендуем обарбатывть таблицы группами. Опция Add Files на втором шаге позволит быстро добавлять нужные файлы.

Reply

Я не согласен с этими рекомендациями. Для столь большого количества файлов ищите инструмент, который работает напрямую с файлами, минуя Excel. Сам Excel просто "умрет" от такого их количества.

RAMESHKUMAR G says:
June 26, 2020 at 1:06 pm

i have to calculate difference between two sheets and save it in spearte worksheet

Reply
RAMESHKUMAR G says:
June 26, 2020 at 1:10 pm

please reply its urgent

Reply

Hello,

Thank you for contacting us.

For us to be able to help you better, please email us at support@ablebits.com with a detailed description of your task and a sample workbook containing 3 sheets: Sheet 1 and Sheet 2 - your source data and Sheet 3 - the result you expect to get. Please shorten the tables to 10-20 rows and replace any confidential information with some irrelevant data.

Reply

Hello,

Can I avail of the free trial?
Thanks!

Reply

Hello Emarcee,

All our Excel add-ins are available as a part of Ultimate Suite that you can install in a trial mode and check how the tools work. Here is the direct download link for you: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

Moreover, there is a user manual for each tool included in Ultimate Suite. You can open it by clicking on the corresponding link, run the tool in Excel and click the F1 button at the bottom of the add-in pane/window to have the help page open for this particular tool.

Hope you’ll find our software helpful.

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.