Output alteryx formulas as excel comments?

As far as I'm aware Alteryx can't write excel comments to specific cells, but I'm wondering if there's a functionality that helps excel users debug alteryx excel outputs, by letting alteryx show the formulas used for each cell in excel (without having to output them in separate columns/sheets. The first thing that came to my mind was cell comments, but maybe there's another way to achieve this?

Thanks in advance

Workflow

Accepted answers

TSP

@AkisM

I’d say do you really need the formulas there as you’ve described ?

Can you consider writing your data to a separate sheet, and using queries/pivots/formulas referencing your data sheet to produce your calculations in a fresh sheet. You may find your formulas are more manageable in a separate location which won’t require Alteryx to produce.

if you’re able to share a sample worksheet it would be easier to demonstrate there.

TSP

All comments

AngelosPachis

Hi @AkisM ,

I suggest you have a look at the following two posts and try to reverse engineer them.

The first one, inputs the raw formulas from an excel spreadsheet to Alteryx (you probably want to do the opposite)

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Alteryx-Excel-Import-Is-there-a-way-to-bring-through-the/td-p/758460

The second one reads the comments from different cells in Alteryx (again, you would have to manipulate the xml in Alteryx to output the formulae as comments in your Excel spreadsheet)

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/comparing-column-headers-and-notes-in-header-cells-to-see-if-two/m-p/758040/highlight/true#M182762

I will have a closer look and try to help myself besides providing the links as soon as I can.

Cheers,

Angelos

AkisM

Thanks for your response @AngelosPachis , I read those links and they're interesting but they don't exactly provide what I'm looking for.

TSP

@AkisM could you post a sample of what you're trying to achieve?

Perhaps a made-up excel output and a sample workflow.

Thanks,

TSP

AkisM

Hi @TSP , all I'm trying to do is to output Excel formulas rather than just flat values. So that when the user mouses over a certain cell in the excel produced by Alteryx they can see the underlying formula instead of a plain value.

TSP

So most simplistically are you looking for Alteryx to output to cell A1 as follows:

=Sum(B1:D1)

AkisM

@TSP Exactly

TSP

There are two ways I’m interpreting your question:

1) you want to output formulas from alteryx into Excel - as I suggest in my post above. Meaning you’ll need to modify your workflow to be in a format [=] + [formula]

2) you want to have alteryx write its formula into excel. Meaning you want to take your current workflow and output a different presentation.

I can’t think of a way to achieve option 2. In alteryx, each tool has input(s) and output(s). Whilst the activity in a tool equates to formulas, the data going in and out is flat values.

If you’re able to narrow down which option you’re desiring that might help

TSP

I see you’re leaning towards my option a. If you could share a sample of what input, existing workflow and desired output you’d like to achieve, the community will be able to help.
without a sample I can just say that it’s possible to output an [=] + [formula] presentation.

AkisM

@TSP I'm looking for option 1. Unfortunately it doesn't look like it is as simple as just outputting "=A1+B1". I tried that in a workflow, and the resulting excel stasys stuck at showing the text value =A1+B1 instead of doing the formula. In addition the numbers are stored as text instead of numbers so even if the formula worked it would throw formatting errors in excel. And if I try to convert the columns to numbers, that doesn't work either because it also gets applied to "=A1+B1" which is clearly text, not a number.

TSP

Instead of beginning your formula with = , begin it with \

then within excel do a find and replace for \ to =.

Ensure your numeric fields are set to a numeric type within alteryx prior to exporting

TSP

Here's how I got it to work.

This method will work with a single find & replace, providing you use a character that doesn't appear elsewhere within your data (e.g '\').

Hope you find this helpful.

TSP

@AkisM @Did this solution work for you? If so please consider marking it to help others.

TSP

AkisM

Hi @TSP , thanks for your time, that looks like a potentially helpful bandaid fix but I am looking for something that does not require the user editing anything in the produced file

TSP

@AkisM
The above approach would work if you wrote a VBA script to do the find and replace upon opening the excel file. look on StackOverflow etc for guidance on that.

in reality you’re asking the impossible from alteryx in isolation. Your solution will require a workaround in excel too. Alteryx outputs flat values which is how excel will interpret the data without any prompts.

AkisM

@TSP But even in the find replace method that you provide, there is another issue. You said "ensure all numeric fields are set as numeric before exporting with alteryx"

What happens in the case where column A has numeric values, but at the last row of column A i need a formula displaying the sum? Alteryx will convert that cell to NULL because "/SUM(A1:A5)" is not a numeric value. So there will be nothing to use find/replace on, and no formula.

TSP

@AkisM

I’d say do you really need the formulas there as you’ve described ?

if you’re able to share a sample worksheet it would be easier to demonstrate there.

TSP

AkisM

@TSP I have indeed considered that, but because the nature of the data is dynamic (rows will be moving up and down, so the formulas that are referencing specific cells might end up picking up wrong values when that data is moved around) it gets complicated. In any case, since you ruled out the possibility of alteryx showing formulas, I guess i have to examine other routes.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703