How to Consolidate the Values in Multiple Worksheets

In Excel worksheets, many related values may spread among multiple worksheets. And in this article, we will introduce how to consolidate such values.

In an Excel file, there will certainly be many worksheets. For example, in this Excel file, you can see that this is the sales volume of different sellers.Sales Volume in 5 Months for consolidate values

Besides, there are five months in the worksheet. And for each month, there is an independent worksheet. But now you want to know the total sales volume of each person. If you add those numbers one by one, you need to switch the worksheets and note down the numbers. So here you can use the features of consolidating values in Excel.

Consolidate Values

And the following are the steps about using this feature.

  1. Create a new worksheet. You can rename it according to your need.
  2. And then click the A1 cell. You need to choose this cell because all the format need to keep consistent with the original form.
  3. Click “Data” on the ribbon.
  4. Click the button of “Consolidate” in the data ribbon. And then you will see the “Consolidate” window pop up.Data and Consolidate
  5. Input the area of first worksheet: January!$A$1:$B$6Input Area of Worksheet
  6. And then continue add the other worksheet area.





Remember to add the area one by one.

  1. Then check the box of “Top row” and “Left column”. In your actual worksheet, if there is no header of row or column, you don’t need to check the box. It all depends on your actual requirement. And if you want to make the result update with the changing of the source number, check the “Create links to source data”.Check the Three Options

And then you will get the result. as a result, this is the sum up of the sales volume of each seller in the five months.The Consolidate Result

And you will find that the value of first cell is missing. And you can simply add it manually. And you can also delete the column B that has been automatically created.

How to Rescue Damaged Excel File

The result of Excel file corruption can be devastating, especially when you have input important information into it. And such data disasters can happen due to various reasons, among which virus and malware are the primary culprit. And this Excel recovery tool can be your lifesaver. And it is able to retrieve data and information from eves badly damaged Excel files. Therefore, you can rest assured because of the powerful function of this tool.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit

One response to “How to Consolidate the Values in Multiple Worksheets”