Administrative Assistant Excel Test: Tasks and Practice

An administrative assistant Excel test checks whether you can handle the spreadsheet work an office runs on: tidy a list, total it, count it, filter it and look up a detail. The formulas are usually basic to intermediate, but the test is often timed, so speed matters as much as knowing the answer. This guide lists the tasks that come up most in admin roles, with a practice sheet and the answer to every task.

What an administrative assistant Excel test usually covers#

Each employer writes or chooses its own test, so no list is exact. ExcelReady has no access to any employer's or test provider's real questions. What we can do is look at the work admin assistants actually do in Excel, which is what most tests are built around:

The format varies too. Some tests give you a live spreadsheet with tasks to complete. Others show a screenshot and ask multiple-choice questions, such as which formula gives a result. For a wider view of office roles, see the Excel skills admin and office jobs use.

The practice sheet#

Here is an expense-claims log of the kind an admin assistant might keep. It sits in A1:E7, and every task below uses it:

Type it into a blank workbook and try each task before you read the answer.

Task 1: format the list so it is easy to read#

Formatting tasks check that you know where things are, fast. Try these:

1. Make the header row bold: select A1:E1 and press Ctrl+B (Mac: ⌘B).
2. Show the amounts as currency with two decimals: select D2:D7 and press Ctrl+Shift+$ (Mac: ⌃⇧$).
3. Fit the columns to their contents: select columns A to E and double-click the border between two column headers.
4. Keep the headers visible when you scroll: View > Freeze Panes > Freeze Top Row.

For anything not on a shortcut, Ctrl+1 (Mac: ⌘1) opens Format Cells. The formatting shortcuts lesson drills all of these.

Task 2: total and average the amounts#

A total is the most common formula question in any office test. Click D8 and press Alt+= (Mac: ⌘⇧T) for AutoSum, or type it:

=SUM(D2:D7)

The result is 830.45. For the average claim, use =AVERAGE(D2:D7), which gives 138.41 when shown with two decimals. Check that the range covers every row: a range that stops at D6 quietly drops Kai's claim. The SUM function guide covers adding several ranges at once.

Task 3: count and total by status#

Questions that start with "how many" usually want COUNTIF. To count pending claims:

=COUNTIF(E2:E7,"Pending")

The result is 3. The COUNTIF function guide shows other criteria, such as ">100" for claims over 100.

"How much" questions with a condition want SUMIF, which adds only matching rows:

=SUMIF(E2:E7,"Pending",D2:D7)

That gives 124.95 (38.20 + 64.00 + 22.75). Note the order: the column to check first, then the condition, then the column to add.

Task 4: sort and filter the list#

Sorting and filtering are usually done with the menus, not formulas.

• Sort by amount, largest first: click any amount, then Data > Sort Largest to Smallest. Excel moves whole rows, so names stay with their amounts. Never select only one column to sort, or the rows get mixed up.
• Show only Pending claims: press Ctrl+Shift+L (Mac: ⌘⇧F) to add filter arrows, open the Status arrow and tick only Pending.

When a filter is on, the row numbers turn blue and some are skipped. Clear it with Data > Clear before you hand the file back, or the next person will think rows are missing.

Task 5: look up a detail with VLOOKUP#

Lookup tasks give you a name or code and ask for a matching detail. To find Leo Grant's department, with his name typed in H2:

=VLOOKUP(H2,A2:E7,2,FALSE)

The result is Finance. The 2 means "the second column of the range", and FALSE means an exact match. Change 2 to 4 and you get his amount, 310. The VLOOKUP function guide explains each argument and the #N/A error.

How to prepare in the week before the test#

Practise the tasks, then practise them against the clock. A good order:

1. Find your level. The 12-question Excel level test shows which of the skills above you already have.
2. Fill the gaps with short, hands-on lessons, one skill at a time. The list of practice tasks to do before an Excel test gives you more to try.
3. Time yourself on a full test. The basic Excel mock test is close to the level most admin tests aim at. If the job ad mentions data entry, also read the Excel data entry test tips.
4. Know the platform if the recruiter names one. ExcelReady has practice pages for formats such as the Indeed Excel assessment and the Kenexa Prove It Excel test. These practise the same skills; they are not the real questions.

For worked examples of the question styles, read Excel test questions and answers and the guide to the basic Excel test for employment.

Practise it#

Reading the answers is not the same as doing them under time pressure. Try the HR roster interview drill: it is a timed-test style staff roster where you look up a salary, count and total by department, flag bonuses and handle a missing ID, and every answer is checked instantly. Warm up with the SUM and AutoSum lesson if totals still feel slow.