Consolida i dati in Excel
This tool offered by Microsoft Excel is one way to combine data from cells located in different sheets of an Excel file or in different books, allowing you to group them and get a summary of all the information. This is an extremely useful tool for accounting.
How to use?
An important aspect, before fully entering into the manipulation of this tool, is that Excel, for this type of action, requires that the tables you want to group are the same. The only thing different between them should be the data.
- Create a new sheet inside the Excel file.
- Go to the tab Data of your ribbon.
- Find the option Consolidates.
- A box with several drop-down lists will appear.
- In the first list, you will choose the concept you want to work on. Sum, percentage, count, minimum, maximum, etc. In this case, we will choose the sum option.
- The next section, Reference, takes into account the location of the data you will use.
- Go to the tab and select the data you will consider for grouping and click on add.
- Repeat for the other sheet that contains the data you want to add.
Final procedure
Once you have followed these steps, you would already provide Excel with all the data you want to combine into the new sheet (it is important to verify that none of it is duplicated in any of the sheets) and the steps below 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 have selected.
Keep important columns
Taking into account the function that has been chosen, and that the whole has also been selected contents of the tables, it is very likely that there are many rows or columns that are irrelevant to you. So you should eliminate them leaving the ones that contribute to what you really care about.
Look in detail
If you need to view the data individually, icons with a plus sign (+) will certainly appear in the row bar which, once clicked, will act as a drop-down list and show all the give corresponding to a specific month.
Each icon basically represents a month. Count the number of icons to identify the month you want visualize. 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 the leaves
Excel has a large number of tools that enable the Customization of columns, dynamic tables, drop-down lists, among many other things.
Data combinations don't only work for sheets that are in one file, can also combine data from different books.
Go ahead and try it!
These tools allow you to make your task much easier than it was previously, DON'T you will no longer have to revise sheet by sheet each of the amounts for each month and each year.
Don't waste any more time and start practicing to manage it perfectly. Enjoy the positive results this program offers you and share your knowledge with your entire office.
If you learned with us, you might like it learn also how to calculate annual growth rate in Excel right here.
