50 Ultimate Excel Tips and Tricks for 2020

Aug 13, 2023

50 Ultimate Excel Tips and Tricks for 2020

The ultimate compilation of Excel tips and tricks to enhance your productivity using Excel. Updated for 2020! I’ve combined long-established tips and tricks with new ones Microsoft added in 2019 and 2020. You’ll find solutions to the most common challenges you face in Excel.

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

Contents 0:00
1. Move Data 0:35
2. XLOOKUP() 1:04
3. Filter List 2:24
4. Ideas 3:04
5. Remove Blanks 3:54
6. Quick Analysis Tool 4:28
7. AutoFit Column Width 5:27
8. Absolute Cell Reference 5:57
9. Paste Special Values 7:19
10. Drop-Down List 8:05
11. FILTER() 9:15
12. Remove Duplicates 10:43
13. Difference Between Lists 11:31
14. Flash Fill 12:15
15. AutoFill 13:06
16. Data Types 13:53
17. Transpose 15:11
18. Freeze Panes 15:37
19. Text to Columns 16:27
20. Recommended Pivot table 17:25
21. Slicers 18:04
22. Conditional Formatting 19:01
23. IF() 20:29
24. 3D References 21:12
25. Forecast Sheet 22:45
26. SUMIFS() 24:00
27. IFERROR() 25:15
28. Ctrl-Arrow Keys 26:03
29. Filled Maps 26:26
30. PMT() 27:13
31. Show Formulas 28:20
32. Advanced Select 29:10
33. Named Range Shortcut 29:56
34. Hide Cells 30:47
35. COUNTBLANK() 31:30
36. Natural Language Query 31:59
37. Goal Seek 33:08
38. Insert Screenshot 34:30
39. Power Pivot 35:14
40. 3D Maps 36:16
41. ISBLANK() 39:08
42. Analysis ToolPak 39:45
43. CONVERT() 40:48
44. Get Data from Web 41:24
45. People Graph 42:59
46. SORT() 44:18
47. Status Bar Info 45:36
48. Insert Multiple Rows 46:22
49. CHOOSE() 46:53
50. UNIQUE() 48:50

#seletraining #excel #tipsandtricks

Content

0 -> 50 Ultimate Excel Tips & Tricks. Updated for 2020! This video is the

11.309 -> ultimate compilation of Excel tips and tricks to enhance your productivity

15.15 -> using Excel. I've combined long-established tips and tricks

18.66 -> featured in previous videos with prominent new ones Microsoft added in

22.65 -> 2019 and 2020. You'll find solutions to the most common challenges you face in

27.63 -> Excel. For the complete list of contents please refer to the description of this

32.579 -> video. Let's get started. #1 Move Data. Here's the quickest method to move

40.89 -> data around in Excel. Just highlight the cells and grab the border anywhere

46.14 -> around the outside and drag it and drop it where you want it to go. You can do an

51.39 -> entire column or you can do an entire row. No need to copy/paste and then

61.829 -> delete the original. #2 XLOOKUP(). XLOOKUP() is a powerful new function

70.2 -> added to excel in late 2019 and designed to replace VLOOKUP and HLOOKUP. Let's say

76.35 -> we want to look up the name "John" in this table and return a value from the

80.58 -> February column. You can use XLOOKUP and the first parameter is the lookup

86.909 -> value which is going to be John. The array that we're going to look up John's

91.74 -> name in is this area right here and we want to return values from the February

98.009 -> column. And that's all you need to do. It returned 17,232 which is the

104.97 -> intersection of John and February. Now one of the benefits of XLOOKUP is it

110.49 -> does horizontal lookups as well so let's say we want to look up February in this

119.549 -> list but we're going to return the value from John's row

126.76 -> and it found the same number 17,232 in the February column next to John. So it

133.54 -> does both vertical and horizontal lookups. Be sure to check out my XLOOKUP

138.46 -> dedicated video if you want more details and some of the other powerful features

142.27 -> in XLOOKUP. #3 Filter List. Want an easy way to manage a large table of data.

150.19 -> Click anywhere inside the table, go to the Data tab and click on this Filter

156.52 -> icon and it creates a filter at the top of each column in your table. Now you can

162.46 -> select one and choose from the list the values that you want to see. It filters

170.8 -> that data out. You can use multiple columns and filter out just the data you

179.86 -> want to see. It's a great way to manage large lists of data. #4 Ideas. If

188.23 -> you're looking for suggestions on how best to display your Excel data use

191.8 -> Ideas for inspiration. Just click anywhere inside of a table, go to the

197.35 -> Home tab, and choose Ideas. This brings up a list of graphs and charts based on

204.13 -> that data. You can hit + to add them to your spreadsheet. You can even choose

211.51 -> which fields interest you the most and in this example, let's change Sum to an

217.06 -> Average and update those charts. I hit + here on the Units by Region, it

223.09 -> creates a new tab, and adds the data from that chart into your spreadsheet

228.459 -> reflecting the average of the unit's. Ideas is great at suggesting the

232.51 -> data that matters the most. #5 Remove Blanks. If you want to

238.45 -> remove a bunch of empty cells in a set of data just highlight that whole list,

242.55 -> go to the Home tab, and choose Find and Select from the menu. Choose Go to

