Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Conditional Formatting and Applying to Multiple Excel Tabs

JWuWuTang
5 - Atom

Hi everyone,

 

I had a question regarding applying conditional formatting to an Excel file to multiple tabs and how to set that up in Alteryx. Currently, my process involves creating the rules in Excel, and then having to format paint each tab, but because I have so many tabs, it becomes very tedious. I figured that there must be a solution to do this in Alteryx, but because I am pretty new to Alteryx, wanted to see if I could find some help. 

 

I have attached two sample files and some screenshots, one of how I do my conditional formatting in Excel, and one sample file that I want to use as my Alteryx input. I've looked at some other posts regarding conditional formatting, but am having some trouble applying the Table tool and Column/Row rules, and then don't know how to apply it to each tab of my file.

 

Conditional Formatting Rules

JWuWuTang_3-1585694847362.png

 

JWuWuTang_1-1585694632061.pngJWuWuTang_2-1585694642829.png

 

 

5 REPLIES 5
AbhilashR
15 - Aurora
15 - Aurora

Hi @JWuWuTang,

 

@CharlieS has great solution for conditional formatting for excel outputs in the URL below, which might serve as a good starting point for what you are looking to achieve: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Conditional-formatting-in-output-file/...

 

Give it a try and let us know if you run into challenges.

JWuWuTang
5 - Atom

Hi @AbhilashR,

 

Thank you for pointing me to that thread. So this is the input and output I get after leveraging that workflow:

Input:

JWuWuTang_0-1585766685758.png

Output:

JWuWuTang_1-1585766699749.png

 
 
 

How do I make is so that if Column B is peaches, but Column A is not peaches, I would like to highlight it yellow? If both Column A and B are peaches, then I would like to highlight it green. Also, right now in my workflow, I have it set so that it filters in column B for "Peaches", but how do I make that a user-inputted section. I have also attached my workflow for your reference.

 

 

AbhilashR
15 - Aurora
15 - Aurora

Hi @JWuWuTang, I put all the calculation in one formula. Is this what you are looking for?

IF [A] = 'Peaches' AND [B] = 'Peaches'
	THEN 'background-color:rgb(206,255,173)'
ELSEIF [A] != 'Peaches' AND [B] = 'Peaches'
	THEN 'background-color:rgb(255,255,0)'
ELSE Null()
ENDIF

 

JWuWuTang
5 - Atom

Hi @AbhilashR, That formula looks great, thank you! I've modified it in my workflow to fit the dataset I will be using. I had another question, if I am having the user input a file which is already formatted, and I want my outputted file with conditional formatting to retain the same formatting elements of that original file (font, font size, table borders, etc.), is there a way to do so?

AbhilashR
15 - Aurora
15 - Aurora

Hi @JWuWuTang, glad it worked out! If I understand your question, you are reading and writing back to the same file? If yes, make sure you have a block until done tool between the input and output tools. In the output tool, you can specify the specific cells or a specific tab you want to write back to. This way you retain the pre-existing formatting.

 

In the attached example I write back to a named range in excel, you can certainly specify cell ranges. Please mark this post as solution accepted if it answered your questions.

Labels