MS Excel data validation feature allows you to set up certain rules that dictate what can be entered into a cell. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and 10. If the user makes an invalid entry, you can display a custom message as shown below.

Validation Criteria

To specify the type of data allowable in a cell or range, follow the steps below, which shows all the three tabs of the Data Validation dialog box.
• Select the cell or range.
• Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box having 3 tabs settings, Input Message and Error alert.

Settings Tab

Here you can set the type of validation you need. Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your choice.
• Any Value − Selecting this option removes any existing data validation.
• Whole Number − The user must enter a whole number.For example, you can specify that the entry must be a whole number greater than or equal to 50.
• Decimal − The user must enter a number. For example, you can specify that the entry must be greater than or equal to 10 and less than or equal to 20.
• List − The user must choose from a list of entries you provide. You will create drop-down list with this validation. You have to give input ranges then those values will appear in the drop-down.
• Date − The user must enter a date. You specify a valid date range from choices in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2013, and less than or equal to December 31, 2013.
• Time − The user must enter a time. You specify a valid time range from choices in the Data drop-down list. For example, you can specify that the entered data must be later than 12:00 p.m.
• Text Length − The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).
• Custom − To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either TRUE or FALSE).

Input Message Tab

You can set the input help message with this tab. Fill the title and Input message of the Input message tab and the input message will appear when the cell is selected.

Error Alert Tab

You can specify an error message with this tab. Fill the title and error message. Select the style of the error as stop, warning or Information as per you need.

Data Filtering in Excel

Filters in MS Excel

Filtering data in MS Excel refers to displaying only the rows that meet certain conditions. (The other rows gets hidden.)
Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set filter to do this. Follow the below mentioned steps to do this.
• Place a cursor on the Header Row.
• Choose Data Tab » Filter to set filter.

• Click the drop-down arrow in the Area Row Header and remove the check mark from Select All, which unselects everything.
• Then select the check mark for Size 36 which will filter the data and displays data of Shoe Size 36.
• Some of the row numbers are missing; these rows contain the filtered (hidden) data.
• There is drop-down arrow in the Area column now shows a different graphic — an icon that indicates the column is filtered.

Using Multiple Filters

You can filter the records by multiple conditions i.e. by multiple column values. Suppose after size 36 is filtered, you need to have the filter where color is equal to Coffee. After setting filter for Shoe Size, choose Color column and then set filter for color.

So, this brings us to the end of blog. This Tecklearn ‘How to perform Data Validation and Data Filtering in Excel’ blog helps you with commonly asked questions if you are looking out for a job in Microsoft Excel. If you wish to learn Excel and build a career in Business Intelligence domain, then check out our interactive, Microsoft Power BI Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Microsoft Power BI Training

Microsoft Power BI Training

About the Course

Microsoft Power BI Training at Tecklearn will help you achieve expertise in business analytics Our best online training course teaches you all important concepts like Power BI Desktop, Mobile, Power Query & Power Pivot, Data modelling, visualization, creating dashboards and reports, DAX, etc. As part of this program, you will work on real-world projects. Also, our Power BI course curriculum will equip you with all the key skills that are required to clear the Microsoft Power BI Certification exam (70-778).

Why Should you take Microsoft Power BI Training?

• The average annual pay for a Power BI Professional is $114,000 . -PayScale.com.
• Cognizant, Dell, KPMG, Hitachi, Wipro, Avanade, Annik Inc, Brillio and 45,000 MNCs across 185 countries use Power BI and it has a market share of around 7% globally.
• Microsoft Power BI has been ranked at No.1 position in the Gartner Magic Quadrant for Analytics and Business Intelligence Platforms for 13 consecutive years.

What you will Learn in this Course?

Introduction to Microsoft Power BI
• Microsoft Power BI Introduction
• Power BI Products
• Power BI Architecture
• Installing Power BI
• Connecting to Data Sources
Power BI Workflow
• Key features of Power BI workflow
• Power BI Vs MSBI
• History of Power BI
• Power BI Products
• Data modelling and relationships
Power Query for Data Transformation
• Power Query for Data Transformation
• Learning about Power Query for self-service ETL functionalities
• Working with Excel data
• Introduction to Query Editor
• Data transformation
• Pivot and UnPivot
• Merge Join, relational operators, date, time calculations, working with M functions
• Summary Tables
• Writing custom functions and error handling
• M advanced data transformations
Filters and Drill Down Report
• Visualization Filter
• Page Level Filter
• Report LEVEL Filter
• DRILL Through Filter or Report
• BookMark Report
Power Pivot for Data Modelling
• What is SSAS
• Data Modelling
• Star Schema
• Snowflake Schema
• Introduction to MDDB and Tabular
• Data Access Modes
• Tabular with DAX
• Power BI with DAX
• DAX Functions
Data Analysis Expression – DAX Queries
• TABULAR with DAX
• Power BI with DAX
• Calculated Members, Row, Filter & Evaluation Context
• Cumulative Charts
• Calculated Tables, ranking and rank over groups
• DAX advanced features
Power BI Desktop & Administration
• Reports and Visualization
• Learning about data modelling and data relationships
• Deploying data gateways
• Reports and Dashboards
• On Premises Data Gateway
• Gateway Clusters
Introduction to Power Q & A
• Power Service
• Power Q & A best practices
• Integrating with SaaS applications
• Gateway

491