Here are ten Excel assignments you can finish in an afternoon: a marksheet total, pass or fail with IF, an attendance count, SUMIF and SUMIFS on a sales table, a VLOOKUP price list, text cleaning, dates, conditional formatting and a pivot table. Each one gives the data, the task and an answer formula to check against.
Functions
Excel Assignments for Practice: 10 Tasks with Answers
Ready to practice?
Apply what you just learned with interactive exercises.
Type the data into a blank workbook, write your own formula first, then compare. Any formula that returns the same result is right; the answers below are one good way, not the only way.
Assignment 1: Total and percentage on a marksheet
Type this table in A1:D6, with the headings in row 1.
| A | B | C | D | |
| 1 | Name | Maths | Science | English |
| 2 | Asha | 78 | 85 | 72 |
| 3 | Ravi | 56 | 48 | 61 |
| 4 | Meena | 92 | 88 | 95 |
| 5 | Kiran | 35 | 42 | 38 |
| 6 | Sana | 66 | 71 | 58 |
E2: =SUM(B2:D2)
F2: =E2/300
Format column F as a percentage. Asha's total is 235 and her percentage is 78.33%. This is the SUM function at its simplest.
Assignment 2: Pass, fail and grade with IF
Task: in G1 type Result. A student passes with 40% or more. Show Pass or Fail. Then in H1 type Grade: A for 75% and above, B for 60% and above, C for 40% and above, otherwise Fail.
G2: =IF(F2>=0.4,"Pass","Fail")
H2: =IF(F2>=0.75,"A",IF(F2>=0.6,"B",IF(F2>=0.4,"C","Fail")))
Kiran is the only Fail (38.33%). Asha and Meena get an A, Sana a B and Ravi a C. Practise the IF function before you add the nested version. On Excel 2019 or later, IFS can replace the nesting.
Assignment 3: Count attendance with COUNTIF
On a new sheet, type Asha's name in A2 and her ten days of attendance in B2:K2, with P for present and A for absent: P, P, A, P, P, P, A, P, P, P.
Task: count the days present, then work out the attendance percentage.
L2: =COUNTIF(B2:K2,"P")
M2: =L2/COUNTA(B2:K2)
Asha was present 8 days, so attendance is 80%. COUNTIF counts the cells that match; COUNTA counts every filled cell.
Assignment 4: Total by region with SUMIF
Open a second sheet and type this sales table in A1:D9.
| A | B | C | D | |
| 1 | Region | Product | Units | Amount |
| 2 | North | Pen | 120 | 1200 |
| 3 | South | Pen | 90 | 900 |
| 4 | North | Book | 40 | 2000 |
| 5 | East | Book | 55 | 2750 |
| 6 | South | Book | 30 | 1500 |
| 7 | East | Pen | 80 | 800 |
| 8 | North | Pen | 60 | 600 |
| 9 | South | Pen | 45 | 450 |
=SUMIF(A2:A9,"North",D2:D9)
The answer is 3800. Try East (3550) and South (2850) as well. The SUMIF practice uses a real workbook.
Assignment 5: Two conditions with SUMIFS
Task: find the amount for Pen sales in the North region only.
=SUMIFS(D2:D9,A2:A9,"North",B2:B9,"Pen")
The answer is 1800 (the two North Pen rows, 1200 and 600). Note that in SUMIFS the range to add comes first, unlike SUMIF. The SUMIFS practice drills this order.
Assignment 6: A price list with VLOOKUP
On the sales sheet, type a price list in F1:G5 and an order in A12:B12.
| Item | Price |
| Pen | 10 |
| Book | 50 |
| Ruler | 15 |
| Eraser | 5 |
Task: look up the price and work out the cost of the order.
=VLOOKUP(A12,F2:G5,2,FALSE)*B12
The answer is 180 (15 times 12). FALSE asks for an exact match. The VLOOKUP lesson explains why, and XLOOKUP vs VLOOKUP shows the newer option.
Assignment 7: Clean and split a name
Type asha RAO (with extra spaces and mixed capitals) in A2 of a new sheet.
Task: tidy it to "Asha Rao", then pull out the first name and the last name.
B2: =PROPER(TRIM(A2))
C2: =LEFT(B2,FIND(" ",B2)-1)
D2: =MID(B2,FIND(" ",B2)+1,50)
B2 gives Asha Rao, C2 gives Asha and D2 gives Rao. See TRIM, PROPER, FIND and MID.
Assignment 8: Dates, days and months
Type 1 April 2024 in A2 and 30 June 2024 in B2, both as real dates.
Task: find the days between them, the working days (Monday to Friday), and the complete months.
C2: =B2-A2
D2: =NETWORKDAYS(A2,B2)
E2: =DATEDIF(A2,B2,"M")
The answers are 90 days, 65 working days and 2 months. NETWORKDAYS counts both end dates. DATEDIF is a hidden function: Excel does not suggest it as you type, but it works.
Assignment 9: Highlight low marks
On the marksheet from Assignment 1, select B2:D6.
Task: colour every mark below 40 red. Go to Home, Conditional Formatting, Highlight Cells Rules, Less Than, type 40 and choose a red fill.
Two cells turn red: Kiran's 35 in Maths and 38 in English. Change a mark and the colour follows it, which is the point of conditional formatting.
Assignment 10: A pivot table and a chart
Use the sales table from Assignment 4.
Task: select A1:D9, then Insert, PivotTable. Put Region in Rows and Amount in Values (Sum). Then, with the pivot table selected, Insert, Column Chart.
Check your pivot table against Assignments 4 and 5: North 3800, South 2850, East 3550 and a grand total of 10200. If a number is different, a typo crept into the data. Pivot Tables 1 and Pivot Tables 2 walk through it on a real dataset.
Five project ideas when you want something bigger
An Excel project is the same skills in one workbook that does a real job. Each of these needs only what you just practised.
- Class gradebook: the marksheet with total, percentage, grade, a rank with =RANK(F2,$F$2:$F$6) and a summary of pass and fail counts with COUNTIF.
- Monthly budget: income and spending by category, totals with SUMIF, and the balance left with =B2-C2.
- Stock register: items, stock and a reorder level, with =IF(B2<=C2,"Reorder","OK") and conditional formatting on the word Reorder.
- Sales report: the table above, with SUMIFS by region and product and a pivot table and chart on top.
- Attendance register: a month of P and A marks per student, COUNTIF for the days present and a percentage, with red below 75%.
Practice these in the browser
Writing the formula on paper is not the same as getting it marked. On LogicExcel you type real formulas into a live grid and are marked on the result as you type, with no software to install. Start with the Excel practice exercises and the Excel exercises for beginners. If you want a path, the Excel courses start free, and each one ends in a marked exam.
FAQ
How do I practise Excel assignments at home?
Type the data into a blank workbook, write the formula yourself, and only then compare it with an answer. Repeat each assignment with different numbers until you can write the formula without looking. A practice site that marks your formula as you type tells you straight away if it is wrong.
What should a basic Excel assignment include?
A small table of data and a task that needs a formula: totals with SUM, a decision with IF, counting with COUNTIF, adding by condition with SUMIF or SUMIFS, and a lookup with VLOOKUP or XLOOKUP. Teachers and employers usually add formatting, a pivot table and a chart.
Is my formula wrong if it differs from the answer?
No. Excel only cares about the result. =B2+C2+D2, =SUM(B2:D2) and =SUM(B2,C2,D2) all give the same total, so all are right. Check the number, not the spelling of the formula.
Can I do Excel assignments without Microsoft Excel installed?
Yes. Any spreadsheet that has these functions works, and in your browser you can practise on LogicExcel without installing anything. IFS needs Excel 2019 or later, and XLOOKUP needs Excel 2021 or later, Microsoft 365 or Excel for the web.
Keep going
Guides people read next.
Indeed Excel Assessment Practice: Facts and Practice Tasks
Indeed Excel assessment practice: what Indeed has said about its tests, what it has not published, and our own practice tasks to build the skills.
20 minMO-200 Excel Exam Practice: Skills, Tasks and a Study Plan
Practice for the MO-200 Excel exam: Microsoft's objectives, worked formula tasks, common errors and a two-week plan you can start today.
22 minMO-201 Excel Expert Exam Practice: Skills and Formula Tasks
Prepare for the MO-201 Excel Expert exam with Microsoft's objectives, worked formula tasks on one sheet, common errors and a three-week plan.
23 min