Top 25 Excel 2016 Tips and Tricks

Aug 14, 2023

Top 25 Excel 2016 Tips and Tricks

The Top 25 tips and tricks for Excel 2016. Use these tips and tricks to improve your efficiency using Excel. I am sure you will discover some that you have not seen before and others that you have been using incorrectly. My personal favorite is #21. Become more productive using Excel at work, school and home.

Check out the Advanced Excel video at    • Top 15 Advanced Excel 2016 Tips and T…  .
Another 15 Excel 2016 Tips \u0026 Tricks:    • Another 15 Excel 2016 Tips and Tricks
Introduction to Excel for Beginners:    • Excel Tutorial - Beginner
Don’t miss the latest Ultimate 50 Excel Tips and Tricks:    • 50 Ultimate Excel Tips and Tricks for…

Support me with your Amazon purchases, click here: https://amzn.to/2Kxp533

Contents 0:00
1. Quick Analysis Tool 0:47
2. Filter 1:44
3. Drop-Down List 2:25
4. AutoFit Column Width 3:12
5. Transpose 3:47
6. Remove Duplicates 4:13
7. Goal Seek 5:23
8. VLOOKUP 6:55
9. Flash \u0026 Auto Fill 9:06
10. Paste Special Values 10:47
11. Images in Charts 11:50
12. IF Function 12:28
13. Insert Screenshot 13:40
14. Absolute Cell Reference 14:15
15. Show Formulas 15:54
16. Text to Columns 16:57
17. Conditional Formatting 18:11
18. PowerPivot 19:36
19. Freeze Panes 20:46
20. Ctrl-Arrow Keys 21:40
21. 3D References 21:58
22. Forecast Sheet 23:43
23. SUMIFS Function 24:16
24. IFERROR Function 25:38
25. Filled Maps 26:30

#seletraining #excel #tipsandtricks

Content

0 -> After years of using Excel in a corporate environment here are my top 25

4.11 -> Excel 2016 tips and tricks.

16.609 -> Welcome to Sele training. I'm about to show you some useful tips and tricks for

21.449 -> Excel 2016. They're in no particular order so be sure to watch all the way

25.83 -> through to the end of the video. These tips and tricks have made me more

29.039 -> productive and I'm sure they'll make you more productive as well. Take a moment

32.61 -> and click the Subscribe button on the bottom right of this screen or at the

36.36 -> end of the video click on my smiling face and as always like, share, comment,

41.61 -> ask me questions. I'm happy to answer every question that I receive. Now let's

45.93 -> get started. Number 1 - Quick Analysis Tool. Most people aren't aware of the

52.289 -> Quick Analysis tool. When you highlight any table, in the bottom right-corner is

57.75 -> an icon. This is the Quick Analysis tool menu. If you click on that it gives you a

62.67 -> wealth of options you can choose from. To modify your table for example, I can

68.4 -> click on Totals. Click Sum and it shows the sum of the columns. You can click

73.92 -> this sum and it sums the rows. You can also do formatting on the table, add

81.619 -> icons, create charts and insert them into your spreadsheet, turn it into a table,

91.049 -> and add sparklines. There's so much available here and it makes it quick and

98.34 -> easy to avoid going through the menus and manually inserting each one of these

101.88 -> different options onto your table. Number 2 - Filter.

107.579 -> if you're not using filter you should be. It's very simple. Just click anywhere

112.92 -> inside of a table of data, go to the Data tab, click this Filter icon, and it

119.88 -> creates a filter at the top of each one of the columns. When you click on one of

124.38 -> these it shows you all the unique values listed anywhere in that column

130.619 -> and you can turn them off. Select one in particular or two or three or as many as

135.48 -> you like. Hit OK and it filters out only those rows of data that matched that

141.09 -> filter. It's a great way to manage large lists of information. Number 3 -

146.849 -> Drop-Down Lists. Say you want to select a list of values like 1 2 3 4 and you want

154.739 -> it to be selectable from this column right here.

157.56 -> Go to the Data tab, click on Data Validation, and choose List from the menu

164.7 -> choice. In the source select the choices that you want to make available and hit

173.1 -> Enter. Make sure this in cell drop-down checkbox is checked and hit OK. Now when

178.859 -> you're in this column and you hit the down arrow it gives you those four

182.76 -> choices to choose from. If you try to type something that is outside of that

186.63 -> range it gives you a warning that the value doesn't match the list. Number 4 -

192.72 -> AutoFit Column Width. This is by far the quickest way to

197.22 -> adjust the width on your columns. Just go to the space between columns so your

202.68 -> cursor turns to this shape and double click. It'll automatically readjust the

207.81 -> size of the columns to fit the widest point of the data anywhere in that

212.73 -> entire column. You can also highlight all of the columns, click on any one of them

220.62 -> the same way, and it will do multiple columns at the same time. Number 5 -

227.209 -> Transpose. Let's say you want to reverse the columns and rows in a table. All you have

233.669 -> to do is highlight the entire set of data, right-click, copy, choose the

239.04 -> location where you want to place the results,

241.48 -> click Paste Special, and check this box Transpose, and hit OK. Now all the columns

249.159 -> and rows have been reversed. Number 6 - Remove Duplicates. Removing duplicates is

257.799 -> handy if you have a list of names or other information where there's

261.31 -> duplicates and you want to end up with a unique set of values. In this example I'm

266.35 -> gonna highlight this list, click on Data, choose Remove Duplicates. The columns are

274.75 -> selected - first name, last name, and amount and in this case I'm going to hit OK to

279.16 -> remove the duplicates where all three of those columns match. That should be true

284.139 -> for row 3 and row 8 and as you can see it removes the 1 duplicate value. Now

295.6 -> let's do the same thing but in this case we're not going to do the amount and

300.37 -> we're only going to do it where the first name and the last name are exactly

303.22 -> the same. It now removed two duplicates. Now be sure to use this

309.099 -> Remove Duplicates when the results are going to be removed. If you just want a

314.05 -> filter you should use the Filter as we looked at earlier because this one

318.039 -> deletes the data that is duplicates. Number 7 - Goal Seek. Goal Seek is an

325.87 -> advanced function in Excel and is part of the What-if Analysis tools. In this

330.31 -> example say we have a number of items that we sell. We know how many the

335.44 -> quantity that we've sold of the first two items and the price each and what

340.09 -> the total dollar amount is but for the third item we want to know how many of

343.69 -> these we need to sell in order to get the overall total to $6,000. Well, you can

349.63 -> punch in numbers right here randomly to try to figure that out and then keep

359.59 -> working it until you get to a number that matches the 6000 that you're

362.65 -> targeting but you can also use the What-if Analysis to figure it out for

367.57 -> you. To use Goal Seek you want to start by clicking on the target cell,

373.36 -> go to Data, What-if Analysis, and choose Goal Seek. It fills in the target cell

381.789 -> as your Set Cell. The value we're trying to reach is 6000 and the cell we're

389.169 -> going to change is the quantity for Item3. When you hit OK

395.129 -> it goes through and calculates 197 as the magic number to reach that goal of

401.919 -> 6000 total. Just be aware that you can use your imagination to come up with all

407.5 -> kinds of scenarios for the What-if Analysis using Goal Seek. It handles very

412.12 -> complex solutions. Number 8 - VLOOKUP. VLOOKUP is a very commonly used tool to

421.25 -> find data in a list. In this example I have a list of names with an associated

427.7 -> ID. Over here I'm creating a new table and I want to reference the names in

433.67 -> this table to look up the ID from this list and fill them in. To do that I want

439.67 -> to use the VLOOKUP function. Click on the insert function button and you want to

445.88 -> find VLOOKUP in the list. You can type it in, do a search against all and once it's

454.25 -> there select it and it brings up the function arguments. To remind you of what

459.2 -> you fill in, for each one of these there's a description down here. The

463.01 -> lookup value is what value do I want to look up in the list, and that is the name

468.38 -> Nancy. The list that I'm going to choose from is the entire list and the Column

481.82 -> Index Number is the column that I want to return the value from. In this case

487.61 -> this is column 1 and this is column 2 so I'm going to choose column 2.

491.66 -> And finally, the range lookup is either True or False. If you use True it looks

497.45 -> for the closest match. False is an exact match and in almost every

501.92 -> situation you want to use False. Hit OK and you can see that it looked up Nancy

509.18 -> in the table and found the ID and returned it over here. All we have to do

515.45 -> then, let's copy this data down. Now you'll notice right here Carol isn't in

521.27 -> the list so it returns an N/A. Now also note that there is an HLOOKUP function which

527.3 -> is horizontal instead of vertical so if you're looking up something in a

530.87 -> different format you may need to use the HLOOKUP but it essentially has the

534.56 -> same parameters. The beauty of the VLOOKUP function is if you change a

538.97 -> value it will update it automatically in the table.

546.41 -> Number 9 - Flash and Auto Fill. Flash Fill and Auto Fill are slightly different.

552.8 -> In this example we have some email addresses and they fit the format of

557.57 -> first name dot last name throughout the list. If you start typing the name,

562.64 -> because you want to pull it out of the email address, Flash Fill automatically

568.22 -> detects the pattern that you're using and it recommends a solution to fill it

572.3 -> in for you. So you can hit enter and it fills those in for the remainder.

576.28 -> Auto Fill is also based on patterns so if you put a number in, for example, and you

582.26 -> use the right corner and drag it down, it auto fills those numbers in there. Now if

589.25 -> I did 1, 2 and selected both and then drag it down

595.99 -> it sees the pattern and automatically increments by one each time. You can do

601.58 -> the same thing with dates, with months. Just remember there has to be a

616.43 -> detectable pattern and to use the correct option on the corners. So if you

620.78 -> type something in and you drag it, it's gonna copy it. If you type something in

626.48 -> and you double-click it fills it in automatically, matching up with the left

631.43 -> column next to it. And if you define a pattern make sure you highlight both or

637.64 -> more and then drag. Flash Fill and Auto Fill are both really good at making

643.34 -> you more efficient when typing information in. Number 10 - Paste Special Values. Paste

650.42 -> Special Values is a handy tool to use if you have a bunch of data with formulas

654.8 -> and you just want to copy all of that information over to another location and

658.94 -> get rid of all those formulas. So you can see on this I have a formula that adds B

665.36 -> and C together into a full name. I have a formula that does a divided-by for the

671.33 -> percentage on this and if I want to just take those things and remove them and

675.589 -> copy this to another location so I just am left with the data, all you have to

680.18 -> do is highlight, Copy, pick a new location, and do Paste Special. In the list choose

690.71 -> Values and hit OK. Now you'll notice when I highlight these there's no formulas.

696.95 -> It's just the value of the data. You'll find this to be more common than you'd

700.85 -> think because often times you want to remove all that formula and formatting

704.959 -> and use the data in a different way and this is a quick function to help you

708.38 -> do that. Number 11 - Images in Charts. it's real simple to spice up a chart

716.029 -> with images. I've created a bar chart from a set of data. If you come into the

722.6 -> chart and click at least a couple of times to bring up the Format. Click on

728.33 -> this Fill & Line icon. go under Fill and there is a Picture or Texture Fill

734.6 -> option. If you select that, you can pick from a file and choose an image and it

742.22 -> will fill that image in on your chart in place of the normal texture. Number

748.76 -> 12 - IF function. The IF function is very handy if you want to do conditional

754.82 -> data representation. For example in this chart I have a column ABCD, a column with

762.23 -> numbers. Some of those numbers are greater than 10 and some are less. I want

767 -> to create an IF function to determine when these numbers are greater than 10

772.1 -> and say "BIG" and if they're less than 10 I want to say "SMALL" so I click on the

778.55 -> Fx. Type in IF, choose All, and do Go to search for it. Bring up the IF function.

787.089 -> The first parameter it asks for is the logical test. What we want to know is if

793.3 -> this number is greater than 10. If that's true we want to print "BIG" and if it's

804.41 -> false we want to print "SMALL". Ht okay and there you have your answer.

811.76 -> I'm going to copy this down to the other ones and now

815.19 -> I can see the one that's small. Number 13 - Insert Screenshot. If you want to insert

823.71 -> a screenshot onto your excel page go up to the Insert tab, click on Illustrations,

829.23 -> and go to Screenshot. It will show a list of active screens from other

834.3 -> applications. Select the one you want and it inserts the image onto the page. You

839.67 -> can then adjust the sizing however you want and you can also come up to the

845.28 -> Crop and crop out portions of the image.

854.69 -> Number 14 - Absolute Cell Reference. Excel uses two types of referencing - relative

862.02 -> and absolute. As you can see on this chart C4 times D4 takes the

869.01 -> quantity times the cost and gives a result. This is a relative cell reference

874.8 -> because as I look down the list it's referring to the second one to the left

879.48 -> and the first one to the left in the formula. The Total is also a relative

885.18 -> cell reference because it is showing E4 minus F4, the discount. And as you move

891.66 -> down the list it's referring to the one to the second left and the one to the

896.58 -> left in the formula. Now if I was to add a discount in here the formula would be this

902.91 -> number times this number and this is a relative cell reference reflecting the

911.07 -> one to the left times this one up here. The problem is is when I copy this down

916.49 -> this one shouldn't be pointing to this number times G2 because there's

923.67 -> nothing in G2. We want it to stay G1 so we need an absolute cell reference. To

931.86 -> do that all you have to do is put a $ in front of G and a $

936.54 -> in front of 1 to force that to be a fixed value to that cell. And now when

943.56 -> we copy these down, there's G1, there's G1, there's G1 and there's G1

949.9 -> as well. That is an Absolute Cell Reference. Number 15 - Show Formulas. Often

958.23 -> a spreadsheet gets to the point where you have a number of formulas spread out

961.68 -> all over the place and it's really difficult to look at each one

964.2 -> individually. Wouldn't it be nice if you could see a list of all the formulas all

969.06 -> at once. Go into File, Options,

975.29 -> Advanced and scroll down toward the bottom and there is a "Show formulas in

982.47 -> cells instead of their calculated results". If you check that box and hit OK

987.12 -> it now actually shows all of the formula references for you to view. I'm going to

992.4 -> turn that off. A shortcut method to do that is Ctrl ~ and you can toggle

999.54 -> that on and off. Another option you have is just a double-click on a cell and you

1006.02 -> can see the color codes of the reference cells that is being used and the formula

1011.3 -> for that cell and those options make it easier to see formulas at a glance.

1016.33 -> Number 16 - Text to Columns. When using Excel you'll often find yourself wanting

1023.03 -> to copy data from other applications or from a webpage or other source. I have a

1027.56 -> set of data in Word. If I copy this and paste it into an Excel spreadsheet

1033.34 -> unfortunately it copies it all in as one line and we really want it to break all

1038.42 -> of these components out into different columns. That's the whole purpose of

1041.81 -> using Excel. So there's a quick way you can convert these. Just highlight them, go

1047.06 -> to the Data tab, and click on Text to Columns. Now in this situation you can

1053.75 -> choose between Delimited or Fixed Width. These are separated by commas so I'm

1059.12 -> going to pick Delimited and I'm going to choose comma and not tab. You'll

1066.8 -> notice down here that it knows where the spacing is between each column. Hit

1072.56 -> next. you can change some of the formatting if

1075.44 -> you want to and when you're done hit Finish and now it separates them into

1079.309 -> different columns. Just remember there has to be some delimiter to separate

1085.399 -> them out, or if it's fixed width you can choose that as an option too. Number 17 -

1092.259 -> Conditional Formatting. You saw a sneak peek of the conditional formatting when

1097.789 -> we looked at the Quick Analysis tool earlier but there are more options

1101.21 -> available from the menu. Let's say on this table we want to show all of the

1104.899 -> entries that have greater than 2000. Just highlight the list and on the Home tab,

1111.23 -> choose Conditional Formatting, Select Highlighted Cells Rules, and choose

1116.269 -> Greater Than, and enter 2000. Now it has highlighted everything greater than 2000.

1123.59 -> if you want to turn that off go back to Conditional Formatting and Clear the

1128.09 -> Rules. There's many other options available here. You can show the Top 10%,

1144.77 -> you can change Data Bars so you have a visual representation, you can change the

1150.95 -> Color Scales for a more visual representation. I particularly like the

1156.44 -> Icon Sets and you can even define a new rule and make up all kinds of options to

1165.53 -> select what you want. It's very powerful. So give yourself a better representation of

1169.58 -> your data through visual and highlighted items using Conditional Formatting.

1175.12 -> Number 18 - PowerPivot. PowerPivot is a free feature that's installed as an

1183.02 -> add-in in Excel but it's not installed by default. To enable it go to the File,

1189.49 -> Options, click on Add-ins, and come down here to the bottom and choose Com

1196.43 -> Add-ins and hit Go. You'll see in the list Microsoft PowerPivot for Excel.

1201.89 -> Check that box and hit OK and it will add this PowerPivot

1207.77 -> tab onto your screen. When you click on that tab you have a whole new

1212.84 -> set of features available to you. Now without going into a detailed

1216.53 -> explanation of how to use PowerPivot, because you can find that information

1220.04 -> elsewhere on other videos, but the bottom line is this tool allows you to connect

1225.5 -> to other Oracle or SQL data sources and a variety of other data sets and use

1230.48 -> it as a BI tool for large sets of data. In fact, it expands the 1 Million row

1235.58 -> limit of Excel to virtually unlimited numbers. It is meant to turn Excel into a

1240.92 -> business intelligence analysis tool so take a look. Number 19 - Freeze Panes.

1249.34 -> Freeze Panes allows you to lock certain portions of the screen. In this example I

1254.72 -> have a large set of data. When I scroll to the right I lose the first column

1259.639 -> name information and if I scroll down I lose the header information. To solve

1266.419 -> this you can use Freeze Panes. Click on the corner where you want the rows above

1272.6 -> and the columns to the left to be locked. Go to View and click Freeze Panes and

1280.12 -> choose Freeze Panes from the menu. Now when you scroll right you can see the

1286.549 -> names in the left column and when you scroll down you can see the rows in the

1292.19 -> heading. To undo the freeze panes just click on it again and click Unfreeze.

1298.179 -> Number 20 - Control Arrow Keys. Don't forget to hold down the Ctrl key when

1304.76 -> you're arrowing around a set of data. Ctrl right-arrow, down-arrow, left-arrow,

1310.429 -> up-arrow. It'll move to the end, beginning, bottom, or top of the data set

1315.95 -> that you're working within. Number 21 - 3D References. If you're tracking data over

1323.179 -> a period of time it's often common to create a different month tab for each

1328.1 -> one of the sets of data and in this case I have September, October, and November.

1331.309 -> They are identical in terms of the format but the data is different in each.

1335.57 -> Let's say I want to create a total of all of those. I can hold the Ctrl key

1340.639 -> down and drag this tab and then rename it to get our Total tab. Let's say we

1350.059 -> want to add a heading to the top of each one of these charts. We can add it in and

1354.679 -> go into each one of the tabs one at a time but with 3D Referencing all you

1359.84 -> have to do is click on one on the end, hold down the Shift key, and click the

