Conditional formatting

Posted on 2011-10-07
Last Modified: 2012-05-12
We have a spread sheet with a column that contains numbers in the format of 0000000.000. 7 numbers dot 3 numbers. some columns erroneously contain excess digits. For example 00000000.000 (8 numbers dot 3 numbers) or 7 numbers dot four. Is there any way to use conditional formatting-Hightlight cell rules to expose which cells contain excessive digits.
Question by:Brazilee
    LVL 18

    Expert Comment

    by:Richard Daneke
    Yes.   Add a new rule to highlights cells greater than the maximum of 7 digits (9999999.999).
    LVL 23

    Expert Comment


    For the examples with greater than 4 numbers after the decimal point, instead of using conditional formatting just select the row and format the cells to the format.

    This article gives a good run down on the options available when formatting numbers

    No formatting will help where the number of digits before the decimal point is greater that 7 to use conditional formatting
    1/ Select the columns
    2/ Select the Home tab
    3/ Select Conditional Formatting on the Styles Panel
    4/ Select Highlight cells rules
    5/ Select more rules
            Format Only Cells That Contain
            Cell Value
            greater than or equal to

    LVL 9

    Accepted Solution

    This doesn't use conditional formatting, but it will give you the information you're looking for.
    You can add these formulas into two new empty columns in your spreadsheet:

    This formula will return a True if the number of digits to the left of the decimal (for the value in A1) is more than 7 digits:

    This second formula will return a True if the number of digits to the right of the decimal (for the value in A1) is more than 3:

    Place these on row 1 and copy down.

    You may also be able to use these to apply conditional formatting, but I didn't give that a try.

    Let me know if this helps, or if you have any questions on how to use these.


    Author Comment

    Hello Sah18;

    This looks to be the right track. If my column starts at B2 would i modify the formula like this.

    I tried this and it returns a result of "false" which I guess its supposed to. If I modify
    the the cell to exceed the limit, it returns a "value" result. is this correct?

    thank you
    LVL 18

    Assisted Solution

    by:Richard Daneke
    The formulas do work and can be used in the conditional formatting rules.
    Details are provided in the attached example.
    LVL 18

    Expert Comment

    by:Richard Daneke
    In the example, the conditional formatting is applied in column A.
    The two formulas are shown in column B and C for example use only.  They can be deleted and the conditional formatting will still work.
    My first answer did not look close enough to see that you need a upper limit and a decimal count test.
    LVL 9

    Assisted Solution

    Hi Lee,

    Yes, just replace A1 with whatever your cell is that has the value you wish to test (B2, if it is in the 2nd row, 2nd column).  "value", though, means you have something wrong in the formula, so you may want to make certain you don't have any typos there.

    If you're able to, feel free to attach a copy of your actual spreadsheet (or an abbreviated version of it), and I can check what you have entered for your formulas.  I could also see then if something else unexpected is tripping up the formula.

    Take care,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now