Intermediate Microsoft Excel Tutorial - Lesson 1

Aug 14, 2023

Intermediate Microsoft Excel Tutorial - Lesson 1

Welcome to my intermediate Microsoft Excel Lesson 1 tutorial video! This video will cover some essential Excel functions and tips that will take your spreadsheet skills to the next level. I’ll go over Pivot Tables and Pivot Charts, demonstrate how to use the VLOOKUP function with some useful tips and tricks, and show you how to make multiple dependent drop-down lists.

Additionally, I’ll show you how to use the UNIQUE function to easily extract unique values from a dataset, add data validation to a cell to control user input, and use a nested FILTER function with the UNIQUE function to manipulate and filter data with precision.

Whether you’re a student, a professional, or just someone looking to enhance your Excel skills, this video is perfect for you. With our step-by-step instructions and practical examples, you’ll be able to apply these techniques to your own spreadsheets and improve your data analysis and visualization skills.

So, if you’re ready to take your Excel game to the next level, be sure to watch this tutorial and learn from the best. And don’t forget to like, share, and subscribe to our channel for more exciting Excel tutorials!

0:00 Introduction
1:12 How to create a PivotTable from range or table
2:16 Recommended PivotTable
3:01 PivotTable from range or table
3:55 Add PivotTable fields to report
4:50 Change PivotTable fields layout
7:20 Sort columns or rows in PivotTable
8:58 Add filter fields
9:59 Create a report on a new tab for each employee
11:51 PivotTable tips and tricks
15:43 Add a PivotChart
19:02 How to use the VLOOKUP Function in Excel
27:42 How to make multiple dependent drop-down lists in Excel
29:42 Using the UNIQUE Function
30:40 Adding data validation to a cell (drop-down list)
32:03 Using a nested FILTER function with the UNIQUE function
35:58 Using VLOOKUP to get the final result

Practice Sheet PivotTables: https://bit.ly/PivotTable_data
Practice Sheet VLOOKUP: https://bit.ly/VLOOOKUP_data
Practice Sheet Multiple Dependant Drop-downs: https://bit.ly/Drop-Down_list

Intermediate Lesson 2 - How to use Power Query:    • How to use Power Query - Intermediate…
Intermediate Lesson 3 - How to use Power Pivot:    • How to use Power Pivot in Excel - Int…

How to use the XLOOKUP Function:    • How to use the XLOOKUP Function in Mi…
Drop-down lists with XLOOKUP:    • How to use XLOOKUP to Create Dependen…
Drop-down with the INDIRECT function:    • Quickly Create Multiple Dependent Dro…
Excel beginners video tutorials:    • Microsoft Excel Beginner’s Tutorials

Function Lessons in Excel:    • Formulas and Function in Microsoft Excel

Content

0.15 -> - Hi there. Welcome to Teacher's Tech.

1.86 -> My name is Jamie, and it's great to have you here.

4.38 -> In today's video tutorial, we're gonna be taking a look

7.05 -> at Intermediate Microsoft Excel.

9.6 -> So, this is Lesson 1

11.07 -> in my Intermediate Microsoft Excel series,

13.95 -> and we're gonna be covering three different topics today.

17.1 -> We're gonna be looking at how to create

19.11 -> a PivotTable with graphing,

21.3 -> we're gonna be looking at the VLOOKUP function,

24.3 -> how to be using it,

25.53 -> and we're also gonna be looking at how to create

27.66 -> a multiple dependent dropdown in Microsoft Excel.

31.41 -> So, all these are gonna be timestamped down below

33.78 -> so you can jump to different parts of this lesson.

36.36 -> So, let's get started with the first one

38.19 -> with pivot tables in Microsoft Excel.

40.623 ->

43.86 -> If you would like to follow along with today's tutorial,

46.32 -> I'll put the links to the worksheets that I'm using

48.78 -> down below in the description.

50.04 -> Then you can click on the link,

51.36 -> download, open them up, and follow along

53.88 -> as I'm showing you in today's tutorial.

56.52 -> Also, it would help me out a lot to know

58.56 -> if these tutorials are helping you

60.03 -> by hitting that like button

61.65 -> or maybe even leaving a comment down below

63.66 -> what you're looking for in future intermediate lessons too.

67.89 -> So, make sure you do hit that subscribe button

70.502 -> and that notification,

71.94 -> then you get notified when my next tutorials do come out.

75.3 -> So, we're starting with how to create a pivot table,

78.27 -> and this is the data that we're using here.

80.82 -> If you haven't downloaded this yet,

82.32 -> just hit pause on the video,

83.79 -> go to that link I mentioned before

85.23 -> and download it and open it up.

87.66 -> Now, to do an insert, it's very simple,

90.63 -> you just go up to the menu and hit Insert.

93.3 -> And below in the ribbon,

94.56 -> you're gonna see pivot tables right away.

96.51 -> As I go through these pivot tables,

98.31 -> I'm gonna give you some tips and tricks also.

100.74 -> And I'm gonna start with the tip right away.

102.72 -> And the first tip is to select inside anywhere in the range

107.61 -> or the table if you're using a table

109.89 -> before you go and click on PivotTable because what it does,

113.31 -> it automatically finds the range for you.

115.5 -> So, here's an example.

117.21 -> If I go up to PivotTables,

119.34 -> notice that they have some recommended pivot tables here.

122.7 -> I'm gonna cancel and click out

125.19 -> and I click on recommend it again and it doesn't know.

128.22 -> Now, I could still select my my table or range from here

131.85 -> and it'll be fine.

132.9 -> But just as a quick tip, if you select inside of it

136.53 -> and pick PivotTable or Recommended PivotTables,

139.17 -> it's gonna know where the data is coming from.

142.41 -> Now, I just wanna mention a little bit

144.15 -> about the Recommended PivotTables.

146.91 -> This is a quick way to to create them.

150.63 -> Usually, they're pretty accurate with probably the main ones

