How to Add Rows and Columns in Excel (4 Easy Methods)

Suppose you have made a report, but before submitting it you noticed that you still need to add more essential data in some rows and columns within the dataset. Here comes the necessity of inserting rows and columns.

No worries, we are here to rescue you from that kind of situation. This article illustrates 4 easy ways how to add rows and columns in Excel including shortcuts, commands and VBA. Here’s an overview of inserting rows and columns.

How to Add Rows and Columns in Excel: 4 Easy Methods

We have the following dataset to highlight the methods. It contains a salary report of some employees of a company.

1. Add Adjacent & Non-Adjacent Rows and Columns Using Keyboard Shortcut

First, we’ll learn how to apply keyboard shortcuts to add adjacent & non-adjacent columns or rows.

1.1 Add Adjacent Rows and Columns

Now let’s see how to add adjacent rows and columns.

Adjacent Rows

We’ll add rows before row number 5 and 6. The number of rows you select that number of rows will be added above.

• First, select the rows by dragging with the left click of your mouse on the row numbers or press and hold the CTRL key and click on the row numbers.
• Then press together the CTRL key and the Plus key (+) from your numeric keyboard.

Note: Use a numeric keyboard for applying shortcut keys.

• Now see, it added two rows above and took the formats of the above row by default. To change it, click on the appeared Format Painter icon and select Format Same As Below.

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

Adjacent Columns

Adding adjacent columns is the same as adding adjacent rows. Here, we’ll add two columns before Column D.

• Select the columns by dragging with the left click of your mouse on the column names or press and hold the CTRL key and click on the column name.
• Next, press together the CTRL key and the Plus key (+) from your numeric keyboard.

Two columns are now added, like before you can also change the format of the new columns based on the left or right column by selecting the options after clicking the Format painter icon.

1.2 Add Non-Adjacent Rows and Columns

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

Non-Adjacent Rows

We’ll add rows before rows 5 and 8.

• Press and hold the CTRL key and click on the row numbers one by one.
• Then just press the CTRL+ keys simultaneously.

Note: Use a numeric keyboard for applying shortcut keys.

Now see, two rows are added before the selected rows.

Non-Adjacent Columns

Like non-adjacent rows, we’ll select the columns in the same way. We’ll add columns before Columns C and E.

• Press and hold the CTRL key and click on the column names one by one.
• Finally, just press the CTRL+ keys together.

Here’s the output.

2. Use Insert Tool to Add Rows and Columns

Another way to add rows and columns in Excel is by using the Insert tool from the Home ribbon. You need to follow the steps below to see how to do that.

• To insert rows, select the row numbers before which you want to add rows and then click as follows: Home > Cells > Insert > Insert Sheet Rows.

• To add columns, select the column names and click as follows: Home > Cells > Insert > Insert Sheet Columns.

• Or you can select the Insert option from the context menu after right-clicking on the selected rows numbers or column names.

Here is the output after adding rows and columns.

Similar Readings

• How to Switch Rows and Columns in Excel
• [Fixed!] Rows and Columns Are Both Numbers in Excel
• Excel VBA: Set Range by Row and Column Number
• How to Convert Multiple Rows to Columns in Excel

3. Add Rows and Columns in Excel Table

We 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. Then follow the steps below.

• Select the row numbers or column names first, then click as follows: Home > Cells > Insert.
• Then to add rows select Insert Table Rows Above and to add columns, select Insert Table Columns to the Left.

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

• Another advantage of Excel Table is, if you type something into the adjacent rows or columns it will add a row or column automatically. Here, we inserted a new ID in Cell B12 and a new heading- ‘Remarks’ in Cell F4, and look, the Table added a new row and column.

• But if you paste something adjacent to the table that is not in the same size of the Table, then it won’t be added to the table automatically. You will have to add them manually by resizing the table size.

• To add them to the table, select any cell from the table and then press Resize Table from the Table Design ribbon.

• A dialog box will open up to set the new range. Select the new range and press OK.

The table is now adjusted with the new range.

4. Apply VBA Macro to Insert Every Other Rows and Columns

When you are gonna add rows or columns after every row or column in your dataset then it’s very time-consuming for a long dataset to add them using shortcuts or commands. In this type of case, VBA is the best option.

4.1 Inserting Every Other Rows

First, we’ll see how to add every other row with a VBA macro.

• Select Visual Basic from the Developer ribbon.

• After appearing in the VBA window, insert a new module by clicking: Insert > Module.

• Later, write the following codes 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

• Finally, select the data range and run the codes.

Here’s the desired output.

4.2 Inserting Every Other Columns

Now we’ll apply another VBA macro to add every other column.

• Open the VBA window and insert a new module by following the same process from the previous section.

• Then insert the following codes-

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

• Next, select the data range and run the codes.

Blank rows are added after every column in the dataset.

How to Remove Rows and Columns in Excel

Removing rows and columns is quite easy in Excel. Here we’ll learn how to do it.

• Select the row numbers or column names, then click as follows: Home > Cells > Delete.
• Then select the Delete Sheet Rows to delete rows and select Delete Sheet Columns to delete columns.

• Or, you can right-click your mouse on the selected rows or columns and select the Delete option from the context menu. Or press the shortcut key- CTRL –.

The blank rows and columns are gone now.

How to Insert Cell in Excel

Sometimes we need to insert just cells instead of rows or columns. The process is pretty much the same, let’s learn it here.

1. Inserting Single Cell

First, we’ll learn to insert a single cell.

• Select the cell before which you want to add a cell. We selected Cell E11.
• Then click as follows: Home > Cells > Insert > Insert Cells.

• Also, you can do it by right-clicking on the cell. Select the Insert option from the context menu.

• A dialog box will appear to choose where Excel will shift the existing cell. We chose Shift cells down.

• A blank cell is now inserted there.

2. Inserting Multiple Cells

The process is the same for inserting multiple cells.

• Select the cell range where you want to add blank cells.
• Then again click as follows: Home > Cells > Insert > Insert Cells.

• Choose the option to move existing cells and press OK.

Here’s the output.

Things to Remember

• Always remember that rows are added above and columns are added to the left of the selected field.
• You need to select the table first to access the Resize Table.
• You must enter or paste data only to the cells adjacent to the table.
• Use a numeric keyboard for applying shortcut keys.

Frequently Asked Questions

1. What Are the Shortcut Keys to Add a Single Row or Column in Excel?

The shortcut key is- CTRL +

2. How to Insert Multiple Columns or Rows Quickly?

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

Download Practice Workbook

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

Conclusion

Now you know how to add rows and columns in excel in different ways. Interestingly, you can delete rows and columns in similar ways. Using CTRL and – keys together will delete the selected rows or columns. You can also use the Delete feature situated just below the Insert feature to delete rows and columns. We will be glad to know if you have any suggestions or further queries. Please share them with us in the comment section below.

Related Articles

• Excel Macro: Convert Multiple Rows to Columns
• Excel VBA to Set Range Using Row and Column Numbers
• [Fixed!] Missing Row Numbers and Column Letters in Excel
• How to Find Difference Between Rows and Columns in Excel
• How to Switch Rows and Columns in Excel Chart
• How to Lock Column Width and Row Height in Excel
• How to Delete Empty Rows and Columns in Excel VBA
• Excel VBA: Get Row and Column Number from Cell Address