Converting a massive Excel range into a Table

Hi all the Excel warriors!

Not a detailed VBA question, but directly related regardless. I have been blessed to work with a real legacy Excel monstrosity.

A table containing 250+ columns and tens of thousands of rows (plenty of outdated data to sort out). We are talking about 100+ MB file with shared access to users and a multitude of VBA-s that I only start to figure out. My first wish to start cleaning up the place would be to convert this massive range into a proper `Table` (Ctrl+T), making all sorts of stuff easier to address. Currently everything is just manual headers and plain rows.

Any tips or experience to share on this? My first concern is related to VBA functionalities that I don't fully understand yet. Is there a reason to worry that just converting the same data into a Table would mess up some references within the existing VBA code? On another note - performance, quite obviously, is an issue, especially while saving on network drive. Might converting this to Table have a possible further negative effect here? As noted, super-legacy system, but currently runs on 64bit Excel within Office365 package which was expected to blow a little life into it.

Any thoughts here are extremely welcome. Anyone else have worked with something as obscene as this?

Thanks!

It sounds like there are input operations existing with this data ... people are inputting into it .... is that the case? If not, if this is a giant storage area, then port it across to PowerPivot. I've done stuff with enormous data in PowerPivot, and linked in many additional table areas to allow the inputting of various data subsets, settings, choices, etc. The data inside PowerPivot is not editable ... but the connections to related tables, and the DAX formulas to perform database calculations, make it an extraordinarily powerful tool. Be aware that the data I was processing was in excess of 2 million rows.

Yes, that's exactly the case - people are entering data there and I think in a shared mode. But obviously that also makes it a hybrid solution, since it acts as a giant storage area too.

Could you please describe the point of PowerPivot? I was planning to connect PowerQuery to this table anyway for data analysis and perhaps extraction and while it would be better, PQ should be able to handle it without Table-formatting too. Does PowerPivot functionality somehow clearly differ from PowerQuery (sorry, never used PowerPivot, unlike PQ)? I was thinking more in the lines of adding control + data entry functionalities through Table setup perhaps, making ongoing entry work easier (and perhaps safer). But got scared that it would ruin something that is already existing if that makes some sense.

Given works are under way to replace this thing with a proper modern system, objective is to make as little fundamental changes as possible - but perhaps there are some tweaks worth doing until the modern system is available. I thought converting this massive range into a Table would be a gateway to a better way of working with the existing solution (until it's replacement).