Excel - Match Data from Multiple Sheets using the Consolidate Feature - Episode 929

Microsoft Excel Tutorial - Match Data from Multiple Sheets using the Consolidate Feature. Welcome back to the MrExcel netcast! In this week's video, we'll be discussing how to match up data from two different worksheets in Excel. Often times, we have one field in common between the two sheets, such as "CUSTOMER", but the lists of customers may not be the same on both sheets. So, how do we consolidate this data efficiently and accurately? Well, there's a feature that has been around since the early days of Excel that can help us with this - the Consolidate feature. To access the Consolidate feature, go to the DATA tab and click on "Consolidate". It's important to start in a blank section of the spreadsheet, as this is where the output report will go. Next, we'll need to build references to both sets of data. The first reference will be to the "FORECAST" sheet, and the second reference will be to the "ORDERS" tab. Make sure that both references have the same left column, in this case, "CUSTOMER". Then, check the box for "USE LABELS IN THE TOP ROW AND LEFT COLUMN" and click OK. In just a few simple steps, we now have a consolidated list of customers from both sheets. And the best part? The list is in the exact same order that we added the data in - "FORECAST" first, followed by "ORDERS", "COST", and "GROSS PROFIT". However, there may be a few annoyances that we need to address, such as the missing "CUSTOMER" heading and the blank cells in the data. But don't worry, we have a solution for that too! To add the "CUSTOMER" heading, simply select all of the cells in the consolidated data and use the "GO TO SPECIAL" feature. In Excel 2007, you can find this under the "FIND & SELECT" menu. Then, select the blank cells within the selection and type in "0". This will fill in all of the blank cells with a "0". And just like that, our consolidated data is complete and accurate. The Consolidate feature may not be the most high-tech or flashy feature in Excel, but it has been around for a long time and is often underutilized. So, next time you need to consolidate data from multiple worksheets, give the Consolidate feature a try. Thank you for watching this week's netcast from MrExcel, and we'll see you next time for more Excel tips and tricks! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-... Yet another solution to matching data from two different worksheets...the Data Consolidate command. Episode 929 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book! Table of Contents: (00:00) Introduction to matching data from 2 different worksheets (00:20) Identifying the common field between the two worksheets (00:30) Using the CONSOLIDATE feature to solve the problem (00:42) Accessing the CONSOLIDATE feature on the DATA tab (00:52) Selecting a blank section of the spreadsheet for the output report (01:02) Building references to both sets of data (01:21) Consolidating the data without using the multiple consolidation range in a pivot table (01:42) Addressing annoyances in the pivot table (02:04) Using the GO TO SPECIAL function to fill in blank cells with 0s (02:26) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Combining data from multiple sheets in Excel Excel data consolidation feature Excel tips for merging data from different worksheets How to fill blank cells with 0 in Excel How to use the CONSOLIDATE feature in Excel Matching data using the CONSOLIDATE feature in Excel Organizing data from multiple sheets in Excel Quick and easy way to merge data in Excel using CONSOLIDATE Solving data matching issues in Excel with CONSOLIDATE feature Tips for efficient data consolidation in Excel Using the DATA tab in Excel for consolidating data YouTube tutorial on matching data from different worksheets Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...