With certain forms or lists in Excel, it is helpful to have some control over what people can enter. For example, if you have a zip code column you could have Excel only accept entries that are at least 5 characters, but no more than 10.\u00a0 Or if you have a column for sales rep, you could make people choose from a predefined list of names, so later when you want to sort by sales rep, misspellings won’t cause it to sort incorrectly.<\/p>\n
For the zip code example, lets say that the zip code is column E on your spreadsheet.\u00a0 First, you would select the whole column by clicking on the E column heading.\u00a0 Then go to the Data menu and choose Validation. (In Excel 2007, go to the Data tab of the ribbon and click the Data Validation button.)\u00a0 In the Allow drop down box, select “Text Length”.\u00a0 At Data, choose “between”.\u00a0 Enter 5 as the minimum and 10 as the maximum.\u00a0 If you want to, you can also enter an Input Message which is shown when a cell is selected, and\/or an Error alert which is displayed when someone enters data that does not meet the settings you have chosen.<\/p>\n
For the second example, you have to make the list of acceptable entries first.\u00a0 Since you don’t want it to be in the way, I would suggest putting it on a different tab.\u00a0 We’ll use column A of Sheet2 to hold a list of sales reps.\u00a0 Then, back on the main tab, select the sales rep column of the spreadsheet, and go to Data Validation.\u00a0 This time select List in the Allow drop down box.\u00a0 In Source, enter =Sheet2!$A:$A<\/em>.\u00a0 Again, enter an input message or error alert if desired and click OK.\u00a0 Now when you click in the Sales Rep column, will see that you get a drop down list to choose from.\u00a0 If you type something else in manually, you will get an error and Excel will force you to change your entry.<\/p>\n","protected":false},"excerpt":{"rendered":" Have you ever wanted to have more control over what people can enter in a form or list you have created in Excel? Well, nerds call that “data validation” and it’s actually not hard to do.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/144"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=144"}],"version-history":[{"count":3,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/144\/revisions"}],"predecessor-version":[{"id":1012,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/144\/revisions\/1012"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}