Microsoft Excel Tutorial - Beginners Level 3

hi there jamie keate here today at teachers tech hope you're having a great day today today i'm continuing on with my excel for beginner tutorial so this is number three in this series i have number one number two i'll put that down below in the description if you want to check that out if you're just starting from scratch this one continues on with more features that i'm going and looking at it from a beginner's point of view so let's get started here today with this excel for beginners level three today on teachers tech so just before we get started i just wanted to point out down below in the description i'll have everything listed in this video and it will be time stamped where you can just click quickly click on the number and we'll jump to that part of the video if you're looking for something specific uh and i'll also have other video videos to other tutorials there such things as vlookup pivot tables and other ones uh looking at it from a beginner point of view so today we're going to start with looking at the copy and paste features so this might sound simple but i want to make sure that you understand all the tips and tricks of using this feature in the pace special to get more out of it and to kind of speed things up with some tips and

tricks along the way so i have some information in a table here just your basic you know monday through sunday sales i have a basic formula if you look into the formula bar right up here is just multiplying the units with the average price to get a revenue for that day now i'm going to make a copy of this little table here just by selecting it right clicking and i'm going to hit copy and you could also hit control c on your keyboard on your mac it would be command c but i'm going to go ahead and hit copy if i go ahead and just move it over a couple spots i'm going to right click and i'm going to go to paste and you could go ctrl v on your windows or command v on your mac to paste it so that's your basic paste now the issue is this works fine and everything the issue is you're going to have to do a little bit of formatting to make it look right the colors came across but the column widths aren't the right size and i know this isn't a big deal to fix on this and we can with a double click up here in between the columns start to adjust or different ones or if we wanted to highlight everything and just double click in between any of these it's going to adjust everything to make it fit just like that so not that big of a deal to adjust it but i want to show you some other ways that we can copy pasting looking at all the different functions so if i have this again if i go ahead and right click and i've already copied it but i'm going to copy it again and i'm just going to go back over here and right click and i want you to take a look at all the different paste options under the right click now these aren't all the paste options either i'm going to show you where you can find more in a few moments but so this was your basic one that we already did you can see there's values if you didn't want the formatting from something so if you're copying and pasting it from a different sheet and you didn't want to have to go through and take off the formatting you could save yourself a step just like that this is going to do the formula so it's not going to change anything you notice the formatting isn't there this one i really like so if

i go ahead and hit transpose i take a look at this so what it did was and i'm going to adjust this real quickly so we can see everything so with transpose it took my monday through sunday and put it across the top so i've taken where my rows were and turned them into the columns just by that simple copy and paste and this one i that feature a lot of times people overlook it and it's a really easy thing to do so i'm just going to go back a step again and looking at our copy paste features here so as i go through formatting is just here so like for an example if i went to the values i could actually go after and decide while i want the formatting i could click in here you can see if i go to formatting it puts the colors back and the lines back around everything so those are some of the basic features of the copy paste the other place i wanted to point out if you go up to paste here you can see a few more options so as i go through i'm just going to hover over and you can see form formulas and numbers formatting keeping source formatting uh no borders if we didn't want any borders around keep source column width now this one i showed you at the beginning uh if you're copying pasting and you don't have to adjust how i adjust adjusted and again might not take a long time but if you could go keep source with the column widths and then that's the transpose one i've showed you before so very quick to take advantage of those ones now some other things that you can do with uh copy and pasting i'm just going to go i'm i'm doing this one i'm just using ctrl z to undo i could go ahead and just grab

