How to Consolidate Data in Excel from Multiple Rows

This guide will show you how to consolidate data in Excel from multiple rows. In Excel, consolidating data from rows is a common task, especially when you need to summarize or merge large datasets. You can consolidate data in Excel with multiple rows to create summarized reports for easy reference.

Steps: How to Consolidate Data in Excel from Multiple Rows?

Follow the below steps to learn how to consolidate data in Excel from multiple rows:

Step 1: Check Data in Rows

• Combining rows in Excel can be useful when you have multiple rows with the same data.
• For example, you have sales information of different fruits and you want to consolidate the data to get the total sales for each fruit.

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 sum function to get the total (its the default function in consolidation).
• Based on your requirement of consolidation, you can also use the other functions, such as count, average, max, min, product, count numbers, stddev, stddevp, var, and varp.

Step 4. Select and Add Reference

• Click on the destination cell where you want the consolidated data (for example, cell D2).
• Under “Reference”, select the data without headers (since in this example we have already mentioned the headers in cell D1 and E1).

• Click the “Add” button to add the data under “All references” list.

Step 4. Use Labels

• Select the “Left column” box (since in this example we have the fruits category in left column based on which we want to consolidate the sales details).
• Do not tick the top row, since in this example, we have headers in cell D1 and E1. In case you have not entered the headers then you can tick this box.

Step 5. Get the Consolidated Data from Multiple Rows

• Click “OK” once you have updated the Consolidate window.
• You will get the consolidated data from rows based on the function, data reference and the labels selected in the Consolidate window.
• In this example, the sales for each fruit is added from all the rows and shown in cell E2 to E4.

Shortcut key to Consolidate Data in Excel from Multiple Rows

Below is the keyboard shortcut key to consolidate data Excel from multiple rows:

Conclusion

Consolidating data from multiple rows in Excel means consolidating information from several rows into one summary row. It’s especially helpful for summarizing large datasets, so you can see all important details in one row for easier decision-making and reporting.

Learn More