People genuinely appreciate your work. They attend your sessions, tell you how helpful the material was, and encourage you to keep sharing. Many follow your posts for years and seem sincerely interested in what you have to say.

But sooner or later many trainers experience a confusing moment. When you offer a paid workshop, course, or training program built around the same topics you have been teaching for years, the response can be surprisingly quiet. The same audience that eagerly attended ten free webinars suddenly becomes hesitant.

At first it is easy to assume something went wrong. You might think the topic was not interesting enough, the price was off, or the audience simply is not that serious about learning.

Sometimes those explanations are true. But often something subtler is happening. Over time you may have unintentionally positioned yourself as someone who provides excellent free explanations, and your audience has quietly learned that if they wait long enough, the material will eventually show up somewhere without cost.

In other words, you have landed in what might be called the free content friendzone.

The culture of free knowledge in Excel

The Excel world has a long tradition of sharing knowledge openly. Long before social media, people were already publishing techniques and solutions on forums and personal blogs. Later came YouTube tutorials, LinkedIn posts, community meetups, and conference days where experts regularly share ideas for free.

Many of us learned Excel that way ourselves. And that culture has produced an extraordinary amount of knowledge and has helped millions of people improve their skills. But it also creates a particular mindset among learners. The thinking usually sounds something like this:

“That is interesting. I will probably find a tutorial about that somewhere.”

And in many cases, that assumption turns out to be correct. Between blogs, YouTube, documentation pages, and now AI tools that can generate step by step instructions on demand, it is easier than ever to find some explanation of almost any Excel topic.

In other words, the idea that knowledge should be freely available has spread even further. This is where the line between free content and professional training begins to blur.

Free content vs training

Free educational material and structured training are often treated as if they were interchangeable, but they serve very different purposes.

A blog post might show how Power Query reshapes messy data. A training session walks through an entire data-cleaning workflow from start to finish and explains why each step matters.

Both formats are valuable, but they solve different problems. Free material helps people discover what is possible. Training helps them incorporate those ideas into their work.

There is also a practical difference in the experience. Trying to piece together a skill entirely from free tutorials can feel a bit like wandering around Costco eating samples. You can certainly fill up that way if you try hard enough, but it takes a lot of effort and you never quite get the full picture of what the product is actually supposed to be. A proper meal is simply a better experience.

Good training works the same way. Instead of assembling fragments from dozens of sources, you get a coherent explanation of how everything fits together.

The scale problem

Another reality that many new Excel educators eventually discover is that direct-to-consumer training requires a huge audience.

Even if thousands of people follow your work, only a small percentage will ever purchase something. A typical funnel looks something like this:

This is why many experienced trainers rely on multiple outlets. Trade associations, conferences, publishers, and corporate training programs often provide the foundation of a sustainable career. These organizations understand that structured learning has value and are accustomed to paying for it.

Interestingly, this often leads to better teaching material. When trainers know they will be compensated for their work, they can spend more time developing examples, refining explanations, and designing exercises that genuinely help people practice new skills.

Do not take it personally

One of the most important lessons for independent educators is learning not to internalize the free-content expectation.

When a workshop does not sell well, it is easy to interpret that as a judgment about your expertise. In reality, the issue is often structural rather than personal. The internet has made information abundant, and audiences have become used to encountering new ideas casually.

What people actually pay for in training is not raw information. They are paying for structure, clear explanations, real-world examples, and the chance to ask questions and get guidance. Those things are very different from watching a quick tutorial online.

Communicating why the skill matters

There is also a communication challenge that Excel trainers sometimes overlook. Because we often teach through quick demonstrations or tips, we sometimes present our work as if it were just another clever trick.

When material is framed that way, audiences naturally treat it casually. They assume they can return to it later. But many Excel skills are far more significant than that. Learning how to structure data properly, automate repetitive work, or design reliable analytical models can fundamentally change how someone approaches their job.

Sometimes the skill simply needs to be presented with enough seriousness for people to recognize its importance. Every industry does this. After all, almost any movie will eventually become free if someone waits long enough for the copyright to expire. Yet people still pay to see films when they are released because the experience has value right now.

Learning works the same way. Free resources support exploration, while training helps people adopt new capabilities much faster.

What Excel trainers can do about it

Recognizing this dynamic is useful, but the more practical question is what Excel trainers should actually do about it.

The answer is usually not to stop sharing free material. The Excel community has benefited enormously from that tradition. The more useful shift is simply being more deliberate about what different kinds of content are meant to accomplish.

Free content is excellent for introducing ideas. A blog post, webinar, or conference talk can demonstrate a technique and show why it matters. But it does not have to teach an entire workflow from start to finish. Trying to compress a full training experience into a short demo often makes the explanation less clear, not more.

Another helpful shift is thinking carefully about where paid training tends to work best. Many Excel trainers eventually discover that institutional settings are a better fit than direct consumer sales. Trade associations, conferences, publishers, and corporate learning programs are designed for structured education and are accustomed to investing in it.

It also helps to communicate clearly why a skill matters. In a world where AI can instantly generate formulas or instructions, the real value of training is not the answer itself. The value is understanding how and why the solution works so it can be applied confidently in new situations.

And it helps to accept one more reality: the free content ecosystem is not going away. If anything, AI has accelerated it. The amount of freely available explanation in the world is now effectively unlimited.

But that does not eliminate the need for training. If anything, it makes good training more valuable, because people still need help turning scattered pieces of knowledge into reliable skills.

Free content helps people discover ideas. Training helps them build capability.

For Excel educators, the real challenge is learning how to support both without accidentally positioning themselves as a permanent source of unlimited free instruction.

The post How to escape the free Excel content friendzone first appeared on Stringfest Analytics.

That approach gives us something useful: a list of potential problems. But lists of warnings only get you so far. They tell you what might be wrong, but they do not help you understand how the model itself is structured.

In this post I want to take the idea a step further and show how to visualize the structure of an Excel model using Python. Instead of just printing audit findings, we start thinking about the workbook as a network of dependencies. Once you look at it that way, you can actually map the model and make its logic visible.

If you want to follow along, you can download the demo workbook and the accompanying notebook below.

The notebook and full output are presented below. An explanation of what the script is doing and why these checks matter follows afterward.

At a very high level, the notebook treats the spreadsheet less like a grid and more like a system of connected calculations. That shift in perspective matters because many spreadsheet problems are not obvious when you look at cells one by one. They become clearer when you look at how information flows through the model.

With that in place, let’s walk through the code step by step and look at what each part is doing and what it tells us about the structure of the workbook.

Step 1: Loading the workbook as structured data

The first important move in the notebook is this:

file = "demo_financial_model.xlsx"
wb = openpyxl.load_workbook(file, data_only=False)
print("Sheets:", wb.sheetnames)

When this runs, Python opens the workbook and reads it as a structured file rather than as a live Excel interface. That distinction matters.

Inside Excel, you are usually interacting with the model visually. You click cells, inspect formulas, trace arrows, and move around sheet by sheet. Python approaches the workbook differently. It reads the file as data. That means it can inspect every worksheet, every cell, and every formula systematically.

In this example, the workbook contains three sheets:

['Inputs', 'Calculations', 'Outputs']

That already tells us something useful about the intended design of the model. The workbook appears to be organized using a familiar structure:

• Inputs for assumptions
• Calculations for the working logic
• Outputs for final results

That separation is common in well-organized analytical models, and it gives the rest of the notebook something to work with. The code can now ask whether the workbook is actually behaving the way that structure suggests it should.

Step 2: Looking for hardcoded constants outside the Inputs sheet

The next section of the notebook scans the workbook for numeric constants that appear outside the Inputs sheet:

hardcoded_cells = set()
input_sheets = ["Inputs"]for sheet in wb.sheetnames:
    ws = wb[sheet]    for row in ws.iter_rows():
        for cell in row:            if cell.data_type == "n":
                if sheet not in input_sheets:
                    if cell.value is not None:
                        hardcoded_cells.add(f"{sheet}!{cell.coordinate}")

This part is based on a simple modeling principle: in many structured workbooks, important assumptions should live in dedicated input areas, not be scattered across calculation sheets.

When assumptions are centralized, they are easier to review, update, and explain. If key numbers start appearing directly inside the calculation layer, the model becomes harder to trust. A reviewer has to ask: is this value intentional, or did someone overwrite a formula?

In this example, the notebook reports:

Hardcoded cells: {'Calculations!A3', 'Calculations!A4', 'Calculations!A2', 'Calculations!C2'}

This ties directly back to the previous post. There, I already discussed that A2, A3, and A4 were not especially concerning because they are just year labels. But Calculations!C2 was more significant. It represented a hardcoded revenue value in a place where I would normally expect a formula driven by inputs such as units and price.

That is the first reason this notebook matters. It is not just building a pretty visualization. It is carrying forward actual audit findings from the earlier script and placing them into the structural picture of the model.

Step 3: Expanding Excel ranges so the relationships are real

Next, the notebook defines a helper function called expand_range():

def expand_range(sheet, start, end):    col1, row1 = coordinate_from_string(start)
    col2, row2 = coordinate_from_string(end)    col1_i = column_index_from_string(col1)
    col2_i = column_index_from_string(col2)    cells = []    for col in range(col1_i, col2_i + 1):
        for row in range(row1, row2 + 1):
            coord = f"{get_column_letter(col)}{row}"
            cells.append(f"{sheet}!{coord}")    return cells

This may look technical, but the idea behind it is very straightforward.

Excel formulas often refer to ranges instead of individual cells. For example, a formula might sum a range like E2:E4. To a human reader, that clearly means three cells. But if we want to build a dependency graph, we cannot treat E2:E4 as one mysterious blob. We need to expand it into its underlying parts:

• E2
• E3
• E4

That is exactly what this function does. If we skip this step, the graph will miss real dependencies and the workbook will appear simpler than it actually is. A model might look like it has fewer connections than it really does, and that could hide the true influence of certain cells.

This is a good example of where Python adds value beyond Excel’s built-in tools. Excel can certainly evaluate a range formula correctly, but Python lets us unpack that formula into explicit relationships that can be counted, visualized, and analyzed.

Step 4: Building the dependency graph

This is the core of the notebook:

G = nx.DiGraph()cell_pattern = r"[A-Za-z_]+!\$?[A-Za-z]+\$?\d+"
range_pattern = r"([A-Za-z_]+)!([A-Za-z]+\d+):([A-Za-z]+\d+)"for sheet in wb.sheetnames:
    ws = wb[sheet]    for row in ws.iter_rows():
        for cell in row:            location = f"{sheet}!{cell.coordinate}"            if cell.data_type == "f":                formula = cell.value                ranges = re.findall(range_pattern, formula)                for r in ranges:
                    rng_sheet, start, end = r
                    expanded = expand_range(rng_sheet, start, end)                    for ref in expanded:
                        G.add_edge(ref, location)                refs = re.findall(cell_pattern, formula)                for ref in refs:
                    ref = ref.replace("$","")
                    G.add_edge(ref, location)

