Excel Practice Exercises for Beginners with Answers

The best Excel practice exercises for beginners use one small, clearly labelled dataset and build skills in order: line totals, sums, a fixed rate with absolute references, IF, SUMIF, COUNTIF, averages, filtering and a chart. Each task below gives the exact cells, the formula and the expected answer, so you can check your own work.

Key takeaways

• Type the practice data yourself before writing any formula; entering data is part of the skill.
• Learn relative and absolute references early, because almost every later exercise depends on them.
• Check every total through a second route, such as a category summary that must add back to the grand total.
• Treat error messages as clues, not failures, and practise fixing them on purpose.
• Change one input after finishing, and confirm that every result updates without retyping.

Why do practice exercises work better than watching tutorials?

Watching a trainer build a spreadsheet makes Excel look simple. The real learning starts when you choose a range yourself and discover why your total differs from the expected one. That moment of investigation is where references, ranges and formats finally make sense.

Many free exercise pages online give a single function per task and a "show answer" button. That is useful for a quick check, but it can let a beginner skip the thinking step. The exercises in this guide work differently: every task builds on the same workbook, so a mistake in an early step shows up later, exactly as it would in a real office file.

The dataset is a fictional order sheet for office supplies. It is small enough to check by hand, yet it contains categories, quantities, rates and a delivery status, which covers the patterns most administrative and accounts roles in the UAE meet every week. No real business information is used.

These are original learning exercises, not official Microsoft certification questions. The formulas use English function names and commas between arguments. If your regional settings use semicolons, Excel will expect semicolons instead, so adapt the separator rather than the logic.

How should you set up the practice workbook?

Open a new workbook, rename the first sheet Orders and type these headers into A1:E1: Item, Category, Qty, Rate, Status. Enter the six records below exactly. The Row column only tells you where to type; it is not part of the workbook.

Follow three habits from the start. Keep one header row and one record per row. Leave no blank rows inside the data. Type numbers as plain numbers, without currency letters, and apply a number format afterwards; letters typed into a value cell turn it into text, and text does not calculate.

Save the file with a descriptive name, such as Excel practice orders. You will return to it in every exercise, and later you will deliberately change inputs to test your formulas.

Which exercises build the core formula skills?

The first four exercises cover the foundations: arithmetic formulas, the SUM function, absolute references and the IF function. Work through them in order and do not move on until your answers match.

Exercise 1: Calculate each line total

Type Line total in F1. In F2, multiply the quantity by the rate with =C2*D2, then copy the formula down to F7 using the fill handle.

Expected results in F2:F7 are 96, 60, 225, 240, 60 and 200. Click F3 and look at the formula bar: it now reads =C3*D3. Excel adjusted the row numbers because these are relative references, and each row should use its own quantity and rate.

Never type the answers over the formulas. A workbook that calculates from its inputs can be trusted when the inputs change; a workbook full of typed answers cannot.

Exercise 2: Add a grand total and check it

Type Total in E9. In F9 enter =SUM(F2:F7), and in C9 enter =SUM(C2:C7).

F9 should show 881 and C9 should show 68. Check the first result by hand: 96 + 60 + 225 + 240 + 60 + 200 = 881. The colon in F2:F7 means "from F2 through F7". Make sure the range stops at row 7, because a range that includes F9 would try to add the total to itself and produce a circular reference warning.

Notice that 881 is a value and 68 is a count of items. Keep them in separate, clearly labelled cells rather than combining them.

Exercise 3: Apply one rate to every row with a fixed reference

Type Discount rate in L1 and enter 0.1 in L2, then format L2 as a percentage so it displays as one tenth of the value. Type Discount in G1 and Net total in H1.

First try =F2*L2 in G2 and copy it down. Every row below G2 returns zero, because the copied formulas now point to L3, L4 and so on, which are empty. That is the classic beginner trap with relative references.

Fix it in one of two ways. Either click L2 while editing the formula and press F4, which adds a dollar sign before the column letter and before the row number to make an absolute reference, or click L2, type DiscountRate in the Name Box to the left of the formula bar and press Enter. With the name in place, G2 becomes =F2*DiscountRate, which reads like a sentence. In H2 enter =F2-G2, then copy both formulas down to row 7. Every row now uses the same rate while F2 changes to F3, F4 and so on.

