Packing List Excel With Formula: Build a Smart Checklist

Formulas & functions · Updated November 20, 2026

Yes. In an Excel packing list, put each item in column A and its packing status in column D; with item names in A2:A30 and statuses such as Packed or Not packed in D2:D30, enter =COUNTIF(D2:D30,"Packed") to count the items already packed. Add a second formula to count what remains, then use a progress percentage to see how close the list is to complete.

What a Packing List Formula Can Do

A packing list is useful even without formulas, but a few simple calculations make it easier to manage a long list. Instead of scanning every row, you can see how many items are packed, how many are outstanding, and whether the list is complete. This is especially handy when several people are packing, or when you are preparing separate bags for different parts of a trip.

Excel does not supply a special packing-list function. You make a tracker from ordinary worksheet features: rows for items, columns for details, and formulas such as COUNTIF and SUMIF to summarize the information. The formulas update when you change a status, add an item within the formula’s range, or adjust a quantity.

A formula cannot tell whether an item is physically in your suitcase. It counts the values you enter. For that reason, use consistent status labels and treat the sheet as a checklist, not as an automatic confirmation that anything has been packed.

Plan the Worksheet Layout

Start with one row per item and a clear heading for each column. A practical layout for a personal trip is:

  • Column A — Item: the thing to pack, such as socks or a phone charger.
  • Column B — Category: clothing, toiletries, documents, electronics, or another group.
  • Column C — Quantity: how many you need to take.
  • Column D — Status: Packed or Not packed.
  • Column E — Notes: details such as “carry-on” or “borrow from Sam.”

Put the headings in row 1 and the first item in row 2. If you expect up to 29 items, use rows 2 through 30 for the list. Keep the status column separate from the quantity column: “two” describes how many you need, while “Packed” describes whether the task is done. This separation makes summaries more reliable.

For a reusable workbook, leave a few spare rows inside the formula ranges. If you later add items beyond the last included row, a formula that stops at row 30 will not count them. You can extend the range when needed or convert the item range to an Excel table so new table rows can be included more easily.

Count Packed Items and Items Remaining

The COUNTIF formula counts cells that match one condition. With item names in A2:A30 and status labels in D2:D30, the opening formula counts cells marked Packed. If your actual list ends on row 12, adjust the range to D2:D12 rather than counting a large, unrelated section of the sheet.

To count items still to pack while ignoring unused rows, use a formula that checks both whether the item cell is filled and whether the status is not Packed. With item names in A2:A30 and statuses in D2:D30, enter =COUNTIFS(A2:A30,"<>",D2:D30,"<>Packed"). This counts named items whose status is Not packed or blank. A blank status therefore remains visible as unfinished work, rather than disappearing from the count.

For example, suppose six items are listed: three are marked Packed, two are marked Not packed, and one has no status yet. The packed count is 3 and the remaining count is also 3. The second count includes the two explicitly unfinished items and the one with a blank status. That is often safer than treating an empty status as complete.

Track Quantity and Completion Percentage

Counting item rows is not the same as counting individual pieces. If you need four shirts, that is one checklist row but four pieces. To total the quantities for rows marked Packed, place quantities in C2:C30 and statuses in D2:D30, then use =SUMIF(D2:D30,"Packed",C2:C30). If the packed rows have quantities of 4, 2, and 1, the result is 7 packed pieces, even though only three item rows are complete.

A completion percentage can help when a list is long. With item names in A2:A30 and statuses in D2:D30, use =IFERROR(COUNTIF(D2:D30,"Packed")/COUNTA(A2:A30),0). If six named items are listed and three are marked Packed, the result is 0.5, or 50% after you format the result as a percentage. IFERROR makes the result zero when there are no item names, instead of showing a division error.

This percentage measures completed rows, not the fraction of all required objects packed. If one row represents ten identical items and another represents one, each row contributes equally to the percentage. Use the quantity total instead if you specifically need a piece-based count, and decide whether that is meaningful for your trip.

