Excel Data Visualization

We need data from number of sheets to be checked, collated and transformed to provide analysis, insights and illustrations for our client. There are 70+ locations each needing separate reports. Plus each location is in an area/building (7-10) and reports for these areas are also need to show the aggregate of the locations within this space. See video guide here https://youtu.be/4YzKEDkTccQ plus the spreadsheet here: https://drive.google.com/drive/folders/1AXdjmeBqZ39LeqvExBhaNywU_Q-GRuTX?usp=sharing