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

Excel Table: Styles There are lots of different Table Styles that you can choose from to spice up your Excel Table.  You can also customize your own style if you don't like any of the default Excel styles.To access the Table Styles, you simply need to click inside your Table and then the Table T...
Text to Columns – Emails If you have a data set with text consisting of names and email addresses that are wrapped inside a parenthesis, like:John ( ...then you can use the Text to Columns feature in Excel to take out the email addresses and put them in a separate ...
Convert Values to Dates Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.One of the cool uses of Flash Fill is convert your values into Excel dates a...
Autosum an Array of Data in Excel When you have an array of data in Excel with Totals at the bottom and to the right of the data, you can quickly fill in the Totals with the Autosum button.STEP 1: Highlight your data including the "Totals" row and column;STEP 2: Click the Autosum button (under the Home or...