Pasting Data to the Bottom of a Formatted Table

One trap to avoid

Formatted tables are a great feature in Excel. They were expanded and renamed in Excel 2007 (formerly called Lists) and their use offers many advantages.

See the links at the bottom of this post for other blog posts on Formatted Tables.

When you paste data into the next blank row below the table, then the table will expand to incorporate the new rows. There is however an instance when pasting won’t add rows to the table.

If you paste a range that is wider than the table then it will be ignored and you will have to manually modify the range to include the new rows. See images below to see how it works.

add to table example

If I copy the range E6:G6 and paste it in cell A4 then the table automatically expands.

pasted to table

If you copy the range E6:H6 (4 columns) and paste in A4 it doesn’t expand the table.

paste doesn't work

To manually add the data to the table you can drag down the small double headed arrow in the bottom right hand corner of the table – see below.

Manually extend table

Below are links to other blog posts on tables.

Format As Table Part 1

Format As Table Part 2 (incl Video)

Tip

More tips

Please note: I reserve the right to delete comments that are offensive or off-topic.