Month: March 2017

Big Excel Spreadsheet Tip: Don’t Use Smart Tags to Convert Text to Numbers

Don't do this for big spreadsheets!
Don’t do this!

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:

Read More →