How to Add Rows and Columns in Excel (3 Methods)

To add rows or columns in Excel:

Select the number of rows or columns you want to add by dragging the mouse.
Press the CTRL and Plus (+) keys together to add new rows or columns.

Adding rows and columns in Excel is a fundamental skill that enhances worksheet organization. This basic feature is essential for inserting new information and maintaining a structured and dynamic Excel workbook.

In this Excel tutorial, we will learn to add rows and columns in Excel using keyboard shortcuts, Insert tool, and VBA.

The following image illustrates the process of adding rows or columns in Excel.

3 Ways to Add Rows and Columns in Excel

Adding rows and columns with keyboard shortcuts can save a lot of time and make the task easier. Also, you can use the Insert tool, and apply VBA to add new rows and columns.

Here are the 3 methods to add rows and columns in Excel:

Using Keyboard Shortcut

You can use keyboard shortcuts to add adjacent & non-adjacent columns or rows in Excel.

Here are 4 cases to add adjacent and non-adjacent rows and columns in Excel:

Case 1: Adding Adjacent Rows

Rows that are next to each other, sharing a common border are known as adjacent rows. For example, row 5 and 6. The number of rows you select, will be added above the selection.

To add adjacent rows, follow the steps below:

Select the row numbers by dragging the mouse.
Then, press the CTRL key and Plus key (+) from the numeric keyboard.Two rows will be added above.
To change the format, click on the Format Painter icon and select Format Same As Below.

Here are the two new rows with the formats of the below rows.

Case 2: Adding Adjacent Columns

Adding adjacent columns is the same as adding adjacent rows.

To add two columns before Column D, you can follow the steps below:

Select the columns by dragging the mouse.
Press the CTRL key and Plus key (+) from your numeric keyboard.
To change the format of the new columns, use the options of the Format Painter.

While adding non-adjacent rows and columns the difference is in the selection of row or column number, the shortcut keys are the same.

Case 3: Adding Non-Adjacent Rows

Rows that are not directly next to each other are known as non-adjacent rows. You can add non-adjacent rows by following the steps below:

Press and hold the CTRL key and click on the row numbers one by one.
Then press the CTRL and Plus key (+) together to add new rows.

As a result, two new rows will be added.

Case 4: Adding Non-Adjacent Columns

To add non-adjacent columns, follow the steps below:

Press and hold the CTRL key and click on the column names one by one.
Now, press the CTRL key and Plus key (+) together to insert columns.

Finally, two new columns will be added.

Using Insert Tool

Another way to add rows and columns in Excel is by using the Insert tool. Here are the steps to add rows:

Select the row numbers before which you want to add rows.
Go to the Home tab > Cells > Insert > Insert Sheet Rows.

To add columns using the Insert tool in Excel:

Select the column numbers before which you want to add columns.
Then, click on the Home tab > Cells > Insert > Insert Sheet Columns.

As a result, new rows and columns will be added.

Alternatively, you can use the Insert option from the context menu to add new rows or columns.

Using VBA Macro to Add Every Other Rows or Columns

Using VBA can save time and make the task of adding rows and columns automatic. It is helpful when you have to repeat the task multiple times.

You can use VBA for inserting rows and columns in Excel in the following ways:

Case 1: Inserting Every Other Row

In this part, you can learn a VBA macro to add every other row. Follow the steps below:

Select Visual Basic from the Developer tab.
Go to Insert > Module.

Write the following code in the Module:

Sub Insert_Every_Other_Rows ()
Dim mRange As Range
Dim Count_Rows As Integer
Dim i As Integer
Set mRange = Selection
Count_Rows = mRange.EntireRow.Count
For i = 1 To Count_Rows
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset (2, 0).Select
Next i
End Sub

Now, select the data range and click on the Run command.

As a result, new rows will be inserted after each row.

Case 2: Inserting Every Other Columns

To add every other column, use the following steps:

Go to the Developer tab > Visual Basic.
Select Insert > Module.

Write the following code in the Module:

Sub Insert_EveryOther_Column()
Dim colNum, c_Start, c_Finish, c_Step As Long
Dim Range_Insert As Range
c_Step = 2
c_Start = Application.Selection.Cells(1, 1).Column + 1
c_Finish = (ActiveSheet.UsedRange.SpecialCells( _ xlCellTypeLastCell).Column * 2) - c_Start Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For colNum = c_Start To c_Finish Step c_Step ActiveSheet.Cells(1, colNum).EntireColumn.Insert
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Now, select the data range and click on the Run command.

After running the code, blank columns will be added after every column.

How to Add Rows and Columns in Excel Table

You can easily add rows and columns to an Excel table because it’s the same as the previous methods. To understand how to do that properly, let’s create an Excel table using the same dataset.

Now, follow the steps below to add rows and columns in the table:

Select the row numbers or column names first.
Then click Home > Cells > Insert.
After that, select Insert Table Rows Above to add columns or select Insert Table Columns to the Left to add rows.

Here are the newly added blank rows and columns in the table.

Download Practice Workbook

You can download the practice workbook from the download button below.

Adding Rows and Columns.xlsm

Conclusion

We have covered all the possible ways to add rows and columns in Excel. You can use keyboard shortcuts, Insert tool, or VBA to insert rows or columns. If you have any suggestions or further queries. Please share them with us in the comment section below.

Frequently Asked Questions

What Is the shortcut key to add a single row or column in Excel?

The shortcut key to add a single row or column in Excel is- CTRL + ‘+’

How to insert columns or rows quickly in Excel?

After inserting a column or row, press the F4 key as many times as you require the number of columns or rows to insert.

How to remove rows and columns in Excel?

To remove rows and columns in Excel:

Select the row numbers or column name.
Now go to Home > Cells > Delete.
Then select Delete Sheet Rows to delete rows and select Delete Sheet Columns to delete columns (Or press the shortcut key CTRL + ‘-’).

<< Go Back to Rows and Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!