At a high level, this code scans every formula in the workbook and asks a simple question:

Which cells does this formula depend on?

For every dependency it finds, it adds an edge to the graph. This is where the ideas of nodes and edges become important.

• A node is a cell
• An edge is a dependency relationship between cells

If one cell feeds another, the graph captures that as an arrow. So if Calculations!B3 depends on Inputs!B2, the graph includes a relationship like this:

Inputs!B2 → Calculations!B3

That arrow matters because it tells us something about the model’s logic. It says that the value in the calculation cell is not independent. It comes from somewhere else. If the source changes, the downstream cell changes too.

This is one of the biggest conceptual shifts in the notebook. Instead of seeing the workbook as just a set of formulas, we start seeing it as a directed network of cause and effect.

In this example, after building the graph, the notebook reports:

Nodes: 19
Edges: 10

So in this small workbook, Python has identified 19 participating cells and 10 relationships between them.

That may not sound dramatic, but it is the foundation for everything that follows. Once those relationships are represented as a graph, we can ask much more interesting questions than Excel’s built-in tools usually make easy.

Step 5: Making sure disconnected hardcoded cells still show up

After building the graph from formulas, the notebook adds this step:

for cell in hardcoded_cells:
    if cell not in G:
        G.add_node(cell)

If a value is hardcoded and not referenced by any formulas, it may never appear in the graph automatically. That is because the graph is being built from formula relationships. No formula relationship means no edge, and possibly no node.

But from an auditing perspective, that kind of disconnected cell can be extremely important.

Take Calculations!C2 in this workbook. That cell contains a hardcoded revenue value. If nothing is pointing to it and it is not pointing anywhere else, it could easily disappear from the graph unless we explicitly add it. But the fact that it is disconnected is itself informative. It tells us that the value is not participating in the model’s logic the way we might expect.

So this step ensures that hardcoded constants still appear in the visualization, even if they are structurally isolated.

That matters because the absence of a connection can be just as meaningful as the presence of one. A disconnected calculation may indicate a harmless label, an abandoned piece of logic, or a genuine modeling error. The graph should give us the chance to see that.

Step 6: Coloring the nodes so the structure becomes readable

Next, the notebook assigns colors based on each node’s role:

def node_color(node):    if node in hardcoded_cells:
        return "gold"    if node.startswith("Inputs"):
        return "lightgreen"    elif node.startswith("Calculations"):
        return "lightskyblue"    elif node.startswith("Outputs"):
        return "salmon"    return "lightgray"

This is not just a cosmetic step. It helps translate the graph into something an Excel user can interpret quickly.

The colors in this example mean:

• green = inputs
• blue = calculations
• red = outputs
• gold = hardcoded constants outside the input layer

That last category is especially useful because it overlays the audit results from the first notebook directly onto the model structure. Instead of only printing a list of suspicious cells, we can now see where those cells live inside the architecture of the workbook.

This is where the graph starts to become more than just a technical artifact. It becomes an explanatory tool.

For example, a gold node sitting inside the Calculations sheet tells a story. It says: this cell is behaving more like an input, but it is not living in the input layer. That is a structural inconsistency.

That is exactly what was happening with Calculations!C2.

Step 7: Forcing a layered layout that mirrors how models are designed

Then the notebook creates positions for the nodes:

layers = {
    "Inputs": 0,
    "Calculations": 1,
    "Outputs": 2
}pos = {}
y_positions = {}for node in sorted(G.nodes):    sheet = node.split("!")[0]
    layer = layers.get(sheet, 3)    y_positions.setdefault(sheet, 0)    pos[node] = (layer, -y_positions[sheet])
    y_positions[sheet] += 1

This step matters because raw network graphs can get messy very quickly. If we simply let a graphing library place everything wherever it wants, the result often looks like a tangle of arrows.

Here, instead of using a free-form layout, the notebook deliberately places the sheets in columns from left to right:

Inputs → Calculations → Outputs

That mirrors the intended design of the workbook itself.

This is a subtle but important choice. It makes the graph easier for Excel users to read because it aligns with the way they already think about a model. Inputs feed calculations, and calculations feed outputs.

So even though the graph is generated with Python, it still respects familiar spreadsheet design principles. The purpose is not to turn Excel into an abstract math problem. The purpose is to make the model’s structure more visible.

Step 8: Drawing the graph and sizing nodes by influence

The graph is then drawn with this code:

plt.figure(figsize=(12,10))sizes = [400 + len(nx.descendants(G,n))*100 for n in G.nodes]nx.draw(
    G,
    pos,
    with_labels=True,
    node_color=colors,
    node_size=sizes,
    font_size=8,
    arrows=True
)plt.title("Excel Model Dependency Graph")
plt.show()

Here’s the resulting visualization of our model:

Two things are happening here. First, the notebook is visualizing the dependency network we just built. Second, it is sizing each node based on the number of downstream cells that depend on it. In other words, cells that influence more of the model appear larger.

This matters because not all cells are equally important. Some cells are relatively local. Others sit near the top of the dependency chain and affect many downstream calculations.

Sizing nodes this way helps the graph communicate influence, not just existence.

That is another place where Python adds value beyond Excel’s built-in auditing tools. Excel can tell you the direct precedents or dependents of a selected cell, but Python can help summarize influence more broadly across the model.

Even in a small workbook, this starts to reveal which parts of the spreadsheet are structurally important.

Step 9: Identifying orphan cells

Finally, the notebook looks for orphan cells:

orphans = [n for n in G.nodes if G.degree(n) == 0]
print("Orphan cells:", orphans)

In this example, the result is:

Orphan cells: ['Calculations!A3', 'Calculations!A4', 'Calculations!A2', 'Calculations!C2']

This is one of the most useful outputs in the notebook because it reinforces the earlier audit findings with structural evidence.

The year labels in A2, A3, and A4 are disconnected because they are simply labels. That is fine.

But Calculations!C2 is different. It is disconnected for a more interesting reason: it is a hardcoded value sitting outside the normal assumption flow of the model.

That is exactly what I pointed out in the previous post. There, the script flagged Calculations!C2 as a hardcoded constant. Here, the graph shows us something more: it is not just hardcoded, it is also structurally isolated.

That combination makes the issue much easier to understand. It is no longer just a suspicious number on a list. It is a number that sits outside the model’s normal flow.

How this compares to Excel’s built-in auditing tools

Excel already includes several useful tools for auditing models. Features such as Trace Precedents, Trace Dependents, and Show Formulas can be very helpful when you are trying to understand a particular formula or troubleshoot a specific section of a workbook.

Where these tools become more limited is at the workbook level. They are designed primarily for interactive, cell-by-cell inspection. That approach works well when you already know where to look, but it becomes harder when you want to step back and understand the structure of the entire model.

The Python approach used in this notebook is aimed at a different level of analysis. Instead of examining formulas individually, the script scans the workbook in a single pass, extracts the relationships between cells programmatically, and then summarizes the structure of the model.

In practical terms, this means we can inspect the entire workbook at once rather than reviewing formulas one at a time. It also allows the same logic to be applied consistently across multiple files, makes it possible to combine structural visualization with audit findings, and can automatically surface cells that appear disconnected or suspicious.

The goal is not to replace Excel’s built-in auditing features. Rather, Python provides an additional layer of visibility. Excel remains excellent for manually exploring individual formulas and tracing specific dependencies. Python, on the other hand, is well suited for stepping back, analyzing a workbook systematically, and creating repeatable checks. Used together, the two approaches complement each other very well.

Why this matters now

This kind of workflow is becoming increasingly relevant as spreadsheet generation becomes faster and easier. AI tools can now produce workbook structures, formulas, and even entire models from prompts. As that capability grows, the bottleneck shifts. The challenge is no longer simply producing spreadsheets; it is understanding them, validating them, and maintaining them.

That is where an approach like this becomes useful. With a relatively small amount of code, we can begin to inspect workbook structure automatically, visualize how logic flows through the model, highlight structural violations, and identify cells that fall outside the intended design.

None of this removes the need for human judgment. As the earlier post showed, some audit findings will inevitably be false positives that require interpretation. What it does provide is a faster and more focused way to review a model. Instead of manually searching through a workbook for potential issues, we can use code to narrow down where our attention is most needed.

Want to build tools like this for your team?

This example is intentionally small, but the underlying idea is very practical. Teams that rely heavily on Excel can use Python to build lightweight validation tools, auditing utilities, and analysis workflows around their spreadsheets.

If you want to set up tools like this for your team, strengthen Python capabilities around Excel, or build more reliable analytics workflows, take a look at my How I Work page:

That is where I outline how I help organizations combine Excel, Python, and modern AI tools in ways that are useful, practical, and grounded in real analytical work.

The post How to visually audit Excel workbooks with Python first appeared on Stringfest Analytics.

Auditing Excel models has always been difficult. Excel is designed as an instant recalculation engine: formulas update automatically, dependencies stretch across sheets, and logic can be embedded almost anywhere. While this flexibility is one of Excel’s strengths, it also makes models surprisingly hard to inspect systematically.

Many firms sell specialized spreadsheet auditing tools, but today we also have another option. Because Excel files are structured data, we can analyze them programmatically. With Python, we can build lightweight utilities that scan workbooks, inspect formulas, and flag potential issues automatically.

This post walks through a minimum viable example of building a simple Excel model auditor with Python. The goal isn’t to replace professional auditing software, but to demonstrate how easily we can start analyzing spreadsheet structure with code.

For this walkthrough, we’ll use a small sample Excel workbook that contains a typical analytical model: a few worksheets, some input values, and formulas that generate outputs across the workbook. Our Python script will open the file, scan the cells, and begin identifying useful auditing information such as where formulas appear, where values are hardcoded, and how calculations are distributed across the model.

Python is a natural fit for this kind of task. It’s excellent at parsing structured files, analyzing patterns like formulas, and producing summaries that would be tedious to generate manually in Excel. As AI-generated spreadsheets become more common, tools like this will likely become just as important as the tools that generate the models themselves.

Understanding the script & its benefits

Before diving into the details of the script, it helps to understand the basic idea. Instead of calculating results the way Excel normally does, this program reads the workbook as structured data and inspects its contents for patterns that often signal modeling problems.

The script uses two Python tools to do this. The openpyxl library allows Python to open an Excel workbook and inspect its sheets, cells, and formulas directly. The re library provides regular expressions, which make it easy to detect patterns inside formulas such as numbers, cell references, or links to other workbooks.

Once the workbook is loaded, the script scans every worksheet and every cell. As it moves through the model, it performs a few simple checks that are common in spreadsheet auditing.

