Free Practice WorkbookMany times you would have received data from your IT system which is formatted wrong! Well a gazillion times if you work in a mid sized firm 🙂
When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as text rather than a value.
Tip 1: You can press F2 in the cell to see why it is in text format.
Tip 2: You can also press CTRL+1 (which brings up the Format Cells dialogue box) to confirm that it is in text:
Tip 3: You can also use the ISTEXT function to confirm a cell`s format:
Now you can easily convert the text into values by using the Paste Special > Values > Multiply combination. Here is how…
Download excel workbookTurn-text-to-values.xlsx
STEP 1: Enter the number 1 in an empty cell
STEP 2: Copy that cell
STEP 3: Select the data range, Right Click and select Paste Special
STEP 4: Select Values & Multiply and press OK
Your data will be transformed into values:
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
30 Days - Full Access 
