It’s time to get evangelical peep! Release yourself from the yolk of the menu bar and it’s insidious demands on you coordination and time!
No? Fair enough…
Let’s just get on with it then, shall we? After all, this is about efficiency, right?
Here are the 5 main shortcuts that I use in Google Sheets each and every day to save me a tonne of time. I’m going to give you a bit more than just the Keyboard Shortcut, I’m going to demonstrate how I use it with some clear examples.
There are some infinitely better ways for you to navigate around your data in Google Sheets than clicking and dragging the scroll bar or…ghasp!!!…using your arrow keys on each and every cell.
1.1 Horizontal Scrolling
This first one is not exclusive to Google Sheets. It is a universal tool for all your programs and apps.
You know how you can scroll up and down your screen by using that handy dandy scroll wheel on your mouse? What if I told you that you could do the same horizontally (or left to right)?
All you need to do is hold down shift while using the mouse wheel.
shift + mouse wheel
1.2 Top, Bottom, Beginning, End
We quite often find ourselves in a position where we need to quickly get to the start or end of data in a row or column or an entire document.
To navigate data only, we use the ctrl + arrow keys (⌘ + arrow keys, for Mac).
We’ll go ahead and use the PC “ctrl” key, for these examples but Mac users will know to replace this with the command ⌘ key.
|ctrl + →||Takes you to the right-most column of data if you have clicked a data cell. If you have clicked on an empty cell, then the shortcut will take you to the end of the spreadsheet.|
|ctrl + ←||Takes you to the left edge of the spreadsheet if you have clicked a data cell. If you have clicked on an empty cell, then you will be taken to the first piece of data to the left.|
|ctrl + ↑||Takes to to the top-most row of data if you have clicked a data cell. If you have clicked and empty cell, then the shortcut will take you to the next available row with data above your clicked cell.|
|ctrl + ↓||Takes you to the bottom of your data if you have clicked on a data cell. If you have clicked on an empty cell then it will take you to next data cell below.|
2. Copy and Paste
The copy and paste short cuts are classics and a pretty much universal for all apps and programs.
Copy: ctrl + C (⌘ + F for Mac)
Paste: ctrl + V (⌘ + V for Mac)
2.1 Paste Values Only
But did you know there is also a quick short cut for pasting values only? Yep: ctrl + shift + V (⌘ + shift + V for Mac)
This will clear all the formatting and formulas out of what you are copying and paste them into the cell. This is handy if you are copying from something that is outside of Google Sheets and pasting it in.
I also find this incredibly useful to copy and paste values over formulas that I wont need again. When you paste values only the values you paste will take on the formatting that already exists in the cell.
2.2 Paste Values Multiple Times
Google Sheet also lets you copy a value or formula and then paste the same value or formula in multiple cells.
Simply copy the cell (ctrl + C) (⌘ + C for Mac), then select the cells you want to paste the cell into and hit ctrl + V (⌘ + V for Mac) to paste them.
3. Selecting Data
I often find myself in a situation where I need to select whole rows or columns, or even the whole data set.
3.1 Select Columns and Row
To select columns and rows simply click on the column letter or row number.
3.2 Multiple Selection
For multiple selection of adjacent rows or columns in Google Sheets you can click and drag your cursor over the rows or click your starting row or column, then hold shift + left click the row or column you want to stop at.
If you need to select rows or columns that are not always adjacent, then you can use ctrl + left click (⌘ + left click for Mac).
Bonus Round: I have also discovered over the years that you can select adjacent rows or columns with shift + left click first then go on and click other non-adjacent cells with ctrl + left click. You can only, however use the shift + left click once in this scenario.
3.3 Select Cell Ranges
You can also select data ranges by combing ctrl + shift + arrow keys.
3.4 Selecting All Data Ranges and Whole Sheets
To select the entire range of data use ctrl + A. Do it again and you will select all the cells in the Sheet.
Find is also another common gem. You can search for words in your sheet by using the following shortcut.
|PC||ctrl + F|
|Mac||⌘ + F|
The Google Suite has its own internal Find ability and you must make sure your page is fully loaded before the find shortcut will work properly.
4.1 Find with Select
Usually when I am using find I am looking for a name or an I.D. and then find a grade or a cost or something. To do this I usually search for the text or value with ctrl + F, type in the value and hit enter . Then I click the column or the row so it helps guide my eye to the grade or cost I actually want. Like this:
4.2 Find and Replace
Find and replace is a powerful tool to strip the time it takes from doing mundane and repetitive changes to data. It might not look like much on the surface but it can be your biggest ally in efficiency. So much so that I will go over the wonders of Find and Replace in detail in a future post.
For now lets just look at what it cant do. Let’s say we want to change any value that is a 14 to a 15 in our example. I have highlighted the 14’s in the image below so you can easily see it.
The shortcut for Find and Replace is ctrl + H (⌘ + H for Mac):
You can also refine your Find and Replace to the Sheet you are working on or even a range of selected cells.
5. Duplicating Formulas Down a Row or Column
This one is another gem. Often times you want to repeat a formula down a column or row. You can do this quickly in a number of ways.
You can also do the same thing by starting with the formula cell and dragging the selection down or across your cell and then click ctrl + enter (⌘ + H for Mac).
How about we add an average to the bottom of our example sheet or each column.
Warning: Sometimes you might find that your formulas are not going as expected when you duplicate them in this way. This is usually because you may need to lock a reference cell or just the row or column so that it does not move when you duplicate it.
To find out more about this check out my post:
There you have it. My top five most use short-cuts that I used each and every day.
The best way to memorise these shortcuts is to get out there and practice right now.
If you want to know more shortcuts for Google Sheets, there is a shortcut for that too ctrl + / (⌘ + / for Mac).
Have you found any of these useful? Any surprises? Do you have a different top five? Are you ready do convert the masses?
Let me know in the comments below.