One sign of an expert Excel user is mastery of keyboard shortcuts. There are hundreds of shortcuts out there, and if you use them effectively, you may find you don’t even need a mouse to work in your Excel spreadsheets anymore.
Here’s a cheat sheet with 6 Excel shortcuts, tips and tricks that are bound to come in handy. Because whether you’re trying to navigate your spreadsheet, generate formulas or analyze cell references, there’s (probably) an Excel shortcut for that.
1. Easily Switch Between Tabs
This tip is useful when you’re working on an Excel spreadsheet with multiple tabs. Instead of using your mouse to go from one tab to another, use the following keyboard shortcuts to get there faster.
- CTRL + PAGE UP: To move one sheet to the left, hold down the CTRL key and press the PAGE UP key.
- CTRL + PAGE DOWN: To move one sheet to the right, hold down the CTRL key and press the PAGE DOWN key.
Each time you use either of these shortcuts, you’ll move one tab over in the direction of your choice.
2. Select Rows or Columns
You don’t need a mouse to select rows or columns anymore. It may not seem like much, but using your keyboard to make selections is convenient.
- SHIFT + SPACEBAR: Go to any cell in the row you want to select. To select that row, hold down the SHIFT key and press the spacebar. If you want to select multiple rows, hold down the SHIFT key and use the up or down arrows to select the row(s) above or below it.
- CTRL + SPACEBAR: Go to any cell in the column you want to select. To select that column, hold down the CTRL key and press the spacebar. If you want to select multiple columns, hold down the SHIFT key and use the right or left arrows to select the column(s) before or after it.
3. Insert and Delete Rows
After you select your rows or columns, use the following shortcuts to insert or delete the rows or columns you just selected.
- CTRL + “+” (plus key) or CTRL + SHIFT + “+” (plus key): To insert rows or columns, hold down the CTRL key and press the plus key. If that doesn’t work (sometimes I find that shortcuts are slightly different depending on whether you’re using the regular keyboard or the number pad), another option is to hold CTRL and SHIFT keys and press the plus key.
- CTRL + “-“ (minus key): To delete rows or columns, hold down the CTRL key and press the “minus” key.
It warrants repeating that this shortcut is most effective when used in conjunction with shortcut #2. For example, to insert rows in your spreadsheet, first use the SHIFT + SPACEBAR shortcut, then the SHIFT and arrow keys shortcut, to select the number of rows you’d like to insert. Then use the CTRL + “+” (or CTRL + SHIFT + “+”) shortcut to insert new rows. These rows will be inserted right above the rows you’ve selected.
4. Lock Cell References
Locking cells is useful when you’re working with a lot of formulas. If you’re copying and pasting a formula and want a cell reference to remain locked, you can add dollar signs before the row or column reference. But because manually adding the “$” signs in your formula will likely take some time, this following shortcut makes it easy.
- F4: Go to the cell with the formula you’re working on (you’ll need to navigate specifically to the cell reference you want to lock), then press the F4 key once to lock both the row and column references. If you only want to lock the row reference, press F4 a second time. If you only want to lock the column reference, press F4 a third time. Press the F4 key one more time to remove the locks altogether.
The next time you copy the formula in that cell, those references will stay locked regardless of where you paste it.
5. Sum Rows or Columns
You probably already know the =SUM formula sums up the cells you select. There’s an easier shortcut for this too.
- ALT + “=”: If you have a list of numbers you want to sum up, go to the empty cell below your column of data. Then hold down the ALT key and press the “=” key. This also works when you have a single row of numbers. Just go to the empty cell beside your row of data, and use the ALT + “=” shortcut.
If needed, note that you can also highlight the cells you need summed up, then use the ALT + “=” shortcut. This specifies which cells to include in the =SUM formula.
6. Jump to Cell References in Formulas
One of my most used shortcuts: jumping to cell references. It’s useful when you’re looking at a complicated formula or when you’re working on a workpaper someone else prepared.
If you see a formula and want to jump to a cell it’s referencing, you can manually search for that cell. Or you can use a shortcut for it.
- CTRL + “[“: Click on the cell with the formula you’re working on. Hold the CTRL key and press the “[“ key to jump to the first cell being referenced in your formula. It works even if that cell is on a different tab, which is what makes it so helpful.
- F5 + ENTER: After you jump to the referenced cell, you can jump back to the original cell that contains the formula by pressing the F5 key, then pressing ENTER.
How to Use These Excel Shortcuts Effectively
I admit these shortcuts may not be easy to understand via text. The best way to learn how to use them is to actually test them out in Excel. Give them a try – I use these shortcuts every day as an accountant, and they’re both convenient and efficient.
For Mac users who utilize Excel, note that shortcuts for Macs often differ. Most of these shortcuts are specifically for PC users. If you’re interested in Excel shortcuts for Mac users, let me know in the comments below.
1 thought on “6 Helpful Excel Shortcuts For Accountants”