Teach Yourself Excel Lesson 8 - Data Validation

Nick's picture


Data Validation

Download starting Sheet

We have our headings entered, so now we need to enter some expenses.  

Enter the following data:

Date Expense type Necessary ? Description Amount Year Month
12-May-06 Eating out FALSE chez patrick 10    
16-May-06 Computer TRUE new laptop 400    
17-May-06 Eating out FALSE Pub 20    

NOTE: You will need to adjust the column Widths when the data entered is bigger than the heading.

Now, one thing that differentiates a good spreadsheet from a bad one is efficiency and data quality.

We've typed "Eating Out" twice, and FALSE 2 times, but we didn't need to. We may have even misspelt "Eating Out".

We can use Data Validation to help us here.

Steps:

  1. Select cell B1
  2. Press CTRL + SPACE (to select the column)
  3. Click "Data" on your Ribbon
  4. Click "Data Validation"
  5. Choose "List" from the "Allow" dropdown
  6. In the box, type:  Eating Out, Computer
  7. Press OK

data-validation

Now click on cell B2

You'll notice that there's an arrow to the right.

Click on the arrow, and you'll see that you now have the option to choose an entry from the list

data-validation

This saves massive amounts of time when entering data if you can categorise the data before hand. So now, instead of typing in expense type, we can choose from the list.

Do the same for the "Necessary ?" column.  (This is where you note whether the expense is a necessary one or not)

Your entries this time should be: TRUE, FALSE.

NOTE: In Step 6, You can enter as many expense types as you want by separating them with a comma, or choose a range of cells with your mouse where you have entered the different expense types.

 

Your sheet should now look like this

Next Lesson: Teach Yourself Excel Lesson 9 - Delete Data

 

Training Video on Data Validation in Excel:

AttachmentSize
data-validation-lesson.xls19 KB