Stop dragging creating false values

Question

I am using drag formula to copy multiple forumlae across many columns. The formulae are only in some columns, others are blank. The reason is that the file is then saved in CSV format so the blank cells retain the correct positions.

However when I drag those columns with blank cells Excel put the words "Column1", "Column2" etc in the first row of the blank cell columns. This is wrong as the resulting CSV file has invalid values in those positions.

How can I drag without the words "Column1", "Column2" etc appearing i.e. just blank so that the CSV shows:-

Valid Value,,,Valid Value,Valid Value,,,, etc

Answer 1

It does contain a table (I mentioned this in your other thread) starting in row 6. If you don't want a table there, right click say A6, and choose Table - Convert to range from the menu.

Answer 2

https://1drv.ms/u/s!AjOlZpcBKY49jKIkWjp0YAm9jgiiCA?e=aXBf4T

THis is a link to the zipped in my OneDrive.

Is this any help?

Answer 3

Hello AlanFarthing,

Unfortunately, I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight into your issue.

Thanks Shalom

Answer 4

Thank you for your reply but I cannot get either of those methods to work.

I also see that I cannot delete the words Column1, Column2; as I delete them they return. If I use Clear Contents, or delete key or back space key..

I also tried entering "" but that just enters "" with a number for each "" column e.g. ""2, ""3

Any more advice please?

Answer 5

Hello AlanFarthing,

I'm Shalom and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

I understand that you want to drag a formula without inserting column names in the blank cells. This is a known issue that occurs when you drag a formula across multiple columns that have no data in the first row. Excel automatically fills the first row of the blank columns with the column names, such as Column1, Column2, etc. This can cause problems when you save the file as CSV, as the column names will appear as invalid values.

There are some possible solutions that you can try to avoid this issue. One solution is to insert a blank row above the first row, and then drag the formula across the columns. This way, Excel will not fill the blank cells with the column names, and you can delete the blank row later. Another solution is to use the Fill command instead of dragging the formula. You can do this by following these steps:

Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Excel will fill the selected cells with the formula without inserting the column names.

I hope this helps you to drag a formula without affecting the blank cells. If you need more help, please feel free to ask me.

Best Regards, Shalom