Connect Excel to Power BI So It Updates Itself

Q: Does the video show how to keep my Excel formatting when the data refreshes?

Yes. In the video Anne shows that the Properties button beside Refresh controls whether a refresh resizes your columns, so switching that off keeps your formatting fixed while only the data updates.

Anne Adair-Major
Jun 23
5 min read

In organisations, it's far too common that a lot of recurring reports run on data that's already out of date before anyone reads them. The issue stems from how reliant we are on exporting data. Even with the best of intentions, you export a table from Power BI into Excel, and by the time you've formatted it for a board pack or the weekly update, and by the time someone actually opens it, the underlying numbers have moved while the spreadsheet hasn't. The reason is that a standard export is a static snapshot, a copy of the figures frozen at the moment you saved the file. With the right know-how in Power BI, though, you can do better than that. You can connect an Excel file directly to a Power BI dataset so the figures refresh on demand, which turns a weekly re-export into a single click. This post sits alongside the video and covers when to use a live connection, why it's worth it, and what to check if the option isn't there.

Key Takeaways

A standard Power BI export is a static snapshot that goes out of date the moment the file is saved, while a live connection keeps the Excel file linked to the Power BI dataset and refreshes on demand.
Connecting Excel to a Power BI dataset replaces a recurring manual export with a single refresh, so you format the file once and refresh it from then on.
A live connection export supports up to 500,000 rows, compared with 150,000 for a static .xlsx export and 30,000 for a .csv.
If the live connection option is greyed out, it's almost always because you don't have Build permission on the dataset, or the report owner hasn't enabled export in the report settings.

https://youtu.be/KH8q7cpsRpI?si=VuL-i2wJyLuYdAXP

In the video above, I walk through the whole thing on screen, step by step. The sections below give you the thinking behind it and the judgement calls the video doesn't stop to make.

Static exports versus a live connection

The difference that matters is whether your export stays connected to the data. A live connection keeps your Excel file linked back to the Power BI dataset, so the figures update when you refresh, while a static export is a one-time copy that never changes on its own. That's the real difference between the two export options Power BI offers you. A static export gives you a clean table that's fine for a one-off. For anything you produce on a schedule, it means rebuilding the same file every cycle. A connected file moves that effort to the front instead, so you set up the layout and formatting once, and from then on you refresh rather than rebuild. The distinction sounds minor, but across a year of weekly reports it's the difference between a few minutes and a few hours a month.

The table below shows the export options Power BI gives you and what each one is for.

Export option	What you get	Best for	Row limit
Data with current layout	A one-time static copy, formatted as shown	A quick one-off you won't revisit	150,000 (.xlsx)
Summarized data with live connection	An Excel table linked to the dataset, refreshable	Reports you produce on a schedule	500,000
.csv export	A plain static file with no formatting	Moving raw data elsewhere	30,000

Source: Microsoft Learn, verified June 2026. Row limits can change, so check the current figures on Microsoft Learn before relying on them.

When a live connection is worth the setup

A live connection earns its place on any report you produce on a schedule, and a static export stays the simpler choice for one-off analysis you won't revisit. The reports where it pays off are the repeating ones, board packs, project updates, monthly finance summaries, anything where the format stays the same and only the numbers change. Those are the reports where manual re-exporting eats time nobody accounts for, and where a stale figure slipping into a leadership deck can do real damage. A connected Excel file deals with both at once, because you're working from current data and you've taken the weekly copy-paste step out of the process entirely. If your work is mostly analysis you run once and never reopen, there's no need for any of this, and a static export will serve you better for being simpler.

What to check if the live connection option is greyed out

If the live connection option is greyed out when you look for it, two settings on the report side are almost always the reason, and both are quick to fix. You need Build permission on the Power BI dataset, which is what lets you connect to the data rather than only view it, and the report owner needs export switched on in the report settings. It's a one-time change on the report side, and most owners simply haven't enabled it, because nobody told them anyone wanted to work this way. The video shows exactly what they need to turn on, so it's worth sending their way rather than trying to describe it.

Connecting Excel to a Power BI dataset replaces a recurring manual export with a single refresh, which is one of the lowest-effort improvements available to anyone who maintains regular reports. After years of working with Power BI teams, I find the manual export habit is usually a sign the reporting setup underneath could be working harder than it is. If that sounds like yours, you can book a Digital Ops review with me through my site, and I'll point you at the quick wins worth doing first.

Common Questions

How do I make Excel refresh automatically from Power BI?

Export from your Power BI visual using the Summarized data option with a live connection, which downloads an Excel file linked to the dataset. From then on you open that same file, go to the Data ribbon, and click Refresh to pull the latest figures.

What's the difference between summarized data and data with current layout in Power BI?

Data with current layout gives you a static snapshot that never updates on its own, while Summarized data with a live connection stays linked to the dataset and refreshes on demand. For any report you produce repeatedly, the live connection is the one that saves you the rework.

Why is the live connection export option greyed out in Power BI?

It's usually because you don't have Build permission on the dataset, or the report owner hasn't enabled export in the report settings. Both are one-time changes on the report side, so the fastest route is to ask the report owner to switch them on.

Does the video show how to keep my Excel formatting when the data refreshes?

Yes. In the video I show that the Properties button beside Refresh controls whether a refresh resizes your columns, so switching that off keeps your formatting fixed while only the data updates.