## What Is Marksheet Format In Excel?

Marksheet in Excelis created in an Excel workbook to maintain a cumulative data for present and future analysis. It helps us keep track of the performances of the employees in a company, students in an educational organization, etc.

An** Excel Marksheet format **is an automated Marksheet that gives us the required results when we modify or update the existing data, or add new data.

##### Table of contents

###### Key Takeaways

- The
**Marksheet in Excel**is created to calculate the result of the students. - Excel functions such as
**IF, SUM, AVERAGE, COUNTIF, ROUND, etc**., are used to calculate the results, like finding the total, average, and count of students falling under specific criteria, etc., using**Marksheet format and formulas.** - We can provide an alternative output instead of the default “
**True**” or “**False**” using the conditional functions such as**IF()**and**COUNTIF(),**as we saw in the examples like output as**Pass**or**Fail**,**Promoted**or**Distinction**, etc.

### How To Make Marksheet In Excel Format?

We can **Make Marksheet In Excel Format **using the following methods, namely,

**SUM function.****Comma Method.****Colon Method (Shift Method).**

**AVERAGE function.****ROUND function.****IF function.****COUNTIF function**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### #1 – SUM Function

It adds the selected numeric cell values and returns the total value. Its syntax is,

**SUM function – Comma Method –**In this method, we enter the cell values separately in the formula using commas.

The following example depicts the marks of the subjects of the students of a class, and we will calculate the total marks using the **Excel SUM Function Comma Method**.

The steps to evaluate the values using the **Excel SUM Function Comma Method** are as follows:

**Step 1:**Select cell**F2**.

**Step 2:**Next, we will enter the**Excel SUM Formula**in cell F2.- Enter the value of ‘number1’ as B2, i.e., the sum values in cell B2.
- Enter the value of ‘number2’ as C2, i.e., the sum values in cell C2.
- Enter the value of ‘number3’ as D2, i.e., the sum values in cell D2.
- Enter the value of ‘number4’ as E2, i.e., the sum values in cell E2.

- Enter the value of ‘number1’ as B2, i.e., the sum values in cell B2.

Therefore, the complete formula is ** =SUM(B2,C2,D2,E2) **in cell F2.

**Step 3:**Press the “**Enter**” key. The result is “**353**” in the image below.

**Step 4:**Drag the formula from cell**F2**to**F6**using the fill handle in excel. The output is shown below.

**SUM function – Colon Method (Shift Method) –**In this method, the cell values are entered by selecting the cell range or dragging the cursor on the required cells.

Using the same example as above, we will calculate the marks of the subjects of the students of a class and the total marks using the **Excel SUM Function Colon Method**.

The steps to evaluate the values using the **Excel SUM Function** **Colon Method** are as follows:

**Step 1:**Select cell**F2**.

**Step 2:**Next, we will enter the**Excel SUM Formula**in cell F2.*=SUM(B2:E2)*

**Step 3:**Press the “**Enter**” key. The result is “**353**” in the image below.

**Step 4:**Drag the formula from cell**F2**to**F6**using the fill handle. The output is shown below.

**Output Observation: **Hence, both the methods of the SUM function, i.e., the Comma Method and the Colon Method, gives the same output.

### #2 – AVERAGE Function

It returns the average of the selected numerical values. The values can be numbers, percentages, or time. The function first calculates the sum of the values, and divides it by the count of the values of the list.

For instance, the data below depicts the marks of the subjects of the students of a class, and we will calculate the average marks using the **Excel AVERAGE Function**.

The steps to evaluate the values using the **AVERAGE Function** are as follows:

**Step 1:**Select cell**H2**, and enter the formula*=AVERAGE(D2:G2).*

**Step 2:**Press the “**Enter**” key. The result is “**82.5**” in the image below.

**Step 3:**Drag the formula from cell**H2**to**H6**using the fill handle. The output is shown below.

### #3 – ROUND Function

It is used to round up the numerical data according to the number of digits assigned to round the values.

**ROUND functions Arguments Explanation**

*number***:**It is the numerical value we want to round up or down. It is a mandatory argument.*num_digits***:**It is the number of digits we want to round up. It is a mandatory argument.

The following example depicts the marks and average marks of the subjects of the students of a class, and we will calculate the round value of the average marks using the **Excel ROUND Function**.

