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.
These shortcuts only work on devices with keyboards (either stand alone or screen pop ups).
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.
Video: Selecting an entire sheet or tab when the data has gaps
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
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.
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!