Top 15 Advanced Excel 2016 Tips and Tricks

Sharing buttons:

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!