Preserve Formatting overwrites not all rows?

[GoldenDesign04]

I have several workflows where I want to use the preserve formatting but I find a limitation in it that seems to be quite maddening.

With the current output setup: 

Overwrite Sheet or Range

With Preserve formatting on

The range is Sheet1$A1:P1000

I have only a header row lightly formatted and a single column with a data validation dropdown

When a new run is completed, all the formatting remains as advertised. Yet, if there is more rows from previous than the newest run, it only somewhat removes the old rows. Leading to confusion among those that need to work the file after it generates.

Example output:

As circled, the rows 22 and on are from previous. The report does remove the "Issue Date" column, but none of the others. 

On Version 2020.1.5 of Designer with a server/designer update coming later this month to 2020.3

Hopefully some of you know a fix

[T_Willins · Accepted solution]

Hi @GoldenDesign04,

I have run into this issue as well.  When Alteryx is writing to Excel using a range, it will only overwrite the cells it has data for, not for all cells in the specified range.  In your case, it looks like your workflow output only had about 20 rows, which would only override rows 1-20, not rows 1-1000.  If you want to override all rows in this range, you need to make sure your output has 1000 rows.  There are several ways to do this depending on your workflow; let me know if you need assistance with this.

[pedrodrfaria · Accepted solution]

Hi @GoldenDesign04 

As @T_Willins explained, it is a limitation to the configurations when outputting to a range. 

Use the the RecordID and Generate Rows tools, to make sure your dataset you want to output will cover the whole range you want to use in the excel output.

I attached a workflow to show you how you can do this.

Pedro.