How to Consolidate Data in Excel

This guide will show you how to consolidate data in Excel step by step. Consolidating data in Excel is important for anyone working with large amounts of information from different sources. Whether you are merging data from various outputs, combining the results, or integrating data from different reports, Excel has powerful tools to help. By combining data effectively, you can better analyze trends, create detailed reports, and make informed decisions.

How to Consolidate Data in Excel from Multiple Worksheets?

You can consolidate data in Excel from multiple worksheets to summarize and analyze information spread across various sheets. Follow the below steps to learn how to consolidate data in Excel from multiple worksheets into a single worksheet:

Step 1: Check Data in Worksheets

• In each worksheet, you should make sure that:
  • each data range is in a list format,
  • each column should have a header in the first row,
  • each column should contain similar data, and
  • there should be no blank rows or columns.
• In each worksheet, all the data ranges must have the same layout.
• Leave the worksheet empty in which you want the consolidated data to be reflected.

For example, you have information spread across two different sheets with January and February sales data and you want to bring it all together into one worksheet in Excel.

Step 2: Access Consolidate Button

• Go to the Data tab on the Ribbon.
• Click the “Consolidate” button in the “Data Tools” section.

Step 3. Select Consolidate Function

• Select the desired function to consoidate.
• The default function is sum in consolidate. The other functions are count, average, max, min, product, count numbers, stddev, stddevp, var and varp.

Step 4. Add All References of Worksheets

• Under “Reference”, select the first worksheet data with headers.

• Click “Add” to add the first worksheet under “All references” list.

• Similarly you can add the second worksheet by selecting the data under “Reference” and clicking the “Add” button. All the added worksheets will show under “All refrences”.
• If you want to delete any sheet reference, then select the reference under “All references” and click the “Delete” button.

Step 5. Use Labels

• If you want to show the column header then tick the “Top row” box under “Use labels in” section.
• Tick the “Left column” box if you have static data and want to show the information.
• If you want to create links to the source worksheet data then tick the “create links to source data” box.
• Click “OK” once you have updated the Consolidate window.

Step 6. Get the Consolidated Data from Worksheets

• Click “OK” once you have updated the Consolidate window.
• Based on the data in the workheets and the options selected under the “Functions” and “Use labels in”, the consolidated data will change.

What is the Shortcut key to Consolidate Data in Excel?

Below is the keyboard shortcut key to consolidate data Excel:

Conclusion

Data consolidation in Excel is a feature that lets you gather data from multiple worksheets into one single sheet in the same workbook. You can also use it to perform operations like sum, average, max, min, product, and more on the data. Simply put, to consolidate means to combine many things into one more effective thing. This tool summarizes data from different sheets and combines it into one sheet. It’s useful for businesses to merge data from different worksheets into one, after performing calculations. You can also consolidate data in excel from multiple rows.

Learn More