How to add a row in Excel easily that you might not know?

In the process of processing data in Excel, the task of adding or deleting rows or columns cannot be avoided. However, not everyone knows how to do that, so let’s find out with Buffcom.net.

Microsoft Excel is already a familiar tool for those who work in office jobs. This is also one of the many skills that most employers will require when you need to find a job today. Especially for those who need to process a large amount of data such as statistical tables, data, etc. In the process of performing and operating in Excel spreadsheets, you need to use many tricks to add rows in Excel to serve the best data processing.

I. Principles of adding rows and columns in Excel

When adding a row in Excel 2010, we need to pay attention to the following principles:

First principle: you need to know exactly whether you want to add one or more rows.
Second principle: pay attention to the positions where you want to add rows in Excel. For example, if you want to add a row in Excel before row 5, you need to select row 1 or the entire row 5.
Third principle: you can use the mouse or shortcut keys to insert rows in Excel.
When using the mouse, you can add one or more rows from the Ribbon, and you can also add rows in Excel directly in the sheet. The most important principle is:

To add one row, select a cell or select the entire row below the row where you want to insert.
To add (n) rows, you will select (n) rows below the row where you want to insert rows in Excel using the mouse or shortcut keys.

II. How to add rows and columns in Excel

1. Adding rows in Excel using keyboard shortcuts

Clicking on a row in an Excel spreadsheet and selecting “insert row” can be a time-consuming task. So, how can you quickly add a row in Excel without leaving the keyboard? The following methods will help you quickly add a row in Excel using the keyboard.

Method 1:
Press Shift + Space to select the entire row.
Press Control + Shift + + to add a row above the selected row.

Method 2:
Press Control + Shift + + to insert a row in Excel.
Press Enter to add a row below the selected row.

Method 3:
Press Alt + I (insert) and then press R (row).

Method 4:
Press Ctrl + Y.

Method 5:
Select the current row by pressing Shift + Space.
Press the shortcut for right-click + I to execute the “insert row” command in Excel. The right-click shortcut key is located between the Windows key and the Ctrl key.

Method 6:
Select the range where you want to insert a new row. To do this, click on the row number to the left of the first cell in that row, then hold down the left mouse button to expand the range.
Press Ctrl + Plus on the numeric keypad or press Ctrl + Shift + Plus. If you need to add several rows in Excel at once, say 100 rows, then use the F4 key, which repeats your last action. For example, if you want to insert 100 blank rows, select a range that contains about 10 rows, then use the shortcut to insert rows into the blank space, and then press F4 ten times.

Method 7:
Use a special shortcut if you need to add a row in Excel 2010 and your data is located to the right of the worksheet.

2. How to add a row in Excel using the mouse

Method 1:
Select a cell and one or more rows depending on where you want to insert a row in Excel. Then right-click within the selected area and choose Insert.

Method 2:
Follow step 1 of method 1.
Select the commands: Home -> Insert.
Note: Is there any difference between adding a row in Excel 2003, 2007, and 2010? For adding a row directly using the mouse on the worksheet or using basic shortcut keys, there is no difference in the different versions of Excel. However, in the method of selecting Home -> Insert in Excel 2003, there will be a difference in the operation of adding a row in Excel 2010 compared to 2007. In the 2003 version, you do not choose from the Ribbon as in the other two versions, but choose from the Menu bar.

III. How to insert a row in Excel into the selected table area while preserving the format of the remaining parts.

1. Quick guide

Select the row where you want to insert > Right-click within the selected area > Choose Insert.

2. Detailed guide

Step 1: Select the row where you want to insert. To select, click on the first cell of the row (the order cell) or click on any cell in that row and press Shift + Space to automatically highlight the entire row. Note: Select as many rows as you want to insert, and the new row will be inserted automatically above the selected area.

Step 2: Right-click within the selected area and choose Insert. The result will be a new row inserted in Excel above the rows you have selected.

IV. Other tips for adding rows in Excel

1. Inserting rows alternately in Excel

Step 1:

Open the Excel file in which you need to insert rows alternately in Excel 2010. First, add a column at the end of the data table with numbers from 1 to the end, and repeat in the rows without data. To perform the operation more quickly, enter the number 1, then hold down the Ctrl key, and drag the mouse down to the bottom.

Step 2:

On the newly added row number at the bottom, right-click and select Sort, then choose Custom Sort.

Step 3:

When the Sort Warning window appears, click the Sort button at the bottom. Note: If you select Expand the selection.

Step 4:

In the new Sort interface, uncheck the My data has header option. In the Sort by section, select Column E. Order, and set it to Small to Largest. Then click OK. The result is that the blank rows will appear alternately in the existing data table. Simply delete the numbers in column E.

Note: if you want to add 2 or 3 blank rows to the worksheet, you also need to create a sequence of numbers from 1 to the row where you want to insert rows in Excel. Then, if you want to add more rows, just repeat the corresponding sequence of numbers. With the above method, you can insert rows in Excel anywhere in the data table. The only difference is adding a column for numbering from one to the row where you want to insert rows.

2. Deleting rows in Excel

2.1. How to delete blank rows in Excel

