How to get real analysis out of Dynamics 365 this afternoon

Who this is for: anyone with a Dynamics login and a question nobody's answered. No technical skill needed. What you'll need: Microsoft 365 with Copilot, and somewhere on OneDrive or SharePoint to save a file. How long: about forty minutes the first time, ten every time after.

The situation this fixes

Somebody asks, in a meeting, why membership is down in the North West. It's a reasonable question. You know the data exists. You also know that getting a defensible answer means asking someone to build a report, and that report is three weeks away and will arrive after the conversation has moved on.

So you do what everyone does, which is have an opinion instead.

There's a much shorter route, and it's slightly undignified: get the rows out of Dynamics into Excel and ask Copilot to do the analysis. It's low-rent. It's also genuinely good, and you can do it between two meetings without asking anyone's permission.

Step one: build the view you actually want, in Dynamics

Do the thinking here rather than in Excel. Exporting everything and sorting it out later is how people end up with 40,000 rows and no answer.

1. Open your membership area in Dynamics and pick a list close to what you want: your contacts or memberships list.
2. Click the view name at the top left, then Create view (or Edit columns on a personal view if you'd rather not add one).
3. Add the columns you'll want to slice by. For a lapse question that's grade, status, join date, end date, branch or region, and payment method. Six or seven columns, not twenty.
4. Filter to the population you care about: a date range wide enough to compare two or three cycles, not all history.
5. Save it. Give it a name you'll recognise in a month, like Lapse analysis 2024-2026.

Payment method is the column people leave out and then wish they hadn't. It's usually the strongest single predictor of whether somebody renews, and it costs you nothing to include it now.

Step two: export it, and choose the right kind of export

With your view on screen, click Export to Excel in the command bar. You get four options and the difference matters:

• Static worksheet. A flat snapshot. This is the one you want for Copilot.
• Static worksheet, page only. The same, but just the rows currently on screen. Almost never what you want.
• Dynamic worksheet. Refreshable from Dynamics later. Useful, and we'll come back to it.
• Dynamic PivotTable. A pivot wired to Dynamics. Skip it; Copilot will build better pivots than you'd configure by hand.

Take the static worksheet. It lands in your Downloads folder.

Step three: the bit that stops most people, and why

Copilot will not work on that file where it currently sits. It needs the file on OneDrive or SharePoint with AutoSave on, because it reads your rows through the live service rather than off your disk. A file in Downloads is invisible to it, and the Copilot button will either be missing or greyed out with no useful explanation.

This is the single most common reason people try this once and conclude it doesn't work.

1. Move the downloaded file into a OneDrive or SharePoint folder. Dragging it into your synced OneDrive folder in File Explorer is fine.
2. Open it in Excel and check AutoSave is On in the top left. If it's off, toggle it; if it won't toggle, the file isn't in a synced location yet.
3. Click any cell in your data and press Ctrl+T to format it as a table. Confirm the header row is ticked. Copilot works far better on a real table than on a loose range.
4. Give the table a name. On the Table Design tab, set Table Name to something like Members. You'll be able to refer to it by name when you ask questions.
5. Check your date columns are actually dates and not text. If they're left-aligned, they're text; select the column and use Data → Text to Columns → Finish to convert.

Dynamics often exports dates and lookup fields in a format Excel treats as text. Fixing that now takes two minutes. Skipping it produces confidently wrong answers about trends, which is much worse than no answer.

Step four: ask Copilot the questions you'd have waited three weeks for

Open Copilot from the Home tab. Then ask in plain English, one question at a time. Don't try to get everything in a single prompt.

Start with something you already know the answer to. It sounds like a waste of the tool. It's the fastest way to find out whether your export is sound, and it takes thirty seconds.

1. "How many rows are in this table, and what date range do the join dates cover?" That one is your sanity check.
2. "Show me a breakdown of members by grade and status as a PivotTable."
3. "What proportion of members who left in each of the last three years were in each grade?"
4. "Add a column showing how many complete years each member had been a member when they left."
5. "Compare lapse rates by payment method." This is usually where somebody in the room goes quiet.
6. "Which branch has the highest lapse rate among members with two to five years' standing?"

Copilot will add columns, build pivots and draw charts directly into the workbook. Everything it does is a normal Excel object afterwards, so you can keep it, edit it, or paste it into a paper.

Ask for the formula, not just the number

When it adds a calculated column, ask it what formula it used and why. Two reasons. You'll learn Excel faster this way than from any course, and you need to be able to defend the number if it ends up in front of a board.

A useful habit is to ask it to explain a result you didn't expect. Occasionally the explanation reveals that your export was wrong rather than your assumption. A filter you forgot, a status you didn't realise counted as active. Better to find that here than in the meeting.

The kind of thing it will write for you. Worth understanding, not worth typing from scratch.

=LET(
  joined,  Members[Join date],
  ended,   Members[End date],
  years,   DATEDIF(joined, ended, "Y"),
  years
)

What this is not, and the honest limits

It's a photograph. The moment you export, the file starts drifting from reality, and nothing warns you. That's fine for a question you're answering this week and actively dangerous for a number somebody starts quoting monthly.

Copilot is also working only with the columns you exported. It has no idea what your grades mean, whether a status of Inactive counts as lapsed, or that one branch changed its levy in March. You are the context. That's the job it can't do for you.

And it will occasionally be wrong in a way that looks tidy. Round numbers, a total that doesn't reconcile with your finance system, a trend built on 200 text-formatted dates it silently ignored. Check anything you're going to repeat.

Before anything from this leaves your desk, ask one question: what's missing? If the export excluded members with no recorded end date, or a whole branch that runs its own records, the analysis is fine and the conclusion isn't.

The next rung up, when you've outgrown the photograph

If you find yourself repeating the same export more than twice, stop exporting and go one step up the ladder. There are three rungs and each costs a bit more effort for a lot more durability.

• Dynamic worksheet instead of static. Same Excel file, but Data → Refresh All pulls current rows from Dynamics. Copilot still works. This alone kills most of the repetition.
• Power BI on live data. No export at all, definitions agreed once, and a dashboard your board can open themselves. More work, and it stops being your file and starts being the organisation's reporting.
• Ask a question of the whole estate. Excel only ever sees the columns you exported. When the question spans membership, payments, events and correspondence at once, a spreadsheet is the wrong shape and no amount of Copilot fixes that.

Do this yourself, or have it done

Everything above is yours, today, with licences you already hold. We'd rather you did it. A membership manager who has found something real in a spreadsheet asks much better questions of a reporting project than one who hasn't.

Where we help is the rung above. Turning the export you keep repeating into a refreshable view, agreeing what active and lapsed mean so that membership and finance stop producing different numbers, and building the reporting that runs on live data instead of a file in your OneDrive. That's Maintain and Evolve. And if you've already built a spreadsheet the team quietly depends on, bring it. We'd far rather adopt a working thing than start from a blank specification, and yours already encodes decisions nobody wrote down.