Pharmacy Management System in Excel: Items, Sales Bills, Stock, Expiry Alerts and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: pharmacy-medical-store-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Items, Sales, Stock, Bill, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Ravina Medical, Nagpur, as of 17 October 2026 (made-up data). Ten items in four categories, 10% discount on MRP, a 90-day near-expiry window.

Sales are ₹23,906.70 from 68 bills with a margin of ₹5,523.70 (23.1%). This is a stock and sales record, not medical advice. For a home medicine refill list, see the medicine stock tracker.

2. Build it sheet by sheet

Step 1. Setup and Items: the master list

1. On Setup type the as-of date in B3, the discount in B4 (10%) and the near-expiry window in B5 (90 days).
2. On Items one row per item: Code, Item, Category, Lot no, Expiry, MRP, Cost, Opening, Reorder level. Category comes from a drop-down.

Step 2. Sales: one row per item sold

1. Headers: Date, Bill no, Code, Item, Qty, MRP, Amount, Cost, Key. Type the code; D5 fills the name: =XLOOKUP(C5,Items!$A$5:$A$14,Items!$B$5:$B$14,"Check code").
2. F5 brings MRP, G5 the amount =ROUND(E5*F5*(1-Setup!$B$4),2) and H5 cost with XLOOKUP.
3. I5 builds a key for the bill: =B5&"-"&COUNTIF($B$5:B5,B5) gives B-007-1, B-007-2 and so on.

Step 3. Stock: closing, expiry and order list

1. C5 opening, D5 sold =SUMIFS(Sales!$E$5:$E$2000,Sales!$C$5:$C$2000,A5), E5 closing =C5-D5, F5 value at cost.
2. H5 days to expiry =G5-Setup!$B$3 (date maths) and I5 the flag: =IF(H5<0,"Expired",IF(H5<=Setup!$B$5,"Near expiry","OK")).
3. J5 order list with AND: =IF(AND(E5<=Items!I5,I5="OK"),"Yes","No"). Expired stock is not reordered; it goes back to the supplier.
4. Colour I5:I14 red and amber with conditional formatting.

Step 4. Bill: print one bill

1. Type the bill no in B4. D4 finds the date and B7 the first item: =XLOOKUP($B$4&"-"&A7,Sales!$I$5:$I$2000,Sales!$D$5:$D$2000,"").
2. Bill B-007 shows cough syrup, antiseptic and a bandage roll, ₹400.50 after discount. Set the print area to A1:E14.

Step 5. Report and Dashboard

1. Report B5 sales by category: =SUMPRODUCT(SUMIFS(Sales!$G$5:$G$2000,Sales!$C$5:$C$2000,Items!$A$5:$A$14)*(Items!$C$5:$C$14=A5)). SUMIFS gives sales per item; SUMPRODUCT adds those in the category.
2. B11 adds the value of stock that is expired or near expiry: ₹2,878.
3. Dashboard KPI cards: sales, margin %, bills (a distinct count), items to act on, value at risk and items to order. Charts: sales by category and stock value by category.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. One row for two lots of the same item. Different lots have different expiry dates. Give each lot its own code (M06A, M06B) so the flag is right.
2. Reordering expired stock. Closing stock includes expired bottles. The AND test stops an Expired item from showing Order = Yes.
3. Discount typed on each row. Keep the discount on Setup. A changed rate then applies to all new bills the same way.
4. Selling from an expired lot. Check the Stock flag before billing. The bill footer also reminds the counter to check expiry.
5. Counting bills with COUNTA on Sales. A bill with three items is three rows. Use a distinct count of bill numbers.

5. More real examples

Example 1: Clinic dispensary. Replace MRP with the clinic rate, drop the discount and add a Patient ID column on Sales.

Example 2: Veterinary store. Same layout; add a Species column on Items and report sales by species.

Example 3: Distributor. Add a Retailer column on Sales and an outstanding sheet like the khata book.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I find medicines expiring in 90 days in Excel?

Days to expiry = expiry − as-of date. Show "Near expiry" when that is between 0 and 90 and "Expired" when it is below 0.

How do I make a bill print from a bill number?

Give each sale row a key of bill no and line number, then XLOOKUP the key "B-007-1", "B-007-2" on the Bill sheet.

What discount should a medical store give?

That is your business decision. The template keeps it as one input on Setup; MRP is the ceiling price printed on the pack.

How do I count the number of bills?

=SUMPRODUCT((range<>"")/COUNTIF(range,range&"")) counts each bill number once.

Where does lot-wise stock value come from?

Closing quantity × cost per unit, as in Stock F5. The near-expiry project shows the same idea for a larger store.

7. Quick quiz

Ten questions on this page. Sign in to save your score.