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 miss 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 one 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 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
equals transpose 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 two 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 and 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 it okay 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 it okay
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 for 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 like a 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 and I 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 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 in 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 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 worse unlikely 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 equals
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 if the 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.4 to gallons that's real
straightforward you can see a lot of
different choices for conversion but
let's take this a step further since
it's advanced excel number six 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 x
rates this is an exchange rate website
click on that and you'll see that it's
set to do one 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 spread
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 add it 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 pick 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 seven 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 in or
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
I like 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 nine people graph people graph is
an office 365 ad in that gives you a new
way to represent David 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 it 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
yet 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 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 more
to list changes on a new sheet it opens
up a history of all the changes that
were made which sell and what change was
made from the old value to the new when
you're done and you've accepted 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 it 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 item 1 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 in the location I'm going to
copy it to is right here hit OK and as
you can see it pulled all item ones 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 items 1
and items to click the advanced again
but in this case the criteria range is
going to be all of this including the
item to 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 item ones and item
two now let's say we want to do an or
I'm going to take item two out and I'm
going to choose location a both location
a and item one 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 item one 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 new
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 it's a very powerful
tool with a lot of options number twelve
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 thirteen network day's function
the network day's function counts the
number of working days between one date
and another just type in equals network
days and pick your start date and end
date and there's a number of working
days between now optionally it has
holidays that you can exclude so for
example if I put in 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 fourteen imbedding 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 now 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
item ones in this list all you have to
do is go over to the find and select
click find type in item 1 choose find
all and in this list down here hit ctrl
a that selects and highlights all the
item ones 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 and tricks
click on the links coming up to see the
original Excel 2016 tips and tricks
video or catch up on any of the
tips and tricks videos for Microsoft
Office and 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