Excel Table Doesn’t Expand For New Data

This week, while working on a client’s Excel project, I ran into some trouble with Excel tables, while adding new data. Instead of expanding to include the rows, the table just ignored them.

Fortunately, the problem is easy to fix, if you know how, and if you have the patience to do lots of clicking.

NOTE: A table will not expand on a protected worksheet, even if the cells below the table are unprotected.

Excel table does not expand for new data http://www.contextures.com/xlExcelTable01.html#expand

New Data Ignored

Tables are a great feature in newer versions of Excel. They make it easy to add, sort, and filter your data. Usually, tables behave quite nicely, unless, of course, you try to add a Custom View to the workbook, or do something with a group of sheets that contain tables.

The file contains a UserForm for data entry, similar to the one shown below, that you can download from my Contextures site. When you click the “Add this part” button, the new record is added at the end of the existing rows.

userformdataentry01

Everything worked well on my computer, so I sent the file to my client, for testing. Unfortunately, things didn’t go well on his computer.

The data overflowed the table, instead of it expanding automatically. As a result, the new data didn’t sort with the other items, and wasn’t included in the drop down list, based on one of columns.

tableoptionsac05

After hearing about the problem, I tried the code on a couple of other computers, and in different versions of Excel. I couldn’t reproduce the problem, so there had to be a setting on my client’s computer that was causing the problem.

Fix the Problem

After exploring a few possibilities, I finally figured out that the problem was caused by an AutoCorrect setting. Who even remembers that Excel has AutoCorrect settings?

I added code to the UserForm, to change those settings, but you can make the same changes manually. However, the settings are buried 5 clicks deep, so pack a lunch for the journey!

NOTE: There is lots of room on the Ribbon’s Table Tools Design tab – maybe these settings could be added there, in a future version of Excel. In the meantime, you could add the AutoCorrect Options command to one of the tabs, so it’s easier to access.

For example, put it on the Review tab, with the other Proofing tools.

tableoptionsac07

Change the AutoCorrect Settings

NOTE: Changing these settings will affect all workbooks that you open in Excel, on your computer.

To fix the tables, so they automatically expand to include new rows or columns, follow these steps:

  1. At the left end of the Ribbon, click the File tab
  2. Then click Options
  3. In the Excel Options window, at the left, click Proofing
  4. In the AutoCorrect options section, click AutoCorrect Optionstableoptionsac02
  5. Click the AutoFormat As You Type tab – you’ve finally reached the settings!
  6. Add check marks to “Include new rows and columns in table” and “Fill formulas in tables to create calculated columns”tableoptionsac03
  7. Click OK, twice, to return to Excel

Other Problems to Check

[Updated] Thanks for your comments, describing other problems that prevented a table from expanding. If changing the AutoCorrect settings doesn’t fix the problem, try one of the solutions suggested in the comments. And remember, a table won’t expand if the sheet is protected.

For example:

José Carvalho: try converting the Table to Range (right click on the Table) and then back to Table (same procedure, but select all data first)

Alex: cause was actually some bad data in the cells *below* the table.  The fix for me was to highlight the rows below the table and delete them

Meadd: someone added a totals row to the table, then hid the totals row from view. It appeared my entries were the next row for the table, however since it was below the hidden totals row, the table did not expand.

Doug (sent by email): had a column filtered, which prevented expanding the table

Video: Excel Table Does Not Expand Automatically

To see the steps for setting up an Excel table, and checking the AutoCorrect options so it expands automatically, please watch this short video.

Download the Sample File

To experiment with Excel tables, and to get the code to change the AutoCorrect settings programmatically, please go to the Excel Table page on my Contextures website.

_____________

Excel table does not expand for new data http://www.contextures.com/xlExcelTable01.html#expand

Save

You may also like...

27 Responses

  1. Patrick M says:

    Nice detective work! I’d never heard of this setting before. Indeed, in Excel 2010 it’s not even in the Ribbon–you have to go into the Options area to get to it.

    It would seem like a nice thing to put on the Table tab, but the AutoCorrect would be more of a global setting and not an attribute of the selected table, right?

    • @Patrick M, no wonder — that setting is very well hidden!
      And you’re right, the AutoCorrect command is better on the Review tab, because it can be used for other settings.

      For the Table tab, I’d like to see just those two table-related check boxes. Then we wouldn’t have to dig into AutoCorrect.

  2. MF says:

    one quicker way to go to that setting is to use Menu key on keyboard.
    Press Menu key –> Additional actions
    There we should see the AutoCorrect

  3. BG says:

    I ran into a similar problem with a table that would not expand when I would tab forward from the last active cell, and found it was due to having a protected worksheet. Even if the protection setting allows adding new rows, when I reach the last table row in the last column and tab forward, the active cell leaves the table. Do you know of a work-around for this, without turning off protection?>

  4. Eleanor says:

    I have been trying to use access for entering and automating my grocery receipts, but I was not having much luck. Now after I saw your tip on using table in excel I realized that excel could be used. Is there a template you have that will record date, store, item, quantity and amount so I can use it as my grocery shopping list in the future.

    Your assistance would be greatly appreciated.

    Best Regards,
    Eleanor

  5. JLJ says:

    This is great, but what if all the auto correct boxes are already checked and the form still will not auto expand? Does protecting the sheet have anything to do with the table not auto expanding? It seems to work fine then when I protect it and copy it for my client the table will no longer auto expand and all his autocorrect boxes are checked.

  6. dennis says:

    I have tried this but the table still does not expand.
    I have 2 tables on the same worksheet and the other table expands when adding new data

  7. Erik says:

    I have the same problem, a table does not expand when adding data when the sheet is protected even though the cells of the table are unlocked and the protection of the sheet allows unlocked cells to be selected.
    Even when all the items in sheet protection are ticked the table still does not expand.

    I have checked the setting as suggested but they are already ticked by default.

    Any help would be very much appreciated.

  8. Tony Sutcliffe says:

    Thank you for a very useful tip. I couldn’t work out why a table expanded for me but not for my client. This answered the question and solved problem. I was even able to include an Auto Open macro to ensure the setting is correct.

    Note that sheets with a Table have to be left unprotected.

  9. José Carvalho says:

    For those reporting that tables do not expand even when all settings are correct, try converting the Table to Range (right click on the Table) and then back to Table (same procedure, but select all data first). Note however that formulas will not expand on columns with more than one formula.

  10. indzara says:

    I have enabled the auto-correct settings. On a protected sheet, tables are not expanding with new rows. I have some calculated columns in the table and I would like the user to not be able to edit them by accident. But protecting the sheet removes the auto-expand feature of the table. Please let me know if there is a solution to this. Thank you.

  11. Alex says:

    Just a further note on this. I had similar symptoms and the cause was actually some bad data in the cells *below* the table. There was nothing visible to the user but there was something there preventing the auto expansion of the table. The issue was only present in one Excel workbook, others would expand fine. The fix for me was to highlight the rows below the table and delete them, the table then expanded as it should.

  12. Meadd says:

    I ran into a problem on my table not auto-expanding. The problem was that someone added a totals row to the table, then hid the totals row from view. It appeared my entries were the next row for the table, however since it was below the hidden totals row, the table did not expand.
    Nothing too complicated, but one other thing to check if you’re running into problems.

  13. Justin says:

    I have a workbook with four worksheets that are essentially copies of each other. On each worksheet is a table which I summarize with a pivot table below it. On three of the worksheets, I can paste in data just fine, but one of the worksheets will not allow me because it will go into the pivot table. Each worksheet has only one blank line between the table and the pivot table. I remember seeing somewhere a setting for autoexpanding the particular table, but I cannot find it anywhere, and I cannot get into any properties on the table itself.

  14. Maarten says:

    I found a fix too (did not read all answers, but wanted to share my solution). In the design tab you can select the table name in the top left. So after clicking on that table name, you can expand the section of the table manually. I found this after if saw the first solution of changing the Auto correct options was not relevant for me.

    • Maarten says:

      Please note that you have to select an active table header to be able to see the table design tab. Also click on the resize table option, below the table name in the top left. (@moderator: Please embed in this comment.)

  15. Pawel says:

    I had the same issue – AutoCorrection was set fine – In my case I had some cells filles with a symbol like ‘ which is why auto expand didn’t work. I removed all symbols in any cells below the table and it worked fine again

  16. Nanthjan says:

    Thanks so much…

  17. Russ says:

    The autoexpand stopped working on one particular computer. The same file works fine on other computers. The computer has the correct AutoCorrection settings and all sheets are unprotected. The application was working before on the computer, and the user is not aware of any change.

  18. Gadi says:

    Exactly the problem I was facing. Thank you for the very clear explanation Debra.

    One workaround to overcome the table not expanding in a protected sheet is to use a macro to add a new row at the bottom of the table to accommodate a new row input. I found this to be an efficient workaround if you wish to keep the sheet protected and allow the table to expand for new input.

    Sub InsertTableRow()

    ActiveSheet.Unprotect

    With Range(“MyTable”)
    .ListObject.ListRows.Add AlwaysInsert:=True
    .Cells(.Rows.Count + 1, 1).Select
    End With

    ActiveSheet.Protect

    End Sub

    The macro selects the first cell in the newly added table row, but this is of course optional.
    Read in VBA help the difference in the property AlwaysInsert True vs. False and choose whichever suits you best.

  19. Roz says:

    I also had this issue. I was pasting more cells than there were columns in the original table. (You can test this without vba too). When you do this, a new row does not appear. (for example your table has 3 columns, you paste 4 cells in the row below the table)

  20. K says:

    For me, I had to go to View/Freeze Panes and select Unfreeze Panes. (I originally had the panes frozen vertically and horizontally by selecting D2, just below the header, and selecting Freeze Panes.)

Leave a Reply

Your email address will not be published. Required fields are marked *