• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

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

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
  • Click on the DATA tab
  • Select the cells C2 to C6 (The cells that will be used to record the scores)

Getting Started with Microsoft Excel

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

Getting Started with Microsoft Excel

  • You will get the following dialogue window

Getting Started with Microsoft Excel

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

Getting Started with Microsoft Excel

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

Getting Started with Microsoft Excel

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

Getting Started with Microsoft Excel

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

Getting Started with Microsoft Excel

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

Getting Started with Microsoft Excel

  • Enter "ja" and click on "OK" button
  • You should be able to see only the results for Jane and James.

Getting Started with Microsoft Excel

Group and Ungroup

Groups allow us to view easily and hide unnecessary details from either columns or rows. In addition to that, we can also use groups to analyse data that belongs to a common category. Let's illustrate this with an example. We will use the student scores example above.

Getting Started with Microsoft Excel

  • Right click on the score and select insert column. Name the name column gender.
  • Change James to Juanita. Put female for Janet and Juanita. Put male for the rest of the students. You sheet should look as follows.

Getting Started with Microsoft Excel

We will now group the females together and display their average score and do the same for the males.

  • Click on DATA tab on the ribbon
  • Select all the columns and rows with data
  • Click on Group drop down button as shown in the image below

Getting Started with Microsoft Excel

You will get the following window

Getting Started with Microsoft Excel

  • Make sure Rows options is selected
  • Click on OK button
  • You will get the following preview

Getting Started with Microsoft Excel

  • We will now calculate the average scores for females and males
  • Select the whole data as shown below

Getting Started with Microsoft Excel

Click on Subtotal drop down button under DATA tab

Getting Started with Microsoft Excel

You will get the following window

Getting Started with Microsoft Excel

  • Set "At each change" into gender
  • Set "Use function" to average
  • Select "Add subtotal" to Score
  • Click on "OK" button

Getting Started with Microsoft Excel

 

Download the above Excel Code

Adding images to spreadsheets

At times, you would like to brand the documents printed in excel with letterhead information and print with the company logo, etc. Excel has features that allow you to import images into Excel. The command for adding images is found under the INSERT tab on the ribbon.

Getting Started with Microsoft Excel

You will get the following dialogue window

Getting Started with Microsoft Excel

  • You can browse to any folder on your computer that has pictures, and you can select any picture of your choice.
  • You will get results similar to the ones shown below

Getting Started with Microsoft Excel

Tutorial exercise 4

Use the INSERT tab pictures command to add a picture of your choice to the worksheet.

Summary

In this article, we have learnt how to perform basic arithmetic operations using Excel, format the data, and apply validation rules, filter data and how to take advantage of groups to further analyse data and improve presentation.

 

YOU MIGHT LIKE: