• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Custom Data Validation to Limit Entries

You are here: Home / Excel / Excel Custom Data Validation to Limit Entries
Excel Custom Data Validation
September 15, 2014 by Mynda Treacy

In this tutorial we're going to look at how Santa is using Excel Custom Data Validation to limit the number of presents kids request on their Christmas wish list.

With Christmas fast approaching our kids are already talking about what Santa might bring them this year.

Little do they know that Santa will be implementing a new, 3 present request limit and formalising the process via a spreadsheet on Excel Online.

Yes Kids, Santa is finally tech savvy!

Below is a preview of the form. Watch what happens if you try to list more than 3 presents:

Custom Data Validation in Excel

Santa's Data Validation HelperHow did Santa set this up? Let’s just say he had a little helper πŸ˜‰

Custom Data Validation

Custom Data Validation is actually quite straight forward and very flexible.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Step 1: Select the range of cells you want validated. Mine are C8:C17… I’ve allowed more than 3 cells because I expect some tenacious kids will not stop at the first error they get in C11!

Step 2: Go to the Data tab of the ribbon > Data Validation > Data Validation

Data Validation menu

Step 3: In the Settings tab choose β€˜Custom’ from the β€˜Allow’ list:

Data Validation settings

Step 4: Enter your formula.

Data Validation formula

The formula must evaluate to TRUE or FALSE. i.e. it’s a logical test. My formula is:

=COUNTA($C$8:$C$17)<=3

Which simply counts how many cells in the range C8:C17 contain data, and then tests to see if it is less than or equal to 3.

If the formula resolves to TRUE, the entry is allowed, and if it resolves to FALSE the entry is disallowed and the error alert is displayed.

Step 5: Optional – Add an input message to give instructions as to what is expected.

Data Validation message

Step 6: Optional – Add an Error Alert in case they try to buck the system!

Data Validation error message

Tip: there is a regular Validation List is in cell C5 for the child to self-asses their behaviour for the year (Santa takes this with a grain of salt ;-)):

Data Validation list

Next year Santa will be stepping it up and collating the present requests via an online Excel Survey so he can leverage the use of PivotTables and other Excel tools.

Other Uses for Custom Data Validation

Another use for Custom Data Validation is to set a monetary limit for a range of cells. Let’s say you’re collating budget data and each department is allowed to budget for 3 projects but the total must not exceed $50,000.

Data Validation restrict total amount

The Data Validation formula is:

Data Validation restrict total amount formula

Excel Custom Data Validation

More Data Validation Posts

select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Searchable Drop Down List in Excel

Searchable Drop Down List in Excel

This searchable drop down list in Excel includes an option for "All" and ignores duplicates. No VBA and no formulas. You won’t believe how easy it is.
selecting multiple items in data validation list

Populating Multiple Cells from Single Data Validation (Drop Down) List

Using a little VBA we can use a single data validation list to select multiple items and populate multiple cells
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Excel Combo Box KO’s Data Validation

Excel Combo Box KO’s Data Validation

Use an Excel Combo Box as an alternative to Data Validation Lists
Excel Tables as Source for Data Validation Lists

Excel Tables as Source for Data Validation Lists

Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Data Validation With Dependent Lists

Excel Data Validation With Dependent Lists

excel drop down lists

Excel Drop Down Lists

Excel Drop Down Lists or Data Validation Lists as they're officially known, are a great tool speeding up data entry and ensuring data is entered correctly.

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: ExcelTag: Data Validation
Previous Post:Excel Pivot Tables Year on Year Change
Next Post:Change the Color of Words in TextChange the color of words in text

Reader Interactions