152.97 -> that you would want, but you can see where it goes

155.64 -> sum of unit costs by regions, sum of unit costs by item.

159.78 -> It does some recommendations based on the data

162.3 -> and the titles that they think that you would want.

165.69 -> So, you can always start with Recommended PivotTables

168.99 -> and choose one of them,

170.67 -> but we're gonna be going from PivotTable on it.

173.88 -> So, and after we go through PivotTable,

176.55 -> you'll understand after you insert

178.38 -> the Recommended PivotTables,

179.88 -> you can still make the same changes.

181.77 -> So, I'm just gonna go ahead, click PivotTable,

184.32 -> and you can see that right away

186.45 -> since I'm clicked inside of the range here

190.11 -> that it has the sheet and the range from A1 to G44 here.

196.02 -> Now, where do you want this pivot table to go?

199.05 -> So, this is important, do you want a new worksheet

201.03 -> or do you want it to be on the same worksheet?

204.33 -> So, I'm gonna go New Worksheet

207.12 -> and it's gonna open up another tab.

208.92 -> You can choose what you wanna do on that

211.47 -> or you can set a different location here.

214.08 -> So, you can also choose

215.22 -> whether you want to analyze multiple tables,

217.41 -> and I'm not doing that in this case,

219.36 -> but I'm gonna go ahead and hit OK

221.46 -> and what you get if I look down at the tab at the bottom,

224.52 -> you can see I have a new sheet right here

227.46 -> and now they're asking me to start building my report

231.33 -> by selecting my different fields to put onto it.

236.25 -> So, now we have to be selecting our pivot table fields,

239.4 -> and you look over in the right

240.87 -> and it can be done very quickly.

242.85 -> So, if I go ahead and I'll just click on Sales Rep,

246.36 -> and what you notice is it gets put into rows

249.93 -> and now the names are gonna go down this column

251.97 -> and then the data as I select it would be put into rows.

255.99 -> You can quickly change this, so I could drag it into columns

260.25 -> and now the names are gonna go across the top

262.92 -> and we are gonna have our information

265.41 -> below it in the columns.

266.94 -> Now, we can quickly turn this off and on, the Sales Rep,

270.12 -> so if I click off of it, it goes away.

272.58 -> We can drag these down too.

274.17 -> So, if I drag this into a spot,

276.69 -> I could drag it around into columns,

278.82 -> you can see how easy it is to manipulate.

281.64 -> So, I have another tip here that I want to give you.

284.25 -> So, this is kind of the default setup here

286.62 -> with the fields down below where you drag into.

290.25 -> If you go up to the gear here and Tools and dropdown,

294.33 -> you can pick a different way to view it.

296.79 -> So, I prefer this one right here, I'm just gonna select it,

300.33 -> and we get these on the side here

303.69 -> with our different fields that we can just drag across.

305.88 -> Now, that's up to you what you prefer.

307.68 -> And the other thing I want to point out

309.6 -> with if we go back to it, you can sort your fields A to Z.

314.1 -> So, if you go ahead and select it,

315.51 -> you can see how it changes it.

317.64 -> Now, this if you had a lot of different fields,

319.89 -> this would just make it easier to find them.

321.81 -> So, just remember those couple tips right there.

324.81 -> Okay, let's add some more things to this now.

327.57 -> So, we're gonna have our Sales Rep here,

330.15 -> and I want this in columns,

332.49 -> and I'm gonna place it here so you can see

334.92 -> some of the ones are hiding right now.

336.9 -> And the other thing I want to add,

338.49 -> so let's go ahead and add our totals.

341.76 -> And you can see as I click on different ones

344.4 -> how quickly it starts to change.

346.53 -> So, I can click on and off.

348.6 -> I'm gonna go ahead and click on Units.

351.42 -> It shows all my sums of units here,

353.85 -> but what I want is to have items.

355.92 -> So, when I click my Items, now I do want this in rows,

359.1 -> it places them down here.

361.23 -> So, now if I pick my Units,

363.39 -> it fills in so I can see each person.

366.12 -> So, if I look at Dwight's, I can look at per binder,

368.58 -> per desk, and all the different items,

370.77 -> and it gives me the grand total there.

373.11 -> So, these are just some different ways

374.85 -> you can get some quick,

376.89 -> your report being built by selecting the fields.

380.58 -> So, did you notice if you click out of the data

383.07 -> that the pivot table fields go away?

385.56 -> So, click back in and they reopened here.

389.1 -> Now, I wanna point out just if you're adding more fields,

391.8 -> let's try, I'm gonna add a region here.

394.17 -> So, if I click on Region,

395.91 -> you can see it added to the rows at first

398.31 -> and then it's gonna be breaking down this way,

400.38 -> it breaks down the binder,

401.94 -> and based on the central, north, and south

404.16 -> where it was selling and being sold in different places.

407.4 -> Now, if I bring this over to columns,

410.64 -> you can see now that it's gonna go across the top.

413.43 -> It's kind of breaking it between the different sales people.

417.81 -> So, in the case of someone like Erin,

419.67 -> you can see that she sold in the central and north

423.09 -> while Dwight only sold in the central here.

426.6 -> So, go ahead, try different things,

428.4 -> try adding by selecting different ones,

432.33 -> selecting, seeing how it works

434.19 -> to get the different reports on it

436.68 -> and then moving from rows to column

438.3 -> to see the ways you can adjust it.

440.31 -> Now, another way you can adjust it is to sort,

443.43 -> and if I look over here you can see that we have a dropdown,

446.847 -> and this is gonna help me sort.

448.65 -> So, in this case, this is gonna sort my column labels,

451.8 -> which are the sales reps here,

454.56 -> and if I drop down I can go ahead

457.5 -> and select individual ones.

459.06 -> So, if I said okay, let's deselect

461.79 -> and I'm gonna go and let's compare Dwight,

464.52 -> and I'm gonna compare Jim and hit OK.

467.16 -> And you can see that we have the two right here

471.45 -> and I can look side by side to see their totals

474.15 -> of their items being sold.

476.13 -> If I wanted to see total of amounts in different ones,

479.13 -> I could pick a different thing to add

481.56 -> from over here to add it.

483.63 -> And you can see how quickly it can adjust.

486.03 -> So, if you drop down again,

487.32 -> I could select everything, hit OK,

489.6 -> and everything goes back on.

491.16 -> I can select this way too.

493.02 -> So, if I wanted to sort a little bit more,

495.9 -> if I didn't wanna see all the different items,

498.09 -> maybe I just wanted to see paper,

500.01 -> I could hit OK, and now I'm just seeing paper,

503.07 -> and I see the salesperson

504.48 -> so it makes it easy to look at that way.

508.2 -> So, go and try a few different ways to sort.

510.84 -> Now, I wanna show you how you can actually build

513.99 -> an individual report and it will create a new tab down below

518.52 -> for each sales rep, or depending on what you're creating,

521.7 -> and it's done only with only few clicks.

525.33 -> So, before I show you

526.68 -> how to create a tab for each salesperson,

529.05 -> I wanted to point out the use of filters.

531.3 -> So, let's say in this data with the units,

534.18 -> I just want to to see by region also,

536.79 -> and I like to filter through there.

538.29 -> So, if I go and take Region and put it in Filters,

541.14 -> you can see up top,

543.18 -> I can simply now dropdown and I could be looking

546.39 -> for a certain region or zone.

548.25 -> So, if I wanted to see in central, I hit OK,

550.53 -> and I just get those results back.

553.35 -> You can also see that you can select multiple,

555.6 -> so if you wanted more than one or switch it back.

558.84 -> It's easy to add more than one filter,

561.21 -> so or even move from different ones,

563.01 -> I could take this sales rep here and move it up,

566.97 -> and now I can sort by region and sales rep.

570.27 -> So, if I was looking for, let's say, in the north,

574.08 -> and I'm gonna also choose a sales rep,

576.57 -> I'll choose Pam, hit OK,

578.82 -> I can see in the north she sold this many.

581.91 -> If I was gonna go back and check out a different region,

585.39 -> at this point you can see

586.53 -> now she hasn't had got anything in the south,

589.05 -> so I just wanted to point out the use of filters.

591.75 -> But now I wanna show you how you can use the filters

594.3 -> to create a tab for each sales and person in this example.

599.49 -> So, I'm gonna go ahead and deselect the items

602.22 -> that I have here and I'm gonna build another report.

605.1 -> And remember, I want a new tab for each one,

607.98 -> for each salesperson.

609.21 -> So, I do need a total,

611.1 -> and this is gonna go into the values here.

613.95 -> I do want the item there so I can see the item

617.61 -> beside the total here.

619.14 -> And we're gonna need the sales rep.

620.91 -> And I'm gonna actually add this to the filter,

623.28 -> so I'm just gonna drag it into the filter like this.

627.09 -> Now, something else I wanna point out

629.85 -> is to format your numbers here.

632.22 -> So, if I go and just select in here,

635.01 -> I can right-click and number format.

637.95 -> So, maybe you wanted in accounting or currency,

641.82 -> you can see the different examples that goes through.

644.25 -> So, if I want two decimal places and to look like this,

647.34 -> I'm gonna go ahead and hit OK,

649.38 -> and you can see how it formats it.

651.27 -> You can choose the format that you would like.

654.57 -> The other thing I wanna show you

655.89 -> is if I right-click on this, you can sort this.

659.04 -> So, maybe I wanted from smallest to largest

661.29 -> or largest or smallest or smaller,

663.48 -> I'll just go largest to smallest, just like this.

666.09 -> So, some things that you can do to it.

669.36 -> Now, what I want to do is generate

671.82 -> the individual reports on each tab.

674.52 -> If I go ahead and dropdown

677.43 -> and go to Options right here,

680.25 -> and you can see Show Report Filter Pages.

683.58 -> So, when I select this,

684.78 -> we're gonna get Show Report Filter Pages.

687.63 -> If I go ahead and hit OK now,

690.69 -> it's gonna go ahead and if I look at the bottom,

693.42 -> so if I look, there's Andy's,

695.34 -> there's Dwight's, there's Erin,

697.71 -> I'm gonna get an individual report for each salesperson.

703.62 -> And you could adjust it depending on what you want.

705.72 -> So, I wanted to make sure you understood

707.22 -> how you could do that very simply

709.89 -> inside the pivot table fields.

711.93 -> So, I'm just gonna go back,

713.88 -> you can see as I go through these different ones,

715.53 -> I'm just gonna go back to the original pivot table

718.83 -> that I was working on here

719.82 -> and I just wanna show you a couple other things

721.41 -> and some more tips.

722.82 -> Now, let's say I was curious

724.38 -> where this 2,135.14 number came from.

729.03 -> If you double-click on a number,

730.5 -> so I'm just gonna double-click on this number,

732.33 -> it opens this up and shows how that number was created

738.33 -> by all of this information.

740.31 -> So, that was by a double-click,

742.35 -> it opened up a new sheet when I did it,

744.42 -> you can see there's a Sheet16 beside it now.

747.33 -> So, just try double-clicking on a number

749.79 -> and it's gonna show you how it's even broken down more.

753.33 -> Now, some other things I wanna show you with formatting,

756.72 -> and usually things can be done in a few different ways.

759.51 -> I'm gonna just show you if I go down to value here,

762.75 -> right here, and just drop down,

765.33 -> you can see that there's Value Field Settings.

768.18 -> You can also get to Value Field Settings

771.06 -> if you right-click on them too.

772.56 -> You can see Value Field Settings right here.

775.11 -> So, if I go ahead and choose value, this settings here,

779.4 -> Value Field Settings.

780.78 -> Let's say I could say an average account

