If you spend a lot of time using Microsoft Excel at work, learning some tricks will help you get more done and get results to your boss faster. And, hopefully, that will result in some promotions and bonuses for you.
So don’t take this lightly. Learning shortcuts and formulas can feel tedious at first - as you’ll want to do it the way you’ve always done it - but, once you get used to them, you’ll feel better about your skills, proud of your expertise, and wondering how you ever used to do it the old way.
Some Basics
Freezing Panes
Freezing panes is a basic Excel trick that is important for easy analysis of data. You can freeze any number of rows to the top of Excel or columns to the left of Excel so, when you’re scrolling, they’ll remain at the top and left of the screen. This is perfect for header rows and columns describing the data. You can find this option in the “Window” Menu.
Auto Fill
If you have a sequence you need to enter over several rows or columns, you can use “autofill” to save a lot of time. For example, if you have to enter 1-100 in column A, first enter “1” in A1, “2” in A2, and “3” in A3. Then highlight all 3 cells. Move the cursor to the bottom right corner of cell A3 and you’ll see the cursor turn into a “+” symbol. Once it does, click and hold the mouse as you drag it down to cell A100.
It will auto fill the rest of the cells, automatically continuing your pattern of increasing by 1 in each cell. You can do this with other number patterns, months, days of the week, and more.
The Most Time-Saving Keyboard Shortcuts
The ‘Shift’ Key
One of the most important keys on the keyboard while using Excel is the ‘Shift’ Key. If you hold down the shift key and use the arrows to move from cell to cell, you’ll begin to highlight a range of cells. This allows you to copy and paste ranges of data very quickly.
The ‘Shift’ Key is even more powerful when combined with the ‘Ctrl’ Key. If you hold down both ‘Ctrl’ and ‘Shift’ and then use the arrow keys, you will select all the data up until the last populated cell. For example, if you have data in cells A1 to A10, B1 to B10, and C1 to C10 and your cursor is in cell A1, you would:
-
Hold down Shift + Ctrl.
-
Press the down arrow key while holding those two keys. (This selects A1 to A10)
-
Then press the right arrow while still holding down Shift + Ctrl. (This now selects B1 to B10 and C1 to C10 as well)
So with just a few keystrokes, you selected three columns of data. This saves a lot of time when copying data or formulas from one row or column to another.
Ctrl + Arrow
If you leave the ‘Shift’ key out and only use Ctrl with one of the arrow keys, you’ll jump to the end of the data without selecting it. This is helpful if you have a large amount of data and just want to get to the bottom or right of it.
Shift + F10
Sometimes, you’ll get in a groove with your keyboard in Excel when you realize you need the right-click menu. So, you reluctantly slow down your process and go for the mouse. Well, no more. Shift + F10 opens the right-click menu for you. Then, you just need to use the arrow keys to navigate the menu and the Enter key to pick the option you need.
F4: Repeat the Last Task
To give you an example of the power of the F4 key for productivity, it can be expertly used for inserting or deleting rows. Each time you want to insert a row, you need to right-click on a row and select Insert. If you need to insert multiple rows, you can quickly do that by hitting F4 after you’ve inserted the first row. It will repeat the last action you performed, which was inserting a row. This also works extremely well for deleting rows but also works for most actions.
The Most Useful Formulas
SUM
If you’re working with data, SUM is the most useful formula and it’s easy to use! If you have data in cells A1 to A10 and want to sum them up in cell A11, you would type (in cell A11), “=SUM(A1:A10)” and it would do the work for you.
Text Formulas
Sometimes, you might be working with large amounts of text data and you might find yourself needing to perform the same action on each piece. Imagine having 200 rows and needing to remove the last three characters from each piece.
LEN: To get the length of a text string in cell A1, you would type “=LEN(A1)”
LEFT: LEFT returns a specified number of characters starting at the left of the text.
-
It requires two parameters: LEFT(Cell the text is in, # of characters you want returned)
-
For example: LEFT(A1, 5) would return the 5 leftmost characters in A1.
If you needed to remove the last three characters from cell A1 without necessarily knowing how many characters are in cell A1, you would type =LEFT(A1, LEN(A1) - 3).
That will return three characters less than the length of the string in A1, leaving off the last three characters.
RIGHT: RIGHT works exactly as LEFT does, just returning characters starting from the rightmost of the text.
So there you have it. Three categories of Excel tricks (Basics, Shortcuts, and Formulas) that will have you working more quickly and more easily finding the answers you’re looking for in data. This will make you, your boss, and your paycheck happier. Take the time to practice with each of them and they will soon become second nature for you.
