How to Find and Change Numbers Stored as Text in Excel

Whenever a client or a colleague sends me an Excel workbook I like to inspect it, to be sure that the “numbers are really numbers.” I will not get accurate results when I SUM them if there are some cells that appear to contain numbers, but really display a “number stored as text.”

Prevent GIGO

Prevent GIGO

This is an example of GIGO – “Garbage in delivers Garbage Out!” I take steps to prevent an outbreak of GIGO in my Excel worksheets.

Looking for Numbers Stored as Text

As a best practice, I use both the COUNT() Function and the COUNTA() function to see if there is a difference between the number of cells with numbers (the COUNT Function) and the number of non-blank cells (the COUNTA Function). In this example, I was expecting to see 31 cells with numbers in the range of sales. There were on 27. So, I need to find those 4 cells that contain “numbers stored as text.” 

If you have a keen eye – and some time to spare – you can look for cells with a little Green triangle in the upper left corner. Click on the message next to these “marked” cells and read the error message. Excel will offer to correct the error – one cell at a time. That is not an efficient way to proceed!

Use the ISTEXT() or ISNUMBER() Functions

An alternative way to find the cells that contain text stored as numbers is to use one of the “logical functions” ISTEXT or ISNUMBER. Each returns either TRUE or FALSE when evaluating a cell. You can then sort the data to group the TRUE results together and then change them – again, one at a time. This is also not the best way to make the changes.

Use Conditional Formatting to Highlight Cells with Number Stored as Text

I like to use the ISTEXT() Function as a “Rule” to apply special formatting to the cells that evaluate as TRUE with Conditional Formatting. Be sure that you select the cells that you want to evaluate before applying the Conditional Formatting.

Use Paste Special – Add

The quickest way to change numbers stored as text into real numbers is to use Paste Special – Add. First, select and copy a blank cell. Next, select your data range – in this case the cells with “Sales.” Then, right click the selection and choose Paste Special. In the dialog box select “Add.” Now – instantly – all of the cells with numbers stored as text have been changed into real numbers. You have stamped out GIGO!

Learn how to “Master Excel in Minutes – Not Months!”

Follow this link to watch this video in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels

Save Time and Maintain Consistency When Entering Data

This is every company’s nightmare: You have a list of Customer Names that are entered in multiple worksheets by multiple employees. The (nightmare) result – you have multiple misspellings of the Customer Names! This is GIGO (“Garbage In, Garbage Out”). This is a mess!

This is preventable!

In this Excel Training Video I demonstrate three tips to save you time and help you to maintain consistency when entering long text labels into your worksheets. The Tips:

  1. Use Data Validation – Choose from a List
  2. Use a Named Constant  – you type =”Shortcut” and get the Customer Name spelled out correctly
  3. Use MS Office’s AutoCorrect Option – Create your “Replace and Replace With” in Excel and you can use it in Word and PowerPoint

Here are the steps to follow in this lesson:

  1. Enter the list of Customer Names in a worksheet. Highlight the list and type a NAME for this range into the Name Box. Remember to begin your Name with a letter and you can not use a Space in the Name.
  2. Tip # 1 – Use Data Validation – Allow – List. You can watch my lesson on Data Validation.
  3. Tip # 2 – Create a Named Constant. You can watch my lesson on Named Cells, Ranges & Constants.
  4. Tip # 3 – Use MS Office’s AutoCorrect Option – Go to Tools – AutoCorrect Options in any MS Office Program. For the “Replace” entry, type in your “Shortcut.” I like to make my 1st character an”_” so that it is easy to find, edit or replace. For the “Replace with” type in the full Customer Name. Remember to ht the SPACEBAR when you use this in Word & PowerPoint!

It is easy to find the Excel Training Video you want to view – Search my Index of Excel Topics

New! Danny’s DVD Training Series, “The 50 Best Tips for …” is now available at the online store for The Company Rocks

2 reasons to format cells before you enter data in Excel

Stop! Think! Consider! Develop a plan!

Before you enter any data in your spreadsheets.

Ask yourself questions about your data.  Why?

  1. To save yourself time
  2. To maintain consistency in data entries
  3. To prevent GIGO  (Garbage in equals Garbage out)
50 Best Tips for Excel 2007

Excel Tips

In this video lesson I demonstrate the advantages you will obtain when you:

  1. Stop – before you start to enter data
  2. Pre-plan your data list
  3. Format your data fields / columns BEFORE you make your 1st entry

Here are the steps to follow in this video lesson:

  1. Set up and Format your Column / Field Headers for each data field.
  2. For each Column / Field Header, select the columns and format the cells (Ctrl+1) BEFORE you enter any data.
  3. On the NUMBER tab, choose “Special” to select formats for Social Security #, Phone #, and Zip Code

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Find the Free Excel Training Video that you want – Click here to go to my Index of Excel Video Lessons

3 ways to minimize data entry errors in Excel

Excel is a great program for organizing and analyzing the information that you have in your system. But what if you have lots of data entry errors and inconsistencies?

In this Excel Training Video, I show you 3 ways to minimize data entry errors. You will learn how to employ Excel’s Data Validation feature to prevent accidental and intentional data entry errors!

These are the steps for this Excel Video Training Lesson:

  1. Choose the cell or range of cells that you want to apply Data Validation to.
  2. Select Data, Validation and start with the “Settings” tab.
  3. To control a range of dates, select “Date” in the Allow drop-down menu. Choose the range of dates that you wish to control. In this video I chose <= =TODAY()
  4. To create a “Pick from list” validation: 1st step is to create the list (in a separate workbook) and then “Name” the list. In the Allow menu, choose “List” and in the Source, use the F3 shortcut to paste in your named list.
  5. To prevent Duplicate entries, in the Allow menu choose “Custom” – This allows you to write a Formula. E.g. = COUNTIF($E$ :$E$30,E2)=1 where E2 is the 1st cell in your Validation range.
  6. In the “Error Alert” tab, you can pick from 3 different “Styles. Only the “Stop” style will prevent a data entry that violates your Data Validation rules. The other 2 styles are warnings and information – you can over-ride the validation rules with either of these 2 styles.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.