community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Workaround for Alteryx Workflow to not Break Excel Pivot Tables w Formatted Field Settings

Meteor

Hello Alteryx community,

 

This is for users who want to have an Excel XLSX Data Output which contains a Pivot Table that has formatted field settings such as "accounting" and "numeric". 

 

Traditionally, you would go to a metric in the Pivot Table and --> Right Click --> Field Settings and then apply your preference to format your cells. 

alteryx_excel_02.jpg

 

I saved the file, closed out and found this error message after I re-ran the Alteryx Workflow

 

alteryx_excel_03.jpg

 

When I clicked on "Yes" in the error prompt, the Pivot Table in the Excel was broken. 

 

alteryx_excel_04.jpg

 

Interestingly enough while I was sharing the issue over web-share with Zach from Alteryx Customer support, he suggested that I do the following and it worked:

  1. highlight the entire column 
  2. leverage the field settings in the ribbon
  3. apply my field field settings and save/close file

Note: Prior to this, I had to re-create the Pivot Table after I found that the Pivot Table was broken in Excel. 

 

alteryx_excel_05.jpg

 

I applied my field settings update using the "highlighting the entire column + using the field settings in the ribbon", saved the file and closed out.

 

Then I re-ran the Alteryx Workflow and the Excel Pivot Table had the field settings applied from the new approach that Zach helped me find as an alternative to my initial way of doing the field settings format. 

 

 

alteryx_excel_01.jpg

 

Problem Solved: 

 

Extra Credit: Ensure that you have the "Refresh data when opening the file" so that your pivot table has the latest and greatest data otherwise end-users will have to remember to manually refresh the pivot table. 

 

alteryx_excel_06.jpg

 

 

 

 

 

 

Bolide
Bolide

This is a great hack and I am sure many of them will benefit from this.

 

Thank You @eric_ramos 

 

Regards,

Yug

Labels