these numbers here and let's say i'm going to copy this like this and i have a different sheet here and i'm going to go to this and you notice there's no revenue here if i right click and paste it actually took the formula so it i copied the formula over and it just brought it right in here i have different numbers i could adjust it just like that so the other thing you can do though i could go back to sheet one i could just copy everything from oops from the top here just like this and go to sheet 2 and paste it in just like this and you can see how it goes in but if i wanted to skip the step and make sure those number or everything is the right width i could go up top here and if i paste it here keep formatting width everything fits automatically the formula copied over and it's working with these numbers here across so not only can i copy uh the whole tables and different parts of it the formula can be moved and it will work when i bring it over and as long as it aligns with how this did with the right cells uh how the formula are how the how it was set up so the next tip i want to show you is pasting a link so i'm just gonna go ahead and on the sheet one i'm going to highlight this part right here just the revenue right click copy i'm going to go to sheet 2

and i'm going to just right click and this time i'm going to paste link here so go ahead i'm going to click on that i'm going to format just like i've showed you before i can go through and add my formatting you can see as i change and we have my formatting uh so what it does now this is linked so let's go to this bottom one where it says 540 dollars if i go back to sheet 1 and let's change this maybe we missed some sales and i'm going to make this a lot higher turn it to 44 and now it went to 1134. if i go to sheet 2 this is reflected so it was automatically changes so you can copy and it links so as long as you paste it as that link option it will link through now another thing you can do if i go back to sheet 1 you might maybe want this on multiple sheets so and you don't want to have to go to each one and paste it on every one at a time you can actually select multiple sheets to paste it on so if i go ahead i'm just going to write to select this right click and copy and now let's say if i wanted if i had you can see i have sheet four if i hold shift down and keep holding it down on my computer i can select multiple sheets now what i'm going to do is if i right click and paste it i'm going to just do my normal paste and you can see it goes through and if i go to sheet 5 it's on it 6 it's on it put it in that exact same place on all those sheets so it's a quick way to go so you don't have to paste it one at a time on every sheet you can do that way so the last thing i want to show you with copy and paste i'm going to move to a different sheet number three here and that's with

the paste special now let's say this was the price of my different widgets that i have here and i could say well these are the amounts i need to increase the price up by this one's going to go up by two dollars one dollar or this is a percentage increase i'm going to just take this and select it and copy and i'm going to go over here this time right click i'm going to go to paste special and you can find these up top too so if i go up you can see paste special you're going to get to the same one but i'm going to click space paste special and on this one i want to add this amount to it so i'm going to click add and i'm going to paste all you can see how you can kind of go through and select what departs if you want from formulas values i'm going to hit ok and it gave me the new price so it increased the prices by what i said and you can also do this by percentages by multiply so if i had a percentage that it was going to increase by i could right click and then i could go ahead and go back to uh paste special again this time i'll multiply and hit ok and the price went up by those up that percentage increase there so that's under the pace special so take advantage of that paste option when you're working in excel it can save you a lot of time and making sure you choose the correct the paste option to save you those steps

in my previous tutorial talked about autofill so if you want to learn about autofill go check that out in number two tutorial in this one i want to talk about flash fill so flash fill is a great way to pull information from columns to combine things together and what i'm going to do is to quickly show you an example of it i just have some names here you can see just some names put together and i have years worked and i'll show you an example right here i'm going to type the first person's name into one spot here so just like this now if i go down and start typing in the second one notice it's starting to populate if i just hit enter it populates it and found the pattern that i was looking for and it automatically just filled in everything down it so then i could go adjust my columns and you can see everything just pulled it and put together in one cell i'm just going to hit ctrl z uh and you can change this easily too so if also now i wanted maybe just the first and last name i'll show you a different way you can do this if i was going to pull this down this corner just where that little black you can see that little corner that little dark square there if i pull it down and you think oh i just copied down the whole way but what you do is if you click on this you can go to flash fill just like that and it changes it now another way and again i'm just going to click go ctrl z when you're in this what you can do if you just hit control e so ctrl e it does it that's the shortcut for flash fill and it puts it right down through it now you can adjust this too so if i don't want uh i'm just going to delete this again maybe i just want the first initial so if i just hit j scott like this and i'll hit control e and go down i'll click in click out control e it knows to take the first letter off and then put it with the last name so i can start to combine it so even with the year's work i'm going to go back again on this one if i wanted to put the number 5 in there it knows that it's going to pull so i'll do this a different way this time change it back to flash fill and you can see how it adjusts it will even pull those numbers in so it's a very quick way to combine your different columns and put it into one cell and i'm going to show you a few more things you can do with this okay so now i'm going to show you a

