How to consolidate values ​​or data from a row into a worksheet in Excel

Don't worry, here you will learn how to use it very easily, but before you start we recommend that you are familiar with concepts such as inserting formulas into text boxes in Excel, basic aspects that you should master.

What is data consolidation in Excel?

When we use the term consolidation, we mean it to the unification of related data from multiple sheets, that is, using the consolidation tool, you can count or add cells in Excel and perform various mathematical operations in multiple tables and show the final result in a unified table.

How to consolidate values ​​or data from a row into a worksheet in Excel" src="/images/posts/56160b6dc8c8b3f68a9ad8d6f176f7e1-0.jpg">

This can be especially useful for tracking inventory for multiple stores that sell similar products. In this way, you reach conclusions as total sales, shopping Total and other factors, although clearly this function can be applied to any circumstance.

How to consolidate values ​​or data from a row into a worksheet in Excel

Using tools like Solver to solve mathematical equations and operations in Excel is a great idea to make your work easier and provide clearer data

This is where data consolidation appears, as it is very useful to have the full control data belonging to a document. Either way, there are some things you need to keep in mind to apply this feature correctly.

The data and its ranges must be the same

The most recommended is that each of the rows you intend to consolidate has the same data type and the same length. Otherwise, conflicts are very likely to occur when viewing the totals.

For this reason, we recommend that you create before a model for each of the tables you want to create. This way you will avoid any inconvenience when using the consolidation function.

How to consolidate values ​​or data from a row into a worksheet in Excel" src="/images/posts/56160b6dc8c8b3f68a9ad8d6f176f7e1-1.jpg">

Consolidate data into an Excel sheet

1. Insert the Excel document you created earlier. In case you haven't created a specific space for summary or data collection, it's time for you to.
2. Position yourself on the space where you want the consolidated data to appear. You should now go to the section Data, located at the top of the screen.
3. The option appears in this space Consolidate, the depending on the function you want to perform, you can use additions, subtractions, etc.
4. Click on the section References, now go to the first table you want to include, select the whole table and click on the option Add.
5. Go to the rest of the tables, as the tables are the same, the system will detect them automatically and all you have to do is click on add (if they are not detected automatically, select them manually). Repeat this procedure for each of the tables that you want to add to the consolidation.
6. In the Use labels, select options Top row, Left column and we also recommend that you enable the option to create links with source data. Enabling the latter option will ensure that your data is automatically updated when you make changes.
7. To create the consolidated table, do click on the option OKAY. This will display a general table with the totals present in each of the tables that compose it.

Note that you can also consolidate data in Excel from multiple sheets into one, but this process is different from what we have shown before, to learn it you can visit the above link.