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!