Adding rows/columns to an embedded Excel Table

If you LINK to the workbook, you can LINK to a named range in a worksheet external to the document, and you can work within Excel in such a way that the Named Range always references all the data in your table.

If you actually EMBED the worksheet, so it exists only inside your document, there is no such way to adjust the range that Word displays. To change which part of the sheet is displayed, you should not have to delete and re-insert the object (for an embedding, you would lose all the data if you tried that). You should be able to open the sheet for in-place editing, adjust the size of the "window", and display the additional rows. Although people do sometimes report that they cannot make those adjustments. Also, because an embedded object is basically a shape, it can never span more than one Word page, so a large table will cause problems.

In more detail...

For the LINK approach, if you are not familiar with range names, you can define a range name in Excel using Formulas->Define Name. Although Excel will automatically extend the range when you add rows, it will only do so if you add them in certain ways (e.g. select the row below the data and use Insert). 

In Word, how to link to the range name depends on whether or not the link is inserted using a LINK field. For example, for a link that just links to a workbook without specifying a range of cells in any way, when you use Alt-F9 in your document, you will see a field that looks something like this:


{ LINK Excel.Sheet.12 "C:\\the path name to the folder\\Book1.xlsx" "" \a \f 0 \p }

That does not even specify a sheet, but what it tends to do is link to the sheet selected at the point when you saved the file.

If you want to link to a specific range, you need to change the "", so e.g.

  - if you want to link to Sheet1, A1:C5, you can modify the field to be


{ LINK Excel.Sheet.12 "C:\\the path name to the folder\\Book1.xlsx" "Sheet1!R1C1:R5C3" \a \f 0 \p }


  - if you created a range name called myrange1 that "covers" A1:C5, you can use


{ LINK Excel.Sheet.12 "C:\\the path name to the folder\\Book1.xlsx" "myrange1" \a \f 0 \p }


(There can be a little bit of complication there because there are "worksheet" and "workbook" range names, so you may find that you have to use this instead:


{ LINK Excel.Sheet.12 "C:\\the path name to the folder\\Book1.xlsx" "Sheet1!myrange1" \a \f 0 \p }

)

Then select the field and press F9 to update it. Again, to insert a table that spans more than one Word page, you will need to use a LINK that specifies a text format such as plain text, HTML or RTF (usually my preferred option) rather than the "Worksheet object" format or any of the image formats. You can use the "switches" at the end of the field to change that - to an extent. e.g. use \a \r to insert RTF.

If you inserted the LINK by paste linking a floating object/image, then you will not see a LINK field and in fact there isn't one. In that case, you should be able to modify the "LINK target" by selecting the object/image, right-clicking, then selecting Linked Worksheet Object->Links...

You will then need to select the correct link to modify, because Word does not select the correct one for you automatically. But when you have done that, 

 - click Change source... 

 - click the Item... button, and type in the expression that defines the range (e.g. Sheet1!R1C1:R5C3, myrange1 or Sheet1!myrange1). Word does not give you any help at this point - no lists of sheets, names, and no check that the name is valid or that Word will work with it.

 - select the name of the workbook in the dialog. (I do this after defining the item because Word sometimes loses the name of the workbook if you select it before defining the Item).

 - click Open to modify the link definition. 

Word still will not attempt to verify the link. To do that, you have to press Update Now. Or you can click OK, and Word should update the link (whether it is set to be manual or automatic).

Either way, if Word cannot find the link target, it sets the Item to "" and updates the result, without any error message, so you may or may not notice that it has not changed the link.

(There are other ways to work with these links).

Finally, Word does not work with all the types of range name that Excel recognises. I do not know all the rules, but
 a. the range name has to resolve to the address of a block cells. It cannot resolve to (say) a constant value such as "2" or "abc"
 b. If you create a table in Excel, Excel creates a range name for that table and adjusts its value as the table grows and shrinks. It also has special syntax For example, if you have a Table called Table1 you can reference the entire table using Table1[#All]. Word probably will not work with the range name Table1 directly (I have occasionally seen cases where it seems to, but in other cases it just doesn't). Word certainly will not work with the range expression "Table1[#All]". But if you create a range name called myrange2 and set it to =Table1[#All], Word probably will work with that.