Force the connection to be refreshed when the file is opened
If you want fresh data when you will open the Excel file next time, you will need to click on the refresh button in the ‘Data’ tab of the ribbon. This can be done automatically changing the settings of the connection in Excel :
- Open the ‘Data’ tab of the ribbon
- Click on ‘Connexions’ button
- Click on ‘Properties’ button
- Check ‘Refresh data when opening the file’
Format and document data in Excel sheets
You are totally free to setup the Excel file like :
- click inside the pivot table and select a theme in the ‘Creation’ tab of ‘Pivot table tools’
- click inside the pivot table and add graphics or segment in the ‘Insert’ tab
- add macros ad save as .xlsm
- add a Power View tab
- add comments
- lock sheets to prevent modifications
Eventually print or save a paginated report as a PDF
You may setup the print settings and export as a PDF. You will get a paginated report.
Eventually convert the pivot table into a flat table
- Duplicate the sheet containing the pivot table and display the new sheet
- Be sure all the data you need is displayed
- Select a cell within the pivot table
- In the ribbon, select the ‘Creation’ tab under ‘Pivot table tools’
- Remove totals and subtotals
- Click on ‘Report layout’ button, then select ‘Show in tabular form’ and ‘Repeat all item labels’
- Right click a cell containing + or – icon, then select ‘Expand/collapse’ / ‘Expand entire field’
- In the ribbon, select the ‘Analyze’ tab under ‘Pivot table tools’
- Click on ‘OLAP tools’ button, then select ‘Convert to formulas’
Share the document
Do not hesitate to share the Excel file on the network. Users will not be able to refresh data but it will still be dynamic.
Please consider the pivot table can be modified allowing users to view any data so locking modifications might be a good option.
Reuse the Excel file in the future with just one click
Once your Excel report is setup, you may open it at any time but it will content the last refreshed data.
If you want to refresh it, open the Excel report with Power BI Sidetools:
Create as many Excel reports as you need and store them in the Exports folder. They will appear right near the ‘Export to Excel’ menu item.
As a good practice, I suggest you create a calculated table in Power BI desktop with the appropriate filters. You will just need to display this table within Excel’s pivot table.
Hiding tables and columns within Power BI desktop will also help to setup the Excel file.
References about Export to Excel
In the service, there is a possibility to “Analyze in Excel” which creates an Excel file connected to the service :
Marco Russo from SQLBI developped an external tool to create an Excel file connected to the desktop. I reused a part of Marco’s code to create the connection between Power BI desktop and the Excel file. Thank you Marco !
You may want to look at the original version of this blog post : https://thebipower.fr/index.php/2020/10/30/easily-export-to-excel-with-power-bi-sidetools/