This is the top 15 advanced Excel 2016
tips and tricks. As a follow up from the top 25 Excel 2016 tips and tricks video,
here's some additional advanced features. If you missed the original top 25, follow
the link at the end of this video and be sure to check out my tips and
tricks playlist to see other videos for Outlook, Word, PowerPoint, OneDrive, Skype for Business, and Windows 10. Now let's get started. Number 1 - Advanced
Transpose. You probably already know how Transpose works. You highlight a block of
text, right-click, and copy it. Pick your target location, right-click, paste
special, and choose Transpose. Problem is is, if you make a change, the change is not
reflected in the new target location. So how do you make that live? There's a
simple way to do it. Go to your target location and make sure you highlight the
rows and columns based on the size of that table. In this case it's 2 by 6.
Don't click anywhere and start typing =TRNSPOSE. Put in left parenthesis
and then highlight the block. Put in right parenthesis and instead of hitting
Enter, press Shift-Ctrl-Enter. That transposes it to your target location
and now when you make changes, those changes are live. Number 2 - Calendar Picker. Have you ever
wanted a calendar to show up next to your dates? Here's a trick that might
work, but first you're going to need to make sure that the Developer tab is
available. So go to File, Options, and go to the Customize Ribbon, and make sure
this Developer is checked. Hit OK. That adds the Developer menu. Click on it,
choose insert, and come down here to the very bottom where the More Controls
option is, click that and in the list scroll down to the Date and Time Picker,
and hit OK. It wants you to place it on the screen somewhere so I'm going to
just select a spot right here. Now you're in Design Mode. If you turn that off you
can click on this and bring up a calendar. We're gonna go back into Design
Mode and select this entry and click properties and under the properties I'm
going to type in the cell B2 where that date is located. I'm going to close that
properties window, turn Design Mode off, and now as I pick from the date it
automatically changes my B2 cell location. That gives you a convenient way
to put a calendar on the screen. Number 3 - Slicers.
Let's say we have a table with data that represents the color, size, and cost. We're
used to filtering where we can select from the down arrow and choose just one
option from the menu and filter out the data. There is a better method to be able
to select the data. It's called Slicers. Just highlight the
whole table, go to Insert, choose Table, and make sure this checkmark is turned
on for My Table Has Headers. Hit OK. Now click inside of that table anywhere, and
go to Insert again, and choose Slicer. It'll bring up all of the different
filtered columns. Check all three, hit OK, and now you have multiple slicers
representing the data from your table. Now you can just click and select from
the list and it filters on the fly. You can clear the filter, choose a different
one, and mix and match however you want. It's a much better method than a filter.
Number 4 - Scenario Manager. Scenario Manager is part of the What-if-analysis in Excel. I'm looking at a car loan situation where I've plugged in the
numbers at 6% for 60 months on a principle of 35,000. This will be the
payment and this is the total cost of the vehicle. What I want to do is create
scenarios for a Best case, a Worst case, and a more Likely case and see how those
numbers come out. I could create those separate columns and put in the data but
if you want to make changes on the fly that's where the scenario manager comes
in. Given that these three items are the variables that can be changed, I'm going
to highlight them I'm gonna go to Data, and click on What-if-Analysis, and choose
Scenario Manager. For the first entry I'm going to click Add and I'm going to call
this one the Best case. The cells that will change are already highlighted and
it's going to be those three so I hit OK and then it asked me to enter the values
for each of those. So in the Best case scenario I might get some special
program and I'll get 3% but in order to get that deal it's going to have to be
36 months and they give me a price of 32,000. I hit OK and it creates the Best case scenario. Now I'm going to add the second one.
We'll call that one the Worst case. It still brings up the same cells and the
Worst case scenario is 8%. It's going to have to be 60 months and
it's for the full price 35,000. And finally, I'm going to add the Likely. Hit
OK and the Likely scenario is, I'm going to get 4% for the 60 months at the
35,000 mark. Now I can come in here once I've created these and hit Show and it
changes the numbers and changes the total. So you can see for the Best-case
scenario my total payment over that period is going to be 33,000. I show the
Worst case scenario and if I show the likely scenario.
Another option you have is you can click the summary and it asks you what is the
result cell. Which is this one right here. and I can choose to do a scenario
summary or I can do a pivot table but in this case I'm going to do a summary. I
hit OK, it brings up a new spreadsheet, and you can see the scenario is listed
here under the Best, Worst, and Likely case. It's a very handy tool. An easy method to
look at a bunch of different scenarios and compare your results. Number 5 -
CONVERT Function. CONVERT is a function that allows you to convert data from one
type of measurement into another. It's real simple. You just type in =CONVERT, the number you're converting from, and then you choose the measurement
that you want to use. There are a million different ones to choose from here and,
for example, I'm gonna choose Liters. Double-click that, enter comma, and now
it gives me only the choices that I can convert liters into. So let's make it
Gallons and there's my results. 100 Liters is 26.42 Gallons. That's real
straightforward. You can see a lot of different choices for conversions but
let's take this a step further since it's Advanced Excel. Number 6 - Convert
Currency Live. Converting measurements is great but what if you want to do
currency conversion where currency rates change all the time. Here's a slick trick.
Bring up a browser and search for XRATES. This is an exchange rate website.
Click on that and you'll see that it's set to do 1 U.S. dollar into Euros by
default. We're going to go ahead and pick the rate table for the US dollar and it
brings up a list of the top ten and a number of other currencies. All you have
to do is copy the URL for this website with this link. Go back to your spreadsheet,
click on the Data tab, go to Get Data From Other Sources, and select from the
web. In the URL paste the link that you just copied and hit OK. It brings up the
tables that are available from that website. The first one being the top 10
list and the second one being the full detail list. So we're going to select
that one, click Load, and there's your table added in. Now from the previous
sheet we can create a formula to take that value and multiply it by the
Canadian dollar. Now you can take this a step further. Create a VLOOKUP and have
selectable choices for the other currency, but now you have the data
available from the website and what you can do is if you come back to the sheet
you can also go up to Data, click on Properties, and click this button to go
to the query properties and set this to Refresh Automatically When Opening the
File or on a set number of minutes, and that's how you can get real-time
currency conversion data. Number 7 - Hide cells, Here's a sneaky
trick to hide cells. In this example, we have 3 times 5 equals 15. Let's say we
want to hide this number 5 right here. If you right-click, go to Format Cells, and
choose Custom. In the Type, enter semicolon 3 times and hit OK. That hides
that cell even though it's still there. And if you change a number, it still
works in a calculation. Number 8 - Remove Blanks. Here's a shortcut method to
remove blank rows from a list. Highlight the columns and hit Ctrl-G. Click
this Special button and choose blanks and hit OK. It's now just highlighted all
the blank rows. Press Ctrl-+ and then - and choose Shift Cells Up, and
hit OK. That removes all the blank rows. Number 9 - People Graph. People Graph is
an office 365 Adin that gives you a new way to represent data out of a table. In
this example, we're gonna have a region and a number of subscribers in that
region we want to represent it as a people graph. You go to Insert ,click on
the people graph icon, or find it in the menu and it brings up a generic display.
Up in the upper right corner you can see there's a spreadsheet that represents
your data. This is where you select the data that you want to put in the graph.
So I'm going to highlight that block of the table. Hit Create and I'm going to go
in there and I'm going to change this to Subscribers for the title. So there's
your graph. You can also go into the settings and pick from different types,
and also change the theme to a different color format, and if you don't want a
person you can select from these different icons to represent the data in
your list. The nice thing is you can change these
numbers and it updates it in your graph automatically. You can click on that in
the corners and resize it as needed, move it around, copy and paste it into other
applications. That's People Graph. Number 10 - Track Changes. Just as with other
office applications, Excel can track changes. You may want to use this feature
when collaborating on a spreadsheet with others so that you can see each person's
edits. To enable it, go to the Review tab and click on Track Changes and select
Highlight Changes. Make sure you check the box to Track Changes While Editing.
I'm going to choose All, You can also specify who you want to track changes
for. Make sure that this Highlight Changes On Screen is checked, and hit OK.
Hit OK to save it and now you can begin making changes to the data that you have
in the list. You'll notice that each one of these changes puts a mark in the
corner of each cell and you can see who made the change and what time and date
and what change was made. If another person opens the spreadsheet up they'll
see the same changes as well. These changes are just temporary until you
accept them. If you come back to the Review tab and go to Track Changes,
choose this Accept or Reject Changes option. It saves it again. Hit OK. Now you can choose which changes you want reviewed. I'm gonna select Not
Yet Reviewed, from Everyone. Hit OK and now it's going to step you through each
one of the changes and you're given a choice between accepting, rejecting, or
you can just accept all the changes or reject them all. This looks correct. I'm
going to hit Accept and it moves to the next. When you're done it marks all the
ones that you've accepted and you can save that with the spreadsheet. There's
also an added feature that allows you to take a look at all the changes that were made
by clicking on Track Changes again and go to Highlight Changes. Select this
check mark to List Changes on a New Sheet. It opens
up a history of all the changes that were made, which cell, and what change was made from the old value to the new. When you're done and you've accepted or rejected all the different changes, come up here, go to Highlight Changes, and turn it off,
and hit OK. It warns you that this will remove the work from shared use and save
all the changes as permanent. Hit Yes and you're done. Track Changes is a great
collaborative tool that allows you to share information with other users.
Number 11 - Advanced Filter. We've seen Filters and Slicers but there
is another method to filter data from a table. It's called Advanced Filter. To prep for an Advanced Filter I'm going to copy
this heading information right over here and use it as a criteria selection area.
And for our first Advanced Filter I'm going to choose Item1. That's what I
want to select from this list. If you go to Data there's an Advanced option in
the Sort and Filter section. Click that and it asks you for multiple information.
So I'm going to choose, first off, to Copy this to Another Location. The range that
I'm going to select from is going to be this entire table. The criteria range is
going to be the criteria range that we created. The location I'm going to
copy it to is right here. Hit OK and as you can see, it pulled all Item1's from
the table and put the results over here. Now the way this works is each thing
that's on a row is an AND selection. So I can come in here and say I want Item1
and Item2. Click the Advanced again but in this case the criteria range is
going to be all of this including the Item2 line. I'm going to copy it to
another location. It's already set to the same place. I hit OK and now it's pulled Item1's and Item2's. Now let's say we want to do an OR.
I'm going to take Item2 out and I'm going to choose location A. Both location
A and Item1. Click the Advanced Filter again. The criteria range I'm going to
choose just that first row and hit OK. Now it found only one Item1 with a
location A. Now we can take this a step further. I'm going to choose just a
location A and I'm going to select all the ones that are greater than 20.
I do the Advanced. Choose Copy to Another Location. The table the criteria range
will select is just this and the copy to location is correct. So there's the only
location A that's greater than 20. So you can use some math in there as well.
Advanced Filter is a very powerful tool with a lot of options. Number 12 -
Analysis Tools. There is a hidden advanced Excel menu available.
It's called Analysis Tools. It's not listed by default so you have to add it
manually. To do that go to File, Options, and choose Add-ins. At the bottom under
Excel add-ins hit the Go button and check this box for Analysis Toolpak. Hit
OK. That makes it show up under the Data tab right over here under Data Analysis.
This brings up a number of statistical analysis tools including correlation,
linear regression, moving averages, all kinds of choices here. Now I'm not going
to go into the details because you can find videos that describe these in more
depth but just so you know that they are available from that menu and that's how
you get to it. Number 13 - NETWORKDAYS Function.
The NETWORKDAYS Function counts the number of working days between one date
and another. Just type in =NETWORKDAYS and pick your start date and end
date and there's the number of working days between. Now optionally it has
holidays that you can exclude. So for example, if I put in the fourth of July. In
the formula the third parameter is the holidays. Hit Enter and there it
subtracted the one holiday out of those two date ranges and counted only the
workdays. Number 14 - Embedding. Did you know
you could take a spreadsheet table and insert into a Word document and have it
update automatically? The key is make sure you close your spreadsheet first
and open up Word. Go to the Insert tab and select this Text Insert Object.
Choose Create from File and browse to the file you want to insert. Make sure
you check this Link to File and hit OK. That inserts your spreadsheet. Now let's
go back to our spreadsheet. We're going to change from 17 to 18 and we're gonna
add a total at the bottom and we're going to save that. Close your
spreadsheet again, come back to your original Word document. If you right-click on that table, choose Update Link, and now it updates that information from
the spreadsheet. That's Embedding. Number 15 - Advanced Select. Here's a trick to
selecting objects and making changes to them. Let's say we want to modify all the
Item1's in this list. All you have to do is go over to the Find and Select,
click Find, type in Item1, choose Find All, and in this list down here hit Ctrl-A.
That selects and highlights all the Item1's in the list. Now the trick is
you can close this and come over to your formatting area and do all kinds of
things like highlight those, change the color, clear them, and essentially it's
doing it only to the ones that you've selected. That's Advanced Select. You've
just watched the top 15 Advanced Excel 2016 Tips & Tricks.
Click on the links coming up to see the original Excel 2016 Tips & Tricks
video or catch up on any of the tips and tricks videos for Microsoft
Office. 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!
