• 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
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • 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

linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
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.


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...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

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

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

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.

Blog Categories

  • 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
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

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.