248.83 -> Special, and check this box for blanks. Hit OK. It's now highlighted all the

255.04 -> blank cells in my data set. Just right-click on any one of them,

259.75 -> choose delete, and choose Shift Cells Left. Hit OK.

264.43 -> It just cleaned up that list. #6 Quick Analysis Tool. When you highlight a

272.56 -> table in your spreadsheet in the bottom-right corner there's an icon. This is the

277 -> Quick Analysis Tool. When you click on that icon it brings up a wealth of

281.26 -> information that you can select from. You can pick Sums of the columns, Sums of the

287.05 -> rows, get a Running Total, or Percentage. You can select Charts and insert them

293.71 -> into your spreadsheet. You can set the formatting so that you have data bars

298.72 -> that represent the value or you can change the color scheme for the value

302.89 -> sets. Have visual representation of your data as an icon, mark the top 10%. You

310.09 -> can add sparklines. That shows you a mini line graph of your data. There's so much

316.93 -> available here without having to go through the menus to manually create

320.95 -> each one of these items, so look for that icon in the bottom-right corner of any

324.94 -> of your tables. #7 AutoFit Column Width. This is by far the quickest way to

332.32 -> adjust the width of your columns. Just go to the space in between any columns

338.35 -> until your cursor changes and double-click. It'll automatically

342.46 -> readjust the size of the column to fit the widest point of the data anywhere in

346.6 -> that entire column. You can also highlight multiple columns, double-click

352.63 -> on any one of them, and it'll do multiple columns at the same time.

358.63 -> #8 Absolute Cell Reference. Excel uses two types of referencing - Relative and

364.66 -> Absolute, or a mixture of the two. As you can see in this cell B4 X C1 is the

371.979 -> cost times the discount. These are both relative references because they are

376.78 -> just referring to a certain location to the left or up three rows and if we were

382.24 -> to copy this formula down you would get errors because each one is still

388.15 -> referring to the one to the left and the one three above.

391.83 -> In this case it gets an error because it's trying to use Row 3 so what we

397.2 -> want to use instead is an absolute reference. We want C1 to be fixed to this

403.8 -> location for all the rows. The way to do that is to highlight that and hit F4 to

410.55 -> toggle between the different relative and absolute cell referencing. Now you

415.47 -> can lock the column the row or both and, in this case, we're going to keep this

419.97 -> locked to C1. Now when I copy it down it does the correct formulas for each one

427.28 -> always referencing the absolute reference of C1. Anytime you plan to copy

433.8 -> formulas make sure you set the relative or absolute referencing appropriately.

439.04 -> #9 Paste Special Values. Sometimes you want to eliminate formulas and copy

445.8 -> just the data from cells. In this example, I have a first and last name combined

451.44 -> using a formula. I want to highlight this, right-click, choose Copy, and paste it but

458.49 -> I want to paste just the values not the formulas, so if I right-click, choose

463.23 -> Paste Special, check this box for Values and hit OK. Now you can see it just has

470.19 -> the value and not the original formula. You'd be surprised at how often you need

476.04 -> to use this type of function because you want just data and not formulas, so make

481.68 -> sure to use Paste Special Values. #10 Drop-Down Lists. Let's say you have a

489.93 -> region North, South, East, and West and you want them to be selectable anywhere in

495.06 -> this list. Just highlight the rows that you want, selectable, go to the Data tab,

500.99 -> choose Data Validation from this list, change this to List, make sure this

508.83 -> checkbox for In-cell Drop-down is checked, and for the source you want to

513.6 -> choose your options available in the list, which is going to be these four. Hit

517.979 -> OK and now in each one of these rows you have an arrow that you can choose from

523.68 -> that list.

526.499 -> You can still type in data but if you try to enter it, it gives you an error because

531.819 -> the value doesn't match the data validation list that you created. You can

537.519 -> go back to the list, you can change the input message prompting you to enter the

543.369 -> data, and you can also edit and create an error alert whenever you type data that

548.499 -> doesn't validate. So if you want to make it easy to select data from a list and

552.67 -> validate it, use the Drop-Down List. #11 FILTER(). FILTER() is a new

560.199 -> dynamic array function added to Excel in 2020. One of the challenges with XLOOKUP

565.629 -> and other lookup functions is that they can't return multiple values from a list but

570.1 -> FILTER() does. This is how it works. Let's say we wanted to find all the East

575.41 -> region sales results from this list. You can use the FILTER() function by providing

582.639 -> it an array, which is going to be this entire list and what we want to include

587.139 -> is anything in the region that equals "East" and it's that simple. It returned

596.92 -> all the region East Reps, Products and Units. The FILTER() function spills the

602.199 -> results over into multiple rows and columns based on what it finds and if

607.12 -> you look at any individual one you can see that the formula is the same in all

611.62 -> of them but it originates from the upper-left corner. You can even get more

617.199 -> complex with the filter by taking the region and multiplying it by the rep

625.99 -> equaling your selected rep and now we have the combination of both where the

632.35 -> region and the rep match. FILTER() is a powerful new dynamic array function that

637.389 -> solves many issues with previous lookup functions. Look for it in the 2020 or

641.619 -> later versions of Excel. #12 Remove Duplicates. If you have a list of data in

