When exporting to Excel, Displayr will generate an .xlsx file containing any tabular data or charts from the pages that you have chosen to export. Alternatively, you can also export tables or visualizations to Excel as charts. This article covers the following:
- Method - Exporting a document to Excel
- Method - Customizing the Export
- Method - Exporting tables or visualizations to Excel as charts
- Method - Canceling the export
- Additional Details
This article describes how to export a Displayr output...
into Excel:
If you'd like to update an existing Excel document, please see How to Update an Excel Document from Displayr.
Requirements
- A Displayr document with at least one output to export.
- Items to be exported must not be hidden.
- You cannot select individual items from a Page to export, the export will export all unhidden items from the Page. In this case, you might consider using Copy > Copy Data or Duplicate the output as an output off a Page directly in the Report tree.
Method - Exporting a document to Excel
- In the Report tree, select the Pages or items created directly in the Report tree off a Page to export.
- Select Share > Export Report > Excel.
-
Choose which pages to export:
- Export selected will only export your selection.
- Export All to send all un-hidden outputs in the entire Report to Excel.
- Click Export
Method - Customizing the Export
Before you click Export above, you can further customize how you want the items exported using the Advanced Options menu at the bottom of the window. This includes how to arrange items exported within the file and styling. See below for details of each option.
-
Choose how to export pages/outputs:
- Create one worksheet for each page/output
- Combine all pages/outputs onto a single worksheet
- Create one worksheet for each folder
-
Choose how tables are styled in Excel: this allows you to export them exactly as they appear in Displayr, export them unstyled, or specify one of the default Microsoft table styles. To change the table style select the desired format from the dropdown menu list. The names in the menu will match the names in Excel.
- Choose the spacing between tables on the same Excel worksheet: specify the Number of rows between the bottom of one table and the top of the next, or the Number of rows between the tops of the table (fixed table spacing). To specify the spacing, select the preferred option and input the desired number of rows.
-
Miscellaneous options:
- Ignore NET and SUM: choose to prevent NET and SUM rows/columns from being included in the export.
- Include table of contents: choose whether or not Displayr generates an additional Excel sheet at the beginning of the Excel export, which provides links to the different tables within the export. Click the links in the table of contents to jump to the desired table within the Excel file.
- Repeat headings: if your export contains tables with the column headers for each table, you can select this option to repeat the headings, or you may choose to suppress the headers on subsequent tables by deselecting this option.
- Include significance arrows (when shown in the table): choose whether or not the blue/red significance testing arrows are included in the Excel document.
- Show column comparisons in separate row (when shown in the table): if you are using the Compare Columns option for significance testing. If selected the significance testing letters will be added as new rows in the Excel output. If unchecked, the letters will be added as text within the main cells of the table.
Click Export once you've made all of your formatting selections.
Method - Exporting tables or visualizations to Excel as charts
By default, all tables or visualizations in Displayr will export to Excel as tables. To export them as charts instead, do the following:
- Select the tables or visualizations you want to export to Excel as charts.
- Go to the object inspector and go to Appearance > Excel Export.
- There, change the Format menu to Table With Chart.
- In the Excel Chart Type menu that appears, select the chart type you want to use.
- If you select Match Displayr, Displayr automatically selects a chart type that best matches the table's data.
- OPTIONAL: If you click Set as Default, that will set the default Excel exporting method for all outputs (tables, visualizations, and calculations) in the document.
Charts exported to Excel using this method can also be updated if the underlying values change. See How to Update an Excel Document from Displayr to learn more.
Method - Canceling the export
Sometimes you may need to cancel your export. Many times the export works so fast it will be finished by the time you realize, but for larger and more complex reports, you can cancel the export by clicking on the spinning circle by the document name at the top of the window. This will expand out to show you what is being processed and you will see a trash can next to the Exporting line which you can click to cancel the export.
Additional Details
All tables in Displayr have both a Name and a Label field.
When exporting to Excel, the Label field will appear in the header and footer of the table:
They will also be used in the table of contents if the table of contents option is selected when exporting.
Please note that when exporting tables to Excel, it controls the wrapping of column text in tables in the exported file. This means that there are cases where what you see in Displayr may not completely match what you see in the export.
Next
How to Update an Excel Document from Displayr