Written by Allen Wyatt (last updated January 4, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
Joshua is trying to establish a conditional format that will alert a user that text data has been entered into a cell intended for numerical data or when numerical data has been input into a cell intended for text data.
A conditional format can be used to draw attention to when an improper value (text or numeric) has been entered in a cell, but a more robust approach might be to prohibit the improper value from being entered in the first place. This can be done with the data validation capabilities of Excel. These capabilities have been discussed, in detail, in other ExcelTips; more information can be found here:
http://excel.tips.net/E165_Data_Validation.html
Using data validation, you can specify the type and range of data permitted in a cell, along with how stringently you want that specification followed. If you prefer to not use data validation for some reason, you could set up a conditional format that would verify if the information placed in a cell is of the data type you want. Follow these steps:
Figure 1. The Conditional Formatting dialog box.
=ISTEXT(A1) =ISNUMBER(A1)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6906) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Conditional Format that Checks for Data Type.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Conditional formatting is a great feature in Excel. Unfortunately, you can't sort or filter by the results of that ...
Discover MoreConditional formatting is very powerful, but at some point you may want to make the formatting "unconditional." In other ...
Discover MoreConditional formatting is a great tool. You may need to use this tool to tell the difference between cells that are empty ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments