Have you seen those guys in the finance department of your office? They practically breath, drink and eat excel. Have you seen them work? Their fingers pretty much fly all over the keyboard and all you will see is tables expanding and collapsing, formulas and charts appearing out of thin air on their screens.
Basically, it’s practice. Their work revolves around excel. They have to be efficient at getting around excel and so they get familiar with keyboard shortcuts.
You might think, “Whatever can be done with excel shortcuts, I can do it better with a mouse”, but you’re wrong. Going for the mouse every now and then decreases your efficiency. Again you might think, “Big deal it hardly takes a second to reach the mouse”, but then, you also spend time in clicking and navigating to your desired option. It all seems miniscule but multiply it with the number of times you do it for a single task and you will realise that you are actually wasting your time reaching out for the mouse and navigating.
That’s where Excel keyboard shortcuts come in
While you are putting your data and preparing your sheets, it’s best to keep your hands on the keyboard. How do you manage that? By making use of the excel keyboard shortcuts.
While practicing excel keyboard shortcuts, I highly recommend that you disconnect the mouse from the computer before you begin. This will be frustrating at the beginning but it will force you to stick to the keyboard.
Ctrl + Direction key
This will help you jump from data to data. Let’s say, we have data in a table that looks like this.
While I have my cursor at B2, pressing ctrl + down arrow will take you straight to cell B3. Another ctrl + down arrow will move to B5. See what happens?
Between B4 and B5 lies and empty cell B3. Using the ctrl + down arrow button will skip the empty data and navigate straight to the next cell which has data which is B5.
Now, having the cursor at B5, can you guess what happens when you press ctrl + down arrow? B6, yes. That’s where the cursor will navigate to. One more ctrl + down arrow will take me to B8 and another one will take me to B10.
And not just ctrl + down arrow. You can use this mode of navigation in all directions. This is especially helpful when you are working with large table with a lot of rows and columns.
Shift + Direction key
After placing the cursor on a particular cell, shift + will help you select cells as you away form that particular cell.
Ctrl + space
This will help you select an entire column
Shift + space
This will help you select an entire row
Combo techniques
Things are starting to get serious now. We will combine the Excel keyboard shortcuts that we just learnt to move even faster around the work sheet.
Ctrl + Shift + direction key
We just saw that Ctrl + direction key, jumps to the next cell with data, shift + direction key selects cells. The combo of ctrl + shift + direction key will jump to the next cell with data while selecting all the cells in between.
Ctrl + Space followed by shift + left or right arrow
Taking the sample worksheet, if I were to place the cursor at B5 and press ctrl + space, I will be selecting the entire column B. After which, pressing shift + left arrow will expand the selection to column A. Likewise, pressing the shift + right arrow will select column C. You can hold down the shift key and continue to press the direction keys to select as many columns as you want.
Shift + space followed by shift + up or down arrow
I am sure you are beginning to guess what’s going to happen by now. So, If I were to place my cursor at B5 and press shift + space, I will be selecting the entire row 5. After which pressing shift + up arrow will expand the selection to row 4 and pressing shift + down will expand to row 6. You can continue to press the up or down arrow as many times as needed to select multiple rows.
Ctrl + shift + space
This one is like a super combo. So, we know that ctrl + space will select an entire column and shift + space will select an entire row. Curious to find out what will they do together? If you’ve guessed an entire table, you are absolutely right. So in this example,
if I were to place the cursor in any of the cells and press ctrl + shift + space, all the cells across A1 to H10 will get selected.
Alt + = followed by the enter key
Summation of large values is something that you would like to do in a hurry. Let’s say I want to make a sum of all these big numbers.
There are a lot of ways of summing this data. Some select the data and look at the bottom right corner of the screen for sum, count and average. Some type in the bottom cell “=”and then select each cell with the mouse and some use the “=sum(“ formula. But then, there is one little short cut that calculates the sum almost as fast as a calculator.
So for this example, I will place the cursor in A18 and press alt + =
and immediately hit enter.
In less than a second you have calculated the sum with a keyboard short cut combo.
Paste special
One Excel cell can hold a lot of information. Font, format, formulas and a lot more. When you copy or cut an excel cell you are copying all that information and when you paste it on another cell, you are pasting all of that data.
For example, I want to make a sum of these numbers.
I place the cursor in cell A7 and hit enter to get the sum:
Now, if I copy cell A7 (for which the short cut is ctrl + c by the way) and paste it in C7 (which is ctrl + v) you will not be pasting there 834756 you will end up pasting the sum formula which is in cell A7 and therefore, after pasting what you see in C7 will be the sum of the numbers in column C.
This is how the default copy paste function in Excel works. But what if I want to paste the value 834756 and not the formula. That’s where paste special comes in.
Copy the cell A7, use the direction keys (avoid touching the mouse, get used to navigation in keyboard) to get to cell C7 and in consecution type alt -> e -> s. This will open the paste special menu.
Under paste function, notice that the values radio button has the v underlined. On your key board press v and automatically, the values radio button will be selected. Press the enter key and now you will only paste the value 834759.
So if you do it all in consecution alt -> e -> s-> v -> enter, you will be performing the paste special command selecting only values in less than a second.
It’s a start
There are more such nifty little Excel keyboard shortcuts that will make you a lot more efficient at Excel tasks, which we will be discussing in future. For now, practice these shortcuts while forcing yourself to not use the keyboard. You will feel that it’s slowing you down in the beginning but once you get used to them you will be instantly recollecting the shortcuts as you work and dishing them out at lightning speed.
Want to improve your investing with excel? Join our top-rated professional diploma in financial trading & investment today!