1364.519 -> other end. Now you've highlighted all four of these tabs. Go ahead and do your

1370.94 -> insert, put in your heading, make your changes,

1380.14 -> and now when you click on the individual ones you'll see that changes I made

1385.64 -> we're done on all four. Now let's take our total and let's use a 3D Reference

1390.409 -> to add the quantities from each one of these three tabs into this cell. I'm

1394.909 -> gonna hit the Sum. I'm gonna go to the first tab, choose that cell, hold down the

1402.35 -> Shift key and select all three and hit Enter. Now that you're on the Total tab

1409.13 -> you'll see that it's summed September through November. Now I'm going to just

1414.559 -> drag these down, copy them across and you have the total for all three. That is 3D

1420.5 -> Referencing. Number 22 - Forecast Sheet. You want a quick peek of the future? So

1428.63 -> you've got a set of numbers here that look like they're in a pattern. All you

1432.23 -> have to do is highlight that, go to Data, and choose Forecast Sheet. This gives you

1440.149 -> a trend line and the forecast, and you can click the up-arrow here to extend it out

1446.33 -> for a longer period of time if you want to look farther in the future. That's

1450.08 -> just a quick look at the Forecast option for something really simple. Number 23 -

1456.169 -> SUMIFS Function. The SUMIFS function allows you to do conditional summing of

1462.95 -> data. So in this example I have a table with month, item, and amount. Over here I'm

1469.82 -> going to add the sum of the amounts based on this criteria. Entering the SUMIFS

1475.82 -> function we just do =SUMIFS and the range is the range of values

1483.919 -> that you're summing. The criteria in this first example is the month so we're

1489.86 -> going to select the month range and the value we're looking for is right there

1495.88 -> and you can see the total is 68 which is the sum of these first three. For this

1503.12 -> one we're going to do the same thing

1507.14 -> The range is still the values the, criteria that we're selecting for first

1513.38 -> is the month, with this being the month. We can continue on with another set of

1519.47 -> criteria. Have it be the item, and this is the specific item we're selecting and

1526.61 -> you can see 78 is the total of just May Item2's.

1532.19 -> And that's the SUMIFS function. Number 24 - IFERROR function. The IFERROR

1541.61 -> function can be used to clean up some bad data. For example, I have a formula

1546.02 -> here that calculates the per item amount for the quantity and the total. If I copy

1551.99 -> this down I get a divided by zero error because the quantity on this line is

1557.48 -> zero. Well, the IFERROR function can be used to clean this up. All you have to do

1563.96 -> is come in here, add IFERROR in front of your formula, and if that value is an

1571.58 -> error then what do you want to do? Let's just put in zero. Now when I copy that

1578.21 -> down it puts a zero in there but still calculates the other locations correctly.

1584.48 -> And that's a quick fix with the IFERROR function. Number 25 - Filled Maps. Filled

1592.91 -> Maps are just like any other chart but way cooler. Take a set of data, highlight

1598.97 -> it, make sure you have location specific information in here, which can be a state,

1603.86 -> a city, a zipcode, GPS coordinates, or any other location related data elements . Go

1610.73 -> to insert and choose Maps. Select this Filled Map and your data elements will

1618.89 -> be placed on a map. In this case the United States. You can also add different

1624.38 -> chart elements like Data Labels and you can change the chart to different types

1630.32 -> of styles. It's a very cool 2016 feature. And that conclude

1637.57 -> this video of the top 25 Excel 2016 tips and tricks. Don't forget to check

1643.45 -> out the other tips and tricks videos for Outlook, Skype for Business, Word,

1648.31 -> PowerPoint, and more coming in the future. Thanks for watching. Hey, if you want to

1653.86 -> see more videos like this one, please subscribe and if you've enjoyed this

1657.76 -> video be sure to click the thumbs-up and leave a comment. I really do appreciate

1661.6 -> your support!

Source: https://www.youtube.com/watch?v=EdnAeqxqdzM