We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Dynamically Update Reporting from Excel Spreadsheets

MattD
Alteryx Alumni (Retired)
Created

When you’re frequently writing and rewriting data to Excel spreadsheets that you use for Excel graphs and charts, it can quickly become a hassle to make and remake your reporting objects to keep them up-to-date so you’re visualizing the most recent data. A best practice to keep the hassle out of the process exists, though! If you keep your plots isolated to their own spreadsheet, referencing cell values in another sheet used to capture your data, you can simply overwrite the source data sheet and your plots will update automatically upon launching Excel. In the example below (attached in the v10.6 workflow Dynamically Update Reporting from Excel Spreadsheets.yxzp) we’ve included the workaround to make your Excel outputs seamless:

Step 1: Write Data

Step 11.jpg

Step 12.jpg

Step 2: Create Plots

Step 2.jpg

Step 3: Update Data with the “Overwrite Sheet (Drop)” Output Option in the Output Data Tool

Step 31.jpg

Step 32.jpg

Step 4: Your Plots are Updated!

Step 4.jpg

Be sure to have your Excel file closed so that you can rewrite to the file without receiving the error below!

Error.jpg

“Unable to open file for write:”

See the process in full below:

Dynamically Update Reporting from Excel Spreadsheets.jpg

Attachments
Comments
lrygiel
7 - Meteor

Great idea. Thanks

leahpenelope
7 - Meteor

Hello,

This method works great on an existing excel document whose data within is simply overwritten and it’s chart dynamically updated.


What if my excel output and filename are datetime sensitive I.e. an output is churned every day and an excel chart needs to be generated within the excel output file based on the data within?

 

in addition, I would also need to output other sheets to the same file?

 

Is there anyway to achieve these?


thank you!