Shortcuts — Spreadsheets Shouldn’t be Scary (Part 6)

Spreadsheets can be pernickety. Clicking on those tiny rectangles can cause a lot of frustration. Understanding some nifty shortcuts can really make your life easier.

(If some of the terms in this blog are not familiar to you, check out our previous blog post or short and sweet video on Spreadsheet terms — we have one for Google Sheets and one for Microsoft Excel)

These shortcuts only work on devices with keyboards (either stand alone or screen pop ups).

Undo

We all make mistakes, it’s useful to learn how to correct them. If you can a mistake, simply click:

Ctrl / Cmd + Z

Note: if you are on a Windows or Linux device then throughout this blog you follow the first part (Ctrl + Z), this means press the Control button, keep holding this button down, and please the Z button. If you are on Mac, same instructions, but you follow the second part (Cmd + Z), press the Command button, hold down, then press Z.

Another useful version of this is Redo — in case you need to undo your undo… To redo something you have undone:

Ctrl / Cmd + Y

Quick Reminder of Some Basic Shortcuts

Save: Ctrl / Cmd + S

Save as (if you want to rename or change the file type): Ctrl / Cmd + Shift + S

Copy: Ctrl / Cmd + C

Paste: Ctrl / Cmd + V

Cut: Ctrl / Cmd + X

Print: Ctrl / Cmd + P

Navigating Around the Spreadsheet

Move up / down sheet / tab

To navigate between your sheets (or tabs), the shortcuts are quite different on different devices:

Windows / Linux: Ctrl + Page Up/Down
Mac: Alt + Left / Right arrow keys

Move to the Extremes

Sometimes you need to move to the end of your data:

Ctrl / Cmd + arrow key

In this case, you press the arrow key in the direction you want to move. It is important to note this shortcut will stop at any empty cells, so the arrow keys may need to be pressed a few (or many) times to reach the actual extreme.

For some extreme (ha!) shortcuts:

Move back to the top most left cell of your tab (A1):

Hold down Ctrl / Cmd until your are in A1
Press the up arrow (until you reach the top)
Now release the up arrow and press the left arrow (until you are in A1)
[Note: you can either hold the arrow keys until you reach when you desire, or repeatedly press them)

Find the furthest right (or down) cell with data:

If you data is fully complete with no gaps or missing data points, this is very easy:

Ctrl / Cmd + right arrow key (or down arrow key)

If you data has some gaps, simply repeat (or keep holding this down). If you go too far and are at the very end of sheet / tab, you can just reverse one, i.e.

Ctrl / Cmd + left arrow key (or up arrow key)

Note: the same can be done for further left and up, but good spreadsheet practice should make this obvious…

Find any gaps in your data:

The commands to find the extremes can be useful for finding gaps in your data, as they will stop if the row or column you are moving through has any cells with nothing in them.

Highlight / Selecting

If you want to do more than just navigate to the extremes, you want to select as you go, through in a press of the shift key, and follow the instructions above:

Ctrl / Cmd + Shift + arrow keys

If you want to highlight only a few cells, not all your data, left out the control / command, and hold it down until the desired cells are blue:

Shift + arrow keys

Again, if you want any errors you can use the opposite arrow key to reverse. The arrow keys can also be pressed in any order, just keep pressing the other keys.

Select the whole sheet / tab

If you want to select (or highlight) the entire sheet or tab you are in:

Ctrl / Cmd + A

Note: if you data has gaps you may need to press this shortcut a twice to select the whole sheet / tab.

Selecting an entire sheet or tab when the data has gaps

Select Row

If you want to select an entire row, click on any cell in the row:

Shift + Space

To select additional rows, you can then use

Shift + up / down arrows

Select Column

Similarly to selecting a row, you can select a column by selecting any cell in the column:

Ctrl + Space

To select additional columns, you can then use

Shift + left / right arrows

Advanced Copying and Pasting

Any of the shortcuts in the previous section can be combined with the basic copy, cut and paste commands. But sometimes you need something more advanced to get your desired effect.

Pasting the values only

Have you ever pasted something copied from another spreadsheet or the internet and it goes… a bit funny? It’s the wrong font, size, colour. It messes up your neat spreadsheet. Well the trick to avoid this is to paste the values only (not the formatting).

On Google Sheets (or Microsoft Excel Online):

Ctrl / Cmd + Shift + V

On Microsoft Excel Desktop (i.e. not in your internet browser):

Alt + E + S (to make the Paste Special pop up appear)

Then press V.

Then press Enter.

The difference between Paste and Paste Values Only

IMPORTANT NOTE: pasting values only removes any formatting, but also when copying formulas, it pastes the value displayed in the cell, not the formula itself. Be careful with this.

Copy Down / Right

If you need to copy the same value (or formula) down or right to a certain cells, you can highlight the cells you need (remember, using Shift + down / right arrow), then press:

Ctrl / Cmd + D / R

There is another way to copy data and formulas down columns in spreadsheets, called Replication. We have a video and blog coming out about this soon!

— — — — — — — — — — — — — — — — — — — — — — — — — —

Part of our mission over at Taught by Humans is to provide education for humans. By humans. Powered by tech. Recently we’ve been focusing on Spreadsheet Skills. We have a few ways to learn about spreadsheets:

Short and Sweet — up to 5 minute videos explaining a specific topic or task.

Courses — longer courses which can be taken online at your own pace.

Bespoke — training tailored to your needs as a business or individual.

Come learn with us as spreadsheets shouldn’t be scary.