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

Show The Percent of Column Total With Excel Pivot ... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF COLUMN TOTAL calculation.This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, ...
Add Leading Zeros in Excel Do you have a lot of numbers with an uneven number of digits in your Excel list?Do you want to make them uniform by adding leading zeros to them?Well, it's a pain to add zeros in front of them one by one!Thankfully, Excel allows you to add leading zeros with one singl...
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...
Create a Named Range in Excel Whenever I work with spreadsheets, there's no escaping the fact that I have to work with a lot of ranges.Some of these ranges I have to reuse time and time again to create different calculations!Good thing there are Named Ranges in Excel!By using a Named Range, you ca...