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

Row Differences If you have two rows that you want to compare, for example, sale amount versus amount paid, you can easily filter the differences between these two columns by going in to the Find & Select > Go To Special > Row Differences. Once the cells have been identified, you ca...
Remove Duplicates in an Excel Table When you have duplicates values within your Excel Table there is a quick and easy way to remove those values. The duplicate values could be all over your Excel Table and sometimes it takes valuable time trying to locate those duplicates and then deleting them. Not to worry,...
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...
Excel’s Fill Handle Tips This post was inspired by Mr.Excel's new book MrExcel XL: 40 Greatest Excel Tips of All Time and in particular the 3rd tip that I read called The Fill Handle Does Know 1,2,3... Excel knows to fill down/right when you are working with dates, days, months, years and even quarter...