12:45 pm - Advanced Formating + Styles
12:45 pm - Advanced Formating + Styles
5. 12:45 pm - Advanced Formating + Styles
The Basics of Microsoft Excel: Customization35:43 2
Anatomy of Microsoft Excel43:09 3
Formatting: Making it User Friendly39:05 4
11:30 am - Building a Document from Scratch31:22 5
12:45 pm - Advanced Formating + Styles43:30 6
1:30 pm - More Formatting, Transpose, & Alignments38:34 7
2:30 pm - Basic Formulas31:38
3:00 pm - Auto Sums & Inserting Rows19:46 9
3:30 pm - Advanced Auto Fills28:42 10
9:00 am - Working with Large Worksheets39:16 11
9:45 am - Hiding Columns41:50 12
10:50 am - Tips and Tricks on Printing31:53 13
11:20 AM - Titles, Headers, Footers and More35:37 14
12:50 pm - Q & A09:16 15
1:00 pm - The Magic of Sorting & Filtering29:41 16
1:30 pm - Conditional Formatting33:23 17
2:30 pm - Communicating with Charts30:42 18
3:00 pm - Making Your Chart Pop37:19 19
9:00 am - Working With Tables36:01 20
9:45 am - PivotTables, PivotCharts & Subtotals40:22 21
10:45 am - Graphics: Shapes30:45 22
11:15 am - Graphics: Pictures20:27 23
11:45 am - SmartArt14:31 24
1:00 pm - Text to Columns, Data Validation41:14 25
1:30 pm - Data Validation: Date20:59 26
2:30 pm - Working Between Programs26:24 27
Templates in Microsoft Excel14:13
12:45 pm - Advanced Formating + Styles
We're going to go continue on with advanced formatting we're gonna learn about styles and we're going to go into also alignment, so as I said earlier, we are going to do both pc and mac going back and forth that's working out for everybody and hopefully see that's a benefit whether you're on the pc or the mac, you can also see how we're going to be working another platform it could be helpful for you to learn how the other half lives, so we'll be starting off with with some more advanced formatting onda also working on on stiles okay, so I talked to louis about style styles is going to be some nicole is about pre pre set formats where they're already just kind of off the shelf working with that so we are going to go let's actually start off on the pc side this time and we're going to be working on our aptly named day one session three and we're gonna go into the advanced formatting plus styles are you concede that there? All right? So day one session three advanced formatting plus styl...
es will just go ahead and open that up here so I just want you know that down below we have number format here we have proper up a replace stiles one styles too and transpose so somebody asked a question earlier about being able to capitalize hey somebody from are from our home student based here so I'm actually going to show you a little bit of how to do that okay so actually gonna show you ah function how to make things uppercase and also capitalized so this is going to be a text related function as opposed to more working with formulas all right so we've got a lot of stuff that we can work with here so this is already pre set up this is pre formatted for a lot of us if we've already covered already so we worked with you know, adding some colors and some borders and things like that they're going to go into some or a little more advanced formatting so what are we talking about here? Advanced number formatting excuse me so number formatting in terms of looking our zip codes here they look a little bit odd for sir some of them look at our phone numbers here they also look a little bit harder to work with so we're gonna work with how you can do advance number formatting to do this and then I'm gonna come into working with dates and such a cz well where we can actually do ah formatting dates so let's just jump right into this so I'm going format our zip code to be a proper format so you might not have noticed this but you know we know that zip codes tend to have five digits and these are some of them are missing person when I typed it in I typed in zero six to seven eight and it did too said well, you know what zero isn't really a number doesn't really understand that but if I tell her that this is a zip code it will understand that likewise for phone numbers because I tell it that this is a phone number type before matt is going to know to make it look like a phone number so what does that look like that's gonna have parentheses is gonna have a hyphen things like that and maybe in your mind you could think about other things that might have that so we're gonna do some examples of that very quickly just a little bit so I'm just gonna go ahead and click here and I'm gonna do uh my shortcut key see here for those you could do your control hold on a second I have to make some adjustments here I would want to do the shortcut keys to show you but I see commands now it's not working okay, so you want to try to do it on the command control shift down arrow to do it for some reason it's not doing it on my computer I don't know why I'm ok anyway so tried on your own it's going to be command control shift down there or command shift down arrow okay, again, I'm gonna have some a little bit of challenges just because I'm working with the dual platform, but I want you to know that it is going to be control, shift down arrow just to select everything. And on the mac it's going to be command shift down arrow. Okay, so just remember some of those shortcut keys. All right? So essentially, I want to highlight the thing I want to work with. All right. So what am I talking about here? The zip code. Now, if you recall, I can get to my format cells, dialogue, bob in a number of different ways. If I'm on either the mack or the pc very easily, I can just right click and choose format cells. You see there's format cells. Now, another way you could do this on the pc is remember, we talked about this dialog box launcher. That's. Another way you can get to it, member, that little dimple I talked about so that's going to take me directly to my numbers dialog box within the format cells. All right, so what I'm looking to do is to work with one of these categories to make it so it does the right thing here, so I'm going to go ahead and go down to special so we have all of these things that we may or may not explore but some of these things are relatively self explanatory like currency in accounting and date we are going to explore but I do want you to see how we can work with what's called the specials you'll see here it actually has four things just right off the bat for us to work with with the zip code then we have a zip code port plus the four digits then we have phone number and we have so security numbers so we just want to do zip code and you notice that excel is considered enough to give us a little sample of what it's gonna look like you can see there it is right there okay that's just going to add on the zero for us really really nice and very straightforward is highlight and then go to your format cells dialog box and there we go it adds on the zeros for us now let's try it with a phone number and this is really cool because a huge huge time saver so it's highlight our phone number column there just everything that's inside of that and then I'll let you choose which one you want to do and remember there's also a shortcut you khun dio believe its control command one or control one if you want to do the shortcut key to able to do that or you can right click you do format cells or you can use your dialog box launcher so you have all kinds of options to do that somebody come back up to year and again I'm going to go to special and you'll see here I have a phone number shinto work with so very simply I click on that it gives me a preview of it and see that's preview in the first one there so you can see it so plan bam and adds on the parentheses another parentheses a little bit of a space and then a hyphen. So what does the magic of the behind the scenes coding to make this happen for us so very simply choose phone number, click on ok and then it's done magic. What happens if you don't see a style that you want like, say when you don't want the parentheses she's just one okay that's a good question so you could hear the questions to basically let's say you won't have a question you say you have a situation where you wanted to be a little bit more specialized that's when you're going to go into custom and custom is a little bit more complicated where you're working with some of these things here and you can set it up to put in all this special coating it's a little bit more advanced outside the scope of this class but you can certainly do your own research and see all these things because all these essentially just placeholders for you to say all right this is what it should look like you know based on that and that and you can see how you know there's some certain things in there like a parentheses you could probably get rid of the parentheses right in there to make it so it doesn't do that right so you'd essentially do that so you can set it up initially to have excel do the special coating for you then go to custom and then make these changes all of this gobble goop in there has a limit of a learning curve understanding what each of them means but there is kind of ah sort of symbology behind it in terms of the coding alright so say like the style but you have, uh mix between you know, us phone numbers and european so now you have country codes how can you kind of integrate both into that same column? Is that still within the formula's? Why would be two different types of phone numbers? Well, if it's two different you'd have tio you'd actually have to select each of those individually okay tim to make him do that because it wouldn't have it's really know if there's more than one number we wouldn't be able to necessarily do that because the code that we'll be putting in but you you could dio you could probably go a little more complex than that and then do like an if statement or something like that so if or or something like that so either one of those okay so we're getting a little bit past that but definitely good question because some of us might want to be able to do that so you can always go into custom and do that and then certainly go ahead and try to do some research on your own this is all very very goo gullible but a little bit of a learning curve all this kind of stuff because it's not super straightforward it's not super accessible toe like what the language that we already speak you know so just kind of to play around a little bit okay someone go and click okay and earlier we talked about pre format so pre formatting we mean it means basically formatting ahead of time so if we want we know that we're going to be putting in phone numbers we might want to actually format it ahead of time to make it so we just type in the numbers and then automatically puts it in there for us okay so let me just try one right here let's do social security number all right so I have that there and then just for fun what I'm going to do now while I'm doing this I like this format so I'm actually going to do after I finish this I'm going to copy this format to this and I'm gonna do that in a really fancy way a little bit different than what we've done already in the past but I want this to adopt this I'm going to do that just a little bit but I'm going to do first is essentially say hey xl I want everything to be pre formatted as a social security number for matt that's gonna have the hyphens in there and all that kind of stuff for me and I'll do one right next to it that's going to another phone numbers you can see how that's gonna work all right I'm gonna go back to my format cells dialog box right click format cells and I'm gonna come back here to special and their social security number right there and I simply click okay and again nothing really happens because all baked in it's all happening behind the scenes so I know this is going to be a little bit wider so let me go ahead and give it a little more rooms so security numbers when I was going to be nine digits plus the two hyphens so just go ahead and put in okay put in these things here all right you can see you have your nine digits right just like that and I'm not putting any hyphens in there I had returned and then bam there is a visiting their automatically for me. All right, that works for everybody. Okay, so, let's, try it again for phone number this time. So let's, go ahead and do that. And then I'm gonna highlight everything to pre format it. Come back again to my numbers dialog box go to special and this time I'm gonna say phone number and again it's pre formatting click okay. And this time I'm just type in. Ah, phone number. You see that? No hyphens, no parentheses. Nothing. But then when I hit return, that automatically does it for me. Just huge, huge time saver. So this is the kind of thing that you can set up ahead of time. And remember the last time we did this right? When did we do it? We do with rap text. That might be something you want to do all the time as well. So pre format it can save you a lot. A lot of time you just kind of know what you want to do. You envision it and then you just and then you just apply it and then when you apply it, it's just they're automatically behind the scenes. So the same thing with phone numbers, so I just continue put in some some numbers there. Just like that and then it's done just like that you don't have to worry about because parentheses and the hyphens and all that kind of stuff same thing with social security numbers and also if your potential you're doing zip codes right yes pl on dh there basically asking can you put in format cells conditionally on a country for example we know that the country code the u s is always one for the uk is forty four can you somehow set like an international format that will always recognize I'm assuming that's the specific question that were then once you set that in like it it will always be dollars and it's the us it will always be pounds of its the uk or is that just way too sophisticated so is there a country condition like it knows where you are at that point I mm I'm just guessing here but I'm guessing that's going to be I've never seen anything like that I'm under your options and it might just crashed and I'm sure you can do it for the whole web work sheep and not necessarily for specific south pacific area so this is a very good example of why we did our auto recovery I thank see that perfect they did that on purpose actual that was totally by design okay very studio looking see everything's here automatically see that if I did only if I kept it at the ten minutes it would it would no longer be here because we have only been here for eight minutes okay, so um now your question was about to go to the options here and let's see could be under advanced so the question is about the default that what you're asking is that what the question is? Why did I ask for a little bit more clarification? But the question exactly is can you be more? Is it possible to especially format cells conditionally on which country they are from? So I'm assuming if you if you're using a cell that you're using a european currency with automatic, you can set a condition to say this cell will always be euros the next there will always be dollars and it will always recognize that yeah, well either way the currency has to be applied regardless all right, so the currency has to still because by virtue of excel when you type in and phone number of who would you type in a number the number is just there by itself okay but then when you apply when you click on the icon to make it say ok, this is going to be your own this is going to pound this is going to be dollars we still have to click on that so regardless you still have to go find the find the currency no matter what because there's no automatic that happens right when I'm saying I'm assuming it also applies to phone numbers as I was saying you did but always default or one of it's the us number forty four, the uk but I've never seen anything that yeah, I have not worked out of his cell in other countries, but I would imagine they have it. They're already set up within within that special format that maybe it sits there for that particular country um prize for anyone to stump you completely. Sir, please. Um, okay, very good. So let me go ahead and get out of this. All right? And then okay, so we have this year now let me show you before we move on to some other formatting. It's really great tool that's going to allow us to copy this formatting where we have, you know, thie gray and we have the border on both sides. We also have the boat, the bold and everything like that, we won't apply it to these guys. So if you recall towards the end of last segment we did something called auto fill remember auto fill we did we did auto fill when we copied all the california's you remember that we did auto fill, I think, for something else as well, but we're going to auto fill this time for formatting and you're going to use this a lot a lot a lot because I basically want to have this format applied to here and while we're doing this we're going to learn about something else that is probably you've probably ignored for most of your time on any of these office programs that is really one of the biggest helpers that people tend to just not know what it is so they just kind of like shoot away like a gnat so notice here I have my auto filled plus sign and I click and drag and then that happens and I'm gonna let go and you see what it does right there it copied the words and the formatting you see that and that's not necessarily what I want but we're on the right track so what I want you to notice is this little guy right here that's known as my auto fill options you're going to see that all the time whenever you're doing like pasting or auto fill something like that is going to say hey, you know what? I'm not sure if you really wanted to do that so I'm gonna give you some other options so I click on that and instead of copying the cells I just want to fill formatting on lee so I do that and then bam just like that it kept the text that was in there but it copy the formatting what was there I brought in the borders who brought in the colors all that kind of stuff here and the same thing could be said about formatting for numbers all right let's just say for example, this was dollars I'm just going to go ahead and just do this right now it's just gonna go ahead and just say budget just so we can see this here all right? I'm gonna make this into a dollar sign do that and that's dollars now what I can do now is I can copy the format even though it's not for matt in the way that we know format of what may be the way that we tend to think about format in terms of the color of this shape because the font and all that kind of stuff number also still counts as a format as faras excel is concerned but it's a number format so what we're going to do is we're going to say, hey, you know what? This is going to be a dollar sign for the rest of these so what I'm going to do this time is simply double click all right? I double clicked on there because it is going all the way to the end of it I'll do that one more time all right? So when you double click to do an auto fill it's basically saying hey do an auto fill till the end of that segment all right, everybody got that that's a really, really neat trick when you double click on this instead of clicking and dragging it goes all the witness you've got, like hundreds of them or thousands of them, it goes all the way in the end, it knows to do that. Of course, I could easily just click and drag to do this, but I wanted to show you another shortcut click and drag on me if I double click on that that happens and certainly not all the same exact number. So what we're paying you two were paying attention to that little guy we've been ignoring all these years and there he or she is can tell you, okay, we're going to go ahead and click on that and we're going to say, phil formatting on lee and see just like that now it's brought it as the dollar signs or if it's a number for matt okay, so that's something we'll be using a lot a lot a lot you could do it for dates, you could do it for other number four match you could do it for, uh, text formats there's so many, so many things will be coming back to this over and over again, and it could have done that for the phone number formats really, really helpful. Ok any questions on that is that is that a good everybody feel like they got they got good control over that so I'll just do it really quickly on the max side as well and I'm going to open up to uh numbering format I believe that's what we were advanced formatting advance for mounting plus styles and where are you okay there we go and see very similar you see here I was gonna go ahead and do all the rest of these the same way and I'm going to go now I'm gonna right click on this I'm going to say format cells and again it could have done command one to do that and I'm gonna go to my number when you go to special and then that wass phone numbers I do that give me a preview of it fantastic same thing okay so again I'm just trying to show you those of us who were on the different platform that is almost exactly the same thing on the pc and on the mac okay one thing I want to show you also is that for those who wanted to kind of do like some quick easy things here you're going to see you have this number group and you have a drop down all right this drop down is going to take you to like a variety of different places right there so if I just click on that that just convert is it right to that particular thing? Right? So if I'm going to have, like, a date or something like that, it's going to convert it to those particular things here, so time percentage all that kind of stuff all going to be right there in the drop down on the pc side, you're going to see it's a slightly slightly different. So let me just show you unexamined. Well, I'm just gonna type in date entered, all right? So this is a number, another type of number formatting I'm going to fix this, you can probably guess what I'm going to do. This will be some good review. I wantto basically take this for matty and apply it to here. I'm just going to use my auto fill, click and drag click on my little auto fill options, phil, for money only see that nice. All right, so I'm gonna put in date entered, so they're going, they're going to give me a certain date format. All right? I'm gonna go ahead and say twelve seventeen and that does that now I don't necessarily like that date format, so I'm going to change that using this drop down the drop down is a little bit different on the window side, so just understand that what I just showed you is on the backside. This is on the window side you see here they've got a few different kind of more specific things right there, so what I'd like to do is either work with the date or this date it's usually gonna say long gator short date something like that I want to be able to make it look like this twelve, seventeen thirteen just like that click on that and then that what is that it has put in the holy I should actually say long date so I want to say a short date and long day there we go so we have a short date and long give again this is only on the pc side's just understand that so we have short date and long date right there and the nice fast way of being able to make that change two dates all right, so let's come back to again, we're gonna go back to the max, I'll show you another way we could do this. I do this I was going to go ahead and just type in twelve seventeen and that comes into something wacky, right? Because it doesn't actually see this is a date it's converted it to a number of format all right, so it doesn't understand that what that means is like how many? I think technically what that actually means is how many days has passed since january first nineteen o one believe it or not that's what we're talking about here forty one thousand days so that's how excel is calculated it's a little bit crazy so that makes no sense to us so we're going to change that. So I'm going to show you how we can now go into the date format to then make things look how we wanted to look so again we're going to go to our format cells by right clicking, choosing format cells and we're going to see that we have the date category and you see, we have all of these different types of date formats we can choose they're really a lot of the senate depend on what you wanted display it's gonna depend on how much room that you have, things like that so you'll see that I can say, you know, double blah whatever with a full day, I can actually have it worse, but flushed out entire day see the entire month spelled out you have all these things here, so I'm just gonna go ahead and choose that one and again I'm under the date category and go to here, I'm going to say okay, and then there that is okay, so hopefully pretty straightforward it's the same thing on the pc side as it is on the backside when you're wanted to deal with dates okay, and then eventually we're going to get into date with, um with auto fill as well because it might be time when you want to actually have dates that are kind of in sequence of each other and things like that. Okay, um, any questions on this up to this point? Looking good? Okay, excellent, excellent. So let's, let's jump over teo let's actually, we're going to skip this proper upper and replace right now we're going to styles one into right and you see, we have some kind candidates for us to work with here. So these air again sort of like bare bones things where we're not actually don't have too much to work with right now in terms of any color or anything like that and what we've done already was we've done it kind of quote unquote manually where we want to be able to actually make it so it's like it's right off the shelf and we could work with different styles all right? So the way that we're going to do that is let me just see here on this there is no under tables like it's going to be somewhat somewhat similar to this but not necessarily the same thing as tables are inside on the pc side let me just then they don't call it style so much when we come back here and I'm going to go to your styles one here and then let me just kind of bring this up a little bit so we can see and what we're talking about here these self style so these sell styles are basically pre set formats to be able to say, hey, this is what I want this to look like, which is one klick instead of doing like bold and pink background and underline it's already there for me, so what I want to do is simply his highlights what I want to work with and then you're going to see get on the p c side, I'm going to get this great thing called a live preview, so as I mouse over it see that's actually showing me what it's gonna look like, this is my live preview, so I can actually take a look at it before I decide and commit to something so you can see that there's even a whole thing just for headings just like that. So I'm going to make this heading three and just like that, I've now added on that font that color that size and that border underneath it just because it was a cell style just built right in I don't have to come back to the max side to see if it's actually even in options I thought it was something to dig a little bit deeper right now we can go ahead and go to this one here I'll go just to choose our names I'm gonna go to sell styles and then let's just go ahead and try another one you can see how that works so just priest that's signs so weir styles so important but let's let's talk about that for a second why would we want to use styles number one for speed as you can see all right number one you can see how the fact that all I have to do is highlight and click and that's really nice just bam highlight and then it's done probably more than that is for consistency because let's just say we have headings we want to make our headings be across the board the same thing over and over again because we wanted to look professional we wanted to make it look clean we wanted to look cohesive we wantto have kind of a theme with our with our sheets so that's important so it's a good good thing teo think about when you're working with these two I actually work with stiles tohave consistency tohave these themes and it's also going to save you a lot of time to execute additionally it'll save you time if you want to change your styles at the same time so if any of you are familiar with an indesign working with styles it's the same idea of paragraph styles, object styles and character styles which means that if you wanted to change his style to begin with because I wanted to change this style to be something totally different, everything that has that style connected to it, we'll also change that makes sense, okay, so it's linked up to that if I change it someplace else, I'd like to make one change in all ten of the things that I've had those style associated with will actually change. Okay, hopefully that's clear. All right, so we have this there we can go ahead and just add to it and I'm gonna get into a second in terms of making a new style and also how to gia modify styles all right? So let's, just go ahead and I'm going to keep this here and I'm going to do this one right here. I'm going to go ahead and just create a style on my own so that's another great thing is those air styles that we were looking at those air styles that come right off the shelf for us to work with, which is nice, but we want to be able to have our own personal style we want to be able to customize it with our own fonts and colors and do whatever we want to do to it we can go ahead and have our little our fingerprint on there and make it our own so I'm gonna go ahead right now and first just change it to what I wanted to be so very simply I could go ahead and just make this change and I'll just choose this kind of berlin thing right there I'm going to make this a little bit bigger, right? And I'm going to go ahead and change the color do that and then for fun I'll just put a little thick border underneath it and you can see here I have thick bottom border, I do that and then, um I'll go ahead and give it a little bit of a color, okay? Let's do this kind of light gray all right, so I have that there, all right? So now what I'd like to do is copy this because I'm planning on using it a lot like I'm gonna use it may be on this or maybe on one of these guys or whatever wanted two or maybe something down below so very simply all I do is selected first go over to my styles and then wait and on the bottom is this new cell style option it's gonna be a new cell style based on what I've selected you can see here all the things that it's actually picked up on the font is berlin sand sixteen dark red it has a bottom border and its shaded all right, fantastic. I can even go a little bit further deeper and go into my formatting dialog box and actually add some other things on there if I want to. I'm not gonna do that right now, but know that you can do that if you like. And I'm just gonna go ahead and give this a title. All right? I'm just gonna call this, um, heading page title just like that. Yeah. And I'm gonna click, okay? And then you're going to see under myself styles. I will see that right here, it's not as where it saved k c alphabetize it just like that. You see? There it is right there. So if I want apply, this was going to move this down a little bit. I'm just gonna drag this down here. Remember how I did that? I'm using my eagle's claw just drag that down. I want a kind of away from it. Just imagine this is kind of going to be one heading. Another heading. Very simply. I can not go to myself styles and just apply that very quickly and easily remember how long it took me to actually create this. Now that only just takes a half a second to be able to apply this all right already with me on that. So I feel pretty cool, okay, so something that hopefully you'll find some some good usage for and again, you can do it on any of these things, and we can modify the style we can do all kinds of different things to it as well. All right, so if I go now to sell styles, you'll see when I right click on the style it's gonna allow me to have a number different options, including the ability to modify it. So I right click and you see there's modify, and this takes me to this part right here once again, same thing, and then I'm gonna click on form at this time because again, what I'm doing is I'm modifying style, I'm not doing a new one, so I actually do want to come in here and then I'm gonna have all my options. And you know what? I changed my mind about the color I'm just going to make. It may be a dark of black just like that look, okay, look. Okay, okay. And then this one, actually, sometimes the first one you did it does not actually take it so that I can actually most of the other ones will do, and then from now on, this will do it it's a bit of a bug but it didn't do it on the first one I did because that was the original, but I'll do it on all the other ones sometimes the original when it doesn't take it so now if I do it one more time just so you know what I mean? I just want you to understand that there's a little bit of a bug to it I'm gonna say modify again format again and I'm going to choose a different color again so let's just do kind of that color, okay? Okay see now they both changed so just understand because it was the first one it just doesn't take it because it wasn't technically applied to it it was based on often it is a little bit of a weird bug, but now you know howto sort of deal with that, okay? All right so that's that that's working with styles in a nut shell on the window side you can see all these other things that you can work with a little bit more advanced not gonna get into all the details, but if you want to actually take a style from another document that would be emerging the style but we're not going to get into that right yet so let me jump over to the to the max side and confirm whether that does in fact exist because I cannot see it I'm not remembering where that might be maybe it doesn't exist but I think that there is a stiles right here ok yeah so it's just it's just a little bit different okay so you can actually yes it's slightly slightly different in here so you can actually go to it I'll just do that one more time under format and then style right here and then I could actually name it and do everything I want to do I conform at it all right here so then it's a slight like I said a slightly slightly different to ariel black make it nice and easy going to say twelve points I'll say the color is going to be this dark red and then I understand you can do all these other things right so I can actually put a phil underneath it I want to and I'll do this kind of light gray click okay okay, that was somehow everything was highlighted yeah it's not exactly the same tell you the truth so like I was telling in the beginning things are a little bit different so this was actually a style of the whole thing. So I do apologize for that just like something's you'll you'll see that you will have on the p c said that you don't have on the mac side and vice versa and this happens to be one of them but I'm going to do a little bit of research on this probably after after today's session and I'll see that could be over looking something all together then I'll come back and I'll clean it up and kind of let you know what I find out okay so let's now go now to this proper upper and replace all right so somebody asked about this cat I think it was you okay, right? I think you were asking about that about in terms of doing like first name last name type of thing we have like shannon lea millie see, these are all in small caps so what we're going to do now is we're gonna work with doing a function for text to make it so these become proper he's become proper right? So this is going to be proper upper and replace so we're just going to do the proper one for right now and we'll do upper as well so how do we do that? Okay, every formula starts off with an equal sign okay? And even though this is just going to be for text it's excel still sees it as a formula x l still sees this as some kind of function right? This is gonna be a text function so before actually get into this I wantto take you a little bit further into our functions. You can learn a little bit about functions before we actually do this to kind of be able to teach yourself on some levels he'll notice here I have this fx right there and see if I click on that it says open the force of the builder right? And you gonna see right sistol fx right there and if you're on the pc it's going to look a little bit different but you can see that I can go to a number of different categories where you can see here engineering carrot category let's see, I know what it is at this point but I want you to be able to understand how the interface works. This is financial stuff this is information do you see that there on dh then logical and they were trying to get down to text so I want you to learn a little bit about what each of these things are you khun see how many formulas there are I mean, you can there's so much stuff with an excel statistical and then finally we you have a text of the bottom and the pc is going to be a little bit different. So we'll come back to that in just a second so here's text and see a lot of things you khun dio including proper so when I click on that it tells me here what it actually is so this is what what I'm trying to get for you so if you see a particular format piccoli, if you're working in um you know ah temple it or something like that you'd be able to actually learn all about that all right so you can see here convert the texture into a proper case the first letter in each word in uppercase and all other letters teo lower case already telling me what the sin taxes see that so proper and that seems okay where is the texas you want to do it okay everybody got that all right so it takes a little time to get there if you don't know what it is because I could have actually just typed in proper here but it wanted you to see howto work with this you can actually do it on your own all right so I'm gonna go ahead and double click on that and you're going to see this pops up with kind of a little shortcut for me tio put in what my text is that I want to actually change convert to it all right you see that so I don't actually have to type in any formula it's done it for me we're going to do it both ways because some people like to just kind of have their hands dirty with it so again all I did was double click on the word proper and that popped up in putting the equal sign and the proper in there for me with an open front the season a closed parentheses waiting for me to essentially select what I want to make it upper case all I do now is click on that all right nobody with may and it says proper a four and I simply hit return and let's do that one other way let's actually get rid of this formula builder I don't care about that too much because we're going to a different way which I actually prefer this time we're going to type an equal sign we're gonna do it for shannon lied so we're gonna type in the equal sign and then pr you'll notice that something pops up to kind of help you finish your sentence so a nice shortcut for this when it does that it's simply okay uh well it should have done it with the tab key let me see here proper okay all right maybe I'm not this one but it should do it on your computer once it sees it there if you hit the tab key it should actually just pop right up if not just to just type in the word proper and then open parentheses and then simply click on the thing that you want to make a proper and then close the parentheses and return all right let's just try that one more time I know that was a little bit a little bit sloppy let's try that one more time it's stipend proper open parentheses and this time we're going to dio james know melinda or are we here yeah james I think I'm going to skip one my dad I think I'm down one one row okay so we're gonna go ahead and just type in click on james could enter and there it is pretty clear we're getting any questions here I know sorry of doing a little bit and the people really seem to be engaged okay students anything here that's challenging okay, good and I made a little error here but this will be a teachable moment see, I moved it down a little bit lower some now I can actually fix that problem no problem by clicking and dragging to bring it up there there was a question just for our clarification between the uses of the words function and formulas there is a difference and what that absolutely that's a great question and I'm going to go into much more detail on formulas and versus functions in a little bit but the main difference is a a function basically summarizes what a formula is with text so in this case it's a function so something you might see instead of saying a one plus b one plus c one you'd actually say hey some of a one through the one instead of all the plus signs okay so it's essentially a text text functions you can see here is his proper that's a function so whenever you have text in they're going to see it's gonna be a function okay versus a bunch of what we call operators like plus sign divided by and things like that we have a function that basically sums all that stuff up okay that's a great question we're going to get into all that stuff when we get into our formula's all right now I'd like to just finish this whole thing up I don't want to type an equal sign proper one hundred thousand times so how my I'm going to ask you all here how am I going to then automatically fill it in well just a quick click to do for everything underneath it very good I'm going to double click on the right hand corner here because that gives me my automatic auto fill and that works for this too because auto filling the format so that is just fantastic a huge huge time saver just double click it to do the auto fella do that again you see here okay I'm gonna come over here I'm gonna double click right here because when you're double click for auto fill automatically fills in everything underneath it as an auto fill understanding that I want to actually do it in a relative way so I understand if you look at the formula this is a this is a nine this is a ten etcetera because excel us smart enough to know what you want to do okay and then let's lastly on this particular one let's do um upper all right, so we're going to do upper so this excess cell it's going to be very, very similar. Except we're just gonna do a different formula, so I'm just gonna go ahead and do equal sign oper open parentheses. And then I clicked on that to make say, hey, that's gonna be upper had returned and then bam, it makes everything in all caps. Okay, you see that? Then we can go ahead and just do the same thing. I'll do that again if you want to just follow along their way it one more time upper open parentheses and just simply click on the thing you want and just pay attention to these things too. You see how this says upper and then text this is a little cheat sheet that excel is trying to give to excel is trying to say like, hey, this is the syntax you want to put, go and type that in and then this was gonna happen after that when you get more advanced since actual see it's going to pop up like that with a lot of detail for you to work from and again, I could have gone through my formula builder and done it that way as well, but these air pretty straightforward if you wantto learn mohr advanced form atticus, you may have more advanced functions then you might want to go into the formula builder and it's going to put all that kind of stuff in there for you. All right? Or at least give you a little field for you to be able to type in there. Now, I'm going to go ahead and just double click here. And that just pops in automatically. Just doing that.
Ratings and Reviews
Even though it's 2021, this course is still amazing. I followed along just fine using the latest version (365). I watched thinking it was just going to be another "basic" overview of Excel. It's not. I learned a lot that could really help me keep track of my business, not just financial aspects either. I HIGHLY recommend this course.
a Creativelive Student
Great class and highly recommended but now out of date. Surely an update is planned?
This is Excel for Mac. I think real Excel work is done in Excel for PC as that version offer much more compared to Mac version. Apart from that shortcuts are quite different between versions. Just a note.