How do you set reorder points in Excel or Google Sheets?

To set reorder points in Excel or Google Sheets, give each item one row with its average daily sales, vendor lead time in days and safety stock, then calculate reorder point = daily sales × lead time + safety stock. Add a status column that compares on hand plus on order with that number, and a conditional formatting rule that highlights every row at or below it.

What layout works for a reorder sheet?

Keep two tabs: a Sales tab with one row per sales line (item in column A, date in column B, quantity in column C) and an Items tab with one row per item. Put the column names in the first row of each and leave no totals rows in the middle.

Keep every column on the same tab. Google's help notes that conditional formatting formulas can only reference the same sheet unless you use INDIRECT, so a status rule is simpler when everything sits on one row.

What formulas calculate the reorder point?

Three formulas in row 2, filled down for every item. They use SUMIFS and IF, which both Excel and Google Sheets support.

1. Daily sales (E2): =SUMIFS(Sales!C:C, Sales!A:A, A2, Sales!B:B, ">="&TODAY()-365)/365 adds the last 365 days of units for the item and divides by 365.
2. Reorder point (H2): =ROUNDUP(E2*F2+G2, 0) covers demand during the lead time plus the safety stock, rounded up to a whole unit.
3. Status (I2): =IF(C2+D2<=H2, "Reorder", "OK") flags the item once stock you have and stock on its way no longer cover the reorder point.

Example: a 3/8-inch ratchet sells 730 units a year, so daily sales are 2. The vendor takes 10 days and you keep 10 units of safety stock. The reorder point is 2 × 10 + 10 = 30. With 24 on hand and none on order, the row reads Reorder.

How do you highlight low stock with conditional formatting?

Use a formula rule so the whole row turns a colour, not just one cell. The dollar sign before each column letter keeps the rule pointed at columns C, D and H as it moves down the rows.

In Excel:

1. Select the item rows, for example A2:I500.
2. On the Home tab, select Conditional Formatting > New Rule.
3. Under Select a Rule Type, choose Use a formula to determine which cells to format.
4. In Format values where this formula is true, enter =$C2+$D2<=$H2.
5. Select Format, pick a fill colour, and select OK.

In Google Sheets:

1. Select the item rows, for example A2:I500.
2. Select Format > Conditional formatting.
3. Under Format cells if, choose Custom formula is.
4. Enter =$C2+$D2<=$H2, choose a formatting style, and select Done.

Microsoft notes that the formula must start with an equals sign and return TRUE or FALSE; this one does.

What mistakes should you avoid?

• Forgetting what is on order. Comparing only on hand with the reorder point reorders items that are already on a truck.
• Stale counts. The sheet is only as good as column C. Update it after every receipt and count.
• Mixing units. If lead time is in days, sales must be per day too.
• Setting it once. The SUMIFS formula refreshes daily sales, but lead times and safety stock need a review each quarter.
• Seasonal items. A yearly average under-orders before a peak. The forecasting guide covers this.

Is there a quicker way than maintaining the formulas?

ReorderOwl is one option. You upload your item list and sales history, and it returns a reorder workbook for every item, with what to order, how much and from which vendor. It works in ChatGPT today; Claude setup is coming shortly. It is free to start. See ReorderOwl for spreadsheets for the columns it needs.

Frequently asked questions

What is the reorder point formula?

Reorder point equals average daily sales times lead time in days, plus safety stock. Reorder when on hand plus on order falls to that number.

Does Excel or Google Sheets have a reorder point function?

No. You build it from ordinary formulas such as SUMIFS, ROUNDUP and IF.

How do I highlight a whole row when stock is low?

Select the rows and add a conditional formatting rule that uses a formula, such as =$C2+$D2<=$H2, with a dollar sign before each column letter.

Why does my Google Sheets rule not work across tabs?

Google's help says conditional formatting formulas can only reference the same sheet unless you use INDIRECT. Keep the columns the rule needs on the same tab.

How often should I update reorder points?

Let the sales formula refresh daily sales automatically, and review lead times and safety stock at least each quarter.

Sources

1. Use conditional formatting to highlight information in Excel, Microsoft Support, accessed 2026-10-01
2. Use conditional formatting rules in Google Sheets, Google Docs Editors Help, accessed 2026-10-01
3. SUMIFS function, Microsoft Support, accessed 2026-10-01
4. Google Sheets function list, Google Docs Editors Help, accessed 2026-10-01