I'm really excited about today's video. It's a highly requested
video on Excel shortcuts that you should know. Your
colleagues should know. Anyone who works with Excel should know. Let's get started. (upbeat music) Click anywhere inside your
data set, use Ctrl+Shift+L to add a filter to this, and then you can go about using the usual filter options, filter for names, press
enter, that's my list. To deactivate the filter,
click inside the data set, Ctrl+Shift+L takes it away. It's a toggle Ctrl+Shift+L puts it back Ctrl+Shift+L takes it away. Click anywhere inside the
data set and press Ctrl+T. The table has headers, click on OK. Design tab is activated, you
have a lot more table options. You can decide on the style you want, you can add a total row to this, summarize with a pivot
table or remove duplicates. Now there are many more
advantages to tables. If for some reason you want to
convert this back to a range, just click on convert to range. To jump from one side of
your data set to the bottom, use Ctrl+Down arrow. To jump to the right, Ctrl+Right arrow To jump to the left, Ctrl+Left arrow Jump back up, Ctrl+Up arrow. This is much faster than
using the scroll bar. Now if you wanna jump to the bottom but highlight everything in
between, use Ctrl+Shift+Down. Ctrl+Shift+Right arrow, highlights
everything on the right side. I can click on the home tab to take me to the top of the data set
so if I had some text here and I used Ctrl+Shift+Right arrow, it highlights everything there, it doesn't jump to D,
because D is in fact empty, this information is sitting in E. If I wanted to format all of these, I'm gonna use Ctrl+Shift+Down arrow
to highlight them, Ctrl+1 which takes me to
the format cells options, I can decide on my
formatting and press enter. If I'm not using an Excel table and I wanna add a sum to this, just click on the cell below
or above your data set, use Alt+Equals sign,
that automatically inputs the sum function for you
and it tries to figure out the range that you probably want to sum, so in this case it has
correctly figured it out. If I had a number here,
and I use auto sum here, it think that I want to
add these two values, and press enter. So it's quite smart in trying to figure out what you want to sum. If you want copy and paste your formula, but you wanna have your
numbers as hard-coded, so you wanna paste it as values without the formula behind this, you need this shortcut,
so first Ctrl+C to copy, and then Alt+E+S+V to bring up
the paste special dialog box, all you have to do now is press enter, and you've pasted it as values. Now you notice it's a
difficult one to remember, the way I remember it is
Eat Some Vitamins, ESV, you just have to figure out
something that works for you. To add a new line inside
a cell, use Alt+Enter, so you have to be in edit mode because if you press enter here
it just moves down one cell, to move down a line inside a cell, press Alt+Enter and now
you can input your text, and you can adjust the
formatting as you like. To select the entire data
in the current region, click anywhere inside your
dataset and press Ctrl+A. Once your dataset is highlighted, to insert a default chart, click Alt+F1. The next one is a great one, let's say you wanna add
email addresses to this, and you wanna repeat this
pattern for the next ones It should be James.Willard and so on, I'm gonna hold down the Shift
key, and the down arrow key to highlight this area, and then I'm gonna press
Ctrl+E, that's flash fill magic. It tries to figure out the correct pattern based on the pattern that I gave it first. In this case I just gave it one option and it figured it out correctly, sometimes for flash fill to work properly you might need to give
it more sample data. To highlight an entire
row, use Shift+Space, to highlight an entire
column use Ctrl+Space. To insert a new row or
column, use Ctrl+Plus, this brings up the insert dialog box, you can select insert an
entire row, entire column, but because we already
learned a shortcut before this we're gonna use that first. So let's say I wanna
insert an entire column, I'm gonna highlight the whole column, what was the shortcut key? Ctrl+Space, Ctrl+Plus, that automatically is gonna add a column, it's not gonna ask you if
you want a row or a column. Now let's repeat for the row, Shift+Space, insert a new row, Ctrl+Plus. To remove an entire row, Ctrl+Minus. To remove an entire column, Ctrl+Minus, but if you wanna avoid the dialog box, we're gonna highlight
the entire column first, and then we're gonna do Ctrl+Minus. If you wanna drag an entire
row and drop it somewhere else, so let's say I wanna move Richard Elliot, in between Robert and Paul, what I'm gonna do is
highlight the entire row, now if I drag this and let it go, it's gonna try to replace this data but I don't want it to replace it, I wanna actually put it
in between those two. So here you drag it, but
while you're dragging it, you're gonna hold down the Shift key, and that's gonna insert
it between those two. To hide a column use Ctrl+0, to hide an entire row, use Ctrl+9. When I first receive a new
workbook from someone else, I wanna check if anything has
been hidden in there or not. The shortcut key I use is Alt+;. That highlights the visible cells only, and this way I get a visual indication that some rows and
columns have been hidden. If nothing comes up, nothing is highlight, it means that no rows or
columns have been hidden. Now let's say I've updated
this information here, I wanna add a timestamp to this, I'm gonna use the shortcut key Ctrl+;, and if I wanna add the time to it I'm gonna add a space and
use Ctrl+: and press enter. One bonus tip: when I receive a new file, and I wanna highlight all
the constants in that file, I press F5, Alt+S, O, X, Enter. This highlights all the numbers that don't have formulas behind them, so here I can see this
has a formula behind it. What I recommend you do
is to pick three shortcuts that you didn't know before, and you think you're
gonna need in the future, and practice them a few times. If there's any other Excel shortcut that you consistently use, please share it with us
in the comments below. But if you wanna learn more tips to help you design
reports faster in Excel, it's your lucky day, because
I cover nine more tips in my Excel advanced course on Skillshare, but because this class is on Skillshare, which is an online learning platform, once you register you get
access to over 25,000 classes in business, programming, Office applications, and a lot more. So you don't just learn from me, but from thousands of other instructors. Their premium membership
gives you unlimited access so you can join the classes
that are right for you, and for your career goals. So let's say if this weekend you wanted to get started learning about
data science, and Python, there's nothing holding you back. Skillshare is also very affordable, and annual subscription
is less than $10 a month. And here's the best part, the first 500 of my subscribers who use the link in the description below are gonna get a two month free trial that's gonna give you unlimited access. Click that link, sign
up, and start learning. Thank you so much to Skillshare
for sponsoring this video, and supporting my channel. I'm not sure what the
shortcut for thumbs up is, but if you know it please use it, otherwise just click that thumbs up. The list of all the shortcuts is provided below this video, print it out, keep it handy, and share it with your colleagues. See you in the next video. (relaxing music)
