Intermediate Excel Skills, Tips, and Tricks Tutorial
Intermediate Excel Skills, Tips, and Tricks Tutorial
Here’s my entire playlist of Excel tutorials: http://bit.ly/tech4excel
Learn many of the intermediate skills, tips, and tricks that you’ll need in order to be able to use Excel effectively. This is the second of several video tutorials on Excel. Please consider watching my other video: The Beginner’s Guide to Excel. In this intermediate video you’ll learn some advanced formatting tools such as conditional formatting, how to save time by using the Autofill Handle, the basics of formulas and functions, sorting and filtering, and a few random useful tips such as Freeze Panes. Find my whole Excel playlist here: http://bit.ly/tech4excel
Consider supporting Technology for Teachers and Students on Patreon: https://www.patreon.com/technologyfor… Here’s the practice file that goes with the video: http://bit.ly/intermexcel
Click below for more information about some of the movies mentioned in this tutorial:
Dirty Rotten Scoundrels: http://amzn.to/2xTpck1
The Princess Bride: http://amzn.to/2yk3tDc
Ladyhawke: http://amzn.to/2xOhLsA
Stardust: http://amzn.to/2yjJvs9
Young Sherlock Holmes: http://amzn.to/2xaJJNv
Mystery Men: http://amzn.to/2yRmQ3r
Content
0 -> In this video tutorial we're going to take a look at the intermediate
3.759 -> Skills tips and tricks that you need to know in order to use excel
8.019 -> Properly and efficiently if you haven't already watched my other excel video. It's called the Beginner's guide to
14.679 -> Excel and I highly recommend that you start with that video before watching this one
18.85 -> so one of the last things that I showed in [that] beginners guide video was how to do some formatting how to
25.15 -> Change let's say the column widths how to do italics and things like that well in this video
31.17 -> We're going to start off [by] looking at a couple of advanced formatting options, then I'll show you a data entry
37.51 -> Shortcut that's fantastic called the autofill handle then we'll take a look at how to do formulas and functions in excel
44.46 -> And then we'll finish with a few more intermediate and advanced tips and tricks so here. I am in the spreadsheet
50.46 -> I started in the beginners tutorial
52.3 -> It's a list of movies that I own and I'm putting in [some] data about those movies well
58.59 -> I want to show you a couple of shortcuts as you're trying to format the data
62.19 -> That's in a spreadsheet
63.76 -> These tricks will save you a lot [of] time the first is called the format
67.57 -> Painter and you can find it here on the home tab home Ribbon in the clipboard group
72.51 -> And it looks like a paintbrush the format painter is what it is and the way
76.74 -> It works is let's say that part of your spreadsheet has a format that you wish other parts of your spreadsheet
82.75 -> Had all you have to do is click on the good example that [you] would like click [here] on the format painter and that
89.35 -> Copied it. It didn't copy the content rather it copied the format of this cell
94.86 -> So now all I need to do is move [my] mouse to [the] place where I want to copy that
100.72 -> Format to and I can click and if I release the mouse click it will copy the format
105.99 -> But Let's say you would like the entire range this range
109.06 -> Of 5 or 6 cells to have that layout you can just click and drag
113.77 -> Holding the click and then release and you can see now they've all been painted with that format in this case
120.119 -> That's not at all what I want so I'm going to undo it with this arrow up here in the upper left and it takes
124.68 -> Me [back] now another more advanced formatting option that is pretty exciting at least to me is what they call
131.5 -> Conditional formatting so it's up here at the top on the home tab home Ribbon here it is in the styles group
138.76 -> Conditional formatting and the way this works is you [select] a column or a row [either] way?
144.51 -> I'm going to select the column d. So I just click [on] it and the entire Column is selected [now]
149.879 -> [I] simply go up here to conditional formatting and click and
153.43 -> It gives me some options it mentions Rules several times basically
157.89 -> we're going to set up some rules for the content of these cells and
161.53 -> The formatting of these cells and the easiest example I can think of is color scales, so let's just look at that
167.819 -> But I want you to explore this in more detail if you're interested in conditional formatting because it's really an exciting tool
173.76 -> So I'm going to go into color scales
175.47 -> and I want the color of
177.73 -> Each cell to change to adjust depending on how valuable the movie is now to do this I would select
185.59 -> Probably this color scale here
187.829 -> It looks like it has green at the top and red at the bottom
190.9 -> So the idea the more green it [is] the more valuable it is the redder it is the more worthless
195.959 -> It is if you want [to] reverse that you can and there are all of these [other] options as well with different color schemes
201.9 -> But I'm going to go with that first one
203.28 -> I'll just click on it and what this does is it evaluates the entire column it looks for the highest numbers and
210.73 -> Gives them the greenest background and then the lowest numbers get the darkest red
216.19 -> Background and then everything else in between is adjusted accordingly so that easily I can just glance at this spreadsheet
222.239 -> And I can see where the most valuable movies are if you like that please investigate conditional formatting more
229.44 -> There's great ways to display the data in a conditional way you can use Data bars
233.849 -> You can say if the highlighted cell is greater than 10 then make the background
240.069 -> Green or put the text in bold if it's less than 5 then format it with a background of red
247.139 -> There's all of these if then statements that you can use with these highlight cells rules. There's top/bottom rules top 10%
254.62 -> bottom 10% icon sets
256.65 -> We can show arrows going up down left or right and just some really exciting
262.3 -> Formatting options that you [have] here okay before we leave formatting and move on to a few other things
267.99 -> I want to show you that because these numbers represent currency
272.27 -> I probably should format them a little differently so I can click here on D and everything in Column D
277.94 -> That's a number will get formatted differently right now everything in here is formatted in a general way
284.22 -> Which is to say that there's nothing special about the way
286.7 -> They're formatted at least the numbers themselves however if I go up here
290.54 -> I can change that from general to any one of these other things
293.6 -> And there's even more beyond that there's also some shortcuts like this. I just want to click on the dollar sign now
299.24 -> they all show up as
301.05 -> Accounting currency if you don't need it or want it to be accounting you can just go down and do regular
306.12 -> Currency all that changes is where the dollar sign is located
309.54 -> Okay, so this is awesome this spreadsheet is turning out great so far
314.01 -> Next let's look at formulas. This is where the true power of excel is found
318.53 -> I believe so far in our spreadsheet [all] we've done is enter text or numbers and then also in some cases
325.37 -> We've applied some formatting but other than that we haven't put anything special into the spreadsheet
332.04 -> well
332.55 -> Let's change that what I'd like to do next is put in some
336.54 -> Formulas that will help me know some things like what's the total value of my DVD collection?
342.06 -> What is the average value of the DVDs that [I] own and what's the most expensive?
347.91 -> Value and the least expensive stuff like that all right, so let's figure that out down here at the bottom [of] the spreadsheet
354.38 -> [I'm] going to create a formula now. It doesn't have to be here
357.74 -> I could put the formula anywhere on this spreadsheet or another spreadsheet, okay, wherever I want to put this formula
365.24 -> I can do it, but I'm going to do it here so I'll just click and whenever I do a formula in excel
369.99 -> [I] click on a cell and then I think to myself the phrase this cell so [I] click this cell
376.53 -> Equals and I want to find out the total amount that my collection is worth
381.33 -> So I need to somehow add up all of those numbers
384.12 -> [let] me show you the hard way first the hard way would be to say okay. We have a [five] so I'll type in five
391.02 -> Plus two plus five and I can just keep doing this plus six plus nine
397.7 -> I can just keep doing that basically creating a formula
400.97 -> And then all I have to do is tap return or enter and look it adds up those numbers the thing is that's way
407.75 -> Too tedious, so instead of doing that what I can do is type in the word sum
412.92 -> so this cell is equal to the sum of
416.22 -> And then you put a left
417.68 -> parenthesis and now I just need to describe the
421.59 -> Cell or in this case the range that I want it to add up that
426.08 -> I want it to sum fortunately as I showed in [the] beginners video
429.23 -> I can name a range every range in excel has a name and the name of this range is
435.93 -> D3
437.22 -> Through D 22 so I'll just type D3 and if you remember from the beginners video the word through is
445.2 -> Denoted with a colon and then D
448.14 -> 22 and then you're supposed to put another parenthesis the end parenthesis now
453.08 -> I'm going to not do that just to show you that even though you're supposed to you. Don't have to you now hit enter and
459.84 -> Look what it's done. It's added up all of those numbers [the] grand total worth of my DVD collection is
466.53 -> 135 Dollars
467.85 -> [now] if you want to make sure that it worked you can double click [on] it and look what it did it
472.38 -> Highlighted all of the numbers that it got that it included in the formula
477.27 -> Now let me show you an alternate way the way. I [just] showed
480.05 -> You is one of the nicest quickest ways to do it
482.42 -> Especially if you have a giant spreadsheet, but there are a couple of other ways in addition to typing in the range
488.99 -> I could just move my mouse up to the top
492 -> click and drag with my mouse on the range that I would like it to add up and
497.04 -> Then just tap return and you get the same results now
500.84 -> There's also yet another even faster and easier way to do it
504.74 -> And I'm going to delete my formula in order to show you this properly what I would do is
510.02 -> I would just click on the cell but instead of typing equals
512.54 -> I can just go [up] here on the home Ribbon and go to the editing group and here
518.39 -> We have something called auto sum, it's this symbol here, and you can just click [on] it
523.28 -> And it will automatically look immediately above and it will add up
527.97 -> Everything in those cells so I can hit enter and I the same results
533.04 -> So that's the fastest. I will say that sometimes it gets it wrong sometimes it gets the wrong data
539.36 -> So just watch out for it
540.889 -> I usually use autosum
542.819 -> But just be a little bit skeptical
545.37 -> Sometimes it might include data that you don't want included okay speaking of including data that you don't want
550.62 -> Notice what it did to my conditional formatting now
553.559 -> This is the only green value and everything else is small compared to it, right
558.569 -> So I need to fix that with my conditional formatting I'll go in and highlight that entire Column and I'm going to clear the rules
566.55 -> Okay, so it gets it completely out
568.399 -> and I just
569.699 -> want to [do] conditional formatting with
572.97 -> Just those numbers not the total so now I go in conditional formatting color scales and look that's back to being useful
580.29 -> Okay, now I should go in and type total and maybe put these in bold just to show that this number is different
586.519 -> It's special it's different than those other numbers, okay next
590.18 -> I'd like to do an average formula, and it's similar [I] would just say to myself this cell is equal to the average
598.5 -> Left Parenthesis, and then I can click and drag or I can type in the range or notice that there is an auto
606.66 -> Average which again, you should use it. It's great, but just be a little skeptical of the results
612.589 -> I'm going to double click between those cells to make it so you can see the word average
616.319 -> Next I want to do one of what's the highest value and the lowest [value]
621.959 -> For these the formula is a little
624.36 -> Different for highest the formula is this this cell is equal to the [max] or just max?
630.93 -> Left parenthesis click and drag on the range or type in the range
636 -> It's up to you hit return so what it did is it?
640.17 -> Looked through that range [found] the maximum number the highest number and printed it here
646.41 -> Next I'll do lowest for lowest
649.069 -> We do this cell is equal to min left
653.069 -> parenthesis and the range I'll type it in this time D3 through D [22] d 3
659.49 -> through D 22 hit enter
662.029 -> And there's the lowest so you can see these formula are pretty useful
666.61 -> You can get some really good numbers out of them
668.67 -> So those are probably the four most common ones that I use some average Max and min
674.249 -> But just know that there are many many more
676.449 -> functions that you [can] use if you want to see pretty much an exhaustive list of all [of] the
681.22 -> Functions that you [can] use just type in equals, and then look up here. This is the formula bar is what we have here and
689.649 -> It's an alternative to typing your formula right here [in] the cell
693.779 -> You [could] just go up to the formula bar and start typing and notice as you type you get some suggestions
700.089 -> So it's recommending because I typed in [Avril] it's recommending average average a average if there's four different average
708.22 -> Functions that I could draw from okay, so it will give suggestions
711.72 -> That's one thing to know about but also over here on the left. Look what it does. There's a click drop-down
718.089 -> Where you get some pretty good suggestions about the functions that you [might] want there's also a formulas tab
724.449 -> Where you can go and a whole function library?
727.869 -> Where you can get math, and trig formulas lookup and reference formulas [texts] logical these are great recently used
736.54 -> financial formulas and if you really don't know which one you want to do just go here to
742.569 -> Insert function, and you can get it on most of these dropdowns
745.6 -> And then just type a brief description of what you want to do. Let's say. I want to count the number of
752.679 -> DVDs that I have so I'll do a search for count and it brings up some
756.67 -> Options that I have and it tells me what exactly it's going to do so use these tools that you have
763.119 -> Use the formula bar use the formulas tab and if you need to use the insert function button
769.809 -> That gives you some additional options, so you can also click here insert function
774.819 -> So have fun playing around with formulas and functions. So they're very powerful
779.47 -> Next we're going to take a look at a few intermediate and maybe some advanced tips and tricks that you should know [about]
785.439 -> One that is pretty well known is called the autofill handle
789.04 -> And this is where you'll find it when you click on a cell look in the lower, right corner
793.269 -> There's a green square and if you put your mouse on that square it will become a plus sign now
799.699 -> It's already a plus sign
800.72 -> But it becomes a black
801.87 -> Plus sign once you see that you know that you've got the autofill handle
805.439 -> And what you can do with this is you [can] click and drag to copy. What's in the cell?
810.23 -> So if I want to copy 2015, I just click and drag it's 2015 again 2015 again
816.17 -> and I can just keep doing that I can even just click and drag and get a whole column of
821.04 -> 2015 so [that's] the autofill handle I'm going to undo that
825.569 -> now that also works with text not just
828.36 -> With numbers so I can copy what's in a cell by using the autofill handle and clicking and dragging now
835.49 -> What's really cool is you can copy a pattern so right here?
839.87 -> I just clicked and dragged to highlight 2016 2015 now
843.769 -> If I use the autofill handle look what it does it figures out the pattern the pattern is minus [one]
850.019 -> subtract one
850.79 -> And it just repeated that pattern so if you will show the autofill handle
855.99 -> The Pattern that you would like for it to copy it will copy that pattern you can also
861.3 -> Use the autofill handle to copy a formula now in this case the formula doesn't work too. Well, this is the sum formula
868.519 -> It's trying to add up all of these numbers when I use the autofill handle dragged it over look what it did now
873.529 -> It's trying to sum up this column and so because those aren't [numbers]. It's not working real well, but let me do it this direction
880.86 -> You can see it is now adding up the years it thinks [that] it's currency, but that's okay
886.68 -> It's adding up all of these years and coming up with a total so I just love the autofill handle
891.87 -> It's especially good for anything related to time so for example dates. Let's say
899.12 -> I want to put in each of the months of the year
901.74 -> With dates and times you don't even have to establish a pattern you can just type in the [first]
907.23 -> Thing that's related to date or time
909.379 -> And then just go to the corner click and drag and look it automatically knows what comes next same with days of the week
916.649 -> same with full dates
918.75 -> and
920.519 -> It's also the same with times
923.88 -> So this autofill handle is just a lifesaver [a] time-saver for sure so I'm going to select all of that
931.079 -> Just by clicking on the column letters across the top clicking and holding and then I'll right-click and choose clear
937.089 -> Contents, and that clears it out, but I wanted you to see that autofill handle
941.56 -> Okay, next up just three fast
944.86 -> Rapid-fire tips that I think everyone needs to know if you're using excel [more] than just at a beginning level [and] the first one is
952.029 -> How to sort so I put my movies in just in random order, but I would like to sort them alphabetically
957.819 -> So what I'll do is
958.74 -> I'll click on the first cell of the first record so star wars I clicked on that cell
964.42 -> Now either on the home tab or on the data Tab either one
969.79 -> You should be able to find sort and filter
973.149 -> I'm going to go to the data tab here's sort and I would like it to sort
977.47 -> Alphabetically a to z so I click on it and look what it did now all of the movies are alphabetized
984.1 -> From a to z now the first time you do this you're going to be worried that you'll lose the integrity of your spreadsheet
990.88 -> [that] [maybe] the dates won't match up now, but if you notice they do
995.529 -> Okay, go back rewind the video if you want, but this used to say 2007 now
999.36 -> It says 2003 so sorting is a really great feature in excel. What about filtering?
1005.48 -> What is filtering well filtering is a way to eliminate parts of your spreadsheet?
1010.259 -> Temporarily so that you can see the data you want to see to turn on filtering what I like to do is click and drag
1016.079 -> Across the Column headings that I've created
1018.779 -> so location Genre value ETc once I've highlighted all of them I go up to the top and I click filter and
1026.069 -> Notice they all now have a little [drop-down] arrow
1029.089 -> And so what I'm going to use this to do is I want to eliminate all of [the] PG-13 movies
1034.4 -> Let's say we're going to have a birthday party for a small child. I don't want to show a movie
1038.13 -> That would be too scary, I would go into ratings and click and right now
1043.309 -> It's showing everything okay, so I want to deselect all and go down, and I just want to choose
1050.1 -> Pg. That's the only one. I want selected now if I had g movies I could select those too
1054.62 -> But I'll just stick with that click ok and you can see it looks like it got rid of those additional records
1060.88 -> It really didn't get rid of them. You can see here on the left
1063.51 -> It jumps from [one] two jumps to five and in excel
1067.77 -> You just cannot eliminate row numbers or column headings. They always exist. They can be hidden, and that's what's happened here
1074.16 -> Is they're hidden but they still exist [all] right? So isn't that pretty useful
1078.6 -> I love filtering even more really than sorting now to get the data all showing again
1083.88 -> I can just click here on what looks like a funnel. Okay? It used to be the drop-down arrow now. It's a funnel
1089.01 -> I just click on it and
1090.7 -> Go to select all click [ok] and that takes the filter off
1094.62 -> I could do the same kind of filter for Genre
1096.99 -> And I could say I only want to see comedy or children's movies things like that
1101.47 -> So filters are very powerful now the last more advanced tip that I want to share with you is called Freeze Panes
1109.03 -> This spreadsheet really is not that big let's say I go buy a hundred more DVDS
1114.22 -> [then] it's really going to be a bigger spreadsheet
1116.61 -> and it's going to be harder to
1118.03 -> Put in my data and even to look at it as your spreadsheet gets more vertical
1122.429 -> You can kind of lose track of what your column titles are
1126.04 -> So that's where freeze panes comes in what you do with Freeze panes is you click?
1131.23 -> Underneath the row that you would like to freeze so I'm going to click on three because that's underneath my column titles
1138.58 -> then I'm going to go to the view tab the view Ribbon and notice there's an option in the window group called Freeze Panes and
1145.36 -> Watch what happens when I click freeze panes, [okay]?
1148.44 -> It first pops up with three options, and I'm just going to click freeze panes
1152.74 -> [so] now as I browse down the page as I enter more records
1156.57 -> [look] what happens those first two Rows stay visible to me
1160.65 -> and that's because I froze everything above [Row] 3 so that's where I'm going to stop in Reality a
1167.89 -> Comprehensive excel tutorial would be 15 20 25 [hours] long and this is already too long of a video
1174.88 -> But I really believe that if you watch my beginners guide to excel video and this intermediate
1182.08 -> Video that you will have what you need to get started using excel effectively as you continue using and learning excel
1189.51 -> I'd recommend that you this resource here at the top. Tell me what you want to do
1193.23 -> you can [just] type in something that you're trying to do and it will give you some help and
1198.97 -> Some Guidance on how to do it
1200.74 -> Thanks for [watching] [this] tutorial and please consider subscribing to my Youtube channel for more videos
1206.17 -> [about] technology for teachers and students and please consider following me on social Media platforms such as twitter and Facebook
1214 -> So that we can continue learning together
Source: https://www.youtube.com/watch?v=lxq_46nY43g