Pinterest Pixel

Consolidate in Excel with the CONSOLIDATE Tool

The Consolidate tool in Excel is located in the Data menu and combines values from multiple ranges... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Consolidate in Excel with the CONSOLIDATE Tool | MyExcelOnline Consolidate in Excel with the CONSOLIDATE Tool | MyExcelOnline

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.

Consolidate in Excel with the CONSOLIDATE Tool | MyExcelOnline
Download workbookConsolidate.xlsx

STEP 1: Select a cell outside your data table.

Consolidate in Excel with the CONSOLIDATE Tool

STEP 2: Go to Data > Consolidate

Consolidate in Excel with the CONSOLIDATE Tool

STEP 3: For the Reference, make sure to select the entire table.

Then ensure Use Labels has both Top Row and Left Column selected.

Press OK.

Consolidate in Excel with the CONSOLIDATE Tool

Your consolidated data table is now ready!

Consolidate in Excel with the CONSOLIDATE Tool

If you like this Excel tip, please share it
Consolidate in Excel with the CONSOLIDATE Tool | MyExcelOnline Consolidate in Excel with the CONSOLIDATE Tool | MyExcelOnline
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  How to View Multiple Worksheets in Excel

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...