Working with lists? Use Freeze to fix key columns and rows when you are scrolling

Make sure you’re in the correct cell depending on which app you’re using:

Excel

Click in the cell below the row and in front of the column you want to freeze.
Then View > Freeze Panes > Freeze Panes.

Sheets

Click in the cell in the row and the column you want to freeze up to and including.
Then View > Freeze > Up to row # and Up to column #.

Need to apply the same formatting to non-consecutive cells?

Excel / Sheets

Select the first cell/block of cells, then hold the CTRL key on the keyboard while you select other cells. This also works to select non-consecutive cells for functions such as SUM!

Want to copy formatting from one cell to other cells?

You can do this in an instant – in Excel you use the Format Painter, in Sheets, it’s called Paint Format.

Excel

Select a cell containing the formatting you like. Click on the Format Painter button on the toolbar. Then highlight cells you want to copy the formatting to.

Sheets

Select a cell containing the formatting you like. Click on the Paint Format button on the toolbar. Then highlight cells you want to copy the formatting to.

Need to split data from one column into multiple columns?

Ensure you have blank columns ready for the split data. Select the column to be split. Then:

Excel

Choose Data > Text to columns

choose a separator (space in this case) then click finish

Sheets

Choose Data > Split text to columns

choose a separator (space in this case) then press the Enter key

Copy the contents from the cell above

You don’t need to copy and paste or fiddle around with the mouse, just use a keyboard shortcut:

Excel

Ctrl ‘ (note on a UK/US keyboard this is the apostrophe to the right of the semi-colon key)

Sheets

Ctrl D

View two places in a workbook at once

Excel

You can either :

a) Split the view of the current worksheet in two by highlighting a column or row then clicking View > Split

you can now scroll the two parts of the workbook separately

or –

b) View anywhere in the workbook, including a different worksheet, by having two windows open side by side – click View > New Window,

then View – Arrange All

two copies of the workbook now appear in different windows that can be navigated independently

Sheets

There are no menu options in Sheets as there are in Excel, how there is a simple solution to this – you can replicate either of the above by simply having two browser windows open at once side-by-side, with the same URL in each!

Editing keyboard shortcuts

Excel / Sheets

if you don’t know this one you really ought to…

Ctrl X to cut

Ctrl C to copy

Ctrl V to paste

(these work on most other apps too – if you right-click you’ll usually see them appear as options)

Clear formatting from cells

Excel

In the Home tab of the ribbon click Clear – Clear Formats

Sheets

From the menu select Format – Clear Formatting

Select from a cell to the end of a row or column in the current data set

Excel / Sheets

Click on the starting cell, then hold the Shift & Ctrl keys and press the relevant arrow key (up, down, left or right) to select the cells. It will stop the selection when it reaches the end of the data set or hits a gap.

(Example 1: user has selected cell D6 and pressed Shift Ctrl ⇒)

(Example 2: user has selected cell D6 and pressed Shift Ctrl ⇓)

Select the whole data set

Excel / Sheets

Click anywhere in the data and press Ctrl A on the keyboard to select the entire data set