Avatar of Steve Monk
Steve Monk
Flag for United States of America asked on

Excel Table - Adding Rows when First Column has Data Validation

Excel Table Support.
I have created a Table in an excel worksheet where users will be expected to add new rows manually. Note that this document will be a shared document on Sharepoint - so I cannot use any VBA etc.

I have created a number of other tables in the workbook that act as Data Validation lists. The data validation etc is working fine.

However - this is my problem. The user only has to enter two pieces of information in the table - and both pieces of data are entered into the first two cells/columns of the table. Normally this works perfectly - you just go to the first row under the table and begin typing - and when you tab to the next cell - the table automatically expands to add the row etc.
However, the first column (and second column) are both controlled by a Data Validation list.

The issue is - if you click on the cell below the table - the data validation is not available yet (as the table has not expanded) - so the person cannot "Select" from the list. If they attempt to type something - if they get it wrong and tab to the next column then they will of course get an error - because the table will expand and the cell now contains validation - so they either have to retry or cancel - both of which "remove" the new table row added - so the validation goes away - so they cannot select from the drop list (as it no longer exists on the cell).
So I am kind of between a rock and a hard place. I want to make it as simple as possible - basically being able to select from the right values in a new row - but they are not going to do the whole "click on the bottom row of the table, right click and select insert, then select "Insert table row below" - thats just not going to happen.

Anyone have any bright ideas on how I can allow selection or table row creation "easily" when you have data validation in the first column?
ACTMicrosoft SharePointVBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Steve Monk

8/22/2022 - Mon
Professor J

Can you ask users before typing pressing the TAB key so that the row is inserted before they type?
Steve Monk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steve Monk

ProfessorJimJam - unfortunately Tab will not work - using tab in the first row below the table does not seem to do anything (i.e. it does not expand the table). So thank you for the suggestion - but it does not work., though it was a helpful suggestion to try.
Professor J


Glad you found your solution by yourself.    Yes, the Tab works only if the active cell is the last cell on the table.  but another keyboard shortcut to insert a new row,  is Control and Shift and +
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rob Henson

Tab from the last table cell to create a new row is a Word feature not Excel.

Pre-populating the cells below the table will include them in the Used Range of the sheet and will as a consequence increase the file size. For a couple of thousand rows shouldn't be too much of an issue but it depends on how wide the table is; might only be 2000 rows but if your table is 6 columns wide, that's 12000 cells that excel is storing.

If you look at the bottom right of the table, the last cell will have a triangle filling the bottom right corner; you can use this to drag the table down before entering in first column. The user could do this as a one off for a dozen or more rows at the start of entering some data and then drag it back up when finished to take table back up to the last entry.
Rob Henson

Another option, would be to use the right click menu in the cell below the table. There is an option in the right click menu to "Pick from drop-down list". This shows a list of all previous entries from the column above, if all of these are from the Data Validation then the pick list will effectively replicate the Data Validation, at least for those items that have been used previously.

To get all items from the Data Validation, you could copy/move the Validation list above the header row of the table with no blank rows between the last entry in the list and the header. Then hide the validation list rows. The right click will then show all of the Data Validation options. It will also include the header but I assume your users would recognise not to use that??
Rob Henson

Thought of another option, include a cell containing just a nominal value eg "aaa" in the Data Validation list. The user can then just enter that value in the cell below the table and press enter. It will be recognised as valid when the table expands; the user can then use the drop-down to select a proper value. I have deliberately suggested "aaa" so that when the Validation selection list is selected the entry already populated will be selected and if that is at the top of the list the user can then scroll down rather than having to think about having to scroll up or down for the entry they require.

Keyboard shortcut which might help users is Alt+Down Arrow.

If Data Validation is in place it will activate the Validation drop-down, if not it will activate the right click, select from List option.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Professor J

Rob,   Your comment
Tab from the last table cell to create a new row is a Word feature not Excel.

It works in Excel.  please see attached file and the animated gif.
Rob Henson

Apologies, Tab didn't do that for me in the file I created to test the options. I was using Excel 2016; I did have another big file open as well that was causing some calculation delays so maybe that stopped it.
Steve Monk

Thanks all for those who commented and provided suggestions - the items you provided were certainly helpful.
Fortunately my file is "relatively" small - so adding rows under the table with the same validation I think will do it for now. The table only has 6 columns.
I could not use the suggestion to add all the possible values in hidden rows at the start of the table as the validation list is actually a dynamic list that grows (user firsts enters values on a different worksheet that automatically is added to the validation list - then they have to select the value in the other worksheet). Also the result will be used in at pivot table - so could not include hidden rows even if that were feasible.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.