So I have been given a spreadsheet that has been generated from another system and is formatted already. The really annoying thing is that sub totals have been added and alot of cells are merged and the whole thing is generally large and difficult to read.

Because of the formatting, I cannot get this into a pivot table because I have cells that are not the same size or are merged..etc.

Anyway, what I want to know is there any easy automatic way of getting excel to regenerate the spreadsheet in such a way I can get it into a pivot table or is there any pivot table alternative.