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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of inserting rows and columns in Excel


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.

Dataset of inserting rows and columns in Excel


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.

selecting row numbers 5 and 6 in the dataset

  • 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.

selecting Format Same As Below

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

output after adding two rows

Adjacent Columns

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

selecting columns D,E and pressing CTRL+

  • 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.

selecting formatting option


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.

selecting rows 5, 6 and pressing CTRL+

  • 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.

Output after adding two non-adjacent 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.

selecting non-adjacent columns C,E and pressing CTRL+

  • 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.

output after adding two non-adjacent columns


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.

applying Insert Sheet Rows command to insert two rows

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

applying Insert Sheet Rows command to insert two columns

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

selecting the Insert option from the context menu to add rows

Here is the output after adding rows and columns.

Output after adding rows and columns


Similar Readings


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.

Dataset converted to an Excel table

  • 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.

selecting Insert Table Rows Above and Insert Table Columns to the Left option

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

output after adding rows and columns in Excel 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.

adding rows and columns in Excel Table by manually inputting data

  • 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.

adding unfit data in Excel table

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

applying Resizing Table option to resize data

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

selecting new table range

The table is now adjusted with the new range.

resized Excel table


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.

selecting Visual Basic from Developer ribbon

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

inserting a new module in VBA window

  • 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.

applying VBA codes and running

Here’s the desired output.

output after inserting every other row


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.

applying VBA codes and running

  • 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.

output after inserting every other column


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.

selecting the Delete Sheet Rows and Delete Sheet Columns options

  • 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 –.

applying Delete option from context menu

The blank rows and columns are gone now.

output after deleting rows and columns


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.

applying Insert Cells option

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

applying Insert option from context menu

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

selecting Shift cells down option

  • A blank cell is now inserted there.

output after adding a blank cell


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.

applying Insert Cells option

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

selecting Shift cells down option

Here’s the output.

output after adding 3 cells


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

Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo