Rows and Columns
By the Syncfusion DevTutorials team · Last updated
Insert and delete rows and columns, change their size, AutoFit, and hide and unhide them. Then tidy a stock list in the Chapter 2 project.
Data does not always fit perfectly when you first create a worksheet. You may need space for new items, wider columns to display information clearly, or helper columns that should stay out of view. In this lesson, you'll learn how to insert and delete rows and columns, adjust their size, use AutoFit, and hide or unhide them. You'll finish by cleaning up a stock list in the Chapter 2 project.
Open the Excel sandbox and follow along.
Insert Rows and Columns
Insert a Row
- Right-click the row number where you want the new row to appear.
- Choose Insert or Insert Row Above.
Excel inserts a new blank row above the selected row and moves the existing rows downward.
Insert a Column
- Right-click the column letter where you want the new column to appear.
- Choose Insert or Insert Column Before.
Excel inserts a new blank column to the left of the selected column and shifts the existing columns to the right.
To insert multiple rows or columns at the same time, first select the required number of rows or columns and then choose Insert. For example, selecting three rows and inserting will create three new rows.
Formulas automatically adjust when rows or columns are inserted. For example, if a formula uses =SUM(B2:B10) and you insert a row within that range, Excel updates the formula to include the new row automatically.
Delete Rows and Columns
- Right-click the row number or column letter you want to remove.
- Select Delete.
The entire row or column is removed, and the surrounding data shifts to fill the empty space.
Using Delete from the shortcut menu removes the entire row or column. Pressing the Delete key on the keyboard removes only the contents and leaves the row or column in place.
Column Width and Row Height
If text appears cut off or numbers display as #####, the column is not wide enough.
Resize by Dragging
- Move the pointer to the border on the right side of a column heading until it becomes a double-headed arrow.
- Drag right to increase the width or left to decrease it.
- To resize rows, drag the border below a row number.
Set an Exact Size
- Right-click a column letter and select Column Width.
- Right-click a row number and select Row Height.
- Enter the required size and select OK.
To make multiple columns the same width, select them first and then resize any one of the selected columns.
AutoFit
AutoFit automatically adjusts the size of a row or column to match its contents.
- Double-click the border on the right side of a column letter.
- Or go to Home > Format > AutoFit Column Width.
You can select multiple columns and AutoFit them all at once. The same process can be used for rows.
When you see ##### in a cell, it usually means the column is too narrow to display the value. Increase the column width or use AutoFit to make the value visible.
Hide and Unhide
Hiding removes rows or columns from view without deleting their data.
Hide Rows or Columns
- Right-click the row number or column letter.
- Select Hide.
Identify Hidden Rows or Columns
Hidden rows or columns cause numbering or lettering to skip. For example, you might see A, B, D, indicating that column C is hidden.
Unhide Rows or Columns
- Select the rows or columns on both sides of the hidden item.
- Right-click the selection.
- Choose Unhide.
For example, to unhide column C, select columns B through D, right-click, and choose Unhide.
Hidden rows and columns continue to be used in formulas. Hiding data improves worksheet organization but does not protect information from being viewed.
Common mistakes
| Mistake | Result | Solution |
|---|---|---|
| Pressing the Delete key to remove a row | The row remains and only the data is cleared | Right-click the row number and choose Delete |
| Inserting a row in the wrong location | Data appears in the wrong order | Undo the action and insert the row in the correct position |
| Deleting a column used by a formula | The formula displays #REF! | Undo the deletion immediately |
| Hidden data cannot be found | Rows or columns appear to be missing | Look for skipped row numbers or column letters and unhide them |
| Columns are too narrow | Cells display ##### | Use AutoFit or increase the width |
Exercise
Click Reset in the sandbox header. Enter the following values in column A:
- A1:
Fruit - A2:
Apple - A3:
Cherry - A4:
Damson
Then complete each task and click Check.
Chapter Project
Put together everything you've learned in Chapter 2 by cleaning up a shop stock list. You'll add a missing product, create a total, convert the result into a fixed value, and improve the worksheet layout.
Corner Shop Stock List
What to Remember
- Right-click a row number or column letter to Insert or Delete rows and columns.
- New rows are inserted above the selected row, and new columns are inserted to the left of the selected column.
- Drag borders to resize rows and columns.
- Double-click borders to use AutoFit.
#####usually means a column is too narrow.- Hide removes rows or columns from view without deleting their data.
- Select the neighboring rows or columns and choose Unhide to make them visible again.
- Formulas automatically adjust when rows or columns are inserted within their referenced range.
That's the end of Chapter 2. In Chapter 3, you'll learn how to format cells using fonts, colors, borders, and alignment.