784.44 -> if I was doing a Count, hit OK.

786.63 -> Well, the dollars don't make any sense anymore,

788.88 -> but it's just counting up,

790.62 -> here it would be 15 rather than $15.

793.2 -> So, depending on what you were trying to show

796.77 -> in your pivot tables,

798.03 -> remember that we have value settings

800.37 -> and you can change what you want right through here.

804.99 -> So, I'm gonna leave this as Sum.

807.57 -> Now, another thing I wanna show you

808.89 -> when you're right-clicking is if you right-click,

812.25 -> you can Show Values As.

814.02 -> So, I find this is a cool way to show percentage.

816.78 -> So, do we want,

818.43 -> you know, you try a few different ones.

820.2 -> So, if I say % of Row Total, Column Total,

823.95 -> it'll say Column Total,

825.45 -> I can see that the binders make up 48%,

829.95 -> the pens will make up 21% ,

832.65 -> and it just shows you in a different way.

834.78 -> So, all these different reports can be generated

837.72 -> and remember, you're right-clicking

840.57 -> to get to more of these here.

842.91 -> So, I just go back to no calculation and it's gone.

847.92 -> So, now I wanna show you how you can apply

850.38 -> some conditional formatting to your pivot table numbers here

854.19 -> before we get into the graphing and charting.

856.71 -> So, let's say if we wanted this

858 -> to be like a data bar through here.

860.97 -> So, what I need to do is go back up to the Home tab

864.12 -> up top here and look under Conditional Formatting.

867.78 -> If I dropdown,

869.16 -> you can see we can apply all these different things,

871.26 -> but we're looking at the data bars,

872.91 -> data bars and if I hover over,

875.19 -> notice it's just doing the cell that I'm selected in

878.46 -> and I wanted to be applying to all of them.

881.07 -> In this case, just go to More Rules right here

884.19 -> and you can see apply rule to only that cell.

887.4 -> No, we want it to apply to that cell

890.13 -> showing the Sum of Total Values here.

892.26 -> So, if I select this one here, that will do the trick.

896.16 -> Now, the other thing,

896.993 -> do you want the numbers to be shown?

898.74 -> So, right now we show both

900.15 -> or do you want to be just the bar only?

902.73 -> So, I can go and pick a color now

905.16 -> and it will preview down below,

906.6 -> so maybe I pick an orange just like that

909.54 -> and I'm gonna go ahead and hit OK,

911.88 -> and now I have a data bar applying to these different ones.

916.83 -> So, just different way to show your reports

921.36 -> that you can add conditional formatting to them.

924.03 -> Okay, let's move over to graphing or charting

926.88 -> our pivot tables now.

928.53 -> So, let's say you wanna chart your pivot table report

931.62 -> just to make it look a little bit better,

933.15 -> a little bit easier for people

934.89 -> to just take that quick glance and know what's happening.

937.997 -> So, I'm just gonna select inside of this here

941.1 -> and I'm gonna go up to Insert.

943.65 -> And take a look, we have PivotChart right here.

947.76 -> We do have the dropdown, I'm just clicking on it here.

950.49 -> And right away, so it defaulted to the column.

954.03 -> I can pick, I just got an idea maybe of different ones

957.42 -> how it looks here just by going through.

960.48 -> I'm gonna keep this simple,

961.71 -> I'm going to create a pie chart here.

965.16 -> You can see up top, if I wanted to see a different view,

968.1 -> I can hover over to get the view

970.83 -> to try any of these different ones.

972.6 -> I'm gonna just keep it to the pie right now.

975.33 -> Okay, so now just hit OK on it.

977.61 -> And I have this in here, I can move it around,

981.15 -> I can size it if I wanted to differently

984.24 -> if you wanted it larger here.

986.22 -> Now, I wanna point out this is dynamic

988.62 -> so I can take a look at different things.

990.45 -> So, if I wanted to take a look at the sales rep here,

994.41 -> maybe I wanted to compare different ones,

996.48 -> I could select multiple, maybe I just wanted to pick one.

1000.08 -> So, if I pick Michael Scott,

1001.76 -> I can see just the one person's here.

1004.61 -> So, it's going through, finding the information,

1007.16 -> and breaking it down for me.

1009.53 -> You can see the items or over here.

1012.5 -> So, if I go back I can go and select all

1015.17 -> and it puts it back again.

1016.82 -> So, you can sort using the chart to give those quick looks.

1020.81 -> I can dropdown here also,

1023.42 -> and if I wanted to maybe deselect

1026.27 -> if I just wanted two things here.

1027.74 -> So, if I had paper and desk, hit OK,

1030.05 -> and it just compares the two different ones.

1032.66 -> So, you can do all these different things

1034.52 -> within the chart still

1036.53 -> just like we were doing just with the data before,

1039.23 -> so but you can apply this to the chart.

1042.56 -> So, now what I wanted to show you too,

1044.69 -> you can make this, customize this look,

1047.93 -> if you just double-click in it.

1049.64 -> If this wasn't open, this Format Chart Area,

1053.06 -> just double-click on it and it will open it up.

1055.97 -> You can see I can quickly start to change things

1058.82 -> whether I want it to be maybe a gradient fill,

1061.88 -> you can see how the background changes.

1063.62 -> I can adjust my colors down here

1066.32 -> if I wanted to add different ones,

1068.09 -> if I wanted a pattern, pattern fill,

1070.37 -> you can play with all these different options.

1072.5 -> We have our text options also here.

1076.16 -> So, if you wanted to change different fonts and colors,

1078.98 -> you can adjust all those things.

1081.14 -> So, if I go ahead and select,

1082.939 -> maybe I wanted this to be differently, I can even dropdown,

1087.68 -> so that's the Chart Title.

1089.21 -> Or if I wanted maybe the Legend here,

1092.21 -> you can see how it selects different things.

1094.13 -> So, I could go ahead, maybe I'll add,

1097.67 -> I can change the fonts like this.

