How to Consolidate Excel Data from Multiple Sheets into One: Step by Step

Who I am
Marie-Ange Demory

Author and references

Work has never been easier. With each new update of the different Microsoft Office programs, there is a new tool to learn how to use and that, regardless of the complexity that may present itself at the beginning, once you learn how to use it, everything becomes easier .

This is the case with Excel. It is not a simple spreadsheet, through this program you can do many things in a really simple way. Find out how easy it can be once discovered the shortcuts that offers you.

How to Consolidate Excel Data from Multiple Sheets into One: Step by Step

Consolidate data in Excel

This tool offered by Microsoft Excel is one way to combine data from cells found 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 delving 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.

  1. Create a new sheet inside the Excel file.
  2. Go to the tab Data of the ribbon.
  3. Find the option Consolidates .
  4. A box with several drop-down lists will appear.
  5. 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.
  6. The next section, Reference , take into account the location of the data you intend to use.
  7. Go to the sheet and select the data you consider for grouping and click add.
  8. Repeat for the other sheet that contains the data you want to add.
Read How to update my PC's motherboard BIOS or UEFI to the latest version? - Step by step guide

Final procedure

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

  • Check the boxes for: top row, left column and link to source data.
  • By clicking on accept, the new sheet will appear on the full screen of 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.

View 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 the 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, pivot tables, drop-down lists, among many other things.

Data combinations work not only for sheets that are in one file, but 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.

Read How to RECORD GAMES Capture the screen on my PC (example) - See how it's done

Don't waste any more time and start practicing it 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 may want to learn also how to calculate annual growth rate in Excel right here.

add a comment of How to Consolidate Excel Data from Multiple Sheets into One: Step by Step
Comment sent successfully! We will review it in the next few hours.