In G9, =SUM(G2:G7) returns 88.10, and in H9, =SUM(H2:H7) returns 792.90. Confirm that 88.10 + 792.90 = 881.00. Change L2 to 0.2 and every discount doubles at once, which proves that all six rows read the same cell. Set it back to 0.1 before continuing.

Exercise 4: Label larger orders with IF

Type Size in I1. In I2 enter =IF(F2>=200,"Large","Standard") and copy it down to I7.

The expected labels are Standard, Standard, Large, Large, Standard and Large. The USB drive line totals exactly 200, so it qualifies because the test uses "greater than or equal to". Change >= to > and the USB drive becomes Standard. Precise comparison operators matter whenever a business rule has a boundary value.

How do you summarise data with SUMIF, COUNTIF and averages?

Summaries turn a list into answers a manager can use: how much per category, how many orders are still pending, what a typical line looks like. Create a second sheet named Summary for the next three exercises.

Exercise 5: Total by category with SUMIF

On Summary, type Category in A1 and Value in B1. Enter Stationery in A2 and Electronics in A3. In B2 enter =SUMIF(Orders!B:B,A2,Orders!F:F) and copy it to B3. Whole-column ranges such as B:B do not shift when the formula is copied, and they are safe here because the total rows on Orders have no category label.

Stationery should show 216 and Electronics 665. In B4, =SUM(B2:B3) should return 881, matching the grand total on Orders. That match is your second-route check: if the category totals do not add back to the grand total, a range is misaligned somewhere.

The first range holds the categories to search, A2 holds the category to find, and the last range holds the values to add. When you use fixed ranges instead of whole columns, both must cover the same rows. Matching B2:B7 against F3:F8 would pair each category with the wrong line.

Exercise 6: Count orders by status with COUNTIF

Type Status in D1, Orders in E1 and Value in F1. Enter Delivered in D2 and Pending in D3. In E2 enter =COUNTIF(Orders!E:E,D2), and in F2 enter =SUMIF(Orders!E:E,D2,Orders!F:F). Copy both down to row 3.

Delivered should show 4 orders worth 581, and Pending should show 2 orders worth 300. COUNTIF counts records, not quantities. The Pen line holds 30 items but still counts as one order. Mixing up "how many rows" and "how many items" is one of the most common reporting errors in real offices.

Exercise 7: Find the average, the largest and the smallest line

Type Average line in H1, Largest line in H2 and Smallest line in H3. In I1 enter =AVERAGE(Orders!F2:F7), in I2 enter =MAX(Orders!F2:F7) and in I3 enter =MIN(Orders!F2:F7).

Expected results are 146.83 (shown to two decimal places), 240 and 60. Two lines share the minimum value, the Pen and the Folder, so MIN alone does not identify a single item.

Now compare two different "averages". In H4 type Average rate and enter =AVERAGE(Orders!D2:D7), which returns 27.67. In H5 type Value per item and enter =Orders!F9/Orders!C9, which returns 12.96. The first treats every line equally; the second weights each rate by its quantity. Knowing which average a question needs is a skill that most beginner exercise lists never test.

How do text, sorting, filtering and charts fit in?

Formulas are only part of everyday Excel work. Cleaning labels, sorting lists, filtering records and presenting a result visually are equally common tasks, and each has a trap worth practising.

Exercise 8: Join text with the ampersand

On Orders, type Label in J1. In J2 enter =A2&" ("&B2&")" and copy it down to J7. J2 should read Notebook (Stationery).

The ampersand joins text, and anything in double quotation marks appears exactly as typed, including the spaces and brackets. Older guides often teach CONCATENATE for this; the ampersand does the same job and is easier to read. For a quick length check, =LEN(A7) returns 9, because "USB drive" contains nine characters including the space.

Exercise 9: Filter without losing the full total

Select A1:J7 and turn on filters from the Data tab. Filter Category to Stationery: you should see Notebook, Pen and Folder.

Type Visible total in E11 and enter =SUBTOTAL(109,F2:F7) in F11. With the filter on, F11 shows 216, while the SUM in F9 still shows 881, because SUM includes rows that a filter hides. Clear the filter and F11 returns to 881.

This difference matters before you share a report. Always state whether a total covers every record or only the visible ones. Then practise sorting: sort the list by Line total, largest to smallest, and confirm that each row moved as a whole, with its item, category and status still together.

Exercise 10: Chart a checked summary

On Summary, select A1:B3 and insert a clustered column chart. Title it Order value by category.

Electronics should show 665 and Stationery 216. Leave row 4 out of the selection, because the total is not a third category. Avoid 3D effects, which make bars harder to compare. A chart should present a number you have already checked in the cells; if the chart looks surprising, inspect the selected range before changing colours or fonts.

What do common Excel errors mean, and how do you fix them?

Most beginners panic at their first error message. A better approach is to create each error on purpose in a spare area of the Summary sheet, so you recognise it immediately when it appears in real work.

Exercise 11: Create and fix three errors

In K1 enter =F9/0 and you will see #DIV/0!. Change it to =IFERROR(F9/0,"Check divisor") and the cell shows a readable message instead.

In K2 enter =SUMM(1,2) and you will see #NAME?, because the function name is misspelt. Correct it to =SUM(1,2), which returns 3.

In K3 type the text ten items, then in K4 enter =K3*2. You will see #VALUE!, because Excel cannot multiply words. Replace the text with the number 10 and K4 returns 20. Use IFERROR sparingly in real files: hiding every error can also hide a genuine mistake.

Exercise 12: Test the order of operations

In K6 enter =2+34 and in K7 enter =(2+3)4. The first returns 14, because Excel multiplies before it adds. The second returns 20, because brackets are calculated first. Whenever a formula combines operations, add brackets to make your intention clear, even when Excel would get the order right without them.

How can you check that you really understand the workbook?

Finishing the exercises once proves you can follow instructions. Changing the inputs proves you understand the workbook. Save a copy first, then try this controlled test.

On Orders, change the Notebook quantity from 12 to 13. The Notebook line total should become 104 and the grand total 889. On Summary, Stationery becomes 224 and the Delivered value becomes 589, while the order counts stay at 4 and 2 because no row was added or removed. With the same rate in L2, the net total becomes 800.10.

If any result fails to update, a value was typed over a formula or a range is too short. Find it, fix it, then restore the original quantity of 12 so the answers in this guide apply again.

A good next step is to rebuild the same workbook from a blank sheet without reading the instructions. Then invent your own dataset, such as a household budget or a list of study topics, and write a fresh answer key before you build the formulas.

How should beginners plan their Excel practice?

Short, regular sessions work better than one long attempt, because references and ranges become automatic through repetition. Use the plan below as a sequence, moving to the next stage only when the current one feels comfortable.

Keep a short log of every error you met and how you fixed it. When you sit down to practise again, start by repeating one task from that log. This habit turns weak points into strengths faster than simply moving on to new functions.

Once the basics are secure, the topics in the last stage open the door to real analysis work. The guide to advanced Excel techniques for effective analysis shows where these skills lead, and the overview of Microsoft Excel certification explains the options for people who want formal proof of their skills. Confirm current certification exam rules and fees on the official Microsoft website.

When is a structured Excel course the right next step?

Self-study works well for building confidence, but many learners reach a point where they need feedback on their own files. A trainer can spot why a formula fails in seconds, explain the habit behind the mistake and set the next exercise at the right level.

At Bright Future Training Institute, the Microsoft Excel course in Dubai runs in the classroom at the NBQ Building, Bank Street, Bur Dubai, close to BurJuman Metro Station, and as online live sessions for learners elsewhere in the UAE. Learners who already handle formulas comfortably can look at advanced Excel training, and those who also need Word, PowerPoint and Outlook can compare the MS Office courses in Dubai. The wider range of IT courses covers related software skills.

Certificates are approved by KHDA and recognised across the UAE. Students can book a free demo class before enrolling, which is a useful way to bring this practice workbook and ask about the exercises you found hardest. Course advisers can explain current fees and schedules, as none are published here.

Keep practising, then get feedback

Work through all twelve exercises, change the inputs, and rebuild the workbook from memory. When you are ready for guided feedback, talk to a course adviser through the contact page, on WhatsApp or by phone at +971 54 373 3394, or by email at [email protected], and book a free demo class.