1099.47 -> If I wanted the legend to be positioned differently,

1102.89 -> I can start to change things all around.

1105.05 -> So, all these different things that you can play with

1108.08 -> to get the chart looking how you want.

1110.75 -> You can move these inside too,

1112.37 -> so if I wanted to place these in different places,

1115.22 -> again you can size your chart differently

1117.68 -> to get it the way you want.

1120.44 -> So, I hope you like this, this intermediate pivot tables.

1124.79 -> I'm gonna do more about pivot tables in future lessons

1128.93 -> just because there's more that you can do with this.

1130.85 -> I thought this would be a good start for you

1133.46 -> if you kind of just maybe touched the surface to them before

1136.7 -> or maybe haven't used them at all either.

1139.28 -> So, now we're ready to move on to our next section.

1142.58 -> So, now I wanna show you

1143.96 -> how to use VLOOKUP in Microsoft Excel.

1147.05 -> VLOOKUP is a very powerful function that you can use

1150.59 -> to look up something in a column vertically,

1153.59 -> that's where the V is gonna come from.

1154.94 -> So, it looks down a column that you set

1157.88 -> 'cause it's gonna be the furthest left

1159.59 -> and then it returns some item in a that corresponding row.

1164.78 -> So, in this case here I already have VLOOKUP in this cell

1168.5 -> already created and I'll create a new one here.

1170.45 -> If I just go ahead and select this cell,

1173 -> you can see in the formula bar, it's already there,

1176.12 -> but what it's gonna go through,

1177.5 -> so it looks at GOTC right here and it finds it,

1180.62 -> I told it to go down the furthest left of the selection

1183.29 -> and this is this vertical column here.

1185.57 -> It finds GOTC,

1187.22 -> then I told it to look up in that row, this salary here.

1192.89 -> So, it gave me back 68,000.

1195.56 -> So, let me just change it,

1196.64 -> so instead of C, I'm just gonna go put I here,

1201.14 -> and it returned 58,000.

1202.85 -> So, it went over to the furthest left here,

1205.43 -> found the range that I selected

1207.98 -> and it went down and it found GOTI right here

1212.15 -> and it went across and found $58,000.

1214.72 -> So, this is a small data set here,

1217.79 -> and if this was a large one,

1219.83 -> you would probably see how much more powerful this is.

1222.5 -> But if you had thousands of employees

1224.48 -> and you're looking up something like this,

1225.89 -> with this formula you can simply just do those searches

1229.52 -> really, really quickly.

1231.35 -> Now, something I just wanted to point out here with VLOOKUP,

1235.55 -> I was just taking a look at the XLOOKUP function

1238.97 -> because XLOOKUP has a few more capabilities to it.

1242.51 -> With VLOOKUP you can only look to the right,

1246.47 -> but XLOOKUP you can actually look left or right.

1249.41 -> And with XLOOKUP you can kind of do

1251.99 -> the HLOOKUP or VLOOKUP together.

1254.42 -> So, it's something I would recommend

1255.74 -> if you have a newer version of Microsoft Excel,

1258.38 -> I do have a video tutorial on that,

1260.48 -> and I'll put a link in the description now below it

1262.37 -> and up and above in the card.

1264.62 -> So, let's go ahead and start our VLOOKUP

1267.53 -> and build it for this sheet.

1269.45 -> And if you haven't got this sheet open, hit pause,

1271.97 -> download it and follow along.

1274.46 -> So, we're gonna go through

1275.6 -> the VLOOKUP function a couple times,

1277.64 -> couple different examples in two different cells.

1280.1 -> The first place we're gonna put the VLOOKUP function

1282.56 -> is right in this cell.

1283.97 -> So, what I want to have happen

1285.47 -> is it's gonna look up an employee name

1287.72 -> and bring me back an employee designation.

1290.51 -> So, it needs to go over to this column here

1293.78 -> and then look down this column here

1296.36 -> to find that the designation in the same row

1301.13 -> that it found the employee name.

1303.11 -> So, we're gonna start in this one first time

1305.72 -> we're gonna go and just go Insert Function up here.

1309.32 -> So, open up that one, Insert Function.

1311.87 -> If you don't see VLOOKUP, just do a quick search.

1314.3 -> You don't need to type the whole thing here.

1316.46 -> I'm just gonna hit Go and then double-click on it.

1319.4 -> So, we get our four different arguments here.

1322.43 -> So, the first argument's gonna be the Lookup Value.

1325.34 -> Well, the Lookup Value is what the name

1327.44 -> is that I type into this cell.

1329.45 -> So, this is gonna be the look up cell

1331.46 -> or look up value in this cell right here.

1333.71 -> So, I'm gonna click in I6,

1335.36 -> and you can see it put I6 into there.

1338.12 -> The next is the table array.

1340.46 -> So, I'm gonna click in this spot

1342.29 -> and the table array is gonna be this selection.

1345.17 -> And remember, it's gonna be the furthest left column

1347.9 -> of that array that it's gonna search down.

1350.81 -> So, it's gonna look for a name here.

1353.84 -> And so, I need to make sure

1355.01 -> this is the furthest left column, and it is,

1357.11 -> so I'm gonna select here.

1358.7 -> And I need it to go to at least go to employee designation.

1363.32 -> It could go all the way to salary if I wanted,

1365.18 -> but it doesn't need to,

1367.01 -> but I'm gonna leave it right there for this one.

1370.1 -> Now, the next one is the column index,

1373.34 -> and this is how it works.

1375.5 -> So, in B, this is the first part

1377.9 -> of the table or range here.

1379.64 -> So, this is column one, even though I have A here,

1383.21 -> there's nothing in it, this doesn't count,

1385.37 -> so B is number one, C is number two, D is number three,

1390.35 -> E is number four, and F is number five.

1393.23 -> So, we told it to,

1394.94 -> it's gonna look down the furthest left of my array,

1397.7 -> that is this one,

