Microsoft Excel Tutorial for Beginners: Lesson 3

[soybean]

Data Validation in Excel: Filters, Grouping, Sorting Examples​

In this tutorial, we are going to cover the following topics.

Data validation
Data filters
Group and Ungroup
Adding images to spreadsheets

 

[soybean]

Data validation​

Data validation is very important in the sense that it helps us avoid mistakes that can be avoided. Let’s assume you are recording student exam marks and you know the minimum is 0 and the maximum is 100. You can take advantage of validation features to ensure that only values between 0 and 100 are entered.
Add a new sheet in your workbook by clicking on the plus button at the bottom of the worksheet.

Add a column for S/N, Name and Score. Your sheet should look as follows

S/N	Name	Score
1	Jane
2	James
3	Jones
4	Jonathan
5	John

 

[soybean]

• Click on the DATA tab
• Select the cells C2 to C6 (The cells that will be used to record the scores)

 

[soybean]

• Click on Data validation drop down list.
• Click on Data validation.

 

[soybean]

• You will get the following dialogue window

 

[soybean]

• Click on Error Alert tab
• Enter the alert title and message as shown in the diagram below.

 

[soybean]

• Click on OK button
• Try to enter a score greater than 200. You will get the following error message

 

[soybean]

Data filters​

Data filters allow us to get data that matches our desired criteria. Let’s say we want to show the results of all the students whose names start with “ja” or get scores that are less than, greater than or equal to a certain value, we can use filters to get such data.
Select the name and scores columns as shown below

 

[soybean]

• Click on DATA tab on the ribbon
• Click on Sort & Filter drop down list as shown in the image below

 

[soybean]

• Click on the Name Filter
• Select text filters
• Select begins with
• You will get the following window.