2:30 pm - Working Between Programs
2:30 pm - Working Between Programs
26. 2:30 pm - Working Between Programs
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
2:30 pm - Working Between Programs
We are going to finish off big you're gonna love what you were gonna learn here we're gonna start off there's been a lot of questions about working with other program so in the last segment actually was yesterday we worked with charts and we actually worked with doing some beautiful charts and everything but then also shows you how you can bring those charts into a power point for example you might want to work and were like cat actually found that she could actually bring her power point your charts into a word and so if you're working with other programs you can do that particularly if you're working within an office program. So there's been a lot of questions around that the chat room particularly about printing as well so well how do we actually print them when we bring them in all right so I'll show you how to do all those things so what I have up here is last sessions human yesterday's day to session for actually has brought up just some data for us to work with here to be able t...
o create a chart and I was going to do a very simple quick chart just to show you how we can bring it into another program and how we can start working with those programs as well in terms of working with different objects from excel all right so you're going to see it's really really nice really straightforward and in addition to working with charts we worked earlier with smart are didn't wait someone we work with smart art, it's the same idea where you can work with the smart art and one program and also work with it in another program within the office realm. Okay, so let's, go do that, and then again, I'm going to show you how to work with the printing aspect as well. So this could be a little review for some of you maybe some of you missed the class yesterday, so this could be some good new information for you as well. So let's take a look. I'm just gonna highlight all my content right there. I'm gonna make into a quick, sharp go insert and we go to column chart and all that kind of stuff right there. K wham bam! I have a chart very quickly and very beautifully so at this point, all I need to do is simply copy. Alright that's simply all I'm doing right now just going copy this chart. So I'm going to do is write clique and we talked about right clicking a lot in this class, so we're going right click choose copy and now it's in my computer's clipboard so we call them when we copy something and we pace it it's in the clipboard so it's, they're saving the computer's kind of short term memory in its back pocket two people to put it elsewhere so where is that elsewhere in this case it's going to be inside a power point? So this is the sort of the remnants of what we did last time let me go ahead and just do a brand new one so let me just create a brand new power point here, so let me just change the layout to something blank, so I have that are ready to go so as you know, when I copy, I can also paste right that's the subsequent thing that we do here now when I write click, you're going to see I have certain paste options all right? And remember this either paste option to be able to do as destination themes and bed the workbook by the ability to keep source formatting and in bed I also have the option to do use destination and link data and then I can also keep source formatting and link data I can also just embedded as a picture, all right? So they give us a lot of different options. Now what do these options actually mean? So when we're talking about a destination theme in power point power point comes with themes built into it, and in this case I don't really have a theme because it's just a white playing background, but you do work with themes within power point that may have different colors, different fonts and in terms of the colors in our in our data bars, those also might I have certain sort of specifications for them, so that might be something that you'd want toe have it at here too, so everything is going to match was going to match the text is going to match the background all that kind of stuff. So that's, what we're talking about with the destination theme and betting just basically is sort of the counterpart tto linking all right, so linking essentially means that when I make a change on the spreadsheet initially, it's also going to make a change here is gonna give me the option to be able to refresh it. So we're going to see some examples of that all right, so there's that that's going to be on dh, then embedding essentially is not going to be linking, however is going to give us the option toe work with the original data, okay? And they'll come over here and this is going to say, use destination and link the data that's what I was talking about before so I think it's important now to understand linking before we understand embedding so we can see the difference, but I'm going to show you examples of both okay and then we have keep source formatting which is essentially the source where did it come from? It came from excel and I had all these colors associated with it I had all the formatting in terms of the fund and all that kind of stuff wade to come from a keep it so you have those options there so it's important to pay attention to all the things that is asking you but understand it's also linking the data all right, so these are all for kind of very similar in terms of the overlap just understanding is either keep the source formatting, keep the destination formatting or am betting or linking. Okay, so hopefully that's pretty clear when our final one is just you just say you want to import it as a picture. All right? So we'll be exploring all those different things. The picture parties you understand is just that we don't actually have the ability to treat it as as a kind of a live object anymore it just basically gonna be a static image let's go ahead and take a look first of all at embedding this workbook I click on that and this immediately comes in. All right now this is going to give me the option to do a lot of things like I could over an excel including re sizing, moving around, doing whatever I want to do you know, so this could be kind of neat if you had some bullets here or something like that that's going to be describing what the data is, and then you might have the chart over here on the right that reflects what the content is. So it's gonna be a nice wayto kind of interact with it, all right? So I could make this a little bit wider, it moving around like that, just like I said, but also notice that I now have chart tools, even though I'm in a presentation software program, I still have the ability to interact with this chart, okay, so I could actually go to here, change the design very easily and just make it make the change right here, even though I'm no longer in itself, I still have that option. So it's very, very cool the fact that you couldn't do that, because it's like kind of it's kind of reached across the aisle to be able to say, hey, you know what? Even though you're not in the program where you started from, I'm still gonna let you borrow some of the tools that are built into excel, okay, so we have all those options are gonna come back to the colors, and then I also have all the options for layout, so if you recall from yesterday we went into great detail on the charts so what I'm going to do just for right now to kind of maybe a little refresher is goto legend I'm going to say none just so I can have that right there and then maybe you'd have a little bit of notification there in terms of what you want to do within power point but what I want you to understand is the fact that not on ly can I work with all these things in terms of the design the layout the format but guess what I can come back to the original data and work with it ok and I do that by coming over your toe edit the data so notice when I move my mouse over to says show the data that this chart is based on very very clear to see that show the data that this chart is based on okay now notice what's next to this this is great out that's because we did not link this data we did not link this chart we embedded it okay so I'm going to now say edit this data and going to see what's gonna happen like magic now I now have both excel and power point side by side for me to work with here okay so I can make a change on this side so let's go ahead and give nancy drew a big jump let's just give her nine thousand and then you'll see how on the left hand side so actually let me bring back that legend all right? So we go ahead and just a legend I'm going to show the legend on the bottom so we can see who's who? All right, so there's nancy drew quarter one and I'll actually make a go a little bit bigger than that so you can see the big jump, okay, you see how she just jumped up just like that? All right? And it also adjusted even the axes in terms of their their measurements and everything. Okay, so you can see how that works. So when I'm done with it it's very, very simple. I just close this out. I don't have to save nothing because it's already linked to it and then make clothes that out and then they come back to this and then there we are, okay? And it's all right there and it's always gonna link back to it no matter what you do. Okay, now there has been a few questions about printing on dh printing is essentially if you print on power point if you print one word, there was no difference whatsoever because you don't have to set any special specifications or anything like that for working with a charge or another object or anything like that you're going to see you here I'm going to go file and print and you see here it shows me exactly how it looks on the slide deck itself. Okay, so really no difference there, okay, so if you do you have any more specific questions? I know there was some some questions in the chat room, so I'm not covering exactly what you're asking about certainly post them on the chat room s o I can so I can answer them directly, but as faras printing goes, you have all the same options as you normally would within power point with inward and also in excel okay, so now then let me go ahead and now do a different kind of pacing all right or a different kind of importing into this I'm going to do another copy and paste into here, so let me go ahead and pace listen to hear me do that I'm going right click and then actually let me undo that I'm going to go back into excel and go back into my charts and do much art went all over again, start from scratch here and, um and do this one all right, so pretty much the same thing now, but when I paste this I'm going to do a linking all right, I'm gonna do a linking so good again copy come back over to power point and again, right, click and have all these options and this time I'm going to choose instead of embedding, which were these two I'm going to say, um, used destination theme and link or you source destiny is you seduced me, used source formatting and like either one is going to be the same. Because I don't not have any theme associated with this again. If you did have a theme, it may change the color. If you decide to choose the destination theme power point is the destination. In this case, excel is the source. So if that's not clear, hopefully it is now. So now I'm gonna go ahead and now say use destination theme and link the data. Okay, so I'm gonna click on that now that comes in, and really not a whole lot of difference in this case. All right, that's. Great. You could do that, etcetera, etcetera. And then I go over to my design and notice. Now I have this refresh data. So what this is talking about is if I make a change inside of my excel file it's going to refresh it because it's now linked up to this. Okay, so let me actually get rid of this so there's not any confusion in this one. Okay, so let me make sure that this is this is still here even though I deleted it on the excel file it's still here because I copied it all right now let's go ahead and we're going to give this time we'll give sean connery who's in quarter one ah, big jump. Okay, so we'll make him go up to fifteen thousand so we can really see the difference here, okay? And I come back to here and then you'll notice sean connery automatically has this big jump. Totally different file. Okay? And it just happens automatically. All right, let's do it again with two sean connery for fourth quarters. You can see it. Come back here and look at sean connery. All right, let's, let's give him twenty thousand. Ok, because this is linked. I'll see there. Sean connery right there. Big twenty thousand ok, so you see how that works? All right? Because we've linked the data. All right? Now let's do that one more time. And this time I want to show you how we can do this, um, working with as an image because that might be something you might want to do, and I'm going to kind of take it, take it a step further than before, so again, I'm just gonna copy this come back into power point to do a new slide and this time I'm going to do it as an image you see that they're ours a picture and notice when I do it is a picture I get picture tools okay? And that actually could be something you want to do because now as opposed to yesterday, we don't really know too much about our picture options, but now we actually know how to delve into this a little bit more so we could do some or interesting things with this we could actually come in and maybe work with some of artistic effects and do some cool stuff with our charts. Then when we went to filters, see now I get all these cool things that I could actually work with because it's a picture as opposed to an actual chart so that's kind of neat I could do that kind of thing I can put a different kind of frame and border around it if I want to see that because it's a picture, it gives us a lot more kind of artistic type of options to work with as opposed to working with andi actual chart and actually changing the data and everything like that. Okay, so I'm gonna go ahead and just choose that and I might want to do some other effects on here like I could do ah reflection of some kind and then you've got something cool like that you got your chart and then you've got a little reflection of your chart down below so that's really cool so when you have an image it's going to allow you to do that kind of thing pretty simply okay so again the question about printing let's make sure we have everything here and you see it looks exactly like how I have it on my deck inside of power point okay for any of this is no reason for you to be connected to the internet is that this is all happening within that because running kidney stones was great name was thinking that's something you had to actually do but you don't need to be connected you know I needed for this part you do not need to be connected to the internet when we start talking about our our templates you will be connected to the internet okay home assuming that everybody is connected to the internet because they're watching this right so wayne ever know the magic of television ok right that's the future okay wireless wireless okay so now the other thing I want to show you can addition toe all these formatting tools that we can do and we already learned all about all these things and all the amazing things you could do with all this for mounting stuff is that you may want to actually save this as a kn image itself outside of this you can work with it on so many other levels, okay? And that's what's so cool about it working within power point or word if you have an image it's an image file on, we just created this image file out of a beautiful chart we can then make it as another kind of image file. So how am I going to do that? I'm just going to simply right click on the image itself and guess what? Save as picture comes up for this and this isn't just like a picture, you know, that's only going to be just for power point or just for microsoft or whatever it's going to be. It could be a picture that confused anywhere in a newsletter it could be worked into photo shop into indesign into your website. Anything like that. So I'm going to do now is say, say that picture and this comes up save as type png jpeg gift if any kind of image file you khun do all right, so I'm going to go ahead and say png and then I was going to say chart sales twenty thirteen all right? And then I'm just going to say it to my desktop, so I find it nice and easily say, save and it's come to my desktop. Okay. And then there it is. Chart sales it's a png and I opened it up and bam! There it is. And not only does it have the chart, it has the box I created for it and has a little reflection. Everything's so the whole thing gets changed. So this is one of those things that you know again, if you don't know graphic design software programs, you can still create riel graphic design effects by using excel and or power point in this case, because all those things that I just did in power point exist exactly the same inside of powerpoint and excel. Okay, you see, like, this little reflection, okay, you can do that. And that is very, very cool. So so that's that's bringing this stuff in here. So there's a lot a lot you could do on top of all this, just working with all these bringing in shards, bring in, you know, anything else they talked about? Smart aren't the same way and it's going to bring in all those other options for you to work with. All right? And then it's going to remember that this is smart art, and because this is all part of the same dna it's gonna allow you to edit the smart art just the same okay, so let's step over to the max side and we'll just see how that's gonna work for us as well and let me just have a power point open on this side all right? So I'm ready to go and you'll see how it's going to exist just the same way so I'm gonna highlight my content go to chart go two columns all right? We'll choose that bello there and then right click copy go back to power point and then I'm just going to make it so this is just gonna be a blank and I right click and then notice here I'd have different kind of options here, but if I gotta pay special is going to ask me a few different things here, okay? So I could do it as a drawing object I could do it as a microsoft excel chart object let me see here actually because I'm going to go out and pace and see if it gives me paste options. Yeah that's actually going to be the way that you want to do it you want actually pace it in and then you want to choose from the paste options dialogue because it's not the same on on the map where you have when you right click and you see all those options you see the option after you pace it in to click on this guy when I gave a little bit of warning last time, tio always pay attention to these guys and I've actually given you a couple of warnings about the auto fill and also the paste options, the auto fill options and the paste options. This is a good, friendly reminder to remember toe always pay attention it's there to help you so becomes in not the way that you want. Always click on your extra options here to see what we can do here. So this is chart ling to excel. All right, so excelled chart entire workbook just the picture of the sharp keep the source formatting so there's a lot of overlap here. So now when I have this here, I can resize it. I go to my chart layout, and I go to my charts right here and let me actually make this a little bit bigger and notice here is edit, right? So I cannot edit this this content right here. Click on that and it takes me right to excel to be able to make the changes to it. And then we picked on this time. Okay, let's do showing again since he's kind of a little bit of lower number let's make him go to fifteen thousand, all right, and then we go back to power point, and there is a fifteen thousand okay, so same thing a little bit different in pc side. It kind of splitting up side by side, but it's still there, it's still doing the same exact thing? So if you have a lot of data that's constantly changing and you know that you want that data to be shown someplace else visually, but other people are changing it just copy and paste, but link it so you do not have to worry about whether you're up to date with it because it's always going to be up to date and excel is going to be aware of that, and power point is going to be aware of it case you're going to have all that stuff just live constantly consul talking to each other, right? So I really need you could do the chef's cat. I wonder if I have saved one of these documents in my dropbox accounts say the numbers for the month of december in warner creek and my clients have the link to my dropbox document on if I make the changes, it would automatically update there too. That feels I don't know if it's gonna work that way because that's going most likely be online. So when this is going to save in the drop box sorry it does saving my drop box but the link remains the same well if it saves a you're sharing the same dropbox and then it goes into their dropbox and they're linking to that drop box then it that yes that it will work because if you say that your dropbox yeah and that all my players in their drop box so you're linking to the link to the link absolutely so then it should work but if they have to re download it then it won't work so just understand that if it's there and their drop box on their computer that orc because then they just click on the link once in a while then I'll have to do is change the data and they see the new charts on and then you're saving in the drop box it's going through the cloud through their computer and right into the file yeah that's that's really a great idea of way to think about that actually using the cloud computing if you're working with clients and then just have it go right up to the cloud which is in the drop box and then come right back down to them yeah it's great so has really really slick how this works I mean all these machinations behind the scene allowing our lives to be much easier and also just free of kind of mistakes and free of having to do more work late iran, because it's all just happening seamlessly because everything's connected so great that's that's a great, great use for it. Okay, very good. Now let me just talk about one other thing here there's going to be times and this this may be not something you could be doing a lot, but I want to just cousins were on this topic of kind of linking some stuff up here, and it may not be exactly what you want to do, but I wouldn't want to kind of bring it up here. There may be times when you want to insert what's known as an object, right and there's lots of different objects we can we can bring in, so if I go to insert and then I come down here to object, you see there's a lot of different objects you can bring in, and most of the time you're going to do an object from a file and not a brand new one, they have all these options here, but I still have not found a use for working with these, because typically you're gonna quit. You're going to insert something that already exists, so you might want to insert an excel file. This way a lot of times you can certainly copy and paste there may be times you want to just insert from a file. So if I click on let's, just go right, tio any of the ones who were working on today and I can bring in let's actually go back to the one yesterday? Well, actually work on this chart one. Okay, so I have this day to session foreign charts. It's gonna allow me to insert this one. Okay, now, it's already in there, you see it there, and I'm not going to displays an icon. I could if I wanted to know where it's going to be a link that gets to it, but I'm going to simply click okay? And there it iss it comes in just as an object itself, right? Seems like, packaged right in there exactly how I how I created it to see that. So I don't have to worry about kind of like some, possibly like weird formatting in terms of, you know, copy and pacing and doing at such itjust comes in as an object right here, okay, so when I double click it, which I just did, it takes me back to the original from able to be ableto mess with it, so if you're working with you know a sheet as I am right here it just brings it right in for me to work with you see that I can make the changes and it'll change it right here accordingly they want on the pc side it's similar that however we just have a different way that we get to it when we go to insert and then we have object on this side and you see it's the same exact thing where we say create new or create from file and then we're gonna have we've have ability to browse and then we come right to it we'll go to day two will do charts again all right and then I'm going to choose a link on the piece on the max out they don't have the link but I think it's automatically linked on the max side I'm going to say ok and that comes in just like that and I can resize this everything whatever changes I make on the spreadsheet it's going to change here so what I've been showing up until this point was working with the chart copying the chart and then pacing and as either ah link or embedding it but this is going to allow us to work with this type of thing with spreadsheet itself because they're going to time you're gonna want to do that inside of power point and potentially even side of word okay so hopefully that's that's helpful for you okay, so any questions coming from any place out on the planet about this? Yes, well, we did have a question come from both jim and jesse l we weren't sure if we're going to touch on it later, not about embedding an excel chart into a blogged, and if that will work, or if that would be something where it would just be a static image exactly it would. It would just be a static image. Yeah, it would be a static image to do that. If it was on line somehow and you had a a link to it, then you could actually do an embed into that thing, but it has to be on a server someplace for that to work. Otherwise you'd actually want to have is an image file. Okay, then just bring it in, but it wouldn't be static. It wouldn't be. It would be a dynamic thing where you could actually work with you to be a static image. Okay, now they're importing some of these chance into power point. Presumably, you might want them having a show, so you'd want to see these columns growing, huh? Is that something you have to do? An xl was something to do. Once they're in power, you have to do that in power. You do, and you can do that, so maybe that will be a future class for us, but you can totally do that, and it looks very cool where you can have each of these things come in one by one, and they look very, very cool and it's a very powerful way to display your information. There's a pattern, I think that's a power point thing, right? You can't do animation inside of excel, but you can do it inside of you, your animations right in here, making do their thing.
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.