Format Percentage Cells, Restrict Values >100.001

Posted on 2011-10-18
Last Modified: 2012-05-12

I have a percentage dilemma with Excel: I have ranges formatted as percentages and I'm tring to restrict the user from entering values greater than 100.001. Using Excel's Data Validation feature, the closest I can come to it and still force users to enter numbers and not text is:


Although this looks like it would work, it doesn't. Users can't enter text, but it still allows them to enter values greater than 100.001. I tried reversing the formula but that didn't work, and neither did using Whole Numbers Data Validation - that wouldn't let me enter squat on a cell formatted as a percentage no matter what the maximum number was.

Any help would be greatly appreciated!

Question by:monbois
    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    Try 1.00001 instead of 100.001
    LVL 50

    Accepted Solution

    Percentages can be a little odd - when you format a cell as % then entry can be 50 but excel stores that as you need to keep numbers in the range 0 to 1.00001

    Try using the "Decimal" option then "between" and use those 2 values - 0 and 1.00001

    that won't allow text either

    regards, barry

    Author Closing Comment

    Works perfectly! Thanks!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    52/53 week year end date formula 5 35
    Sum Per Month 7 23
    rank minimum order 9 19
    Calculating Percentile Value inside Excel. 2 15
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    754 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

    16 Experts available now in Live!

    Get 1:1 Help Now