Excel inventory data: free practice file with tasks and answers

Inventory is where Excel earns its keep in many companies. Somebody has to know what is on the shelf, what it is worth and what needs ordering before it runs out, and very often that somebody has a spreadsheet with a few hundred rows and a handful of formulas. Here is a stock list you can practice those formulas on, with tasks that have an answer key.

The file is the stock list of a small office supply wholesaler: 80 products, from ballpoint pens to standing desks, in six categories and from five suppliers. Every product has a SKU, units in stock, a reorder point, a unit cost and a sale price. Solve the tasks in the file first, then check your answers under each task on this page.

Inventory data: 80 products in six categories

The Inventory sheet has one row per product and nine columns:

The SKU is the only column where every value is unique, so that is what you look up. Product names are unique too in this file, but in a real stock list two products can easily share a name, such as two office chairs from different suppliers. Get used to looking up the code.

The data is clean on purpose: one header row, no blank rows, and numbers stored as real numbers. If you want to practice cleanup instead, take the messy data file.

The three numbers every stock list needs

Almost every question you get about inventory comes down to one of three things:

1. What do we have? A count or a sum of units, often with a condition: products in one category, units from one supplier, items that are out of stock.
2. What is it worth? Units times a price, added up. At unit cost it is the value on the balance sheet. At sale price it is what the stock would bring in.
3. What do we need to order? A comparison between two columns in the same row: is stock at or below the reorder point?

The first is COUNTIF and SUMIF territory. The second and third need a formula that works row by row before it adds up, and that is where SUMPRODUCT comes in. Lookups tie it together: when someone asks about one product, you find its row with XLOOKUP.

How to use the file

Open the file and go to the Tasks sheet. Each task has a number, a question and an empty cell for your answer. Write your formulas on the Inventory sheet, in an empty column to the right of the data, and type the result next to the task. Then check it against the answer on this page.

The data runs from row 2 to row 81. The answers on this page use exact ranges like E2:E81 because they are easy to read. Save a copy of the file before you sort or delete anything, because the answers only match the data as it is in the file.

The header row has filter buttons. They are a quick way to check an answer by eye: filter on Furniture, select the In stock column and read the sum on the status bar. If the status bar and your formula disagree, one of them has a condition you did not mean.

Formula tasks on the inventory data

The tasks start with a single function and work up to formulas that compare columns and multiply them. They are in the same order as in the file.

Count and add with one condition

The first tasks are warm-ups. COUNTIF counts the rows that match a condition, so the number of products in one category is =COUNTIF(C2:C81,"Electronics"). A condition can also be a number: =COUNTIF(E2:E81,0) counts the products with nothing on the shelf.

SUMIF adds up instead of counting. The three arguments are where to look, what to look for and what to add: =SUMIF(D2:D81,"TechLine",E2:E81) adds up the units in stock from one supplier. The two ranges have to start on the same row and be the same size. If they are shifted by one row, SUMIF does not complain. It just adds up the wrong rows. Practice it in SUMIF with a text criterion.

Two conditions with COUNTIFS

"How many office supplies are running low?" is two conditions: the category and the stock level. COUNTIFS takes them in pairs of range and criterion:

=COUNTIFS(C2:C81,"Office supplies",E2:E81,"<100")

A row only counts when both conditions are true. The comparison goes in quotes, with no space and no thousands separator. Try the same structure in COUNTIFS with two conditions.

Look up a product by SKU

When a colleague asks for the price of SKU-1042, you don't scroll. You look it up:

=XLOOKUP("SKU-1042",A2:A81,H2:H81)

XLOOKUP takes what to find, where to find it and what to return. It looks for an exact match unless you ask for something else, so you don't need the FALSE that VLOOKUP needs. Swap H2:H81 for G2:G81 and you get the unit cost instead. Practice it in Look up a price with XLOOKUP.

A SKU that is not in the list gives #N/A. That is correct, but it looks like a broken sheet to the person reading it. XLOOKUP has a fourth argument for exactly that case:

=XLOOKUP("SKU-1099",A2:A81,B2:B81,"Not found")

