Output data into excel in Accounting number format

[187]

Hi Alteryx engineer, can we achieve this with reporting tool via alteryx?   we wanna output data into excel and if the number is negative, we want it to look like this, and when we click the cell , it displays original value. 

Display

Cell

[DataNath]

Hey @187, I've seen this asked about a few times within the community and as far as I'm aware it's not possible. The typical workaround to get the bracketed negatives is to do something like below. However, because of the punctuation this will lead to the field becoming a string and when you export to Excel it wouldn't show the true -x value. I'm more than happy to be shown otherwise here but if not then I'd recommend chucking it in the ideas section if it's not already there!

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas

[SPetrie · Accepted solution]

Its a bit annoying to setup, but you can do it with a template excel file with conditional formatting rules instead of the reporting tool.

First you setup your template excel file and add a new rule for the worksheet. Make sure to apply it to the range where you expect your data to be written in the final excel file output. I set this one to be all rows in columns A through J

Save your template and then you can start getting the Alteryx workflow ready.

Before you are ready to output the data, you feed the location of the template file and a final output file into the blob input and output tools.

This step creates a copy of the template and saves it as the final output so you are not changing/damaging the template file.

I do it as a branch off the main flow with a block until done so it controls processing order, so I toss a summarize in there as well so I only have one row to feed the tools.

I add in/edit a file name to save to my final excel file using a range that encompasses my data. In this instance I used A1:J50 but you can dynamically change that if you want.

Setup the output to use that new filename and to overwrite the sheet or range.

Now it will save to the final file that has our formatting already setup.

I used a few random numbers as an example.

This can be expanded to use other rules for formatting or coloring or additional sheets within the template file as well, its just needs more prep time for the template and formulas to specify sheet name and ranges.

The example workflow is attached.

[sparksun]

Here is my way:

1 Setup an excel template with your expected cell formating 

2 Output Alteryx data to the excel template with the following configuration

3 Click Run and you will see your expected result in excel: