• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Consolidate in Excel

September 5, 2021 by Andrew Childress
Consolidate in Excel

As you work in Excel, you may find yourself using data spread across multiple worksheets. Let’s learn how to Consolidate in Microsoft Excel.

As you work in Excel, you may find yourself using data spread across multiple worksheets. It’s often useful to bring this data together. For example, you may have multiple columns of numbers on multiple sheets. You may want to find the average of all these values in a single column on a different sheet. Fortunately, Excel lets you do exactly that. Let’s learn how to Consolidate in Microsoft Excel.

How to Consolidate in Excel

Consider the following scenario. Sheet 1 has numbers in the range A1:A4.Sheet 2 also has numbers in the range A1:A4. On Sheet 3, you want to display the average of the values in each of the four rows in column B. You can use Excel’s Consolidate feature to do this.

To get started, click into cell B1 on Sheet 3. Then, on Excel’s Data tab, click Consolidate. The Data tab is found on the ribbon up at the top.

Consolidate data in Excel

Excel launches the Consolidate menu. The Function setting defaults to Sum, but you can change it to Average by clicking in the dropdown. Then, click into the Reference box.

Go to Sheet 1, then click and drag to select your range of data in A1:A4. Click the + icon on the Consolidate menu, and the reference will be added to the All References list.

Consolidate in Excel

Then, repeat these steps for Sheet 2, selecting the data and clicking +. Back on Sheet 3, click OK. Excel will calculate the average of the values in cells A1:A4 on both sheets, and display the results in Column B of Sheet 3. 

Excel consolidate data

As you can see, Excel’s Consolidate Sheets feature is a powerful tool to work with data spread across multiple spreadsheets. It avoids the need to duplicate data, by referencing values instead of copying them. Try it out next time you’re working with complex data in several locations.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Rate function in ExcelRATE in Excel
Next Post:Advanced Filter in ExcelAdvanced filters Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top