Excel for Pharmacy Students: Building a Stock and Sales Sheet
A pharmacy has to know what it has on the shelf, what it has sold and what it needs to reorder. A small Excel workbook can do this for a practice exercise, a student project or a very small shop, and building one is a good way to learn the tools that matter most: tables, formulas, lookups, validation and conditional formatting. This guide builds a simple stock and sales sheet step by step. All products and numbers are invented for practice. A real pharmacy needs proper records that follow national rules, and a spreadsheet is no substitute for a dedicated system.
The plan: three sheets
Use three worksheets, each with one job. Separating them keeps the formulas simple and the data clean.
| Sheet | Purpose | Main columns |
|---|---|---|
| Products | One row per product | Code, Name, Unit price, Opening stock, Reorder level |
| Sales | One row per sale | Date, Code, Quantity, Unit price, Total |
| Stock | Current stock per product | Code, Name, Opening, Sold, Remaining, Status |
Step 1: Build the Products sheet as a table
Type the headings in row 1 and a few practice products below. Then click inside the data and press Ctrl and T (or choose Insert, then Table). Tick the box saying the table has headers, and name the table on the Table Design tab, for example tblProducts. A table grows automatically when you add rows and keeps formulas consistent.
| Code | Name | Unit price | Opening stock | Reorder level |
|---|---|---|---|---|
| P001 | Practice product A | 1.50 | 200 | 50 |
| P002 | Practice product B | 3.20 | 120 | 30 |
| P003 | Practice product C | 0.80 | 500 | 100 |
The names are placeholders on purpose. Do not use real prices or stock figures from a business for an example file.
Step 2: Build the Sales sheet
Make a second table named tblSales with the columns Date, Code, Quantity, Unit price and Total. You will fill Unit price and Total with formulas, and you will type only the date, code and quantity.
Limit mistakes with data validation
Typing a wrong code is the most common error. Excel can offer a drop-down list instead. Select the Code column cells, choose Data, then Data Validation, set Allow to List, and enter =INDIRECT("tblProducts[Code]") as the source. The drop-down then lists every product code. For Quantity, choose Whole number greater than zero, so no one enters a negative or decimal quantity.
Look up the price automatically
In the Unit price cell, use a lookup so the price always comes from the Products sheet. In current versions of Excel you can use XLOOKUP.
=XLOOKUP([@Code], tblProducts[Code], tblProducts[Unit price], "Not found")
If your version does not have XLOOKUP, use VLOOKUP or INDEX and MATCH.
=INDEX(tblProducts[Unit price], MATCH([@Code], tblProducts[Code], 0))
In the Total column, enter =[@Quantity]*[@[Unit price]]. Because the sheet is a table, Excel fills the formula down for every new row.
Step 3: Calculate remaining stock
On the Stock sheet, list each product code, using a copy of the Products list or a formula that refers to it. Then use SUMIF to add up the quantity sold for each code.
=SUMIF(tblSales[Code], [@Code], tblSales[Quantity])
The Remaining column is the opening stock minus the quantity sold. This basic version ignores new deliveries. To include them, add a Purchases sheet with the same structure as Sales, and use a second SUMIF so that Remaining equals Opening plus Received minus Sold.
Step 4: Flag products that need reordering
A stock sheet is useful only if it tells you when to act. In the Status column, compare Remaining with the reorder level.
=IF([@Remaining]<=[@[Reorder level]], "Reorder", "OK")
Then add colour. Select the Status column, choose Home, then Conditional Formatting, then Highlight Cells Rules, then Text that Contains, and make “Reorder” red. A red cell is easy to see on a long list.
Step 5: Summarise sales with a PivotTable
Click inside the Sales table, choose Insert, then PivotTable, and place it on a new sheet. Drag Code to Rows and Total to Values to see revenue per product. Drag Date to Columns, and group it by month, to see trends. After you add sales, right-click the PivotTable and choose Refresh.
Step 6: Protect and back up the file
- Protect formulas. Lock formula cells and protect the sheet from the Review tab, so formulas are not overwritten by accident.
- Keep a backup. Save a dated copy regularly. A spreadsheet can be corrupted or deleted.
- Do not store personal data. A stock sheet does not need patient names, and files with personal information need proper protection.
A short practice exercise
- Create the three products above in a table.
- Enter five sales rows with different codes and quantities.
- Check that Unit price and Total fill in correctly.
- Check that Remaining equals Opening minus Sold for every product.
- Sell enough of one product to push it below the reorder level, and confirm that the Status turns to Reorder.
Common mistakes
- Typing prices by hand in every sale. Use a lookup so a price change is made in one place.
- Mixing text and numbers. A quantity stored as text will not add up.
- Merged cells. They break tables, sorting and PivotTables.
- Leaving blank rows. Blank rows can cut a range in half. Use tables.
- Treating the sheet as a legal record. A real pharmacy must keep registers that comply with national requirements.
Key takeaways
Keep products, sales and stock on separate sheets, turn each into a table, use lookups instead of retyping prices, add validation to prevent errors, and use SUMIF, IF and conditional formatting to show what needs reordering. These skills carry over to many other spreadsheet tasks.
This is a practice exercise with invented data and is not a substitute for a licensed pharmacy management system or required records. See our Disclaimer.