Excel Automation

General information

The Excel Automation brick allows users to automate the process of populating Excel files with data and retrieving output from Excel calculations. This feature streamlines the interaction with Excel files, enhancing efficiency by reducing manual data entry and extraction.


Brick Locations

Bricks Featured → Excel Automation

Brick Parameters

  • Excel file
    • A file that should be recalculated. Supported formats: xlsx, xls.
  • Input cells
    • In this section, users can define the input cells in the Excel file that will be populated with data. Users can specify the sheet name, input cells, input connector, and the corresponding column in the input connector. This setup ensures that the correct data is sent to the specified cells in the Excel file.
    • Sheet Name: Dropdown to select the specific sheet within the Excel file.
    • Input Cells: The cell references in the Excel sheet where data will be entered. The range should be the same as the selected input data
    • Input Data: Dropdown to select the data source.
    • Input Data Column: Dropdown to match the column from the data source to the input cell. This field is not mandatory. If you are leaving the value empty, the full dataset will be used as input.
  • Output cells
    • This area allows users to specify the cells from which data will be retrieved after the Excel file processes it.
    • Sheet Name: Dropdown to select the specific sheet within the Excel file.
    • Output Cells: The cell range in the Excel sheet from where data will be extracted.
    • Output Connector: Field to define the name of the output connector for the extracted data.

Brick Inputs/Outputs

  • Inputs
    • Brick takes at least one dataset
  • Outputs
    • Brick produces datasets


Excel automation brick does not support:
  • Pivot table
  • VBA
  • Charts

Example of usage

To illustrate the potential usage of the Excel Automation in Datrics, let’s go through the example of the Revenue calculation for the SaaS product.
We have the business model described in xlsx file.
notion image
Input values in the model are
  • Clients: total users, active users, %, paying users,%, growth YoY,%, YoT retention
  • and Cost - Package prices and CAC
Depending on the changing input variables expected revenue will vary.
Let’s create the Data app in Datrics to support the calculation of the various scenarios of the Business model for the SaaS company.

Creating the pipeline

Here’s the illustration of the full pipeline, let’s go step by step to create it.
notion image

1. Defining Input Values

Using API input brick to create the dataset with all of the variables of the model.
notion image
For convenience, we are separating the dataset into two:
  • Price - one column with all the package prices,
  • Clients - variables representing all the other variables.
We may do that using Filter columns brick.
notion image

2. Configure Excel Automation Brick

  1. Upload Excel File: Begin by uploading the Excel file you wish to automate.
  1. Configure Inputs: Set up the input cells with the appropriate data connections and cell references.
    1. We are mapping the cell address in Excel to the input dataset columns.
      notion image
  1. Configure Outputs: Define the output cells where the automated results will be collected.
    1. notion image
  1. Apply Configuration: Once the input and output cells are configured, click 'Apply' to set the automation in motion.
  1. Save Configuration: Save the current configuration to be used in future automation tasks.
Run the pipeline to see the calculated revenue.

3. Visualize the Data

To visualize the output dataset we need to transpose the table using Transpose Data brick.
Visualize the revenue via Charts brick with simple line chart and add to the pipeline API Output brick to use the output data in the data application.
notion image
Now we are ready to run the business model in various scenarios changing the values in the input!

4. Build Data Application

Last step to present the results and share with the team - create the data application for our business model.
  1. Deploy the pipeline. We do not need to set the schedule.
    1. notion image
  1. Create the Data App out of the deployed pipeline
  1. Select the widgets to be displayed - pipeline trigger, chart and output data.
    1. notion image
  1. Publish the App and share with the team!
notion image