{"id":150,"date":"2022-11-20T04:50:34","date_gmt":"2022-11-20T04:50:34","guid":{"rendered":"https:\/\/aimm.in\/blogs\/?p=150"},"modified":"2022-11-22T05:18:07","modified_gmt":"2022-11-22T05:18:07","slug":"data-validation-1-in-excel","status":"publish","type":"post","link":"https:\/\/aimm.in\/blogs\/2022\/11\/20\/data-validation-1-in-excel\/","title":{"rendered":"Data Validation #1 in Excel"},"content":{"rendered":"\n\n\n\nWe commonly use Microsoft Excel or Google Sheet for every day data entry.\u00a0 \u00a0In this blog post, we can see how we can validate data based on a criteria to avoid mistakes in the data entry.\n\nLet us say we are entering Name of the Student, Subject, Marks Obtained and Max Marks.\n\nIt is commonsense that the marks obtained shall not be more than Max marks and not less than 0 unless your exams are valued with negative scores.\n\nWithout validation, excel could allow any number of digits in the marks obtained column.\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/DV-1.jpg?resize=402%2C363&ssl=1\")
\n\nTo control the user from entering the numbers only between 0 and 100, Go to Data Tab in the Main menu and Choose Data Validation Function under Data Tools.\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv-2.jpg?resize=920%2C195&ssl=1\")
\n\n\u00a0\n\nIn the Data Validation dialog box, please enter Whole number under the Allow drop box and in Data, choose between. Minimum number is 0 and Maximum number is 100. Click Ok to continue.\n\n\u00a0\n\n\u00a0\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv3.jpg?resize=471%2C378&ssl=1\")
\n\nTo inform the user while entering the data, we can give customized error message in the Input Message Tab as below.\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv4.jpg?resize=426%2C335&ssl=1\")
\n\nIn the Error Alert Tab, we can give customized error message for the user just in case he\/she enters a wrong value.\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv5.jpg?resize=407%2C337&ssl=1\")
\n\nThe image below shows the user our customized input message on how to enter the data.\n\n\u00a0\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv6.jpg?resize=392%2C228&ssl=1\")
\n\nGiven below is the sample of the error alert message just in case the user enters a wrong value.\n\n![\"\"](\"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv7.jpg?resize=783%2C235&ssl=1\")
\n\n\u00a0\n\n\u00a0\n\n\n\n\n\n\n\n\n<\/body>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":157,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"pagelayer_contact_templates":[],"_pagelayer_content":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-150","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/aimm.in\/blogs\/wp-content\/uploads\/2022\/11\/dv7.jpg?fit=783%2C235&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/posts\/150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/comments?post=150"}],"version-history":[{"count":3,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/posts\/150\/revisions"}],"predecessor-version":[{"id":161,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/posts\/150\/revisions\/161"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/media\/157"}],"wp:attachment":[{"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/media?parent=150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/categories?post=150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aimm.in\/blogs\/wp-json\/wp\/v2\/tags?post=150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}