The steps to evaluate the values using the **Excel ROUND Function** are as follows:

**Step 1:**Select cell**I2**, and enter the formula*=ROUND(H2,0).*

**Step 2:**Press the “**Enter**” key. The result is “**83**” in the image below.

**Step 3:**Drag the formula from cell**I2**to**I6**using the fill handle. The output is shown below.

### #4 – IF Function

It tests the logical conditions and gives the output as true or false. In addition, it performs analytical tests for the given data using mathematical operators. The argument can have desired output other than true and false.

The following example depicts the scores of two subjects of the students of a class, and we will evaluate whether they are passing or failing using the **Excel IF Function**.

The steps to evaluate the values using the **Excel IF Function** are as follows:

**Step 1:**Select cell**D2**, and enter the formula*=IF(AND(B2>=60, C2>=90), “Pass,” “Fail”)*

**[****Note:**** The ‘logical test’ value is B2>=60 AND C2>=90, i.e., the condition values of cells B2 & C2.**** ]**

**Step 2:**Press the “**Enter**” key. The result is “**Pass**” in the image below.

**Step 3:**Drag the formula from cell**D2**to**D6**using the fill handle. The output is shown below.

### #5 – COUNTIF Function

It counts all the cells with cell values in the range as specified by the conditions.

**COUNTIF functions Arguments Explanation**

: It is a range on which the criteria argument is applied. It is a mandatory argument.*range*: It is a condition applied to the range of values argument. It is a mandatory argument.*criteria*

The following example depicts the annual scores of the subjects of the students of a class, and we will whose score is greater than 80 using the **Excel COUNTIF Function**.

The steps to evaluate the values using the **Excel COUNTIF Function** are as follows:

**Step 1:**Select cell**B7**, and enter the formula, i.e., the*=COUNTIF(B2:B6,“>80”)***‘range’ value is B2:B6,**and**the ‘criteria’ value is ‘>80’.**

**Step 2:**Press the “**Enter**” key. The result is “**2**”, i.e., in the cell range**B2**to**B6,**there are 2 cells greater than 80 [cell**B2**and**B3**].

### Important Things To Note

- All the formulas used should be written in the correct syntax, or else we will get the “#NAME?” error.
- For entering any text in the function, we should use double quotes (“ ”) as we have used while writing “Pass,” or “Fail,” etc, in the IF and COUNTIF function examples.
- If the dataset consists of non-numeric values in the Marksheet, the formulas ignore them and finds the total of the other numeric values, irrespective of the functions used.

### Frequently Asked Questions (FAQs)

**1. How to create Marksheet in Excel?**

Following are the steps to create a **Marksheet in Excel**.**1)** Insert Personal Details. The first part of our mark sheet contains the student details.**2) **Insert the Subject Names as column Headers.**3)** Insert respective Marks of the subjects of individual students.**4) **Insert Subject wise Grades**5)** Calculate Total Marks using the formulas.**6)** Calculate the Result and display the same.

**2. How to calculate grades in the Marksheet in Excel?**

To calculate the grade, we need to,**• **Add up all scores,**• **Divide by the total number of exam points, and**• **Multiply by the weighted percentage.**• **Then calculate the percentage for the project, and add the two totals together to get the final grade.

**3. Example of calculating Result from Marksheet in Excel?**

The following example depicts the annual scores of the students of a class, and we will evaluate whether they have got Distinction using the **Excel IF Function**.

The procedure to evaluate the values using the **Excel IF Function** is,**• **Select cell C2.**• **Enter the formula *=IF(B2>=80), “Distinction,” “Promoted”)***• **Press the “Enter” key.**• **Drag the formula from cell C1 to C6 using the fill handle.__[__** Note:** The ‘logical test’ value is B2>=80 AND C2>=90, the ‘value if true’ value is ‘Distinction,’, and the ‘value if false’ value is ‘Promoted,’]

The output is shown above, i.e., student details in cells

**C2, C4,**and

**C5,**are promoted, and the student details in cells

**C3**and

**C6**, are promoted with distinction.

### Download Template

This article must help understand the **Marksheet in Excel’s** formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Marksheet In Excel. Here we create marksheet in excel using Top 5 Methods along with examples & a downloadable template. You can learn more from the following articles –