ElyxAI

Pipeline hygiene in Excel: deals with no next step

Revenue Operations ManagerPipeline Hygiene

A pipeline hygiene sheet is one row per deal. You type the deal name, the owner, the stage, the amount, the next step in words, and the date of that next step. A formula marks the row missing when the stage is still open and either the next step or the next date is blank. It marks the row stale when the next date is already before today. It leaves closed-won and closed-lost blank, because those deals are not waiting on a next step. This sheet is not a sales forecast, a lead score, or a customer-service log. It does not predict which deal will close. It answers a narrower question: which open deal has no next step, or a next step whose date has passed. An amount you typed can be summed for the missing rows. The sum is not a forecast. Stage names come from a list you control: your open stages, plus the two closed words closed-won and closed-lost. A synonym such as won or dead will not match the formula, and a closed deal will look open. COUNTIFS then counts missing and stale by owner. If you paste rows from an export, the next step is still only whatever landed in that column. A blank cell is missing, even if someone mentioned a step in a call. The trial lasts 7 days, with no card. Elyx can write the hygiene column and the counts. You still name the next step.

The Problem

The pipeline fails the Monday review when the next step lives in a chat. The deal row shows a stage and an amount, and the action was a sentence that scrolled away. Nobody can filter to the deals that have no date. Copying the chat into a note without a date does not fix it. The date has to be a real date, and the next step has to be one sentence in its own column. A second failure is using this sheet as a forecast. A weighted total, a close month, or a probability is a different question. If you add them here, the missing-step count gets mixed with a number the meeting will treat as a promise. Keep the amount only so you can see the sum of rows that are missing a step. Do not divide it by a win rate you typed from memory. A third failure is deleting a deal when it closes. Set the stage to closed-won or closed-lost and leave the row. The hygiene cell goes blank, so the missing count drops the deal without erasing the history of the last next step.

Benefits

Each deal is one row with an owner, a stage, a next step, and a next date.

Missing means an open deal with a blank next step or a blank next date.

Stale means the next date is before today and the deal is still open.

Closed-won and closed-lost drop out of hygiene without being deleted.

COUNTIFS and SUMIFS read the hygiene word. You do not retype the total.

Step-by-Step Tutorial

1

Create the list and the header

Name the sheet Pipeline. In row 1 type: Deal, Owner, Stage, Amount, Next step, Next date, Hygiene, Note. Deal is a name you can find again. Owner is one person. Stage is a word from the list. Amount is a number you typed, in the currency you already use, with no formula hiding inside it. Next step is one sentence. Next date is a real date. Hygiene is a formula. Note is optional. Freeze row 1. Do not merge cells.

A table will copy the hygiene formula to new rows. A plain range works if you fill the formula yourself.

2

Lock stage names, including the two closed words

Select Stage from row 2 down. Data, Data Validation, List. On a sheet named Lists, type the open stage names you actually use, then closed-won and closed-lost. The hygiene formula looks for those two closed words exactly. Won, lost, or closed will be treated as open, and a finished deal will show as missing if the next step is empty. Add a stage on Lists before anyone types it. Two spellings of one owner do the same damage to a filter, so validate Owner the same way if the team is small enough to list.

Do not put a probability in the stage name. Stage is a word. Amount is a number. Hygiene is the check.

3

Enter one open deal and one closed deal

On row 2 type an open stage, an amount such as 1000, leave Next step and Next date empty. On row 3 type the same owner, stage closed-won, an amount, and leave the next step empty too. You will predict that row 2 is missing and row 3 is blank in Hygiene, before you trust the column. Do not type a sentence in Note and expect the formula to read it. The formula reads Next step and Next date only.

Amount can be zero. Zero is a number. Blank amount is not the same question as a missing next step. Do not use amount to signal hygiene.

4

Write the hygiene flag

With Stage in C2, Next step in E2, and Next date in F2, use =IF(OR(C2="closed-won",C2="closed-lost"),"",IF(OR(E2="",F2=""),"missing",IF(F2<TODAY(),"stale","ok"))). Closed stages return a blank, even when the next step is empty. An open stage with either cell empty is missing. An open stage with both cells filled, and a next date before today, is stale. Otherwise the row is ok. TODAY moves forward when the file opens, so a date that was ok yesterday can be stale this morning without anyone editing the row. Format Next date as a date. If F2 is text, the comparison F2<TODAY() will not behave like a date.

=IF(OR(C2="closed-won",C2="closed-lost"),"",IF(OR(E2="",F2=""),"missing",IF(F2<TODAY(),"stale","ok")))

A next step with no date is missing, not ok. The date is what makes the step checkable.

5

Count missing and stale by owner

On a sheet named Morning, put an owner in A2. Missing rows for that owner are =COUNTIFS(Pipeline!B:B,A2,Pipeline!G:G,"missing"). Stale rows are =COUNTIFS(Pipeline!B:B,A2,Pipeline!G:G,"stale"). All missing rows, regardless of owner, are =COUNTIFS(Pipeline!G:G,"missing"). A blank hygiene cell, which is what closed deals show, is not missing and not stale. Start the ranges at row 2 if you want the header excluded. The header says Hygiene, not missing, so a whole-column count is usually safe. Write the rule next to the count.

=COUNTIFS(Pipeline!B:B,A2,Pipeline!G:G,"missing")

Filter is for reading the rows. COUNTIFS is for the number. Do not type over the formula after a filter.

6

Sum the amount on missing rows only

The amount sitting on missing rows is =SUMIFS(Pipeline!D:D,Pipeline!G:G,"missing"). For one owner, add the owner criterion: =SUMIFS(Pipeline!D:D,Pipeline!B:B,A2,Pipeline!G:G,"missing"). SUMIFS ignores text in the amount column, so a header is safe. It also ignores closed deals, because their hygiene cell is blank. Do not call this sum a forecast. It is the amount you typed on rows that failed the next-step check. If you need a forecast, that is a different sheet. Label this total Missing amount so the meeting cannot misread it.

=SUMIFS(Pipeline!D:D,Pipeline!G:G,"missing")

Currency format belongs on Amount. The sum uses the values, not the format.

7

Sort the review without deleting closed deals

Filter Hygiene to missing, then to stale. Sort Next date oldest first on the stale list: the oldest date is the step that slipped first. Remove the filter before you close the file. When a deal closes, change Stage to closed-won or closed-lost. Do not delete the row. Hygiene goes blank. The last next step remains visible if someone asks what the last action was.

Conditional formatting on the word missing makes those rows obvious even before the filter.

8

Check three rows before you share the count

An open deal with an empty next step must say missing, and COUNTIFS must include it. An open deal with a next step and a next date of yesterday must say stale, not ok. A closed-won deal with an empty next step must show a blank hygiene cell and stay out of the missing count. A next date equal to today is not stale, because the test is strictly before today. If a check fails, fix the stage word or the date format before you send the count. The count is only this sheet.

Write the three checks on Morning if more than one person updates deals.

Template Features

Hygiene from the cells, not from a retyped word

Missing, stale, ok, or blank. Closed stages force a blank.

=IF(OR(C2="closed-won",C2="closed-lost"),"",IF(OR(E2="",F2=""),"missing",IF(F2<TODAY(),"stale","ok")))

One next step, one date

The sentence and the date are separate columns. A date buried in the sentence will not go stale.

Count by owner

COUNTIFS reads the owner and the hygiene word.

=COUNTIFS(Pipeline!B:B,A2,Pipeline!G:G,"missing")

Amount on missing rows

SUMIFS adds the amounts you typed where hygiene is missing. It is not a forecast.

=SUMIFS(Pipeline!D:D,Pipeline!G:G,"missing")

Closed deals stay on the sheet

Changing the stage clears hygiene. The row remains for the last next step.

Concrete Examples

An open deal with no next step

Monday review. One deal is still in an open stage. Nobody filled the next step.

Stage: an open word from your list. Next step: empty. Next date: empty. Amount: 1000.

Result: Hygiene is missing. The owner count of missing includes the row. The missing-amount sum includes 1000. The 1000 is the amount you typed, not a prediction.

A next date that slipped

The next step is filled. The date was yesterday. The stage is still open.

Next step: send the proposal. Next date: the day before today. Stage: open.

Result: Hygiene is stale, not missing, because both cells are filled and the date is before today. A date equal to today would be ok. COUNTIFS for stale includes this row. COUNTIFS for missing does not.

A won deal with an empty next step

The deal closed. The next step was never filled. You do not want it on the missing list.

Stage: closed-won. Next step: empty. Amount: 1000.

Result: Hygiene is blank. The missing count ignores the row. The missing-amount sum ignores the 1000. The row is still on the sheet.

Pro Tips

Paste is not a next step

An export can fill Deal, Owner, Stage, and Amount. If the next-step column arrives blank, hygiene will say missing. That is the point. Do not hide the blank by typing a dash. A dash is text, and the row would look ok once a date is present.

Today is not stale

The test is next date strictly before TODAY. A step due today is ok until tomorrow, when the file recalculates. If you want today included, change the test to F2<=TODAY() and use it on every row. Do not mix the two.

One owner per row

Two names in Owner will not match either person in COUNTIFS. Split the deal or name the single owner who has the next step.

Do not store a probability here

A win rate typed beside the amount will be read as part of the review even if no formula uses it. Keep forecast math on the forecast sheet. This sheet only marks missing, stale, and ok.

Formulas Used

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

Example request

“Build this in my workbook: Deals missing a next step, 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