In the Source box you can type =city to reference the named range.įor the dependent list in cell G2, the selection in cell F2 needs to be converted into a reference to the named ranges. The list in cell F2 is created just like in the previous example. Type the name you would like to apply and press Enter.Click in the Name Box to the left of the Formula bar.For example, range A2:A4 is named city, range B2:B6 is named cardiff and so on.įollow these steps to create a named range. To achieve this, we first must name each list. The selection from this list affects what names appear in the next list in cell G2. In the example below, we have a list of cities in cell F2. For these lists, the item selected in one list will affect what options appear in the next list. Let’s take our drop down lists further and create dependent lists. In this example, the items were in range A1:A5 of a sheet called Names. When you need a more dynamic list for items that change over time such as lists of products, places and people, then referring to a range makes sense.įor this list, click in the Source box and then go and select the cells that contain the items. When you need a simple list such as Open and Closed, or Yes and No, then typing the entries in makes sense. You can then either type the list items directly into the Source box separated by a comma, or refer to a range of cells that contain the list items. Creating lists is a simple and effective way of controlling data entry. Check out more great Excel date functions ().Ĭreating drop down lists is the reason most people become familiar with the Data Validation feature. The TODAY function returns the current date from the computer. Select Custom from the Allow list and enter the following formula into the Formula box. If they are, then the entry is valid.įor this example, the validation was applied to range A2:A6. The EXACT function is used to compare the cell entry with the uppercase version to see if they are the same. The UPPER function probably speaks for itself. The cells need to accept the entry of both text and numbers, but the text must be uppercase.įor this we can use a formula with the UPPER and the EXACT functions. You may need to ensure that data is entered in uppercase, such as this example of UK postcodes being entered. In the Settings tab, select the validation rule criteria.
Click the Data tab and then the Data Validation button on the Ribbon.First select the range of cells you want to apply the validation to.In this blog post we will explore 11 useful examples of what Data validation can do. A very important job when working with data. It controls what can be input into a cell, to ensure its accuracy and consistency.
It often goes unnoticed as Excel users are eager to learn the highs of PivotTables, charts and formulas. This compilation includes publications for practitioners of all skill levels.This is a guest post by Alan Murray from Computergaga.ĭata Validation is a very useful Excel tool. Each of the books listed in this compilation meets a minimum criteria of 15 reviews and a 4-star-or-better ranking.īelow you will find a library of titles from recognized industry analysts, experienced practitioners, and subject matter experts spanning the depths of pivot tables all the way to business modeling. Titles have been selected based on the total number and quality of reader user reviews and ability to add business value. The editors at Solutions Review have done much of the work for you, curating this directory of the best Excel data analysis books on Amazon. There are few resources that can match the in-depth, comprehensive detail of one of the best Excel data analysis books.
There are loads of free resources available online (such as Solutions Review’s Data Analytics Software Buyer’s Guide, visual comparison matrix, and best practices section) and those are great, but sometimes it’s best to do things the old fashioned way. Our editors have compiled this directory of the best Excel data analysis books based on Amazon user reviews, rating, and ability to add business value.