I've got a few massive spreadsheets (think millions of cells not thousands). These have text in, numbers stored as text, numbers stored to 1dp, numbers stored to 2dp, dates, times and all manner of other stuff.
Is there any way of highlighting what formatting is in use in a cell, to compare sheets of similar looking data? I know you can highlight numbers stored as text using the error checking options but can I tell the difference between 0.9, 0.85 (1dp), 0.90 and 0.9 (stored as text)?
using =Sheet1 A1 = Sheet2 A1 etc isn't descriminating enough
Ideally I either need to output these differences in conditional formatting (ie. Numbers stored to 1dp in blue) or spitting out a numeric value for the formatting that I can play with in a function. (I know you can read cell colours with some VBA code).
Only question would be how it deals with custom categories.
If i was dealing with millions of cells though i would be tempted to go the vba route from the outset. Otherwise you would need to set auto calc on and off as needed which is a pain.