October 27, 2020

Top 25 Excel 2016 Tips and Tricks

After years of using Excel in acorporate environment here are my top 25 Excel 2016 tips and tricks.

Welcome to Sele training.

I'm about toshow you some useful tips and tricks for Excel 2016.

They're in no particularorder so be sure to watch all the way through to the end of the video.

Thesetips and tricks have made me more productive and I'm sure they'll make youmore productive as well.

Take a moment and click the Subscribe button on thebottom right of this screen or at the end of the video click on my smilingface and as always like, share, comment, ask me questions.

I'm happy to answerevery question that I receive.

Now let's get started.

Number 1 – Quick AnalysisTool.

Most people aren't aware of the Quick Analysis tool.

When you highlightany table, in the bottom right-corner is an icon.

This is the Quick Analysis toolmenu.

If you click on that it gives you a wealth of options you can choose from.

Tomodify your table for example, I can click on Totals.

Click Sum and it showsthe sum of the columns.

You can click this sum and it sums the rows.

You canalso do formatting on the table, add icons, create charts and insert them intoyour spreadsheet, turn it into a table, and add sparklines.

There's so muchavailable here and it makes it quick and easy to avoid going through the menusand manually inserting each one of these different options onto your table.

Number2 – Filter.

if you're not using filter you should be.

It's very simple.

Just click anywhere inside of a table of data, go to the Datatab, click this Filter icon, and it creates a filter at the top of each oneof the columns.

When you click on one of these it shows you all the uniquevalues listed anywhere in that column and you can turn them off.

Select one inparticular or two or three or as many as you like.

Hit OK and it filters out onlythose rows of data that matched that filter.

It's a great way to manage largelists of information.

Number 3 – Drop-Down Lists.

Say you want to select alist of values like 1 2 3 4 and you want it to be selectable from this columnright here.

Go to the Data tab, click on DataValidation, and choose List from the menu choice.

In the source select the choicesthat you want to make available and hit Enter.

Make sure this in cell drop-downcheckbox is checked and hit OK.

Now when you're in this column and you hit thedown arrow it gives you those four choices to choose from.

If you try totype something that is outside of that range it gives you a warning that thevalue doesn't match the list.

Number 4 – AutoFit Column Width.

This is by far the quickest way to adjust the width on your columns.

Just goto the space between columns so your cursor turns to this shape and doubleclick.

It'll automatically readjust the size of the columns to fit the widestpoint of the data anywhere in that entire column.

You can also highlight allof the columns, click on any one of them the same way, and it will do multiplecolumns at the same time.

Number 5 – Transpose.

Let's say you want to reversethe columns and rows in a table.

All you have to do is highlight the entire set ofdata, right-click, copy, choose the location where you want to place theresults, click Paste Special, and check this boxTranspose, and hit OK.

Now all the columns and rows have been reversed.

Number 6 -Remove Duplicates.

Removing duplicates is handy if you have a list of names orother information where there's duplicates and you want to end up with aunique set of values.

In this example I'm gonna highlight this list, click on Data, choose Remove Duplicates.

The columns are selected – first name, last name, and amount and in this case I'm going to hit OK to remove the duplicates where all three ofthose columns match.

That should be true for row 3 and row 8 and as you cansee it removes the 1 duplicate value.

Now let's do the same thing but in this casewe're not going to do the amount and we're only going to do it where thefirst name and the last name are exactly the same.

It now removed twoduplicates.

Now be sure to use this Remove Duplicates when the results aregoing to be removed.

If you just want a filter you should use the Filter as welooked at earlier because this one deletes the data that is duplicates.

Number 7 – Goal Seek.

Goal Seek is an advanced function in Excel and is partof the What-if Analysis tools.

In this example say we have a number of itemsthat we sell.

We know how many the quantity that we've sold of the firsttwo items and the price each and what the total dollar amount is but for thethird item we want to know how many of these we need to sell in order to getthe overall total to $6, 000.

Well, you can punch in numbers right here randomly totry to figure that out and then keep working it until you get to a numberthat matches the 6000 that you're targeting but you can also use theWhat-if Analysis to figure it out for you.

To use Goal Seek you want to startby clicking on the target cell, go to Data, What-if Analysis, and chooseGoal Seek.

It fills in the target cell as your Set Cell.

The value we're tryingto reach is 6000 and the cell we're going to change is the quantity for Item3.

When you hit OK it goes through and calculates 197 asthe magic number to reach that goal of 6000 total.

