Maintaining Conditional Formatting even after Rendering to Excel File

[hlee03]

Hi,

I want to create a conditional formatting for a column to be exported into an excel file. In the example attached, I use a table tool to create the row rule. I've also included the screenshot below:

 

I then use a render tool to export it into an excel file. This does the trick in terms of exporting an excel file with the conditional formatting set in the table tool.

HOWEVER, I ideally want excel to keep the conditional formatting rule. 

For example, in the sample output, I want Excel to still color the row red anytime I enter A into the row.

I've searched the community but couldn't find an answer.

Is this something that's possible using Alteryx?

Thanks

[ddiesel · Accepted solution]

Hi @hlee03!

I don't believe you can push your conditional formatting logic from Alteryx to Excel, but let me know if you find a way!

In case this helps, here's my personal favorite "hack" for controlling formatting in an Excel workbook. I wrote about it here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Data-format-in-output-as-quot-Accounti...

First, I create an Excel template that has a hidden "data" sheet. The main sheet holds all the formatting. Each cell on the formatted sheet contains a formula that points to the corresponding value on the hidden data sheet. More details and an example in the post.

Let us know what you find out.

Thanks,

Deb

[hlee03]

Hi,

Thanks for the response! This is a neat trick and will be helpful.

However, I'm running into a roadblock.

It's my understanding that there's 2 sheets, one visible and one invisible. The invisible sheet is overwritten using alteryx to populate the rows. The visible sheet then points to the invisible sheet to populate with the conditional formatting of choice.

However, the challenge I'm seeing is that when a user needs to update the values, they cannot update the visible sheet without breaking the conditional statements as that is done in the invisible sheet. This means that anytime they have to change values, they will have to manually unhide the hidden sheet, change the values, then rehide it.

Is there a work around for this?

Attached is the test excel file I'm working with to reference the "Hide" sheet from the "Test1" sheet. The Hide sheet will eventually become invisible.

[ddiesel]

Hi again @hlee03!

Back to basics here for a moment... is there any reason why you cannot set the conditional formatting in the excel workbook? If you overwrite an existing sheet to a named ranged, any conditional formatting that you apply in Excel should stick.

There is conditional formatting in the attached sample workbook to highlight any values over 200. If you change the values in the text input, the conditional formatting follows to the overwritten workbook.

Let us know if an approach like this might work for you.

Thanks,
Deb

[hlee03]

It looks like the issue was that I was using a dynamic range to reference the hidden sheet which led it to break if the hidden sheet was not populated. 

Thanks for the solution!