Before you Begin

Learn how to export Oracle Analytics workbooks to Excel while keeping the formatting in the table and pivot visualizations.

Background

In Oracle Analytics, you can add formatting to table and pivot visualizations such as to change the default font, font size, displayed column names, colors, conditional formatting and date formats.

Oracle Analytics supports exporting up to 25,000 rows to Excel.

What Do You Need?

Create a Dataset Table

In this section, you add the sample_order_lines2023.xlsx to Oracle Analytics to create a dataset table.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the sample_order_lines.xlsx file, and then click Open.
  4. In Create Dataset Table from sample_order_lines2023.xlsx, click OK. Click Save Save icon.
  5. In Save Dataset As, enter sample_order_lines2023, and then click OK.


    Description of sample_order_lines2023.png follows
    Description of the illustration sample_order_lines2023.png

Create a Table Visualization

In this section, you create a table visualization and apply formatting styles.

If your table's columns appear in a different order, you can rearrange the column order in the Grammar panel's Rows group or move the columns in the visualization to match the order in the image.

  1. Click Create Workbook. In the Workbook, review the Oracle Analytics insights about the dataset, and then close the Auto Insights panel.
  2. In the Data panel, hold down the Ctrl key, and select:
    • Product Sub Category
    • Product Container
    • Ship Mode
    • Sales
    • Discount
    • Shipping Cost
    • Profit
  3. Right-click, select Pick Visualization, and then select Table visualization table icon.


    Description of product_sales_table.png follows
    Description of the illustration product_sales_table.png

Change the Table's Properties

In this section, you change the table's appearance by updating properties.

  1. Click Properties Properties icon. Click Off in the Suppress Repeating Values row.
  2. Click Edge Labels Edge Labels icon and expand Product Sub Category.
  3. In the Display Header row, click Auto, and then click Custom. In the text box, enter Product Type.
  4. Click Totals Totals icon. Click Off in the Grand Total row. In the Subtotal row, click None and click Product Sub Category.
  5. Click Values Values icon and expand Discount. In the Display As row, click Value and click Percent Of.


    Description of props_formatting.png follows
    Description of the illustration props_formatting.png

Apply Conditional Formatting

In this section, you apply conditional formatting using rules.

  1. Click Manage Conditional Formatting Rules Conditional Formatting icon in the floating visualization toolbar.
  2. In Conditional Formatting, click Visualization. In Name, enter Loss. In Column, select Profit from Visualization Columns.
  3. Select less than < from the operator List icon list and enter 1 in Target.
  4. Click Format Format icon, click Red in the color chart or enter #f7012c, and then click OK. Click Save.


    Description of formatted_product_sales_table.png follows
    Description of the illustration formatted_product_sales_table.png

Export the Table to Excel

In this section, you export the table visualization to Excel.

  1. Click Save. In Save Workbook, enter Product Sales, and then click Save.
  2. In the table, click Menu Visualization menu icon, select Export, and then select File.
  3. In File, enter a Name for the file. From Format List iconlist, select Excel (xlsx) and click Save.
  4. In Enter name of file to save to, use the file name or enter a different name for the Excel file.
  5. Double-click the Excel file to examine the exported table visualization.


    Description of excel_table.png follows
    Description of the illustration excel_table.png

Create a Pivot Visualization

In this section, you change the visualization type to a pivot visualization.

  1. In the Data panel, select Product Category, drag it to Rows in the Grammar pane to replace Product Sub Category.


    Description of replace_data_element.png follows
    Description of the illustration replace_data_element.png
  2. Click the visualization type List icon list and select Pivot Pivot icon.


    The pivot visualization shows the conditional formatting and the data element column change.

    Description of pivot_vis.png follows
    Description of the illustration pivot_vis.png
  3. Click Save Save icon.

Export the Pivot to Excel

  1. In the workbook, click Export Export icon, select File.
  2. In File, enter Profit Loss in Name as the pivot visualization file name. From Format List iconlist, select Excel (xlsx) and click Save.
  3. In Enter name of file to save to use the file name or enter a different name for the Excel file.
  4. Double-click the Excel file to examine the exported pivot visualization.


    Description of pivot_in_excel.png follows
    Description of the illustration pivot_in_excel.png

Learn More