Do you need to extend your Excel table?

Tables are a great tool for storing all your tabular data, but when you want to add new data to the table you will need to extend the boundaries of the table.

This will allow you to add one or more rows to the bottom of your table so it’s obvious that you can add new data to the table.

This post will show you how to expand your Excel table and increase its range size.

Extend a Table with the Resize Handle

The easiest way to make your table bigger is to use the resize handle.

The resize handle is a small area at the very bottom right corner of the table that you can click and drag to change the size of your table.

This will allow you to expand the size by adding either rows or columns to the table depending on which direction you drag the handle.

• Place the mouse cursor over the resize handle so that the cursor turns into a two-way arrow.
• Left-click and drag either down or to the right. Dragging down will extend the table with new rows while dragging to the right will extend the table to new columns.
• Release the left mouse key.

This is a quick and easy way to expand the table by a couple of rows or columns.

Extend a Table with the Table Design Tab

There is also a resize command you can find in the Table Designs tab.

When you select any cell in a table, the Design tab will appear in the ribbon.

1. Select any cell inside your table.
2. Go to the Table Design tab.
3. Click on the Resize Table command found in the Properties section.

This will open the Resize Table menu which will allow you to select a new range for the table.

4. Edit the range in the input to the new size or use the mouse cursor to select the new range in the sheet.
5. Press the OK button to resize the table based on the new range.

The first row in your new selection must be the current column heading row of your table and the new range must also overlap the previous table range.

This will extend the table to the new range!

Extend a Table with the Tab Key

The lower right cells in the body of your tables have a special way to extend the table by adding a new row.

When you select the lower right cell in the table and press the Tab key, this will add a new row just below the table and move the active cell cursor to the leftmost cell in the new row.

If you press and hold the Tab key this will continue to move the active cell cursor to the right which eventually will get to the lower right cell and repeat the process of adding another row to the bottom of the table.

This is a handy way to extend the table with rows when the table doesn’t have too many columns.

Extend a Table with AutoExpansion

When you type anything in the row directly below a table, you will notice the table automatically expands to include this new data.

This is a very handy feature for data entry. But it will only expand the table by one row at a time.

You can take this a step further to AutoExpand the table to include any number of new rows.

1. Type something in a cell that is not adjacent to your Excel table. This way the data is not automatically absorbed into the table.
2. Select a range of around the cell you just typed in and included a number of blank cells as well.
3. Place the mouse cursor over the active range border so that the cursor turns into a four-way arrow icon.
4. Click and drag the range so the top left cell is just below the table.
5. Release the left click.

This will result in the table auto-expanding to include the new range!

Extend a Table with VBA

It is possible to automate expanding your Excel tables with VBA.

Suppose you always want to extend the table with an extra 5 rows each time. You could create a macro that increases the table range by 5 rows each time you press a button to run the code.

Press the Alt + F11 keyboard shortcut to open the visual basic editor to add the code.

Also, if you have the Developer tab enabled you can go to the Developer tab and press the Visual Basic command to open the editor.

Sub ExtendTable()
    
Dim tblName As String
Dim rng As Range
Dim tbl As ListObject
Dim addRows As Integer
Dim addCols As Integer
Dim rowCount As Integer
Dim colCount As Integer

tblName = "Orders"
addRows = 5
addCols = 0

Set tbl = ActiveSheet.ListObjects(tblName)

rowCount = tbl.Range.Rows.Count
colCount = tbl.Range.Columns.Count

tbl.Resize Range(tblName & "[#All]").Resize(rowCount + addRows, colCount + addCols)
    
End Sub

Go to the Insert menu in the visual basic editor and select the Module option. This will create a new module where you can add your VBA code.

Copy and paste the above code into the new module.

This code will get the table named Orders from the active sheet and determine how many rows and columns the table has.

It will then resize the table by adding a given number of rows and columns onto the original table dimensions.

The code is currently set to add 5 rows and 0 columns based on the addRows = 5 and addCols = 0 lines of code, but you can change these to suit your needs.

Each time you run the code, it will expand the table by the fixed number of rows and columns.

Extend a Table with Office Scripts

If you want to automate your table expansion process in Excel online, then you’re going to need to use Office Scripts for this.

Office Scripts is a JavaScript-based language that is available on both the Excel desktop and web apps.

Go to the Automate tab in the ribbon and select the New Script option to open the Office Script Code Editor.

function main(workbook: ExcelScript.Workbook) {
	var rowsAdded = 5;
	var colsAdded = 0;
	var tableName = "Orders";

	let myTable = workbook.getTable(tableName);
	let myRange = myTable.getRange();

	//Get current dimensions of table
	let rowHeight = myTable.getRowCount();
	let colWidth = myTable.getColumns().length;

	myTable.resize(myRange.getAbsoluteResizedRange(rowHeight + 1 + rowsAdded, colWidth + colsAdded));
};

Copy and paste the above code into the Code Editor to replace its contents. Then press the Save script button and your Office Script is ready to be used.

The script gets the current row and column count from the Orders table. Then it will resize the table by adding a fixed number of rows and columns.

You can customize how many rows and columns are added to the resized table by changing the var rowsAdded = 5; and var colsAdded = 0; lines of code to suit your needs.

Press the Run button in the Code Editor to execute the code and the table will expand with the given number of new rows and columns.

Conclusions

You can extend an Excel table in several ways.

The easiest method is to use the resize handle to quickly add more rows or columns to your table. Once you know this exists, it’s quite easy and convenient to use.

If you need a method that is quicker to expand the table to a very large size, then you can use the Resize Table command which allows you to type in the new range.

For automated processes where you need to expand your table, you can extend it with VBA or Office Scripts.

Whichever method you choose, extending a table is quick and easy. Do you have any tips for resizing tables Excel? Share them with me in the comments below!