Dynamic Dashboard

Hi Vrinda-0902​,

I’d first check where the workbook is stored and whether the sheet passwords are just protecting the sheets from editing or encrypting the entire file.

If the workbook is stored on SharePoint or OneDrive, Power BI can refresh from it without requiring users to close it, provided the file is accessible and the changes have been saved. If it’s stored locally, you’ll need to consider a gateway for scheduled refresh.

Sheet protection generally doesn’t prevent Power BI from reading the data, as long as the workbook itself isn’t encrypted. However, if the owners are editing the workbook simultaneously, refresh will only pick up the data that has been saved.

If each sheet is maintained by a different owner, I’d consider storing the data in separate files or consolidating it into SharePoint Lists or another central data source. This can make refresh and access management easier.

You can build a Power BI dashboard connected to a multi-sheet Excel workbook without asking each sheet owner to close their sheet every time the dashboard refreshes.

The key is to determine where the workbook is stored and what “password protected” means. Excel sheet protection usually prevents editing cells; it does not encrypt the workbook's data. Power BI may still be able to read the data, depending on the protection type and connector.

Solution

- If the workbook is stored on SharePoint or OneDrive, connect Power BI to the cloud file and configure refresh.

Microsoft Learn

- If each worksheet is only sheet-protected (password required to edit cells), try connecting through Power BI Desktop.

- If the workbook is encrypted with a password to open, Power BI Service refresh may fail. Remove file-level encryption if permitted, or move the data into SharePoint Lists, Dataverse, or SQL.

Hi Vrinda-0902​ ,

Could you review the suggestion provided above and let us know if you have any additional questions, we are happy to address. 

Thanks!!