Rows and Columns in Excel (All You Need to Know)

In Microsoft Excel, rows and columns form a grid structure in the spreadsheet. Each cell in the grid is identified by a unique combination of its row number and column letter.

In this article, you will learn everything about rows and columns in Excel. The spreadsheet’s horizontal sections are known as rows, and they are denoted by the numbers 1, 2, 3,… The vertical portions are called columns, and they are denoted by the letters A, B, C,… up to Z, after which come AA, AB, AC,… and so forth.

In the following image, the column and row headers are displayed. The intersection of each row and column makes a unique cell.

In this blog post, we will cover

1. The differences between rows and columns in Excel
2. How to insert and delete row/rows
3. How to insert and delete column/columns
4. How to group rows and columns
5. How to switch row to column and vice versa

It is essential to have the knowledge of rows and columns to handle and analyze data in Excel efficiently. It offers an effective method of arranging data and executing different actions on the spreadsheet’s contents.

Note: We have used Excel for Microsoft 365 to create this tutorial.

Excel Rows vs Columns

Here is a comparison table to show the key differences between rows and columns of Excel.

How Many Rows and Columns Are There in a Typical Excel Worksheet?

Excel worksheets generally consist of 16,384 columns and 1,048,576 rows. Although specifications may vary slightly throughout Excel versions, these figures are true for Excel 2007 and later editions, such as Excel 2010, 2013, 2016, 2019, and Microsoft 365.

The maximum number of rows and columns in older versions of Excel (before Excel 2007) is 65,536, and 256, respectively. Later versions can handle more complicated spreadsheets and larger datasets due to their increasing capacity.

How to Navigate Rows and Columns in Excel?

Here are two simple keyboard shortcut tips to navigate rows and columns in Excel.

• Press Ctrl + Down Arrow key to go to the last row of a data table or the last row of the sheet if there are all empty cells under the data table. To get back to the previous position, press Ctrl + Up Arrow
• Press Ctrl + Right Arrow key to go to the last column of a data table or the last column of the sheet if there are all empty cells to the right of the data table. To get back to the previous position, press Ctrl + Left Arrow

Here is an image showing how this navigation works.

How to Insert or Delete a Row in Excel?

Excel always inserts a new row above another. We can use the Insert and Delete commands to insert or delete a row or multiple rows respectively. The process of adding rows or columns is pretty much the same. Let’s have a look below.

Insert Row or Rows

To insert a new row above another row, you need to select the lower row first. Say, we want to insert a row above the 8th row. For this purpose,

• Select any cell of the 8th row.
• Right-click on it to open the Context Menu.
• Select the Insert
• After that, select Entire Row in the Insert dialog box and click OK.

In the image above, you can see that a new row is added at the 8th row. The 8th row becomes the 9th row of the table now.

To insert multiple rows, just select multiple cells of the corresponding rows and apply the previous command. Here, we want to insert two rows above the 7th row. For this purpose,

• Select the range B7:B8.
• After that, use the same procedure shown in the previous procedure. Follow the image below for clarification.

You can also insert a row or multiple rows above another using the context menu from the row header. See the image below.

Delete Row or Rows

The process of deleting a row or multiple rows is similar to the process of inserting rows. Just select the Delete command from the Context Menu. See the image below.

Here is another image showing the procedure of deleting multiple rows.

How to Insert or Delete a Column in Excel?

Excel always inserts a new column to the left of the current column. We can use the Insert and Delete commands to insert or delete a column or multiple columns respectively. The procedure is shown below.

Insert Column or Columns

To insert a new column to the left of another column, you need to select the right-sided column first. Say, we want to insert a column to the left of column C. For this purpose,

• Select any cell of column C.
• Right-click on it to open the Context Menu.
• Select the Insert option.
• After that, select Entire Column in the Insert dialog box and click OK.

In the image above, you can see that a new column is added to the left of the Full Name column. Column C becomes column D now.

To insert multiple columns, just select multiple cells of the corresponding columns and apply the previous command. Here, we want to insert two columns to the left of the Full Name column. For this purpose,

• Select the range B7:B8.
• After that, use the same procedure shown in the previous procedure. Follow the image below for clarification.

Note: You can also insert or delete rows or columns from the Insert and Delete drop-down of the Cells group.

Delete Column or Columns

The process of deleting a column or multiple columns is similar to the process of inserting columns. Just select the Delete command from the Context Menu. See the image below.

Here is another image showing the procedure of deleting multiple columns.

How to Group Rows and Columns in Excel?

Grouping rows or columns keeps the data in a group. You can view or hide the group at any time. Here, we will group the rows under Team A.

• First, select the rows under Team A.
• After that, select Data >> Outline >> Group.

