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

Get FREE Advanced Excel Exercises with Solutions!

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.

Rows and Columns in Excel

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.

Particulars Rows Columns
Definition Horizontal portions of Excel cells Vertical portions of Excel cells
Addressing Identified by numbers Identified by letters
Size Parameter Height Width
Total Numbers in a Sheet 1,048,576 16,384
Selection ·         Drag the selection cursor left to right after selecting a cell

·         Click on the row number to select the entire row or press Shift + Spacebar

·         Drag the selection cursor downwards or upwards  after selecting a cell

·         Click on the column letter to select the entire column or press Ctrl + Spacebar

Direction Horizontally from left to right Vertically top to bottom
Data Organization Represents a separate record or entry Represents a specific attribute or characteristic of the data
Example to SUM two Items =SUM(7:8) =SUM(C:D)
Total Value Display Location To the bottom To the right
Formatting Height adjustment, text alignment, and cell borders can be applied to entire rows. Width adjustment, text alignment, and cell borders can be applied to entire columns.
Expanding Data New rows are inserted above or below existing rows. New columns are inserted to the left or right of existing columns.
Scrolling Horizontal Vertical

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.

Navigating Last Row and Column


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.

Inserting Rows in a Data Table

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.

Inserting Multiple Rows

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

Inserting New Rows Above an Entire Row


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.

Deleting a Row from the Data Table

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

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.

Inserting Column

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.

Inserting Multiple Columns

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.

Deleting a Column from the Data Table

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

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.
Applying Group Command

Click on the image to enlarge

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

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.

Grouped Rows

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 
Grouping Columns

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.

Grouped Columns

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.

Copying Data Range

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

Switching Rows to Columns and Vice Versa

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

Opening VBA Window

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

Opening 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

VBA Code for Setting Range

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.

Setting Range by Row and Column Numbers

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.
Hiding 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.
Unhiding 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.

Hiding Columns

Click on the image to enlarge

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

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

Unlocking Cells

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

Setting up Options to Lock Height and Width

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.

Row Height and Column Width Locked


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

Opening File Tab

  • Next, open the Options feature.

Opening Options Feature

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

Unchecking R1C1 Style

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

Column Letters Appear


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

Showing Row Numbers and Column Letters

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo