How to consolidate data in Excel from several sheets into one – Step by step

Consolidate data in Excel

This tool offered by Microsoft Excel is a way to combine data from cells that are found in different sheets of an Excel file or in different books, allowing them to be grouped and a summary of all the information can be obtained. This is an extremely useful tool on the accounting level.

Consolidate data in Excel

How to use?

An important aspect, before entering fully into what is the manipulation of this tool, is that Excel, for this type of actions requires that the tables you want to group are the same. The only thing different between them should be the data.

  1. Create a new sheet within the Excel file.
  2. Go to the Data tab of your ribbon.
  3. Locate the Consolidate option .
  4. A box will appear with different drop-down lists.
  5. In the first list, you are going to choose the concept under which you want to work. Sum, percentage, count, minimums, maximums, etc. In this case, we will choose the sum option.
  6. The next section,  Reference, takes into account the location of the data that you are going to use.
  7. Go to the sheet and select the data that you will take into account for the grouping and click on add.
  8. Repeat the procedure for the other sheet that contains the data you want to add.

Final procedure

Once these steps have been followed, you would already be providing Excel with all the data that you want to combine in the new sheet (it is important to verify that none of them is duplicated in any of the sheets), and the following steps are the ones that will help you materialize their combination. In this sense:

  • Check the boxes for: top row, left column, and link to source data.
  • By clicking on accept, the new sheet will appear on your screen filled with the data you selected.

Keep the columns important

Taking into account the function that has been chosen, and that the entire content of the tables has also been selected , it is very likely that there are many rows or columns that are irrelevant to you. So you should eliminate them leaving those that contribute what really interests you.

View in detail

If you need to view the data individually, icons with the plus sign (+) will surely appear in the row bar, which, when clicked, will serve as a drop-down list and will show all the data corresponding to a specific month.

View in detail

Each icon basically represents a month. Count the number of icons to identify the month you want to view. To re-group it, click on the icons that will appear in the column on the left side of your screen and it will show the data again in a simple way.

Not just for leaves

Excel has a large number of tools that allow the customization of columns, pivot tables, drop-down lists , among many other things.

The data combinations not only work for sheets that are in a single file, you can also combine the data from different books.

Go ahead and try it!

These tools allow you to make your task much simpler than it was previously, you will no longer have to review sheet by sheet each of the amounts for every month and every year.

Don’t waste any more time and start practicing it to handle it perfectly. Enjoy the positive results that this program offers you, and share your learnings with your entire office.

If you learned with us, you might like to learn also how to calculate the annual growth rate in Excel right here.

Leave a Reply

Your email address will not be published.

Back to top button