Copy, paste, freeze

A table you get from someone else is rarely easy to work with: the numbers are right, but handling it is a pain. The orders file below has four problems at once:

• the client names are cut off and can't be read;
• the Old amount column is left over from an earlier version and isn't needed anymore;
• order 1014 went missing, and it needs a row in its proper place;
• the table is long, and when you scroll, the header (the row with the column names) scrolls away.

Each one takes a couple of clicks to fix. Clean up the file step by step:

Rows and columns

Inserting and deleting live in the menu that opens when you right-click. On a Mac, click the trackpad with two fingers or hold down Control while you click. Which items the menu shows depends on where you click:

• on a row number: Insert Rows Above and Delete Rows;
• on a column letter: Insert Columns Left and Delete Columns;
• on an ordinary cell: the same commands under Insert and Delete.

If you select several rows first, for example by dragging across the row numbers from 5 to 7, Excel inserts or deletes three rows at once.

Deleting removes the row across the whole width of the sheet, and the rows below move up to take its place. To erase just the values and leave the row empty, choose Clear Contents in the same menu or press Delete.

Column width

To change the width, drag the border between the column letters above the sheet. Point at the border, and the pointer turns into a double-headed arrow, then drag. Double-clicking the same border fits the column to the longest value in it.

Text that doesn't fit spills over into the next cell if that cell is empty, and gets cut off if it isn't. Numbers can't do that. When a number doesn't fit, Excel shows hash marks #### instead. The number itself is fine, the column just needs more room.

Keep the header in view

Freezing keeps the top row on the screen while you scroll down the sheet. On the View tab, click Freeze Panes and choose Freeze Top Row. A line appears under the first row: everything above it no longer scrolls away.

This doesn't change your data, only what you see on the screen. To unfreeze, go to the same place: Freeze Panes → Unfreeze Panes.

Copy and paste

Excel uses the same keys as other programs:

• Ctrl + C copies the selected cells;
• Ctrl + X cuts them: after you paste, they disappear from their old place;
• Ctrl + V pastes what you copied into the selected cell.

On a Mac, press ⌘ instead of Ctrl. The same commands are in the right-click menu.

Copied cells get a moving dashed border. As long as you can see it, you can paste as many times as you like, and the Esc key removes it.

Sometimes a number in a cell isn't typed in by hand but calculated by a formula: an instruction Excel uses to work out the number by itself. On the sheet below, the total in B5 is calculated that way. If you paste a cell like that the usual way, the formula moves to the new place instead of the number, and there it calculates something else. To paste the number itself, right-click and choose Paste Special → Values.