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://excelribbon.tips.net/C0786_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 can set up a conditional format that will verify if the information placed in a cell is of the data type you want. Follow these steps:
Figure 1. The New Formatting Rule dialog box.
=ISTEXT(A1) =ISNUMBER(A1)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7073) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Conditional Format that Checks for Data Type.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
After you've applied a conditional format to a cell, you may have a need to later delete that format so that the cell is ...
Discover MoreWhen you paste information into a row that is conditionally formatted, you may end up messing up the rules applied to ...
Discover MoreConditional formatting can be a great tool to get your data looking just the way you need. However, when you sort data ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-02-28 13:42:04
Arthur Menu
I use conditional formatting to set up a rule that turns a selected cell green if a certain formula is true and red if the formula is false for a range of data. My workbook has 52 worksheets where I have set up this rule. It usually works but on occasion when I start inputting data into one of the 52 worksheets in which I have not entered data up to that point, Excel does not apply the rule. To activate the rule I select the cell in which I have set up the rule, open the conditional formatting tab and manage the rule. I have to edit the rule. I don't need to change anything, just OK what I have already set up. I don't know why Excel on occasion deactivates a rule that I have set up. This doesn't happen all the time. Most often the rule is active on any worksheet. But not always and I don't know why. Is there any quick and simple way to get Excel to check and make sure that all instances of the conditional formatting are active?
2017-03-15 10:48:28
Jack
A handy add on to this is if you want to apply conditional formatting to a whole row based on the result of a test on a single cell in that row, you can put a $ in front of the Column letter e.g. =ISNUMBER($A1). Then if the cell in column A is a number the whole row get formatted
2017-01-14 05:34:37
Barry
There are some quirks in MS Excel that may defeat this simple test (=ISTEXT(A1), so the input looks like a number but is actually text.
A numeric can be entered as text and the ISTEXT formula will accept it as text (likwise the ISNUMBER function in reverse). This in itself is OK but if the value in the cell is used in a formula (and not all functions will do this) e.g. =A1*2 then Excel will interpret the text as its numerical equivalent, and not give the #VALUE error.
Numbers can be entered as text either by formatting the cell as "Text" or preceding the entry with an apostrophe " ' ".
The inconsistent treatment can be annoying but it seems simple arithmetic operators (e.g. + - * /) will convert the text to numbers but other more complex functions do not. I have tested this exhaustively because there are so many functions in Excel. But it is worth bearing this mind when devising your spreadsheet and input validation
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments