Excel Formulas for College Students – Attendance, Marksheet & Budget Examples
Learn useful Excel formulas with practical examples for attendance calculation, college marksheets, semester results and monthly expenses.
Excel is one of those computer skills that many students hear about in college but do not practise properly. You may know how to open an Excel sheet, enter some data and change the colour of a table. But when a teacher asks you to calculate attendance percentages, prepare a result sheet or manage monthly expenses, things suddenly become confusing.
This happens because knowing where the buttons are is different from understanding how formulas work. Excel becomes much more useful when you start using it to solve small, real problems.
For example, imagine you have five college subjects and need to check your attendance. Instead of calculating every percentage manually, Excel can update the results whenever you enter new attendance figures. The same idea works for marksheets, grades and monthly spending.
In this guide, we will learn Excel formulas for college students with practical examples. You do not need advanced computer knowledge. If you understand basic addition, subtraction, multiplication and division, you can start learning these formulas.
If you prefer learning Excel through videos alongside this tutorial, explore the best YouTube channels for learning Excel . The important part is to practise the formulas in your own workbook while watching.
Understanding Excel Rows, Columns and Cells
Before writing formulas, it is important to understand how Excel organises data. A workbook is an Excel file, and it can contain several worksheets. Each worksheet is divided into rows and columns.
Columns are identified by letters such as A, B, C and D. Rows are identified by numbers such as 1, 2, 3 and 4. Where a row and column meet, we get a cell.
For example, B3 means column B and row 3. A formula can read the value in B3 and combine it with values from other cells.
| Row | A — Name | B — Subject | C — Marks |
|---|---|---|---|
| 1 | Name | Subject | Marks |
| 2 | Aman | Maths | 85 |
| 3 | Priya | English | 78 |
| 4 | Rahul | Science | 92 |
In this example, cell C2 contains 85, C3 contains 78 and C4 contains 92. If you want to add these marks, you can write a formula in another cell.
=SUM(C2:C4)
The answer will be 255. The colon in C2:C4 means the complete range from cell C2 to cell C4.
How to Enter a Formula in Excel
Create a new blank workbook and rename the first sheet if required.
Add some values in the cells where the formula will read them.
Click the cell where you want the answer to appear.
Begin with an equals sign (=), enter the formula and press Enter.
If you are completely new to spreadsheets and MS Office, the basic computer course learning guide can help you understand Excel alongside Word, PowerPoint and other computer skills.
Important Excel Formulas Every College Student Should Know
You do not need to learn hundreds of Excel functions in the beginning. Start with functions that solve common problems. Once you become comfortable with them, more advanced formulas will be easier.
| Function | Purpose | Example |
|---|---|---|
| SUM | Adds values | =SUM(B2:B8) |
| AVERAGE | Finds arithmetic mean | =AVERAGE(B2:B8) |
| COUNT | Counts numeric cells | =COUNT(B2:B8) |
| MAX | Finds highest number | =MAX(B2:B8) |
| MIN | Finds lowest number | =MIN(B2:B8) |
| IF | Checks a condition | =IF(B2>=40,"Pass","Review") |
| COUNTIF | Counts matching entries | =COUNTIF(B2:B8,"P") |
| SUMIF | Adds matching categories | =SUMIF(A2:A8,"Food",B2:B8) |
| ROUND | Rounds a numeric result | =ROUND(B2,2) |
| IFERROR | Handles formula errors | =IFERROR(B2/C2,"") |
Let us now use these functions in three situations that college students commonly face.
1. Excel Attendance Formula for College Students
Attendance becomes stressful when students have multiple subjects, practical classes and different numbers of lectures. Some students only check their overall percentage, but their college may also require subject-wise attendance.
A simple Excel attendance sheet can help you organise the number of classes conducted, classes attended and the percentage for every subject.
Create an Attendance Tracking Sheet
Open a blank worksheet and create these column headings in the first row.
| Subject (A) | Held (B) | Attended (C) | Attendance (D) |
|---|---|---|---|
| Mathematics | 60 | 45 | 75.00% |
| Physics | 50 | 38 | 76.00% |
| Programming | 48 | 33 | 68.75% |
| Economics | 40 | 31 | 77.50% |
| Total | 198 | 147 | 74.24% |
Attendance Percentage Formula in Excel
Suppose Mathematics is entered in A2, total classes conducted in B2 and classes attended in C2.
In D2, enter the following formula:
=C2/B2
Now select D2 and choose Percentage from Excel's number formatting options. You can increase the displayed decimal places to two.
Because Mathematics has 45 attended classes out of 60, Excel will display 75.00%.
45 / 60 × 100 = 75%
Select D2 and drag the fill handle down to D5. Excel will adjust the relative cell references for the next rows.
A Safer Attendance Formula
If the number of held classes is zero, the basic division formula can show a division error. You can also add a simple check for impossible entries.
=IF(OR(B2="",C2=""),"",IF(OR(B2<=0,C2<0>B2),"Check data",C2/B2))
This formula keeps the result blank when required values are missing, and warns you if the entered values do not make sense. Format valid results as Percentage.
Show Attendance Status Automatically
Add another column E called Attendance Status. For a sample target of 75%, enter:
=IF(NOT(ISNUMBER(D2)),"",IF(D2>=75%,"On track","Below 75%"))
This checks the calculated percentage and displays the appropriate message. You can change 75% to your college's required target.
How Many More Classes Are Needed for 75%?
This is where attendance calculations become more interesting. Suppose Programming has 48 classes conducted and you attended 33.
The current attendance is 68.75%. You cannot simply add a few classes to the attended count while keeping total classes unchanged. Every new attended class also increases the total number of conducted classes.
The correct relationship is:
(Attended + x) / (Held + x) >= 75%
Here, x represents the number of consecutive future classes you need to attend.
=IF(NOT(ISNUMBER(D2)),"",MAX(0,ROUNDUP((75%*B2-C2)/25%,0)))
Copy the formula down for other subjects. The result assumes that you attend every next class until reaching the target, with no further absences during recovery.
After attending 12 more classes, the record becomes 45 attended out of 60 conducted. This gives exactly 75%.
Remember that the mathematical attendance figure is not an official eligibility decision. Practical attendance, approved leave, lab rules and rounding policies may differ by college.
If you want to check future attendance, recovery classes and subject-wise records without building a workbook, use the 75% Attendance Calculator to understand your current position.
Count Present and Absent Days Using COUNTIF
Another way to maintain attendance is to record P for present and A for absent on individual class records. Suppose entries are stored in B2:B31.
=COUNTIF(B2:B31,"P")
=COUNTIF(B2:B31,"A")
=IF(COUNTIF(B2:B31,"P")+COUNTIF(B2:B31,"A")=0,"",COUNTIF(B2:B31,"P")/(COUNTIF(B2:B31,"P")+COUNTIF(B2:B31,"A")))
This last formula counts only P and A entries as conducted classes. Empty cells and other codes are ignored. Make sure your attendance codes are consistent and do not treat cancelled lectures as conducted classes.
2. How to Create a College Marksheet in Excel
Preparing a marksheet is one of the easiest ways to practise Excel formulas. It teaches you how to calculate totals, percentages, averages and comparisons.
A common mistake happens when subjects have different maximum marks. Some papers may be out of 100, while practicals or internal assessments may be out of 50 or 80. In such cases, the final percentage should be calculated using the total obtained and total maximum marks.
Create a Simple Student Marksheet
Use column A for Subject, B for an optional subject code, C for Marks Obtained, D for Maximum Marks and E for Percentage.
| Subject (A) | Marks (C) | Maximum (D) | Percentage (E) |
|---|---|---|---|
| English | 70 | 100 | 70% |
| Programming | 86 | 100 | 86% |
| Physics | 64 | 80 | 80% |
| Mathematics | 72 | 100 | 72% |
| Lab | 45 | 50 | 90% |
| Total | 337 | 430 | 78.37% |
Calculate Total Obtained Marks
If obtained marks are entered from C2 to C6, use the SUM function.
=SUM(C2:C6)
The result is 337.
Calculate Total Maximum Marks
=SUM(D2:D6)
The result is 430.
Calculate Each Subject Percentage
In cell E2, use:
=IF(OR(C2="",D2=""),"",IF(OR(D2<=0,C2<0>D2),"Check data",C2/D2))
Format E2 as Percentage and copy it down to E6.
Calculate Overall Marksheet Percentage
=SUM(C2:C6)/SUM(D2:D6)
Format the result as Percentage with two decimal places.
Notice something important here. If you simply average all five subject percentages, you get 79.6%. But the actual total-marks percentage is approximately 78.37%.
They differ because the subjects do not all have equal maximum marks. For a total-marks percentage, using total obtained marks divided by total maximum marks is the correct method when the institution counts all these marks together.
If you prefer entering marks in a ready-made tool, the marks percentage calculator can help with different subject totals, required marks and percentage checks.
Use IF to Check a Sample Performance Target
Suppose you want to see whether each subject has reached a personal target of 40%. This is only a practice condition, not an official college passing rule.
Add a column F called Target Status.
=IF(NOT(ISNUMBER(E2)),"",IF(E2>=40%,"Target met","Review"))
Excel will display Target met or Review, depending on the percentage. Change the threshold according to the purpose of your practice sheet.
Find the Highest and Lowest Marks
=MAX(C2:C6)
=MIN(C2:C6)
In this example, the highest raw marks are 86 and the lowest are 45. However, raw marks should not be used to rank performance across subjects with different maximum marks.
To find the highest and lowest subject percentages instead, use:
=MAX(E2:E6)
=MIN(E2:E6)
The highest is 90% in Lab, while the lowest is 70% in English.
3. How to Calculate SGPA Using Excel
Many colleges and universities calculate semester results using subject credits and grade points rather than only the marks percentage.
A subject carrying four credits usually contributes more to a credit-weighted average than a subject carrying two credits. That is why SGPA should not automatically be calculated by averaging every subject's grade point equally.
Example SGPA Worksheet
| Subject | Credits (B) | Grade Point (C) | Weighted Points |
|---|---|---|---|
| Programming | 4 | 9 | 36 |
| Mathematics | 3 | 8 | 24 |
| Database Lab | 2 | 10 | 20 |
| Communication | 3 | 7 | 21 |
| Total | 12 | — | 101 |
The weighted points are calculated by multiplying the credits of each subject by its grade point.
=B2*C2
Enter this in D2 and copy down to D5 if D is your weighted-points column.
=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)
SUMPRODUCT multiplies each corresponding credit and grade point, then adds the products.
(36+24+20+21)/12 = 8.4167
Rounded to two decimal places, the sample SGPA is 8.42.
For multiple semesters, target scores and a more complete credit-based calculation, explore the SGPA, CGPA and Target CGPA Calculator .
Is CGPA the Same as Percentage?
No. CGPA is based on an institution's grading method. Percentage represents a marks-based value or an officially prescribed equivalent.
Do not assume that multiplying every CGPA by 9.5 or 10 is correct. Different universities and regulations can use different conversion methods.
Before entering a converted percentage into an admission or placement form, check the applicable university rule. The CGPA to Percentage Calculator can help you apply the appropriate formula.
4. Student Monthly Budget Tracker in Excel
Managing money can become confusing when you are living in a hostel, travelling to college or paying for books, mobile recharges and everyday food.
Most small expenses do not feel important individually. But by the end of the month, students sometimes cannot remember where their allowance went.
Excel can help you organise these expenses into simple categories. You can see your total spending, compare it with your available allowance and identify which categories take the most money.
Create a Monthly Expense Sheet
Use column A for Date, B for Category, C for Description and D for Amount. Enter the amounts as ordinary numbers and format the cells as currency or Indian Rupees if needed.
| Date (A) | Category (B) | Description (C) | Amount (D) |
|---|---|---|---|
| 1 Nov | Rent | Hostel room | 3500 |
| 3 Nov | Food | Groceries | 1200 |
| 4 Nov | Travel | Bus pass | 300 |
| 7 Nov | Books | College books | 650 |
| 12 Nov | Food | Meals | 900 |
| 18 Nov | Mobile | Recharge | 250 |
| 22 Nov | Travel | Transport | 400 |
| 25 Nov | Food | Snacks | 350 |
| Total Expenses | 7550 | ||
Calculate Total Monthly Expenses
=SUM(D2:D9)
Excel adds all eight transactions, giving a total expense of ₹7,550.
Calculate the Remaining Monthly Budget
Suppose your monthly available allowance is ₹10,000. Enter 10000 in H2 and use H3 for your total expenses.
=SUM(D2:D9)
=H2-H3
You have ₹2,450 remaining from your sample allowance. This is calculated before any expenses you have not entered.
Find Food Expenses Using SUMIF
Now imagine you want to know how much was spent only on food. Instead of adding those entries manually, use SUMIF.
=SUMIF(B2:B9,"Food",D2:D9)
Excel checks the category column, finds rows labelled Food and adds the amounts from column D.
The result is ₹2,450.
Find Travel Expenses
=SUMIF(B2:B9,"Travel",D2:D9)
The answer is ₹700.
Count the Number of Food Transactions
=COUNTIF(B2:B9,"Food")
The result is 3 because the Food category appears three times in the example.
Monthly Budget Category Comparison
This simple chart shows how the sample spending is divided. You can create a similar chart in Excel after summarising each expense category.
Show a Budget Warning Using IF
You can also ask Excel to display a message when expenses exceed your available budget.
=IF(H4<0,"Over budget","Within budget")
If the remaining balance in H4 is negative, the message will be Over budget. Otherwise it will display Within budget.
Calculate Spending for a Selected Month
If you want to keep expenses from several months in one worksheet, enter actual Excel dates in column A. Put the first day of the month you want to analyse in H10.
=SUMIFS($D$2:$D$100,$A$2:$A$100,">="&H10,$A$2:$A$100,"<"&EDATE(H10,1))
This formula adds transactions from the selected month's first day up to, but not including, the first day of the next month.
Make sure H10 contains a real date representing the first of the selected month. Dates stored as plain text may not work correctly.
If you are a BCA student, an expense tracker can also become a useful beginner programming project. The BCA second-year coding and project roadmap explains how to build practical projects around everyday problems.
5. Excel Formulas You Can Use in College Assignments
Attendance, marksheets and budgets are only the beginning. The same functions can also be used for laboratory records, event attendance, survey data, project reports and placement-related exercises.
| College Task | Formula | Purpose |
|---|---|---|
| Average test marks | =AVERAGE(C2:C10) | Calculate mean of numeric scores |
| Highest score | =MAX(C2:C10) | Find largest mark |
| Lowest score | =MIN(C2:C10) | Find smallest mark |
| Count students | =COUNTA(A2:A30) | Count non-empty name cells |
| Count marks at least 40 | =COUNTIF(C2:C30,">=40") | Count scores meeting a sample target |
| Round a result | =ROUND(C2,2) | Round to two decimal places |
| Find total entries | =COUNT(C2:C30) | Count numeric entries |
Relative and Absolute Cell References
When you copy a formula down, Excel normally changes relative references automatically.
For example:
=B2*C2
When copied one row down, it becomes =B3*C3.
But sometimes you want Excel to keep one particular cell fixed. The dollar sign makes a reference absolute.
=B2*$H$2
If H2 contains a fixed rate, copying the formula down changes B2 to B3 but keeps $H$2 unchanged.
This is useful for repeated calculations involving one fixed rate, target or multiplier.
6. Common Excel Formula Errors and Their Solutions
When learning Excel, formula errors are normal. Instead of deleting everything and starting again, check what each error means.
| Error | What It Usually Means | What to Check |
|---|---|---|
| #DIV/0! | Division by zero or an empty divisor | Check denominator |
| #VALUE! | Wrong data type or formula argument | Check text and numbers |
| #NAME? | Unrecognised function or name | Check spelling and quotation marks |
| #REF! | Invalid cell reference | Check deleted or moved cells |
| #### | Value cannot be displayed in the column width, or certain invalid date/time displays | Widen column and check formatting |
Why Is My Formula Showing as Text?
Suppose you type =SUM(A2:A8) but Excel displays the formula instead of the answer.
The cell may be formatted as Text, or Excel's Show Formulas option may be enabled.
Change the cell format to General, edit the formula and press Enter. If formulas appear throughout the worksheet, check Show Formulas on the Formulas tab.
Why Does Excel Show the Wrong Percentage?
A common mistake is multiplying a ratio by 100 and then applying Percentage formatting again.
Enter =45/60 and format the cell as Percentage.
Result: 75%
Enter =45/60*100 and format the result as Percentage.
Result: 7500%
Why Is Excel Rejecting a Correct Formula?
Check whether your Excel installation expects commas or semicolons between function arguments. The examples here use commas, which are common in English-language Excel settings. Some regional configurations use semicolons instead.
Also check that numeric values are entered as numbers rather than text with unnecessary currency symbols or spaces.
7. Excel Practice Exercises for College Students
The best way to learn formulas is to create a sheet yourself. Try these exercises in a fresh workbook before checking the answers.
Exercise 1: Attendance Calculation
A student attended 54 classes out of 72 conducted classes. Calculate the attendance percentage.
Show Answer
Use =54/72 and apply Percentage formatting. The result is 75%.
Exercise 2: College Marksheet
A student obtained 337 marks out of 430 total marks. Calculate the overall percentage.
Show Answer
Use =337/430 and format as Percentage with two decimal places. The result is 78.37%.
Exercise 3: Monthly Budget
Your monthly allowance is ₹10,000 and you have spent ₹7,550. How much remains?
Show Answer
Use =10000-7550. The balance is ₹2,450.
Exercise 4: Count Attendance Entries
Cells B2:B6 contain P, A, P, P, A. Count present entries.
Show Answer
Use =COUNTIF(B2:B6,"P"). The result is 3.
Exercise 5: Calculate Average Marks
Three tests have marks 72, 84 and 90. Find their arithmetic average.
Show Answer
Use =AVERAGE(72,84,90) or enter the marks in cells and refer to their range. The average is 82.
8. A Simple Seven-Day Excel Learning Plan
If you are starting Excel from zero, you do not need to complete every advanced topic immediately. Practise one useful task at a time.
| Day | Topic | Practice Task |
|---|---|---|
| Day 1 | Excel Basics | Rows, columns, cells and formatting |
| Day 2 | SUM and AVERAGE | Create a small marks table |
| Day 3 | Percentages | Build an attendance tracker |
| Day 4 | IF and COUNTIF | Add status and attendance counts |
| Day 5 | SUMIF | Calculate category-wise expenses |
| Day 6 | SUMPRODUCT | Calculate sample credit-weighted SGPA |
| Day 7 | Mixed Practice | Build three worksheets without copying |
After completing this plan, you should be able to explain how your formulas work and why particular functions were selected.
9. Why Excel Skills Are Useful for College and Career
Excel is not only useful for college assignments. It is also used in office administration, reporting, operations, finance support, data entry and many analytical tasks.
For students interested in data analysis, spreadsheets can be a starting point for understanding tables, data cleaning, calculations and basic charts.
Excel alone does not make someone a data analyst, but it helps build useful foundations before moving towards SQL, data visualisation and programming.
If you are exploring analytical and development career paths, our guide on Data Analyst vs Software Developer for freshers explains how the roles and required skills differ.
10. Common Mistakes Students Should Avoid
- Only watching tutorials: Open Excel and recreate each example yourself.
- Typing totals manually: Use formulas so the results update when values change.
- Ignoring cell references: Check the exact rows and columns included in every calculation.
- Incorrect percentage formatting: Understand the difference between a decimal ratio and its display as a percentage.
- Mixing text and numbers: Store marks, attendance counts and money amounts as numeric values.
- Assuming every grading rule is the same: Match formulas with your institution's actual policy.
- Using incomplete expense data: Record transactions regularly to make the budget meaningful.
- Sharing private records: Use fictional examples when sharing practice files publicly. Do not upload real student marks or personal attendance records without authorisation.
Frequently Asked Questions About Excel Formulas
Which Excel formulas should college students learn first?
Start with SUM, AVERAGE, COUNT, MAX, MIN and basic division for percentages. After that, learn IF, COUNTIF, SUMIF, ROUND and SUMPRODUCT through practical examples.
How do I calculate attendance percentage in Excel?
Divide attended classes by classes conducted and format the result as Percentage. For example, if B2 contains total classes and C2 contains attended classes, use =C2/B2.
How can I calculate the percentage of a marksheet?
Add all eligible obtained marks, add their corresponding maximum marks and divide the two totals. In Excel, you can use =SUM(C2:C6)/SUM(D2:D6) when those ranges contain the appropriate marks.
What is the difference between SUM and SUMIF?
SUM adds the selected numeric values. SUMIF adds values only when another cell or range meets a condition, such as adding expenses labelled Food.
Can Excel calculate SGPA and CGPA?
Yes, Excel can perform credit-weighted calculations using SUMPRODUCT and SUM. However, the credit structure, grade points and official rules must match your university.
How do I make a student budget in Excel?
Create columns for date, category, description and amount. Use SUM to calculate total expenses, SUMIF for category totals and subtraction to check the remaining balance.
Can I use these formulas in Google Sheets?
Many common formulas such as SUM, IF, COUNTIF, SUMIF and SUMPRODUCT also work in Google Sheets. Some features, formatting options and regional settings may differ.
Is Excel useful for students from non-technical courses?
Yes. Students in BA, B.Com, BBA, B.Sc and other courses can use Excel for surveys, attendance, data organisation, assignments, personal budgets and basic analysis.
Conclusion
Learning Excel becomes easier when you connect formulas with everyday college activities. A simple attendance tracker can teach percentages and IF statements. A marksheet can help you understand SUM, AVERAGE and subject comparisons. A monthly budget can introduce SUMIF and category-based calculations.
Start with one worksheet and practise the formulas without copying every step. When something goes wrong, check the cell references, numeric values and formula conditions. Small mistakes are part of learning.
Once you understand these beginner examples, you can move towards charts, PivotTables, data analysis and more advanced spreadsheet work.
The best way to learn Excel is to use it regularly for real tasks. Even a simple college attendance or expense sheet can help you build a useful computer skill.