Now the cell says Not found when the SKU is missing and shows the product name when it exists. That is the version to use in a sheet other people type into. Practice it in XLOOKUP with a not-found value.

A word of warning: a SKU typed with an extra space, like "SKU-1042 ", does not match. If a lookup that should work returns #N/A, check the cell you are looking up from before you check the formula.

Stock value with SUMPRODUCT

Stock value is units times unit cost for each product, added up. You could add a column with =E2*G2, fill it down and sum it. That works, but SUMPRODUCT does it in one cell:

=SUMPRODUCT(E2:E81,G2:G81)

SUMPRODUCT multiplies the first cell of each range, then the second, and so on, and adds up all the products. The ranges must be the same size, or the formula returns #VALUE!. Swap G2:G81 for H2:H81 and you get the value at sale price instead. The difference between the two is the gross profit sitting on the shelf. Practice the idea in Order total with SUMPRODUCT.

Stock value for one category

To get the value for Furniture only, you need a condition inside SUMPRODUCT. A comparison on a range gives a list of TRUE and FALSE, and when you multiply it, TRUE counts as 1 and FALSE as 0:

=SUMPRODUCT((C2:C81="Furniture")*E2:E81*G2:G81)

Rows outside Furniture are multiplied by 0 and disappear. Rows in Furniture are multiplied by 1 and stay. Add another condition in parentheses and you have as many conditions as you like, with no SUMIFS in sight.

Compare two columns: what needs ordering

The reorder question is different from the others. The condition is not a fixed value. It is a different number in every row: is this product's stock at or below this product's reorder point? COUNTIF can't do that, because its criterion is one value for the whole range. SUMPRODUCT can:

=SUMPRODUCT(--(E2:E81<=F2:F81))

The comparison checks all 80 rows at once and gives TRUE or FALSE for each. The two minus signs turn TRUE into 1 and FALSE into 0, and SUMPRODUCT adds them up. You will see this double minus a lot in formulas written by people who use Excel every day.

If you prefer to see it row by row, add a column with =IF(E2<=F2,"Order","OK"), fill it down and count the Order rows. That is a fine way to check the SUMPRODUCT answer, and it is the kind of column a purchasing team actually uses. Turn it into a filter, and you have the order list for the week.

Find the most expensive product

MAX gives you the highest sale price, but the question is which product it belongs to. INDEX and MATCH answer that together:

=INDEX(B2:B81,MATCH(MAX(H2:H81),H2:H81,0))

MATCH finds the position of the highest price in column H, and INDEX returns the product name at the same position in column B. The 0 means exact match. This pattern works for any "which row has the largest" question: swap MAX for MIN to find the cheapest. Practice MATCH on its own in Find a position with MATCH.

Gross margin on one product

Gross margin is the share of the sale price you keep after paying for the product: one minus cost divided by price. For one SKU that is two lookups in the same formula:

=ROUND(1-XLOOKUP("SKU-1010",A2:A81,G2:G81)/XLOOKUP("SKU-1010",A2:A81,H2:H81),3)

Format the cell as a percentage to read it as a percent. Don't confuse margin with markup. Markup is the profit divided by the cost, and it is always the bigger number of the two. Ask which one is meant when a manager asks for "the margin".

The same calculation works on the whole stock. The value at sale price minus the value at unit cost is the gross profit sitting on the shelves:

=SUMPRODUCT(E2:E81,H2:H81)-SUMPRODUCT(E2:E81,G2:G81)

Divide the result by the value at sale price and you have the margin for the whole stock. It is close to the average of the product margins, but not quite, because expensive products with many units in stock weigh more. Work out both numbers and see how big the gap is.

Dates as conditions

The Last received column has real dates, so you can compare them. Products last delivered before July 1 are the ones that have sat the longest without a fresh delivery:

=COUNTIF(I2:I81,"<"&DATE(2025,7,1))

The comparison goes in quotes and the date is joined on with &. DATE builds the date from year, month and day, so the formula works however dates are displayed on your computer. More in the guide to Excel dates.

