Forum Discussion

Copper Contributor

Mar 20, 2018

AutoFill Handle Double Click Filling Too Far

When I highlight the cells I want to autofill down and double click the autofill handle, it fills past the last row of data on the left. I have verified through =ISBLANK() and even deleting the blank...

IBCTPL_MasterMDL_15MAR2018.xlsx2.9 MB

excel

Formulas and Functions

Erin Simmons

Copper Contributor

Mar 20, 2018

I have amended the original post to include the workbook. The issue is on the Master tab in copying down the formulas in O2:S2 via highlighting and double clicking the handle in the bottom right corner to autofill down.

SergeiBaklan

Diamond Contributor

Mar 20, 2018

Hi Erin,

Your cells under the bottom of the data are not clean (you may check by Ctrl+End). I made Inquire->Clean Excess Cells Formatting, after that autofill works correctly.

Erin Simmons
Copper Contributor
Mar 21, 2018
Thanks for this. Do you know if there is any way to see in Excel what this formatting is that it is cleaning up? I am curious what is putting it there so that I can adjust as necessary so it won't be there in the first place.

Thanks,

Erin
- SergeiBaklan
  Diamond Contributor
  Mar 21, 2018
  Hi Erin,
  
  I'm not sure how to recognize that visually. It happens if you have some data, especially in formatted columns, after that remove them. More about that is here
  
  https://support.office.com/en-us/article/clean-excess-cell-formatting-on-a-worksheet-e744c248-6925-4e77-9d49-4874f7474738
  
  https://support.microsoft.com/en-gb/help/244435/how-to-reset-the-last-cell-in-excel