To use the Excel consolidate function, you must head to the file path Data > Data Tools > Consolidate. Then, a window will pop up, and you will make selections from there regarding how you want to consolidate your data.
If you need a little more help, donโt worry. The following guide will show you step-by-step with screenshots of how the consolidate feature works in Microsoft Excel. Read on to master this aspect of spreadsheet creation.
Table of Contents
What Is the Excel Consolidate Data Feature
Consolidating data in Excel allows you to get data from multiple worksheets and compile it into a single centralized worksheet. This can help make the information much easier to understand as it creates a ‘master’ worksheet where you can access the summarized data in a much cleaner form.
However, to consolidate data in Excel, you must meet a condition. It must have a single row and column of labels and headings. If the table has multiple labels, the tool may not work as intended. While this does seem to be a significant drawback of this function, most of the tables in your spreadsheet may already follow the specified format.
The consolidate function in Excel also allows you to add a link to the source worksheet, which enables you to refresh the consolidated table once the information in the source worksheet is updated.
Why Use Data Consolidation in Excel?
The data consolidation feature in Excel can help you organize your data.
Many financial analysts spend much of their day collecting and organizing information from different sources. One such example is creating the budget for an organization by merging the accounts from separate worksheets into a single one. This essentially saves you from the hassle of doing the process manually.
The true potential of the consolidate Excel function is realized when you use it to combine large data sets. You can copy and paste the information into a new table, but this will require a lot of manual effort and time. Another benefit of using the consolidate function is that the data in the consolidated worksheet will update automatically if any changes are made to the source worksheet.
Consolidation is a lot easier in Excel than other spreadsheet programs. But both Google Sheets and Excel are Excellent spreadsheet apps that provide their users with many features. Here is our in-depth comparison between Google Sheets and Excelย and which spreadsheet software may benefit you in 2024.
Ways to Consolidate in Excel
There are multiple ways in which you can consolidate your data on Excel. Let’s look at some ways to consolidate data in Excel before we check out how to consolidate data in Excel.
- Position: This technique to consolidate in Excel is best when the worksheets in your workbook follow a similar layout. This can mean using a particular template for your data or using the same rows and columns. The information in your spreadsheet must be in a similar arrangement, with the details being in the same locations. Using the By Position method, then moves the values from the source sheets to the targeted worksheet and adds the labels.
- Category: This method is best used when the worksheets you want to consolidate have similar labels and headings but different coordinates. The sheets can also be in a different order. In these cases, Microsoft Excel will copy all the labels and headings to ensure the data from the source sheet is the same as the data in the master sheet. This differs from the positional approach, where you must add labels for your referencing data. Another thing to note here is that capitalization or spelling mistakes can give you inaccurate results. Before using this method, you must specify the data you want to combine by selecting the Left Column and Top Right labels.
- Formula: When using this method to consolidate data, you will need to use a three-dimensional reference where you also refer to the sheet you want to get data from rather than the traditional cell reference that covers the rows and the columns. This technique is best suited for situations where you wish to consolidate the data from several worksheets in a single Excel workbook.
How to Use Consolidate in Excel
To demonstrate the Excel consolidate function, we have a workbook with three worksheets containing sales data for three years from 2020- 2022. We want to create a ‘master’ sheet that contains a sum of the data in the yearly worksheets. Let’s take a look at how to use the Excel consolidate function:
- Open the Excel workbook where you want to use the consolidate function. Go to the footer bar and click on the Plus (+)ย icon. This will create and add a new worksheet to the footer bar.
- You can rename the sheet to distinguish your master worksheet from the others. To do this, right-click on the sheet you want to rename and click the Rename button in the dropdown menu.
- Head to the Data tab from the top bar, look for the Consolidate button in the Data Toolsย section and click on it. This will open a new consolidate window in the middle of the screen.
- Here in the consolidate window, choose the Function you want to perform. Some functions you can perform are Sum, Count, Max, Average,ย Min, Count Numbers, Product,ย StdDevp, StdDev, Varp, and Var.
- To add the references, click Up Arrowย towards the right of the Reference textbox. This will open a newer window, and your cursor will turn into a Plus (+)ย icon. Go to the table whose reference you want to add, and click and drag to select the table. With the table selected, click on the Bottom Arrow to add the cell reference to the table.
- Click the Add button to insert the table reference to the consolidate function. Repeat Steps 5 – 6 to insert any additional tables.
- With the table references added, you can now select whether you want to add the left column and top row. You can do this by looking under the Use labels inย section. Here you can check the Top rowย and Left columnย options based on your needs.
- Optionally, you can Create links to source data. By clicking this option, Excel will create a link to the source data spreadsheets. If you update the source, it will also update the consolidated data and create an outline.
- With your preferred settings chosen, click on OK to save and apply the changes.
Here are a few things to note when using the consolidate function:
- You can only Create links to source dataย when the source and summary data are in two different worksheets. If both datasets are on the same worksheet, then consolidated data won’t be automatically updated.
- If you don’t check the Top Rowย and Left Columnย options, the function will combine all the cells that are positioned similarly.
Many people who use Excel or Sheets know that adding references to other cells is the most crucial part of using spreadsheet software. You may be wondering if Google Sheets can reference another sheet. Check out our article to find the answer.
How to Automate Consolidation in Excel
To automate consolidation in Excel, create a new worksheet in your workbook and head over to the Data tab. Click on the Consolidate option to open a window in the middle of the screen. Select the function you want to perform here and add the cell references. You can check the Top Rowย and Left Columnย option with the references added if your data has labels. To automate consolidation, click and enable the Create links to source dataย option. This will ensure the consolidated table’s data is updated whenever a change is made to the source table.
Frequently Asked Questions
What Is Consolidate in Excel?
The consolidate Function in Excel allows you to merge data from multiple sources, such as cell ranges or entire worksheets, into a single worksheet. In the consolidate function, you can specify the source range or worksheet and choose the consolidate function you want to perform.
Why Consolidate Data in Excel?
Using consolidate in Excel can be very beneficial if you’re looking to organize your data. The function is great if you’re looking to combine a large set of data which can be useful to get comprehensive summaries for budgeting for businesses or if you have multiple worksheets containing marks of students whom you wish to add and store in a single worksheet.
How Do You Consolidate Data in Excel From Multiple Rows?
Row consolidation can be very beneficial in Excel if multiple rows in your worksheet have the same entries. After selecting the function you want to perform on the data and the cell range, you can select the labels you wish to keep. For multiple rows, make sure to enable the Left columnย option.
Wrapping Up
Hopefully, youโve understood everything in this article and now understand how the Excel consolidate feature works. Note that I’ve got another guide on how to move rows based on cell status, too. It’s another good way to consolidate data in you worksheet.
Related:
- How to Combine Cells in Google Sheets
- How to Reference Another Sheet in Google Sheets
- Master Microsoft Excel With One of the Best Courses
- How to Use HLOOKUP in Microsoft Excel
- Excel vs Numbers, Which is the Better Spreadsheet Program?
- How To Convert Excel Files To Google Sheets
- The Ultimate Guide to Excel Version History
- How To Print Labels from Excel [Easy Guide]
- Guide: Excel IF Statements with Multiple Conditions
