Forcing numeric data type in Excel

Excel data types are particularly significant in the following situations:

• Formulae that perform comparisons such as Vlookup
• Using Excel as a data source in a join query when you have poor control over type casts e.g. while using Microsoft Query

You can set the formats of cells, but this won't update until you actually edit the cells. If you have a lot of cells it's not feasible to edit them all individually.

I find the quickest way to force the conversion of text to number is to do the following:

1. Enter 1 in a cell
2. Copy the cell
3. Paste special into the range of cells you want to convert
4. Specify Value and Multiply

The cells you selected will be converted to numeric type.

Numeric is the easiest as Paste Special has number-specific options. For other data types I think you have to create a copy of the cells using a casting formula and use the calculated values instead of the originals. If I find any faster ways for non-numbers I will update this post.