Once this connection is in place, you can start running prompts or reports on a schedule; for example, automatically generating a weekly variance report and posting it to Teams or emailing it. You could also build guardrails into your flows, such as approval steps or editable review stages, so your team can validate AI-generated insights before they’re published.

In this post we’ll connect a sales data table so that the data flows into Dataverse through Dataflows. I know that in the real world your sales data probably doesn’t live in Excel, or at least I hope it doesn’t. Excel often isn’t the best place for a single source of truth. But I also live in the real world, and I know that Excel has its advantages for powering those sources of truth. So here’s how to bridge that gap into a more developer and enterprise friendly way of doing things.

Save this workbook somewhere easy to find on your OneDrive or SharePoint. You’ll need it there for the next steps.

What is a Dataflow?

A Dataflow is a cloud-based way to extract, clean, and load data into the Power Platform. Power Apps will be our way in to set up these Dataflows, since that is where you can create and manage them to feed data into Dataverse. Dataflows use the same Power Query technology that Excel users already know, but instead of saving transformations inside a single workbook, they are stored in the cloud so multiple apps and services can reuse the same prepared dataset.

Once a Dataflow loads data into Dataverse, it becomes accessible across the Power Platform, including Power Apps for building interactive applications, Power Automate for scheduling and running flows, and Copilot Studio for creating generative AI experiences that draw on your Dataverse data.

A comparison table of using Power Query in Excel versus Dataflows in Power Apps follows.

Syncing the Excel data to Dataflows

To get started, go to make.powerapps.com and head to Tables. These data sources are actually Dataverse tables. You’ll notice that many of them are already there by default. You didn’t create them, and you can safely ignore them for now. Dataverse includes a set of standard tables out of the box like Account, Contact, and Appointment because they support core business functions used by many Power Apps solutions. For this walkthrough, we’ll focus on adding our own table by going to the top menu, selecting Import, and then choosing Import data with Dataflows.

At the top, select Import, then choose Import data with Dataflows. This is where we’ll set up our live connection between Excel and Dataverse. While there are several simple ways to import static data from Excel (for example, by uploading a CSV), Dataflows are what make the connection dynamic.

Great work! Now select Choose Data Source, then pick “Excel workbook.”

At this point, your workbook needs to be stored on OneDrive or SharePoint. Go ahead and sign in with your account, then browse to and select the workbook you want to connect.

Now things should start to look more familiar if you’ve used Power Query before. You’ll first choose which parts of the workbook to connect to. In this case, I’m connecting only to the table I need, currently named “Table1” (I really should have named it better!). Fortunately, we’ll get a chance to give it a clearer name once it’s loaded into Dataverse.

Now we are fully in Power Query, and you can see the label in the upper left corner confirming it. If you wanted to build data transformations, this is where you could do it. For now, I will skip that step and select Next to finalize the Dataflow.

Now we have a few options for how to finalize this connection into Dataverse. I am going to have it create a completely new table.

Here we can name the table, set data types, define a primary key, rename columns, and make other adjustments under the Column mapping dropdown. Each column is assumed to have a single type, just like in Power Query.

For now, I will assume Dataflows and Dataverse handle this setup correctly. If something needs to be changed later, that is fine. This process is reproducible, so we can always go back and update it.

Last, we’ll be asked how we want the Dataflow to refresh. We can set it to run on a schedule or trigger it manually. I am going to set it to refresh manually for now. Since there is a way to trigger a refresh directly within Power Automate, I’ll handle automation later as part of a broader Power Automate flow.

Great work. Now let’s test it with a manual refresh. Go back to your Excel workbook and add a test row. Then return to Power Apps and open Dataflows. You should see your active Dataflow listed, and it may take a moment to finish publishing. From there, select the three dots next to your flow. You can give it a name and choose Refresh to trigger the update.

Amazing. The new record is there. We’ve successfully set up a Dataflow that brings data from an Excel workbook into Dataverse.

Conclusion

In this post, we connected an Excel workbook to a Dataflow in Power Apps and saw how this simple step brings your spreadsheet data into the Power Platform ecosystem. Once the data is stored in Dataverse, it’s no longer limited to one workbook. It can power automations, apps, and even AI-driven summaries across your organization.

In a future post, we’ll build on this foundation by creating a Power Automate flow that pulls your Dataverse data and uses a prompt to generate a dynamic weekly summary. We’ll even have it post directly to Teams for an automated, AI-powered status update, set up an approval process for review and more. Stay tuned!

The post How to connect your Excel workbook to the Dataverse via Dataflows first appeared on Stringfest Analytics.

Here’s the good news: this is exactly where generative AI shines. If you’re somewhat technical and already familiar with the business context and the problem you’re tackling, generative AI can give you that essential jump-start with VBA.

However, as of now, Copilot in Excel doesn’t quite offer the same effortless automation here that you might get with calculated columns or PivotTables. It occasionally falls short of fully automating VBA tasks.

