Modifying a Worksheet, Insert Cells, Rows, Columns and Delete Cells, Insert Rows on a Worksheet

Get unlimited access to the best preparation resource for NSO Class-6: fully solved questions with step-by-step explanation- practice your way to success.

Download PDF of This Page (Size: 274K)

Modifying a Worksheet

Insert Cells, Rows, Columns and Delete Cells

Insert Blank Cells on a Worksheet

  • Select the cell or the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) Of cells where you want to insert the new blank cells. Select the same number of cells as you want to insert. For example, to insert five blank cells, you need to select five cells.

  • On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Cells.

  • You can also right-click the selected cells and then click insert on the shortcut menu.

  • In the Insert dialog box, click the direction in which you want to shift the surrounding cells.

Insert Rows on a Worksheet

Do one of the following:

  • To insert a single row, select the row or a cell in the row above which you want to insert the new row. For example, to insert a new row above row 5, click a cell in row 5.

  • To insert multiple rows, select the rows above which you want to insert rows. Select the same number of rows as you want to insert. For example, to insert three new rows, you need to select three rows.

  • To insert nonadjacent rows, hold down CTRL while you select nonadjacent rows.

On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Rows.

Insert Columns on a Worksheet

Do one of the following:

  • To insert a single column, select the column or a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.

  • To insert multiple columns, select the columns immediately to the right of where you want to insert columns. Select the same number of columns as you want to insert. For example, to insert three new columns, you need to select three columns.

  • To insert nonadjacent columns, hold down CTRL while you select nonadjacent columns.

On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Columns.

Delete Cells, Rows, or Columns

  • Select the cells, rows, or columns that you want to delete.

  • On the Home tab, in the Cells group, do one of the following:

    • To delete selected cells, click the arrow next to Delete, and then click Delete Cells.

    • To delete selected rows, click the arrow next to Delete, and then click Delete Sheet Rows.

    • To delete selected columns, click the arrow next to Delete, and then click Delete Sheet Columns.

  • If you are deleting a cell or a range of cells, in the Delete dialog box, click Shift cells left, Shift cells up, Entire row, or Entire column.

Resizing Rows and Columns

Set a Column to a Specific Width

  • Select the column or columns that you want to change.

  • On the Home tab, in the Cells group, click Format.

  • Under Cell Size, click Column Width.

  • In the Column width box, type the value that you want.

Change the Column Width to Fit the Contents

  • Select the column or columns that you want to change.

  • On the Home tab, in the Cells group, click Format.

  • Under Cell Size, click AutoFit Column Width.

  • To quickly auto fit all columns on the worksheet, click the Select All button and then double- click any boundary between two column headings.

Match the Column Width to Another Column

  • Select a cell in the column.

  • On the Home tab, in the Clipboard group, click

  • Copy, and then select the target column.

  • On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.

  • Under Paste, select Column widths.

Change the Width of Columns by Using the Mouse

Do one of the following:

  • To change the width of one column, drag the boundary on the right side of the column heading until the column width changes to the desired size that you want.

  • To change the width of multiple columns, select the columns that you want to change, and then drag a boundary to the right of a selected column heading.

  • To change the width of columns to fit the contents, select the column or columns that you want to change, and then double- click the boundary to the right of a selected column heading.

  • To change the width of all columns on the worksheet, click the Select All button, and then drag the boundary of any column heading.

Set a Row to a Specific Height

  • Select the row or rows that you want to change.

  • On the Home tab, in the Cells group, click Format.

  • Under Cell Size, click Row Height.

  • In the Row height box, type the value that you want.

Change the Row Height to Fit the Contents

Change the row height to fit the contents

Change the Row Height to Fit the Contents

  • Select the row or rows that you want to change.

  • On the Home tab, in the Cells group, click Format.

  • Under Cell Size, click AutoFit Row Height. To quickly auto fit all rows on the worksheet, clicks the Select All button and then double-click the boundary below one of the row headings.

Change the Height of Rows by Using the Mouse

Do one of the following:

  • To change the row height of one row, drag the boundary below the row heading until the row is the height that you want.

  • To change the row height of multiple rows, select the rows that you want to change, and then drag the boundary below one of the selected row headings.

  • To change the row height for all rows on the worksheet, click the Select All button, and then drag the boundary below any row heading.

  • To change the row height to fit the contents, double-click on the border.

Move or Copy Entire Cells

