Retain Excel Formula After Running The Excel File Through Alteryx Workflow

[#1]

One of the biggest Issue that the Alteryx Community Users face is that when we provide a Excel File as an input and Run it through a Workflow in Alteryx, and save the output into a new Excel File, then the existing Formulas which were present in the cells of the Original Excel File are LOST/ NOT RETAINED and only the Values are copied to the New Excel File. 

So Here I have created a Workflow which will help you to understand how you use the XML Properties of the Excel File and retain the Existing Formulas in your newly created Excel File. 

Spoiler

Here I have used a Demo Input File but I have provide Documentation for each step which give you guidance to Tweak the workflow as per your input data and needs.

Open to Feedback.

Do Like & Share if this Content was helpful.

Regards.

[DawnDuong]

Thank you, this is cool!

[allwynthomas24]

Hey @DawnDuong,

Congratulations on being the Ace User - 2022.

Thank You Very Much for your Kind Words & checking out the Workflow. I hope it's useful for you and other Users using Alteryx. Feel free to edit the workflow as per your data requirements.

If you found the above workflow useful in anyway then do support by sharing it with your peers.

Thanks Once Again.
Have a Great Week up ahead.

Best Regard,
Allwyn Thomas

[saiirangam]

This is Interesting, thank you for posting this 

[allwynthomas24]

I appreciate that you found the workflow useful. Thanks@saiirangam for sharing your views. Regards

[NadiaFS]

This is great! Does it also work for XLSM format's?

[allwynthomas24]

Hello @NadiaFS,

Glad to know that you found it useful.

I have not tested the workflow for Excel Macro Files. But it would be interesting to try it out. 

Kindly modify the workflow as per your requirements and do comment if it worked on your Excel Macro Workbooks and how it solved your Business Problem.

Regards,

Allwyn Thomas 

[NadiaFS]

Hi @allwynthomas24 

Are you able to please share your input file? I am not sure I fully understand the XML Parse tool yet. I tried to recreate it, but I think I am missing something. 

Do you know if it is possible to keep formula already existing in an input/Original Excel Workbook? I have many reports that Sum specific rows for Assets, Liabilities, Equity etc. I have some Columns with Sum's but many Rows with Sum's. Any help would be much appreciated!

Aidan

[allwynthomas24]

Hello @NadiaFS & Aidan,

The input file that I used in my solution workflow is available in the Package itself. So when you extract the Alteryx Package, the input Excel File will be visible/accessible in the Extracted Folder.

If possible, kindly send your Excel File or a part of the excel file.
You can connect with me in Alteryx Private Messages.

Regards.

[Anne-Lise]

Dear Thomas!

Thank you for your workflow which is a great help. when I've tried, I'm still confronted with the problem that my formulat in the XML appears for the range ref "M2:M11". So when using the workflow, it takes the formula for the first row, but not the other ones.

RecordID c_13_OuterXML Excel                                                                                                                                           Formula
1            <c r="M2" s="13"><f t="shared" ref="M2:M11" ca="1" si="0">DATEDIF(L2,TODAY(),&quot;y&quot;)</f><v>43</v></c>    DATEDIF(L2,TODAY(),&quot;y&quot;)
2            <c r="M3" s="13"><f t="shared" ca="1" si="0"/><v>52</v></c>
3            <c r="M4" s="13"><f t="shared" ca="1" si="0"/><v>41</v></c>
4            <c r="M5" s="13"><f t="shared" ca="1" si="0"/><v>59</v></c>
5            <c r="M6" s="13"><f t="shared" ca="1" si="0"/><v>26</v></c>
6            <c r="M7" s="13"><f t="shared" ca="1" si="0"/><v>57</v></c>
7            <c r="M8" s="13"><f t="shared" ca="1" si="0"/><v>56</v></c>
8            <c r="M9" s="13"><f t="shared" ca="1" si="0"/><v>31</v></c>
9            <c r="M10" s="13"><f t="shared" ca="1" si="0"/><v>43</v></c>
10          <c r="M11" s="13"><f t="shared" ca="1" si="0"/><v>42</v></c>
11          <c r="M12" s="17"><f ca="1">SUBTOTAL(9,M2:M11)</f><v>450</v></c> SUBTOTAL(9,M2:M11)

How can I copy the found formula to my range M2:M11?

Thank you very much for your help!