Just be aware that you canuse your imagination to come up with all kinds of scenarios for the What-ifAnalysis using Goal Seek.

It handles very complex solutions.

Number 8 – VLOOKUP.

VLOOKUP is a very commonly used tool to find data in a list.

In this example Ihave a list of names with an associated ID.

Over here I'm creating a new tableand I want to reference the names in this table to look up the ID from thislist and fill them in.

To do that I want to use the VLOOKUP function.

Click on theinsert function button and you want to find VLOOKUP in the list.

You can type itin, do a search against all and once it's there select it and it brings up thefunction arguments.

To remind you of what you fill in, for each one of thesethere's a description down here.

The lookup value is what value do I want tolook up in the list, and that is the name Nancy.

The list that I'm going to choosefrom is the entire list and the Column Index Number is the column that I wantto return the value from.

In this case this is column 1 and this is column2 so I'm going to choose column 2.

And finally, the range lookup is eitherTrue or False.

If you use True it looks for the closest match.

False is anexact match and in almost every situation you want to use False.

Hit OKand you can see that it looked up Nancy in the table and found the ID andreturned it over here.

All we have to do then, let's copy this data down.

Nowyou'll notice right here Carol isn't in the list so it returns an N/A.

Now also notethat there is an HLOOKUP function which is horizontal instead of vertical so ifyou're looking up something in a different format you may need to use theHLOOKUP but it essentially has the same parameters.

The beauty of theVLOOKUP function is if you change a value it will update it automatically inthe table.

Number 9 – Flash and Auto Fill.

FlashFill and Auto Fill are slightly different.

In this example we have some emailaddresses and they fit the format of first name dot last name throughout thelist.

If you start typing the name, because you want to pull it out of theemail address, Flash Fill automatically detects the pattern that you're usingand it recommends a solution to fill it in for you.

So you can hit enter and itfills those in for the remainder.

Auto Fill is also based on patterns so ifyou put a number in, for example, and you use the right corner and drag it down, itauto fills those numbers in there.

Now if I did 1, 2 and selected both and thendrag it down it sees the pattern and automaticallyincrements by one each time.

You can do the same thing with dates, with months.

Just remember there has to be a detectable pattern and to use thecorrect option on the corners.

So if you type something in and you drag it, it'sgonna copy it.

If you type something in and you double-click it fills it inautomatically, matching up with the left column next to it.

And if you define apattern make sure you highlight both or more and then drag.

Flash Fill andAuto Fill are both really good at making you more efficient when typing informationin.

Number 10 – Paste Special Values.

Paste Special Values is a handy tool to use ifyou have a bunch of data with formulas and you just want to copy all of thatinformation over to another location and get rid of all those formulas.

So you cansee on this I have a formula that adds B and C together into a full name.

I have aformula that does a divided-by for the percentage on this and if I want to justtake those things and remove them and copy this to another location so I justam left with the data, all you have to do is highlight, Copy, pick a new location, and do Paste Special.

In the list choose Values and hit OK.

Now you'll notice whenI highlight these there's no formulas.

It's just the value of the data.

You'llfind this to be more common than you'd think because often times you want toremove all that formula and formatting and use the data in a different wayand this is a quick function to help you do that.

Number 11 – Images in Charts.

it's real simple to spice up a chart with images.

I've created a bar chartfrom a set of data.

If you come into the chart and click at least a couple oftimes to bring up the Format.

Click on this Fill & Line icon.

go under Filland there is a Picture or Texture Fill option.

If you select that, you can pickfrom a file and choose an image and it will fill that image in on your chart inplace of the normal texture.

Number 12 – IF function.

The IF function isvery handy if you want to do conditional data representation.

For example in thischart I have a column ABCD, a column with numbers.

Some of those numbers aregreater than 10 and some are less.

I want to create an IF function to determinewhen these numbers are greater than 10 and say “BIG” and if they're less than 10I want to say “SMALL” so I click on the Fx.

Type in IF, choose All, and do Goto search for it.

Bring up the IF function.

The first parameter it asks for is thelogical test.

What we want to know is if this number is greater than 10.

If that'strue we want to print “BIG” and if it's false we want to print “SMALL”.

Ht okay and there you have your answer.

I'm going to copy this down to the other ones and now I can see the one that's small.

Number 13 -Insert Screenshot.

If you want to insert a screenshot onto your excel page go upto the Insert tab, click on Illustrations, and go to Screenshot.

