Unfortunately it happens way too often – we receive a worksheet containing numbers but the cells were formatted as text.
Too bad we need them as numbers!
So, what are our choices?
In versions of Excel prior to 2007 the search for a solution would lead you to multiplying an entire set of cells by 1 via the Paste Special feature.
It was a better alternative than retyping all those numbers, but it still didn’t feel very efficient.
With the newest versions of Excel, we’ve got a much faster solution to our dilemma.
When cells containing numbers are set to be text, the cell will be flagged as having an potential error.
In addition, when that cell (or cells) is selected the Error button will appear.
Click #1: Click the Error button
Click #2: Choose “Convert to Number“
This will convert the numerical data in all selected cells to numbers… no multiplying… no paste special… just a quick 2 clicks and you’re back in business.