Learn how to a build a cloud-first strategyRegister Now


Conditional formatting

Posted on 2011-10-07
Medium Priority
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 19

Expert Comment

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

Expert Comment

by:Michael Fowler
ID: 36935438

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


Accepted Solution

sah18 earned 1336 total points
ID: 36935830
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.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 36939103
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 19

Assisted Solution

by:Richard Daneke
Richard Daneke earned 664 total points
ID: 36939173
The formulas do work and can be used in the conditional formatting rules.
Details are provided in the attached example.
LVL 19

Expert Comment

by:Richard Daneke
ID: 36939177
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.

Assisted Solution

sah18 earned 1336 total points
ID: 36942183
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,

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

810 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