It will show a listof active screens from other applications.

Select the one you want andit inserts the image onto the page.

You can then adjust the sizing however youwant and you can also come up to the Crop and crop out portions of the image.

Number 14 – Absolute Cell Reference.

Excel uses two types of referencing – relative and absolute.

As you can see on thischart C4 times D4 takes the quantity times the cost and gives aresult.

This is a relative cell reference because as I look down the list it'sreferring to the second one to the left and the first one to the left in theformula.

The Total is also a relative cell reference because it is showing E4minus F4, the discount.

And as you move down the list it's referring to the oneto the second left and the one to the left in the formula.

Now if I was to adda discount in here the formula would be this number times this number and this is arelative cell reference reflecting the one to the left times this one up here.

The problem is is when I copy this down this one shouldn't be pointing to thisnumber times G2 because there's nothing in G2.

We want it to stay G1 sowe need an absolute cell reference.

To do that all you have to do is put a$ in front of G and a $ in front of 1 to force that to bea fixed value to that cell.

And now when we copy these down, there's G1, there's G1, there's G1 and there's G1 as well.

That is an Absolute CellReference.

Number 15 – Show Formulas.

Often a spreadsheet gets to the point whereyou have a number of formulas spread out all over the place and it's reallydifficult to look at each one individually.

Wouldn't it be nice if youcould see a list of all the formulas all at once.

Go into File, Options, Advanced and scroll down toward thebottom and there is a “Show formulas in cells instead of their calculatedresults”.

If you check that box and hit OK it now actually shows all of the formulareferences for you to view.

I'm going to turn that off.

A shortcut method to dothat is Ctrl ~ and you can toggle that on and off.

Another option you haveis just a double-click on a cell and you can see the color codes of the referencecells that is being used and the formula for that cell and those options make iteasier to see formulas at a glance.

Number 16 – Text to Columns.

When usingExcel you'll often find yourself wanting to copy data from other applications orfrom a webpage or other source.

I have a set of data in Word.

If I copy this andpaste it into an Excel spreadsheet unfortunately it copies it all in as oneline and we really want it to break all of these components out into differentcolumns.

That's the whole purpose of using Excel.

So there's a quick way youcan convert these.

Just highlight them, go to the Data tab, and click on Text toColumns.

Now in this situation you can choose between Delimited or Fixed Width.

These are separated by commas so I'm going to pick Delimited and I'm goingto choose comma and not tab.

You'll notice down here that it knows where thespacing is between each column.

Hit next.

you can change some of the formatting if you want to and when you're done hitFinish and now it separates them into different columns.

Just remember therehas to be some delimiter to separate them out, or if it's fixed width you canchoose that as an option too.

Number 17 – Conditional Formatting.

You saw a sneakpeek of the conditional formatting when we looked at the Quick Analysis toolearlier but there are more options available from the menu.

Let's say onthis table we want to show all of the entries that have greater than 2000.

Justhighlight the list and on the Home tab, choose Conditional Formatting, SelectHighlighted Cells Rules, and choose Greater Than, and enter 2000.

Now it hashighlighted everything greater than 2000.

if you want to turn that off go back toConditional Formatting and Clear the Rules.

There's many other optionsavailable here.

You can show the Top 10%, you can change Data Bars so you have avisual representation, you can change the Color Scales for a more visualrepresentation.

I particularly like the Icon Sets and you can even define a newrule and make up all kinds of options to select what you want.

It's very powerful.

So give yourself a better representation of your data through visual and highlighteditems using Conditional Formatting.

Number 18 – PowerPivot.

PowerPivot is afree feature that's installed as an add-in in Excel but it's not installedby default.

To enable it go to the File, Options, click on Add-ins, and come downhere to the bottom and choose Com Add-ins and hit Go.

You'll see in thelist Microsoft PowerPivot for Excel.

Check that box and hit OK and it will add this PowerPivot tab onto your screen.

When youclick on that tab you have a whole new set of features available to you.

Nowwithout going into a detailed explanation of how to use PowerPivot, because you can find that information elsewhere on other videos, but the bottomline is this tool allows you to connect to other Oracle or SQL data sourcesand a variety of other data sets and use it as a BI tool for large sets of data.

In fact, it expands the 1 Million row limit of Excel to virtually unlimitednumbers.

It is meant to turn Excel into a business intelligence analysis tool sotake a look.

Number 19 – Freeze Panes.

Freeze Panes allows you to lock certainportions of the screen.

