Excel Skills for Admin and Office Jobs

The Excel skills for admin jobs are practical ones: entering data fast and accurately, totalling and counting with SUM and COUNTIF, filtering lists and formatting reports so people can read them. This guide is for anyone applying for admin assistant, office manager, receptionist or coordinator roles who wants to know what to learn first and how to show it in a test.

What admin and office jobs use Excel for#

Admin roles use Excel mostly to keep lists and answer simple questions about them. Typical files include:

• Trackers: supply orders, IT requests, room bookings or deliveries, each with a status column.
• Logs: visitors, calls, mileage or petty cash.
• Schedules and rotas: who is in, on which day, in which room.
• Simple reports: monthly totals, counts by status or department, a tidy summary for a manager.

The questions behind these files are nearly always "how much?", "how many?" and "show me only these rows". A small set of skills answers all three.

The Excel skills for admin jobs, in order of priority#

Learn these in order. Each one builds on the one before.

If you're new to formulas, start with how to write your first Excel formula.

Totals and counts: SUM and COUNTIF#

SUM adds numbers and COUNTIF counts cells that match a condition. Take this office supply tracker, with data in rows 2 to 8:

Three common questions and their formulas:

• Total cost of all orders: =SUM(D2:D8) returns 220.85. Click the empty cell under the column and press Alt+= (Mac: ⌘+Shift+T) to let AutoSum write it. See the SUM function guide.
• How many orders are pending: =COUNTIF(C2:C8,"Pending") returns 3. The COUNTIF function guide covers criteria like "<>Delivered" (not delivered).
• Cost of pending orders: =SUMIF(C2:C8,"Pending",D2:D8) returns 117.35 (89 + 18.75 + 9.60). SUMIF checks column C and adds the matching costs from column D.

If the status you want is typed in a cell, say F1, use =COUNTIF(C2:C8,F1). Then one formula answers the question for any status.

Filtering a list without losing data#

A filter hides the rows you don't need and brings them back when you clear it. Nothing is deleted. To turn on the filter buttons, click inside the table and press Ctrl+Shift+L (Mac: ⌘+Shift+F). Then use the arrow on a header to pick values, such as only Pending.

One catch matters in tests: SUM still adds hidden rows. To total only the rows you can see, use:

=SUBTOTAL(109,D2:D8)

The 109 means "sum, skipping hidden rows". With the Pending filter on, this shows 117.35, while =SUM(D2:D8) still shows 220.85.

Formatting that makes reports readable#

Formatting changes how a value looks, not the value itself, so keep numbers as numbers. A few shortcuts cover most office reports:

Keep one format per column, so every cost shows as currency and every date as a date. The guide to currency, percent and date formats explains each one.

Pulling details from another list with a lookup#

A lookup copies a detail from one list into another, so you never retype it. Say a second sheet lists each department's budget code, and your tracker needs the code next to every order. With departments in F2:F6 and codes in G2:G6:

=VLOOKUP(B2,$F$2:$G$6,2,FALSE)

This finds the department in B2 in the first column of F2:G6 and returns the 2nd column, the code. FALSE means exact match, and the $ signs keep the list in place when you fill the formula down with Ctrl+D (Mac: ⌘D). If a department is missing from the list, you get #N/A, which is useful: it tells you the list needs updating.

How to practise for an admin Excel test#

Admin tests tend to reward accuracy more than advanced formulas. A good practice routine:

1. Build a tracker from scratch. Type eight rows like the table above, with a status and a cost.
2. Answer five questions with formulas: a total, a count by status, a total by status, an average and a maximum.
3. Filter and check. Filter to one status and confirm your COUNTIF and SUMIF match what you see.
4. Format it with bold headers and currency, using only shortcuts.
5. Time yourself and repeat until it takes under ten minutes.

If you're weighing other paths, compare the Excel skills HR and recruiting jobs use, the skills finance jobs expect and the skills data analysts need. Admin skills are the base for all three.

Practise it#

Start with SUM and AutoSum to total a column, then filter a table with Ctrl+Shift+L and SUBTOTAL, and finish with formatting shortcuts for headers, currency and percentages. Or try the COUNTIF exercise below on the supply tracker right now.