Many 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.

f2 cell

Tip 2: You can also press CTRL+1 (which brings up the Format Cells dialogue box) to confirm that it is in text:

format cells

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…


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

paste special values

Your data will be transformed into values:

correct values

Turn text to values




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Running Total In % with an Excel Pivot Table A Running Total In is the accumulation of values over a certain period, like days, month or years.  It is sometimes referred to as the Year to Date analysis. A Running Total In takes one period's values, then adds a second period, then a third period...and so on, to give the a...
Autofill Formulas in an Excel Table One of the advantages of using an Excel Table is the ability to autofill a formula all the way down your data without having to copy and paste. When you write a formula anywhere in your Excel Table, it will automatically fill down and up within that column. As you add extra...
Fix Excel Hyperlinks to a Named Range Hyperlinks in Excel must be one of the funkiest features that I love playing around with! They allow you to create interactive buttons within Excel (without the need to create a Macro) and you can make them take you to any cell or range within your Excel worksheet. One shor...
Go To Blanks The Go To Special tool within Excel is a must for any serious Excel user as it has an array of useful spreadsheet formatting and clean up tools. One that I use all the time is the Go To Special > Blanks.  This allows you to delete multiple blank rows/column within seconds. ...