How to Consolidate in Excel

Last Updated: July 20, 2019

Microsoft Office Excel comes with several features for customizing tables and charts full of important data. Using the Consolidate tool, you can combine and summarize data from multiple files or sheets. This wikiHow teaches you how to consolidate data from different files or worksheets in Excel.

Steps

1
Open the worksheets you want to consolidate. When you open Microsoft Excel, you can click recent files you have worked on on the front page. If you don't see the files you want to consolidate, click Open in the sidebar to the left. Then click Browse. Navigate to the files you want to consolidate and open them.
2
Ensure the data in each spreadsheet is listed in a consistent format. Make sure the labels on each spreadsheet are listed in a consistent format. For example, if you have the date listed in the top row, and the product listed in the left column, make sure this is the same format for each spreadsheet. Make sure the rows and columns are in the same location for each spreadsheet. Delete any blank rows or columns in the spreadsheets.

Advertisement
3
Open a new Excel file or sheet. This will be the master spreadsheet that will contain all the consolidated information. If you are consolidating data from different files, click File and then New to open a new spreadsheet. If you are consolidating sheets from the same file, click the plus icon (+) to the right of all the sheets in the lower-left corner to create a new sheet within the file.
4
Click the cell that you want the consolidated rows and/or columns to start. Click the row and/or column that is consistent with where the tables are located in the other spreadsheets.
5
Click Data. It's in the menu bar at the top of Excel. This displays the Data panel at the top of the page.
6
Click the Consolidate icon. It's the icon that resembles two sheets with a blue arrow in between them pointing to a blue third sheet. This opens the consolidate menu.
- On older version of Excel, the icon resembles a column of cells with a blue arrow pointing to a new cell.
7
Select a function. Use the drop-down menu below "Function" to select the method that the data will be consolidated. For example, if you want to add all the data together, select Sum. You can also select Count, Average, Min, Max and other functions.
8
Select a reference source. This is the first spreadsheet that you want to consolidate. If you want to consolidate sheets within the same file, click the arrow icon to the right of the bar below "References".^{[1]
X
Research source} If you want to consolidate data from a separate file, click Browse and then select a file with data you want to consolidate and click Open.
9
Highlight the data you want to consolidate. If you are consolidating data from a separate sheet within the same file, click and drag to highlight data and labels you want to consolidate. If you are consolidating information from a separate file, open or click over to that file and click and drag to highlight the data and labels you want to consolidate. This will display the file or sheet name followed by an exclamation mark followed by the row and column range in the references column (For example: "Q1SalesSheet!$A$2:$F$5").
10
Click Add. It's to the right of the "All References" list box. This adds the reference and range you select to the list of references. Repeat steps 6 through 10 for all sheets and files you want to consolidate.
11
Select the row or column for the labels. Click one or both checkboxes next to "Top row" and/or "Left column" to select which row or column contains the labels.
- If you don't check either box, Excel will consolidate all cells in the same position.
12
Click the checkbox
next to "Create links to sources" (optional). By checking this option, the consolidated data will be updated automatically if one of the source references is updated.^{[2]
X
Research source}
13
Click Ok. This adds the consolidated data to the master spreadsheet. You can see the source data from the consolidated data by clicking the plus icons (+) to the left of the cell numbers on the left side.

Community Q&A

Question

What about recording a macro to consolidate data on a single worksheet?

Community Answer

You can record a macro to consolidate data, but it is always better option to use consolidation or pivot table options.