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:

istext

Now you can easily convert the text into values by using the Paste Special > Values > Multiply combination.  Here is how…

DOWNLOAD EXCEL WORKBOOK

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

HELPFUL RESOURCE:

exceltv

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Related Excel Tutorials