A LEGO inventory spreadsheet is a workbook for recording the sets and individual pieces you own, or for checking whether a particular build has all its required parts. In Excel for Microsoft 365, a useful setup separates set records from part quantities, identifies pieces by part and color IDs, and calculates shortages automatically.
What a LEGO inventory spreadsheet can track
“Inventory” can mean two different things: a collection catalogue or a parts checklist. A catalogue records which complete sets you own, when you bought them, and where their boxes or instructions are stored. A parts checklist records quantities of individual elements, often so you can sort, build, repair, or compare a collection with a set’s parts list. A workbook can cover both, but these records answer different questions.
For example, a Sets sheet might show that you own set 60316, while a Parts sheet records how many pieces of each color and type are in your loose collection. A build checklist can then compare the quantities required for a project against those loose parts. Owning a set does not automatically mean every piece is still in the box: pieces may have been used in another build, lost, or replaced. Keeping set ownership and loose-piece counts distinct prevents that assumption from distorting the result.
- Set catalogue: set number, name, theme, year, condition, box status, and instruction status.
- Loose-part inventory: part identifier, color identifier, description, quantity, and storage location.
- Build checklist: required part and color combinations, required quantity, owned quantity, and shortage.
Where set and part information comes from
LEGO set instructions can provide a parts list for that set, while third-party catalogue sites such as BrickLink and Rebrickable publish set and element information that collectors commonly use to organize inventories. These are independent databases, not a guarantee of one universally definitive parts list. They may describe a part differently, use different identifiers, or show different quantities because an edition, production run, or inventory update differs.
Check the exact set number and, where available, the relevant version or year before importing a list. Also decide whether your goal is to reproduce the official set contents or to check whether you have enough pieces for a planned build. Some set inventories include small spare elements; if you are tracking a build checklist, decide whether to include those spares rather than assuming every listed quantity is essential to the model. If a part is missing from a database or appears under an unfamiliar name, compare the physical piece, its element or part information, and the instructions before adding a new row.
When copying or importing data from a catalogue, check its download options, usage terms, and any limits on automated access. Avoid treating a product page’s shopping availability as a complete inventory file: a parts catalogue, a replacement-parts service, and a personal collection spreadsheet serve different purposes. Keep a source or notes column for imported lists so you can investigate discrepancies later.
Choose a structure before entering data
Start with three worksheets named Sets, Owned Parts, and Build Checklist. Use one row per set on Sets, one row per part-and-color combination on Owned Parts, and one row per required part-and-color combination on Build Checklist. This keeps each kind of information in a consistent place and makes sorting and filtering more reliable than putting several pieces or set numbers into one cell.
On Owned Parts, use columns A through D for Part ID, Color ID, Description, and Quantity. On Build Checklist, use columns A through E for Part ID, Color ID, Required Quantity, Owned Quantity, and Missing Quantity. Use a separate row when the same part type appears in two colors; color is part of the identification, not merely a note. If a database uses different identifiers for a physical element and a design, retain the source identifier and document which one you use consistently.
For a small collection, a single sheet may be enough. For a larger one, keep the three worksheets and use Excel tables: select the data and choose Insert > Table. Tables expand as you add rows and make headers easier to filter. Give each row one clear meaning, avoid merged cells in the data area, and do not insert decorative blank rows inside a list. Those habits help formulas, filters, and later exports work as expected.
Calculate owned and missing quantities in Excel
With part IDs in Owned Parts!A2:A500, color IDs in Owned Parts!B2:B500, and quantities in Owned Parts!D2:D500, enter part and color IDs in Build Checklist columns A and B. With the matching part ID in Build Checklist!A2 and color ID in Build Checklist!B2, enter this in D2 to total matching owned quantities:
=SUMIFS('Owned Parts'!$D$2:$D$500,'Owned Parts'!$A$2:$A$500,A2,'Owned Parts'!$B$2:$B$500,B2)
For example, if the matching rows in Owned Parts record 3 and 1 copies of the same part and color, D2 returns 4. SUMIFS adds quantities only when both identifiers match, so an identical part in a different color is not counted. Copy the formula down the checklist. Increase the 500-row limits if your inventory grows, or use Excel tables so the ranges can expand with the data.
With required quantities in Build Checklist!C2:C500 and the calculated owned quantity in D2, enter this formula in E2:
=MAX(C2-D2,0)
If C2 is 6 and D2 is 4, E2 returns 2 missing pieces. If you own 7 and need 6, it returns 0 rather than a negative number. Copy it down the column. These formulas count the quantities entered in the workbook; they cannot confirm that a piece is physically present, correctly identified, or in usable condition.
Make the workbook easy to use
Turn on filters so you can quickly show a theme, storage location, color, or shortage. In Excel for Microsoft 365 on Windows, select the data range and choose Data > Filter, or convert the range to a table using Insert > Table. Keep column headings specific—“Part ID” is more useful than “Part”—and use a consistent format for identifiers. If an identifier contains leading zeroes or letters, set its column to Text before entering or importing values so Excel does not reinterpret it.
Conditional formatting can make missing pieces stand out. Select the Missing Quantity cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, enter 0, and choose a format. A colored cell is only a visual cue; the numeric value remains the record to check. You can also sort the checklist by Missing Quantity to bring shortages to the top, or filter that column to show values greater than zero.
Use data validation for fields where a controlled choice is helpful, such as condition or storage location. On Windows, select the cells and choose Data > Data Validation. A short dropdown list can prevent entries such as “drawer 2,” “Drawer 2,” and “D2” from representing the same place. Do not use a dropdown to force a part into a category you cannot verify. Add a Notes column for uncertain identifications, damage, substitutions, or a source list’s edition.
Importing lists and keeping identifiers reliable
If you paste a parts list from another source, inspect it before relying on formulas. Confirm that each row has a part identifier, color identifier, and numeric quantity, and check that the columns have not shifted. A common import problem is quantities stored as text; they may look like numbers but fail to add as expected. Another is a repeated part-and-color combination: SUMIFS handles repeated rows in Owned Parts by adding them, but duplicate rows in a checklist can make the same requirement appear twice.
Before combining duplicate checklist rows, verify that they really refer to the same part and color. Descriptions are useful for reading, but spelling and naming variations make them less dependable as formula keys. Keep IDs as the matching fields and descriptions as supporting information. If an import has no reliable color identifier, do not silently substitute a color name based on appearance; record the uncertainty and confirm the piece against a trusted catalogue or the original instructions.
When a discrepancy affects a specific set, compare its instructions or parts list with the imported data and note the decision. Keep an unedited copy of the original import in a separate sheet or file. That gives you a way to trace changes if a quantity was corrected or a part was identified differently. Availability, names, and database coverage may change, so record when you obtained a list if you expect to revisit the comparison.
Common problems and practical limits
- Counting by description alone: Similar-looking parts can have different designs, and one design can have several colors. Match on both part and color identifiers.
- Assuming set ownership proves completeness: A set record says you own the set, not that all its pieces remain together. Use a separate parts check when completeness matters.
- Mixing the checklist with total stock: A project’s required quantities belong on its checklist; your available quantities belong in the owned inventory. Keeping them separate avoids overwriting collection counts for each new build.
- Forgetting location and condition: An owned piece may be difficult to find or unsuitable for a build. Add storage and condition fields when they affect how you use the count.
- Overbuilding the workbook: Do not add many categories or formulas before you know what decisions the sheet needs to support. A clear, maintained list is more useful than a complicated file with stale entries.
A spreadsheet also has limits: it does not automatically identify a piece from a photo, verify a set’s production variant, or know that an item was moved or used elsewhere. It is a recordkeeping tool. Periodic spot checks—especially after sorting parts or dismantling a model—help keep its numbers meaningful.
When to use a spreadsheet or another tool
Excel is a good fit if you want control over columns, private local records, custom storage labels, and straightforward calculations. It is especially practical for a personal collection or a project checklist. A catalogue platform may be more convenient if you want to browse structured set inventories, compare your collection with a database, or use its own collection-management features. The trade-off is that database coverage, field names, and export or import options depend on that service.
There is no need to choose one approach exclusively. You can use a catalogue as a reference and keep a spreadsheet as your working record, provided you retain identifiers and check imported data. For people who only need to remember which sets they own, a simple Sets sheet may be sufficient; counting every loose piece is a larger maintenance commitment. Choose the level of detail that answers an actual question, such as “Which sets do I have?” or “How many red 1-by-2 bricks are available?”
Frequently Asked Questions
Is there an official LEGO inventory spreadsheet?
LEGO provides product and building-instruction resources, but a personal spreadsheet is not the same as an official collection-management workbook. Third-party catalogues offer set and part data; check their coverage and terms before using or importing it.
How should I track duplicate LEGO pieces?
Use one row for each part-and-color combination and record the total quantity. If duplicates are entered on separate rows, a SUMIFS formula can add their quantities when the part and color identifiers match.
Should I include spare parts from a set?
That depends on whether you want to reproduce the listed contents or check only the pieces needed to build the model. Keep spares in the checklist if they matter to your goal, and label the choice so the comparison is clear.
Can I use the same spreadsheet for sets and loose pieces?
Yes, but use separate worksheets or clearly separated tables. A set catalogue records ownership of complete sets; a loose-parts list records individual quantities and should not be inferred from set ownership.
What is the best identifier to use for a LEGO part?
Use a consistent part identifier together with a color identifier, preferably from the source catalogue you are using. Names and descriptions help you read the sheet, but may vary or be ambiguous, so they are weaker matching keys.
Related
- Free Yearly Expense Tracking Spreadsheet: Setup Guide
- Excel ODE Solver: How to Solve Differential Equations
- Free Wedding Budget Spreadsheet: The Knot’s Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation List: Build a Dynamic SourceTo make an Excel data validation list expand automatically, base it on an Excel Table, a named range,…
- How to Make a Fillable Form in Word Step by StepThe best way to make a fillable form in Word is to use the content controls in the…
- How to Delete an Excel File on the Web in OneDriveTo delete an Excel file online, find it in the OneDrive or SharePoint location where it is stored,…
- Google Sheets Percentage Bar: Make an In-Cell Progress BarTo make a percentage bar in Google Sheets, put your percentage in one cell and use the SPARKLINE…
- Bakery Inventory Spreadsheet: Setup and Stock ControlA bakery inventory spreadsheet is a workbook for tracking ingredients, packaging, finished goods, stock levels, and replenishment in…
- How to Enable Word Wrap in Excel and Adjust Cell TextTo enable word wrap in Excel, select the cell or cells, then choose Home > Wrap Text. Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.