Empty Spreadsheet is very large

[thehalfboy]

Hey all, I hope someone can advise on a problem I'm seeing with a spreadsheet. We have data validation lists set up in two cells, but other than that and a row of headers, the file is completely empty. However the file size is large, and if I perform "go to last cell" it drops me right down to the bottom of the spreadsheet, so it seems that excel thinks there is data where it should be blank. I've tried highlighting all cells and hitting delete, and trying "Clear Contents" but neither seems to have an effect. Does anybody have any clues how I can fix this?

 

[Joe4]

Welcome to the Board!

Did you happen to apply the Data Validation to the entire column (instead of just selected cells)?
Or did you apply any other formatting to an entire column?

 

[thehalfboy]

Thank you, I think the validation might have been applied to every cell in the column, but even if I remove the data validation completely, I can't clear out the "empty" data.

 

[Joe4]

Thank you, I think the validation might have been applied to every cell in the column, but even if I remove the data validation completely, I can't clear out the "empty" data.

Try deleting those columns entirely.
Then re-save the file (you may need to do this step for it to "reset" the last cell).
Then hit CTRL+END, and see which cell it selects.

Does it go down to the last row on the sheet?
If so, what cell address does it go to?

Make sure that you do not have any formatting or formulas in all cells in any of the other columns.

 

[thehalfboy]

I deleted out every column we'd set up entirely, so there should be nothing there at all, and CTRL + END takes me to H1048576.

If having the data validation down a full column has caused this, how would you set up a form for inexperienced users to use, so that you can limit their options for filling in some cells? If it's not applied to the entire column, won't there come a point where the user can go back to free text?

 

[Joe4]

I deleted out every column we'd set up entirely, so there should be nothing there at all, and CTRL + END takes me to H1048576.

I don't know what you did, but it may be best to start fresh in a new workbook.

If having the data validation down a full column has caused this, how would you set up a form for inexperienced users to use, so that you can limit their options for filling in some cells? If it's not applied to the entire column, won't there come a point where the user can go back to free text?

Here is how I would approach it.
Determine the maximum number of rows that may be used (could be 100, could be 1000, whatever - just pick some number that they will never hit).
Then apply the Data Validation down to that row.

Then, use Cell Lock and Cell Protection to lock all rows under that, so they are unable to enter any data below that row.
By default, all the cells are locked. So you will just want to unlock the ones in your editable range, and then apply Sheet protection (locking the cells does nothing until the sheet is protected).
See: How to lock cells in Excel

 

[thehalfboy]

Ohhhh that's a nice work around! Thank you for your help!

 

[Joe4]

You are welcome.
Glad I was able to help!