ElyxAI

Pharmacy stock in Excel: batch, expiry and reorder

Pharmacist / Pharmacy OwnerInventory ManagementFree Template

Pharmacy stock in Excel is one row per product and batch. The columns that matter at the counter are the product, the batch, the quantity on hand, the expiry date, and the minimum you refuse to go under. IF flags the row in two cases you define. The quantity is below that minimum, or the expiry falls inside the number of days you typed. SUMIF adds the quantity still on hand for one product across its batches, so a total does not hide an old batch behind a new one. This is the dispensary list, not a map of warehouse aisles and not a sales forecast. You type the quantity and the unit cost you paid. The stock value on the row is quantity times that cost. The reorder quantity is the minimum minus what is left, when that difference is positive. The steps below add the status, the value, the reorder quantity, and the expiry list. Elyx can add those columns to the export you already have. You still count the shelf. The trial lasts 7 days, with no card. When a flag looks wrong, check the cell before the formula. Sort the column the formula reads and look for blanks and for words that are not in the dropdown. A blank is invisible to COUNTIF and COUNTIFS. A date stored as text does not compare with a real date, so a late flag or a days count stays empty. Correct the cell, then read the flag again. Leave finished rows on the sheet. They are the history you divide by when you count a period. Deleting them to tidy the file removes that base. Filter them out of the view instead. If you open a new month, copy the sheet and name the old one with the month. The formulas stay as they are. Before you send the total, read three rows against the source you typed from. If those three match, the total is the total of this sheet. Pick the rows yourself. A formula that is right on the row you already calculated by hand is the one to copy down. Write the unit in the header: days, hours, or pieces. A number without a unit gets divided by the wrong base later. If two people maintain the sheet, the dropdown is the agreement on the words. Change the list in one place, not the cells one by one. Keep the source column next to the formula column so the check does not mean opening another file.

The Problem

A pharmacy export often has one line per product and no batch. Two deliveries of the same product then share a quantity, and the earlier expiry disappears. The reorder decision becomes a guess: you either order because the shelf looks thin, or you discover the shortfall at the counter. Split the row by batch. Keep the expiry on that row, and keep the minimum next to the product. The flag reads those cells. It does not order from a wholesaler, and it does not file a regulatory form.

Benefits

Each batch has its own row, so an earlier expiry is not hidden inside the product total.

IF flags the row when the quantity is under the minimum you typed, or when the expiry is inside the window you typed.

SUMIF adds the quantity of one product across its batches. The total and the batch stay visible together.

Stock value is the quantity times the unit cost you entered. The sheet does not look up a reimbursement price.

The reorder quantity is your minimum minus the quantity left, when that difference is above zero.

Step-by-Step Tutorial

1

Create the main table structure

Set up a new Excel worksheet with essential columns for pharmacy inventory tracking. Create headers in row 1: Product Name, SKU, Category, Unit Price, Current Stock, Minimum Stock Level, Reorder Quantity, Supplier, Last Restock Date, and Expiration Date. This foundation will organize all medication and supply data systematically.

Use Ctrl+T to convert your data range into an Excel Table for automatic formatting and easier formula references

2

Add sample pharmacy data

Populate the table with realistic medication examples. Include entries like Amoxicillin 500mg (SKU: AMX-500, Unit Price: $0.45, Current Stock: 250, Min Stock: 50), Ibuprofen 200mg (SKU: IBU-200, Unit Price: $0.12, Current Stock: 1200, Min Stock: 500), and Lisinopril 10mg (SKU: LIS-10, Unit Price: $0.85, Current Stock: 180, Min Stock: 100). This realistic data will help you test formulas accurately.

Include at least 15-20 products representing different categories (antibiotics, pain relievers, cardiovascular drugs, etc.) for comprehensive testing

3

Create a stock status indicator column

Add a new column titled 'Stock Status' to automatically flag inventory levels. Use an IF formula to compare current stock against minimum stock level, displaying 'Low Stock', 'Adequate', or 'Overstock' status. This helps pharmacists quickly identify which medications need reordering.

=IF(E2<F2,"Low Stock",IF(E2>(F2*2),"Overstock","Adequate"))

Apply conditional formatting with color coding (red for Low Stock, yellow for Adequate, green for Overstock) to make status instantly visible

4

Calculate inventory value per item

