Excel – Table data entry – How to always have a blank row at the end of an Excel table

data validationmicrosoft excelmicrosoft-excel-2007

I'm creating a simple Excel spreadsheet for table data entry (table created using the "Format as table" option — couple of columns, there could be hundred of rows). I would like to always have the last row of the table blank and ready for data entry (otherwise I don't see the dropdowns). I am pretty sure I have seen that in the past, but I can't replicate. Any idea how I could do that? Ideally without VBA.

Best Answer

It's often the simplest method to extend the size of your table in advance for as many rows as you think you'll need. Sorting and formulas will continue to work as expected.

  • Select your table
  • In the Table Tools/Design menu, click Resize Table and specify a longer range.

Alternatively, the manual way is to select the last row in your table and do "Insert row below" (or use the little arrow in the bottom right cell). This could be automated using VBA if you're so inclined, although I usually find extending the table does the job just as well and makes it easier for the end-user to deal with.