Lessons

Lesson Info

3:30 pm - Advanced Auto Fills

I wantto go this last part that we're going to be working with is going to be working with auto fill on a whole other level. We're going to be working with different types of auto fills that we can integrate, that you can work with months, you can work, work with days of the week, and you can also work with other things, and then if we have, we might do this tomorrow. We might do this today where you can actually create your own a list that you got auto fill. So we're going to start up with just a blank new document and work with this one so let's, just teo, commander control and and we have a blanket. A document is just gonna be a big playground for us at this point. So let me show you the first auto film, so we did a lot of different types of auto fills, and this is gonna be very different what we've already done. So this is known as a list. This is known as an auto fill list. So what does that mean? These air pre designed less that excel gives us that are either going to be the mont...

hs of the years, the days of the week, or it could also be months of the years, days the week abbreviated. So we need to tell ex l a little bit about what we want to do, so let's just say, for example, I'm just doing some type of scheduling of some kind, so I'm just going to type out inside of a one the word january and this case, the spelling definitely definitely matters because it's part of the whole list and I know that I'm going to go from january all the way to december all the way across. So watch what happens now with his auto fill because there's already built into the program, I'm just gonna go ahead and click and drag straight across and there's a little preview that pops out bam just like that, I just saved all that time, typing all that stuff in with typos and all that stuff. Okay? And why I'm going showing a little bit is how to create your own list on top of that because they give you your own stuff, they give you months, days and both of them also abbreviated now also understand I could also go down. I can see january all the way going down the column as well is going across the road, so click and drag and no, she had a little preview fantastic done huge, huge time saver and also note that you can start in the middle of a sequence also because we've been starting right now with january in both of these examples, but I can start in july maybe your fiscal year or whatever it is going to be starting in a different month so I can start right here and understands it is part of the sequence, so I was going to go ahead and click and drag on this, and then I come over in july to june and just like that super easy, huge huge time saver. Okay, let's, go ahead and just do a control or command a what is that again? What were you going? Okay, well, it's selects everything, right? We're gonna go and select everything and I'm just gonna go ahead and delete everything. And this time we're in the days of the week and we'll do the same thing just go down monday through sunday same exact thing it's all built right in tremendous, just great great time saver and we'll go ahead and do it going across we'll start from thursday going across and just like that, you got okay every guy on that what's, another use for auto fill let's now do abbreviations this time let's say money m o n and let's do that going down see that it's aware that that's also something you might be doing and what's to jan or we could even do feb, right? It could be any part of that sequence that and then bam it just does it right there. So this is known as a list this is known as an auto fill less so it will be doing in a little bit is actually creating our own lists and we'll be able to execute them over and over again. So why would we want to do that? I just really think about the fact that you know, you have a number of different clients that's your console or whatever projects or products or whatever it is you want to do and you constantly find yourself having to type all those things over and over and over again you have that list built in really, really help you. So when I was creating a lot of these files for this class, I've created a whole bunch of different auto list auto fill lists so I wouldn't have to type them out all over again trying to find them and you copying and pasting I had them already inside of my auto fill lists, so before we get to that let's just do a quick little exercise I'm going to do let's just actually get rid of these and I want to show you um see if you can figure this out let's say I want to do monday through wednesday, monday through friday but I want to be able to do it over and over and over again okay so you're going to see her I can see you monday and then I go over here to friday and if I was okay and I'm just going to continue on with that hopefully it'll do that let's see if I highlight this and continue going notice what it does here it continues on with that same sequence okay but really all I wanted just monday through friday all the way across okay, so I'm showing you this because I want you to understand how auto fill works because we're going to do with some other examples in a little bit so I want you to see how it works and how it doesn't work understand the culture of it the way I like to think about this is like your kind of siphoning a hose for excel it's like you're kind of like you're leading it you know? You're making sure like it comes out enough without it understands what you want to do this you're saving the host to bring the pressure up so I had enough stuff here which is great but it didn't know enough of what I wanted to do for me to get what I wanted so I've to siphon the hose a little bit further now so I'm just gonna go ahead and say monday through friday again so I'm saving the hose so it's like it's coming out a little bit more like I'm really pulling it to give excel the hint that it needs so I've shown it twice so now when I do monday through friday and then money through friday again I'm essentially gonna be copying the cell see if that works I do that I come all the way down to friday and you see now I've got money did it there and then teo right goes all the way and then start over monday again et cetera okay and if I had spaces in there it would also take in the spaces that's probably more likely what you're gonna do after each week men of spaces was going essentially this copy what you have there so when you're working with numbers and dates and things like that because we're going to do that with auto fill this this whole siphoning of the host thing is going to be really helpful to make sure that you're not getting unwanted stuff like we're getting on this saturday and sunday and things like that so it's important to be ableto kind of understand kind of the culture of auto fill at the same time, okay? And we'll come back to that in just a little bit so what I'd like to do right now and we've been spending a lot of time on the pc for this exercise let's come over to the max side and I want to create an auto fill list all right, so we're gonna do it on the mac side first then we'll come back to the pc side so again, there could be a list of clients project's products you're working with or people staff that you're working with could be anything so we want to be able to have a list of these things that we have easy access to so I have this is just a blank document and where I'm going to go to do this it's inside my preferences and if you're on the pc wait a minute cause it's not the same way so we're gonna go to our excel and then preferences and you see here I have all my options here we explored a couple of these already that seems like so long ago doesn't it when we're working with saving compatibility and all those right now we're gonna go to this custom lists click on that now you going to see that actually already have four lists already in here you see that excel his gift of these to us we already have is that's what these works so well. So now if I click inside this part this is where I'm going to create my list all right? So this could be any number of different things here, okay? So let's just come up with some some rock bands all right, so what? Just do some that's to led zeppelin that's to metallica, okay? Through the doors, okay? And then I'm just spitballing here boston pink floyd. Okay, so just putting that out there, ok? Whatever it's going to be these your is your new clients, we have that there, and then all I'm doing is typing them in and then hitting, hitting, return or enter after each one that's it, and then eventually these going to be added to this esteemed list over here with all these other guys, very simply, I just click add and then there they are just like that. So now, just like I did with my with my months and my days of the week, I could go ahead and just type out any one of these and it's going to go ahead and allow me to do a man auto fill list for it so let's, go and click okay, I'm gonna come over here and I was going to type out in any order, right? And we'll talk about pink floyd doesn't have to be the first one, the last one and it's going to know that this is part of a list that I've created, so now I use and I do this and see there they are just like that, like magic so so much of this is about really customizing excel to work for you and that's what's so great about this is a huge time saver and you're basically configuring these things from the ground up to be able to say hey, this is what I'm going to use this all the time this is a list I want to use the banya can have as many of these lists is you want really really, really helpful okay, we'll do that one more time alright just if you want to follow along with me so again we're gonna go to excel preferences custom lists and we do a list of anything let's do list of dogs okay dalmatian terrier german shepherd kali and australian shepherd okay, so you just have whatever you wanted to be there all right that bird okay, good. So we have that there fantastic. You had that in their super duper now also understand that in this section if the list already exists you can take it from excel so we'll do this one well does adieu addison here already we got our dog is in there and then we're going to say okay and that's great but then when I come back tio let's go to these guys so let's say I won at a list of these guys already having there save me a lot of time don't have to type it in the list already exists because you've already done all the hard work let's do it now so we're gonna dio is again good or preferences go to custom lists and then all I do now is simply click on this icon and highlight what I want and then come back up to this icon to make it expand again and see it's put it in there as like these cells is basically sucked up the data that's inside of these cells I click on import and then bam it just put him in there for me copy and pacing does not work by the way if you try to do copy and paste on there it seemed like the logical thing to do don't work okay? And then there we go and we have that we've imported them that's fantastic. I click okay and I could do any one of these at any time. All right, so what I say cali I think should spell correctly and I could just go ahead and just click and drag that down cute little dog showing up on my spreadsheet. There we go. And then, um let's teo bob barker click and drag and there they go pretty cool, huh? Yeah, really one of my favorite things I mean it's just it's just amazing, like you just configure that however you want to I don't know the audience picked that up there at home but in the studio we heard a very whispered how I'm sure they're going home with you so let's so you do this on the on the pc side it's a little bit more hidden but very similar in terms of the process um we have to go into the settings into the options excuse me so how do we get to the options we go to file and then options and you remember how we were here a long, long time ago and it's about as deep as it could possibly be so we got two options we gotto advanced and we come down as faras we can possibly go under the advanced category I'm going to see it says edit custom les create list for use in sort send phil sequences so I do that and then again the same exact thing pops up here so I have all the day's abbreviated and full and all the months abbreviated in full I can go ahead and just put in whatever I want right in here red, green, blue, orange and yellow all right and you just say ad and then bam that they are same exact thing no difference the only difference is how you get to it and you also see here's the import if I wanna be able to take it from what I've already typed out prior to this okay and at the same exact process then so I'm just gonna go and click okay click okay again and then I type out red and then when I do the auto phil green blue orange yellow okay, so that's really, really great use for auto film you'll be using that a lot you know you'll just finally after three times a typing in the same thing you'll say what am I doing? I'm just going to make an auto fill of this you have easy access to and then you just use your auto fill handle to bring it right down okay so yes, I still cannot get over that it is fn delete that delete express it I thought my delete button on my mark woah and yeah, I'm glad you brought that up because that happens to a lot of people know what really working but during the fn delete I know it's just like these little society it's a magic all magic, isn't it? Um ok, good. So let's continue on with a little bit of more auto fill so we can show you just kind of how other ways that you can use this so we're going to do some basic basic number auto film and a lot of this have to do with siphoning our hose again so this could be like a list of names and I want to be ableto I have like, numbers coming down so what number they're going to be on there so I type in the number one right now and I want this to be a sequence of like one through whatever twenty five I won't be able to necessarily get what I want. So watch what happens. What do you think's gonna happen if I click and drag on the auto fill right now? That's what I want to have happen what's gonna happen to be the same number because basically what auto fill does by default if it's not a list or a sequence of numbers is going to just it's just going to duplicate is what it does remember with california with c a it has brought in all the cia's is the same thing so I'm just gonna go and click and drag on this to show you whole lot of one's that's not what we want so again it's about siphoning the hose we have to tell excel what we want we have to give it a little bit of a hint so I'm going to say one two, okay, but that's not the end of it because if I just select two right now what am I gonna get? I'm gonna get a whole bunch of twos just gonna repeat that, so I have to give excel little bit of a hint of what I want okay, so basically I'm going to do I'm gonna highlight one and two because I wanted to base to know that both of these are part of the sequence that I'm trying to create, okay, so both of these the one into so it knows oh, this is going to be a sequence of one number separating each other, so I do this now and then like magic, it does it for me, so I don't have to do that. How about a few were to do, like multiples? Ok, like what's an example of that, like five, ten or some like that? Yeah, you know, different kinds of one this could be like two for or there's other ones knew. Yeah, I mean, it will. Whatever sequence you give it, it will take that sequence. So I just say, like, I mean it's just gonna be a little bit crazy, but I could say to and then six and then nine, you know, maybe that was your sequence of things and it's going to continue doing every other one is going to be banned bam bam! Just like that. So I don't know exactly what it's going to do because excels way smarter than I am, see that and it's actually added up all the decimals and because it's not a perfect hole number between everything but this is a good lesson to understand how to work with decimals though so I can actually say decreased decimal so it um where'd you go okay, you can do that kind of thing if you want to but you probably more likely going to do like a five ten, fifteen something like that but did five ten actually let me get rid of these symbols decreased decimal okay, now I have my five ten and again I want to be able to highlight both of these I want people to say, hey, this is going to be the sequence five ten and then this way really easily I could just do that and then automatically knows what to do. Can you do a custom in there too? Sure, absolutely right if you want him, you just type it in just like that. I would recognize that that's a good question actually because I don't know if it will get confused if you just do it and I'm not sure if it'll do that because it might get a little bit I've never done it with numbers before because it might get confused with the number sequence is natural to numbers already, so I might not do that probably easier just to do it like this rather than having a custom list number one because it might get confused by number two numbers built in automatically have a list type of thing kind of sequence already sort of like structured within it um the other thing that's works for his dates we're already ten days but I'm gonna go ahead and preform at this to be a long dates remember how I could do that going this way I can go to the drop down I'm gonna say long date and of course you can always do right click you could do format cells you could go to number date and then the long date is the one with the days that's really nice you going to see how cool this is because not just doing the days but excel is aware of what date is on that date okay, so I was actually gonna change everything exactly how it needs to be so I'm gonna look okay and then I'm just gonna go ahead and just do one one and then fourteen and that comes in just like that alright that's fantastic and I'm going to say one fifteen fourteen alright that's also wednesday right? You can see that they're so it's going to continue on for me just like that so I have to give it a sequence starters I'm going to say ok every two weeks this is goingto lovable alright I just do that and you see it's gonna automatically do all that stuff for me now like wise I could do the same thing or I'm just going teo just to today's dates and then what? You don't have to do me preform at this to just do a short date it could do that and then if I do, um just a couple of days from now twelve nineteen tony that okay, I have that so then it's going to basically do every other day it's going no just to do that okay it's gonna automatically just fill it in every other day every other day every other day so if you want something different, you've got to give it a little of a hint or to make it so it does do that okay? So you're gonna want to make sure that you know what you're trying to get at in order to do that look with the monday front monday wednesday monday through friday type of thing you're gonna want to be ableto kind of plan that out accordingly? Yes time ranges like one two one fifteen one fifteen to one and haven't know that it's a time range um you should be able to do that I'm just trying to think how you would do that. Um you may have to go a little bit deeper into it working with the number for matt of of a custom because the custom actually does have some time built into it or we have hours, minutes and seconds you have this but I'm not sure how you would actually going to see if I do that yes this is basically saying so here I done this you can actually change it within this okay so I just changed the formatting within the number and then here comes on automatically violent specified comes in his twelve a m that's the beginning of the day and I could easily change this to be something else um and then that's going to be there and then you can continue changing them as you need to and then do your thing so I changed this to what is this p m and then change this to one him and see what this does all right so I'm giving this a sequence and it's doing it in military time here so I know exactly what it iss because it's pm verses ain't I am I do that let's see what that does okay anders as it up there for me but I probably can't do like a sash with like twelve forty five till one pans and oh you mean like the range like minute increments okay, so how did you do it? Did you know I just put in one fifteen, one thirty, huh and one forty five in the night no dash colin so you did it after that so social understanding your question so you did eleven fifteen up here because your question is basically doing a rain yeah, you can actually indicate the actual range because like in that way you would assume that yeah, you know it's one fifteen to one thirty or whatever, but could you actually write out one fifteen to one thirty and haven't no one thirty to one forty five six of the next one there might be a way to do it I'm not aware of how to do that so you could do it this way but then in terms of doing within the cell itself I'm not sure you you probably have to kind of take it and make it so it's not time you kind of do it that way where it's just showing is as numbers but it's so recognizing his numbers potentially yes, but not necessarily because the time just shows up like like this with a with the m and the date itself. So maybe what you could do is you can actually do to different columns and do it that way so it just appears that way and then and then still it just appears as if they're the same and then it'll just it'll accomplish the same thing it might be something that you could do you had a question coming from cathy in utah about if you could make a list that will give a calendar date for say, for example, every monday in the year or every you know so you would set a parameter like that? Uh the question sorry. So could you give a calendar date on the list for every set it so you would know have a date for every in my day her example would automatically calculate the next monday seven days the next monday's another seven days the next monday's another seven days oh, I see I see. Well, that would be a formula, right? So you'd actually do a formula to show that there's going to be formulas built around that andi could possibly get into that tomorrow we're going to see how we actually have a date and time formulas you can see days and hours and all seven this might be sort of related what you're talking about there's a month all that stuff so you can actually go into some detail on all these things and you can calculate how many how many days are apart from each other and that kind of thing or how many days you been working someplace yeah yeah maybe that's what she's asking about is that potentially like how many? How many do when they're going to be the day of that? Well, yes, if you put in the first monday yes on you put on that and then it automatically knows what date that is that's the nineteenth december okay they will automatically calculate the next monday's the twenty seventh. The next monday is the fourth of the next month automatically calculated you come up with the formula. Is there a way to is there a form you could right to do that? I'm not still totally understanding the question, so are trying to figure out a scenario when you would use that. So you want to know what day is the next? You just want to put the data in once, basically. Yeah. So you say you have something that occurs on every single monday? Yeah, so so you you anyone all right? Monday, january first once. Okay, you don't have to go and put the next one is january eighth. The next one is january fifteenth. I don't have to type beach that. Once there was a reform at that formula, you could write that well, then automatically calculate after you've just put in january one plus the formula it off, auto fill the next one. See eight the next fifteen the next one. How is that different from what I did here? Just out of curiosity, just some totally understanding. A little better because we had, like this first example is just you know, january what whatever it's going to be this is that this is an easy one because it's like one week apart from each other or two weeks from each other so would it be any different than that if if you really knew the first two and then you set the sequence for it? Well, sadly is not cathy's not giving way trying to guess here we'll question might be cathy they're still out there you want has a more detail, but I think that's the gist of it she wants to be able to have auto fill fel the dates so she doesn't have to keep writing we're only talking about mondays we're not talking about monday, tuesday, wednesday, thursday forty huh it's basically can you calculate it so well this would be all wednesdays it's going teo senate will essentially be the same thing. So as long as cathy if you're out there as long as you are setting up to the mondays in a row it's going to know that's what you want to do if you don't then it's going to dio I believe in just as a sequence of all the days in a row see how it does that fifteen through twenty two but if I do two in a row it's going to know to do that so I just changed this to aye so we kind of do your example of doing two in a row to every other week or every week sees may you see, you can do that is going to all the wednesdays so you can know that's gonna be so that I'm sure that's good. Okay, all right. We just got some jokesters in the chat room talking about how how smart ex ellis way could do anything, right? It's definitely true. Sometimes we have to be a smart of it asked the questions and that's the thing. So it's it's really insane how smart it is and how like it's doing so much, but somehow it doesn't like break the machine. Amazing, like it's, really just phenomenal, and we really just scratched the surface again because if you go into some of these functions, you going to see all the stuff that it can do, like you, looking at some of these statistical functions and engineering and all this kind of stuff? I mean it really very, very sophisticated and it's it's pretty pretty magical.