1398.99 -> and I need it to look down what's the column

1402.65 -> I need it to search through?

1404.06 -> Well, it's gonna be D right here,

1406.31 -> and that's three because it's one, two, three,

1409.85 -> so I need to put three in here just like that.

1414.26 -> So, the next thing what I wanna do

1415.94 -> is type in a true or false

1419.78 -> or I could leave it blank too,

1420.89 -> but in this case I'm gonna type false

1422.69 -> and that means an exact match.

1425.03 -> If I put true, it's an approximate match,

1426.98 -> but I wanted to find an exact match in this case,

1429.2 -> so I'm gonna type in false like so, and I'm gonna hit OK.

1433.61 -> And it came back, notice it came back with N/A here,

1437.72 -> that's because I don't have anything typed in.

1439.64 -> So, let's just type in John here

1441.95 -> and we'll type John and it returns software engineer.

1445.37 -> So, went to the furthest left of the array

1447.83 -> that I showed that I selected

1450.47 -> and then it went to column three

1452.6 -> and found what was in there, so that's software engineer.

1456.05 -> So, if I go ahead and type Bran here,

1459.83 -> it went down this column, the furthest left,

1462.32 -> and then it searched column three and brought back analyst.

1465.89 -> So, this is working quite well.

1467.57 -> You can see it doesn't take a lot

1469.19 -> to create the VLOOKUP function.

1471.56 -> One problem is if you're using false,

1474.41 -> I'll show you the problem if we go to Bran here,

1476.63 -> and I'm gonna just click in here

1477.83 -> and let's say there's some spaces just like that.

1480.92 -> Notice it went to N/A?

1483.44 -> It's because we are looking for an exact match,

1486.65 -> and you gotta be careful not if you're using the exact match

1489.65 -> not to have spaces.

1491.42 -> So, as soon as I get back,

1493.04 -> I delete that again and hit Enter,

1496.31 -> you notice it works again.

1497.57 -> So, I just wanted to point this out.

1499.4 -> Okay, let's run through VLOOKUP one more time.

1502.97 -> So, I'm gonna go ahead and recreate the top one

1506.66 -> from up here for VLOOKUP.

1509.18 -> Now, the reason I'm gonna do this

1510.38 -> because it's a little different on how to count the column.

1513.47 -> So, I'm gonna go, what do I want to have happen?

1516.05 -> Well, the VLOOKUP is gonna go into this cell, so it's I10,

1520.4 -> and I'm gonna start my function this way this time

1523.01 -> just by putting the equal sign then VLOOKUP,

1526.22 -> and I'm gonna choose it right through here.

1528.17 -> So, what is my lookup value gonna be?

1530.96 -> Well, the lookup value is gonna be whatever I put into this,

1534.53 -> so this is gonna be the code,

1536.15 -> and I'm just gonna go ahead,

1537.62 -> click on that cell, I9, and comma.

1540.71 -> What's gonna be my table array on this one?

1544.4 -> Well, my table array on this one

1546.92 -> is gonna go from the code up here

1550.04 -> to the salary one here,

1551.93 -> so there's gonna be four different columns in there.

1555.56 -> And then at the next point I'm gonna put my comma,

1558.65 -> what's my column index?

1560.03 -> And now, this is where things have changed.

1562.1 -> So, remember last time I said B was one?

1565.52 -> Now, that the array is starting here, this is gonna be one,

1570.47 -> so C is one, D is two, E is three, F is four.

1575.18 -> So, it's wherever the array is gonna be starting on it,

1579.26 -> so I just wanted to point that out.

1580.49 -> So, what do I need?

1582.08 -> This is gonna be one, two, three, four,

1584.87 -> so I have to put four for my number on it

1588.17 -> because this is not selected in it.

1590.72 -> So, I'm gonna go ahead,

1591.65 -> put my comma and then put my exact match again.

1596.27 -> And that should be all I need,

1598.19 -> I'm just gonna finish off with a bracket,

1600.02 -> and let's try this out.

1601.61 -> So, if I just put in GOTI, GOTI like so,

1606.8 -> and you can see that GOTI here was $58,000 here.

1612.08 -> So, I just wanted to point out

1613.55 -> when I was doing the VLOOKUP function this way

1617.24 -> is making sure that you understood

1618.83 -> when you count your columns, it's wherever the array starts,

1624.71 -> that's gonna be number one

1626.72 -> and then you move over from there.

1629.93 -> And then, if you have to go back and correct anything,

1632.72 -> you can just go back,

1634.19 -> you can either correct it up top here

1636.29 -> or double-click into this cell and it will open up.

1639.92 -> So, if you wanted to change this to true or false,

1642.77 -> so you can move those back and forth

1644.21 -> or change a different range too.

1646.82 -> All right, let's get ready to move on to the next section,

1650.21 -> and that's gonna be creating

1651.5 -> the multiple dependent dropdown list in Microsoft Excel.

1655.01 -> And I'm actually going to use a VLOOKUP function

1658.16 -> in that one too, I'll add that at the end.

1660.68 -> All right, let's go to the next one.

1662.87 -> So, in this section I wanna show you

1664.64 -> how to create a multiple dependent dropdown list

1667.88 -> in Microsoft Excel.

1669.74 -> Now, there's different ways that you can do this,

1672.23 -> I actually have different videos showing different functions

1674.9 -> and formulas to create this.

1676.85 -> I'll put a list of those down below

1678.71 -> and you can check those out

1679.67 -> to see if those work better for you.

1681.41 -> In this one here today that you'll be working through,

1683.96 -> we're gonna be using the unique function

1686.03 -> and the filter function.

1687.95 -> So, what we're gonna do,

1689.36 -> I'm just gonna give you a little demo here to start with.

1691.91 -> And what it means is when we have a dependent dropdown list,

1695.51 -> what I choose in here will influence what I get over here.

1699.47 -> So, if I dropdown here,

1701.96 -> it shows the different departments.