For example, it looks for formulas that contain hardcoded numbers. In many analytical models, important assumptions should live in dedicated input cells rather than being embedded inside formulas. The script removes cell references from each formula and checks whether any numbers remain, which can indicate hidden constants.

It also flags formulas that link to external workbooks, since these can introduce hidden dependencies that make models difficult to reproduce or maintain. The script then checks for numeric values entered directly into calculation sheets. Constants are allowed on designated input sheets, but values appearing elsewhere may indicate accidental overwrites or poor model structure.

Finally, the script looks for inconsistent formulas down a column. Many models apply the same calculation repeatedly across rows, so a formula that suddenly breaks the pattern may signal an editing mistake. By normalizing formulas and comparing their structure, the script can detect these inconsistencies automatically.

At the end, any potential issues are printed as a simple audit report showing the worksheet location and the formula or value that triggered the alert.

You might wonder why this is useful when Excel already provides tools like Trace Precedents or Show Formulas. Those tools are helpful for inspecting individual cells, but they are still largely manual and workbook-specific. A Python script like this operates differently: it can scan an entire workbook programmatically, apply consistent auditing rules, and produce repeatable results across many files.

Another important point is that everything here runs locally and is entirely rule-based. No AI is involved. The script simply reads the workbook file and applies a set of deterministic checks. That makes it easy to customize, automate, and integrate into larger workflows when auditing or validating spreadsheets at scale.

Understanding the results

At this stage the goal is not a perfect audit, but a quick structural scan that highlights places worth inspecting.

Here’s a quick summary of what the script detected:

• Several hardcoded constants placed directly in the Calculations sheet (cells A2, A3, A4, and C2). In many models, values like these would typically live on an Inputs sheet rather than inside the calculation layer.
• A formula containing a hardcoded multiplier (=B4*12 in D4), which could represent a hidden assumption instead of referencing an input cell.
• A few inconsistent formula patterns down columns in the Calculations sheet, suggesting the formulas may not follow the same structure from row to row.

None of these automatically mean the model is incorrect. But they are exactly the kinds of patterns auditors often look for when reviewing spreadsheets.

Next, let’s open the workbook and look at these flagged cells directly.

Harmless flags: timeline labels

Some of the findings do not concern me too much.

Cells A2, A3, and A4 on the Calculations sheet were flagged as hardcoded constants, but in this case they simply represent year numbers used to label the timeline of the model. In many spreadsheets this is perfectly reasonable. While it would be possible to generate these dynamically using a function such as SEQUENCE() so the timeline expands automatically, that level of flexibility is not always necessary.

Why the Inputs sheet was not flagged

You might also notice that constants on the Inputs worksheet were not flagged by the script. This behavior is intentional and reflects a common modeling convention.

Earlier in the script, we defined which sheets are allowed to contain constants:

input_sheets = ["Inputs"]

Later in the audit process, the script checks whether a numeric value appears in a worksheet that is not designated as an input sheet. If a number appears outside those allowed sheets, it is flagged as a potential hardcoded constant.

This mirrors a common spreadsheet design principle: inputs should be centralized in dedicated input areas, while calculation sheets should primarily contain formulas.

A real modeling issue

However, one finding does appear to represent a genuine modeling problem.

The script flagged the following cell:

Hardcoded constant cell
Value: 25000
Cell: Calculations!C2

In this case, the Year 1 revenue value has been entered directly as a number rather than being calculated from the underlying assumptions. In most analytical models, revenue should be derived from inputs such as Units and Price rather than hardcoded in the calculation sheet.

If those inputs exist on the Inputs worksheet, this value should likely be calculated as:

Revenue = Units * Price

A hardcoded revenue value bypasses the assumptions driving the model and can easily lead to inconsistencies if the inputs change.

A double offender

Another issue appears in cell D4 on the Calculations sheet, which the script flagged twice:

Hardcoded number inside formula
Formula: =B4*12
Cell: Calculations!D4

Inconsistent formula pattern
Formula: =B4*12
Cell: Calculations!D4

This cell violates two different auditing rules.

First, the formula contains a hardcoded constant. The multiplier 12 is embedded directly in the formula rather than referencing a value from the Inputs sheet.

Second, the formula breaks the expected pattern down the column. Earlier rows appear to follow a different structure, but this row switches to =B4*12, which causes the pattern check to flag it.

More importantly, the calculation itself appears incorrect. If this column represents cost, the correct logic would typically be:

Cost = Units Sold * Cost per Unit

Instead, the formula effectively calculates:

Cost = Units Sold * 12

which disconnects the calculation from the cost assumptions stored on the Inputs sheet.

A useful false positive

Still other findings are more nuanced. The script also flagged these formulas:

Inconsistent formula pattern
Formula: =B2*(1+Inputs!B4)
Cell: Calculations!B3

Inconsistent formula pattern
Formula: =B3*(1+Inputs!B4)
Cell: Calculations!B4

At first glance, these appear to be inconsistencies. In reality, the formulas are correct.

These rows are applying a growth rate from the Inputs sheet:

Next Year Value = Previous Year Value * (1 + Growth Rate)

The reason the script flags them is that year 1 typically does not have a growth calculation. The first year is usually a starting value, while later years grow from that base. Because of this, the formula pattern naturally changes after the first row.

From the script’s perspective this looks inconsistent, but from a modeling standpoint it is perfectly reasonable.

The limits of rule-based auditing

This highlights an important limitation of rule-based auditing.

Structural checks can quickly surface patterns that often indicate problems, but they cannot fully understand the intent of the model. As a result, some findings will inevitably be false positives that require human judgment.

In other words, scripts like this are extremely useful for narrowing down where to look. But catching every issue in a complex spreadsheet remains surprisingly difficult.

Conclusion

In the real world, a production-grade spreadsheet auditing system would take years to design, build, and deploy. Large firms that specialize in spreadsheet risk management invest heavily in tools that can analyze complex models, track dependencies, and enforce organizational standards across thousands of workbooks.

But deep down, many of those tools rely on the same basic idea we explored here in just a few minutes: treat the Excel workbook as structured data, scan it programmatically, and look for patterns that often signal modeling problems.

Even a small script can quickly surface issues such as hardcoded assumptions, broken formula patterns, or hidden dependencies. It won’t replace human judgment, but it can dramatically narrow down where to look.

That idea is becoming more important as Excel itself evolves. With AI assistants and Excel agents now capable of generating entire workbooks from a prompt, the challenge is no longer just building models. It’s understanding, validating, and maintaining them.

In other words, the tools that help us inspect and audit spreadsheets may become just as important as the tools that help us generate them. This simple example is just a starting point.

If you’re interested in exploring more workflows that combine Excel, Python, and modern AI tools to build more reliable analytical models, I share a growing collection of guides, experiments, and practical examples inside my membership.

You can learn more about it here:

It’s where I’m documenting many of the patterns and tools I’m experimenting with as Excel, automation, and AI continue to evolve.

The post How to build a simple Excel model auditor with Python first appeared on Stringfest Analytics.

Right now, when you ask these tools to build a workbook, they often fall back on very generic Excel patterns. That usually means things like hardcoded numbers in formulas, calculations scattered across sheets, inconsistent formatting, or outputs that break the moment the data grows.

My guess is that this happens because there’s simply more training data reflecting older Excel habits. Modern practices like structured tables, dynamic arrays, and consistent modeling standards have been adopted much more slowly across organizations. As a result, unless you guide the model, it often defaults to those older patterns.

The good news is that the quality of AI-generated workbooks improves dramatically once you start giving the assistant a few guardrails. A helpful way to think about it is this: treat the AI the same way you would treat a new analyst joining your team. If you want consistent models, you need to explain the standards you expect.

Start with how you want models structured

Most analysts follow certain structural habits when building workbooks, even if those habits are rarely written down anywhere. Over time, you develop a mental model for how a workbook should be organized so that it stays understandable and maintainable.

Typically, inputs live in one place where they can be easily edited. Calculations happen somewhere else, where formulas can operate on those inputs without clutter. Outputs are separated again so the final results can be presented clearly to whoever is consuming the analysis.

When you make those expectations explicit in your instructions to an AI assistant, the quality of the workbook it generates improves dramatically. Instead of scattering formulas and values throughout the file, the assistant has a clear blueprint to follow.

A few simple structural instructions can go a long way. For example, you might include guidance like:

• “Organize the workbook into Inputs, Calculations, and Outputs sections or sheets.”
• “Store input datasets in Excel tables, not loose cell ranges.”
• “Name tables using a tbl_ prefix (for example tbl_sales or tbl_expenses).”
• “Avoid hardcoding numbers inside formulas. Reference input cells or parameters instead.”
• “Use structured references to table columns rather than fixed ranges like A2:A100.”

Using Excel tables is especially helpful here. Tables allow formulas to reference columns by name instead of pointing to specific cell ranges, which makes the logic much easier to understand. They also expand automatically as new rows are added, so formulas and analyses don’t silently break when the dataset grows.

These kinds of rules help the assistant build something closer to what an experienced analyst would produce. Instead of a spreadsheet that mixes inputs, formulas, and results in unpredictable ways, you get a model with a clear structure that someone else can actually understand.

In practice, that small structural decision can prevent many of the subtle errors that creep into poorly organized spreadsheets.

Encourage modern Excel features

Another simple instruction that can make a big difference is telling the assistant which Excel tools it It can also help to specify which generation of Excel functions you want the assistant to favor.

Left on its own, an AI model will often default to older patterns like VLOOKUP, nested IF statements, or fixed ranges such as A2:A100. That’s largely because there is far more training data reflecting those older habits. Newer capabilities, especially dynamic array functions and tools like LET() and LAMBDA(), have spread more slowly across organizations, so the model sees fewer examples of them.

Modern Excel tools are usually much better suited for building resilient models. Functions like XLOOKUP(), FILTER(), UNIQUE(), SORT(), and XMATCH() work naturally with dynamic datasets, while dynamic arrays allow a single formula to spill results automatically as the data grows. LET() can also make complex formulas easier to read by naming intermediate calculations.

Because of this, it often helps to include explicit preferences such as:

• “Prefer XLOOKUP() instead of VLOOKUP() or HLOOKUP() when performing lookup operations.”
• “Use modern dynamic array functions such as FILTER(), UNIQUE(), and SORT() when generating lists or subsets of data.”
• “Write formulas that spill automatically rather than copying formulas down rows or columns.”
• “Use LET() to define intermediate variables and simplify complex formulas.”
• “Reference Excel table columns using structured references instead of fixed ranges such as A2:A100.”

These small instructions help ensure the model uses modern Excel patterns, which tend to produce workbooks that adapt much more gracefully as the data changes.

Set some naming conventions

Another helpful preference to define is how things should be named.

