Select VALUE in the list to bring up the functions dialog box. The cells selected will now display numbers rather than formulas. This would convert the text-formatted numbers to number format instantly and now the SUM function-working fine and showing the accurate result.
Here I am using Paste Special methodConsider the same data that I have used in the previous example.
Convert formulas to values in Excel. After that the difference is divided by the original_value. All you need to do. Another easy way to convert text to numbers in Excel is by converting over an entire column of values at once.