That’s why, in this post, I’ll walk you through some straightforward workflows to effectively use Excel Copilot when working with VBA. You can follow along using the provided exercise file. Make sure you’ve uploaded it to your OneDrive so Copilot can access it.

We’ve got a workbook containing a small dataset on our sales staff, and we’d like to test-drive Excel Copilot to help automate some tasks and build handy VBA macros.

The worksheet has some mysterious blank rows that keep popping up…. unfortunately, a recurring headache. How about we create a quick macro to tidy this up instantly? Let’s fire up Copilot and see how it can help.

One quick reminder: although Copilot might seem limited to your current dataset, that’s not actually true. As a large language model, Copilot is trained on a massive amount of information and can tackle broader tasks too. Check out my earlier blog post on using Excel Copilot for tasks beyond spreadsheets:

I’ll ask Copilot if it can generate a macro for me to quickly remove all blank rows from the worksheet, and it’ll return something along these lines:

Unlike some of Excel’s other features, Copilot can’t execute and automate this macro on its own. We’ll have to manually add and run the macro ourselves.

It’s not all bad news, though. One nice thing is that Copilot can easily build upon and refine this macro based on further prompts. For instance, if we ask it to add comments explaining the code, it’ll do exactly that:

On the bright side, Copilot actually can “see” your worksheet data and directly reference it in macros, which is pretty cool! Let’s give it a shot. I’ll ask Copilot to create a macro that directly interacts with our data:

“Create a macro that generates a separate workbook for each distinct value in the region column, naming each workbook according to its corresponding region.”

Copilot will provide us with some code that we can then copy and paste into our VBA editor, and then see how well it works… or doesn’t.

When I paste this into the editor, I immediately notice some code highlighted in red. If you’ve spent any time working with code editors, you know seeing red usually isn’t good news. Between the potential formatting issues when copying and pasting and Excel Copilot’s limitations as a prompting environment, we’re bound to hit some bumps. So what’s our next move? How do we work around this?

This is exactly the kind of situation where using Copilot 365 is typically the better approach. Here, you’ll get a dedicated Gen AI prompt environment. It’s similar to ChatGPT, but with the added advantage of being able to securely upload, reference, and sandbox your files within your organization’s walls, eliminating the risk of data leaks.

If you’re new to Copilot 365, check out my post on how to use this tool as your personal Excel tutor:

To get started, head over to the Work mode in 365 Copilot and paste your prompt there. Sometimes you’ll get a solid answer even without referencing your dataset directly. But for greater precision and confidence, it’s best to refer explicitly to your actual data. You can easily do this by clicking the + icon to add content to your prompt:

After you’ve found and selected your file from your OneDrive folder, your prompt will look something like this:

Since 365 Copilot offers a more robust and general-purpose Gen AI interface, you’ll typically receive responses that are clearer, better structured, and easier to use. For instance, your VBA macro will be neatly presented in a highlighted code snippet, making it straightforward to copy directly to your clipboard and paste into the VBA editor. You’ll still need to manually add and run the macro yourself, though. (Don’t trust Copilot if it claims it automatically added it to your workbook! In my experience, that rarely ends well.)

So now, taking the code that 365 Copilot generated, let’s return to the VBA editor. This time, everything looks clean—no red errors—and the macro runs perfectly.

Even if you still encounter some errors, you’re in a much better place to troubleshoot here compared to Excel’s somewhat fragile Copilot interface.

If you think you’ll want to refer back to this workbook and continue building on your macros down the road, I’d recommend setting up a Copilot 365 Notebook. You can learn more about how to do that here:

If this seems like a one-off task and you’d prefer not to deal with the hassle of uploading the workbook into Copilot 365, you can always copy the macro generated in Excel Copilot and paste it directly into 365 Copilot. Doing this gives you the benefits of a more powerful, reliable environment and helps you skip some of the earlier steps.

Now, full disclosure: if you’re a seasoned VBA developer, you might roll your eyes a bit at the code quality here. Generative AI tends to be pretty verbose and redundant, and this code is no exception. If you’re aiming for something highly scalable and performant, you’ll likely need deeper VBA expertise.

While generative AI can assist with some of this, the examples here are mainly intended for quick, ad-hoc tasks rather than major development projects. But hey (sorry not sorry, purists) not everything needs to be production-grade to help people get their work done.

In this post, we’ve explored a few straightforward approaches to getting help with VBA macros using Copilot for Excel. While the process isn’t always seamless or intuitive, it’s still an invaluable resource, particularly if you’re stepping into VBA occasionally or for quick tasks. Plus, your data stays secure, since nothing you share with Copilot leaves your organization’s boundaries.

What questions do you have about using Copilot for VBA macros or Excel Copilot in general? Let me know in the comments.

The post Copilot for Excel: How to work with VBA macros first appeared on Stringfest Analytics.