648.879 -> Excel and you want to remove all the duplicates sets of information just

652.809 -> highlight it, go to the Data tab, select Remove Duplicates from the menu,

660.5 -> and in this case, it defaults to all three headings - First Name, Last Name, and

665.52 -> Amount. Where all three match I'm going to

668.55 -> remove one duplicate and it took that row from the list. Let's try this again

676.04 -> but this time let's do all of the duplicates where the first and last name

680.58 -> match and it removed two duplicates. This is a quick method to remove those

688.26 -> duplicates and it works on large lists of data. #13 Difference

693.959 -> Between Lists. Sometimes you want to see the difference between two lists of data.

698.7 -> This could be a monthly budget report or other similar list. If you want

704.73 -> to see the difference between two lists highlight the first one, hold down the

708.3 -> Ctrl key, and highlight the second one. The second one can be on a different

712.23 -> sheet if you want to, just hold the Ctrl key down when you click on it,

715.44 -> then go to the Home tab, select Conditional Formatting, Highlight Cells

721.47 -> Rules, and Duplicate Values. This shows the duplicates between the two lists. But

727.32 -> we can make it the Unique. Hit OK and now you've highlighted the differences

732.48 -> between those two lists. #14 Flash Fill. Flash Fill is an automatic

740.37 -> entry built into Excel to make you more efficient. For example, I've got a first

744.87 -> name and a last name and I want to combine them into a full name in column

748.41 -> C. I can start typing and it automatically detects the pattern that

752.76 -> I'm using with first and last name and wants to fill it in for the remaining

756.48 -> rows on that column. Just hit Enter and it Flash Fills the data. Flash Fill is

762.06 -> enabled by going into File, Options, Advanced, and under editing options make

769.62 -> sure this check mark next to Automatically Flash Fill is turned on. If

776.07 -> it doesn't appear to be working automatically then hit Ctrl-E to trigger

779.88 -> it and Excel does a great job of determining those patterns to do the

784.35 -> Flash Fill for you. #15 AutoFill.

789.82 -> Excel can complete patterns for you with AutoFill. So if I were to enter a 1 and a

795.49 -> 2, I highlight those and drag them down,

798.34 -> it's going to fill in the remainder of those numbers in sequence. I can do the

803.71 -> same thing with dates. You can use combination of words and numbers and it

817.36 -> goes horizontal as well as vertical.

821.85 -> AutoFill can use dates, times, weekdays, months, or any kind of sequential

827.98 -> numbering scheme that you come up with and is great at saving you time in data

832.03 -> entry. #16 Data Types. Data Types is a new feature added in 2020. It's

841.36 -> available by going to Data and into this category for Data Types.

845.95 -> Currently there are Stocks and Geography, but over time Microsoft will be adding

850.21 -> additional data types to the list. I've created a list of states and if I

855.1 -> highlight those and select Geography, it creates an icon showing the link to the

860.59 -> data type and gives you an insert option to add additional data from this list. I

866.41 -> can pick other variables of data like the Area, the Largest City, Population, and

875.62 -> a variety of other data elements. You can reference Data Types as well. I'm going

880.45 -> to create a formula for this state of Oregon and I'm going to select a data

885.73 -> element from here, hit enter and I've just referred to the largest city in

892.18 -> that state. You can use formulas anywhere in your spreadsheet once you've created

897.43 -> these data type links. Data Types is a great way to access information without

902.53 -> having to create your own data sets and over time those new additional data

906.55 -> types will come up that'll make this even more powerful. #17 Transpose.

913.35 -> Transpose allows you to switch the columns and rows on a table. I highlight

919.3 -> this table, right-click, Copy, select the new location where I

925.029 -> want to paste the reverse, right-click again, Paste Special, and choose Transpose.

931.92 -> Hit OK. It's now reversed the columns and rows. #18 Freeze Panes. Freeze Panes

941.709 -> allows you to lock certain portions of the screen. In this example, I have a

945.579 -> large set of data and when I scroll down I lose the top row header information

950.17 -> and when I scroll to the right I lose the left column header information. You

955.06 -> can solve this problem by clicking in the upper-left corner of your data, going

959.529 -> to the View tab, click on Freeze Panes, and select Freeze Panes from your menu.

965.04 -> Now when I scroll down the header row information stays and when I scroll

970.93 -> right the left column header information stays. To turn it off go back to the same

976.87 -> menu and choose Unfreeze Panes. So if you have a large set of data and you need to

982.18 -> lock those columns and header rows use Freeze Panes. #19 Text to Columns.

989.579 -> When using Excel you'll often find yourself copying data from a web page or

994.39 -> other application. I have a set of data and if I copy it and paste it into Excel

1000.74 -> it comes in as one line instead of separated out into columns like I want.

1006.06 -> There is a way to solve that problem. Go to Data, pick Text to Columns. In this

1014.16 -> case, the data is all separated with commas, so I'm going to change it from a

1019.38 -> tab to a comma delimited and in the data preview you see that it has figured out

1025.169 -> where to separate the data and I hit Finish and it spreads it among the

1030.6 -> different columns. It's as simple as that. The key is you need to have some

1034.62 -> delimiter in your data or if there's even spacing you can do it based on a

1039.089 -> fixed width. Either way it can convert it into columns and insert it into your

1043.439 -> spreadsheet. #20 Recommended Pivot Table. Excel has made pivot table

1050.4 -> creation much easier by recommending them for you. Click anywhere inside of a

1055.35 -> data table, go to the Insert tab, and choose

1059.2 -> Recommended Pivot Tables from the menu. Excel uses artificial intelligence to

1063.97 -> make the best recommendation of the data that you have in your table. You can

1068.47 -> choose from multiple different choices here and insert them into your

1072.46 -> spreadsheet on a new sheet, then you can edit the settings just like any normal

1076.99 -> pivot table. So if you're not comfortable with pivot tables let Excel recommend

1082.66 -> one for you. #21 Slicers. You've seen how to filter a list but there's

1089.65 -> another method to filtering your data. Take your data table, highlight it, go to

1095.169 -> Insert, Table and make sure this checkbox next to My Table has Headers is turned

1101.44 -> on. Hit OK. Now go over to Insert, Slicer and

1108.51 -> select the different filtering options you want and it creates a menu for each

1115.45 -> one of these slicers. Now just select the ones that you want and it filters them

1122.44 -> out based on that data. If you hold the Ctrl key down you can select multiples

1132.47 -> and filter just on that data. Slicers is like the cool version of the Filter List.

1140.9 -> #22 Conditional Formatting. You saw a sneak peak of Conditional Formatting

1147.66 -> with the Quick Analysis Tool but there are more options available from the menu.

1151.44 -> Let's say, on this table, we want to show all the entries with units greater than

1155.61 -> 15,000. Just highlight the units, go to Conditional Formatting from the Home tab,

1162.08 -> Highlight Cells Rules, choose Greater Than, and enter 15,000 and it highlights

1169.89 -> everything over 15,000. To clear the rules go back to Conditional Formatting

1176.1 -> and Clear the Rules from the Selected Cells. You have many more options

1180.9 -> available not just highlighting cells that are Greater Than, but you can do In

1184.92 -> Between numbers, Equal to certain numbers, anything that's a Duplicate Value. You

1190.08 -> can pick the Top 10%, the Bottom 10%, how many are Above Average.

1194.7 -> You can set Data Bars with different color codes based on the values, Color

1200.13 -> Scaling, Icon Sets and you can even create your own new rules based on all

1205.95 -> these different rule types. You can even use formulas. Just make sure you get your

1216.6 -> absolute referencing correct.

1223.34 -> So give yourself a better representation of your data using Conditional

1227.57 -> Formatting. #23 IF(). It's very common to use the

1233.78 -> IF() function to evaluate data in your spreadsheet. For example, let's create an

1238.55 -> IF() statement that determines when the units is greater than 15,000. That is the

1244.19 -> logical test. If the value is true then we want to print the word BIG and if the

1249.89 -> value is false we want to print the word SMALL. That's the format for an IF()

1255.71 -> function. Hit Enter and we have our results. Just remember that the logical

1262.1 -> test can be as complex as you want it to be and you can use a variety of

1266.54 -> different things for the value of true or false. There's a lot of flexibility

1270.53 -> with the IF() function. #24 3D References. If you're tracking monthly

1277.37 -> data in Excel, it's common to create a different sheet for each month and in

1281.6 -> this case I have a January, February, and a March sheet. They're identical with the

1287.3 -> exception of the units. Let's say I want to make a total of those three months, so

1294.14 -> the first thing I'm going to do is I'm going to hold down the Ctrl key and drag

1297.41 -> March over to a new sheet, then rename that Total and put a heading on it called

1304.01 -> Total. Now let's use 3D Referencing to add the quantities from each of the

1308.54 -> three tabs into this one cell, so click on the first cell. I'm gonna choose AutoSum.

1313.55 -> Now I'm going to go to the first tab, click on the first cell, and hold the

1319.64 -> Shift key while I highlight all three of these sheets, and hit Enter. That created

1326.63 -> a formula totalling January through March. Then I copy that down and now I

1332 -> have the total from all three sheets. That's how you use 3D Referencing. What

1337.28 -> if we wanted to add the Year to all of the sheets. I'm gonna hold down the Shift

1341.27 -> key, and highlight all these again, and right-click and Insert a new row. We'll

1349.91 -> Merge and Center, and call it 2020, and you can see that it added it to all

1355.149 -> the sheets. 3D Referencing is as simple as selecting multiple sheets when you do

1361.48 -> entries into formulas or type data into your sheet. #25 Forecast Sheet. It's

1369.73 -> really easy to create a forecast of data that you have in a table. Just highlight

1374.23 -> your table, go to the Data tab, click on Forecast Sheet, and it automatically

1379.899 -> calculates a trend based on your data. You can change the forecasted end date

1384.61 -> and extend it if you like, and change other options such as the confidence

1391.21 -> level of your data, change the range of your data. Hit Create. It creates a new

1396.94 -> sheet with your data in a table, including the forecast results and a

1402.19 -> graph representing your data. Hover over any spot on the data and you can see

1409.779 -> values at different periods of time. So if you want a quick representation of a

1414.279 -> trend line and your data use Forecast Sheet. Congratulations! You've made it

