This will be a nightmare to connect 5,000 worksheets (if this is even possible), I'm assuming that your Emp ID field is the primary key.
I'd suggest breaking off the benchmark (is this a KPI ?) into another sheet if this is fixed, otherwise you could just add the column to the Actual table as pictured.
Doing some quick calcs, the maximum row limit in Excel is 1,048,576 rows. If you put all of your staff in one worksheet (and had 7 day operations) you would fill the worksheet every 29 days.
Either break this up by Employee ID groups (e.g. Emp ID 1000-1500), or by their natural work groups.
As for databases, any of them will handle this data and you would lose the restriction on row limits.
It is indeed a nightmare! To reply to your query,Unit is a KPI. And there are about 20-30 KPIs. Against each KPI we have their respective benchmarks in another Worksheet. So I am not sure whether combining them into a single Worksheet would be a feasible option. So the other option I have is if I simply uploaded all these Worksheets into an Access database. Will that help? And after uploading it into the database, should I create a Union while connecting the Actuals and Benchmark data? Because a Join doesn't seem to work.
If you are going to the bother of creating an access database, you could consider including the KPI in the same dataset so that you don't have to do any joins, but don't let that stop you - either method will work fine for this size dataset.
The union will be on employee id in both tables in Access.
Thanks so much for your help. I created two different tables without any PKs in Access. Post that, while uploading on Tableau I used the following Custom Query:
Select * from Actuals
Select * from Benchmarks
This seem to work well on a small dataset. Let's see how it works on a larger dataset.