Build the Checklist in Excel

  1. Open a blank workbook in Excel for Microsoft 365 on Windows. Enter the column headings in row 1 and your items below them.
  2. Enter a quantity for each item and choose a consistent status spelling. For example, use Packed and Not packed throughout rather than mixing “Done,” “Yes,” and “packed.”
  3. Place summary labels in an open area, such as G1:G3, and put the corresponding formulas beside them. Label each result so you know whether it counts rows, pieces, or a percentage.
  4. Test the formulas by changing one status. The packed count should rise when you change Not packed to Packed, and the remaining count should fall.
  5. Save a clean copy as a template if you want to reuse the structure for later trips. Clear the old item and status entries before starting a new list.

In Microsoft 365 for Windows, you can make a drop-down list for consistent status entries by selecting the status cells and choosing Data > Data Validation. Set Allow to List, then enter the status choices separated by a comma, such as Packed,Not packed. On Mac, Data Validation is also available from the Data tab, though the dialog layout can differ. Excel for the web supports data validation, but some commands may be arranged differently from the Windows desktop ribbon.

Make the List Easier to Scan

Formatting helps people use the sheet correctly. Freeze the heading row with View > Freeze Panes > Freeze Top Row, so the column names stay visible while you scroll. Widen the Item and Notes columns, and use a date or trip name near the top of the sheet if the workbook will hold more than one list.

To color status cells, select the status range and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Create one rule for Packed and another for Not packed, choosing readable colors with sufficient contrast. The colors are visual cues only; the text in the cells is what the formulas count.

Keep summary cells outside the rows being counted. If a total formula is placed inside the list range, it may be mistaken for an item or included in a calculation. You can also distinguish headings and summary cells with bold text or a fill color, but avoid relying on color alone to communicate whether an item is complete.

Common Formula Problems

  • The count stays at zero: check that the status text matches the formula criterion. “Packed ” with a trailing space may not match “Packed.” Re-enter the value or correct the formula text.
  • New rows are not counted: check the formula’s last row. If it ends at D30, an item in row 31 is outside that range.
  • Blank rows affect the remaining count: a simple count of all statuses not equal to Packed may include empty cells. The COUNTIFS version also checks that the item cell is not blank.
  • The percentage looks like a decimal: select the result cell and use Home > Number > Percent Style. A result of 0.5 represents 50%.
  • Excel shows a formula error: make sure the formula begins with an equals sign, the ranges have matching lengths, and text criteria are enclosed in straight double quotation marks.

Excel formulas normally use commas between arguments in the setup described here. Some regional settings use semicolons instead; if Excel rejects a correctly written formula, the computer’s list-separator setting may be different. Also check whether a cell is formatted as Text, which can make an entry display like a formula rather than calculate. Change the cell to General, then re-enter the formula.

When to Use a Template or Another Tool

A self-made sheet is a good choice when you want control over categories, quantities, and summaries. A prebuilt Excel template can save setup time, but inspect its formulas and labels before relying on it. A template may count checked boxes or use different status words, so copying a formula into a differently arranged sheet can produce misleading results.

For a short personal trip, a paper list or a notes app may be faster than maintaining a spreadsheet. Excel becomes more useful when you reuse lists, coordinate with other people, track quantities, or want a summary that updates automatically. Avoid adding complex features unless they solve a real problem: a simple status column is often easier to maintain than a workbook with many linked sheets.

Frequently Asked Questions

How do I make a packing list in Excel?
Put one item per row and add columns for category, quantity, status, and notes. Use consistent status values such as Packed and Not packed, then add summary formulas in cells outside the list.

What formula counts packed items in Excel?
With statuses in D2:D30, =COUNTIF(D2:D30,"Packed") counts cells that exactly match Packed. Change the range to match the rows in your own worksheet.

How can Excel count items that are not packed yet?
With item names in A2:A30 and statuses in D2:D30, =COUNTIFS(A2:A30,"<>",D2:D30,"<>Packed") counts named items whose status is blank or anything other than Packed. Use the matching criterion and spelling consistently.

Can a packing list count quantities as well as items?
Yes. Put numeric quantities in one column and use SUMIF to add quantities only for rows with a Packed status. This counts pieces rather than checklist rows, so it answers a different question from COUNTIF.

Can I reuse the same Excel packing list for another trip?
Yes. Save a blank template with the headings and formulas, then duplicate it or clear the previous trip’s entries. Check the formula ranges after adding rows, and make sure old statuses do not remain in the new list.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.