1704.03 -> These different departments are being pulled

1705.89 -> from this column over here.

1708.26 -> So, if I pick finance here,

1710.66 -> then my list will change here,

1713.24 -> so it will change on what I can pick.

1715.34 -> So, there's only accountant in here,

1717.23 -> so I'll show you if I picked a different one.

1719.72 -> If I went to security and then dropdown,

1723.77 -> you'll see that I get head of security or the guard.

1726.8 -> So, if I was picking guard and then dropdown,

1730.58 -> I only have one person that's listed as a guard,

1733.91 -> but if I was going to go back

1735.59 -> and pick maybe let's say marketing,

1738.35 -> and then I was picking marketing assistant

1741.35 -> and then dropdown,

1742.76 -> notice that two people came up for marketing assistant,

1745.61 -> and then I could pick one of them

1747.14 -> and then their salary comes up based on what I picked.

1750.53 -> So, these were all dependent upon what I chose before,

1753.83 -> and that's what I wanna show you

1755.24 -> how to create today in Microsoft Excel.

1758.3 -> So, the first step here is to be able to pull out

1761.6 -> the individual market names here.

1764.21 -> You can see that there's two sales here,

1767.42 -> three sales, there's multiple marketing.

1769.88 -> I don't want it to be written each time,

1771.26 -> I just want sales to be once and finance to be once.

1775.07 -> So, I just want to be able to have

1776.3 -> all the different department names.

1778.22 -> So, what I'm gonna do is use the unique formula

1780.53 -> to begin with, and I'm not gonna put it

1782.24 -> in this spot right here,

1783.86 -> I'm actually gonna just put it to the side here

1786.77 -> and I'm gonna start my formula here.

1789.92 -> I'm gonna be putting all my unique

1792.59 -> and filters down in this row,

1794 -> and then I'm gonna hide it after so you can't see it.

1796.88 -> So, I'm gonna go equals and then I can start typing unique.

1800.21 -> You can see as I start typing, it's closer and closer,

1802.91 -> here's unique, I'm gonna select it.

1805.34 -> All I need to do is select what I need to be searching,

1809.12 -> and it's gonna be this right here.

1811.19 -> So, I'm just using a range here.

1812.93 -> If you were using a table,

1815.09 -> you'd be able to select that column by the columns name too.

1819.14 -> But I'm just using a range,

1820.46 -> so I'm actually gonna just hit Enter,

1822.98 -> and I get my unique different department names.

1828.08 -> Now, this is what I'm gonna be putting

1830.33 -> in department over here,

1831.74 -> and this is where I need to use data validation.

1834.92 -> And where you find this is if you go

1836.96 -> up to the Data tab up top and then looking in the ribbon,

1841.34 -> find your Data Validation.

1842.99 -> So, I'm just gonna click on it

1844.76 -> and what we need to change it to is List,

1848.18 -> so Allow List just like this.

1851.42 -> And what is the list gonna be?

1853.49 -> So, I'm just gonna click on this, it's in this spot here,

1857.18 -> this is where the list is coming from,

1859.07 -> and I'm just gonna hit Enter.

1860.99 -> And I'm gonna show you something,

1862.43 -> I'm gonna make a change to this

1863.51 -> so it works better in a moment, I'm gonna hit OK.

1865.82 -> And watch this what happened,

1867.23 -> so I dropped down, notice it only says sales on this?

1871.1 -> What I could do, I could have selected everything

1872.99 -> so it would've shown everything.

1874.55 -> But what I like to do is,

1876.08 -> and I'm just gonna click on Data Validation again

1878.69 -> and open it back up.

1880.31 -> If you add a hashtag at the end,

1883.1 -> it will always adapt to how many spaces

1886.43 -> is in that list that it's pulling.

1888.23 -> So, if I hit okay now and go back,

1891.65 -> you can see that all of these from here

1895.04 -> are now in my dropdown list here,

1897.89 -> so this is the first step that we're gonna do.

1900.65 -> So, the next step that I want to have happen,

1903.05 -> I want to be able to once I choose sales,

1905.63 -> then I should be able to only look

1907.34 -> within the sales job titles that are there.

1910.46 -> So, it should look down this list

1912.74 -> and there are repeats of different ones,

1914.33 -> you can see there's sales assistant,

1915.98 -> and sales assistant, and sales manager,

1918.26 -> so it should find two different things.

1920.54 -> So, I am gonna be using unique,

1922.31 -> but I also need to filter it

1924.35 -> and I need to connect it to this H2 cell.

1927.95 -> So, let's go ahead and start

1929.36 -> our unique function formula over here.

1932.24 -> And to do this, I'm just gonna put equals

1934.37 -> and then start typing unique, and I can see it pop up,

1937.43 -> but right away I need to add the filter function.

1941.39 -> So, if I start typing filter and I need to add this,

1945.17 -> and what do I need to be looking at?

1946.64 -> The array, so I'm gonna be comparing this

1949.277 -> and I'm just gonna select the whole column here.

1952.19 -> And then I'm gonna put a comma,

1953.87 -> it's gonna be comparing to this one here.

1957.62 -> But when this one equals what?

1960.08 -> Well, this is where I need to put the equals sign

1962.9 -> in of the plus equals, this spot here.

1966.32 -> So, I was gonna look down C

1968.24 -> to see when this connects to here.

1970.79 -> And then that's the end, so that's one bracket,

1973.28 -> we have to add another bracket

1974.66 -> 'cause we have the the two opening ones there too.

1978.05 -> So, I'm just gonna hit Enter,

1980.09 -> and you can see since I have sales

1982.25 -> now it brought back sales manager and a sales assistant.

1985.49 -> If I drop down and go to marketing,

1988.58 -> it brought back these two.

1990.56 -> Now, we're gonna do the same thing what we did before,

1993.29 -> we are gonna put the data validation,

1996.83 -> data validation up here.