Consistent naming makes a workbook much easier to understand once formulas start referencing multiple tables, parameters, and calculations. Without it, even a well-built model can become hard to follow.

You can guide the assistant with simple conventions such as:

• “Use snake_case for all named ranges and variables.”
• “Prefix Excel tables with tbl_ (for example tbl_sales, tbl_expenses).”
• “Prefix input parameters with p_.”
• “Prefix calculated metrics or measures with m_.”

These may seem like small details, but they make formulas far easier to read and navigate. The same principle applies in programming and analytics: clear, consistent names reduce confusion and make systems much easier to maintain and extend.

Encourage documentation inside the workbook

Another useful preference is asking the assistant to include basic documentation inside the workbook.

A good analyst rarely hands over a model without explaining how it works, and the same expectation can improve AI-generated files. You might ask the assistant to add brief comments explaining complex formulas, include short descriptions at the top of worksheets, or create a simple Assumptions sheet.

An assumptions sheet is especially helpful because it gives readers a clear place to see the key inputs driving the model: things like growth rates, scenario parameters, or cost estimates.

You can also include instructions like:

• “Add comments to explain any complex or non-obvious formulas.”
• “Include a short description at the top of each worksheet explaining its purpose.”
• “Create an Assumptions sheet listing key inputs, parameters, and model drivers.”

This begins to overlap with the idea of test-driven instructions, where models include validation checks and error flags. That’s a deeper topic for another post. For now, the goal is simply encouraging the assistant to make its logic visible.

A model that documents itself is much easier for someone else to understand and trust.

Set visual expectations

Excel workbooks are not just computational models. They’re also documents people need to read and interpret, which means visual consistency matters.

You can help the assistant by specifying simple formatting preferences, such as using one color for inputs, another for outputs, applying consistent number formats, or building charts with a specific color palette.

For example, you might include instructions like:

• “Use a consistent color to highlight editable input cells.”
• “Apply clear and appropriate number formats to outputs (such as currency, percentages, or thousands separators).”
• “Use consistent table styles and chart formatting throughout the workbook.”
• “Apply the company color palette when creating charts or dashboards.”

Some teams even upload their brand colors or example dashboards so new workbooks align with existing reporting standards.

These small details may seem cosmetic, but they go a long way toward making AI-generated workbooks feel less like rough drafts and more like finished deliverables.

Don’t forget the “last mile” work

Another category of instructions analysts often forget to include is the last mile formatting work.

This is the cleanup that happens right before a file gets sent to a manager or included in a presentation: freezing panes so headers stay visible, autofitting column widths, setting print areas, aligning charts, and making sure number formats are consistent.

None of this work is particularly difficult, but it can quietly consume a surprising amount of time.

The good news is that AI assistants can usually handle these tasks just fine, as long as you tell them to. You might include instructions like:

• “Freeze panes so table headers remain visible when scrolling.”
• “Autofit column widths to improve readability.”
• “Apply consistent number formats across output tables and reports.”
• “Set appropriate print areas and page layout for printable sheets.”
• “Align charts and tables neatly on output or dashboard sheets.”

If there are small formatting tweaks your boss regularly asks you to make before sharing a workbook, those are exactly the kinds of expectations worth building into your instructions.

Give the assistant examples of good work

Another powerful way to improve results is simply showing the assistant examples of good workbooks.

A well-structured template, a modeling standards document, or a sample dashboard can give the AI a clear signal about what “good Excel” looks like in your environment. Instead of generating models completely from scratch each time, the assistant can start to mirror the layouts, conventions, and patterns used in those examples.

You can reinforce this by including instructions like:

• “Follow the structure and formatting used in the provided workbook template.”
• “Use the uploaded dashboard as a reference for layout and chart styling.”
• “Follow the conventions outlined in the modeling standards document.”
• “Match the color palette and formatting used in the example reports.”

Even a single well-built workbook can act as a powerful reference point. When the assistant has a concrete example to follow, it becomes much easier for it to produce models that align with the way your team already works.

Conclusion

Excel AI assistants are already surprisingly capable. They can generate formulas, structure workbooks, and even assemble fairly sophisticated models with very little prompting.

What they still lack, however, is something most experienced analysts rely on every day: a modeling standard. In most organizations, analysts develop informal conventions for how workbooks should be structured, how formulas should be written, and how results should be presented. Those habits make models easier to maintain, review, and extend. But AI assistants don’t automatically know those expectations unless we tell them.

As these tools evolve, it’s likely we’ll see better ways to load persistent style guides, templates, and organizational preferences directly into the assistant. When that happens, the quality and consistency of AI-generated Excel models will improve dramatically.

Until then, the best approach is fairly simple. Don’t just tell the AI what model you want to build… tell it how you want Excel to be built. A short set of preferences around structure, formulas, naming conventions, and formatting can go a long way toward producing models that behave more like the work of an experienced analyst.

If you’re experimenting with these tools, try writing a small Excel style guide for your prompts and see how much the results improve.

And if you’re interested in more practical examples of modern Excel workflows, automation techniques, and AI-assisted analytics, I share a growing collection of guides, templates, and resources inside my Modern Excel + AI membership:

That’s where I’m collecting many of the patterns, prompts, and tools I’m experimenting with as Excel and AI continue to evolve.

The post How to get better results from Excel AI assistants first appeared on Stringfest Analytics.

My friend Tobias Zwingmann wrote a great piece called “AI Workflows vs AI Agents vs Everything in Between” that does an excellent job clarifying the difference between these architectures. This post is very heavily inspired by his framework. What I want to do here is apply those ideas specifically to Excel use cases, because once you start looking at real systems the distinction becomes much clearer.

The traditional Excel workflow automation (no AI)

Before we get into AI, it helps to understand the simplest architecture that already powers a huge amount of Excel work. The diagram below comes from Tobias Zwingmann’s article and illustrates the basic structure of a workflow:

In a workflow like this, the system simply follows a predefined sequence of steps. A trigger occurs, a set of operations runs in a fixed order, and an output is produced. Nothing about the process is dynamic. The system is not interpreting the situation or deciding what to do next. It is just executing the instructions that were defined ahead of time.

This pattern is actually the bread and butter of most Excel reporting systems. In fact, you could argue that Power Query itself is essentially a workflow engine. Each transformation step is recorded and then replayed in sequence every time the query refreshes.

Once you start looking at Excel systems through this lens, you realize that many common reporting pipelines already follow this exact architecture.

Example 1: Basic Power Query data pipeline

Suppose a sales team exports a CSV from their CRM system every morning. Instead of cleaning the file manually, you build a Power Query workflow inside Excel.

The steps might look something like this:

1. Import the CSV file
2. Remove unnecessary columns
3. Standardize date formats
4. Convert text values to numeric fields
5. Create a calculated column for total sales
6. Load the cleaned data into an Excel table

Once the query is built, the workflow becomes extremely simple. Each time the query refreshes, Excel applies the exact same sequence of transformations.

The process is fully deterministic. The same input always produces the same output.

Example 2: Power Automate + Office Scripts workbook processing

A slightly more modern version of the same idea might combine Power Automate with Office Scripts.

Imagine a team that receives weekly Excel files from regional offices. Each file needs to be standardized before it can be used in a central reporting model.

A Power Automate flow might handle the process like this:

1. A new Excel file is uploaded to a SharePoint folder
2. The flow triggers automatically
3. An Office Script runs to standardize the workbook:
   • Convert raw data into a structured Excel table
   • Rename columns to match the reporting schema
   • Apply consistent formatting
4. The cleaned workbook is saved to a “Processed Files” folder

Again, the workflow follows a fixed sequence. Nothing about the system decides what to do dynamically. It simply executes the predefined steps every time a file appears.

In both of these examples, the process behaves like a checklist:

A → B → C → D

The system never decides what the next step should be. It simply runs the steps that were defined ahead of time. This kind of deterministic automation is still the foundation of most Excel systems today. And in many cases, it’s exactly what you want: predictable, transparent, and easy to maintain.

Where things start to get more interesting is when we introduce AI as one step inside this workflow.

The Excel Automated AI workflow

The next step up from simple automation is when AI becomes one step inside a structured workflow.

The diagram below (again taken from Tobias’s article) illustrates the idea. The process still follows a controlled sequence of steps, but one of those steps uses AI to interpret or generate something.

The important point is that AI is not orchestrating the system. The workflow still controls the order of operations.

This pattern turns out to be extremely practical for Excel users because most reporting processes already follow a predictable structure. AI simply handles the parts that require interpretation or narrative.

Example: automating a variance deck

A good example is the workflow I showed in my recent post on automating a monthly variance presentation with Power Automate and Copilot.

At first glance this might sound like an “AI agent,” but technically it is much closer to an AI workflow. Here’s the actual architecture:

Power Automate flow → Load Excel table → Run AI prompt to analyze results → Generate executive summary bullet points → Insert bullets into PowerPoint template → Save finished presentation

In other words:

Power Automate → Excel table → AI prompt → PowerPoint output

The key detail is that the workflow itself is still fully controlled. Power Automate determines the sequence of steps. The AI is simply responsible for generating the narrative summary that would normally be written by an analyst.

That means the system remains predictable and auditable:

• The workflow determines when it runs
• The Excel table defines the data source
• The PowerPoint template defines the output format
• The AI only fills in the narrative portion

This pattern is extremely powerful because it lets you automate the parts of reporting that used to require human interpretation, without giving up control of the overall process.

The Excel AI Agent

This next architecture is what people usually mean when they talk about AI agents. In this model, the AI is no longer just a step inside a workflow. Instead, the AI becomes the decision-maker that determines which steps to take.

Instead of a predefined sequence like:

A → B → C

You provide a goal, and the agent decides how to achieve it:

Goal → agent decides steps

The agent can call tools, access data sources, perform analysis, and generate outputs. Importantly, the order of those actions is not fixed ahead of time.

Example: receipt classification agent

A good example of this architecture is the expense report agent I showed in my post about building a PDF receipt classifier with Copilot:

The user interaction is very simple: The user uploads receipts. That is the trigger. But from there, the agent decides what actions are required to reach the goal.

For example, the agent may:

• extract text from the PDF
• identify transactions within the document
• look up the correct expense account codes
• structure the results into rows
• generate a properly formatted Excel workbook
• return the file to the user

A simplified architecture looks like this:

User goal → AI agent → Tool selection and reasoning → Excel file creation

The key detail is that the agent determines how to reach the outcome.

Why this is an agent and not a workflow

The difference becomes clearer when you compare it to a workflow. In a workflow, the sequence of steps is fixed. For example:

Power Automate → Excel → AI step → PowerPoint

Every run follows the same path.

The receipt classification example is different. Instead of defining the steps, you define the goal: classify transactions from uploaded receipts and return an Excel report. The agent then decides how to achieve that using the tools and knowledge sources available to it.