To see how long each product has waited since its last delivery, subtract the date from the end of the year: =DATE(2025,12,31)-I2 gives the number of days for the product in row 2. Fill it down and sort by the new column, and the slow movers float to the top. In a live file you would use TODAY() instead of a fixed date, so the number grows by itself every morning.

Check your answers like an inventory manager

A formula that returns a number is not the same as a formula that returns the right number. People who run stock lists for a living have a few habits that catch most mistakes before anyone else sees them:

• Does it add up? The units per supplier should add up to the total units in stock. Five SUMIF results that don't match one SUM mean a typo in a supplier name.
• Is the size plausible? The stock value of one category should be smaller than the total, and Furniture should be worth far more per unit than Paper. An answer ten times too big is usually a range that runs one column too far.
• Can you find one example? If the formula says a product needs ordering, filter the list and look at one of them. If it says nothing needs ordering, be suspicious.
• Does the count match the filter? Filter on a category and compare the row count on the status bar with your COUNTIF.

Inventory tasks are popular in Excel tests for jobs in purchasing, logistics and retail, and the questions look a lot like the ones on this page: a lookup, a count with two conditions, a stock value and a list of what to order. More on what gets tested in the guide to Excel for job interviews.

Practice without an answer key

Not everything in a stock list is a single number. These exercises train what you would do with the file at work:

• Build a reorder list. Add a column that says Order when stock is at or below the reorder point, then filter on it. Add another column with how many to order to get back to twice the reorder point.
• Make a summary per category with the six categories in one column, then COUNTIF for the number of products and SUMPRODUCT for the stock value next to it. Lock the ranges with $ so you can fill the formulas down.
• Highlight low stock with conditional formatting: a formula rule like =$E2<=$F2 colors the whole row. See how in the guide to conditional formatting.
• Sort by stock value. Add a column with units times unit cost and sort the list with the most valuable stock at the top. In most stock lists a few products hold most of the value.
• Build a pivot table with suppliers in rows and the sum of units in stock as values. Then add the category as a second row field.
• Turn the data into a table with Ctrl+T, and write the reorder formula with column names instead of ranges.

When an answer doesn't match

These are the mistakes that most often give a wrong number on stock data:

• Ranges of different sizes. SUMPRODUCT returns #VALUE! when E2:E81 meets G2:G80. Check that every range ends on row 81.
• Comparing to a fixed number instead of the row. "Below the reorder point" is a different number for every product. COUNTIF with "<20" answers a different question.
• Looking up the name instead of the code. Two products can share a name, and XLOOKUP returns the first one it finds. Look up the SKU.
• Margin and markup mixed up. Divide by the sale price for margin and by the cost for markup.
• A typo in the criterion. "Electronic" without the s returns 0 with no error. Copy the text from the data, or point to a cell that holds it.

Inventory data in Google Sheets

The file works in Google Sheets. Upload it to Google Drive and open it with Google Sheets. XLOOKUP, SUMPRODUCT, COUNTIFS and INDEX with MATCH work the same way, and the filter buttons come along. See the other differences in Google Sheets vs. Excel.

More practice data

This file is one of several free datasets with tasks and answers. Each one trains a different kind of formula:

• Sales and employee data: 240 orders and 60 employees, for SUMIF, COUNTIFS, averages and dates. The sales file is also used in the pivot table practice.
• Customer data: 150 customers, for lookups, text functions and counting with two conditions.
• Messy data: 60 orders full of extra spaces, text numbers and duplicates, for cleanup practice.
• Finance data: a year of bank transactions, for income, expenses, results and percentages.

In short

A stock list asks three questions: what do we have, what is it worth and what do we need to order. COUNTIF, COUNTIFS and SUMIF answer the first. SUMPRODUCT answers the second and the third, because it can multiply and compare row by row. XLOOKUP and INDEX with MATCH find the one product someone asks about. Solve the tasks in the file, check your answers here and find the mistake when a number is off.

To drill one formula at a time on a small sheet, start with the exercises below. They check your answer right away and show a hint when the formula is wrong. The first exercise in each topic is free.