1998.3 -> So, I go to this spot, I click on Data Validation,

2002.59 -> and what am I gonna use?

2003.64 -> Well, I'm gonna use a list again,

2006.34 -> and what's my source gonna be?

2008.68 -> So, I can click on this

2011.2 -> and I'm just gonna click in this cell here

2013.93 -> and I'm gonna add the hashtag right away,

2016.6 -> hit Enter and hit OK.

2018.85 -> So, now we should have this,

2021.79 -> marketing manager, marketing assistant,

2023.47 -> it's pulling from here.

2025.06 -> So, if I change this to finance and look what we have,

2029.05 -> there should only be accountant,

2030.55 -> and you can see it reflected over here.

2033.28 -> We'll make one more change

2034.3 -> to make sure it's working, security,

2036.67 -> and now we should have this dropdown

2038.53 -> between head of security and guard.

2041.2 -> Okay, let's move to our next spot.

2044.05 -> So, now we want the names to populate down here

2047.32 -> based on the guard.

2048.67 -> So, like if we have this selected,

2050.29 -> it should just choose the one guard

2052.6 -> that we have, the name list.

2054.34 -> So, how do we do this?

2055.45 -> Well, the names are all unique already,

2058.06 -> so we don't have to use the unique function in this one,

2061.48 -> we'll just use a filter and connect it back to the H2 spot

2066.34 -> or H3 cell that we already have.

2068.8 -> So, I'm gonna go back to my J column

2071.02 -> and I'm gonna put in equals

2072.55 -> and we'll use our filter here.

2075.55 -> And what is it that we're gonna be needing?

2078.16 -> Well, I'm gonna need this column right here.

2081.49 -> And what's gonna be the next one?

2083.98 -> It's gonna be the job title.

2085.84 -> So, it's when the job title is equal to what?

2088.84 -> Well, it's when the job title

2090.49 -> is equal to whatever's in this cell right here.

2094.6 -> So, now I can go ahead and just close that,

2097.18 -> and you can see there's the one name.

2099.04 -> So, let's see what happens up here as we choose

2101.68 -> if we choose marketing

2103.33 -> and we choose, let's say, marketing assistant,

2106.03 -> we should have two names right here.

2108.94 -> So, it's pulled the list though.

2110.26 -> Now, we have to use our data validation

2112.51 -> to put it back into the dropdown list here.

2115.51 -> So, I'm just gonna select this cell,

2118.39 -> make sure you're under Data, go to Data Validation,

2122.98 -> drop down, choose your list.

2126.4 -> Choose your source, it's just gonna be this spot here.

2130 -> I'm gonna add my hashtag,

2131.8 -> hit Enter and hit OK.

2134.26 -> So, now I should get those two here.

2137.26 -> So, we have one more step here that we want to add

2140.085 -> and we wanna be able to connect their salary here.

2143.38 -> And we're gonna do this actually with VLLOOKUP,

2146.65 -> sort of similar to what we did in our last lesson.

2150.01 -> Now, let's use our VLOOKUP function to finish this off

2153.52 -> by getting the salary connected to the name.

2156.04 -> So, our function's gonna go

2157.9 -> in this cell right here in H7,

2160.48 -> and we're gonna start this with our equal

2163.06 -> and we're gonna look up for VL.

2165.37 -> And there it is, I'm gonna select it.

2167.32 -> So, what are we looking up?

2168.94 -> Well, we're gonna look up what name

2171.19 -> is in this cell right here.

2173.38 -> So, I'm gonna select it,

2174.37 -> that's why I look up value, place my comma.

2177.64 -> What's my table array?

2179.23 -> Well, my table array is gonna be going from this part

2183.34 -> up here at the beginning of the names

2184.75 -> down to the end of the salary here.

2188.53 -> Now, our next point is our column index.

2191.65 -> Now, remember, since employee name is the first column,

2195.43 -> this is number one,

2196.36 -> so this is the first column in the array,

2198.61 -> this is one, two, three, four.

2201.64 -> So, we're gonna need to have the column index number B4,

2205.99 -> so I'm gonna put in number 4 like that,

2208.63 -> and I'll put another comma,

2210.31 -> and I want it to be false because I want the exact match.

2214.48 -> And I'll just end this and hit Enter,

2217.09 -> and let's try it out a few times.

2218.92 -> So, if we're dropping down here,

2220.78 -> and if I was looking in sales dropdown,

2224.348 -> maybe I wanna see sales manager and dropdown here

2228.82 -> and I can see Walter White gets $186,556.

2233.83 -> And the thing I'm gonna point out now,

2235.36 -> you can see all these things changing in the column J.

2238.42 -> You can hide this,

2239.65 -> so what you can do if you right-click

2242.26 -> at the top of the column here on J, we do have Hide here.

2246.01 -> So, it didn't delete it, it just hid it there,

2249.16 -> so now people won't see the information coming up there

2253.39 -> and then you have your multiple dependent dropdowns

2256.69 -> working right through here.

2258.19 -> And then you have the VLOOKUP function getting that salary

2262.03 -> from reading this last cell.

2263.98 -> So, like I said before,

2265.39 -> there's many different ways that you can create

2267.94 -> multiple dependent dropdowns in Microsoft Excel.

2271.69 -> I do have other ones, I like the XLOOKUP one too.

2274.99 -> You don't have anything to hide in cells,

2277.48 -> so take a look at that one and I have some other ones too.

2280.39 -> I'll put the links to those, as I mentioned,

2282.64 -> down below and in the cards.

2285.88 -> This brings us to the end

2287.08 -> of this Intermediate Microsoft Excel Tutorial.

2290.11 -> Make sure you keep posted

2291.22 -> for when I have the next ones coming out,

2293.23 -> and check all the links down below in the description

2295.57 -> to all those other tutorials that I talked about.

2298.39 -> Thanks for watching this time on Teacher's Tech.

2300.22 -> I'll see you next time with more tech tips and tutorials.

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