Because receipts vary widely, the reasoning path can change from run to run. That dynamic decision-making is what makes this an agent rather than a workflow.

Why this matters for Excel users

Workflows with an AI step will cover most reporting scenarios.

Agents become useful when the inputs vary significantly and the system needs flexibility in how it extracts and interprets information. Expense receipts are a good example, since each document may require a slightly different approach to produce a clean Excel table.

The agentic Excel workflow

The final pattern is what you might call an agent embedded inside a workflow:

This is actually where many practical business systems are likely to land.

Instead of choosing between a rigid workflow or a fully autonomous agent, you combine the two. The workflow still orchestrates the overall process, but a specific step is delegated to an agent that can reason dynamically.

The structure typically looks like this:

Workflow → Agent step → Workflow continues

For example, imagine an expense automation system:

Upload receipts → Agent extracts transactions → Workflow validates totals → Excel workbook generated → Email finance team

In this setup, the workflow handles the predictable parts of the process: triggering the system, validating results, saving files, and distributing reports. The agent handles the messy reasoning step, such as extracting transactions from receipts, interpreting vendor names, or assigning categories.

This hybrid pattern often works well because it combines the strengths of both approaches. The workflow provides structure and reliability, while the agent adds flexibility where the inputs are unpredictable.

I haven’t built a full example of this pattern yet, but it’s something I’m planning to experiment with. Once I do, I’ll update this post with a concrete walkthrough.

Conclusion

Right now the AI conversation is full of big terms: agents, agentic systems, autonomous workflows. But for most Excel professionals, the practical question is much simpler: what kind of system are you actually building?

Sometimes the right answer is still a straightforward automation workflow. Tools like Power Query, Power Automate, and Office Scripts can already handle many repetitive tasks reliably. In other cases, it makes sense to insert an AI step into that workflow to summarize results, classify data, or extract information from documents.

True agents are different. They introduce autonomy into the process and decide which actions to take in order to reach a goal. That flexibility can be powerful, but it also adds complexity.

Understanding the distinction helps you choose the simplest approach that actually solves the problem. In many real-world Excel scenarios, that ends up being a structured workflow with one or two well-defined AI steps.

A big part of how I’ve been thinking about this, to repeat, comes from Tobias Zwingmann’s excellent breakdown of AI workflows vs. agents. His framework helped clarify the landscape, and my goal here was simply to ground those ideas in practical Excel examples.

To explore these ideas further, I highly recommend checking out Tobias’s book The Profitable AI Advantage. It focuses on how organizations can actually create real business value with AI rather than just experimenting with the latest tools or buzzwords.

Thanks again, Tobias, for the inspiration behind this post!

If you’d like to explore these ideas further, this is exactly the kind of material I cover in my membership, where I walk through practical Excel examples using tools like Power Query, Power Automate, Copilot, and Office Scripts in much more depth.

The post How to understand the difference between Excel AI workflows and Excel AI agents first appeared on Stringfest Analytics.

But before engagement can be boosted, it has to exist at all.

And that is where many virtual sessions stall. The room goes quiet. You ask a question about a formula choice or a PivotTable setup and nothing comes back. No voices, no chat messages, just a long pause and a grid of muted microphones. If you train Excel regularly, you have almost certainly experienced this moment.

Part of the context is technological. With AI tools now embedded directly into Excel and available in any browser tab, many professionals understandably assume that if they do not fully grasp something in the moment, they can revisit it later. The perceived cost of staying quiet has dropped considerably.

I think that is a dangerous gamble. AI can generate explanations, but it cannot build the habit of articulating your reasoning in real time. It cannot simulate the experience of defending a calculation to a skeptical stakeholder or explaining a model to a colleague. In fact, strong participation in a learning community may be more important now than it was before.

With that framing in mind, here are several practical strategies I use to move a room from silence to participation without turning the session into an awkward performance exercise.

Replace vague invitations with concrete actions

One of the most common mistakes in virtual training is asking overly broad questions. “Any questions?” or “What do you think?” sounds inclusive, but in practice it places all the social risk on the participant and signals that engagement is optional.

A more reliable approach is to convert participation into a specific task. Instead of asking for general feedback, ask participants to type the value they calculated into chat, paste the formula they used, or respond with a quick indicator such as “1” if their PivotTable matches yours and “2” if it does not. These prompts feel procedural rather than performative. Participants are not volunteering a speech; they are completing a defined step. At the same time, you gain immediate feedback on whether people are following along or drifting off track.

Design exercises that require visible output

Breakout rooms are especially vulnerable to silence if expectations are not explicit. Simply asking participants to “discuss what you found” often leads to minimal conversation because there is no shared sense of responsibility for producing something.

We often assume that adults will naturally organize themselves, but that rarely happens without structure. In theory, everyone knows how to collaborate. In practice, people hesitate, wait for someone else to start, or avoid taking the lead.

You see the same thing when a traffic light stops working. Everyone knows the rule is to treat it like a four-way stop, yet the intersection often becomes awkward and hesitant because nobody is quite sure who should go first.

Breakout rooms work the same way. Instead, define the deliverable before the breakout begins. Assign one person to share their screen, one person to explain the approach the group used, and one person to identify the most important result. Let participants know each room will report back with something concrete, such as a PivotTable, chart, or one-sentence insight. When the outcome is clear and visible, participation tends to follow.

Use chat as a primary participation channel

Many professionals are more comfortable typing than speaking in a virtual setting. Rather than fighting that dynamic, it often makes sense to lean into it. I frequently pause during demonstrations and ask short, targeted questions that can be answered quickly in chat. For example, I might ask which column participants grouped by first, which function they used to clean a text field, or what assumption underlies a particular summary metric.

Reading and synthesizing those responses out loud reinforces that chat contributions are not incidental. They are part of the analytical conversation. Over time, participants begin to see chat as a normal way to participate rather than a side feature that only a few people use.

Normalize mistakes without lowering expectations

Excel can be unforgiving in small ways. A misplaced reference or incorrect aggregation can produce the wrong answer immediately. Participants may hesitate to speak because they are unsure whether their answer is correct and do not want to appear careless.

It helps to address this directly by acknowledging that even experienced analysts occasionally make these mistakes. Sharing a quick example of how you diagnosed a broken formula or tracked down an incorrect range reference can make the process feel less intimidating. At the same time, it is important to keep the expectation that reasoning should ultimately be clear and defensible. The goal is not to eliminate standards but to make it easier for participants to surface partial understanding so it can be refined.

Set expectations at the start of the session

If participation matters to you, it helps to say that directly at the beginning. Let participants know the session is meant to be a working environment rather than a passive webinar. Explain that you will ask for responses in chat, that breakout rooms will include short report-backs, and that visible thinking is part of the design.

It also helps to be honest about the limits of teaching online. In a classroom you can read the room: confused looks, nods, people falling behind. Online those signals mostly disappear. Because of that, I tell participants that I need them to be my eyes and ears. If something is unclear, say so in chat. If something works well, mention it.

When these expectations are set early, interaction feels normal rather than surprising, and people are less likely to default to silence.

Why this matters

In a world where AI can generate formulas, summaries, and even slide decks quickly, the differentiator is no longer the mechanical output. It is the ability to reason about that output, validate it, and communicate it clearly. Virtual Excel training should reflect that reality. If participants leave with a list of new features but without having practiced explaining their thinking, the long-term impact is limited.

If you are interested in learning more about how I design Excel and analytics training to build durable capability rather than short-term familiarity, take a look at my “How I Work” page:

I outline my approach to developing Excel and data skills within teams and organizations, and you can also get in touch there if you would like to explore relevant training or consulting services.

The post How to handle silence in virtual Excel training first appeared on Stringfest Analytics.

In this one, I want to focus on what is arguably the biggest risk of using generative AI for data analysis and reporting: getting the numbers wrong.

One of the oldest and most reliable principles in Excel still applies here. If something matters, you cross-check it using a different calculation method. So we are going to compare the total sales returned from our prompt with the total calculated independently using an Office Script. If those two values match, we will treat that as a validation checkpoint and allow the report to move forward.

The dataset is intentionally simple so you can follow along. Upload it to your OneDrive and keep the workbook open, since we’ll need to create an Office Script for it. After that, head over to make.powerautomate.com and we’ll build the flow together from there.

If you’re just getting started with these tools for Excel automation, I’d recommend beginning with my LinkedIn Learning courses to get a solid foundation before diving into this workflow.

If you’d like a more step-by-step walkthrough of how this flow works and why we’re structuring it this way, take a look at this post:

Creating the Office Script

In the workbook, I created a simple Summary sheet where the Sales column is totaled and that result is stored in a named cell called TotalSales.

function main(workbook: ExcelScript.Workbook): number {

    const TotalSales = workbook
        .getNamedItem("TotalSales")
        .getRange()
        .getValue() as number;

    console.log("TotalSales:", TotalSales);

    return TotalSales;
}

The Office Script retrieves the value from that named range, logs it for debugging, and returns it as a number to Power Automate so we can compare it against the AI-generated total as a validation step.

OK, with the workbook ready, let’s move over to Power Automate. I’m going to create a manually triggered flow for now, though you could easily switch it to run on a schedule or another trigger later. Since our data lives in Excel, there are a few extra setup steps involved before everything is wired together.

Get the workbook content

First, search for the action called “Get file content using path.” For this parameter, you simply point it to your workbook. And remember, you can rename each step so the flow is easier to read later.

Run the custom prompt

Next, choose “Run a prompt” from AI Builder”

Open the Excel workbook [Excel workbook] 
Locate the table containing sales data.



The dataset contains transactional sales data with fields such as:
Date, OrderID, Salesperson, Region, Customer, Product, Category, Units, UnitPrice, and Sales.

Primary KPI:
- TotalSales = sum of the Sales column.

Contextual calculations (for internal use in commentary only):
- TransactionCount = number of rows contributing to TotalSales
- AverageSale = TotalSales / TransactionCount
- TopCategory = category contributing the highest share of TotalSales
- TopSalesperson = salesperson contributing the highest share of TotalSales

Flash Report Objective:

Return:
- TotalSales (numeric only, no formatting)
- Commentary (1–2 concise executive-style sentences)

Commentary Guidance:
- TotalSales is the anchor KPI.
- Use contextual metrics to highlight meaningful performance dynamics such as:
  • Category mix (Hardware vs Electronics vs Service)
  • Revenue concentration by salesperson or customer
  • Volume vs pricing effects (unit-heavy vs premium items)
  • Product mix influence on revenue
- Focus on business interpretation, not mechanics.
- Do NOT restate every metric mechanically.
- Do NOT mention column names, sheets, or calculation steps.
- Do NOT invent trends, time periods, comparisons, or benchmarks not present in the data.
- Neutral, confident executive tone.

