Manipulating Rows and Columns in Excel

Insert, Delete, move, and hiding rows and columns in Excel

Although the number of rows and columns in a worksheet is set,

We may still insert rows and columns to create room for more data,

or delete rows and columns if the data included in them is no longer required.

The overall number of rows and columns in the worksheet is not affected by these procedures. Rows and columns can be resized or hidden to suit our needs.

Cells Group on the Home Tab
Figure 1- Cells Group on the Home Tab


The Cells group on the Home tab of the Ribbon offers controls for quickly inserting, deleting, or formatting rows and columns (see Figure 1).

1. Inserting Rows and Columns

Insert rows and columns into a worksheet to fill in blanks or add data.

Columns are added to the left of the specified column, and rows are added above the specified row.

How to Insert Rows

  1. Click the row heading to the right of where We want the new row to appear.
  2. On the Home tab, select the Insert command.
  3. The new row will be shown above the already chosen row.
Note: We can also insert a row by right-clicking the header of the row above which we wish to add the new row and selecting Insert from the shortcut menu.
 Insert a row
Figure 2- Insert a row
Note:  When we enter additional rows, columns, or cells, the Insert Options button appears next to the added cells. This option lets us choose how Excel formats the cells. Excel formats added rows using the same formatting as the cells in the row above by default. Hover the cursor over the Insert Choices button, then click the drop-down arrow to view more options.

How to Insert Column

  1. Click the column heading to the right of the location where we want the new column to appear.
  2. On the Home tab, select the Insert command.
  3. The new column will be added to the left of the already chosen column.
Note: We may alternatively insert a column by right-clicking the header of the column to the left of which we want to add a new column, then selecting Insert from the shortcut menu.
 Insert a column
Figure 3- Insert a column
Note: When entering rows and columns, make sure to select the full row or column by selecting the header. If we just choose a cell in a row or column, the Insert command will only insert a new cell.

2. Deleting Rows and Columns

To fill empty space or eliminate undesirable data,

We may delete rows and columns from a spreadsheet.

Before removing a row or column,

be sure it doesn’t include any data that we wish to preserve.

To delete rows

  1. Select the row(s) to be deleted.
  2. On the Home tab, select the Delete command.
  3. The selected row(s) will be removed, and the rows below will be moved up.
Delete a Row
Figure 4- Delete a Row
Note: We can also delete a row by  right-clicking the row header, and then
clicking Delete on the shortcut menu.

To Delete columns

  1. Select the column(s) to be deleted.
  2. On the Home tab, select the Delete command.
  3. The selected column(s) will be removed, and the columns to the right will be shifted to the left.
Note: We may also delete a column by right-clicking it and then selecting Delete from the shortcut menu.
Delete a Column
Figure 5- Delete a Column
Note: It's critical to distinguish between removing a row or column and merely wiping its contents. Right-click a heading, then pick Clear Contents from the drop-down menu to erase the content of a row or column without causing others to shift.

3. How to move a row or column

To alter the information of our worksheet, We may need to change a column or row.

Steps to move a row or column

  1. Choose the appropriate column heading for the column we want to relocate, then click the Cut instruction.
  2. Ctrl+X on your keyboard or on the Home tab
  3. Choose the column heading to the right of the location where we wish to shift the column.
  4. As an example, suppose we select column A if we wish to relocate a column before column A.
  5. On the Home tab, click the Insert command, then pick Insert Cut Cells from the drop-down menu.
  6. The column will be relocated to the desired place, and the columns to the right will be shifted to the right.
Moving a Column
Figure 6- Moving a Column
Note: we may also access the Cut and Insert commands by right-clicking the mouse and then selecting the relevant commands from the drop-down menu.
Moving a Row
Figure 7- Moving a row

4. Hide and Unhide a row or column

Within a worksheet, we may Hide rows and columns.

Any data or calculations stored in hidden rows and columns are still accessible via references; they are only concealed from view.

When we require the data, we may reveal it by unhiding rows and columns.

In printing, hidden rows and columns are not visible.

Steps to hide and unhide a row or column

  1. Select the column(s) to be hidden, right-click the mouse, and then choose Hide from the formatting menu.
  2. The columns will be obscured. The concealed columns are shown by the green column line.
  3. To reveal the columns, pick the columns to the left and right of the hidden columns (the columns on both sides of the hidden columns).
  4. Select Unhide from the formatting menu by right-clicking the mouse.
  5. The columns that were concealed will resurface.
 hide a row and column
Figure 8- hide a row and column
Note: We may also hide a row or column by right clicking its header and selecting Hide from the shortcut menu.

5. Modifying Columns, Rows, and Cells

By default, the height and width of each row and column in a new worksheet are always the same.

Excel allows us to change the width and height of columns and rows in a variety of ways, including wrapping text and merging cells.

To modify column width

The columns in the default worksheet are broad enough to show roughly 8 characters.

If our data is too long to fit in a cell, We can expand the column to show the whole contents of the cell.
To minimise worksheet space, we may also make columns narrower.

