Foreword

Before I start, I’d like to point out that this course is not intended for those who start working with Excel for the first time. For this I have written other courses. So if you’ve never heard of a formula or what a macro does and when or how they are used, first read our basic Excel courses.

Second, this course was created with Excel 2010 in mind but it can also be used by Excel 2007 users and even for users of Excel versions 2003, 2002 and 2000. If I can remember where the options are found in Excel 2003 and previous versions, I will not fail to mention this.

Data Validation

We use “Data Validation” to ensure that the data entered in cells, is duly completed.
In Excel 2007 and 2010 we find “Data Validation” (1) in the ribbon under the “Data” tab.
In all previous versions, we find this option under the menu item “Data”, option “Validate …” (2).

When you click this button, the validation that is dictated, is applied to all the selected cells. So if you have selected a whole range, a whole row or one column, the validation will be used in all cells that are currently selected.

Validation List

Let us first see what I already made for you. I have a spreadsheet with two tabs.

The “invoice” tab, which includes an invoice, which of course displays all the customer data, plus a description and price of the service. Nothing special here, just an ordinary bill.

I have the “customers” tab that contains a list of all of our customers, their address and their VAT number.

The intention is that we change the name of the customer, then choose the rest of his data like address, postal code through the “Data Validation” function and automatically fill it in the “vertical column”. Difficult? Not at all, if only you know how to do this.

The first thing we do is to list the validation set. First select the cell where you want the list (1). Click the “Data Validation” button in the ribbon (2). Select “List” in the “Allow” box (3). Now you have two options. One, you type a list in the “Source”, where you list all of the data separated by a comma, or, you get this list from a table somewhere in your spreadsheet. Since I already have a list under the “Customers” tab, I go for the second option and click the “Fold” button (4), the box next to “source”.

I open the worksheet “Clients” and select the entire column A (1). Why the entire column A? Because then I can later add more customers without having to change anything in the data validation. If you do not want this, it is okay, you select only the names of your customers. Click again the “Open” button (2).

This brings us back in the “Data Validation” where you can see that the source is filled in (1). Click the OK button (2).

If we now return to the worksheet “invoice” option, you will see that the list is added neatly to the field “name”, with a drop-down arrow (1).

In the next lesson, we will see how other fields in the customer data can automatically be filled.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats