Excel for Office Jobs: Tests, Real Tasks and Next Steps

You now have the main Excel skills that office, admin, accounts, sales support and data entry jobs need. This last lesson puts them to work. It shows the tasks that employers commonly give in Excel tests, practical mini-projects you can build for your portfolio, how to describe your Excel skills honestly on a resume, and where to go next.

What employers usually test

An Excel test for a junior role is often a short practical task, 20 to 40 minutes, on a sample workbook. Typical items:

• Clean a list: trim spaces, fix capitals, remove duplicates (lesson 8).
• Calculate columns with correct formulas and absolute references (lessons 3 and 4).
• Use IF, SUMIF or COUNTIF for a result (lessons 5 and 6).
• Look up values from another table with VLOOKUP, XLOOKUP or INDEX MATCH (lesson 7).
• Sort, filter and format a table neatly (lessons 2 and 10).
• Build a pivot table and a chart (lessons 11 and 12).
• Print or set up a sheet for one page (lesson 14).
• Sometimes: data validation, conditional formatting or date calculations (lessons 9 and 13).

The employer looks at three things: whether the answers are correct, whether you used sensible methods (formulas, not typed numbers), and whether the sheet looks tidy. Accuracy comes first.

A practice test you can do now

Here is a mini-test using the shop data. Do it with a timer of 30 minutes, without looking at the lessons.

1. Put the Sales data into a Table named Sales.
2. Add Amount = Qty * Price for each row, using a Table formula.
3. Add a Category column from a price list with a lookup.
4. Show total sales, number of orders, and the average order value in three cells.
5. Make a summary of sales by region with SUMIF, and check that it adds up to the total.
6. Add an Overdue or Size column with IF.
7. Create a pivot table of sales by staff and a column chart from it.
8. Highlight all amounts above 2500 and set the sheet to print on one page, landscape.

Then check yourself. Total sales 20150, 10 orders, average 2015, North 7800, South 7750, East 4600. If your numbers match, the key skills work.

Real office tasks you can model

Build small, real workbooks. They prove your skills better than a certificate on its own, and they are good practice. Ideas:

• An invoice template. Customer details, line items with quantity and price, tax at a rate in one cell, a total, and the invoice number and due date. Use your own country's tax rules, which you should check from an official source.
• An attendance sheet. Names down, days across, P and A entries with a drop-down, a count of days present, and a percentage with conditional formatting under a limit.
• A monthly expense tracker. Date, category, amount, with a pivot table and a chart by category.
• A stock register. Items, opening stock, received, issued, closing stock by formula, and a flag when stock is under the reorder level.
• A marks or results sheet. Subject marks, total, percentage, a result with IF, and a rank with RANK.
• A simple payroll calculation. Hours, rate, overtime, deductions. Use plain test numbers, not real people's data, and check statutory rules with official guidance.

Keep the files neat, with a Notes sheet and sample data that is invented. Never use real names, salaries or phone numbers in a public portfolio.

Describe your Excel skills honestly

Most resumes say “Proficient in MS Excel”. It means little. Be specific and truthful, and match it to the job advert. For example:

Weak:    MS Excel - advanced

Better:  Excel: formulas (IF, SUMIF, VLOOKUP), pivot tables, charts,
         data cleaning and validation. Built a stock register and
         monthly expense tracker (practice projects).

• Name the functions and features you can actually use.
• Mention a real task or project, without exaggeration.
• Say basic, intermediate or advanced honestly. An interviewer may test you.
• If a job asks for macros or Power Query and you have not used them, say you are learning them, and that you know the basics.

Our resume builder lets you list skills and projects clearly, and the ATS checker shows how a reading program sees your file. Both are free to try. Use the job advert's exact wording where it is true, for example pivot tables or VLOOKUP.

Common mistakes in Excel tests

• Typing a number that should be calculated by a formula.
• Forgetting absolute references and getting wrong results when copying.
• Not checking totals against a simple second method.
• Leaving the sheet untidy: no headings, mixed formats, columns too narrow.
• Running out of time on one hard question. Do the easy items first.
• Saving in the wrong format or place. Read the instructions on how to submit.

What to learn next

• More lookups and dynamic arrays: XLOOKUP, FILTER, SORT, UNIQUE (Microsoft 365).
• Power Query (Get and Transform data) for cleaning and combining data from files without formulas. It is very valuable.
• Power Pivot and the data model for larger analysis.
• Macros and VBA to automate repeated tasks, or Office Scripts.
• Google Sheets and LibreOffice, since many workplaces use them.
• SQL for databases, in our SQL for Beginners course, when your data outgrows spreadsheets.
• Python with pandas for larger or repeated analysis, in our Python course.

The best way to improve is to use Excel for real things: your budget, a family business list, a club's accounts, a study timetable. Each small project teaches you something new. Keep a list of the questions you could not answer, and look them up.

Your task

1. Do the practice test in 30 minutes and compare your results with the numbers given.
2. Build one of the practice projects, such as the invoice template or the attendance sheet, with invented sample data.
3. Write the Excel line of your resume in the Better style, using only skills you really have.
4. Choose one item from the learn-next list and plan two hours for it this week.

How to check it worked

• Your results match: total 20150, 10 orders, average 2015, North 7800, South 7750, East 4600.
• Your project uses formulas, not typed totals, and has a Notes sheet.
• Your resume line names specific features and has no exaggerated claims.