The column width is set to 8.43 characters by default.

A column width of 0 (zero) to 255 characters can be specified.

When we set the width of a column to 0 (zero) characters, the column is hidden.

Step to modify column width( By Format Option)

  1. Choose the column we wish to enlarge.
  2. On the Home tab, in the Cells group, select the Format button, followed by Column Width (See below figure ).
  3. Alternatively, right-click the column header and select Column Width from the shortcut menu.
  4. In the Column Width dialogue box, input a value in the Column width box before clicking the OK button.
Modify column width
Figure 9- Modify column width ( By Format Option)
Note: If we see pound marks (#######) in a cell, it signifies the column is too narrow to show the cell's information. To display the cell content, just expand the column width.

Step to modify column width (By Draging Cell)

  1. Position the mouse over the column line in the column heading.
  2. Now the white cross becomes a double arrow.
  3. Click, hold, and drag the mouse to increase or decrease the column width.
  4. Release the mouse. The column width will be changed.
Modify column width ( By Draging Cell)
Figure 10- Modify column width ( By Draging Cell)

To AutoFit column width

The AutoFit function allows us to automatically set the width of a column to match its content.

Step to AutoFit column width

  1. Move the mouse over the column line in the column header so that the white cross transforms into a double arrow.
  2. Make a double-click with your mouse.
  3. The column width will be automatically adjusted to match the content.
Figure 11- AutoFit column width
Note: We may also AutoFit the width of many columns at once. Simply choose the columns we want to AutoFit, then click the Format dropdown menu on the Home page and pick the AutoFit Column Width option. This method may also be used to calculate row height.

To modify Row Height

Excel automatically changes row heights to fit the row’s highest entry.

Row heights can, however, be manually increased or decreased as desired.

The row height is set to 15 points by default. A row height of 0 (zero) to 409 points can be specified.

When we set the height of a row to 0 (zero) points, the row is hidden.

Step to modify Row height ( By Format Option)

  1. Select the row that we want to resize.
  2. On the Home tab, in the Cells group, click the Format button, and then click Row height.
  3. Or, right-click the row header, and then click Row Height on the shortcut menu.
  4. In the Row Height dialogue box, type a value in the Row height box.
  5. And then click the OK button
Figure 12- Modify Row height ( By Format Option)

Step to modify Row height (By Dragging Cell)

  1. Move the cursor over the row line until the white cross changes to a double arrow.
  2. To change the row height, click, hold, and drag the mouse.
  3. Let go of the mouse. The height of the currently chosen row will be adjusted.
Figure 13- Modify Row height ( By Draging Option)

To modify all rows or columns

Instead of resizing rows and columns separately, we may change the height and width of each row and column at the same time.

This approach allows us to make each row and column in our worksheet the same size.

Steps to modify all rows or columns

  1. To select every cell in the worksheet, locate and click the Select All button directly below the formula bar.
  2. Move the mouse over a row line until the white cross changes to a double arrow.
  3. To change the row height, click, hold and drag the mouse.
  4. When you’re satisfied with the new row height for the worksheet, let go of the mouse.

Figure 14- Modify all rows or columns

Enjoy!! Happy Learning👍

THANK YOU FOR READING. PLEASE PROVIDE YOUR VALUABLE FEEDBACK.

Free Excel EBOOK

We don’t spam! Read our privacy policy for more info.

You may also like...

50 Responses

  1. It’s really a nice and helpful piece of info. I am glad that you shared this helpful information with us. Please keep us informed like this. Thanks for sharing.

  2. Just wanna input on few general things, The website style and design is perfect, the written content is very excellent : D.

  3. When I initially commented I clicked the -Notify me when new feedback are added- checkbox and now each time a remark is added I get 4 emails with the same comment. Is there any way you may remove me from that service? Thanks!

  4. Very nice style and wonderful articles, practically nothing else we want : D.

  5. Fantastic blog! Do you have any tips and hints for aspiring writers? I’m planning to start my own site soon but I’m a little lost on everything. Would you suggest starting with a free platform like WordPress or go for a paid option? There are so many choices out there that I’m completely overwhelmed .. Any ideas? Many thanks!

  6. Attractive component to content. I simply stumbled upon your website and in accession capital to say that I get in fact enjoyed account your weblog posts. Any way I will be subscribing to your feeds and even I fulfillment you get admission to consistently quickly.

  7. I think this is one of the most vital info for me. And i’m glad reading your article. But wanna remark on some general things, The website style is ideal, the articles is really excellent : D. Good job, cheers

  8. Provadent says:

    Hi there, i read your blog from time to time and i own a similar one and i was just wondering if you get a lot of spam comments? If so how do you prevent it, any plugin or anything you can recommend? I get so much lately it’s driving me crazy so any support is very much appreciated.

  9. Some genuinely nice stuff on this site, I like it.

Leave a Reply

Your email address will not be published. Required fields are marked *