Lessons

Lesson Info

11:30 am - Building a Document from Scratch

So that's our basics of formatting so what I've done in this case is I've actually made it so there's already something already completed so I'm going to jump to you right now we're just starting something from scratch because some of us may have never actually done an excel document from scratch, so this is the time we were going to just do something just completely completely bare just brand new it was going to put in some dummy data and then we're actually gonna go to one that eyes going to be a little bit more flushed out for us, okay? Not quite as much as this I'm saying jesse l was saying, you know how today create all this but now we're going to find out just yes, exactly so now waken be inspired but what we have here once we finally create our own all right? So the shortcut key for creating a brand new one the word and when you have new I just gave it away there were news starts with what letter there's a demonic so it's going to be command and or control and to be able to crea...

te a brand new document from scratch. All right, you're going to see that's also going to transfer over and other programs as well, so that's a really good one to know about, so we're going to do command and for new uh where are you command and who aren't you know, doing it uh oh I'm sorry because I'm still in the pc side sorry okay let's come over to the max side I'll do command and and there we go and see now I'm just doing something from scratch so the reason why I'm showing you this is because I want us to kind of just take a look att how content comes in and how we're going we may experience some strange things we're putting in some basic things and we also want to be able to form at some things from scratch outside of what they've already given us. So I want you to follow along as I do this because the words that I put in are going to have a purpose in terms of what we're going to do to them afterwards. All right? So let's just go ahead now and type out first name last name I'm going to say home address and what am I doing to go to the left of going from the left to the right is what hitting tab or you can use the arrow keys as well I'm going to use the tab keeper the arrow keys will do just the same but I'd like to use the tab key because it's right there you know, just like a little clue quick pinky joint right away over there right and they're going to notice I talked in home address and it's too long we're going to deal with that on the second day and we're gonna go ahead and just say, um home city home states and then we're going to go and say zip code and were going to say work email address and when you're finished typing that in I'll give you a second don't do anything yet I want you to hit the enter key I want you to notice how like a typewriter it comes all the way back to the beginning so that's why we want to use these modifier keys we want to use tab keep going across them when you're finished with ro typing and end use the enter key because it knows for you to come all the way back to the other side back to the beginning all right? So just go and you can put in your name all right? So I am putting whatever you want okay five, five five main street and just keep just keep typing is whatever it's going to be him in the tie pin san francisco, california canine four one one o and this is good practice to get used to that tab notice how fast you can do it as you're doing that okay, so what this kind of thing will be used for is you have a client list you might have an inventory list is somebody asked about so this is basically kind of the starting point for this where were not necessarily having you know more formatting or anything like that I'll set up your question mine doesn't actually when I press enter return doesn't actually go all the way to the love the on ly time it's going to do that is if you may have kind of stopped first second typically if you stop and you kind of erased and did something weird is going to think that was the last place that she wanted to go back to if you start from the beginning and just kind of keep going fat tap tap tap tap then it'll go back again so maybe when we put in some more information you can try that again but it shouldn't do that if it's if it's if it's uninterrupted that makes sense if it's uninterrupted in terms of putting in the content but if it was like oh I made a mistake kind of thing it's going to go back to where that mistake happens that's thinks that it's doing something good for you but in fact it might not be but in the future it will be fine um okay very good so um you notice that I have a few kind of like a milwaukee things that I'm working with here right never noticed the first thing is home atter I'm missing that are you going to see also that san francisco kind of gets cut off? It was a little more room in there. My work email address, right not so great here. I also like to put a little I want to add in something else inside of you h one and notice how work email address goes into h one that's not really something I want so it could be a little bit sort of confusing or miss a misleading that it's actually in h but let's just go in and click let's do this together click on g one and I want you to notice that work email address is here. But if I click on h one, your instinct might be to think that well, yeah that's just going to say address right when in fact it says nothing because all this is doing is carrying over to the other side so it's basically just sort of like, you know, you have a tree in your neighbor's yard that's essentially what's happening there but it's still in your property kind of thing, so we're going to want to fix that eventually, but understand that technically this is all one cell and that's the g one self same thing for home address notice how it's actually cut off, but because we have something in d one it goes underneath it all right so we're gonna be able to fix that and just a little bit all right? So let's just go now to h one and I'm just gonna go ahead and just say notes because I wanna have some notes there for my clients or whatever it's going to be and I hit return and notice that work email is now cut off we're going to be able to work with that nice and easy all right so what I want to do now I want to be able to first of all do on auto fit for this I want to do an auto fit that's going to make this column auto fit remember we talked about auto fit earlier what we did our little rhyme could you what clique taught ofit double clicked auto fit auto if it has to do with with of the column or the height of the road so remember I want to move my mouse right between the two column headings right there on the border of the lines right there not here not here not here always going to be at the top of the column so I want to be able to adjust this so I could do it manually if you want to because you wanted to maybe just do this I'm gonna click and drag this and like yeah that's fine but I'm going to do that or I could simply double click and see, it automatically fits there. Okay, remember doubleclick thought ofit that's going to make your life very, very easy. Easier in fact, okay. And you can do it also the same thing now with work email because notice that there's work email but I just really long email address that's underneath there. Okay, so it's longer than the rest of these. So what auto fit does is it's going to find the longest thing within the column and just auto fitted around that? What is going to do so again? Just simply do double flow between the gene, the h and then bam! Just like that it's all set up. Okay? Yes, please. I got lazy and I have everything up in a lower case. Is there a way to fix that there's a function for that. Really? Huh? Yes, there's a function called proper or upper? I'm not sure we're going to be getting into that. But those you have that question. If you want to research it on your own, the function is just called upper. If you want everything to be an uppercase or proper if you wanted to be first name last name but that's yeah, and every function starts with what in equal sign alright, so that you just type an equal sign and then upper or proper and I'm going to be going into some functions and I'll toast I'll show you how to kind of teach yourself when you want to get into some more advanced stuff where you can learn about the functions as well once we get into function but we won't be getting into that one all right so what I want you to dio I know you're going to kill me for this let's undo what we just did so I can show you an even shorter shortcut on howto work with auto fit just undo all of the auto fits that you just did which should be just two okay, so they should just kind of still be scrunched in there you may have twenty different columns that are going to be kneading for an auto fit so rather than having to do each individual one separately I'm going to show you you can do it much much faster than that so what we're going to do is we're going to highlight all of our columns was going to go from here it is a right there right? So and again I have my downward facing arrow you want people to see that downward facing arrow because it's showing me and that the whole column is going to go ahead and get selected as opposed to this remember xl is trying to communicate to us so it's gonna go and click and drag over all the way to h and now with everything highlighted if I do a double click on any one of those it'll also fit the entire spreadsheets I come over here double click and then bam everything it's auto fitted that okay let's undo that one more time I must show you something related to that which is going to be involved with selecting all alright, so notice how we have on the upper left hand corner we have this little guy with this kind of little nexus this little sort of like armpit shoulder where the a and the one meet you see that and notice how on the max side I get this arrow that's kind of pointing diagonally see that that's what's known as the select all so we're going to be selecting the entire spreadsheet now at this point. Okay, so when I do that everything is selected that might be a little bit faster if you know that you want to have on auto fit for the entire spreadsheet. Just go ahead and do a select all and you can go ahead and just double click anywhere and it does the same thing when any guesses what the shortcut keys for select all on your keyboard or command a on the mac controlling on the pc and it would do the same exact thing it just selects everything right there okay, so that's going to be really nice for us to work with just in terms of auto fit? Huge huge saver of time. Okay, all right, so now you see here I have just kind of with his bare bones spread sheet and I can work with formatting it very easily very nicely just by simply clicking in drag and do all that same stuff and you can use your shortcut keys if you want to and it's all the same thing here so I can go ahead and choose whatever color I want and then okay, and then I can bowl that you can do all that same stuff here, all right? So that's we're going to be working with this in terms of starting something from scratch. So the first thing that I showed you was actually something that was already kind of bare bones. So you've already had some experience with this working with something that's, bare bones, but in terms of putting the stuff in that's what that was the first part of we talked about in terms of doing the tab doing enter and then also making sure you're working with the auto fit to be ableto make sure that that everything comes in nice and clean and we're not any nothing is really hiding from us now let's do something a little bit further now with notes because sometimes what you're gonna want to do is you're gonna want to make something that that wraps. What does that mean? That means that we're gonna have notes they're going to go on and on and on and on and on, but we don't wanna have it make it go too far wide off of the column or off of the cell itself. We wanted to call rapping, so I'm gonna go to column h and I'm just gonna make that about wide. So what, twenty seven? Is that what it says? Let's find so I know that my notes are going to be just like that. So I'm just gonna go ahead and type in a bunch of stuff here, and we're going typing, whatever. But the point is I want to make it type until it gets to the edge of the the edge of the cell here. Okay, so we go in and say this client was very easy to work with, remember their birth day and holiday gifts, and I do that and that's a big problem that's just gonna spill out over there. It's not gonna look good when I print it. I might even have something over here at the end of it and column I and that's gonna be a problem for me so what we're gonna do is we're gonna work with a man called wrapping the text so basically that's going to wrap it so when it comes to the edge it's like a little brick wall is going to shoot it down to the next line so bam and the bam is going to go down after that so we're going to do that right now very quickly so there simply were going to go to wrap tax and a lot of this stuff is going to be on the pc also kind of trying to divide the time as evenly as possible I'm gonna click on that and this dropped and comes up it's gonna say rap text and then just like that now it automatically wraps it for me okay so that's really really nice because it's not going to spill on over and because this has to do with what we're talking about printing so printing is going to be really, really key because printing is finite excel in the digital world is almost sort of infinite you can keep going on and on and on but printing this finite because we only have one piece of paper to work with so we may need to worry about are with in this case and that's where the rap text comes in we're going to do a little bit more of this when we start talking about alignment but I just want you to see this part here now let's say I know I want to have more notes coming way the bottom all of these because I'm gonna have a whole series of different clients and I'm going to be doing our inventory whatever it's going to do so I know that I need to do a rap text moving forward for all the rest of these okay, so what I'm gonna do is I'm going something called pre formatting free means before all right? So I'm going to format it ahead of time we were going to do a few those things for pre formatting in this class actually just you can see how we can form at things ahead of time so we know that it's going to be x y and z let's just go ahead and do that so how do I reformat same way as you do a normal format you go ahead and you highlights what you want to have format it very simply just click and drag and I know I'm going to do twenty people here very simple and I just simply say rap text it's kind of like a little ghost but you're setting it up ahead of time to say hey when this stuff comes in just do a rap text that's it so when a clip on rack tech my click on rap text this pops up again, nothing appears to happen but what's going on now is it's all kind of, like, baked in it's, baked into the cells itself on the back end of things, and now when I click on this and I say, okay, we're gonna have a difficult, difficult client is client was very that we know what has any of those right difficult client and see that to work, okay, remember to be nice, okay? And you see that how it did it automatically form because I pre format of it all right? And you're going to see that same idea applies to any number of things I'm going to jump over to the other side, to the max, into the means or the window side, and we're going to do something very similar in terms of reformatting, but everything I've done up until this point on the backside is almost exactly the same on the pc side, so I won't really be repeating so much, but I'm going to have some content for us to work with. Ok, so how we doing with questions entirely? Sure, I follow it, but I'll throw it out there exactly the way it is step of land is saying his question about the order of it. If you later enter a longer entry in one of the columns, will the column get automatically broader again? Toe auto fit no there's not an automatic you'll still have the auto fit the rob text okay, well I need to do that you also the ability to do the shrink to fit also so that'll have to do with that'll have to do with the pre formatting basically so you can pre format to make it so it wraps at that point or it does shrink to fit which essentially this sort of like the cousin to wrapping texts where you going teo trying to fit essentially does the same thing but shrinks down the content to fit it in there and then charles hamilton also had a question I think this is going to be a pretty easy fix but I'm not sure he asked what if you want the non rap tech sells in in the other cells at the top of the cell versus the bottom and that's just simply sure I understand the situation's to say it again the non wraps text in the other cells what if you want them at the top of the cell instead of at the bottom oh so like this is an example like right here like you mean these like that this is not this is not wrapped that thing that's right? So how would you put david at the top of ok good so we're going to be working with alignment on the next exercise but this is what's known as vertical alignments okay let's hold it then we're coming up to the greater good so just a little a little teaser for you here's alignment and you'll be able to see there's our vertical and there's a horizontal so we'll get to that so stay tuned okay but great question okay, very good when we come back over to the the pc side of things and then let's open up tio different document altogether now and let's open up now tio let's see basic formatting let's try that you see here we have some stuff for us to work with again some basic formatting of stuff here not a whole lot of you know it's not very pretty we can work with all these things here and you're going to see I have my auto fit ability to do that right there whenever you see pound signs like that don't get scared it happens what that means is that a number needs to be auto fitted because what's happening in there if I click inside here here's another function for you to know about instead of today this is now. So what now means it means that not only its date but it's also the time as well and it's way too big for this so what I'm going to do is guess what I want auto fit so what I'm gonna do class how do you auto fit with our little she's here I'm karima doubleclick okay I do that and then it's automatically fit in there and I can see that they're so what it does it automatically fits it to the longest part here so it shows it right there so I could go ahead and work without so that double click help me out with that but anyway, the thing I want you to take away from that is the fact that when you see those pound signs was pound signs mean that it's not text because the text will overlap to the next column whereas uh numbers will just show a pound sign asshole siri's of pound signs again let me do that one more time all right? She can see that there so you're gonna be ableto just see that as pound signs okay, so that's a good thing to know about so what I want I'm leaving this for you if you want to do a bit of practice thing especially since we're gonna have a little bit of a lunch break this would be a good one for you to practise on so you can see kind of a bare bones she for us to work with and you can go you can do some formatting on this is well and we're going to going into some number formatting to do some dollar signs things like that as well but you could go out and play around with this if you want you just to be able to kind of see kind of all the selfish you can, you can practice on to see what you remember in terms of working with shading and fonts and things like that. All right? So let me just also point out the fact that again we're on the window side we have this wrapped text right here so you could do your rap texting right there if we want to add it on there, you can see that as well, all right? And then all your other stuff is exactly the same gender terms of phil color, etcetera, etcetera. All right, so let's come back to another father we can work with, and we're going to work with moving and copying, pacing data and also number formatting, so this is going to be a little bit of review what we done, so go what we've already done. We're going to do day to session to moving, copying and pasting data, so we've already done a little bit exercise on this, but I think it's always important to review, I don't like to take for granted that since we already did it that people actually remember it so it's always good to kind of do a little bit of reinforcing, all right? So let's just say, for example, we want to move acquisition down below tio here we actually want to kind of move it so it's no longer going to be here so remember some I'm going to be on the pc side. I can just move it by using my move tool on my move tool you can see is going to be that eagle's claw remember that the four sided arrow and on the max side it's going to be the hand so we click on that kind of grabs it so very simply is going to go and click and drag this and then it's done, all right? And you could do that with so many, so many different things again, I just want to do a little reinforcement on this s o and on the max I remember just wait for it to get the hand and then just go ahead and dragon now on the, uh the other one here we want to actually copy this so there's two ways that I can copy this, I'm going to show you a new one that we have not done yet, and this is going to be something called auto film as opposed to auto fit. The auto fill is awesome. We're going to doing auto fill a lot in this segment or I should say, and then this whole course, because basically what I want to do is I want to basically copy what's in here and I want to copy it actually for the next four you're going to see how great this is and this is just one generation of of the of the auto film so brand new thing we have not done if you look in the lower right of my active cell, you're going to see a little black box for my mouse over the little black box I'm gonna get a little black plus sign I think about plus on its going toe add more to it and this is just one of several that I'm going to show you during the course so what I want to do is I'm gonna auto fill this lets go in and click and drag down one, two, three, four and notice how it gives me a little preview like a little kind of a little thing is popping up saying, hey, this is what you're about to put in there. So when I let go all of these air now international sales and that could be done for so many, so many different things if you want to copy something. So a good example of when you might be doing this, okay, everybody got that, I don't want to move on and I'll just do that one more time make sure that we're clear on this just go out and do an auto fill and just drag that straight down now, again, an example of when you made do this is everybody I work with is from california. All right? Why? I have to sit waste my time doing california. So guess what? I'm just going to move my mouse on top of the lower right of mice. Active cell turned into a little black cross like I did on the pc and it simply click and drag to get a whole bunch of cs saving me a ton of time just like that. And it could be anything you want it to be the same business could be something like that, right? And again, this is just gonna be one adoration off the auto fill. Okay, so we're going to going into mohr on that and just just a little bit, okay? All right. And then one last thing I want o finish on. I'm working because I want to get some of your questions is I want to focus on number formatting. We're going to do a lot of number formatting and then this course, but this is going to be our just basic number formatting. So let's, come back, tio, come back to the pc side and open up to day one session to number formatting, and she hears something that's more or less complete for us to work with. All right, but I'd like to see something a little more kind of communicative like what's going on you know, I don't know that these are dollars I might want to change that to a different kind of thing that's probably gonna be percentage right makes sense it's commission rate etcetera, etcetera. So what I want to do is I just want to have this bottom part the numbers and also this right part be numbers okay, so I'm just gonna go ahead and highlight what I want and then I'm going to dio I like this part I have to get used to the difference here. Okay? I'll just get one of the time anyway so you guys remember how to do it? I think keyboards a little bit different here, so let me I think it's also control there we go. Okay, so now I have all that done ready to go and I want to work with my number formatting so on the pc side we have a group called number and then I have communally enough. I have this little dollar sign for me to work with and all I do is simply click on that and then it turns most of them into dollars with the exception of some of these other ones, these pound signs, what do we know about pound signs means that we need to auto fifth um so let's just go ahead and put our dollar signs on the rest of these first do that and then also just notice that on the window side I do have a drop down that I could actually do pounds like a new euros I could do chinese and I can also go down french I can also do mohr accounting format so these air just kind of like default that's the drop down and right in there and get back to the mac and see if that's available it was going to go ahead and click on the dollar signs and then I can see this serkan see this there and as you recall, hopefully that when we see these pound signs it means that the column is not big enough so now I can do an auto fit for more than one column at a time. Okay, so remember we learned how to do that. I don't want to have to do it individually so I know that if these four so simply just gonna click and drag going across just like that all four columns and then what am I gonna do? Double click, double click and then now they show so huge huge time saver all right and then finally let's go ahead and do our commission rate from point oh four to a percentage writes our percentage as we learned in seventh grade math right point zero four is actually if you promise me if you're planning to do percentage of any kind just so you know, you want to actually do it as a decimal you don't want to do if you typed in four and then you made a percentage, it'll come out to the four hundred percent you don't actually want to do that you want actually because percentages for one hundreds so just understand that all right and percentages right next to it lovely and then that's done just like that, okay? And then let me just jump over the max side of things just so we can have a fair shake of things right? And then let's come back to here, I will go into my numbering format and you going to see me zoom in a little bit? It could do this all the way across and see that and then it's a little bit different on the mac side you see that it's, not a dollar sign it's a little like looks like money just so you may have missed that that's. Why I wanted to come back to this to make it's very important you see here I could do a lot of the same options right there, and I do that and then the same idea same exactly happens click and drag that double click and then auto fit and then I'll go to my percentage and then again there's my percentage that looks great perfect and of course you can repeat rents as you see fit and that's it we are going to go into some a little bit deeper number formats in the next segment after lunch we're going to actually go into all that kind of stuff we're gonna go into alignment after lunch we're going to go into a lot more kind of sophistication and control over the layout okay? But this is a great foundation for us to stay with right now, so I'll leave it at that I appreciate that let's hear from our students because they've all been very intently looking down but I've seen a lot of nodding heads as well that's how is this election being for you is where any challenges it or anything that wasn't clear it's going to save me a lot of time in there? You show me a lot of details I'll be fast in six awesome appreciate that right now happy to do it amazing I think it's great to build something from scratch as well because a lot of people are asking at the beginning because always starting with spreadsheets that david preset so is great I think we could see in the chapters of people are really feeling it was great to start from the beginning so we talked a little bit about what we're going to get into after lunch you want to get in any more detail? Yes, we're going to go into mohr of formatting mohr of alignments were going again we're going to get mawr in terms of orientation and some page layout functions I'm going to introduce you to something called stiles also style is going to be very helpful to do like kind of preset formatting potentially create your own styles thinks like that she had to get a little preview of styles right there if you're on the mac side of things on the pc side of things you're going to see we have styles right here I'm going to say we'll be able to get into those so if you want to kind of get some refresher on that are kind of learn a little bit about it before before we finished before we come back from lunch, go ahead and do that on also just one thing I want to talk about in terms of creating new documents from scratch I want to talk about the fact that we are going to be doing towards the end working from the templates and the temple it's just fantastic that is really, really amazing so a lot of this stuff someone asked about inventory, some people were asking about invoicing and stuff like that excel gives you literally hundreds of different temple tissue can work with I'm going to show you how you can access them, how you can customize them and essentially, you know, just make them your own and then, you know, continue building off of those on any number of categories. So wait, I'm going to take a forty five minute break here in san francisco were going to grab some lunch, I'm sure wherever you are in the world, you're going to take a bio break up, but we got a lot to cover this softness looking for to that. Thank you for all your comments and free, but we always appreciate it, and we've got just a few I'm going to share with david this point. Jonathan sand photography. I hope you got that right. Jonatan is say dave has an awesome peaceful voice. This is very calming. It's a great way to learn don't panic. Great job, guys. Thank you for that. Johnson and fran from texas saying he had an r har moment this morning, he realized that you can create a timestamp by using the if statement hey, think of something really appreciative on jen emma saying she's been using itself for years for spreadsheets, but she's already learning so much today, so thank you gently, great feedback, thank you