A very basic use of “Force Convert to Values” is to force convert the content of all cells, no matter formulas or values, into values. Rephrasing the statement: this function tells Excel to forget about whatever is in the cells, no matter formula or value, and re-consider the “value” of cells as the content.
After applying this function to a range, there will be no formula in that range.
Very often, more often than everyone or Microsoft could imagine, when you import data from an external source into Excel, the numeric data (including dates) become “dead”.
Below is a demonstration of the problem :
A sample file is including in the Excel Power Expander software, in the file: “Force Convert To Values.xls” in the “Examples” folder.
Five columns of data (Columns A to E) were import from an external source (a text file or download from SQL Database), as shown below. You don’t like the number format of Column C, D and E. You prefer them to have a comma separator and with two decimal places. Also, you don’t like the format of the dates in column A. You want them to be in the default date format of your operating system.
With Columns C, D and E selected, you choose to set the number format of the cells. As shown in the dialogbox below.
Then, however, there is NO CHANGE AT ALL on the spreadsheet!! The numbers look exactly the same as before! The number format has not been updated at all! (Someone might call the IT help desk now. But the IT help desk won’t be able to help you for sure.)
For no reason, there is no convenient solution to this problem. However, with “Force Convert to Values”, you can fix the spreadsheet in one mouse click.
With columns C, D and E still selected, run “Force Convert to Values” from the “Expander” menu. See the result!! (Figure below.) It’s done.
We can go further by fixing the dates in column A as well. You can have all your data selected, and run “Force Convert to Values” from the “Expander” menu. The result is shown in the figure below.