Automating Data Entry using Data Validation in Excel

Profits is backed by revenue and revenue is backed by products entity is selling. Accountants often on daily basis have to prepare sales report. Two of the important ingredients of every sales report is the product name itself and price per unit. For such reports accountants often have to type products’ names and per unit prices to populate excel cells to run their query or other functions.

Our life will become very easy if we have to select products from the drop down list instead of typing and per unit price show up automatically when product is selected. This can be done in Excel in many ways but the easiest of all is using Data validation and an appropriate LOOKUP function.

What we want is:

• easily select product name from the list
• excel automatically provides per unit price of product selected

Once we have this we will easily continue with our calculations.

List Drop-Down – INBOUND!

Following steps help you make a drop-down list of the products.

Preparing your data

Step 1: Make sure all the products are in one column. Select all of the products excluding the heading, if any. In our case the data is in cell A14 to A20. If the products are in large number you can quickly select them by hitting combo CTRL+SHIFT+DownArrow

Step 2: Once all the products are selected hit Ctrl+F3. This will open Names manager. You can access names manager from Excel’s ribbon as well by clicking Formula tab and Names manager button in the defined names group.

Step 3: Click New button at the top of names manager dialogue box. A new dialogue appears. Enter products in the name field and click OK button.

Step 4: Now select the products names with their prices and head to name box just at the left of formula bar and enter: productdata and press Enter. Make sure there is no space in the name. This is the second way of naming. You can confirm whether range is named or not by accessing names manager. And also from the name box by clicking drop down arrow. Sweet!!!! Isn’t it!

Rise of the Drop Down

Step 1: You can embed drop down list in one cell, few cells or whole column as you deem fit. To embed in single cell select just that cell. If you want drop down list to appear in select few cells than select all such cells. If you like drop down to appear in the whole column till the center of the earth then select the whole column and proceed to next step.

Step 2: In our case we have selected from cell A5 to A9. Once area selected, click Data tab and Data validation button from the Data tools group. One can use Alt key to access this tool. Full combination is: Alt+A+V+V. One can use shortcut from 2003 version or earlier which is Alt+D+L

Step 3: Data validation box will pop-up. From the allow drop down select “list”

Step 4: Make sure ‘ignore blanks’ and ‘in-cell dropdown’ is checked. Click in Source field and:

either type: =products. Yes this is the same name you gave to product selection in Step 3 above.

or press F3 button on the keyboard. Paste name dialogue will show up click products name and press OK.

Step 5: Now if you click on any cell in column a dropdown arrow will appear and you can easily select the product you want.

Getting product prices

Step 6: Select the cells B5 to B9 and in the formula bar insert the following function:

=LOOKUP(A5,productdata)

Where productdata is the range where you want to look for the value in cell and is basically a name to the range A14:B20.

Once the whole formula is typed instead of press Enter hit Ctrl+Enter and it will put formula in all of the selected cells. Job done! 🙂

Bonus Tip:

If you do not select any product from the drop down then price column gives #N/A! error. It basically tells that required data is not available to execute the function. But that is not really an error as we haven’t selected anything yet and we know once we select the product it will work. So to avoid N/A error to appear enclose your lookup function in IFERROR function i.e. replace formula in price column with this:

=IFERROR(LOOKUP(A5,productdata),”Select Product”)

This formula simply checks if function runs into an error due to any reason, as it happens in our case, then display “Select product” text otherwise result will be shown.