Table of Contents
Creating a pass or fail formula in Excel involves using conditional formatting to set specific criteria for the data entered in a cell or range of cells. This formula can be used to automatically determine whether the data meets the established criteria and display a pass or fail result accordingly. By setting appropriate conditions and applying the formula to the desired cells, it is possible to efficiently evaluate and categorize data as pass or fail in Excel. This feature is useful for various applications, such as grading systems, quality control, and data analysis.
Excel: Create a Pass or Fail Formula
You can use the following formula in Excel to return either “Pass” or “Fail” based on the grade of a particular student:
=IF(B2>60, "Pass", "Fail")
This particular formula returns “Pass” if the value in cell B2 is greater than 60 or “Fail” otherwise.
Feel free to change 60 to any value you’d like based on the value that is considered passing or failing.
The following example shows how to use this formula in practice.
Example: How to Create a Pass or Fail Formula in Excel
Suppose we have the following dataset in Excel that shows the final grade various students received in some class:
Suppose we would like to create a new column that shows whether or not each student passed the class based on whether their grade was greater than 60 or not.
We can type the following formula into cell C2 to do so:
=IF(B2>60, "Pass", "Fail")
We can then click and drag this formula down to each remaining cell in column C:
Column C now displays “Pass” for each student who had a grade greater than 60 and “Fail” for each student who had a grade less than or equal to 60.
If you’d like, you can also apply conditional formatting to the cells in column C to more easily tell which students passed the class.
To do so, highlight the cell range C2:C13, then click the Conditional Formatting icon on the Home tab, then click Highlight Cells Rules, then click Equal To:
Once you click OK, each cell that contains a value of “Pass” will be highlighted:
This allows us to more easily see which students passed and which ones did not.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: