The general problem is a whole subarea of programming called data cleansing which is part of a larger subarea called data integration. Avoiding these sorts of issues is likely a large part of the reason for the migration from Excel sheets and why the senior dev doesn't want to allow a field to become nullable. I don't think it's unreasonable to say that this is one of the larger sources of complexity in data migrations.
Just choosing to use NULL whenever you could is likely very much the wrong thing to do, let alone changing the data model to make yet more fields nullable. Excel has weak or no integrity checking which is likely the cause of many of these issues. The wrong thing to do is to remove the integrity checking in the new database and dump garbage into it. This just perpetuates the problem and adds significant complexity to future integrations which have to somehow deal with nonsensical data.
Some of the difference is likely due to data model mismatch. Dealing with this is largely a matter of being (intimately) familiar with both data models and knowing how to map the old one to the new one. As long as the new one is capable of capturing the old one. (If not, your team likely has a very big problem.) This can easily require doing more work than just copying columns. Darkwing gives an excellent example of this (as well as why blindly inserting NULLs is the wrong thing to do). Elaborating upon it, if the old model had a
ReceivedDate and an
InProgress bit and the new model has a
ProcessingEndTime, you will need to decide if and how to set the
ProcessingEndTime. Depending on how it's used, a reasonable (but arbitrary) choice might be to set it to be the same as the
StartDate (or shortly afterwards if that would cause problems).
However, some of the difference is likely due to data that "should" be there that is missing or corrupted. (Most likely from data entry errors or poorly handled past migrations or bugs in data processing systems.) If no one on your team anticipated this, then you (collectively) have set yourselves up to spending 20% of the time of the project being "almost" done. (That was a made-up number, but it can be far worse than that, or better. It depends on how much data is incorrect, how important it is, how complex it is, how easy it is to get involvement from those responsible for the data, and other factors.) Once you've determined that the data is "supposed to be" there but is missing. Usually you'll attempt to determine the extent of the problem by querying the old data sources. If it's dozens or hundreds of entries, then it's probably data entry errors and the customers responsible for the data should manually resolve it (i.e. tell you what the values should be.) If it's millions of entries (or a significant fraction of the data), then you may need to reconsider whether you correctly identified that it "should be" there. This might indicate a modeling error in the new system. When you ask the people using the data about the missing data, they are often somewhat aware of it and have ad-hoc ways of dealing with it.
For example, imagine an invoice that had quantities and per item totals (but not unit price), except that some of the quantities were inexplicably missing. Talking to the person who processes such invoices might produce one (or more) of the following scenarios: 1) "oh, a blank quantity means a quantity of 1", 2) "oh, I know those items go for around $1,000 so, clearly this is an order for 2", 3) "when that happens, I look up the price in this other system and divide and round", 4) "I look it up in another system", 5) "that's not real data", 6) "never seen that before".
As suggested, this can indicate some ways of automatically resolving the situation, but you have to be careful that the solution applies to all cases. It is common for other systems to be involved that can cross-check the data, and this is a good thing. However, it's often a bad thing insofar as it can be difficult to gain access to and integrate with these systems to perform the cross-checking, and it often comes to light that the systems conflict with each other not just by one missing some data. Some manual intervention is often required, and depending on the scale, may well require tooling and interfaces to be created specifically for the data cleansing task. Often what is done is the data is partially imported but rows with missing data are sent to a separate table where they can be reviewed. Often this will need to be done at an appropriate granularity for consistency in the new system (i.e. reject invoices not individual line items even if most of the line items are fine in a particular invoice) and it can lead to cascades (if I can't import a client, then I can't import any invoices for that client).