You have two approaches if you want to restrict yourself to Microsoft Office.
The "traditional" approach is to create a Microsoft Access database that links to all the spreadsheets. You then create a View that combines all of the data.
If, however, you are using a new(ish) version of Excel and are able to install addins, you can also do this very easily using Microsoft's free PowerQuery addin.
With powerquery, you create a merge query that pulls all the data from the various spreadsheets and merges them together, this can then be further filtered, cleansed, transformed and output as an Excel "master" table.