couple different ways that you can use this flash fill that might come in handy for you and i just want to note you need to be in the column right beside it to have it work so if i was going to do flash fill here and i'll give you an example if i was just going to type uh my this name in here and notice as i try to i'll go down and i'll go to flash fill it won't work because you need to be in uh the one right beside it here so just to let just if it's not working just to let you know that now let's say i'm going to make an email list here and what i'm gonna do is i know it's first name dot last name and then the company's email so i could type in uh jamie. scott here and i'll say i'm gonna say it's at teachers. tech and so if i do it what i've done before i could use my control make sure i'm in the right cell here i could drag down i could hit control e and it brings it excuse me all the way down i remember the different ways to fill it in but ctrl e what is i like to use that one because it's so quick we have it right here we can adjust and we put in the uh the first name dot last name for it you can use this in numbers so maybe you get phone numbers and they come in like this and they're hard to read you could format one of them using the flash fill so for example if we were just going to uh just put the numbers in here like this one making sure i can read it because it's so hard to read just like it is and i'm going to add some spacing in here remember if i don't get it right it's going to ask for some more information so this time i'm just going to go and pull this down and click on flash fill and it moves it all across so i've reformatted those four not phone numbers and it didn't take very long all i have to do is get the first one sometimes what happens when using flash fill as you go down it might ask you to clarify uh because they're looking for a pattern and if the pattern if they haven't made sense of the pattern they might ask you've got to go through the to the next one and then it will try to get us to connect to the pattern that you want it to so i hope this will come in handy this flash fill i know this is something that saves me a lot of time if i'm working in excel i don't have to duplicate things or pull information out of the info the uh rows and columns that i already have let's move on to our next topic now in this tutorial

okay so what we're going to do now is talk about links and a few different types of links we're going to link from a worksheet out to the web address that when you click on it opens it up and also inside a workbook with all the different worksheets that you have where you could almost have a directory or table of contents and be able to go from one sheet back to maybe a directory one where you could find the different uh maybe it's a chart or something that you want so i'll try to help organize show you how to do that with uh with the different links now what we're gonna do first is just a simple website link and i'm just to use my website as an example here i wrote teachers tech here i'll just build it so you can see it a little bit more and then i'm going to right click on this and i'm going to go to link right here so when i click on link you get this insert hyperlink window open i'm just going to hit cancel a shortcut to this is control k so command k on a mac but control k will open up this so whenever in inside excel you're going to find this in a few different locations when you have c insert hyperlink this is what's going to open up now if i want to open go to a website i want to make sure that it's under this right here linked to existing file or web page i'm just going to put my address in there for my website i'm going to hit ok and you can see right away that now this has become a link and if i go ahead and click on it it's going to open up my website here as you can see so it's just links it that easily now i can use objects to do this too or pictures and i can turn those into links inside the document so what i mean by that if i go to insert and i'll just use shapes here for an example this heart and i'm going to draw the heart right here and now if i right click on it you'll see the link one right there or i could use ctrl k and i have that option again i can put in my address hit ok and i click on it and this is you can see that it's going to where it's going to go to so as i click on it it's going to open up my website and i can see it's twirling there it goes and it opens up from there now another thing you can do if you wanted to you could put pictures or you could take screenshots so but before i do that screenshot i'm just going to change this for a second because i'm going to take a screenshot just of this teacher's tech thing right here so what i'm going to do i'll go back now to my illustrations and screenshot and click on it i'm going to say screen clipping and what i'm gonna do is just use this right here so i took a screen clipping of my website over here and i brought this in so now i could go through and link it just like i did before and so you can quickly paste it in and this becomes a link here that you can easily get the pictures from you can see how it's the link alright so we'll go back to full screen now so that's just linking to the web i'm going to delete uh so if i keep clicking on these it'll keep opening and so what i'm going to do actually if i right click on any of these you can see i can remove links if i want or edit any of these too so i'll leave it like that so a good way that you could help organize your workbook with all the worksheets inside could be a directory or table of contents maybe on the front page where they know they can click on a link and we'll take them to the exact worksheet maybe with a chart or graph on it or maybe just a

