Why are cell formats changing when outputting to Excel in a formatted template?

[Sunceray]

I am outputting columns of data that each contain segments of 9 populated cells and and 2 blank cells in order to ouput the entire column at once. When outputting columns of data to the formatted Excel template, the last of the 2 cells that are empty or NULL within each group is copying the format from the previous cell. (I am only outputting the columns with # for the time being).

[Dhrish]

@Sunceray can you share the desired output. As from the pic it looks like its correct.

[KGT]

Alteryx will only output a column as one type, as that's how data tables work in the non-excel world. In this case, you are replacing the Null() cells, and so they will be changing to the format of the column.

I don't think you would be able to output 2 different formats in the same column. If this is possible with outputting to named ranges, then I apologise, but on the surface, I can't see how it would work. It would need to be outputting all the tables to their ranges, rather than outputting all rows at once to a range.

[Vinod28]

Put something in those "blank" cells
It sounds silly, but just putting an empty string ("") or even a single space (" ") in those cells makes Excel stop applying formatting from above.

[Vinod28]

IF ISNULL([MyField]) THEN " " ELSE [MyField] ENDIF

[Vinod28 · Accepted solution]

If you're using a template, go ahead and format the entire output range before Alteryx writes to it. That way, Excel doesn’t try to get clever with formatting when new data comes in.

[Vinod28]

If you're running this regularly and formatting still gets weird, you could throw in a small Excel macro to clean things up after the data’s written. Most people don’t need this, but it's there if you're picky about formatting.

[Vinod28]

Solved: Keep Formatting in Excel Output - Alteryx Community