Apply Basic Formulas and Conditional Formatting

Formulas are the heart of Excel. With huge list of built-in formula and functions, Excel stands out from other datasheet handling applications. Formulas are used for calculating/analyzing data based on values in designated cells. It supports trigonometric, statistical and other functions. You can also create a new rule, or constraint to apply over your datasheet. This post covers writing formulas and applying conditional formatting on a basic level.

For Instance: We will start off with creating simple worksheet of students, which includes; Name of the student, and obtained Marks in their respective courses.

We need to add two new columns which shows which grade the student has secured and status of the student which shows whether he is pass or fail. So we will add two new columns having names Grade and Status respectively.

We will write a formula which evaluates the grade student has secured. By considering if secured marks are greater than/equal to 80, then he falls in A grade, greater than or equal to 75 secured B+ grade and so on and student who obtained less than 60 marks secured D grade.

The basic syntax of the formula is this:

=IF(Criteria, Action if Criteria Met, Other Action)

At the first row of Grade column, formula goes like this.

=IF(D2>=80,”A”, IF(D2>=75, “B”, IF(D2>=70, “B+”, IF(D2>=70,”B”, IF(D2>=65,”C+”, IF(D2>=60, “C”,”D”))))))

In the line of code, we are writing formula for D2 position which actually contains 60 marks in Grade column. From the formula (=IF D2 >=80,”A”,…..) inferring that if D2 position contains value which is greater than or equal to 80. the Grade ‘A’ would be printed in the designated cell and if marks are greater than or equal to 75, ‘B’ would show and so on. When we put in this formula, it will show the grade student has secured according to the formula constraints. When you will drag down the cross symbol to the end of the column, the formula will be applied to all respective cells.

Now at the first row of the Status column, we will write formula which goes like this:

=IF(E2=”A”,”Pass”,IF(E2=”B”,”Pass”,IF(E2=”B+”,”Pass”,IF(E2=”C”,”Pass”,IF(E2=”C+”,”Pass”,IF(E2=”D”,”Fail”))))))

From the above formula we can infer that if E2 position contains D, the word Fail will be seen in the Status column and in all other conditions Pass would show. Drag down the cross symbol to the end of the column to apply the formula over all the respective cells.

As shown in the above screenshot, where the grade is ‘D’ the word ‘Fail’ can be seen in the corresponding cell.

Conditional Formatting allow users to show only specific data that meets a certain criteria or condition. Navigate to Home tab and click Conditional Formatting button, you will see list of different options.

Select the Marks column, go to conditional formatting and in Highlight Cells Rules, click Less than. Enter the value 60 and select any formatting style. On writing valid value, the selected column will automatically be updated with selected formatting style. Click OK to apply.

Apply conditional formatting styles over all the data to make portion of datasheet more prominent and clear.