Using Alteryx to Multiple Excel Sheets in the Same Workbook

To write to multiple Excel Worksheets in the same Workbook with Alteryx, use the Block Until Done Tool, Control Containers, or other Tools. Often, adding multiple output tools leads to errors because the file is in use. To prevent this, we need to tell Alteryx the sequence for writing multiple sheets to a workbook.

Writing to multiple Excel Files in Alteryx can be done in several ways, but requires sequencing outputs and thinking through the end results

Alteryx is an incredibly powerful tool for accounting, finance, and other professionals who spend a lot of time working in Microsoft Excel. A common request is to output multiple parts of the same, or even multiple workflows into a single Excel Workbook but run into errors when trying to do so. Alteryx can perform the task with several methods, including the use of the Block Until Done Tool, Control Containers, or additional plugins. We’ll explain the methods and provide some tips and best practices when doing so.

Let’s jump in!

How to write to Excel Files with Alteryx

To write a file from an Alteryx workflow to Microsoft Excel, add an Output Data Tool from the In/Out section of the Alteryx Ribbon to the end of your workflow. Click on “Setup a Connection” even though you are writing a file. In the following dialog box, select “Files” then “Microsoft Excel .xlsx” define a sheet and file name and click save.

The result will be an Alteryx Output Tool that is connected to your workflow, and the file path will be shown on the top of the Configuration Panel to the left, when the Output Date Tool is selected.

Example of how to output to Excel using an Alteryx Data Output Tool

When Alteryx runs, a new .xlsx file will be generated. You can also generate .xls files, the older version of Excel files but it is not recommended as Alteryx works better with the modern .xlsx files used by modern versions of Microsoft Excel.

Defining the Output Sheet Name in Alteryx

When writing to an Excel File with Alteryx, pay special attention to the name of the file being output in the configuration panel. It will be shown as: \Filename.xlsx|||Sheet1. There are two parts to the output, the first is the filename ending with the file extension .xlsx for Excel. Three Pipes ||| separate the end of the file name from the sheet name.

This is important because if you want to define a specific sheet title to output to, you will need to update the part after .xslx||| to the new sheet name, such as \Filename.xlsx|||Sheet2

Automatically Writing to Multiple Excel Worksheets

Alteryx can automatically generate new worksheets and tabs when writing to an Excel file based on the content of the data using the Take File / Table Name From Field settings in the Data Output Tool. First configure the tool to write to a .xlsx file, and the options will become available at the bottom of the configuration panel.

Configuring a data output tool to write to multiple tabs of the same excel file

When configuring Alteryx to automatically output data to multiple tabs of an Excel file, it takes data from one large table, then you define which column for it to group data by. Then there are several options available for how the new Excel files are generated.

You can either generate new unique files for each change in value, or category that the data is being separated by or you can output to a single file.

These are the output options available:

  • Append Suffix to File/Table Name – Generates separate files with each in category added to the end of each file name.
  • Prepend Prefix to File/Table Name – Generates separate files with each in category added to the start of each file name.
  • Change File/Table Name – Generates a single file with multiple tabs. A tab for each category name.
  • Change Entire File Path – Generates new files or tabs based on a column in your data set. Usually combined with a formula to create a filename.xlsx|||sheet1 style text field. Files are generated based on each value of filename and sheet name combination.

The following configuration shows an Output Data Tool configured to output to a single Excel Workbook, and generate a new sheet for each change in Category.

Setting the column to group data by when writing to multiple worksheets of Excel

Our categories shown below, would result in a new tab for Chocolate Chip, Oatmeal Raisin, Sugar, Peanut Butter etc. with each set of records filtered and grouped onto the appropriate tab of the excel worksheet.

Example data table in Alteryx about to be written to Excel

These are what the new tabs will look like in the resulting Excel workbook.

Example of the newly generated worksheets in a single excel workbook based on a single Alteryx input

Note: If you try this method and it doesn’t work, or it seems like only the last sheet was written, double check the configuration settings. If your Output Options are set to Overwrite File instead of create sheet or overwrite sheet, the previously written tabs will be erased and only one tab will exist.

Setting the Output Options in Alteryx Data Output Tool to Overwrite Sheets instead of Files

Output options for Excel files are controlled on the Configuration Panel under Output Options dropdown menu.

The Processes Cannot Access the File Because it is Being Used Errors

A common Error when writing to multiple Excel Sheets in the same workbook is “The process cannot access the file because it is being used by another process” This occurs because Alteryx attempts to write multiple tabs to the same workbook at the same time. Use a Block Until Done Tool or Control Containers to avoid the error.

It will look like the following in a workflow:

Example of a file in use by another process error when outputting to two tabs of the same excel workbook in Alteryx

The workflow results will return an error such as the following:

A process cannot access the file because it is in use by another process error in Alteryx

This method of writing to multiple worksheets of an Excel workbook typically occurs when you need more control over writing to a workbook, or perhaps your outputs come from two different views of the same data rather than using the Output Data Tool to group and separate data into tabs.

The Alteryx Block Until Done Tool

The Alteryx Block Until Done Tool from the Developer section of the Alteryx Toolbar sequences how workflows run. It has one input, and 3 outputs. The tool will complete the 1 output prior to starting the 2 output, and then the 3 output. Connect a second Block Until Done Output to the 3 output for additional sequencing.

Here’s an example of using a Block Until Done Tool to write multiple tabs to the same Excel Workbook without receiving an error message.

How to use a Block Until Done Tool in Alteryx to write to multiple tabs of the same Excel file

And the following is an example of sequencing 4 different outputs using multiple Block Until Done Tools chained together.

Writing more than 3 outputs using multiple block until done tools chained together

It’s not uncommon to add a Block Until Done Tool earlier in a workflow, or to add various Filter Tools or Formula Tools after the Block Until Done Tool instead of placing them immediately prior to an Output Data Tool.

Alteryx Control Containers for Workflow Sequencing

In Alteryx Designer 2023.1 and newer, Control Containers can be used to sequence the execution order of a workflow. They allow you more granular control of execution than what’s available with a Block Until Done Tool and the data stream does not have to have a single point of continuity.

Control Containers are available in the Developer Section of the Alteryx Toolbar. Drag and Drop them into your workflow, then drag and drop tools or multiple tools into the container.

Each container has a small input and output anchor at the top of the control container next to the container title.

How to configure and use Control Containers to sequence Alteryx Workflow Outputs to avoid errors

The example above uses control containers to write multiple outputs to the same excel workbook when there are different input files. This would often cause you to combine the data into a single stream, add a block until done tool and then change the filter prior to output.

With a control container, you can have multiple sections of workflow that act independent of each other. Then connect the containers for the output sequence with the output of one container becoming the input of another container.

Additional Options Using Alteryx Crew Macros

We wanted to at least mention that there are additional tools available using one the most popular, and most useful tool packs for Alteryx available, Crew Macros. They are not officially supported by Alteryx, but they are widely used within the Alteryx community and are worth checking out. Tools include, Parallel Block Until done and Wait a Second which allows you some additional sequencing options or the ability to build delays into workflow execution, Chaos Reigns Within: Alteryx CReW Macros.

Conclusion

To write to multiple worksheets of the same Excel file, you can utilize the native options in the Data Output Tool, Block Until Done Tools, or Control Containers. Each one has its own use case. Configuring the Data Output Tool is the easiest but gives you the least amount of flexibility. The Block Until Done Tool is simple for sequencing outputs but can be more difficult determining where to place it when working with large workflows. When data isn’t in a single stream or figuring out where to add a Block Until Done Tool is confusing, Control Containers can take their place but are only available in newer versions of Alteryx.

Scroll to Top