The Consolidate tool in Excel is located in the Data menu and combines values from multiple ranges into one new range.
You would use this feature when you have a single text column on the left and the column has many duplicate values.
First you need to select a blank cell outside your data set and then go to Data > Consolidate. You will then need to select all your data and make sure that the Top Row and Left column boxes are checked.
STEP 1: Select a cell outside your data table.
STEP 2: Go to Data > Consolidate
STEP 3: For the Reference, make sure to select the entire table.
Then ensure Use Labels has both Top Row and Left Column selected.
Your consolidated data table is now ready!