1419.44 -> through half of the tips and tricks list. The other half will continue in 15

1423.19 -> seconds but please take a moment right now to subscribe to Sele Training and

1427.149 -> click the Bell icon to get notified when new videos are released.

1430.809 -> Don't forget the entire 50 tips and tricks list is in the description of

1434.739 -> this video with hyperlinks to each. Now let's get back to it. #26 SUMIFS().

1442.529 -> The SUMIFS() function allows you to do conditional summing of data. In this

1448.57 -> example, I have a set of data for months and items and the amount of each and I

1455.08 -> want to create a sum of January. So I can use the SUMIFS() function. The first

1462.129 -> parameter it wants is the range and that's going to be this amount right

1466.149 -> here. Then it wants to know the criteria range so we want the month where it

1472.45 -> equals January and that gives us a 128 total, which is the total of these three

1479.529 -> entries. Now for this SUMIFS() we want to add just March for Item 2.

1486.49 -> So we're going to use the SUMIFS() function again and we're going to sum the same range

1491.83 -> of amounts, but the criteria is still going to be the month equaling March

1500.11 -> with the second criteria where the item equals Item 2, and that gives you the

1509.32 -> sum 245, which is the total of these three March Item 2's. #27 IFERROR().

1517.95 -> The IFERROR() function can be used to clean up bad data. For example, I have a formula

1523.33 -> here that calculates B divided by C so that it takes the Total divided by the

1528.73 -> number of Quantity and gives me the price Each. This formula works fine

1533.41 -> except when I get down here to the quantity zero and I end up with a

1537.46 -> divided by zero error. You can clean this up with the IFERROR() function. Just add

1544.48 -> the IFERROR() function in front of this formula and if there is an error let's

1550.36 -> just say zero instead. Now when I copy that down it corrects our divide by zero

1556.42 -> error. So if you want to remove those errors from your spreadsheet use the IFERROR()

1561.04 -> function. #28 Ctrl-Arrow Keys. Don't forget to hold down the

1568.179 -> Ctrl key when you're moving around a set of data. If you hold the Ctrl key

1572.35 -> down and hit the right-arrow it goes to the very end of the row, down-arrow goes

1577.36 -> to the bottom, left-arrow to the left, and up-arrow to the top. This is a quick

1582.16 -> method to use to scroll around a table of data. #29 Filled Maps.

1589.17 -> You can use Filled Maps to make a geographic chart of your data. Make sure

1594.58 -> you have location specific information here. It can be a state, a city, a zip code,

1600.25 -> GPS coordinates, or any other location related data elements. Just highlight

1605.05 -> your data in a table, go to Insert, Maps, and choose Filled Map. It inserts the

1612.37 -> chart on to your page where you can customize it using a variety of

1615.85 -> different settings. You can change the data labels,

1619.82 -> pick a different chart style, change the layout, and even change the color scheme.

1632.57 -> #30 PMT(). The payment function is useful to calculate a payment on a loan

1639.63 -> amount. In this example we've entered .045 and formatted the

1644.94 -> annual rate as a percentage. We're using sixty months of payments on a loan

1650.16 -> amount of $20,000. In order to calculate the monthly payment for this amount

1655.43 -> we're going to enter PMT(), pick the rate as the first parameter, and the key here

1661.17 -> is you need to divide this by 12 to get it into monthly periods instead of a

1665.76 -> total annual rate. Then the number of periods is going to be 60 on a total

1671.01 -> amount of 20,000. There are other parameters like Future Value for more

1675.9 -> complex calculations, but we won't use those at this time. End-parentheses on

1680.19 -> that and Enter and we get a monthly payment of $372.86. Now by

1686.01 -> default it makes this a negative number, but you can put a minus in here to

1690.87 -> reverse this to a positive if that's how you want to use the data. So if you

1694.95 -> intend to do any loan payments you're probably going to use the PMT function.

1699.65 -> #31 Show Formulas. When working with formulas in Excel it's sometimes

1706.08 -> helpful to see the formulas in an underlying cell. You can do that by

1710.22 -> double-clicking on a cell with a formula in it. It brings up the formula and it

1714.93 -> shows color codes for the cells that it's referring to. Another option is to go to

1720.96 -> File, Options, Advanced and scroll down to Display Options for this Worksheet. Check

1732.66 -> this box to Show Formulas in Cells and hit OK. That brings up all the formulas

1738.93 -> for all the cells in your sheet or you can use the Ctrl-~ shortcut to

1744.9 -> turn it on and off. These options make it easy to see formulas at a glance.

1750.35 -> #32 Advanced Select. Here's a trick to making changes to

1756.299 -> multiple items all at once. Come up to Find and Select, choose Find, type in

1763.35 -> something you want to search for, and select Find All, hit Ctrl-A to select

1769.53 -> everything in this list, and it highlights all of those items on the

1773.82 -> list. Now you can close the search and it still leaves them selected and at this

1780.39 -> point you can make changes. Anything you do will change all of those entries the

1791.61 -> same way. That's just a shortcut to make changes to multiple items.

1797.61 -> #33 Named Range Shortcut. You may be aware that you can highlight a range of data

1803.16 -> in your spreadsheet and come up to this name box in the upper left and give it a

