Use Copilot in Excel to build a consultation follow-up tracker and check its formulas

What This Does

Consultation follow-up often lives in memory and in the inbox. A small tracker fixes that: one row per consultation, one flag column that says when a follow-up is overdue or has no date. Copilot in Excel can write the flag formula and the conditional formatting for you. You then test the formula on invented rows with known answers, so you trust the flags before real consultations go in.

Before You Start

• Excel is open with a blank workbook.
• Copilot is available to you. Microsoft's page says Copilot in Excel may not be included in your Microsoft 365 subscription or may be limited by organization settings. Look for the Copilot icon in the lower-right corner of Excel. If it is missing, ask your IT contact. For licensing, Microsoft points to the FAQ for Copilot in Excel or your IT admin. This guide states no price.
• Data check. The tracker holds codes, dates and a status only. No client names, A-numbers, receipt numbers, countries, case facts or contact details go in a cell. A consult code such as C-101 stands in for the person, and the real identity lives in your case management system. That also keeps the sheet safe to show an AI feature, though you should still confirm your firm approved Copilot for any workbook that touches client matters.
• You know what "follow up" means in your own practice. The date in column C is your own decision.

Steps

1. Set up the columns

In Row 1, type these headers:

• A: Consult code
• B: Consultation date
• C: Follow-up date
• D: Status (use open, retained or declined)
• E: Flag

2. Open Copilot in Excel

Select the Copilot icon in the lower-right corner of Excel. Microsoft says Copilot opens in edit mode by default. It also offers plan mode, where Copilot produces a plan for you to review and confirm before it edits, and chat mode, which answers without changing the workbook. Choose plan mode the first time so you see the approach before anything changes.

3. Ask for the formula and the formatting

Paste this prompt:

In E2, write a formula that does this for each row.
Return blank if A2 is blank.
Return blank if D2 is anything other than "open".
Return "NO FOLLOW-UP DATE" if C2 is blank.
Return "FOLLOW UP" if C2 is before today's date.
Otherwise return blank.
Fill the formula down to row 301.
Then add conditional formatting that highlights any row where E is not blank.
Add a dropdown for column D with open, retained and declined.

Review the plan, confirm it, and let Copilot make the change. You can use the Stop button in the chat input if it heads in the wrong direction.

4. Compare Copilot's formula with this one

The formula in E2 should behave like this one. Compare the logic even if the wording differs:

=IF(A2="","",IF(D2<>"open","",IF(C2="","NO FOLLOW-UP DATE",IF(C2<TODAY(),"FOLLOW UP",""))))

If Copilot's formula tests a different condition, say what is wrong and ask it to fix that part.

5. Test with invented rows

Type these invented rows into rows 2 to 6. The expected answers assume today is Monday 2026-10-26. To test on a different day, make a copy of the workbook and temporarily replace TODAY() in the formula with DATE(2026,10,26), then restore TODAY() on the working copy.

Add a count of overdue follow-ups in a cell of your choice, for example G1:

=COUNTIF(E2:E301,"FOLLOW UP")

The expected result is 1. If you get another number, fix the formula before you enter a real consultation.

Real Example

Scenario: You hold several consultations a week and want a list of open ones whose follow-up date has slipped.

What you do: Build the tracker in Steps 1 to 4, enter the five invented rows, and check the flags against the table.

What you get: C-101 shows FOLLOW UP, C-104 shows NO FOLLOW-UP DATE, and the other three rows stay blank. The count cell shows 1. Because the result is ordinary Excel formulas, you can read and change them.

Tips

• Go one step past speed. Once you have a few months of rows, switch Copilot to chat mode and ask a question about the outcome: "Compare the gap between consultation date and follow-up date for retained rows and declined rows. What differs?" Check the answer against the cells yourself. It points you to a pattern in how you follow up, and the decision about what to change stays with you.
• The date in column C is a reminder you set, not a deadline from any agency or court. Keep case deadlines in your case management system.
• Re-run the five test rows after you change the formula.

Tool interfaces change. If a button has moved, look for the Copilot icon or a similar AI option in the same area of Excel.