Excel Test for Administrative Assistants

An administrative assistant needs to quickly sum a column of office supply receipts without typing the SUM function manually. Which keyboard shortcut automatically inserts the SUM function for the adjacent cell range?

In target cell C6, calculate the total petty cash disbursed across all four administrative expense vouchers using the SUM function.

When exporting vendor records from an external database, several company names contain accidental leading and trailing spaces (e.g., ' Acme Courier '). Which text function removes all leading, trailing, and excessive spaces while leaving single spaces between words?

Which Excel functions can an office manager use to count how many item rows contain valid numeric quantities in stock? (Select all that apply)

In target cell B2, convert the lowercase employee name in cell A2 into standard Title Case capitalization using the PROPER function.

An administrative coordinator maintains an RSVP roster where column C contains either 'Confirmed' or 'Declined'. Which formula correctly counts how many guests confirmed their attendance across rows 2 through 30?

An assistant needs to combine employee first names (column A) and last names (column B) with a space in between (e.g., 'David Miller') for printing conference badges. Which formula constructions achieve this? (Select all that apply)

In target cell C2, concatenate the First Name in A2 and Last Name in B2 with a space in between using the ampersand (&) operator.

You are multiplying item quantities in column B by a single sales tax rate stored in cell F1. When copying the formula =B2*F1 down column C, the tax rate reference shifts to F2, F3, etc., returning zeroes. How should the formula be locked?

Before printing a multi-page executive meeting schedule, which Page Layout features ensure the document prints cleanly on physical paper? (Select all that apply)

In target cell B6, count how many attendees have a status of "Present" in the meeting attendance log using the COUNTIF function.

An administrative coordinator looks up employee extension numbers from a staff directory table (A2:C50). Column A contains Employee ID, Column B contains Employee Name, and Column C contains Extension. Which VLOOKUP formula finds the extension for the ID in E2 with exact matching?

An administrative assistant enters a lookup formula to find an employee desk phone number, but Excel displays #N/A. What are valid causes for this error? (Select all that apply)

In target cell D2, retrieve the phone extension for "Marcus Vance" by looking up cell C2 in the Staff column (A2:A5) and returning the Extension from B2:B5 using the XLOOKUP function.

An executive annual meeting date is entered in cell B2 as serial date 45658, and today's date is in cell A2 as serial date 45644. How do you calculate how many calendar days remain until the meeting?

An office manager wants to prevent staff from typing misspelled room names on the shared conference room booking sheet. Which Data Validation settings achieve this? (Select all that apply)

In target cell C2, calculate the number of days remaining until the filing deadline by subtracting the Current Date (A2) from the Deadline Date (B2).

An admin receives a roster where department codes are entered inconsistently as 'hr', 'Fin', and 'ops'. Which function converts any text entry into standard all-caps letters (e.g., 'HR', 'FIN', 'OPS')?

An administrative coordinator needs to display 'Reorder' if current stock in cell B2 is strictly less than the reorder threshold in C2, and 'In Stock' otherwise. Which formulas correctly express this condition? (Select all that apply)

In target cell B2, convert the department code in cell A2 into all capital letters using the UPPER function.

When sorting a table of office equipment by Department (Column A) and then by Purchase Date (Column B), what critical rule must be followed to avoid scrambling the data?

Why should an administrative assistant convert a plain list of visitor logs into an official Excel Table by pressing Ctrl + T? (Select all that apply)

In target cell D2, calculate the total cost for Ballpoint Pens by multiplying Quantity (B2) by Unit Price (C2).

An administrative coordinator is tracking delivery lead times from 5 courier services in cells B2:B6. Which function returns the shortest delivery time?

Scenario Background: The Office Manager provided the weekly petty cash voucher log. You need to calculate the total disbursed amount across all four office expense vouchers to determine how much cash is required to replenish the petty cash drawer back to its $500 float.

In target cell C6, calculate the Total Petty Cash Disbursed across all office vouchers (C2:C5) using the SUM function so the finance team can replenish the drawer.

Scenario Background: The human resources system exported an emergency contact directory where employee department text has messy trailing and leading whitespace, causing automated lookup tools to fail. As the office coordinator, you must clean the department string for the headquarters staff list.

In target cell B2, clean the department text in cell A2 by stripping all leading and trailing whitespace using the TRIM function.

When scrolling through a 500-row visitor log, the column headers in Row 1 scroll off the screen. Which feature under the View tab keeps Row 1 continuously visible while scrolling?

Scenario Background: The Executive Office is hosting the Quarterly Leadership Review. The General Manager needs to know exactly how many confirmed department directors will attend in person to finalize executive catering numbers.

In target cell C6, count the number of attendees who have confirmed their attendance ("Confirmed") from the RSVP column (B2:B5) using COUNTIF.

Scenario Background: A visiting client needs immediate technical support in Conference Room B. The front desk coordinator must quickly look up the desk phone extension for IT Support Specialist 'David Brent' from the internal directory table.

In target cell D2, look up the employee name in C2 ("David Brent") within the Staff list (A2:A5) and retrieve the Extension from column B using XLOOKUP or VLOOKUP.

Scenario Background: Quarter-end supplies must be reordered before the fiscal cutoff. The Operations Manager needs the total replenishment cost calculated across all line items (Sticky Notes, Copy Paper, and Dry Erase Markers) to submit the purchase order.

In target cell D5, calculate the Total PO Amount by summing the Total Cost column (D2:D4) using the SUM function.