Build an Equipment Checkout Tracker in Excel

An equipment tracker in Excel tells you who has each tool, laptop or camera, when it is due back and what is overdue. You build it from two tables, an equipment list and a checkout log, plus four formulas: XLOOKUP for item names, IF with TODAY for status, a date subtraction for days out and COUNTIF for totals. This guide walks through each step for office managers, lab and site coordinators, teachers and anyone who lends out equipment.

How the equipment tracker is organised#

The tracker uses two sheets, so each fact is typed once.

• Equipment sheet: one row per item. Columns: ID, Item, Category, Serial number.
• Log sheet: one row per checkout. Columns: ID, Item, Borrower, Date out, Due back, Returned, Status, Days out.

The ID, such as E-101, links the two. You type the ID in the log and Excel fills in the rest. Turn each table into an Excel table with Ctrl+T (Mac: ⌘T), so formulas copy down automatically when you add rows. The same two-table idea runs the inventory tracker in Excel.

Step 1: Set up the equipment list#

On the Equipment sheet, list every item with a unique ID:

Never reuse an ID, even after an item is retired. Labelling the real equipment with the same ID makes checkouts quick.

Step 2: Log each checkout and look up the item name#

On the Log sheet, add a row every time something goes out. Type the ID, borrower, date out and due date. To enter today's date as a fixed value, press Ctrl+; (Mac: Control+;).

In B2, fill in the item name from the ID:

=XLOOKUP(A2,Equipment!A:A,Equipment!B:B,"Unknown ID")

XLOOKUP looks for the ID in column A of the Equipment sheet and returns the name from column B. The fourth argument shows "Unknown ID" instead of #N/A when the ID is mistyped. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions use =VLOOKUP(A2,Equipment!A:C,2,FALSE). The XLOOKUP function guide and this XLOOKUP walkthrough cover every argument, or you can practise XLOOKUP in a live sheet.

To stop typos, add a drop-down to the ID column: Data > Data Validation > List, with the source =Equipment!$A$2:$A$100.

Step 3: Show the status and days out#

The status formula checks the return date first, then compares the due date with today. In G2:

=IF(F2<>"","Returned",IF(TODAY()>E2,"Overdue","Out"))

• If F2 (Returned) has a date, the item is back.
• If not, and today is after the due date in E2, it is overdue.
• Otherwise it is still out, but not late.

In H2, show how long the item has been away:

=IF(F2<>"",F2-D2,TODAY()-D2)

Returned items show the length of the loan. Items still out show days so far. Format H2 as a number, not a date. If you see a date like 21/01/1900, change the format to General. Here is the log as it looks on 2 November 2026:

The projector is due today, so it is not overdue yet. TODAY has no arguments and changes every day, which keeps the log live; the TODAY function guide explains how it recalculates. The days between dates lesson practises date subtraction, and the article on counting days until a deadline shows the reverse calculation.

Step 4: Count what is out and overdue#

COUNTIF turns the status column into a summary. On a dashboard area or sheet:

The COUNTIF function guide and the article on COUNTIF criteria cover more conditions, and you can practise COUNTIF in a live spreadsheet.

Step 5: See who has each item right now#

The latest checkout tells you who has an item. Because new loans are added at the bottom of the log, search from the bottom up. On the Equipment sheet, in D2:

=XLOOKUP(A2,Log!A:A,Log!G:G,"Never lent",0,-1)

The last argument, -1, tells XLOOKUP to search from last to first, so it returns the status of the most recent loan. Use the same formula with Log!C:C to return the latest borrower. If the status is Returned, the item is on the shelf.

Step 6: Highlight overdue loans#

Color makes late items jump out. Select the log rows, choose Home > Conditional Formatting > New Rule > Use a formula, and enter:

=$G2="Overdue"

Pick a red fill. The $ locks column G, so the whole row turns red. The guide to highlighting overdue dates shows date-based rules too, such as items due in the next three days.

Tips to keep the tracker accurate#

A tracker is only as good as its log. Keep these habits:

• Never delete a log row. Fill in the Returned date instead, so you keep the history.
• Use fixed dates (Ctrl+; or typed) for Date out and Returned. Use TODAY only in formulas.
• Keep one table per sheet with no blank rows, so COUNTIF and XLOOKUP see every loan.
• For a monthly report that must not change, copy the log and paste values, or replace TODAY() with a cell holding the report date.

Practise it#

The exercise on this page combines two tracker skills in one formula: an XLOOKUP for the date out and a day count. Then practise XLOOKUP in a live spreadsheet, practise counting days between dates and practise COUNTIF summaries. Every step checks your formula instantly, so you can build the real tracker with confidence.