After years of using Excel in a
corporate environment here are my top 25 Excel 2016 tips and tricks. Welcome to Sele training. I’m about to
show you some useful tips and tricks for Excel 2016. They’re in no particular
order so be sure to watch all the way through to the end of the video. These
tips and tricks have made me more productive and I’m sure they’ll make you
more productive as well. Take a moment and click the Subscribe button on the
bottom right of this screen or at the end of the video click on my smiling
face and as always like, share, comment, ask me questions. I’m happy to answer
every question that I receive. Now let’s get started. Number 1 – Quick Analysis
Tool. Most people aren’t aware of the Quick Analysis tool. When you highlight
any table, in the bottom right-corner is an icon. This is the Quick Analysis tool
menu. If you click on that it gives you a wealth of options you can choose from. To
modify your table for example, I can click on Totals.
Click Sum and it shows
the sum of the columns. You can click this sum and it sums the rows. You can
also do formatting on the table, add icons, create charts and insert them into
your spreadsheet, turn it into a table, and add sparklines. There’s so much
available here and it makes it quick and easy to avoid going through the menus
and manually inserting each one of these different options onto your table. Number
2 – Filter. if you’re not using filter you should be.
It’s very simple. Just click anywhere inside of a table of data, go to the Data
tab, click this Filter icon, and it creates a filter at the top of each one
of the columns. When you click on one of these it shows you all the unique
values listed anywhere in that column and you can turn them off. Select one in
particular or two or three or as many as you like. Hit OK and it filters out only
those rows of data that matched that filter.
It’s a great way to manage large
lists of information. Number 3 – Drop-Down Lists. Say you want to select a
list of values like 1 2 3 4 and you want it to be selectable from this column
right here. Go to the Data tab, click on Data
Validation, and choose List from the menu choice. In the source select the choices
that you want to make available and hit Enter. Make sure this in cell drop-down
checkbox is checked and hit OK. Now when you’re in this column and you hit the
down arrow it gives you those four choices to choose from. If you try to
type something that is outside of that range it gives you a warning that the
value doesn’t match the list. Number 4 – AutoFit Column Width.
This is by far the quickest way to adjust the width on your columns. Just go
to the space between columns so your cursor turns to this shape and double
click. It’ll automatically readjust the size of the columns to fit the widest
point of the data anywhere in that entire column.
You can also highlight all
of the columns, click on any one of them the same way, and it will do multiple
columns at the same time. Number 5 – Transpose. Let’s say you want to reverse
the columns and rows in a table. All you have to do is highlight the entire set of
data, right-click, copy, choose the location where you want to place the
results, click Paste Special, and check this box
Transpose, and hit OK. Now all the columns and rows have been reversed.
Number 6 –
Remove Duplicates. Removing duplicates is handy if you have a list of names or
other information where there’s duplicates and you want to end up with a
unique set of values. In this example I’m gonna highlight this list, click on Data,
choose Remove Duplicates. The columns are selected – first name, last name, and amount and in this case I’m going to hit OK to remove the duplicates where all three of
those columns match. That should be true for row 3 and row 8 and as you can
see it removes the 1 duplicate value. Now let’s do the same thing but in this case
we’re not going to do the amount and we’re only going to do it where the
first name and the last name are exactly the same. It now removed two
duplicates. Now be sure to use this Remove Duplicates when the results are
going to be removed. If you just want a filter you should use the Filter as we
looked at earlier because this one deletes the data that is duplicates.
Number 7 – Goal Seek. Goal Seek is an advanced function in Excel and is part
of the What-if Analysis tools.
In this example say we have a number of items
that we sell. We know how many the quantity that we’ve sold of the first
two items and the price each and what the total dollar amount is but for the
third item we want to know how many of these we need to sell in order to get
the overall total to $6,000. Well, you can punch in numbers right here randomly to
try to figure that out and then keep working it until you get to a number
that matches the 6000 that you’re targeting but you can also use the
What-if Analysis to figure it out for you. To use Goal Seek you want to start
by clicking on the target cell, go to Data, What-if Analysis, and choose
Goal Seek.
It fills in the target cell as your Set Cell. The value we’re trying
to reach is 6000 and the cell we’re going to change is the quantity for Item3. When you hit OK it goes through and calculates 197 as
the magic number to reach that goal of 6000 total. Just be aware that you can
use your imagination to come up with all kinds of scenarios for the What-if
Analysis using Goal Seek. It handles very complex solutions. Number 8 – VLOOKUP.
VLOOKUP is a very commonly used tool to find data in a list. In this example I
have a list of names with an associated ID. Over here I’m creating a new table
and I want to reference the names in this table to look up the ID from this
list and fill them in.
To do that I want to use the VLOOKUP function. Click on the
insert function button and you want to find VLOOKUP in the list. You can type it
in, do a search against all and once it’s there select it and it brings up the
function arguments. To remind you of what you fill in, for each one of these
there’s a description down here. The lookup value is what value do I want to
look up in the list, and that is the name Nancy. The list that I’m going to choose
from is the entire list and the Column Index Number is the column that I want
to return the value from. In this case this is column 1 and this is column
2 so I’m going to choose column 2. And finally, the range lookup is either
True or False. If you use True it looks for the closest match. False is an
exact match and in almost every situation you want to use False. Hit OK
and you can see that it looked up Nancy in the table and found the ID and
returned it over here.
All we have to do then, let’s copy this data down. Now
you’ll notice right here Carol isn’t in the list so it returns an N/A. Now also note
that there is an HLOOKUP function which is horizontal instead of vertical so if
you’re looking up something in a different format you may need to use the
HLOOKUP but it essentially has the same parameters. The beauty of the
VLOOKUP function is if you change a value it will update it automatically in
the table. Number 9 – Flash and Auto Fill. Flash
Fill and Auto Fill are slightly different. In this example we have some email
addresses and they fit the format of first name dot last name throughout the
list. If you start typing the name, because you want to pull it out of the
email address, Flash Fill automatically detects the pattern that you’re using
and it recommends a solution to fill it in for you. So you can hit enter and it
fills those in for the remainder.
Auto Fill is also based on patterns so if
you put a number in, for example, and you use the right corner and drag it down, it
auto fills those numbers in there. Now if I did 1, 2 and selected both and then
drag it down it sees the pattern and automatically
increments by one each time. You can do the same thing with dates, with months.
Just remember there has to be a detectable pattern and to use the
correct option on the corners. So if you type something in and you drag it, it’s
gonna copy it. If you type something in and you double-click it fills it in
automatically, matching up with the left column next to it.
And if you define a
pattern make sure you highlight both or more and then drag. Flash Fill and
Auto Fill are both really good at making you more efficient when typing information
in. Number 10 – Paste Special Values. Paste Special Values is a handy tool to use if
you have a bunch of data with formulas and you just want to copy all of that
information over to another location and get rid of all those formulas. So you can
see on this I have a formula that adds B and C together into a full name. I have a
formula that does a divided-by for the percentage on this and if I want to just
take those things and remove them and copy this to another location so I just
am left with the data, all you have to do is highlight, Copy, pick a new location,
and do Paste Special. In the list choose Values and hit OK. Now you’ll notice when
I highlight these there’s no formulas.
It’s just the value of the data. You’ll
find this to be more common than you’d think because often times you want to
remove all that formula and formatting and use the data in a different way
and this is a quick function to help you do that. Number 11 – Images in Charts.
it’s real simple to spice up a chart with images. I’ve created a bar chart
from a set of data. If you come into the chart and click at least a couple of
times to bring up the Format.
Click on this Fill & Line icon. go under Fill
and there is a Picture or Texture Fill option. If you select that, you can pick
from a file and choose an image and it will fill that image in on your chart in
place of the normal texture. Number 12 – IF function. The IF function is
very handy if you want to do conditional data representation. For example in this
chart I have a column ABCD, a column with numbers. Some of those numbers are
greater than 10 and some are less. I want to create an IF function to determine
when these numbers are greater than 10 and say “BIG” and if they’re less than 10
I want to say “SMALL” so I click on the Fx. Type in IF, choose All, and do Go
to search for it.
Bring up the IF function. The first parameter it asks for is the
logical test. What we want to know is if this number is greater than 10. If that’s
true we want to print “BIG” and if it’s false we want to print “SMALL”. Ht okay and there you have your answer. I’m going to copy this down to the other ones and now I can see the one that’s small.
Number 13 –
Insert Screenshot. If you want to insert a screenshot onto your excel page go up
to the Insert tab, click on Illustrations, and go to Screenshot. It will show a list
of active screens from other applications. Select the one you want and
it inserts the image onto the page. You can then adjust the sizing however you
want and you can also come up to the Crop and crop out portions of the image.
Number 14 – Absolute Cell Reference. Excel uses two types of referencing – relative and absolute. As you can see on this
chart C4 times D4 takes the quantity times the cost and gives a
result. This is a relative cell reference because as I look down the list it’s
referring to the second one to the left and the first one to the left in the
formula. The Total is also a relative cell reference because it is showing E4
minus F4, the discount. And as you move down the list it’s referring to the one
to the second left and the one to the left in the formula. Now if I was to add
a discount in here the formula would be this number times this number and this is a
relative cell reference reflecting the one to the left times this one up here.
The problem is is when I copy this down this one shouldn’t be pointing to this
number times G2 because there’s nothing in G2.
