Rows, columns and sheets

This page covers the structure of a workbook: the cells, rows and columns of a sheet, and the sheets themselves.

Insert and delete

Use the Insert and Delete menus in Home › Cells.

When you insert or delete whole rows or columns, formulas are rewritten on every sheet that refers to the cells that moved: =SUM(B2:B9) becomes =SUM(B2:B10) after a row is inserted inside the range. A reference that lay wholly inside deleted rows or columns becomes #REF!, and a range that loses some of its rows or columns shrinks. Charts move with the cells they sit over, and their data follows the cells it plots.

To insert several rows, repeat Insert sheet rows.

Column width and row height

With the mouse

• Resize a column: drag the right edge of its letter heading. The pointer changes to a resize arrow over the edge.
• Resize a row: drag the bottom edge of its number heading.
• Fit a column to its contents: double-click the right edge of its letter heading.

With the Format menu

Home › Cells › Format has:

Column widths are measured in characters of the default font, as Excel measures them; row heights are in points.

Default column width

Home › Editing › Default width sets how wide every column is that nobody has resized: Narrow — 8, Normal — 8.43, Wide — 14 or Very wide — 20 characters. The status bar confirms the new width. Columns you sized yourself keep their width.

Hide and unhide rows and columns

1. Select cells in the rows or columns to hide.
2. Choose Home › Cells › Format › Hide rows or Hide columns.

Hidden rows and columns take no space on the grid, so their headings are skipped — for example, row 4 followed by row 7.

To show them again:

1. Select a block that spans the hidden rows or columns — for example, click row 4’s heading and Shift-click row 7’s, or type A4:A7 in the name box.
2. Choose Home › Cells › Format › Unhide.

Rows hidden by a filter, an outline or a slicer are separate from rows you hid yourself. Clearing the filter or showing the outline detail brings back only the rows they hid.

Freeze panes

Freezing keeps the rows at the top and the columns at the left in view while you scroll the rest of the sheet — typically the headings.

1. Click the cell below the rows and to the right of the columns you want to keep. For example, click B2 to freeze row 1 and column A.
2. Click the freeze panes button in View › Window (its tooltip is Freeze panes at the cursor — the headings stay put while the rest scrolls), or choose Freeze panes here from the More menu in the title bar.

A grey line marks the edge of the frozen area. To unfreeze, click the same button again. The button is highlighted while panes are frozen.

Frozen panes are saved with the workbook. To look at two distant parts of the same sheet at once, use a split instead — see Views, zoom and split panes.

Work with sheets

A workbook has one or more sheets, shown as tabs at the bottom of the window. A new workbook starts with one sheet called Sheet1.

Add a sheet

Use any of these:

• Click + at the left of the sheet tabs (Add a sheet).
• Click Add a sheet in Insert › Sheets.
• Choose Home › Cells › Insert › Insert sheet.

The new, empty sheet is added at the end and becomes the current sheet. It is called Sheet, or Sheet (2), Sheet (3) and so on if that name is taken.

Switch sheets

Click a sheet’s tab.

Rename a sheet

1. Right-click the sheet’s tab and choose Rename…, or choose Home › Cells › Format › Rename this sheet… for the current sheet.
2. Type the new name.
3. Click Rename (or Apply, from the Format menu), or press Enter.

Sheet names follow Excel’s rules:

• A name can be up to 31 characters. Longer names are cut short.
• The characters [ ] * / \ ? : are replaced with spaces.
• If another sheet already has the name, (2) or a higher number is added.

Move a sheet

Right-click the sheet’s tab and choose Move left or Move right. Repeat to move it further.

Duplicate a sheet

1. Show the sheet you want to copy.
2. Choose Home › Cells › Format › Duplicate this sheet.

The copy is added immediately after the original and named after it with (2), for example Budget (2). It has the original’s cells (values, formulas, formatting and notes), column widths, row heights, merged cells, conditional formatting, data validation, frozen panes, grid line setting, tab colour and filter range.

Colour a sheet tab

1. Show the sheet.
2. Choose Home › Cells › Format › Tab colour….
3. In Tab colour, click one of the eight colours, or click No colour to remove it.

The colour appears as a dot on the tab and is saved in the file.

Delete a sheet

1. Show the sheet, or right-click its tab.
2. Choose Delete from the tab’s menu, or Home › Cells › Delete › Delete sheet.

The sheet is deleted straight away. Press ⌘Z (Ctrl+Z) to bring it back. A workbook must keep at least one sheet; trying to delete the last one shows A workbook needs at least one sheet.

When sheet commands are refused

If the workbook’s structure is protected, adding, renaming, deleting, moving and duplicating sheets are refused. The status bar says, for example, This workbook’s structure is protected, so you cannot add a sheet. Unprotect it on the Review tab first. See Protecting sheets and workbooks.

Grid lines

To hide or show the grid lines on the current sheet, click Grid lines in View › Show, the grid lines button in Page Layout › Sheet options (Grid lines on screen), or Grid lines in the More menu. The setting belongs to the sheet and is saved with it. Whether grid lines print is a separate setting — see Printing and exporting.

Sheet right to left

Page Layout › Sheet options has a button for a right-to-left sheet. It sets the sheet’s direction, as someone working in Arabic or Hebrew would, and saves it in the file.