I often work with really big Excel spreadsheets (>500,000 rows) to do various types of data analysis. And often I’m grabbing data from databases (how else are you going to get such big spreadsheets???) And a common thing that can happen, is that numbers will be copied in as being in a text format, and many math and lookup formulas won’t work on them. One tempting way to convert these is to use a “Smart Tag” where, when you select all the numbers that are being treated as text, you get an ! in a diamond, that you can click on, and it gives you the “nice” option to just “Convert to Number”. If your spreadsheet is over 10,000 rows, DON’T DO THIS. It will take forever to complete… (ok, not really forever, but possibly several days to complete.) Instead you should do the following:
- Temporarily turn off the Excel options for Automatic Formula calculations (change it to Manual in the Formula Options).
- Make a new column in the table (assuming you are using tables, but this same concept works for old fashioned sheets), called “temp”
- Use a formula in this column of =value(original cell). If you are using tables, you should only need to do this once, and it will autofill.
- Copy the entire range of new values into the clipboard.
- Use Paste Values (Makes sure you do VALUES, not just a normal paste) back over the original data
- Turn back on Formula calculations
To illustrate how much of a difference this makes. I was working with a spreadsheet yesterday on a server with 24 cores (which Excel views as 48 processors, probably because of hyper-threading). I chose about 300,000 rows that I wanted to convert to text, and forgetting the pain that I have been through in the past, I chose to use the smart tag…. It started to go to a crawl. I was about done for the evening, so I just decided to let it run overnight. When I came in the next day, it hadn’t finished all of the changes…
But when I did my technique, my whole process took less than 5 minutes…. In fact, it took more time to write this blog article. But if writing this article can save someone the headache, and maybe help me to remember also, so I don’t stupidly use the smart tags again, it will be worth it!