Rules:
- Do not filter or modify the workbook.
- Do not fabricate data.
- Return valid JSON only.
- No text outside the JSON object.

Examples of acceptable commentary style:

- "Revenue reflects a balanced mix across Hardware and Electronics, with premium Service transactions meaningfully lifting overall performance."
- "Sales are moderately concentrated among top contributors, though revenue remains diversified across product categories."
- "Performance is supported by steady transaction volume, with higher-priced offerings contributing disproportionate revenue impact."
- "Revenue mix suggests balanced regional contribution without material dependence on a single product line."


Output format:

{
  "TotalSales": number,
  "Commentary": string
}

You can rename this prompt to something clearer, like ‘Sales Flash Report Generator.’ Before saving it, we should test it, and to do that we need to upload a sample workbook. Go to the three dots, open Settings, and turn on the code interpreter.

Next, upload a sample workbook. This file is only for this test run. In the prompt, locate the placeholder [Excel workbook], delete it, type ‘/’, and choose Image or document. That dynamic field is where you will upload the workbook for testing. Later, when we build the flow, we will pass this in as a variable instead of uploading it manually.

Also, under the output panel on the right where it says “Model Response,” we want the result returned as JSON. There are a few setup steps to get there, but once everything is configured you should see a test result come back. Each run may vary slightly, but ideally the sales total is correct. We will validate that by comparing it to the value returned from the Office Script, and the commentary field will just provide some additional narrative context.

We are structuring the output as JSON because it makes it much easier to extract specific pieces for a downstream report. It also gives us a clean way to verify that the TotalSales figure is accurate before pushing anything forward.

Pass the file contents to the prompt

Go ahead and save the prompt. You will notice there is one more parameter we need to configure. For the purposes of the flow, we have to specify where the workbook will come from. The file we uploaded earlier was just a one-time sample for testing.

In the actual flow, the workbook should be passed in dynamically based on its location, which we can retrieve from the previous step. That said, this action expects the file content as raw base64. We can generate that using the base64() function and pass it directly into this step.

base64(body('Get_file_content_using_path'))

Run the Office Script

The prompt has now been run against our workbook, but before we report anything out, we need to confirm the numbers are correct. There are a few ways to validate this, and if you are even slightly risk averse, you should absolutely be checking these totals yourself before sharing them. Even so, this automated cross-check is a smart safeguard.

Our goal is simple: verify that the Total Sales calculated by the generative AI matches the Total Sales calculated by the Office Script. What happens next in the flow will depend on whether those two values agree. Add the step called “Run script” to your flow, then specify which Office Script to execute and which workbook it should run against.

Set up the condition

Now we want to cross-check that the Total Sales calculated by the prompt’s Code Interpreter matches the Total Sales calculated by the Office Script. If the numbers tie out, we will send the flash report. If they do not, the report will not run and instead I will send myself an email to investigate.

To set this up, add a Condition step to the flow and configure it to compare the TotalSales value from the Office Script with the TotalSales value returned by the prompt. The expressions you will need for this comparison are shown below.

outputs('Run_script')?['body/result']

outputs('Run_a_prompt')?['body/responsev2/predictionOutput/structuredOutput/TotalSales']

If false, send an email

There are plenty of ways to handle this, but I am going to keep it simple and just use the “Send an email” action. You will of course want to use your own email address here, not mine. The idea is simply to send yourself a quick, hard-coded message letting you know the numbers did not match so you can take a closer look.

If you wanted to get fancy, you could include the TotalSales value from the Office Script and the value from the prompt in the email body so you can immediately see the discrepancy. That could be a helpful starting point. Personally, I assume I will need to do some old-fashioned analyst digging anyway, so I am keeping it simple for now.

If true, post to Teams

Now let’s look at the better outcome. If the two values do match, we will go ahead and post the Sales Flash to Teams.

We will post it as an Adaptive Card. An Adaptive Card is simply a structured JSON layout that Teams understands how to render as a clean, formatted message instead of plain text. Because our inputs from the prompt are already in JSON, it actually makes things easier to stay in JSON. That way we keep everything structured and consistently formatted from calculation all the way through presentation.

Of course, that means we need to define a bit more JSON to design the card itself. We will use the Adaptive Card schema framework to do this. Think of it as a template that tells Teams how to display titles, labels, and values. The full card definition is shown below.

You will also notice that I apply a small amount of formatting to the TotalSales variable so it renders as currency, for example $3,760.00 instead of 3760. That final polish makes the flash report feel much more like a proper financial summary.