1807.929 -> name. That gives you the ability to refer to it by that name anywhere in a formula

1813.03 -> on your spreadsheet, but what you may not be aware of is if you have a wide area

1820.049 -> of named ranges you can select the entire list all at once, go to Formulas,

1825.84 -> choose Create from Selection, and check this box for the Top Row. Hit OK and it

1833.16 -> names them all at once. You can see them in the Name Manager and

1837.27 -> there they are. Now you can refer to any one of these

1841.35 -> months by name. It's just a quick trick to name multiple ranges. #34 Hide Cells.

1849.39 -> There's a slick trick to hiding cells. In this example, I have 4 times 5

1855.929 -> equals 20 in a formula, but let's say we wanted to hide this cell from view. If

1861.299 -> you right-click on it, go to Format Cells, click on Custom, and enter 3

1867.66 -> semicolons, hit Enter. That hides the cell from view, but it still works both in the

1875.46 -> formula and its contained in that cell even though it's not visible. To turn

1880.95 -> this off, go back to the same location again and remove the three semicolons.

1886.57 -> A quick trick if you just want to hide a cell. #35 COUNTBLANK(). The COUNTBLANK() function

1894.639 -> is useful to count the number of blanks in a range of cells. Just type

1899.95 -> in COUNTBLANK() and put in the range that you want to count. It shows 6 empty

1908.11 -> cells. You can also use COUNTA() in the same range to count the number of cells

1917.049 -> that are not empty. #36 Natural Language Query. We've

1923.259 -> seen previously how you can click on a chart and go to the Ideas tab to see

1927.49 -> recommended charts of the data, but there is a Natural Query area here as well. You

1933.789 -> can type in things like Total Sales by Category and it creates a chart based on

1941.5 -> that query. Try Average Sales by Year. How about Top 3 Ratings by Product, or

1955.179 -> how about Which is the Most Common Product, or Products that Start with 'S'.

1965.759 -> Add to it the Total Sales of those products. Natural Query Language uses

1974.289 -> artificial intelligence to analyze your question and provide the data in the

1979.029 -> best format possible. You can even see it's question analyzed and the answer

1984.82 -> that it determines from your question. It's very powerful.

1988.62 -> #37 Goal Seek. Goal Seek is an advanced function in Excel that is part

1995.32 -> of the what-if analysis tools. In this example, say we have several items for

2000.389 -> sale. We know how many quantity there are of the first two items so we can

2004.62 -> calculate their totals but we don't know how many of the third item we need to

2009.24 -> sell in order for this number to be $6,000. That's our target. Yyou could guess

2016.62 -> the numbers by punching in things randomly until

2019.89 -> you reach your total of 6,000 but Goal Seek will calculate this number for us.

2025.11 -> To start a Goal Seek you need to click on the target number. This is the number

2030.51 -> that we want to change until we reach this target of 6000, then go to Data

2036.02 -> What-if Analysis, and choose Goal Seek. It set our target as the Set Cell. The value

2043.29 -> we want it to go to is going to be our 6000 and what we want to change in order

2048.39 -> to reach that target is this cell right here - the quantity. Hit OK and it does the

2055.05 -> goal seek and determines that 196.9 is the quantity. And that's just one

2062.13 -> example of Goal Seek but be aware you can use your imagination to come up with

2065.88 -> all kinds of scenarios for the what-if analysis using Goal Seek.

2071.64 -> #38 Insert Screenshot. Here's a tip to adding images from other applications. Go to

2078.87 -> Insert, select Screenshot and it shows the open windows that you have maximized

2084.51 -> on your computer. Select one and it inserts that image of that application

2088.89 -> in Excel. You can then resize and move it around however you want, and do the same

2095.55 -> with multiple images. You can also insert just a portion of your screen by using

2105.12 -> the Screen Clipping. So if you want to add an image from another application

2109.47 -> rather than doing copy and paste you can insert it directly. #39 Power Pivot.

2117.65 -> Power Pivot is a free feature that's installed as an add-in to Excel but it's

2122.85 -> not installed by default. To enable it go to File, Options, Add-ins. Come down here

2131.55 -> to the bottom and select COM Add-ins from the list and hit Go. Make sure to

2137.94 -> check this box for Microsoft Power Pivot for Excel. Hit OK and it adds this Power

2144.03 -> Pivot option to your menu. When you open that there's a whole new set of features

2148.56 -> available from the menu. Power Pivot allows you to connect to Oracle,

2153.329 -> SQL, and other external data sources, and use it for large sets of data. In

2158.339 -> fact, it expands the 1 million row limit of Excel to virtually unlimited numbers.

2163.65 -> It turns Excel into a Business Intelligence Analysis tool. I won't show

2168.569 -> you the details in this video but take a look at Power Pivot if you deal with

2172.619 -> large sets of data from a lot of different sources. #40 3D Maps. You

2179.67 -> can create stunning three-dimensional maps of your data. In this example, I have

2184.2 -> a table of countries with various amounts over multiple years. Click

2190.38 -> anywhere inside that table, go to Insert, and choose 3D Map from the menu. This

2197.789 -> brings up a world globe from Bing Maps. The first thing I'm going to do is add a

2202.769 -> Location so for that I pick the Country, and for the Height I'm gonna pick the

