We've now looked at several ways in which we can add data validation to our workbooks to

help ensure that the data coming into our workbooks is in fact correct.

Once we've added the validation,

it is quite useful to know how to work with it in the workbook.

So, we're going to look at a few tips and tricks for doing that now.

One of the things that can easily happen is

that validation was accidentally added to the wrong cell.

So, for example, what has happened in

this workbook is the validation has been added to the heading.

Now we actually want that heading to say location not showroom or basement.

So what we're going to look at is how we can clear the validation.

Start by clicking on the cell or cells where you want to remove the validation,

come back to your data tab.

Click validation, and at the bottom,

you'll see a Clear All button.

Click on that and say Okay.

You can see the validation has now been cleared from that cell.

Now when we added the validation to this worksheet,

there were already a few values that were incorrect.

You can actually see them here highlighted by little green triangles.

But they're not very visible.

So we would like a really clear way to identify

all the cells that don't meet our validation criteria.

When you come up here to the validation button,

but this time you click the drop down menu just to the right of it.

The second option down is circle invalid data.

When we click on that,

we get really nice highly visible red circles appear around all the invalid values.

If you don't have time to sort that out now,

you can easily clear the circles by coming back to the drop down.

We, however, would like to fix it.

So I'm going to go back to Circle Invalid Data,

come to the first one,

select the correctly spelled showroom

and then we might as well take care of the other two.

The Rack should be Rack one.

And this one should be three.

Problem fixed. Our data is looking much better already.

So that is how you can easily identify and highlight

cells that don't meet your current validation criteria.

When you're working with workbooks that you're not familiar with,

it's not always easy to identify at first glance,

where data validation has been applied previously.

Fortunately, Excel provides us with a method for doing this as well.

We're going to come back to the Home tab now and

on the far right here you can see that there's the Find and Select.

So click on it and we'll come down to the Go To Special.

And this little dialogue is really useful.

It allows you to, for example,

select visible cells only or select cells that contain formulas.

All what we're looking for here is data validation.

There are two options under validation.

Select all the cells that contain validation or select

only the cells that contain the same validation as the currently selected cell.

We are going to look at the second option first.

So click on Same and click Okay.

And immediately it has selected all the cells that have the same validation.

Now let's click away for a moment into a different cell and let's try that again.

So Find and Select, Go To Special,

data validation, but this time we'll do All and click Okay.

And now you can see that all the cells containing validation have been selected.

Another thing you can do is copy cells with validation.

Normally, when you copy a cell,

it will copy the contents of the cell,

the formatting of the cell and any conditional formatting as well as any validation.

If however you just want to copy the validation,

you can do that as well.

Much the same way as you can choose to just

copy the formatting or to just copy the value.

So let's have a look at an example.

Let's say that they've identified that the target level should

always be a number between 5 and 100.

So let's apply that validation now,

I'm going to press control shift down arrow to select all my data.

Then I come back up to data validation and I'm

going to choose a whole number between 5 and 100.

And that's our data validation added.

Now realize that my reorder level meets exactly the same criteria.

So rather than going through that whole process of adding it again,

I can simply copy the validation rules from the target level.

So we're going to press ctrl+C to copy like we normally would.

And now we're going to do a paste but in this case,

we're going to do a paste special.

I'm going to select all my reorder levels,

so click on J4 and control shift down arrow.

And then I'm going to right-click and come down to Paste Special. And here's the trick.

Once I've gone to Paste Special,

right at the bottom you'll see another option to

Paste Special because this one is very special.

So click on Paste Special and once again find the option Validation.

When I click Okay, it's not going to copy the formatting

all the values just the validation.

All right. Let's test that it's worked.

So the values have stayed the same,

which is rather reassuring.

And we'll test that the validation has been added.

So we'll make that 101 and it saying no

because it's not meeting validation criteria. It's worked.

So we've now looked at how we can easily copy

validation and that's really useful if you need to copy

validation from one sheet to another or you've added

data at the bottom of the sheet and forgotten to pull the validation down with it.

On that note though, one of the things we want to ensure we're

able to do is when we add data to the bottom of the data set,

we would like the validation to move down with it.

What people may be tempted to do is to select

the column and add validation to the entire column.

But that's really not good business practice.

For one thing, you're adding validation to cells that shouldn't have that validation.

But given that we now have over a million rows in Excel,

you're also making Excel work unnecessarily hard.

So we really only want to add validation to the cells that contain data.

But, I hear you say,

"What about when I add new values at the bottom?"

Well, the solution is simple.

Convert your data into a table to begin with.

We did this a little bit earlier,

so you can see I'm working in a table.

The Norton Homewares Inventory is already stored in a table and that's great.

Once you're working in a table,

you can come down to the bottom row just beneath

your table and you type in a new product.

So let's call this one MATE-099 and press tab.

And when I come and check any of the cells with validation,

you'll see that the validation has automatically copied down.

And that is probably the best way of ensuring that

your validation order extends with the rest of your data.

So these are just a few tips and tricks to

help you work with your validation more effectively.

So make sure you take some time to download the exercise file and try this out yourself.

[SOUND]

Practice Video: Working with Data Validation

Excel Skills for Business: Intermediate II

Rencontrer les enseignants