Click on the image to enlarge

• The Group dialog box will appear.
• Select Rows and click the OK

Click on the image to enlarge

Note: You can also open the Group dialog box by pressing Alt + Shift + Right arrow key (→).

The selected rows are grouped now. You can hide them by clicking the Minus (–) button and unhide them by clicking the Plus (+) button. See the image below for a better understanding.

Similarly, you can group columns of a data table. To do this,

• Select the cells of the corresponding columns and open the Group dialog box.
• Next, select Columns and click the OK 

Click on the image to enlarge

You can see the Minus button above the column header. Click it to hide the column/columns and click the Plus button to unhide them.

Note: You can see that there’s the Ungroup command under the Outline group. Use it to ungroup the grouped rows or columns.

How to Switch Row to Column and Vice Versa in Excel?

You can switch the rows to columns and vice versa by using the Paste Transpose command. Follow the instructions below to understand how to do this.

• Press Ctrl + C to copy the data range.

• Next, right-click on a cell and select Paste >> Transpose command from the Context Menu.

This command will paste the data table in its transpose form which means it switches the rows to columns and columns to rows.

How to Set Range By Using Row and Column Numbers?

In this section, we are going to set a definite range by using the row and column numbers. We used a VBA code for this case.

• First, open the VBA editor window from Developer >> Visual Basic or press Alt + F11 (Alt + fn + F11 for laptop users).

• Next, select Insert >> Module to create a VBA Module.

• Insert the code below in the Module.

Sub SetRange()
    Set mn_Rng = Worksheets("set_range").Range(Cells(5, 2), Cells(15, 7))
    mn_Rng.Select
End Sub

The code sets a range from the (5, 2) cell to the (15, 7) cell which is the range B5:G15. You can apply any value to this range to populate. Here, we set no value, so the values won’t change in the dataset.

• After that, go back to the worksheet and select Macros or press Alt + F8 to open the Macros dialog box.
• Run the macro.

You will see that the range B5:G15 gets selected. The Select property shows that the macro does set a range.

How to Hide and Unhide Rows and Columns in Excel?

Sometimes, a set of data in a dataset may become unnecessary to display. That’s why we need to hide them.

To hide a set of rows, we need to select the cells of the corresponding rows and apply the Hide command. Here, we want to hide the 12th and 13th rows.

• Select two cells of these rows. Here, the B12 and B13 are selected.
• After that, choose Cells >> Format >> Hide & Unhide >> Hide Rows.

Click on the image to enlarge

• To unhide the hidden rows, just select the rows above and below to the hidden rows. Here, we selected the cells of 11th and 14th rows.
• After that, use the Unhide Rows command to display the hidden rows.

Click on the image to enlarge

You can hide and unhide columns similarly. So, the details are not given here.

Here is an image showing how you can hide columns.

Click on the image to enlarge

And the following image shows how to show the hidden columns.

Click on the image to enlarge

How to Lock Column Width and Row Height in Excel?

We can lock column width and row height by locking the format options for rows and columns. See the procedure below.

• First, select the triangle icon (marked 1 in the image). This will select all the cells in the sheet.
• Next, press Ctrl + 1 to open the Format Cells dialog box.
• Select the Protection tab >> uncheck the Locked
• After that, click OK.

• Now, select Review >> Protect Sheet.
• In the Protect Sheet dialog box, check all the options except Format columns and Format rows.
• Insert a password and click OK.

Reenter the password in the next dialog box and click OK. You can see that you can edit a cell or cell content but you cannot edit the column width or row height. The icon for editing the width and height does not appear when the cursor is placed at the row or column header section.

[Issue] Why are My Rows and Columns Both Numbers in Excel?

Sometimes, rows and columns can be both numbers due to some change in the settings. To get rid of this problem,

• Open the File tab.

• Next, open the Options feature.

• Select Formulas >> uncheck R1C1 reference style and click OK.

Now, the column letters properly appear in the column header section.

[Issue] Why Am I Missing Row Numbers and Column Letters in Excel?

If you find an Excel workbook that has no column letters and row numbers, simply follow the steps below to restore them.

• Select the View tab first.
• After that, check the Headings in the Show See the image below for better clarification.

Thus you have brought back the row numbers and column letters in the workbook.

To sum up, this article will help you learn the basics of rows and columns in Excel. We have discussed various topics such as the differences between rows and columns, ways of inserting and deleting row(s) and column(s), how to group them, and much more. We also provided a brief discussion on how you can switch row to column, and vice versa, hide and unhide rows or columns, and some common issues regarding rows and columns in Excel. If you have any questions or feedback, please share them in the comment section. Thank you!

Rows and Columns in Excel: Knowledge Hub

<< Go Back to Learn Excel