Hey everyone,

I'm currently working on a massive data-gathering project in Excel, and doing it manually is turning into a huge bottleneck. I need to compile a complete spreadsheet of Athletic Directors for the NJCAA (National Junior College Athletic Association).

Because the NJCAA covers all 24 regions across the US, there are hundreds of junior colleges to go through.

Here is the exact layout I need in Excel:

• First Name
• Last Name
• Email Address
• College Name
• City
• State

Right now, clicking through every single college directory, copying names, hunting down emails, and pasting them into Excel is taking forever.

For those who regularly work with automation or lead generation, how can I leverage AI to speed this up or automate it?

I recently got access to the new =COPILOT() worksheet function in Microsoft 365 and spent some time trying it on everyday spreadsheet tasks.

Here are a few examples that I thought were genuinely useful.

1. Generate sample data

You can ask Copilot to generate things like:

• Fictional project names
• Employee job titles
• Product descriptions
• Customer comments

Example:

=COPILOT("Generate 10 fictional project names")

Possible result:

Project Name
Project Aurora
Green Horizon
Northstar Initiative
BluePeak
Atlas Connect
NovaWorks
Summit Path
BrightBridge
Vertex One
Clearview Project

2. Categorize text automatically

Suppose column A contains support tickets.

Support ticket
Can't sign in to Microsoft 365
Outlook won't send emails
Printer is offline
Excel crashes when opening a large file
Forgot my Windows password
Outlook is running very slowly

In the next column, ask Copilot to categorize each ticket.

Example prompt:

=COPILOT("Assign a category to each support ticket", A2:A7)

Possible result:

Category
Account
Email
Hardware
Software
Account
Email

3. Estimate priority

If you have a long list of requests or issues, Copilot can suggest which ones should be reviewed first.

Example:

=COPILOT("Rate each request as High, Medium, or Low priority", A2:A20)

Obviously you'd still review the results, but it's a nice starting point.

4. Extract information from messy text

Suppose one cell contains:

John Smith
Senior Engineer
john@contoso.com

Instead of writing text formulas, you can simply ask Copilot to extract the information you need.

For example:

• Extract the person's name
• Extract the job title
• Extract the email address

5. Summarize long notes

If a column contains meeting notes or customer feedback, Copilot can create a short summary for each row.

Example:

=COPILOT("Summarize each note in one sentence", A2:A15)

6. Generate keywords

If you're building a product catalog or website, Copilot can generate search keywords for each product description.

Example:

=COPILOT("Generate three search keywords for each product", A2:A20)

7. Build a schedule or plan

You can also use Copilot to generate structured content based on information already in your worksheet, while adding a second prompt to guide the result.

Option 1: Meal planner

Suppose you have a weekly meal plan, and cell C2 contains a dietary preference.

Meal	Suggestion
Breakfast
Lunch
Dinner
Snack

Cell C2:

Vegetarian

Formula:

=COPILOT(
"Suggest one meal for each row.",
B5:B8,
"Dietary preference:",
C2)

Here, the first prompt tells Copilot what to generate, while the second prompt adds extra context from another cell.

You can change Vegetarian in cell C2 to High Protein or Gluten Free, and the suggestions update.

Option 2: Employee training plan

Week	Training Topic
Week 1
Week 2
Week 3
Week 4

Cell C2:

New customer support representative

=COPILOT(
"Suggest one training topic for each week.",
B5:B8,
"Role:",
C2)

Change the role to Sales Manager or Data Analyst, and you get a completely different plan.

Option 3: Travel packing list

Category	Items
Clothes
Electronics
Toiletries
Documents

Cell C2:

3-day business trip

=COPILOT(
"Suggest what to pack for each category.",
B5:B8,
"Trip type:",
C2)

Things worth knowing

• It works best with text rather than calculations.
• It isn't designed for heavy math or very large datasets.
• You need a Microsoft 365 Copilot license that's tied to a work or school account.
• The results are AI-generated, so you should always review them.
• If you want to keep the current AI-generated results, copy them and use Paste Special → Values, since they may change the next time the workbook recalculates.

I'm still experimenting with it, but these are the first use cases that actually felt practical instead of just being AI demos.

Have you found any prompts that work especially well?

I'm so happy 😀 I just need to do it a couple more times for it to be a muscle memory. Anyone have any suggestions what to learn next, or a test sheet of some kind?

Anyone can help me? I hate using the web version

Learned this one from a comment two days ago and it has already deleted a habit I'd had for years, so passing it on.

The problem: you write =SUM(A2:A1000) because you don't know how far your data goes. Too small and you miss rows; too big and you're evaluating 900 empty cells and any formula referencing them has to handle blanks. Then someone pastes row 1001 and your total is quietly wrong.

The fix (Excel 365, fairly recent): put a dot in the reference.

=SUM(A2:.A)

The dot means "trim". A2:.A reads from A2 down to the last non-empty cell in column A and stops there. Add rows, it extends. Delete rows, it shrinks. No table required, no OFFSET/COUNTA gymnastics, no volatile functions.

Three variants:

• A2:.A - trim the end (the one you'll use 95% of the time)
• A2.:A100 - trim the start
• A2.:.A100 - trim both

Where it actually changed something for me: I had a ranking formula wrapped in FILTER purely to drop the empty tail of a range I'd guessed at:

=SORT(FILTER(A2:B1000, B2:B1000>0), 2, -1)

With a trimmed ref the FILTER isn't doing that job any more:

=SORT(HSTACK(A2:.A, B2:.B), 2, -1)

I'd keep FILTER if you have genuinely blank cells in the MIDDLE of your data - trim only handles the tail, so a gap on row 40 still needs filtering. But if your FILTER exists only to compensate for a range you picked out of thin air, this replaces it.

Caveat: needs a current Excel 365 build. Not in Google Sheets, where the equivalent is just leaving the row number off (A2:A), which has done the same job there forever.

EDIT: corrected the trim-the-start syntax - it's A2.:A100, not .A2:A100 (the dot goes after the reference you're trimming from). Thanks u/OldJames47 for catching it.