2210.119 -> Amount, and it shows a 3D representation of the Height on the globe. I'm also

2216.359 -> going to pick a Timeline for the year, and it immediately creates a timeline on the

2222.239 -> chart, and I can adjust this and it reflects the data over that time-frame.

2227.039 -> Let's position this to a different location. Now I can choose different

2231.809 -> themes

2238.15 -> but we'll stick with the default. You can also add labels which shows the country

2243.849 -> names, I can flatten the map or leave it as a globe, you can also find a location

2254.579 -> and it takes you to that location on your map. For the layer options let's

2260.65 -> change the thickness so it widens out the data elements on the screen and

2266.49 -> we'll make the height a little bit larger. All the changes I make are

2270.46 -> recorded to this tour and if I click on the settings I can give it a name, change

2275.589 -> the duration, or the amount of time between each transition. I'm also going

2282.19 -> to set this one to rotate the globe. Now let's play the tour and see what it

2288.4 -> looks like. It moves while it's updating the data elements. You can even choose a

2294.999 -> different location to begin your tour.

2303.089 -> You can create a video from this and choose from a variety of different

2307.799 -> resolutions, or you can capture screen and then paste it back into your chart. It

2318.9 -> also creates a note here that there is a 3D Map tour associated with this

2323.369 -> workbook. You can delete this but that's the only way you'll know there's a 3D

2327.839 -> map linked and your indication that you can come back in here, click on 3D Maps,

2332.099 -> and you can see your tour that you created. You can create multiple tours or

2336.749 -> you can select one that you are already created in the past and edit it however

2341.13 -> you like. 3D Maps is a great way to make visually stunning 3D images of your data.

2348.17 -> #41 ISBLANK(). Earlier we looked at how you

2352.979 -> can count blank cells but another useful tool is the ISBLANK() function. ISBLANK()

2357.869 -> returns true if a cell is empty and false if it contains any data. So if we

2364.44 -> check column C to see if it's blank, it's true for this row. You can combine it

2370.619 -> with the IF() statement to make it even more powerful. You can use the ISBLANK()

2381.39 -> function anywhere there's a formula with a true/false value. #42 Analysis ToolPak.

2388.259 -> Excel has an advanced hidden menu option available called

2394.2 -> Analysis Toolpak. You can access it by going to File, Options, Add-ins, and under

2402.39 -> the Excel add-ins hit Go, and check this box for Analysis Toolpak. Hit OK and it

2409.68 -> creates a new menu under your Data tab called Data Analysis. When you click that

2414.839 -> it brings up a menu with a number of complex data analysis tools like

2420.779 -> Correlation, Exponential Smoothing, Moving Averages, and a variety of other options.

2426.739 -> When you select one it prompts you for the input variables and output range for

2432.719 -> the calculation. Enter the pertinent information and hit OK and you

2437.04 -> get your data analyzed and the results output to your spreadsheet. I won't go

2441.66 -> into details but just be aware that the Data Analysis ToolPak is available by

2445.74 -> turning it on from the add-ins menu. #43 CONVERT(). The CONVERT() function

2453.45 -> is useful to convert data from one measurement into another. For example,

2458.25 -> let's type in CONVERT() and we're going to choose this original data value. It

2464.55 -> brings up a list of the options we have for conversion so let's take Days, hit

2470.16 -> Comma and then it gives me only the choices of values that I can convert

2474.6 -> days into, and let's pick Hours. And that's how easy it is to convert data

2480.63 -> from one measurement into another. #44 Get Data from Web. Did you know you

2488.76 -> can pull live data from a web page into Excel? For example, let's go to a web page

2494.49 -> called x-rates. This website provides live exchange rates for various

2502.32 -> currencies. I'm going to take a look at the US dollar conversion rates in the

2506.55 -> rate tables. Now that I have this table information available on the screen I'm

2511.38 -> gonna copy this URL, go back into Excel, go to Data, and Get Data from the Web, and

2521.57 -> I paste this URL, and hit OK. That goes to that web page, connects to the data, and

2529.05 -> brings up the different tables of information available. Now you can look

2533.19 -> through these tables to find one that looks like what you want and we'll take

2537.72 -> this table 1, which has a variety of different currencies against the US

2542.46 -> dollar. I'm going to load that table. It establishes a query and connection to

2549.03 -> that table on the webpage and it pulls my data into the spreadsheet. I can now

2554.13 -> reference this information just like any other table in Excel. Now that we have

2558.24 -> this data let's go up here to the Refresh menu and go to Connection

2561.9 -> Properties. I can set this to Refresh Every 60 Minutes or whatever time I like,

2568.62 -> and I can Refresh the Data Whenever Opening the File. This

2572.309 -> ensures an automatic update of the data. Hit OK and now I have a permanent link to

2577.829 -> data from the web. #45 People Graph. People Graph is a quick method to graph

2584.579 -> data from a simple two column table. Just go to Insert and select this icon for

2590.43 -> People Graph. That brings up a default graph. The first thing you need to do is

2595.98 -> Select the Data and choose a two-column table. The second column needs to have

2603.51 -> values that you can display in your graph. Click Create and you can also come

2609.359 -> in here and change the title. And that displays a graph of your data. You have