Step 1: Select the range of data you want to delete. Use the Ctrl + A key combination to select all the data in the sheet.
Step 2: Select the Home tab -> Find & Select -> Go to Special. Or press the Ctrl + G key combination and select Special.
Step 3: In the Go to Special dialog box, select Blanks and then click OK.
Step 4: All blank cells are now selected. Continue by selecting the Home tab -> Delete -> Delete Sheet Rows to delete the empty rows. Click the Delete button in the Delete dialog box that appears. Here, you can leave the default Shift Cells up command or select the Entire row command to change the surrounding cells. Click the Ok button to remove unnecessary blank cells.

2.2. How to delete blank rows in Excel

Step 1: Select the area of data from which you want to delete blank rows.
Step 2: Select the Data tab. Under Sort & Filter, select Filter.
Step 3: Select the arrow icon in the first cell of any column. Here, uncheck the Select All option.
Step 4: Scroll the right-hand scroll bar to the bottom, select the Blank box, and click Ok.
Step 5: After step 4, we may receive the blank rows, now choose the following tabs in order: Home -> Delete -> Delete Sheet Rows to delete the blank rows.

3. How to select multiple rows in Excel

To select multiple rows at once, you can simply highlight all the rows you need to select. However, in addition to that method, you can also move your mouse to the beginning of the row and click there to select more quickly.

But there are still faster ways that you are encouraged to use, namely using shortcuts. Shortcuts will always be a tool to support users to perform operations as quickly as possible. Therefore, this article will guide you on how to select multiple rows in Excel using keyboard shortcuts.

Step 1: Select the row you want to highlight.
Step 2: Press the following shortcut to highlight the selected rows: Shift + (Spacebar). To highlight additional rows in Excel, click on the first rows by clicking on the row number to select and then hold down the Shift key and drag down to the row number you want to select.

This trick only involves simple steps like that, but it is enough to help you a lot when you have to process many files or add rows in Excel for data tables. By using keyboard shortcuts, you can quickly select many rows you want to highlight.

V. Instructions for adding a column in Excel

1. How to add a single column in Excel

Step 1: Select the cell next to where you want to insert a new column in Excel.
Note: The new column will be inserted to the left of the selected cell. Make sure to select the correct cell to insert the column where you want it.
Step 2: Right-click on the selected cell and click on Insert.
Step 3: In the Insert dialog box, select “Entire column” to add a column. Finally, click OK to finish adding the column.

2. How to add multiple columns in Excel

You can insert 10 or even 20 blank columns using the following steps:
Step 1: Select or highlight 10 columns next to the column where you want to insert new columns.
Step 2: To insert the columns, right-click on the selected columns and choose “Insert” from the dropdown list. Ten blank columns will then appear where you selected. Note that if you want to insert 20 columns, you need to select 20 columns instead of 10, as mentioned above.

3. How to add non-adjacent columns using shortcut keys

Of course, it is more convenient to use the F4 shortcut key to insert multiple blank columns at once. You can insert multiple consecutive blank columns by inserting a blank column before the third column, and then pressing the F4 key nine times. Ten blank columns will then appear between columns 2 and 3.

4. Adding a new column to an Excel table

To add a column to the end of a table, select any cell in the last column of the table, right-click on the selected cell, and choose “Insert -> Table Column to the Right”.

VI. How to insert blank rows between rows

1. Insert unevenly spaced rows

For small data tables or if you want to insert blank rows in certain rows, follow these steps.
Quick guide:
Hold down Ctrl and click to select the rows where you want to insert blank rows in Excel > Right-click inside the highlighted area and select Insert.

Detailed guide:

Step 1: Hold down Ctrl and select each row you need to insert blank rows from the data table by clicking on the number of each row.
Step 2: Right-click inside the selected area and select Insert from the list (or use the Excel shortcut keys listed in sections I, II, III). The result will appear in the alternating inserted rows.

2. Insert rows one by one

Quick guide:
Create a helper column in the Excel work area where you want to insert blank rows and enter numbers 1,2,3,….
Copy the helper column you just created and paste it right below the last cell of that column.
Select all the table areas you just worked with, select Sort in the Data section.
On the window, you will see tables classified by the helper column.
At the Sort by section, select the fake column number.
Choose Values in the Sort On section.
In the Order section, select Smallest to Largest.
Detailed guide:

Step 1: Simulate a helper column. Enter numbers 1 and 2 in the cell where you want to start. Then, move your mouse to the bottom right corner of cell 2 until the plus sign (+) appears, then hold the mouse and drag it to the row where you want to insert blank rows in Excel.

Step 2: Copy the helper column and paste it right below the last cell of that column.

Step 3: Select all the table areas you just worked with > Select the Data tab > Select Sort.

Step 4: On the window, you will see tables classified by the helper column:

At the Sort by section, choose the fake column number you just created.
Choose Values in the Sort On section.
In the Order section, select Smallest to Largest.
Finally, click OK.
The result will be the inserted rows in Excel in ascending order according to the fake column. Then you can delete the helper column to finish. The result will be the inserted rows in ascending order according to the fake column.

VII. Conclusion

With the above operations, we have learned the most basic knowledge to create an Excel table or how to insert blank rows in Excel to serve the necessary needs for office workers or students. Hopefully, this Buffcom.net article has helped you to better control data in Excel.