Excel practice task: check an administrative request log

Updated November 9, 2026

Can you identify the open work, calculate its total effort and keep missing information visible? Use this fictional request log to practise those three checks. You can use formulas, filters or a PivotTable if you can explain the result.

This is a self-study exercise with a worked answer rubric. It is not a validated placement test, instructor assessment or certification exam. The skills self-assessment provides a separate starting point for discussing course levels.

Set up the data

Download the request-log CSV, or enter the table below in A1:F7. The practice workbook contains the same data on its Request log sheet, at different cell coordinates, with formulas already supplied.

Put Review date in J1 and =DATE(2026,11,9) in J2. The fixed date makes the answers reproducible; do not substitute today’s date for this exercise. Format D2:D7 and J2 as dates. After a CSV import, check that the nonblank due dates are real dates with =ISNUMBER(D2); text that merely looks like a date needs correction before date comparisons.

For this exercise, overdue means Open with a known due date earlier than the review date. A request due on the review date is not overdue. A blank due date needs attention but is not evidence that a request is overdue.

Complete four tasks

1. Identify every Open request without changing the row relationships.
2. Calculate the number of Open requests and their total Minutes.
3. Identify the overdue requests and the request missing its due date.
4. Change R106 to Closed. Recheck the count, minutes and overdue list, then restore Open.

When sorting, select the complete dataset or use an Excel Table so each request stays with its owner, status and minutes. Microsoft’s filtering guide explains how a filter hides rows without removing their data.

Worked formulas for a blank worksheet

If you entered the source in A1:F7, count and total the Open rows with:

=COUNTIF($E$2:$E$7,"Open")
=SUMIF($E$2:$E$7,"Open",$F$2:$F$7)

In G1, enter Review. In G2, enter the formula below and fill it down to G7. It treats a missing date as a separate result and flags text dates for review:

=IF(E2<>"Open","Closed",IF(ISBLANK(D2),"Add due date",IF(NOT(ISNUMBER(D2)),"Check date",IF(D2<$J$2,"Overdue","Open"))))

Then count the classified overdue rows:

=COUNTIF($G$2:$G$7,"Overdue")

These formulas assume the only valid statuses are Open and Closed. Check imported status values first; a misspelling such as Opne should be corrected, not treated as a new business rule. This example uses commas as argument separators. See Microsoft’s COUNTIF, SUMIF and IS functions references for the calculation rules.

Answer rubric

Explain why each result follows from the source rows. Reaching the same number by accident is not sufficient: for example, show which requests contribute to 110 minutes and why the missing date is separate from overdue work.

Use the result to choose what to practise

If working with a complete list, filters and basic totals was difficult, compare Excel Intermediate 1. If your next task needs conditional calculations and rule checking, review Excel Advanced Functions. For grouped reports and PivotTables, compare Advanced Data Management.

Bring the task you tried and the step that caused difficulty to a training discussion. An instructor can consider your actual work requirements and experience; this rubric does not automatically assign a level.