Excel Data Validation: Master This Feature with Tables

excel data validationIt’s hard to thrive in today’s world without the basic essential tools of the world’s most popular software, Microsoft Office – especially its extremely handy application, Microsoft Excel. Excel is a spreadsheet application that offers a variety of functions including graphing tools, pivoting tables, and calculation. While most people have a basic understanding of Excel, it can be rather difficult to master the application without a little help (a little help in the form of an Udemy course that can be accessed right here). Once mastered, individuals find that the program can be used in a vast variety of everyday activities, quite often simplifying your workload. From an outsider’s perspective, Excel can be seen as complex, especially when one throws about seemingly larger terms. One such issue that arises with new users of Excel is Data Validation.

What Exactly IS Data Validation? 

Sure, the term seems a little dark to new users, but it truly is far from it. To put it in layman’s terms, the data validation feature is used to make Excel and your worksheet run much smoother, a tool that is actually a pleasure to use the more you get to know the application. The feature assists you in controlling what can be entered in your worksheet. It allows you to create a drop down list in a single cell, as well as restrict entries, and create custom rules for what can be placed in the cell.

How do I create a Drop Down List? 

A convenient drop down list can be created quickly using Data Validation. It’s true that you can actually type the list directly into the dialog box, however it’s better to use a table on the worksheet. And creating a table is easy. For example, if you are creating a spreadsheet that will organize your small company’s payroll, you will have to maintain your list of options by imputing them on the worksheet. You can perform this task on the sheet itself, or on a different sheet.

  1.  Start off by adding a heading for the list, and in this case, we’ll call “Employees” (since for our example, we’ll be sticking with the employee payroll mock spreadsheet).
  2. Right below the heading cell (all in a single column), simply type in the entries you wish to see in the drop down list. So if you’re creating a list of names, put the header in column 1, and then the first name in the cell below, the second below that, etc. However, do NOT leave blank cells in between.
  3. Highlight the heading and then click on the Insert tab. Once there, click Table.
  4. The pop up on the screen after clicking Table, will ask you if you wish to create a table. Click the check mark, stating that your table has headers, and then hit OK.

This may seem complex, and if you’re still slightly lost, be sure to check out our Excel course here.

Now Put a Name to that List Range 

After creating the table, you need to think up a named range (one that doesn’t include that heading cell in the table itself). Once named, it will format automatically if any items are either added to, or deleted from the list.

  1. What you want to do here is simply click the top of the heading in order to highlight all the cells in the list.
  2. Hit the Name box (which is directly to the left of the formula bar).
  3. Type in a single word name for the list (in our case, it will be Employees).
  4. Hit the Enter key in order to finish the process.

Along with the Beginner course, Udemy also offers advanced courses in Excel that can be accessed right here.

Time to Apply the Data Validation 

There’s that phrase again. So far this seems easy enough, right? Let us continue onto the Validation portion of the article. So now that you have created a named range, you can easily use that named range to manifest a drop down list in a cell (or in multiple cells).

  1. First off, please highlight the cells you want in the drop down list.
  2. In the Data tab, hit Data Validation.
  3. The Data Validation pop-up box will appear. In the Settings tab, under the Allow, highlight List.
  4. Hop down to the Source box below the Data box, type in an equal sign followed by the list name (so in this case, it will be =Employees).
  5. Hit the OK button in order and the Data Validation box will close.
  6. Back on the spreadsheet, highlight one of the cells and then hit the drop down arrow.
  7. Highlight a name in the drop down list in order to add it into the cell!

Can I Create a Drop Down from a List on a Different Sheet? 

Once using Excel and you become accustomed to certain settings, you may find that you’d rather NOT create a table, so you can easily create a named range and apply that range as the main source for a drop down list.

You can also use, what’s known as, a Delimited List

So, other than going to that list of items on the spreadsheet, you and always type the list right in the Source box (in the Data Validation pop-up box). You can separate words by using commas only (for example paid,notpaid). This method is extremely case sensitive, so an error box will pop up if you use all Caps. In order to see this alert if you missed a step, hit the Error Alert tab in the Data Validation pop-up and turn on the Error Alert by clicking the box next to “Show error alert after invalid data is entered”.

This could be a lot to take in for first time users but once you go over the basic steps, the quicker you’ll become when working with Data Validation in the Excel application. If you wish to become a master at Excel, Udemy makes it as simple as 1-2-3 with this Excelcourse. Also, Udemy offers courses that specialize in getting yourself familiarized with Microsoft Office so you can learn numerous techniques and tricks, like this handy Microsoft Office course.