Create an 'Inventory Value' column that multiplies unit price by current stock quantity. This shows the monetary value tied up in each medication, helping with financial reporting and identifying high-value inventory items that require careful management.

=D2*E2

Format this column as currency (e.g., $0.00) for better financial clarity and professional presentation

5

Build a total inventory summary section

Create a summary section below your main table (starting around row 25) to calculate key metrics. Use SUMIF to total all inventory values, count low-stock items, and calculate total units in stock. This dashboard gives management a quick overview of pharmacy inventory health.

=SUMIF(K:K,"Low Stock",1) for counting low stock items; =SUM(J:J) for total inventory value

Place this summary in a visually distinct area with bold headers and shading to separate it from transaction data

6

Create a reorder alert using nested IF

Add a 'Reorder Status' column that combines multiple conditions: check if stock is below minimum AND expiration date is not within 30 days. This prevents ordering medications that will expire soon, a critical concern in pharmacy management.

=IF(AND(E2<F2,DAYS(H2,TODAY())>30),"REORDER NOW",IF(E2<F2,"Check Expiry",""))

Use conditional formatting to highlight 'REORDER NOW' cells in bright red for immediate attention by pharmacy staff

7

Build a supplier lookup reference table

Create a separate worksheet named 'Suppliers' with columns: Supplier Name, Contact, Phone, Lead Time Days, and Minimum Order Quantity. This reference table supports VLOOKUP formulas in your main inventory sheet, allowing automatic supplier information retrieval.

Keep supplier data on a separate sheet to maintain data integrity and make updates easier without disrupting main inventory calculations

8

Implement VLOOKUP for supplier details

In your main inventory table, add columns for 'Lead Time' and 'Min Order Qty'. Use VLOOKUP to automatically pull this information from the Suppliers sheet based on the supplier name. This eliminates manual data entry and reduces ordering errors.

=VLOOKUP(H2,Suppliers!A:E,4,FALSE) for Lead Time; =VLOOKUP(H2,Suppliers!A:E,5,FALSE) for Min Order Qty

Use FALSE (or 0) in VLOOKUP for exact matches, ensuring you get the correct supplier information without approximations

9

Calculate suggested reorder quantity

Create a 'Suggested Order Qty' column that uses a formula combining reorder quantity, current stock, and supplier minimum order requirements. This intelligent calculation suggests the optimal amount to order, accounting for pharmacy usage patterns and supplier constraints.

=IF(E2<F2,MAX(G2,VLOOKUP(H2,Suppliers!A:E,5,FALSE)),0)

This formula ensures you never order less than the supplier's minimum while respecting your pharmacy's reorder quantity preferences

10

Create an expiration tracking dashboard

Build a summary section that uses SUMIF to count medications expiring within 30, 60, and 90 days. This critical feature helps pharmacists manage product rotation, comply with regulations, and minimize waste from expired medications.

=SUMIF(I:I,"<"&TODAY()+30,1) for items expiring within 30 days; =SUMIF(I:I,"<"&TODAY()+60,1) for 60 days

Add a separate column showing 'Days Until Expiry' using =DAYS(I2,TODAY()) to identify which specific medications need priority in dispensing

Template Features

Low Stock Alert System

Automatically flags medications below minimum threshold with color highlighting, preventing stockouts and ensuring patient care continuity. Critical for compliance with pharmacy regulations.

=IF(C2<D2,"REORDER","OK")

Expiration Date Tracking

Identifies medications nearing expiration dates to minimize waste and prevent dispensing expired products. Reduces losses and maintains regulatory compliance.

=IF(AND(E2<TODAY(),E2>TODAY()-30),"EXPIRING SOON",IF(E2<TODAY(),"EXPIRED",""))

Inventory Valuation Dashboard

Calculates total inventory value in real-time by multiplying quantity on hand by unit cost, enabling accurate financial reporting and budget management.

=SUM(C2:C100*F2:F100)

Automatic Reorder Point Calculation

Computes optimal reorder quantities based on average daily usage and supplier lead time, reducing over-ordering while maintaining adequate stock levels.

=(G2*H2)+I2

Movement Velocity Analysis

Tracks fast-moving vs. slow-moving medications to optimize shelf space and identify obsolete inventory requiring disposal or return to suppliers.

=COUNTIF(J2:J100,TODAY()-30)/30

Batch & Lot Number Traceability

Maintains detailed records of batch numbers, expiration dates, and supplier information for regulatory audits and rapid recall management if required.

