Up next in 10
Ensuring Data Accuracy with Excel Data Validation
8K views
Nov 7, 2024
Master data validation in Excel with this comprehensive guide. Learn how to apply, modify, and manage data validation criteria for various columns. This video covers essential skills such as setting up input messages and custom error alerts, extending data validation to multiple cells, and identifying invalid data. Enhance your Excel skills and ensure data accuracy with these step-by-step instructions. Perfect for anyone looking to improve their data entry processes and maintain data integrity in Excel. 00:00 Introduction 00:38 Specifying Data Validation Criteria 04:08 Creating Drop-Down List using Data Validation 04:54 Extending Data Validation 05:31 Modifying Data Validation Criteria 06:00 Validating Existing Data 06:28 Clearing Data Validation
View Video Transcript
0:00
hi everyone today we'll discuss how to
0:02
perform data validation in Excel data
0:04
validation is a feature that lets you
0:06
set up rules for what can be entered in
0:08
certain cells for example you can
0:10
restrict the input to a specific range
0:12
of numbers a list of options or a date
0:14
format data validation also allows you
0:17
to create Dynamic elements in your
0:18
worksheet such as drop-down lists input
0:21
messages and error alerts without using
0:23
any macros let's see how data validation
0:26
can be used in a practical scenario
0:28
create a table with heter for name age
0:31
joining date and department and then
0:33
Implement data validation for each
0:35
column to ensure data accuracy of the
0:36
entered information first we'll apply
0:39
data validation criteria to the name
0:41
column restricting users to enter text
0:43
with a specified length select the range
0:45
of cells under the name header where you
0:47
want to apply data validation click on
0:49
the data tab in the ribbon and then
0:51
click on data validation in the data
0:53
tools group in the data validation
0:55
dialogue box go to the settings tab
0:57
click the allow drop-down menu and
0:59
choose text length in the data drop down
1:01
menu select between set five in the
1:04
minimum field and 60 in the maximum
1:06
field to restrict the text length
1:08
between 5 and 60 characters click okay
1:10
to apply the data validation enter some
1:13
data to see whether the validation works
1:15
if the text length is outside the
1:17
specified range Excel will prevent the
1:19
entry next we create a message that will
1:22
appear when users select the validated
1:24
cells this message can help users
1:26
understand what kind of data they need
1:28
to enter select the same range of cells
1:30
again this time we'll set up an input
1:32
message to help users understand what
1:34
kind of data they need to enter in the
1:36
data validation dialogue box go to the
1:38
input message tab check the show input
1:40
message when cell is selected box enter
1:43
a title and an input message that
1:45
provides guidance on the required data
1:47
click okay now when you click on any
1:49
cell in the selected range your message
1:51
will appear guiding users on the data
1:53
they need to enter next we'll create a
1:56
custom error message that appears if a
1:58
user enters invalid data select the same
2:00
range of cells and go to the data
2:02
validation dialogue box again this time
2:05
go to the error alert tab check the show
2:07
error alert after invalid data is
2:09
entered box choose a style stop warning
2:12
or information and enter a title and an
2:14
error message that clearly explains the
2:16
error click okay now if a user enters
2:19
data that doesn't meet the validation
2:20
criteria your custom error message will
2:23
appear helping users understand the type
2:25
of data they should enter and preventing
2:27
unwanted data entry next we'll apply
2:29
data validation to the age column
2:31
restricting users to enter ages between
2:33
21 and 60 select the range of cells
2:36
under the age header end in the data
2:38
validation dialogue box go to the
2:40
settings tab click the allow drop- down
2:42
menu and choose whole number in the data
2:44
drop down menu select between set 21 in
2:47
the minimum field and 60 in the maximum
2:50
field next go to the input message tab
2:53
check the show input message when cell
2:55
is selected box enter age in the title
2:57
field and please enter an age between 21
2:59
and 60 in the input message field
3:01
finally go to the error alert tab check
3:04
the show error alert after invalid data
3:06
is entered box choose stop as the style
3:09
enter error in the title field and age
3:11
must be between 21 and 60 in the error
3:13
message field click okay now when you
3:16
click on any cell in the selected range
3:18
your input message will appear if you
3:20
enter an age outside the specified range
3:23
your custom error message will appear
3:25
next we'll apply data validation to the
3:27
joining date column ensuring the date
3:29
entered is less than today select the
3:31
range of cells under the joining date
3:33
header in the data validation dialogue
3:35
box go to the settings tab click the
3:37
allow dropdown menu and choose date in
3:40
the data dropdown menu select less than
3:42
in the end dat field enter today
3:44
function as shown on the screen next go
3:47
to the input message tab enter date in
3:49
the title field and please enter a date
3:51
earlier than today in the input message
3:53
field next go to the error alert tab
3:56
enter error in the title field and date
3:58
must be earlier than today in the error
4:00
message field click okay to apply the
4:02
data validation now if a user enters a
4:05
date that is not earlier than today your
4:07
custom error message will appear finally
4:09
we'll apply data validation to the
4:11
department column allowing selection
4:13
from a predefined list of departments
4:15
select the range of cells under the
4:17
department header in the data validation
4:19
dialogue box go to the settings tab
4:22
click the allow dropdown menu and choose
4:24
list it is one of the most useful
4:26
features it allows you to create a
4:27
drop-down menu from a cell range or
4:29
values provided in the source field in
4:31
the source field inter HR it land
4:35
operations to create a drop-down list
4:36
with these options go to the input
4:38
message tab enter select Department in
4:40
the title field and go to the error
4:42
alert tab enter error in the title field
4:45
and please select a department from the
4:46
list in the error message field click
4:48
okay now if a user enters a department
4:51
that is not in the list your custom
4:53
error message will appear I applied list
4:55
data validation to a single cell but now
4:58
I want to extend this validation to more
4:59
cells select the cell that already has
5:02
the data validation settings and the
5:04
range of cells where you want to extend
5:05
the data validation click on data
5:07
validation Excel will prompt you with a
5:09
message do you want to extend the data
5:11
validation to these cells click yes
5:13
check the data validation settings in
5:15
the data validation dialogue box to
5:17
ensure everything is correct click okay
5:20
the data validation settings are now
5:21
extended to the selected range of cells
5:32
next let's modify the data validation
5:34
criteria for the Department column
5:36
select a cell or range of cells where
5:38
you want to modify the data validation
5:40
open the data validation dialogue box
5:42
and make the necessary changes check
5:45
apply these changes to all other cells
5:46
with the same setting box if you want to
5:48
apply modifications to all cells having
5:50
the same rule after making the necessary
5:53
changes click okay to apply the new
5:55
validation criteria
6:00
when we create validation criteria it
6:03
validates only new entries or the
6:04
entries you edit manually however
6:07
sometimes you may paste data from other
6:09
sources that do not match the validation
6:11
criteria to find these cells you can use
6:13
the circle invalid data command this
6:15
command will draw a red circle around
6:17
any cell that contains invalid data to
6:20
remove the circles click on clear
6:22
validation circles in the data
6:23
validation drop
6:27
down if you want to delete dat dat
6:29
validation rules on a cell or a cell
6:31
range first select the range of cells
6:33
where you want to clear the data
6:34
validation in the data validation
6:36
dialogue box click the clear all button
6:39
at the bottom click okay this will
6:41
remove any data validation rules from
6:43
the selected range thanks for watching
6:46
if you found this tutorial helpful
6:48
please give it a thumbs up share it with
6:50
your friends and subscribe to our
6:51
channel for more Excel tips and
6:53
tutorials don't forget to hit the Bell
6:55
icon to get notified whenever we upload
6:57
new content if you have any questions or
6:59
suggestions leave them in the comments
7:01
below see you in the next video