Abstract
Transcript
Resources to learn Excel shortcuts
- 200 Excel shortcuts for Win and Mac - online & updated frequently
- Laminated quick reference cards - handy reference, no wifi required
- Excel shortcuts on the Mac - important if you use Excel on a Mac
- Shortcut video training - learn 200 Excel shortcuts with bite-sized videos
Transcript
In this video, we're going to take a quick tour of over 30 popular Excel shortcuts. We put together this list based on a survey of more than 800 Excel users.
All right, so here we are in Excel, and now I'm going to walk you through some very popular and useful Excel shortcuts.
First, since we have a lot of tabs in this workbook, you should know that you can move right through tabs with control page down, and left with control page up. On a Mac, you can also use option with the right and left arrow keys
Now, the most popular shortcuts by far in Excel are copy and paste. You can copy with control C and paste with control V. On a Mac, you can use either the command key or the control key for many of these shortcuts.
Another very useful shortcut you'll see me use often in this video is undo, control Z.
In addition to control V to paste, you can also use Enter to paste. On a Mac, add the function key.
To cut instead of copy, use control X. Then paste normally.
One of the most important shortcuts in Excel is paste special, which is control alt V in Windows and control command V on a Mac.
Before we try it, let's first check the formulas on this worksheet using control + grave. The shortcut toggles the worksheet to show all formulas, and numbers without any formatting.
Just use the same shortcut again to toggle off formulas.
Most often, paste special is used to strip out formulas and formatting. To do that, use paste special with values. Once you're inside the dialog, you can type the underlined letters to select options, so in this case V for values.
The result is just values without formatting or formulas.
There are lots of things you can do with paste special.
You can keep number formatting, but get rid of formulas.
You can keep formulas and number formats, but strip everything else.
And you can also use paste special to copy only formatting, which is useful when you paste data below a table, and you want to quickly extend the formatting.
To do this, just select a row with the formatting you want. Copy, and use paste special with formats.
Paste special has many other useful options, and we cover these in detail in our course on Excel shortcuts.
Entering the current date and time is another task that you might need to do often in Excel, and there are shortcuts for both.
For the current date, use control + ;
For the current time use control shift :
If you want both, just separate with a space, and Excel will recognize this as a valid date-time.
Although you can double-click a cell to edit, you can also edit a cell with your keyboard using F2 and Windows, and control U on a Mac. This allows you to make edits to cells, without taking your hands off the keyboard
Next, let's look at some shortcuts for navigating a worksheet. First, no matter what cell is selected in a worksheet, you can go back to the first cell with control + the HOME key.
To move to the last cell in a worksheet, use control + the END key.
Inside a table, you can use control + an arrow key to travel through the data in any direction. In this way, I can move quickly to all four corners.
To select data with these same shortcuts, just hold down the shift key. This allows you to easily select rows, columns, and even an entire table.
When you're in a table, you can also select everything with control A.
Once you've made a selection, remember that you can extend the edges of the selection by holding down the shift key and using arrow keys.
To select entire rows, use shift plus the spacebar. To select entire columns, use control plus the spacebar.
Again, you can use shift to extend the selection as needed.
Excel also has keyboard shortcuts for inserting and deleting rows and columns. By itself, control shift + will bring up the insert dialog with options to insert entire rows or columns. control - does the same thing with the delete dialog.
However, you can insert and delete much faster if you first select entire rows or columns. This lets you delete and insert without a dialog.
Whenever you're working with data in a table you can make these same shortcuts much more powerful by converting the data to an Excel Table using the shortcut control T.
Now the same shortcuts for selecting rows and columns will only select data inside the table. As before, control A will select the entire table but now it won't include the headers.
The power of tables becomes clear when you have to work with a really large table, like this one here with 3000 rows and 36 columns.
After I convert the table to a proper Excel table, notice that the headers will automatically be visible, even if I move down to the bottom of the table.
On top of that, I can select full rows and columns easily, knowing that the data is always perfectly selected.
No list of Excel shortcuts would be complete without autosum, a handy shortcut for quickly summing numbers with the sum function. Autosum Alt = in windows and command shift equals on a Mac.
You can use autosum to sum both rows and columns. You can even autosum an entire table in one step.
One of the things that you do most often in Excel is enter data, and I've got two shortcuts to show you that can really help you with this.
The first is fill down or control D. Fill down is an excellent shortcut for quickly getting data from cells above without using copy and paste.
You can use fill down on one cell at a time, or with multiple cells. You can even use fill down on multiple columns at the same time.
Fill down is a really handy shortcut, but you can speed things up even more if you enter data in multiple cells at the start. To do this, just make a selection, type some data, and use control enter instead of just enter.
control enter works fine for formulas as well. The relative cell references will update normally.
So, speaking of formulas, let's look at a shortcut for toggling absolute and relative references.
In this worksheet, I want to summarize sales by color, so I can use the SUMIF function.
To do that, I'll need to enter a range and criteria, and then some range. But notice that if I don't lock down certain references, the ranges will shift as the formulas are copied down.
So to prevent this I need to lock the ranges by making them absolute. The shortcut for this is F4 on Windows, command T on a Mac and this will toggle through the absolute and relative options.
Now when I copy the formula down, the range references are fixed properly.
Excel also has dedicated shortcuts for hiding and showing rows and columns.
So, you can hide columns with control 0, and you can hide rows with control 9.
To unhide rows and columns again, just select data that includes the hidden rows or columns, and use the same shortcuts with the shift key held down.
When you have rows or columns hidden and you want to copy and paste only the visible cells notice that you'll end up with everything by default if you just do a normal copy paste.
To fix this, you need to select the visible cells only first, and to do that use the shortcut alt semicolon and windows, and command shift Z on a Mac.
Then copy and paste normally.
We already looked at the shortcut to create a new table, Control-T, but we didn't talk about filters.
New tables automatically get filters, and to toggle these filters on and off you can use the shortcut control shift L on Windows, or command shift F on a Mac.
When one or more filters are actively filtering data, this same shortcut is an excellent way to reset a table. Just use the shortcut twice in a row.
Okay next, two shortcuts useful for formatting.
The first is format cells, or you can think of it as format anything. Just use control 1 in Windows, or command 1 on a Mac.
You can use this shortcut to access formatting for almost anything. The beauty of the shortcut is that you can access this formatting from anywhere without worrying about the ribbon.
And it works great for objects and charts as well so not just for cells.
The next shortcut is repeat an action to do this use F4 or control Y on windows, and command Y on a Mac.
Not all actions can be repeated, but you can add the repeat command to the quick access toolbar, to help you understand when you can repeat an action, as I've done in this case.
So, for example, the last thing I did was apply a border. So I can use repeat to apply the same border to the second table.
You can also use repeat for actions like inserting or deleting rows or columns, or inserting sheets
Okay finally things up with a shortcut that puzzles many people, the shortcut to add a new line inside a cell. On Windows, use alt enter and on a Mac use control option return.
If you have any trouble, make sure that wrap text is enabled.
