From a blank workbook, select Data, Get Data, From File, From Workbook. Save the workbook with multiple worksheets on your local hard drive. (Note that Power Query debuted in Windows versions of Excel 2016 and is currently only available for Windows versions of Excel, but Microsoft is working on adding it to Excel for Mac.) The coworker’s workbook remains unchanged, but you’ll have a worksheet that consolidates all data from all of the worksheets. The technique uses Power Query, which means you can perform the steps in a blank workbook and then refresh that workbook the next time you receive the source data from elsewhere, like a coworker. If you have some title cells in the top rows and the headings are in row 4 or 5, you’ll have to take the time to create a named range on each worksheet that starts at the headings and includes all rows of data. The trick will be simpler if the headings appear in row 1 of each worksheet. There are a few requirements for this technique: Each worksheet must have the same columns in the same sequence. I learned this trick from Australian author Matt Allington. Combining worksheets is simple, but you would never accidentally discover the steps of this technique.