Comments

  1. arvind kumar

    May 9, 2022 at 1:58 pm

    I want to validate data for exam marks entry.
    Marks are in whole numbers. No decimal number allowed. Also we will enter “AA” for absent students and “ML” for students on medical leave.
    Kindly suggest me.
    Thanks.

    Reply
    • Mynda Treacy

      May 9, 2022 at 5:34 pm

      Hi Arvind, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  2. Steve

    February 24, 2021 at 12:55 am

    Hi, Love your content, thank you for sharing.

    With customize validation, how could I limit the number of times a specific election was used. I want to post a spreadsheet for training sign up, so a training session on Sat 15th xxx has 15 seats available, and hence should only be selected 15 times. Once we have 15, it is no longer selectable. if someone changes their mind and deletes the 15th, that now allows someone else to select the 15th.

    Thanks in advance

    Reply
    • Mynda Treacy

      February 24, 2021 at 1:55 pm

      Hi Steve, perhaps this reducing data validation list technique will help.

      Mynda

      Reply
  3. Paltiel Y Werther

    February 14, 2020 at 12:12 pm

    Hi, Thanks for your information. I created a questionnaire with six cells on the right of each question. The first cell is for “disagree strongly” and is supposed to be filled with a “1” the second cell is “disagree somewhat” and is supposed to be filled with a “2” etc. I want to restrict the user to entering an answer into only one cell and to restrict the user to entering a 1 in the first cell or a 2 in the second etc.
    Please advise.
    Thanks!

    Reply
    • Mynda Treacy

      February 15, 2020 at 9:50 am

      Hi Patiel,

      I would use Option Buttons inside a Group Box as described in this post on Form Controls.

      Mynda

      Reply
  4. Cor van Montfort

    November 16, 2019 at 7:30 pm

    Hi,
    can you help me with a solution to get of formula for the “Custom” data validation function?
    I would like to fill a cell with a “positive number” >0 and only one text entry “UNK”.

    thanks for willing to help me.

    Cor

    Reply
    • Mynda Treacy

      November 18, 2019 at 6:32 pm

      Hi Cor,

      Can you please post your question on our Excel forum and provide a sample Excel file with some examples of what is acceptable in the cell. It sounds like you want a number and text in the same cell e.g. 9UNK where 9 needs to be > 0, if so, I don’t think this is possible.

      Mynda

      Reply
    • arvind kumar

      May 9, 2022 at 2:02 pm

      =OR(A1>0,A1=”UNK”)

      Reply
  5. John Phillips

    May 25, 2019 at 2:10 am

    Hi, I have a very basic spreadsheet set up which is working out the productivity of a small workshop, in cells D2 and D3 they enter the working days of the week, D2 can only enter 1-4 and D3 can only enter 1, this then multiples these values by 8.5 and 7 respectively to give a value of 41 hrs, then they add hours to the days which throws up a value in another cell based on =S8/((T2)+(T3))*100, S8 being the productivity value, T2 = the value of cells D2 and T3 the value of D3 (Mon to Thurs 4×8.5 and Fri 1×7), if all the fields are entered to the max ie Mon to Fri 8.5hrs and Fri 7 hrs it returns a value of 100, which is correct, but I need to error S8 if the user makes an error in inputting ie 3 days instead 4 days Mon to Fri, or or Fri that drops the calculation to below 41 hours, as the Productivity calculation goes above 100%, which is impossible.

    Reply
    • Mynda Treacy

      May 25, 2019 at 3:34 pm

      Hi John,

      It’s difficult to answer this without seeing your file. Can you please post your question on our Excel forum where you can upload a sample file and we can help you further?

      Mynda

      Reply
  6. Krishna

    May 29, 2018 at 12:34 pm

    Hi, thank you very much for the wonderful Santa tip. I have a question on how to apply this wholesale to multiple columns, but each column with its own count. I.e. 10 columns side by side and then another 10 columns side by side in a different area of the worksheet, like that I have probably 50 areas; but each column should have its own formula. Is doing it one by one the only way?
    Thanks,
    Krishna.

    Reply
    • Mynda Treacy

      May 29, 2018 at 8:12 pm

      Hi Krishna,

      I don’t know of a way you can copy the data validation rules and have it automatically refer to a different range of cells. AFAIK the references need to be absolute.

      Mynda

      Reply
      • Krishna

        May 31, 2018 at 1:54 am

        Thanks for the reply.
        K

        Reply
  7. Rohan

    April 18, 2018 at 8:24 pm

    Hi, Need Help. I have 10 cases and 100 different entries are mapped against these 10.It Means 10 cases against single common case. Now I want to validate this data where after selecting any one case out of 10 cases, mapped cases (10 cases) should show in next validated cell.

    pls let me know if this is possible.

    Reply
    • Mynda Treacy

      April 18, 2018 at 8:46 pm

      Hi Rohan,

      It sounds like you need dependent data validation lists. Here are some different approaches:

      https://www.myonlinetraininghub.com/excel-dependent-data-validation

      https://www.myonlinetraininghub.com/excel-data-validation-with-dependent-lists

      If you get stuck please post your question in our Excel forum.

      Mynda

      Reply
      • Rohan

        April 18, 2018 at 11:17 pm

        Thank You for Your Help!!!

        Reply
  8. Nikhil Verma

    January 18, 2018 at 5:21 am

    HI, I need help regarding a problem. In my scenario I have a list from A1:A10 and require to set a custom validation in A11 in such a manner so that whatsoever will be the value in cells A1:A10. It can not be entered in cell A11.
    Requirement :- The data in A1:A10 is being updated on daily basis but the A11 cell should not be entered with a value which is already there in the list from A1:A10. Value can be anything i.e. be it Text, Decimal, Special character, etc.
    Thanks in advance,
    Nikhil Verma

    Reply
    • Catalin Bombea

      January 18, 2018 at 11:57 pm

      Hi Nikhil,
      Add a custom validation to cell A11 (in Allow: field, select the last value- Custom), with this formula: =NOT(ISNUMBER(MATCH($A$11,$A$1:$A$10,0)))
      Catalin

      Reply
  9. Prateek Sabharwal

    November 14, 2017 at 3:42 am

    Hi, I needed some help. I am trying to put custom data validation to a cell where there are a case of circular reference.
    I want the value in G6 to be less than or equal to the value in H3. But the formula for H3 is SUM(C3:C34) – SUM(G3:G34) and hence there is a circular reference since the value of G6 itself affects the value of H3. What I want is that I want to limit the value of G6 the value of H3 before the value of G6 to be put in.

    Suppose, the cell G6 is blank and the value of H3 is 5. I want the value of G6 to be less than or equal to 5. But if I put any value in G6, it changes the value of H3 since there is a circular reference. I want the formula to hold for the value of H3 prior to putting the value of G6.

    If I make change in the value of any cell before G6 that affects the value of H3, that should change the formula but any cells after G6 that affect the value of H3 should not change the formula. Is all this possible?

    Reply
    • Catalin Bombea

      November 14, 2017 at 2:30 pm

      Hi Prateek,
      Conditional iterations are not possible in excel, iterative calculation can only be enabled or disabled. I’m afraid you have to build another scenario to get your desired outcome.
      Catalin

      Reply
  10. KAJAL

    August 19, 2017 at 5:36 pm

    I NEED A HELP. I WANT TO ENTER S IN A ROW . I WANT TO SET ITS LIMIT. FR EX. I WNT TO ENTER S 4 TIME . IF I TRY TO ENTER S 5TH TIME .IT SHOULD GIVE ERROR. IS IT POSSIBLE.

    Reply
    • Mynda Treacy

      August 21, 2017 at 8:48 am

      Hi Kajal,

      You could always put data validation in the 5th cell to prevent anything being entered. Might be easiest if you post your question on our Excel Forum with a sample Excel file and I can give you an example of what I mean.

      Mynda

      Reply
  11. Jon Acampora

    September 19, 2014 at 12:29 am

    Great tutorial Santa (I mean Mynda)! That Santa hat is awesome btw!

    Reply
    • Mynda Treacy

      September 19, 2014 at 7:35 am

      πŸ™‚ thanks, Jon.

      I made that picture in Excel. Who needs Photoshop!

      Reply
  12. Col Delane

    September 18, 2014 at 3:43 pm

    Further to Sumit’s query and suggestion, the custom DV formula could be something like this:
    = AND( COUNTA($C$8:$C8) = ROWS($C$8:$C8), COUNTA($C$8:$C$17) <=3)

    Note the lack of a $ before the 2nd cell ref in each of the ranges in the 1st leg of the AND function, which allows the condition check range to expand with the input range.

    With this DV formula, users must enter their requests in order starting from row 8, but can only enter up to 3 requests in total.

    Remember that any DV formula must return TRUE (i.e. no number or text string, etc. – just TRUE) for the input to be accepted into the cell, so you just need to put on your algebra hat and keep tinkering until the formula logic produces that result.

    Also note the spaces between different parts of the formula, which do not adversely impact its working, but allow the user to more easily decipher it.

    Reply
    • Mynda Treacy

      September 18, 2014 at 7:39 pm

      Thanks, Col. Great tip πŸ™‚

      Reply
  13. Oz

    September 17, 2014 at 4:57 am

    This is such the awesome blogpost!
    Good for Santa!

    Reply
    • Mynda Treacy

      September 17, 2014 at 7:02 am

      πŸ™‚ Thanks, Oz.

      Reply
  14. Jef

    September 16, 2014 at 10:46 pm

    Useful and practical information. Works a treat. Thanks for sharing.

    Reply
    • Mynda Treacy

      September 17, 2014 at 7:03 am

      Thanks, Jef πŸ™‚

      Reply
  15. Sumit Bansal

    September 16, 2014 at 2:42 pm

    Hello Mynda.. Great Tutorial. Very Creative.

    One question – The current validation allows entry in C11, if any of the cell in C8:C10 are empty. Would it be better if data entry is allowed only when there is a number in the corresponding cell in Column B.

    I tried it with this formula: =AND(ISNUMBER(B8),COUNTA($C$8:$C$17)<=3)

    Thanks for the awesome article!!

    Reply
    • Mynda Treacy

      September 16, 2014 at 2:47 pm

      Great idea, Sumit. Santa says ‘thanks’ πŸ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x