How to Update Data and Preserve Excel Report’s Formatting while using Alteryx

(和文はこちらから)

Introduction:

Often users have Alteryx for data preparation and Excel for reporting purposes. It becomes tiresome when each time the data output from Alteryx needs to be formatted in Excel for reporting purposes. This issue of formatting and reformatting can be solved automatically using a simple trick mentioned in this post. This is similar to an earlier post which explains how to auto-update powerpoint charts by collaborating them with excel.

How to Achieve it:

Let us take an example of an Excel Report which is formatted as shown below.

There can be several formatting rules to an excel report, such as table header’s background color, text colors, fonts, alignment, any conditional formatting such as the highest number to be displayed in red color etc. Often users face the challenge of preserving this excel formatting while the underlying data is being updated via Alteryx workflow.

To solve this problem, keep the excel report separate from the underlying data. Alteryx workflow will run and give the output data to another sheet in the same excel file which contains the report as shown below.

This data is then linked with the report via referencing the corresponding cells. Please refer to the formula in the image below.

Using this simple trick, it is easily possible to update the processed data via Alteryx, while retaining the report formatting as expected.

Summary:

It is possible to update the excel report’s data via Alteryx while still being able to retain the existing formatting. This blog explains how to achieve the desired results.