table that you can quickly access so i want to show you how you could set that up now i just have you can see i just wrote director here just as an example and i have worksheet one two three four if i was going to want to be able to click on worksheet one and go to sheet one down below what i do is i could use my shortcut ctrl k like i've showed you before but this time you need instead of this place existing file or web page go to place in the document and you'd want this to go to sheet 1 so we hit ok and you can see the link is there and i click on it and it went to this spot you can see where it's highlighted and i'll show you where a moment uh where i uh where that's selected so you can see brought me right to here i could click back on directory again and i'll show you how to add a shortcut so it's easy to get back to directory all the time too now if we go to uh worksheet two actually i'll go to three and do the same thing right click and we can go to our link or use the shortcut you can see it says type the cell reference so this is where it says it's going to go to a1 and that's why that was highlighted that cell when i went over there but we could say a3 and i'll just do this as an example i'll just put a3 in here sheet three hit okay if i click on worksheet and you can see we're in a3 highlighted so that's why it's just jumped to that so if there was a part if it had a lot of information on that worksheet and you wanted to jump to a certain spot you could select where you'd want it to go now another thing that you could do you could go to a named range that you've created so let's say i go to um we'll go to rate here our worksheet two and i'm going to make this i'm going to name this and how we do this is so i'm going to highlight this right here and i'm going to go up to this and i'm just going to call this revenue just like this and hit enter and now i've named this so i've highlighted all the i don't have any spaces in this i went from uh the top of this uh of this table to the other side with all the information and now i have that as a named area by typing it in there and i just hit enter on it so if i had different areas defined i could drop down and see them but if i go to back to my directory and say in worksheet two and i right click and i go to link and this time notice under defined names i have revenue so i hit ok and i go there and now i can go click on it and it brings me to whoops i went to the wrong one there i'll go back and click worksheet 2 and it you can see that it's brought me right to this one so define your areas within a worksheet and you can jump right to those spots too now remember what i said like you might want to have something that brings you back to your directory all the time so you might have something placed in here at the top where it all like a website it brings you back to home so if i was going to click here i could right click and go to my link and where do i want it to go maybe back to the directory and hit ok and so now if i click on it brings me back to here now maybe you don't want to have that on every you want that you don't have to go through and do that on every page remember when we talked about the copying we could right click on this run copy and i could select multiple sheets like i showed you before and i'm just going to go ctrl v and now as i click on each of the sheets it's there so i have this home button that always brings me back to my front page on my directory and remember if you didn't want it to be a just a cell you could add a graphic maybe you go and insert a shape or something that will say it on there and then you hyperlink it back and then you can copy it through it makes an easy way to copy and get back to the directory so if your workbook and all the worksheets uh in it you're getting a lot of them and you just want to have a way to kind of help organize things where you have that table of contents or directory on the first page and you can maybe write little notes and people will know what to go through go to just from that first page so i hope you like this tip so this is the end to my tutorial number three for my excel beginners i will make a number four a lot of times i just make it from what i hear what people are asking for in the comments then i try to put together another i'll put together a number four video uh kind of trying to explain the points that people are asking for but i'll also come out with specific videos about specific topics just not necessarily uh tutorials with many different things in it so i hope this uh helps you out you find it helpful let me know in the comments down below what you're looking for in different ways i could create videos to help you get into excel more thanks for watching this week in teachers tech i'll see you next time with more tech tips and tutorials