Concrete Examples

Same product, two batches

One product arrived twice. The older batch expires sooner. A single product total would hide that date.

Product A, batch 12: 40 units, expiry in 20 days. Product A, batch 18: 90 units, expiry in 200 days. Minimum for product A: 50 units.

Result: SUMIF shows 130 units for product A. The batch row with 20 days left stays on the expiry list. IF flags batch 12 because its expiry is inside the window you set, even though the product total is above the minimum.

Managing Medication Expiration and Waste Reduction

Sarah manages a community pharmacy and needs to minimize expired inventory loss while ensuring adequate stock of fast-moving medications. She tracks which medications expire frequently and adjusts ordering patterns.

Antibiotics (Amoxicillin 500mg): 150 units, 45 expire in 60 days. Statins (Atorvastatin 20mg): 200 units, only 5 expire in 60 days. Allergy meds (Cetirizine): 300 units, 120 expire in 45 days. Monthly loss from expiration: $1,200 average.

Result: Color-coded inventory report showing expiration timeline, automatic calculation of potential waste ($1,200), recommendations to reduce Amoxicillin and Cetirizine orders by 20%, and a '90-day expiration watch list' prioritizing which slow-moving items to promote or discount before expiration.

Optimizing Reorder Points During Seasonal Demand Fluctuations

Michael owns a pharmacy in a tourist destination and experiences 40% higher demand during winter months. He uses inventory data to adjust safety stock levels and reorder quantities seasonally to avoid stockouts while minimizing carrying costs.

Peak season (Dec-Feb): Cold/flu meds average 450 units/month. Off-season (Jun-Aug): same meds average 180 units/month. Reorder lead time: 5 days. Current reorder point: 200 units (year-round). Storage cost: $0.15/unit/month.

Result: Dynamic reorder point calculator showing: Winter reorder point should be 350 units (covers 5-day lead time + 40% higher usage), off-season reorder point 150 units. Projected savings: $800/year in reduced carrying costs. Automatic email alerts when winter stock drops to 350 units, summer stock drops to 150 units.

Pro Tips

Real-Time Stock Alert System with Conditional Formatting

Create automatic visual alerts when inventory falls below minimum thresholds. Use conditional formatting rules to highlight critical stock levels in red, warning levels in yellow, and optimal levels in green. This prevents stockouts of essential medications and overstocking of slow-moving items. Pair this with a helper column using formulas to flag items needing reorder.

=IF(B2<C2,"REORDER",IF(B2<C2*1.5,"LOW","OK"))

Expiration Date Tracking with Automated Removal Alerts

Build a dynamic expiration tracking system using TODAY() function to automatically calculate days until expiration. Create a pivot table or filtered view showing medications expiring within 30, 60, and 90 days. This is critical for pharmacies to minimize waste, comply with regulations, and prioritize FIFO (First In, First Out) dispensing. Use conditional formatting to highlight approaching expiration dates.

=DAYS(E2,TODAY()) and =IF(DAYS(E2,TODAY())<30,"URGENT",IF(DAYS(E2,TODAY())<60,"SOON","OK"))

ABC Analysis for Inventory Prioritization

Classify medications using ABC analysis (Pareto principle) based on annual consumption value. Calculate the cumulative percentage of total inventory value, then segment into A (top 20% value), B (middle 30%), and C (remaining 50%). This allows you to focus tight controls on high-value items, implement standard procedures for moderate items, and use simpler tracking for low-value medications, dramatically improving efficiency.

=SUMPRODUCT((SUMIF($A$2:$A$100,ROW($A$2:$A$100)*0+ROW($A$2:$A$100),$D$2:$D$100)<=SUMIF($D$2:$D$100,">0")*0.8)*1)

Turnover Ratio Dashboard with VLOOKUP for Quick Performance Review

Build a one-page dashboard calculating inventory turnover ratios (COGS ÷ Average Inventory) for each medication category. Use VLOOKUP to pull sales data and inventory snapshots into summary tables. This reveals slow-moving stock requiring promotions or discontinuation, and fast-moving items needing safety stock increases. Update monthly to identify trends and optimize purchasing patterns.

=VLOOKUP(A2,SalesData!$A:$D,3,FALSE)/VLOOKUP(A2,InventoryData!$A:$D,4,FALSE)

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Pharmacy inventory management, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions

See also