If you don’t want a message to show up, clear the check box.Īfter you create your drop-down list, make sure it works the way you want. If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message. If you don’t want a message to show up, clear the check box. If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). You can also just type a list of entries in the Source box, separated by a comma like this: Just include the cells that should appear in the drop-down. If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. Go to the Data tab on the Ribbon, then click Data Validation. Select the cell in the worksheet where you want the drop-down list. Now is a good time to Sort your data in the order you want it to appear in your drop-down list. Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. To stop people from entering data that isn’t in the drop-down list, click Stop. Information will show a message with this icon and Warning will show a message with this icon. To show a message that doesn’t stop people from entering data that isn’t in the drop-down list, click Information or Warning. Not sure which option to pick in the Style box? If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message. If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes (up to 225 characters). If it’s OK for people to leave the cell empty, check the Ignore blank box. Note that we left out the header row, because we don't want that to be a selection option: We put ours on a sheet called Cities, in range A2:A9. On the Settings tab, in the Allow box, click List.Ĭlick in the Source box, then select your list range.
Unlock specific areas of a protected workbook or stop sharing the worksheet, and then try step 3 again. This helps support the channel and allows us to continue to make videos like this.Note: If you can’t click Data Validation, the worksheet might be protected or shared. Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links.
Learners say his sessions are like magic shows. He facilitated 100000+ professionals from 500+ companies like Taj Group, ICAI, British Council, BSI, NIIT, IndiaMart, MetLife, Mahindra and lots more from Executive level to Sr. He received several Best Trainer Awards from corporates for Microsoft Office based workshops. He worked with the Top corporates like Mphasis, Motherson Group with 10+ years of industry exposure.
#DATA VALIDATION EXCEL 2007 TUTORIAL PROFESSIONAL#
☕ If you find my videos useful and would like to support me, you can buy me a coffee – Īn MCA Professional and a Microsoft Certified Microsoft Office Specialist, Vivekananda is working as a Chief Mentor and currently heading Future in Hands. ? Want to see more awesome content like this as soon as it is published: Be it Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365.ġ5:45 Allow only Capital Letters in Excel These tips and tricks are applicable across all versions of Excel. Here, I have show you the implementation of functions while creating a Data Validation criteria. In this tutorial, I will show you 5 Cool Tips and Tricks of Data Validation in Excel. Data Validation in Excel is an awesome feature that helps us to avoid entries based on certain criteria.
While working on Excel Reports, there are several scenarios where we wanted to restrict entries of invalid data. 5 Cool Tips and Tricks of Data Validation in Excel