Download ArticleDownload ArticleThis article was co-authored by How.com.vn staff writer, Travis Boylls. Travis Boylls is a Technology Writer and Editor for How.com.vn. Travis has experience writing technology-related articles, providing software customer service, and in graphic design. He specializes in Windows, macOS, Android, iOS, and Linux platforms. He studied graphic design at Pikes Peak Community College.
This article has been viewed 539,868 times.
Learn more...
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 How.com.vn teaches you how to consolidate data from different files or worksheets in Excel.
Steps
Download Article
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.
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
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.
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.
Click Data. It's in the menu bar at the top of Excel. This displays the Data panel at the top of the page.
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.
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.
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] 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.
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").
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.
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.
Click the checkboxnext 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]
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
- QuestionWhat about recording a macro to consolidate data on a single worksheet?
Community AnswerYou can record a macro to consolidate data, but it is always better option to use consolidation or pivot table options.
Video
Tips
References
About This Article
1. Open all files and sheets with data you want to consolidate.
2. Ensure the data on each sheet is listed in a consistent format.
3. Open a new file or sheet for your consolidated data.
4. Click the starting cell for your consolidated data.
5. Click Data in the menu bar at the top.
6. Click the Consolidate icon with two cells with an arrow pointing to a new third cell.
7. Use the drop-down menu below "Function" to select a method of consolidation.
8. Click the arrow icon or Browse next to the "References" bar.
9. Select the sheet or file with data you want to consolidate.
10. Click and drag to highlight the data you want to consolidate.
11. Click Add to add the reference.
12. Repeat steps 6 through 12 for all other sheets and files you want to consolidate.
12. Select Top row and/or Left column to indicate which row or column contains the labels.
13. Click Ok.
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