2618.329 -> multiple options available from the Settings. You can change the Type of the

2622.079 -> graph, the Theme, and even the Shape of the items that show up on the graph. Over

2636.66 -> the right-hand corner you can mark this as a Saved Image and now it's locked so

2643.14 -> that you can't make changes anymore. You can now resize and move it. Click again

2649.92 -> in the upper-right corner and you can turn that back off. A very simple way to

2655.53 -> create a quick graph. #46 SORT(). SORT() is a new dynamic array function added in

2663.9 -> 2020. It allows you to sort a list such as this list of states. Let's enter the

2669.39 -> function and we'll choose the entire table. The sort_index is either the first

2674.94 -> column or the second column. We're going to go ahead and sort based on state so

2678.809 -> we'll pick the first column, and it defaults to ascending order but you can

2682.68 -> put a -1 in here if you want to reverse it, and the final option is true

2687.18 -> or false whether you want to sort by column or sort by row. It defaults to row

2691.44 -> but column would be useful if you had a horizontal list. We'll choose the default

2696.48 -> and hit Enter and it sorts all those states alphabetically. We can go back and

2701.52 -> change the sort_index to 2 and then it sorts by the amount in the

2705.69 -> second column, and you can see it's sorted in the second column now. Dynamic

2711.06 -> Arrays will spill over into multiple columns and rows as it needs to fill the

2716.099 -> data, so even though this formula is in the upper-left corner, you'll see that

2720.66 -> it's grayed out but spills over into the other columns and rows. Just be aware

2725.04 -> these dynamic array functions are only available in the 2020 version or newer

2729.75 -> and will not function if you open a spreadsheet in an older version so you

2734.069 -> want to use them carefully. #47 Status Bar Info. The Status Bar is this

2741.599 -> row at the very bottom of your spreadsheet and it contains information

2745.17 -> that's useful while you're working. For example, if we highlight this list of

2750.06 -> numbers, down at the status bar it shows the average, the count, the sum, of that

2755.88 -> data. Anything that you select will show

2760.26 -> information down here. You can right-click and turn on and off different

2764.45 -> values on the status bar like the maximum and minimum values, the numerical

2770.25 -> count, you can turn off certain information, and even turn off other indicators like

2775.109 -> your zoom slider and your status. So if you want information in a quick glance

2779.19 -> take a look at the Status Bar Information. #48 Insert Multiple Rows.

2784.319 -> You may be aware you can right-click on a row, select Insert, and

2790.14 -> it inserts a row above that line but there's a quick method to insert

2794.369 -> multiple rows. Just select as many as you want, right-click anywhere in that group,

2799.31 -> click Insert, and it inserts all of those rows. You can do the same thing with

2804.66 -> columns. Select multiple columns, right-click, and Insert, and it inserts multiple

2810.69 -> columns. #49 CHOOSE(). The CHOOSE() function

2816.93 -> returns a value from a list using a given position or index. For example, if

2822.42 -> we wanted to choose the second item from the list of Blue, Red, and Green

2831.959 -> it returns Red, which is the second item in the list. Instead of explicitly

2837.849 -> listing the values you can select them from a cell location. So let's pick the

2844.93 -> second item from this, this, or this value and it returns the second value, which is

2853.749 -> Washington. With the CHOOSE() function you can have up to 254 values in that list.

2859.63 -> It does allow you to select from a range but watch what happens when we do that.

2864.599 -> Let's choose the second value from this range. Yyou might think it's going to

2870.999 -> return Washington but it just gives you an error and the reason why is because

2875.469 -> it treats the range all as one value. If we pick the first value from that list

2881.88 -> it returns the entire list of names. Let's add a second value which is this

2888.549 -> range. So we can return the first value which is that first range or we can

2894.609 -> return the second value which is the second range. So it will work with ranges

2899.859 -> but it treats them all as one value. You can make the position or index number a

2904.93 -> variable as well. We'll put in 1 up here and then we choose the position

2910.299 -> number 1 from these choices and it uses that cell to trigger which index

2918.819 -> number to select. So if you're looking for a quick function to pull values from

2926.559 -> a list use the CHOOSE() function. #50 UNIQUE(). The UNIQUE() function is a new

2934.42 -> dynamic array function that came out in 2020. It returns a unique set of values

2939.849 -> from a list. For example, if we do a UNIQUE() of this list right here, it

2946.93 -> returns the four unique values from that list and ignores the duplicates. The

2952.989 -> UNIQUE() function has a couple of additional parameters. The first one is

2959.17 -> by column and you would use true here if you intend to do a horizontal list but

2964.63 -> it defaults to false, and the second parameter is

2967.69 -> exactly_once. If this is false it returns every distinct item from the list which

2973.78 -> is the default. If it's true it returns items that appear exactly one

2979.09 -> time and, in this example, Pear is the only item that shows up exactly once in

2985.33 -> the list. As with any dynamic array function it will spill the data over

2992.2 -> into multiple rows and columns as needed. The UNIQUE() function is available in the

2997.75 -> very latest Office 365 Excel version so remember this if you have users with old

3002.91 -> versions of Excel because it won't function correctly. Hey, if you want to see

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

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

3015.09 -> your support!

Source: https://www.youtube.com/watch?v=FXs3WG7M-qk