In this example I have a large set of data.

When I scrollto the right I lose the first column name information and if I scroll down Ilose the header information.

To solve this you can use Freeze Panes.

Click onthe corner where you want the rows above and the columns to the left to be locked.

Go to View and click Freeze Panes and choose Freeze Panes from the menu.

Nowwhen you scroll right you can see the names in the left column and when youscroll down you can see the rows in the heading.

To undo the freeze panes justclick on it again and click Unfreeze.

Number 20 – Control Arrow Keys.

Don'tforget to hold down the Ctrl key when you're arrowing around a set of data.

Ctrl right-arrow, down-arrow, left-arrow, up-arrow.

It'll move to the end, beginning, bottom, or top of the data set that you're working within.

Number 21 – 3DReferences.

If you're tracking data over a period of time it's often common tocreate a different month tab for each one of the sets of data and in this caseI have September, October, and November.

They are identical in terms of theformat but the data is different in each.

Let's say I want to create a total ofall of those.

I can hold the Ctrl key down and drag this tab and then renameit to get our Total tab.

Let's say we want to add a heading to the top of eachone of these charts.

We can add it in and go into each one of the tabs one at atime but with 3D Referencing all you have to do is click on one on the end, hold down the Shift key, and click the other end.

Now you've highlighted allfour of these tabs.

Go ahead and do your insert, put in your heading, make yourchanges, and now when you click on the individualones you'll see that changes I made we're done on all four.

Now let's takeour total and let's use a 3D Reference to add the quantities from each one ofthese three tabs into this cell.

I'm gonna hit the Sum.

I'm gonna go to thefirst tab, choose that cell, hold down the Shift key and select all three and hitEnter.

Now that you're on the Total tab you'll see that it's summed Septemberthrough November.

Now I'm going to just drag these down, copy them across and you have the total for all three.

That is 3D Referencing.

Number 22 – Forecast Sheet.

You want a quick peek of the future? So you've got a set of numbers here thatlook like they're in a pattern.

All you have to do is highlight that, go to Data, and choose Forecast Sheet.

This gives you a trend line and the forecast, and you canclick the up-arrow here to extend it out for a longer period of time if you wantto look farther in the future.

That's just a quick look at the Forecast optionfor something really simple.

Number 23 – SUMIFS Function.

The SUMIFS functionallows you to do conditional summing of data.

So in this example I have a tablewith month, item, and amount.

Over here I'm going to add the sum of the amountsbased on this criteria.

Entering the SUMIFS function we just do =SUMIFSand the range is the range of values that you're summing.

The criteria in thisfirst example is the month so we're going to select the month range and thevalue we're looking for is right there and you can see the total is 68 which isthe sum of these first three.

For this one we're going to do the same thing The range is still the values the, criteria that we're selecting for first is the month, with this being the month.

We can continue on with another set of criteria.

Have it be the item, and this isthe specific item we're selecting and you can see 78 is the total of just MayItem2's.

And that's the SUMIFS function.

Number24 – IFERROR function.

The IFERROR function can be used to clean up somebad data.

For example, I have a formula here that calculates the per item amountfor the quantity and the total.

If I copy this down I get a divided by zero errorbecause the quantity on this line is zero.

Well, the IFERROR function can beused to clean this up.

All you have to do is come in here, add IFERROR in front ofyour formula, and if that value is an error then what do you want to do? Let'sjust put in zero.

Now when I copy that down it puts a zero in there but stillcalculates the other locations correctly.

And that's a quick fix with the IFERRORfunction.

Number 25 – Filled Maps.

Filled Maps are just like any other chart butway cooler.

Take a set of data, highlight it, make sure you have location specificinformation in here, which can be a state, a city, a zipcode, GPS coordinates, or anyother location related data elements .

Go to insert and choose Maps.

Select thisFilled Map and your data elements will be placed on a map.

In this case theUnited States.

You can also add different chart elements like Data Labels and youcan change the chart to different types of styles.

It's a very cool 2016 feature.

And that conclude this video of the top 25 Excel 2016tips and tricks.

Don't forget to check out the other tips and tricks videos forOutlook, Skype for Business, Word, PowerPoint, and more coming in the future.

Thanks for watching.

Hey, if you want to see more videos like this one, pleasesubscribe and if you've enjoyed this video be sure to click the thumbs-up andleave a comment.

I really do appreciate your support!.

Leave a Reply

Your email address will not be published. Required fields are marked *