We often need to add rows and columns in excel. It helps us to reorganize the table or data. We can insert rows and columns to include more information in the data. Therefore, inserting new rows and columns is a necessary task too.
This article illustrates 3 ways how to add rows and columns in excel. The following picture gives a glimpse of idea how the rows and columns look like after insertion.
Download Practice Workbook
You can download the practice workbook from the download button below.
3 Ways to Add Rows and Columns in Excel
We have the following dataset to highlight the methods. It contains the results of 12 high school students. Assume that we want to add one or multiple rows below Row 2 and one or multiple columns at the right of Column B in that dataset. The following methods will illustrate how to do that.
1. Add Single and Multiple Rows and Columns Using Keyboard Shortcut
You can easily add new rows and columns in excel using the keyboard shortcut.
Keyboard Shortcut: CTRL+
Note: Rows are always added above while Columns are added to the left.
Follow the steps below to be able to do that.
1. At first, to add a row below row 2, select row 3 by clicking on the row number at the left of the row. (See the arrow in the picture below)
2. After that, hold CTRL & then press the + key. You will see a row added above row 3 & below row 2. Change the formatting according to your requirement using the format icon. Format Same As Above is the default option.
Add Multiple Rows:
3. Now to add multiple rows above, you need to select the same number of rows first. Let’s select rows 3 to 6 by dragging the mouse over the row numbers.
4. Then, press CTRL & the + key together. You will see 4 added rows above row 3.
5. Now we are going to add a column at the right of column B. Then, we need to select column C by selecting the column number at the top of the column.
6. After that, press the CTRL & the + keys together as before. Then, a column will be added to the left as follows.
Add Multiple Columns:
7. Next, select 2 columns instead of one as follows.
8. Now, press CTRL & + keys together. It will add 2 columns on the left.
9. Now you can also select a cell above which you want to add a row or a column at its left. Then if you press the CTRL & + keys together, a small dialog box will popup. You can choose Entire Row to add a row above or Entire Column to add a column to the left of the cell. After that, hit the OK button. Then a new row or a column will be added according to the choice you make.
2. Add Rows and Columns Using Insert Tool
An alternative way to add rows and columns in excel is by using the Insert tool. You need to follow the steps below to see how to do that.
1. At first, select a cell, in this case, let’s say cell C3. Then, go to the Insert tool from the Home tab. You can choose between inserting rows and columns.
2. On the other hand, you will see the alternative options greyed out if you select an entire row or column instead.
3. You can also do so by right-clicking on the selected cells, rows, or columns and then selecting Insert.
4. In the case of cell selection, a small Insert dialog box will pop up as shown in the earlier method. In other cases, an equal number of rows or columns will be added accordingly.
- How to Switch Rows and Columns in Excel (5 Methods)
- [Fixed!] Rows and Columns Are Both Numbers in Excel
- How to Transpose Column to Multiple Rows in Excel (6 Methods)
- Excel VBA: Set Range by Row and Column Number (3 Examples)
- How to Convert Multiple Rows to Columns in Excel (9 Ways)
3. Add Rows and Columns in Excel Table
We can easily add rows and columns to an excel table. To understand how to do that properly, let’s create an excel table using the same dataset. Then follow the steps below.
1. Suppose you want to add a new row below the table. Then start typing in any of the cells below the last row of the table. For example, type Paul Henry in cell B17.
2. Then hit Enter and you will see an entire row added below the table as follows.
3. Now you can start typing in any cells right to the last column of the table to add a column to the right of the table. Let’s try typing Remarks in cell F4.
4. Then hit Enter and a new column will be added to the right of the table.
5. You can also directly paste data instead of typing to get the same results. Suppose we have copied the following data. Now go back to the original form of the table by pressing CTRL+Z.
6. Then paste the copied data into cell B17. The data will be added in new rows in the table. You can add columns in the same way.
7. But if the data has more columns than the number of columns in the table, then the table will not expand as shown below.
8. To fix this problem, click anywhere in the table. Then select Table Design >> Resize Table.
9. After that select the range to which you want to modify your table. Then hit OK.
10. Finally your table will be resized with added rows and columns. You can use the Resize Table command to easily add rows and columns to an excel table in one go.
11. Moreover you can use the Insert feature or other earlier methods here too. Just click anywhere in the table. Then it will allow you to add more rows and columns to the table.
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.
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.
- Excel Macro: Convert Multiple Rows to Columns (3 Examples)
- How to Transpose Multiple Columns to Rows in Excel
- Excel VBA to Set Range Using Row and Column Numbers (4 Examples)
- How to Hide Rows and Columns in Excel (10 Ways)
- [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)
- How to Freeze Rows and Columns at the Same Time in Excel