Colouring specific cells in certain rows

[Mairtt]

Hi there,

I want to colour cells green for greater than zero and red for less than zero. I know how to do this using the table tool but in this example I only want to colour in the movement row. Also I have 50+ columns so i don't want to have to do it one by one, is there any way to do this quickly?

Thank you

[bbak91]

The approach would be to use a column rule instead of a row rule in a table tool. Alternatively, you can format an Excel spreadsheet with conditional formatting and have an output data tool write to with the option to preserve formatting. That way you could control the formatting via Excel rather than Alteryx and possibly do all of the columns more efficiently. 

[apathetichell]

Oh -  I just saw you are doing this for 50 columns. There are ways to do this in a batch macro. If you are stumbling post some data and I can try to get you an example.

[DawnDuong]

Thanks @bbak91 . Until I stumbled upon this did not know that there is a way to output data to excel and still preserve the formatting! 

[Mairtt]

Hi Brandon, thanks for the response, could you please elaborate on the column rule?  How would you set up the rule to only highlight one row in each column?

On your other point, I have calculated the movement row using alteryx so there is no excel file yet where this exists, so i assume the conditional formatting would not work for this?

Thank you

[Mairtt]

Thanks for your response  @apathetichell. I have attached a small example of the data in question, any help you can provide is appreciated. Thank you

[apathetichell]

you'll need to work with the formatting to get the table sizes of the legend key to match with the other table entries but this seems fairly close to what you wanted...

 

the problem with using batch macros is that you start seeing every workflow as needing at least one batch macro....

Also note as discussed by @bbak91 - column rule is the key, since you can use the column rule to measure that the forecast variable includes "movement" and also measure the value of the specific column. I had to transpose and set everything up. I initially thought I could dynamically change fields in the table tool but that wasn't working so I went back to a tried and true transpose and filter method.