View Underlying Excel Formulas

[CGS123]

I'm trying to create a list of all the external sources of an excel file, and I'd rather not have to inspect, using naked eye only, every formula within each tab.  Is there a way to, with Alteryx,  view formulas underlying values in an excel file without advance preparation of the file?

[NicoleJohnson]

I'm not sure if there is a way to do this natively in Alteryx... however, if you take two additional steps with the actual Excel file, it will work.

1. Turn on the "Show Formulas" feature: Formulas > Formula Auditing > Show Formulas

2. Save your file as a .csv file.

Now, if you import the .csv file rather than the original Excel file, your formulas will show instead of just the results. Hope this helps! :)

NJ

[SeanAdams · Accepted solution]

Hey @CGS123,

Firstly - welcome to the community, and congratulations on your first post!

As NJ says, you can look at the formulae to see where there are absolute data points vs. formulae.

If you want to get to the external data sources - the easiest way is to write a little VBA code to enumerate through the data sources: 

https://code.adonline.id.au/list-external-data-references-excel/

Are you trying to scan a bunch of spreadsheets or only one?   If only one, then just pop some code in behind the sheet and you can export the list of external connections.   If you're trying to do this for an entire folder full of spreadsheets, then the easiest way is to do something similar using an Auto Hotkey job which would create an Excel object in memory and do the same as this above.

Let me know if this gives you a good direction towards a solution (and possibly mark this as solved if it does) - or if you still have questions possibly worth popping a sample sheet onto this thread and we can work with you on a specific solution?

Cheers

Sean