How to Preserve Layout and Formatting in Power BI Excel Exports

The finance team already has a way of working. Invoice numbers belong in the first column, amounts need two decimal places, discounts appear as percentages, and the review sheet contains calculations they trust. They want this month’s figures in that workbook—not another file to tidy up before the meeting.

PBRS can export selected Power BI visual data into Excel, apply supported formatting and totals, and place the data into a chosen worksheet in an existing template. It can then repeat that preparation and delivery on schedule. The value is the time your colleague gets back each month.

Decide what needs to survive

“Keep the formatting” can mean several things. One reader wants the same matrix arrangement seen in Power BI. Another wants a designed statement. Your finance colleague wants a workbook they can sort and calculate with. Those needs call for different export routes.

If the colleague changes a discount, should the net amount recalculate? If so, the workbook needs Excel formulas or other workbook logic. Exporting a calculated report value does not automatically create the formula that produced it.

Agree on a small example before configuring the schedule. An invoice amount of 1,250.50 and a discount of 1.23% gives a rounded discount of 15.38 and a net amount of 1,235.12. Check both the displayed figures and what happens when the input changes.

PBRS works with published reports in Power BI Service, Power BI Report Server and SSRS. It does not export directly from Power BI Desktop. The visual-data formatting and template controls discussed here apply to the supported Power BI Service Excel Data Only workflow; other report types have their own export options.

Start with the output that matches the task. Preserving a report’s appearance and preparing a working spreadsheet are related goals, but they are not interchangeable.

Microsoft’s visual export guide explains the native choices and restrictions. Summarized data follows the visual’s aggregation; underlying data requests the available supporting detail. It remains subject to model, visual, permission and export limits.

Use paginated exports when the designed layout matters

A paginated report gives you control over the report design before it is exported. Its Excel renderer maps that design into worksheet cells and can use merged cells to reproduce the arrangement. This can be useful for a statement, but less convenient for someone who wants a simple sortable table.

Microsoft also confirms that report expressions are exported as evaluated values, not Excel formulas. If the recipient must change a discount and recalculate the result, use workbook calculations in a tested template instead. See the paginated Excel rendering guide for layout limitations.

PBRS automates the chosen export and delivery. It does not make every export format a pixel-perfect reproduction of a Power BI page, and it does not turn report calculations into editable spreadsheet formulas automatically.

For the invoice workbook, begin with the four inputs the team needs: InvoiceId, InvoiceDate, Amount and DiscountRate. Keep any workbook calculations in clearly designated cells or on a separate review sheet.

Make the columns readable and useful

In PBRS’s supported Excel Data Only settings, choose the titled visual and the required data mode. Configure column headings, data types and custom format strings. Preserve invoice identifiers as text when their exact characters matter; use numeric amounts, date values and percentage formats for the appropriate columns.

For example, 0.0123 should display as 1.23% when a percentage format is applied. Changing the appearance should not change the intended underlying value. Test leading zeros, negative amounts, blank dates and unknown discounts as well as ordinary records.

PBRS also provides styling for headers, alternating rows and totals, including font weight and colours. Reusable styles help several scheduled workbooks follow the same conventions. A colleague should be able to spot the headings and totals without having to apply the team’s formatting every month.

Add row and column totals deliberately

PBRS can add row totals and column totals, with controls to select the rows or columns to summarize. Use a row total when the values across that row belong together—for example, comparable monthly amounts. Use a column total when the amounts down the column should be added.

In the invoice file, the Amount column may need a total. Invoice IDs and discount percentages should not simply be added. If the source already contains subtotals, check that the selected calculation does not count the same amounts twice.

Test a small result whose total you know, then a larger result. A column added to the visual can move the intended totals range, so review those settings when the report layout changes.

Use the workbook the team already recognizes

Choose the existing Excel template, select the worksheet, and specify a starting row and column or the supported next-available placement option. This lets PBRS put new report data into a familiar workbook rather than forcing the reader to rebuild its structure.

Keep the export range away from formulas and reserved content. A useful design is a data sheet for exported inputs and a review sheet that references them. If formulas sit beside the incoming data, verify header placement and the space required for every exported column.

Do not assume that a template with four prepared rows automatically expands its formulas for forty records. Test the largest expected result and extend the workbook design where needed. Also distinguish a genuinely zero discount from an unknown discount; the calculation should follow the business rule your colleague expects.

Bring related outputs together

PBRS can place multiple supported visual exports into separate worksheets or a single worksheet. For reports that belong in one pack, a package can merge supported Excel outputs into one workbook. Give the tabs clear names and a sensible order so the reader can move from the summary to the detail.

Use the available password-protection options when required and test the final delivered file. A protected worksheet and a password needed to open the workbook serve different purposes. If the pack is merged, verify protection on the merged result rather than assuming the settings on an intermediate file are sufficient.

The PBRS Excel settings guide explains the controls by output type. Once the finished workbook passes the test, save those choices with the schedule and delivery destination.

If an application manages the reporting job, keep the template, formatting and placement settings with its PBRS configuration. The visual-data settings belong inside the configured destination, alongside the Excel Data Only output format.

The example below shows a template-placement entry for a visual called Invoice inputs. It is part of a schedule definition, not a complete API request. It deliberately uses a separate input worksheet so the workbook’s review calculations can be designed around that area.

{
  "PageNumber": 0,
  "VisualTitle": "Invoice inputs",
  "ExportType": 0,
  "WorksheetName": "Inputs",
  "OrderNumber": 0,
  "UseExcelTemplate": true,
  "TemplateExcelFile": "C:\\PBRS\\Templates\\InvoiceReview.xlsx",
  "UseNextAvailableColumn": false,
  "UseNextAvailableRow": false,
  "SpecificColumn": "A",
  "SpecificRow": "5",
  "ExcelColumnFormats": []
}

After execution, inspect the workbook as well as the API result. Confirm the selected visual, worksheet names, cell types, totals and formula behaviour. Keep the tested template version with the job so a later change has a clear starting point for investigation.

Next month, your colleague should be able to open the workbook, recognize the layout and begin the review. The dates and amounts are readable, the appropriate totals are already there, and the template’s calculations behave as expected.

That is where PBRS adds value: the preparation becomes part of the recurring job. Your team maintains a tested export instead of repeating the same formatting, copying and file assembly each month.

Explore PBRS Excel scheduling and the wider Power BI Report Automation workflow. Use the evaluation guide below to test your own report and template with both a typical month and a larger result.

Download the PBRS Internal Evaluation Guide