First off, I apologize that I'm writing a bit of a novel here. My understanding of your question is that you're looking for architectural guidance on the best way to go, and that's not a quick answer.
If, however, I've misinterpreted your intent and you are actually just looking for how to code up an excel file as a database, there is already a lot of articles online that you can google.
Regarding your architectural question...it is really going to come down to choosing the best trade-offs for what you're building. I will give you some pointers that I have learned and hopefully it is helpful to you and others in the community.
I would be very hard pressed to advise that you use an excel file as a database.
While it might seem like the most straight forward solution, the trade-offs here are very costly in debugging file locking issues and dealing with excel specific errors, it becomes a death by a thousand cuts. It is certainly possible, but this is a trap that I personally fell into early in my career.
Here's is a link to some descriptions of the problems that you'd have with an excel file database and here is a 2nd link.
To paraphrase your question, it sounds like you're developing a personal ETL application for improving your productivity and your company's metrics. Spreadsheets come into your e-mail inbox and transformed versions of the spreadsheets go out of your e-mail outbox. You are wanting to look at the departments' data from a historical and comparative perspective. I have done this many times in the past as well and it is a very reasonable goal.
The best way that I have found to do this is to use a SQL Server database. You can start this out in phases of minimal viable product to do this in small easy chunks.
Phase I:
Download and install SQL Server 2016 Express free. Make sure
to install localdb when you install SQL Server 2016. See the localdb
instructions for more information.
Create the localdb instance on the command line. 
Connect to the new localdb instance in SQL Server
Create a new Database that you'll use for importing the data. Give it a name like "ReportData" 
Import the excel files received from the variety of businesses into the new database. This stackoverflow answer gives a great description of how to do it. Here is an alternate example.
If you get any error messages about drivers you may need to download the correct drivers.
Develop your SQL queries that you want to use. For simplicity, I'm just showing a basic select statement here, but you can build some sophisticated SQL queries for aggregating the data in this step.
Export the data from the excel file into a CSV file or an excel file. You do this by right clicking in the "Results" area and selecting "Save Results As..." 
Manually copy and paste the resulting values into the excel templates that you would like.
Note step 9 will be automated soon, but it is better for now to understand your domain objects and be thinking about the business logic that you're building in a quick iterative manner.
Phase II:
Create a new Console application in Visual Studio that will transform the data from the database into an Excel file output. The most powerful way to do this is to use EPPlus. Here is a detailed explanation on how to do this.
Note, when you run the source code from the detailed explanation link, you need to change the output path first, for example to c:\temp. Note also that there are plenty of other Excel spreadsheet helper packages out there, so feel free to look around at other packages as well. EPPlus is simply one that I have been successful with in my projects. This Console application will be querying your SQL Server database using the queries that you built in step 7 above.
Phase III:
In time, you many find that co-workers and managers within your company want to start accessing your data directly through a web page...
At a high level, the steps you would take are:
- Backup the database and restore it onto a server.
- Implement a simple MVC application
- Perhaps even build web pages to allow users to import excel so that they don't need to e-mail them to you any longer.
An additional note, there are Enterprise level ETL and reporting tools out there as well, such as SSIS/SSRS, etc that you could look into if you're looking for a more sophisticated tool set, but I didn't get that impression with your question.
I hope that this answer helps and isn't too long winded. Please let me know if any of the steps are unclear, I know it's a lot of information in one post.