When you move or copy a cell, Excel moves or copies the entire cell, including formulas and their resulting values, cell formats, and comments.

  • Select the cells that you want to move or copy.

  • On the Home tab, in the Clipboard group, do one of the following:

    • To move cells, click Cut /Keyboard shortcut or CTRL+X.

    • To copy cells, click Copy /Keyboard shortcut or CTRL+C.

  • Select the upper-left cell of the paste area.

  • On the Home tab, in the Clipboard group, click Paste Or CTRL+V.

Move or Copy Entire Cells by Using the Mouse

  • By default, drag-and-drop editing is turned on so that you can use the mouse to move and copy cells.

  • Select the cells or range of cells that you want to move or copy.

  • Do one of the following:

    • To move a cell or range of cells, point to the border of the selection. When the pointer becomes a move pointer, drag the cell or range of cells to another location.

    • To copy a cell or range of cells, hold down CTRL while you point to the border of the selection. When the pointer becomes a copy pointer, drag the cell or range of cells to another location.

Insert Moved or Copied Cells between Existing Cells

  • Select the cell or range of cells that contains the data that you want to move or copy.

  • On the Home tab, in the Clipboard group, do one of the following:

    • To move the selection, click Cut or press CTRL+X.

    • To copy the selection, click Copy or press CTRL+C.

  • Right-click the upper-left cell of the paste area, and then click Insert Cut Cells or Insert Copied Cells on the shortcut menu.

  • In the Insert Paste dialog box, click the direction in which you want to shift the surrounding cells.

Prevent Copied Blank Cells from Replacing Data

  • Select the range of cells that contains blank cells.

  • On the Home tab, in the Clipboard group, click Copy, Keyboard shortcut you can also press CTRL+C.

  • Select the upper-left cell of the paste area.

  • On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.

  • Select the Skip blanks check box.

Move or Copy the Contents of a Cell

  • Double-click the cell that contains the data that you want to move or copy.

  • In the cell, select the characters that you want to move or copy.

  • To select the contents of a cell. Do this in the cell. Double-click the cell, and then drag across the contents of the cell that you want to select.

  • In the formula bar, Click the cell, and then drag across the contents of the cell that you want to select in the formula bar.

  • By using the keyboard Press F2 to edit the cell, use the arrow keys to position the insertion point, and then press SHIFT+ARROW key to select the contents.

  • On the Home tab, in the Clipboard group, do one of the following:

    • To move the selection, click Cut.

    • To copy the selection, click Copy.

  • In the cell, click where you want to paste the characters, or double-click another cell to move or copy the data.

  • On the Home tab, in the Clipboard group, click Paste

  • Press ENTER.

Copy Cell Values, Cell Formats, or Formulas Only

  • When you paste copied data, you can do any of the following:

  • Convert any formulas in the cell to the calculated values without overwriting the existing formatting.

  • Paste only the cell formatting, such as font color or fill color (and not the contents of the cells).

  • Paste only the formulas (and not the calculated values).

  • Select the cell or range of cells that contains the values, cell formats, or formulas that you want to copy.

  • On the Home tab, in the Clipboard group, click Copy

  • Select the upper-left cell of the paste area or the cell where you want to paste the value, cell format, or formula.

  • On the Home tab, in the Clipboard group, click the arrow below Paste , and then do one of the following:

    • To paste values only, click Paste Values.

    • To paste cell formats only, click Paste Special, and then click Formats under Paste.

    • To paste formulas only, click Formulas. Drag and Drop

  • If you are moving the cell contents only a short distance, the drag- and-drop method may be easier. Simply drag the highlighted border of the selected cell to the destination cell with the mouse.

Freeze Panes

  • If you have a large worksheet with column and row headings, those headings will disappear as the worksheet is scrolled. By using the Freeze Panes feature, the headings can be visible at all times.

    • Click the label of the row below the row that should remain frozen at the top of the worksheet.

    • Select View TabWindow Group on ribbonFreeze PanesFreeze Panes

    • To remove the frozen panes, View TabWindow Group on ribbonFreeze PanesUnfreeze Panes.

  • Freeze panes have been added to row 2 in the image above. Notice that the row numbers skip from 1 to 11. As the worksheet is scrolled, rows 1 will remain stationary while the remaining rows will move. Following similar steps, you can Freeze or Unfreeze selected columns.

  • Unfreeze Panes option will come only if the respective panes are already Freeze.

Freeze Panes

Freeze Panes

Developed by: