Work has never been easier. With each new update of the different Microsoft Office programs, there is a new tool to learn to use, and that, regardless of the complexity that may present at first, once you learn to use it, everything becomes easier.
That is the case with Excel. It is not just any spreadsheet, through this program you can do many things in a really simple way. Discover how easy it can be once you discover the shortcuts that it offers you.
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.
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.
- Create a new sheet within the Excel file.
- Go to the Data tab of your ribbon.
- Locate the Consolidate option.
- A box will appear with different drop-down lists.
- 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.
- The next section, Reference, takes into account the location of the data that you are going to use.
- Go to the sheet and select the data that you will take into account for the grouping and click on add.
- 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 the combination of them. In this way:
- Check the boxes for: top row, left column, and link to source data.
- By clicking 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 show all the data corresponding to a specific month.
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.