{
  "type": "AdaptiveCard",
  "version": "1.5",
  "body": [
    {
      "type": "TextBlock",
      "text": "Sales Flash Report",
      "weight": "Bolder",
      "size": "Large"
    },
    {
      "type": "FactSet",
      "facts": [
        {
          "title": "Total Sales:",
          "value": "@{formatNumber(float(coalesce(outputs('Run_a_prompt')?['body/responsev2/predictionOutput/structuredOutput/TotalSales'], 0)), 'C2', 'en-US')}"
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "@{outputs('Run_a_prompt')?['body/responsev2/predictionOutput/structuredOutput/Commentary']}",
      "wrap": true,
      "spacing": "Medium"
    }
  ]
}

Go ahead and run the flow. Ideally, you should see something like this. If not, you will receive an email alerting you that there was a variance.

Conclusion

There is a lot more we could layer on top of this. Just because the total ties out does not mean the explanation is solid. The math could be right while the commentary is completely off. You might decide to add additional summary calculations to triangulate the results, or tighten the prompt so it is more deterministic and less free to hallucinate details that are not actually in the data.

You could also introduce a formal approval step so the report routes for signoff or rejection before it ever posts to Teams. That would make the entire process more controlled and auditable, especially in a finance setting.

I would love a clean way to directly guide or override the prompt’s commentary within the flow itself, but at least right now I am not seeing a simple, built-in way to do that.

Even so, this demonstrates that with a bit of structure and creativity, you can absolutely cross-check AI-generated reports. There is real opportunity here, and I will keep experimenting and sharing what I discover.

The post How to validate AI-generated Excel reports with Office Scripts first appeared on Stringfest Analytics.

Still, it’s worth understanding what’s possible today.

Before I go any further, I should say that many of these ideas are heavily inspired by Matthew Devaney. His content has genuinely helped me see a practical path toward actually using Copilot Studio and the Power Platform to build real AI agents and workflows. Thanks, Matthew!

In this walkthrough, we’ll build a very basic agent that populates a variance highlight deck. The setup includes two files.

First, we have a dataset containing a standard monthly variance table.

We also have a very basic PowerPoint template that serves as the reporting deck.

Realistically, your actual deck will probably be more complex than this example, and as the task scales, the prompting and orchestration will need to scale with it. But you have to start somewhere.

You can download those two exercise files below to follow along:

• https://github.com/summerofgeorge/blog-files/raw/refs/heads/master/VarianceReportTemplate.pptx
• https://github.com/summerofgeorge/blog-files/raw/refs/heads/master/variance_data.xlsx

Be sure both files are uploaded to an accessible location in your OneDrive or SharePoint environment that Power Automate can connect to.

Before diving in, I strongly recommend being comfortable with the fundamentals of Power Automate. You should understand how to create triggers, structure flows, and write basic Power FX expressions. If that foundation isn’t there yet, I suggest starting with my LinkedIn Learning course designed specifically for Excel users before attempting to build workflows like this:

To begin, head to make.powerautomate.com and create a new flow. I’ll set this up as a manual flow for now, though in practice it would likely make more sense to schedule it as part of your standard month-end reporting process.

Next, we’ll add the first action in the flow: retrieving the path to our dataset. We need the file path because it will be passed as a variable into the prompt, allowing the flow to locate and work with the correct file at runtime.

Whether your file lives in OneDrive or SharePoint, choose the action labeled “Get file content using path.” That’s the one we’ll use to reference the template dynamically within the flow.

Next, locate your workbook in the same way. It’s a good idea to rename this step to something like ‘Get variance data workbook using path,’ since we’ll repeat the same action shortly to retrieve the PowerPoint template. Clear naming will make the flow much easier to follow as it grows.

Make sure you now have two steps that look similar to this. That way, both file paths will be available and ready to use in the next step of the flow.

Next, search for the action called “Run a prompt.” As the name suggests, this step allows you to execute a generative AI prompt directly within the flow and then use its output in the steps that follow.

Go ahead and create a new prompt. The full text is included below. We’ll walk through how to configure it so it functions properly within the flow.

Open the PowerPoint template  [PowerPoint Template] 
Open the uploaded Excel workbook  [Excel workbook]
 
Locate the table named tbl_variance.

Identify the Month column in tbl_variance.

Determine the latest month present in the data:
- If Month is a date field, use the maximum date.
- If Month is text (e.g., "January", "February"), determine the chronologically latest month based on calendar order.
- If Month includes year (e.g., Jan 2026), use the most recent year-month combination.

Set this value as Selected Month.

Filter tbl_variance for Selected Month only.

Aggregate data at the Department level by summing:
- Budget
- Actual
- Forecast
- Prior Year Actual

Calculate:
- Total Budget
- Total Actual
- Total Variance (Actual - Budget)
- Variance %
- Forecast Variance (Actual - Forecast)

Determine:
- Largest favorable department variance
- Largest unfavorable department variance

Rules:
- For Revenue accounts, positive variance is favorable.
- For Expense and COGS accounts, negative variance is favorable.
- Only highlight departments where absolute variance exceeds BOTH:
  - 5% of department budget
  - $50,000
- If no department exceeds threshold, state performance was within tolerance.

Executive Summary Requirements:

Write the executive summary as 4–6 bullet points.

Formatting rules:
- Each bullet must be a single concise sentence.
- Do NOT write paragraphs.
- Do NOT include numbering.
- Do NOT include introductory text.
- Preserve line breaks so each bullet appears on its own line.
- No emojis.
- Use concise executive tone.

Content requirements:
- One bullet summarizing overall performance vs budget (include total variance and variance %).
- One bullet addressing forecast variance if material.
- One bullet highlighting the largest favorable department (include dollar and % impact).
- One bullet highlighting the largest unfavorable department (include dollar and % impact).
- Optional additional bullet(s) if other material insights exist.

Update the PowerPoint:

Slide 1 shape named "Title":
Variance Report – {Selected Month}

Slide 2 shape named "Variance report":
Insert the bullet-point executive summary exactly as written.
Do not convert bullets into a paragraph.
Do not alter formatting or layout of the slide.

Do not create new slides.
Return the updated PowerPoint file.

Before we test the flow, go to Settings and turn on ‘Enable code interpreter.’ This setting is required if you want the prompt to create or modify files. In our case, we need it enabled so the prompt can generate the updated PowerPoint deck.

Next, we’ll configure the inputs at the top of the prompt. Where you see the placeholders like [PowerPoint Template] and [Excel workbook], those need to be connected to the actual files from the flow.

Click into each field, type the / symbol, and choose the option labeled “Image or document.” From there, you can map in the corresponding file content from your earlier steps.

This next part can feel a little confusing.

In order to test the prompt, you need to upload placeholder files directly into the prompt interface. So go ahead and upload your sample deck and workbook here. You can also rename the variables at this stage to make them clearer and easier to follow.

Just remember: these uploads are only for testing inside the prompt editor. They are not part of the actual workflow. When the flow runs in Power Automate, it will use the real files stored in your OneDrive or SharePoint location.

All right, we’re ready to test.

Before running it, make sure the Output setting is configured to “Documents/Images” since the prompt will be generating a PowerPoint file. Once that’s set, click ‘Test’ and see what it produces.

Great. Once you see an output, go ahead and download the file and review it.

You may find that it takes a couple of test runs to get consistent results while working inside the prompt editor. For whatever reason, that seems to happen during testing here. I have not seen the same issue once everything is running inside Power Automate itself.

Amazing. You should see something like this. It’s alive, right? You just generated a PowerPoint variance deck using AI. That’s pretty cool.

But we do not want users going into the prompt builder every time they need a report. The goal is to run this with a single click or on a schedule. So the next step is figuring out how to automatically capture and save the generated file. Those final steps are critical and, honestly, a bit more involved than everything we have done so far.

Amazing. You should see something like this. It’s alive, right? You just generated a PowerPoint variance deck using AI. That’s pretty cool.

But we do not want users going into the prompt builder every time they need a report. The goal is to run this with a single click or on a schedule. So the next step is figuring out how to automatically capture and save the generated file. Those final steps are critical and, honestly, a bit more involved than everything we have done so far.

Go ahead and save your prompt and exit the editor.

Now we need to define the variables that will feed the prompt when the flow runs. Remember, we are not using the placeholder files anymore. We are passing in the actual files stored in OneDrive or SharePoint.

To do that properly, we have to convert the file content into Base64. The prompt runner expects the files in that format, so this step ensures it can correctly interpret and work with them at runtime.

To do this, populate the parameters for your workbook and deck as shown below:

base64(body('Get_variance_data_file'))

base64(body('Get_variance_report_deck_template'))

Now that the prompt knows where to retrieve the input files, we still need to decide where the generated deck will be saved.

To handle that, add one more action: ‘Create a file.’ First, specify the location where the file should be stored. I’ll borrow a smart approach from Matthew Devaney and use the Copilot Chat Files folder as a convenient place to save outputs like this.

First, we’ll generate a file name for the deck. I’ll call it VarianceDeck and append a random ID so we don’t overwrite prior runs:

concat(concat('VarianceDeck', rand(1000, 9999)), '.pptx')

This creates something like VarianceDeck4821.pptx.

If you’d prefer something more structured, you could also incorporate today’s date into the file name instead, which can be helpful for version tracking.

Now we need one more Power Fx expression, and this is an important one.

When the prompt generates the PowerPoint file, it does not return it as a normal binary file. It returns the file encoded as Base64 text. That is essentially a long string representation of the file’s binary content. This encoding makes it safe to transmit through APIs and flow outputs, but it is not directly usable as a downloadable .pptx file.

So before we can save it with the ‘Create a file’ action, we have to convert that Base64 string back into binary.

That is exactly what this expression does:

base64ToBinary(first(outputs('Run_a_prompt')?['body']?['responsev2']?['predictionOutput']?['files'])?['base64_content'])

Once converted to binary, the ‘Create a file’ action can write it to OneDrive or SharePoint as a proper, downloadable PowerPoint template.

Yes, there are quite a few steps. But now we’re ready to save the flow and run a full test.

If everything is wired up correctly, you should see a freshly generated deck sitting in your Copilot Chat Files folder, ready to open and review.

Keep in mind, this is generative AI, so results can vary. I have generally seen the numbers come through correctly, but you should absolutely review and validate everything before sending it to anyone.

Still, this gives you a real starting point for automating PowerPoint variance presentations, which is pretty powerful. There is clearly much more that can be built on top of this. I am learning as I go, and hopefully you will experiment as well and share what you discover.

The post How to automate a monthly variance deck with Power Automate and Copilot first appeared on Stringfest Analytics.

In my last post, I walked through how to prompt a user to upload a workbook and have an agent act on it:

Part of that was simply me pressure-testing my own understanding of these workflows. Even that relatively simple scenario turned out to be more complex than expected.

The more I dig into it, the more I’m realizing that for many practical use cases, custom agents directly inside Microsoft 365 Copilot may actually be a better fit than going all-in on Copilot Studio.

So in this post, we’re going to build a PDF expense report agent.

The idea is straightforward: a user uploads PDF receipts, the agent classifies each expense against predefined account codes, and then generates a fully populated Excel workbook. For anyone who travels for work, this could be a real time-saver.

You can download the exercise files below to follow along.

To get started, sign in to your Copilot Pro-enabled Microsoft 365 account and navigate to Agents > New Agent from the sidebar.

Configuring the agent

Details

The first step is to define what this agent actually is.

The name and description should be clear and specific. You want something that makes it obvious what the agent does when you (or someone else) sees it later. I’ll use the name “Expense Code PDF reader.”

And for the description:

Analyzes uploaded PDF receipts and assigns the appropriate expense account based on your configured account mapping.

OK now let’s look at the instructions.

Instructions

The name and description tell Copilot what the agent is, but the Instructions determine how it operates:

Task: Classify ALL Transactions in an Uploaded PDF Receipt and Return an Excel Workbook

You are an expense classification assistant.

When a user uploads a PDF receipt:

1. Extract all readable text from the PDF.
2. Identify and extract EVERY transaction found in the document.
   - The PDF may contain multiple receipts or multiple transactions.
   - You must process ALL transactions, not just the first one.

For each transaction, identify:
- Transaction Date
- Vendor Name
- Total Amount

Then, for each transaction:

3. Use the connected Expense Account knowledge source to determine:
   - Expense Account Code
   - Expense Account Name

Rules:

- Only use the connected knowledge source for account codes.
- Do not invent new account codes.
- If no clear match is found, use:
  Account Code: 9999
  Account Name: Uncategorized
- If Date is missing, leave it blank and add "Date not found on receipt" in Comments.
- If Total is unclear, add "Total unclear" in Comments.
- Format Date as YYYY-MM-DD.
- Format Total Amount with two decimals.
- Convert Vendor Name to Proper Case (capitalize the first letter of each word).
- Each transaction must appear as its own row.

Output Requirements:

- Return the results as an Excel workbook (.xlsx).
- The workbook must contain exactly one worksheet.
- The worksheet must contain exactly one structured Excel table.
- The table must use this exact column structure and order:

Date | Vendor Name | Expense Account Code | Expense Account Name | Total Amount | Comments

- Include one row per transaction found in the PDF.
- Autofit all column widths so that content is fully visible.
- Do not output JSON.
- Do not explain your reasoning.
- Do not return plain text.
- Only return the Excel workbook.

At a high level, this instruction set establishes the task, the constraints, and the required output. It directs the agent to extract every transaction from an uploaded PDF, classify each one using a predefined account mapping, and generate a structured Excel workbook. It also imposes strict data rules, requiring the use of the connected knowledge source for account codes, consistent formatting, full transaction coverage, and a defined fallback when no match is found. Finally, it specifies that the result must be a single workbook containing one properly structured Excel table, with no additional commentary or alternate formats.

By clearly defining the objective, the guardrails, and the output format, we minimize variability and shape the agent into a reliable reporting mechanism rather than an open-ended assistant.

Set up the knowledge source

The Knowledge section is where we connect the reference file that governs how expense categories are assigned. In this workflow, the knowledge source functions as the single source of truth for accounting codes. The agent is not allowed to infer categories from general knowledge or make judgment calls. It must look up each classification directly from the structured mapping we provide.

For this example, we will upload ExpenseAccounts.xlsx, which you can download from the repo exercise files. This file contains the approved expense account codes and names the agent is permitted to use. In a production environment where the chart of accounts changes frequently, it would make more sense to store this file in OneDrive or SharePoint so updates can be managed centrally. For demonstration purposes, uploading a static copy keeps the setup simple and controlled.

After uploading the file, it is critical to enable “Only use specified sources.” This setting ensures that the agent relies exclusively on ExpenseAccounts.xlsx when assigning expense categories. If this toggle is left off, the model may draw on its general training data, which introduces variability and risk. By restricting it to the specified source, we guarantee that all classifications trace back to the defined accounting logic and remain consistent across runs.

Set up the capabilities

In the Capabilities section, turn on Create documents, charts, and code. This is required because the agent must generate and return an actual Excel workbook. Without this enabled, it would only be able to respond with text.

Since our output is a structured .xlsx file, this capability is essential. We can leave image creation turned off, as it is not needed for this workflow.

Namely we need to turn on the ability to create documents.

Set the suggested prompts

Optionally, we can add a starter prompt under Suggested Prompts to guide users into the correct workflow.

This makes the purpose clear and ensures the agent runs the receipt classification and Excel report process as intended.

Great work! Go ahead and save your agent.

Testing the agent

Use the starter prompt to begin testing, and be sure to upload your PDF receipts. Click Upload images and files to add them. You can use the sample PDFs in the companion files to test everything.

In my experience, the agent performs best when each receipt is uploaded as its own PDF. You can upload multiple files, just avoid combining them into a single document. If you needed more advanced file handling rules, that’s where a more robust setup like Copilot Studio would come into play.

It shouldn’t take long to receive your results, including a downloadable Excel workbook.

And just like that, we get an Excel table back with the expenses classified.

For the entry missing a date, the agent flags it accordingly and leaves it unclassified. The “market” charge also was not categorized. It might fall under meals & entertainment, but maybe not. This is generative AI, it works probabilistically.

If we want to make this more reliable, we could update the instruction set to explicitly treat “market” purchases as meals & entertainment. That is exactly why the knowledge source matters so much. The clearer and more comprehensive it is, the more consistent the classifications become.

Conclusion

In this post, we built a Microsoft 365 Copilot PDF expense account agent.

What I like about this example is how practical it is. Filling out expense reports after a business trip is one of those tasks I genuinely dread, so having a structured way to automate part of that process feels like a real win.

I’m looking forward to exploring this space further and seeing how far we can push these kinds of focused, workflow-driven agents.

The post How to create an expense report agent with Copilot 365 first appeared on Stringfest Analytics.

Microsoft is making it easier to build lightweight Copilots directly inside Microsoft 365, but those tools are limited. They’re essentially chatbots with custom instructions and knowledge sources. They don’t let a user upload a new workbook and actually perform actions on it [EDIT: not entirely true, working on some posts about how to do simple versions of this with 365 agents]. If you want that level of capability, you need Copilot Studio.

I’m still early in my exploration of the more advanced features, and if you want serious depth, Matthew Devaney’s work is excellent. But part of writing these posts is helping me sharpen my own understanding and adapt these ideas for practical Excel uses.

In this walkthrough, we’ll build a simple agent that allows users to upload a workbook and generates a basic data dictionary overview.

Yes, Copilot inside Excel could theoretically handle something like this. What I like about the Copilot Studio approach is that it’s structured. The instructions are predefined. The workflow is engineered once and reused. The user doesn’t have to reinvent the prompt every time.

As I get more comfortable with this platform, I’ll share more advanced examples. For now, let’s start simple. Head to copilotstudio.microsoft.com and follow along if you’d like.

Download the exercise files here:

• https://github.com/stringfestdata/copilot-studio-demo-files/raw/refs/heads/main/copilot-studio-table-profiler-example-1.xlsx
• https://github.com/stringfestdata/copilot-studio-demo-files/raw/refs/heads/main/copilot-studio-table-profiler-example-2.xlsx

Setting up the agent

To begin, create a new agent and give it a clear, descriptive name like Workbook Table Data Profiler. If you’d like, assign a custom icon as well to make it easier to spot later. I’ll use the Excel logo so it’s instantly recognizable.

You will also need to complete the Description and Instructions fields. The Description explains what the agent is designed to do and appears in the overview so you and anyone else working with it can quickly understand its purpose. The Instructions define how the agent should behave and what steps it should follow when responding. This is where you shape its logic and expectations.

I will provide the exact text for both sections below so you can copy and paste it directly into Copilot Studio.

Be sure to save your work early and often. It is very easy to click away from a page in Copilot Studio and lose unsaved changes, so make it a habit to save after completing each section.

Here is the agent description:

Analyzes an uploaded Excel workbook and generates a structured profile of all structured Excel tables it contains. The agent identifies each table, counts rows and columns, categorizes fields by data type, and provides concise structural observations based strictly on visible column names and data types.

Now the instructions:

You are a data profiler assistant for Excel workbooks.

Your job is to:

1. Open the uploaded Excel workbook.
2. Identify all structured Excel tables in every worksheet.
- Ignore raw ranges that are not defined as structured tables.
3. Count the total number of structured Excel tables.
4. For each structured table:
- Extract the table name.
- Extract the worksheet name.
- Extract the column headers in order.
- Generate a short 1–2 sentence plain-English description of what the table appears to track based only on the column names.

Output rules:

- Only include structured Excel tables.
- Do not modify the workbook.
- Do not describe your reasoning.
- Do not output JSON.
- Do not use Markdown tables.
- Keep descriptions short and clear.
- Follow the exact output format specified in the runtime prompt.

If no structured Excel tables are found, output exactly:

No structured Excel tables were found in this workbook.

Once your overview matches the example shown above, we will move on to configuring the Topics.

Setting up the topic

Next, go to Topics in the left-hand menu and select Add topic, then choose From blank. This is where we define the interaction flow that actually performs the profiling. I will name this topic “Upload workbook and profile data:”

A topic is essentially a structured conversation path. It controls when the agent kicks in, what it asks the user for, what logic runs behind the scenes, and what ultimately gets returned. In this case, we are building a very focused workflow: upload a workbook, analyze it, and return a structured table profile.

The topic will run roughly like this:

• Trigger the topic when the user indicates they want to analyze or upload a workbook.
• Prompt the user to upload an Excel file.
• Store the uploaded file in a variable.
• Run a prompt that inspects the workbook and extracts structured information about its tables.
• Capture the output of that prompt.
• Return the structured profile back to the user as the final response.

By defining this as a topic, you create a repeatable, reliable workflow instead of relying on one-off prompts every time someone uploads a workbook.

To get started, go ahead and fill in the topic description, as reproduced below. This is what helps the agent determine when to run the topic. That’s what the “trigger” and “the agent chooses” settings are about. When the agent interprets the user’s message as intent to use this workflow, it will automatically kick off the topic.

Triggers when a user wants to analyze or profile an Excel workbook. Prompts the user to upload a file, scans all worksheets for structured Excel tables, and returns a structured summary listing each table’s name, worksheet, columns, and a short plain-English description of what the table appears to track. Only structured tables are included, and the workbook is not modified.

Prompt the user to upload the file

Now that the user has expressed their intent and we know they want to profile a workbook, the next step is to ask them to upload the file.

Add a “Ask a question” node and configure it to request a file upload. You can customize the prompt so it clearly tells the user to upload an Excel workbook for analysis. Make sure the input type is set to File so Copilot Studio expects a document rather than text.

When you do this, Copilot Studio automatically creates a variable to store the uploaded file. By default, it will be named something generic like Var1, which is not very helpful. Rename it to something more descriptive, such as varExcelFile. Clear variable names make the flow easier to understand and maintain, especially as your topic grows more complex.

The variable now contains the uploaded workbook and can be used in subsequent steps. We’ll pass this file into a prompt for analysis. Add a new node, select “Add a tool,” and choose “New prompt” for the next step.

Setting up the prompt

When you select this option, a prompt editor will open. We’re going to use generative AI to run this prompt against whatever workbook the user uploads. Paste the prompt below into the editor as a starting point.

Goal:
Generate a structured profiling summary for all structured Excel tables in this workbook.

Inputs
Excel file: [Upload Excel workbook]  


Task:

1. Review the entire workbook.
2. Identify only structured Excel tables (ignore normal cell ranges).
3. Count the total number of structured tables.
4. For each structured table, produce one section using the exact format below.
5. Base all observations strictly on visible column names and data types.

Output Format (follow exactly):

Total Structured Tables: <number>

----- TABLE 1 -----
Table Name: <TableName>
Worksheet: <SheetName>

Row Count: <number>
Column Count: <number>

Numeric Columns:
- <ColumnName>
- <ColumnName>

Date Columns:
- <ColumnName>

Text Columns:
- <ColumnName>

Other Columns (if any):
- <ColumnName>

Observations:
<Write 1–3 concise sentences describing structural characteristics of the table. 
Mention patterns such as calculated-looking fields, presence of time-based columns, mix of dimensions and measures, or suitability for aggregation. 
Base statements only on column names and data types.>

Repeat the exact structure for TABLE 2, TABLE 3, etc.

If no structured Excel tables are found, output exactly:

Total Structured Tables: 0
No structured Excel tables were found.

Rules:

- Include only structured Excel tables.
- Do not modify the workbook.
- Do not output JSON.
- Do not use Markdown tables.
- Do not add commentary before or after the required structure.
- If a category has no columns, write “None”.

We’re going to need a few things to close the loop on making it so the prompt can run with the uploaded workbook. First, we’re going to hit the three dots net to Instructions and go to Settings.

Make sure you enable the code interpreter. This is essential because it allows the prompt to actually read and process Excel files. Without it, the model cannot properly inspect the workbook. You will also see options like temperature and retrieval. For structured profiling, a lower temperature is usually better so the output stays consistent.

Exit the Instructions view to return to the main prompt editor. Now we need to pass the uploaded workbook into the prompt dynamically.

Find the placeholder text [Upload workbook] near the top of the prompt. Delete that text and type a forward slash (/). This will open the menu for inserting dynamic entities. Select Image or document.

Give the entity a clear name, such as “Upload Excel workbook.” Then upload a placeholder test workbook. This file is only used so the prompt has something to run against during setup. Upload one of the sample workbooks provided earlier in this post.

Now you’re ready to test the prompt. Run it and give it a moment to complete. It may take a couple of attempts the first time. If you see an error, try running it again. Occasionally it takes a run or two for everything to settle in.

But eventually you should have some output!

Save your prompt.. but we’re not done yet.

Right now the Prompt node doesn’t automatically know which file to use. If you leave it like this, you’d have to manually upload a workbook inside the prompt builder every time. Instead, we want it to use the file the user uploads in chat.

So under the Prompt node’s input, bind the file parameter to varExcelFile. That variable holds the uploaded workbook from the conversation. Once it’s mapped, the prompt will automatically use whatever file the user provides.

Those red warnings about missing input bindings look dramatic, but they’re usually just stale references. Copilot Studio may have renamed or regenerated internal IDs when you edited the upload step. The Prompt node is just pointing to an old name that no longer exists. Rebinding to varExcelFile fixes it. After saving or refreshing, the warnings typically go away.

Similarly, give the output of this Prompt node a clear, memorable name. For example, set the prompt output to varPromptOutput so you can easily reference it later in the topic:

Send the result to the user

Now we’re finally ready to return the result to the user.

Add a new node, “Send a message.” Start the message with something simple, like:
“Here’s a summary of your workbook’s tables.”

Then insert part of the prompt’s output. Click the small {x} icon in the editor to open the variable picker. From there, select varPromptOutput and choose its text property. That’s the actual generated content you want to display.

Amazing! Let’s go ahead and test this thing.

Testing the agent

Over on the right side of the screen, you’ll see the test panel. Start the agent with something like, “profile my workbook.” That should trigger your upload-and-profile topic, and Copilot Studio will prompt you to upload a file.

Go ahead and upload a different workbook than the placeholder you originally used. Hit send, and after a moment, the agent should return the overview in chat:

If you try this with the second demo workbook and then open that file to double-check the output, you’ll see something interesting: Copilot Studio is actually pretty smart here. It only profiles structured Excel tables. Stray ranges, helper cells, and random data sitting on the sheet are ignored. It focuses strictly on defined tables.

On the other hand, you might notice the formatting of the results is a little odd, or that some of the table descriptions feel thin or generic. That’s normal. This is a solid foundation, but there’s definitely room to refine the prompt and improve the quality of the output.

Conclusion

I hope this walkthrough sparked a few practical ideas you can try in your own projects. One of the real advantages of Copilot Studio is that you are not limited to a one-off chat interaction. You can design something structured and repeatable, especially when you allow users to upload their own files and trigger a defined workflow behind the scenes.

Once you have that foundation working, there are several ways to make the experience more robust. For example, you could enforce a defined JSON schema so the output follows the same structure every time. That makes downstream steps more reliable and easier to maintain. Instead of returning plain text, you could take it a step further and write the results directly into a newly generated Excel workbook or text file, giving the user something tangible and ready to use. You could also connect a knowledge source to enrich the uploaded file with reference data, validation rules, or business logic before returning it.

All of that requires a bit more care in how you design and test your Copilot Studio flow, but it is achievable. And once you see it working end to end, it starts to feel less like a chatbot and more like a purpose-built analytics assistant. More on that soon.

The post How to upload Excel workbooks to Copilot Studio first appeared on Stringfest Analytics.