EXCEL TRAINING SESSION
DATA VALIDATION
Course teacher
Dr. Sujith Kumar S H
What Is Excel Data Validation?
Data validation in Microsoft Excel is a feature that allows you to control the
type of data entered into a worksheet to improve the accuracy of the data. For
example, Excel data validation enables users to limit data entries to a selection
from a drop-down list and to restrict certain data entries, such as dates or
numbers outside of a predetermined range. Data validation can also help control
formulas and the input from those formulas. You can even craft custom Excel
data validation messages that help guide users toward the correct data entry
when they hit a limit. As a result, Excel data validation helps reduce the amount
of unstandardized data, errors, or irrelevant information in worksheets.
How to Validate Data in Excel
To get started, select the cells you would like to use, visit the Data tab, and click
on Data Validation.
Validation rules can be created to ensure data entered is a valid date, number
format, time, text length, whole number, or decimal. Custom formulas can be
used for specific expressions. Data validation rules are created in the settings
tab, messages can be created as data is entered in the Input Message tab, and
error messages can be created in the Error Alert tab.
How to Make a Drop-Down List in Excel
Drop-down lists can facilitate accurate data entry in spreadsheets by restricting
input to predefined choices specified in the drop-down. To create a drop-down
list, first enter all possible options you would like included in the validation list.
Then, visit the Data tab, click Data Validation, select List, and add the cells you
want to use in the data validation list in the Source field.
The Input Message tab allows for custom messages to be included and the Error
Alert tab can display a message if invalid data that is not included in your list is
entered.
Smart or Dynamic Drop-Down Lists
Drop-down lists can be created to include new categories as options
automatically. When creating a list of options to include in a drop-down list,
turn the list into a table by typing CTRL + T or navigating to Home and Format
as Table. Then, you would follow the same steps to create the drop-down list:
navigate to the Data tab, select Data Validation, select List, and add the
dynamic list cells in the table in the Source field.
Conditional formatting can be applied to the dynamic list to prevent duplicate
entries. To highlight duplicate entries, first select the dynamic list, navigate to
the Home tab, select Conditional Formatting, Highlight Cell Rules, and
Duplicate Values.
Remove Data Validation in Excel
To clear previous data validation rules in Excel, first select the cells where you
would like to remove rules. Visit the Data tab, click on Data Validation, and
then select Clear All. To remove a drop-down list, you would follow the same
steps of selecting cells using a list, visiting the Data tab, selecting Data
Validation, and then Clear All. Validation rules will then be removed from the
selected cells and not the entire sheet.
The Limitations of Data Validation in Excel
Using data validation in Excel to set certain restrictions in your worksheet can
help guide users toward more accurate data entries, but ultimately, these
restrictions can easily be bypassed. If a user copies data from a cell without
Excel data validation to a cell with Excel data validation, the validation is
destroyed (or replaced). So, despite using data validation in Excel to control
input, analysts can still be left with messy data sets because of this way to
bypass any restrictions. In addition, if the validation is destroyed or replaced, it
could affect how future data entries are input and generate future analysis
complications.
Loss of Validation Rules: Copying data from cells without data
validation to validated cells can result in the inadvertent removal or
replacement of validation rules, undermining the intended data controls.
Challenges of Data Collection: Many businesses deal with data
collected from various sources, often needing more control over how it’s
entered and managed, leading to messy spreadsheets.
Manual Repairs: When Excel data validation isn’t feasible or breaks
down, validating data within spreadsheets becomes arduous, involving
manual searches and replacements.
Time-Consuming Data Preparation: This process adds significant time
to data preparation, known as the most time-consuming part of analytics
projects.
The Need for Data Preparation
While data validation in Excel remains a reliable feature in many instances, as
data grows beyond manageable limits, Excel’s validation capabilities become
less practical. In such cases, it becomes essential to consider alternative
resources for handling messy data. Data preparation tools play a pivotal role in
this scenario, streamlining the process and reducing the time spent on data
analysis while mitigating potential complications associated with Excel’s
validation. Excel remains a valuable tool, but analysts can maximize its benefits
by incorporating data preparation tools into their workflow. Even when data
validation in Excel is applicable and advantageous, proper data preparation is
often necessary to rectify any discrepancies that may have arisen during the
validation phase, such as instances where users override Excel’s validation
rules.
Data preparation tools are specifically designed to transform raw data into a
usable format for efficient data analytics. This involves tasks like removing null
values, standardizing data entries, and eliminating duplicates. While Excel’s
features can address some of these issues during data entry, they can be
circumvented or insufficient for managing large and expanding datasets.
Therefore, data preparation tools are indispensable for ensuring that analysts
save time and extract maximum value from tools like Excel, especially when
dealing with extensive datasets.
Expand Validation and Preparation Capabilities with Alteryx
When it comes to data preparation tools, Alteryx is widely recognized as the
leader in data preparation. Alteryx greatly enhances the capabilities of data
validation and analysis through the following ways:
Analyze large datasets
Automate data preparation and cleaning to reduce errors and manual
validation
Seamlessly integrate and blend multiple sources
Advanced analytics capabilities such as predictive, spatial, and statistical
functions
Alteryx offers a robust platform enabling seamless data blending, intuitive
analytics, and powerful AI-driven tools. Automate manual tasks, uncover
hidden patterns, and transform data into actionable insights – all without writing
a single line of code.
