Here’s another 15 Excel 2016 Tips &
Tricks. This is a compliation of 15 additional Excel 2016 tips and tricks
that didn’t make it in my previous Top 25 Excel tips and tricks video. If you
haven’t seen it, you’ll want to click the link listed at the end of this video, in
the description below, or the suggested video shown right here. Following the
same format as my previous videos I’m going to show you some powerful tips and tricks to help you become more proficient with Excel 2016. Let’s get
started. Number 1 – Move Columns and Rows. Most people copy and paste data but
you’ve been doing it all wrong. All you have to do is click on a column, wait
till the cursor changes, and then drag it and move it. You can do it on a row
too and, you guessed it, you can even highlight a section of cells. That’s the
right way to do it. Number 2 – Name a Range. Naming a range is very helpful to
refer to sets of data. Most people go to formulas, click on Define Name. They type in the name they want and they click this button to select the data set and
hit OK. Now you have a named North data range. There’s a better method. Let’s say
in this example, I want North, South, West, and East all to be named. The best thing
to do is to highlight all of the data first then go to Create from Selection.
Choose the top row for the name value, which is the North, South, West, East. Hit
OK and it automatically creates all of the data sets with those names for you.
Remember to select the data range first. It’s a much better method.
Number 3 – Status Bar Totals. If you want quick access to some totaling, just
click on a range of cells and down in the bottom Status Bar area you’ll see
the average, count, and sum. You can even select holding the Ctrl key and
choose a variety of different things in different locations and it will show you
that average, count, and sum for those. If you right-click on the Status Bar you
can add minimum and maximum to that list as well. So now you can see those totals.
This is a quick way to see some summary information without having to create
formulas. Number 4 – Insert Multiple Lines. If you want to insert rows you
usually will highlight a row, right-click, and choose Insert and it inserts one
line. Same thing is true on columns. Right click Insert inserts one. What if you
want to do more than one. All you have to do is highlight multiple lines and then
at the top right-click and choose Insert. That puts in the number of highlighted
lines that you selected. Same thing works on columns. Save yourself some time and do multiple lines. Number 5 – F4 Absolute Reference. When you have a formula you
often use absolute references so if you put a dollar sign in front of the column
and in front of the row that makes B3 an absolute cell reference. Wo when you
move data around it’s always going to stay B3 in this formula irregardless. A
viewer mentioned a shortcut key which is the F4 function key. If I hit f4 on this
C5 it makes it an absolute reference C5. You can hit it again and it cycles
through the various choices for no absolute reference, to column and row
both, just row, just column, and back to none. That’s a quick method to enter
those absolute references. Thanks for the tip.
Number 6 – Difference Between Lists. You often need to find the differences
between two lists. It may be that you copy this information over each month or
each week and you want to compare one to the next. I’m putting them together on
the same sheet just for the example but all you do is you highlight the first
one hold the Ctrl key down and then highlight the second one. That’s fine if
it’s on a different sheet, different tab. You can select it with the control key
held down. Now that you’ve selected both lists go to Conditional Formatting on
the Home tab, choose Highlight Cell Rules, and select Duplicate Values. Now you can change this to Unique, hit okay, and now it highlights the cells that are
different between those two lists. That’s the quick method to find differences.
Number 7 – Data Entry Limits. You can restrict data that’s entered into a
specific cell. So take this A2 cell as an example. I’m going to go to Data, Data
Validation and I’m going to select from the list Whole Numbers because I want
the value in here to only be whole numbers between 60 and 250. For an input message I can say what is valid is only 60 to 250 and if there’s an error and
they select something outside of that I’m going to tell them “not a valid entry”
and hit OK. Now you’ll notice that it highlights this cell and it gives me
this information. Only 60 to 250. If I try to type
something outside of that range it gives me an error message and says this is not a
valid entry. Retry lets you reenter. It’s a great way to limit the data that can be
entered into your spreadsheets. Number 8 – Trend Line in Charts.
If you ever wanted to add a trendline to a graph Excel has an option to do this.
Let’s say I want to take this data right here and create a graph and on this
graph I want to add a trendline that shows the flow of the data. You can go
over here to this Add Chart Element, come down to Trendline and I prefer to choose
More Trendline options. That brings up the menu over here and you can choose
from a variety of Trendline types – Exponential, Linear, Logarithmic. I prefer
this polynomial one. That gives you a nice smooth line that represents the
data and that’s how you do Trend Lines. Number 9 – Ctrl Tab. Here’s a
shortcut if you have multiple spreadsheets open. You’re used to doing
Alt-Tab to switch between different applications but if you hit Ctrl-Tab
it switches you between your Excel files. Number 10 – Split/Combine Names. It’s
pretty common to have a spreadsheet with First, Middle, and Last names in separate
columns and you want to create a full name. The easiest way to do that is to
put this formula in to add the names together. This adds a space in between
each one and you end up with the full name combined. Then you can take it and drag it down for the full list. Now one step you also want to take is to
highlight that column, do a copy and paste special as values. This takes the
formulas out and leaves the data the way you want it. Now the second common thing you run into is where the full names combined and you want to break it out into First, Middle, and Last name. To do that you want to use this formula for the First name and what we’re doing is we’re taking the left of
D2 for how many characters? Well wherever we find the space in D2 – one character.
So that leaves us with John and for the middle you want to use this formula. And
essentially this formula looks for the mid string of data starting with where the first space is plus one character and
then going to where the second space is, that number of characters and then ends
up pulling out the name Lee. Big, long, confusing formula but if you copy that
text you should be able to use it in your own formulas. For the last name use this formula. We’re taking the right number of characters from where the
second space is forward and that leaves you this long formula here, which you can
copy. You can double click to copy those down and it gets all the first middle
and last names and do the same thing and copy and paste special as values. That
gets rid of the formulas and your set. Number 11 – PROPER() Function. Let’s say those names are not properly formatted. Well there is a function called PROPER.
It allows you to do the correct upper and lower case. All you have to do is
type in=PROPER, parenthesis, select the cell and there you have the right
formatting. Copy that down and you’ve fixed all of the formatting for those
names. Number 12 – Mean, Median, Mode. The Mean, Median, and Mode are common statistical calculations and you can create those in Excel. For the Mean it is
essentially the average formula so I’m going to take the average of all of
these numbers and it comes up with 141. The Median is the middle number and
there is a function called Median so if we take the median of the same set of
numbers that is the number. It is in the exact middle position of all of those
numbers. And Mode is the most frequently used number and there is a Mode function available as well. It shows 228 because 228 is in the list
twice. If you didn’t have a repeat of the same number then there would be no mode. And that’s how you use mean median and mode in Excel.
Number 13 – PMT() Function. Another common and helpful function in Excel is
the PMT function. It allows you to calculate loan payments and in this
example I’m entering in a percentage as 0.05 but formatting this as 5%. That
represents the annual rate. I’m choosing 60 months as the number of payments and an original loan amount of 20,000. To create the payment function just type
=PMT(. Its first going to ask for the rate which is
this one. Now a key you need to remember here is that it’s an annual rate and since
you’re doing a monthly payment you need to make sure to divide this by 12 so
that you have 12 months out of the year. Then you enter the number of periods,
which in this case is 60, and present value of the loan amount.
Now there’s parameters here for future value and type. We’re not going to use
those but those are available if you want more complex loan calculations. I’m going to do the end parentheses and hit Enter and there is my monthly payment
amount. It defaults to a negative number and a
quick way to change that is to put a minus in front of the formula there and
it reverses it to a positive number. That tells you that you have 377.42 per month for 60 months on a 20,000 dollar loan at five percent interest rate. You can use this kind of formula to
calculate car loan payments or a variety of other calculations. Number 14 –
COUNTBLANK() Function. Another helpful function in Excel is COUNTBLANK. Just
type in COUNTBLANK( and highlight whatever range you want to
find all the blanks. It tells you there are 7 and it does it across rows and
columns. It’s whatever area that you select. A simple little tool but helpful.
Number 15 – Date/Time Shortcut. Did you know you can insert the date and time on your
spreadsheet with a simple shortcut? For the date press Ctrl-; and for the time Ctrl-Shift-; it’s as simple
as that. Stay tuned for more Excel training tips and tricks and feel free
to make suggestions in the comments below for any topics you’d like me to
cover in future videos. Thanks for watching. Hey, if you want to see more
videos like this one please subscribe and if you’ve enjoyed this video be sure
to click the thumbs-up and leave a comment. I really do appreciate your
support!