Description of the video:
[April] Hello everybody. Good afternoon and welcome to Getting started with Microsoft Excel. My name is April Law and I'm going to be instructing today. Can everybody hear me? Is there somebody there that can tell me? Jen or Jason? Ok. Lots of people. Great! Thank you. It's always a little bit disconcerting to start this and be talking and not really know if people can hear me. Again, my name is April law. My dog just walked up here next to me to get some pets while I'm talking. I work for IT Training at Indiana University Bloomington. I will going to be walking you through Getting started with Microsoft Excel. I have some of my colleagues here with me, Jason, Rachel, and Jen. They are here to watch the chat to see if you have questions. If you have questions, they will either interrupt me and have me answer the questions to the group or they will answer your questions to you in the chat. So feel free to use the chat to talk with us today. So in this session, this is Getting started with Microsoft Excel. I'm going to show you some very basic things in Microsoft Excel. We're going to enter some data. I'm going to do some formulas and some functions. And then at the end of the half-hour, we're going to show you all how to get enrolled in our online courses. How to find the course you want, get enrolled in that and get started. So, oh, the other thing I need to tell you today, I am going to be teaching in demonstration mode, which, which means I will be doing the steps on my computer and you just watch on yours. And in the follow-up email you will get, you will have the file that I'm using so that you have a file to practice with a little later. But for the next 20 minutes or so, you're just going to follow. Watch what I'm doing on my screen. Alright, so I'm going to stop this share. And I'm going to start with a new share. So here is my Excel workbook. I'm going to zoom in on this a bit so that you can see a little better no matter the size of the screen you are watching on. So what I have here is my Excel workbook. A workbook in Excel is typically made up of worksheets. The workbook, in this case is... Where are my tools? Oh, there they are. The workbook. Sorry. I've gotta get my ann-my I opened my annotating tools and they locked my workbook. The workbook is called Getting Started with Excel .xlsx. The workbook is made up of worksheets. The worksheets are down here. This one, this workbook happens to have two worksheets. One is called fundraiser sales and one is called summer reading. A workbook can have as many worksheets as you want it to have. There's no limit in Excel, although a workbook with several 100 worksheets is probably not going to be very easy to use. So you, you might want to put your own limits on that, but Excel does not put a limit on how many workbooks, or worksheets, you can have in a workbook. A worksheet is made up of rows and columns. The columns are lettered across the top and the rows are numbered down the sides. And the intersection of any row and column is called a cell. Little rectangles are all cells. And I can move around, I can click, I can use my keyboard. You can't see me using the keyboard, but that's what I'm doing to move around in my workbook. So the intersection of any row column is a cell, and each cell has a unique cell address. The cell address comes from the column number, or the column letter, and the row number. So this cell, my active cell here in green is cell D2. It's in column d, row two. We would write that this way. D2. Okay. Excel also puts the cell address in the name box. The box is right here. So at anytime, if you have a large worksheet and your active cell is somewhere that you cannot see it. You can check that name box and it will tell you where your active cell is. In this case, cell D2. A selection of cells. If I have my active cell here in cell D2, and I select all the way over here. This is called a range of cells. And a range of cells is named by the upper-left corner cell address and the lower-right corner cell address. So in this case, this range of cells would be D2 to... What was it? F5, I think is what that was. So D2:F5. And that's a way to describe a range of cells. Yep, D2 to F5. When I do those annotating tools in zoom, it takes off what I have selected in Excel. So that's what's happening there. Soon as I put on that annotate tool to type something out, it overwrites what I have selected, in Excel, which is a little frustrating, but that's why it keeps disappearing. Excel does not care about these capitals. So to excel, D2 to F5 with capitals is the same thing as this. Excel doesn't care. You can type it either way, whichever way is easier for you to type and easier for you to look at. It won't matter. Also, the other thing we need to look at. Let me clear all of these out and get my selected stuff back here. The formula bar in our workbook. Can de-select there. And the formula bar is right here. The formula bar tells you what Excel is seeing and doing, kind of behind the scenes of your data and your Excel workbook. So if I click on cell a2, I see the word Tote bag in a2 and up here in my formula bar I see what Excel is working with. And Excel is working with the text Tote bag. If I click in cell c2, I see the number 27. I look in the formula bar and Excel is working with the number 27 there. If I go over to b2, I see, in cell B2, 15 dollars. Excel sees, in the formula bar, the number 15. Excel does not care about the dollars. That means nothing to Excel. The dollar sign is there for us, because that makes sense. That makes that number make sense to us and gives us more information about the number. But if Excel is going to do math with $15, it's the same as doing math with 15. So Excel doesn't need to know about the dollars. So what you see in the formula bar is what Excel has going on in the background, same with any kind of formatting applied. If I go to cell b1, I see item price and it is bold. I can see up here in my ribbon that the bold button as active, but in the formula bar, it just says Item Price because excel doesn't care about that formatting. Alright, so that's what the formula bar does. It gives us the idea of what Excel is seeing behind the scenes. Let me remove that annotation and go back. So Excel has a lot of uses. One of the things Excel does very well is it helps you collect data, right? So we could have a list of names and addresses, we could have grades, a class list, we can have inventory, we could have a list of holiday cards we want to send where we have the addresses of our friends and family and we check off the box when we've sent them a holiday card. So Excel does data collection and data organizing, and data analysis very well. Excel also does math very well. So if we have large amounts of research data where we have to run really complicated calculations. We can do that in Excel. Excel does really complicated calculations. Excel also does very simple calculations like the ones we're going to see in this worksheet. But Excel can do both. It can do the calculating for you if you need it to. But you don't have to, you can just keep your names and addresses in Excel and it will keep them very organized and help you analyze that kind of data as well. When Excel is working with math, with formulas or functions, you always have to begin a formula or a function with the =. The equal sign is the indicator to Excel that you are expecting Excel to do some sort of math, whether it's simple math or complicated math. The equal sign tells Excel, oh, hey, it's time to, to do something here, right? That's, that's the symbol for that. Within a formula or a function, we use pretty much the standard mathematical operators. We use the + for addition, the - for subtraction. Multiplication is the *, which is above the eight on your keyboard, shift eight. And division is the /, right? So we can use these mathematical operators and lots of different combinations with lots of different groupings of numbers and cell references and parentheses all across and get Excel to do just about anything we want to do mathematically. Today we're going to work on some fairly simple calculations because this is the Getting started in Excel webinar. So we are going to clear all my stuff. We're going to calculate the item sales here. Right? So we see that the item price for this tote bag is $15, and we see that we sold 27 of those, right? So I could type, if I wanted to. Where's my? I could type equal sign because you always start of a formula with the equal sign. I could type 15 times 27, right? I could type that and I would get the right answer, but the power of Excel comes from not using the numbers that are in the cell, but using the cell references. So instead of typing 15 times 27, I can type b2 times c2 and Excel will multiply whatever is in cell B2 by whatever is in cell C2 and Excel doesn't care what those are. As long as their numbers, Excel will do that multiplication. And then if something changes and you realize that you want the price of the tote bag to go up, you can simply change the value in cell B2 and it will adjust all of the calculations. So let's give this a try. I'm going to click in cell d2, and I'm going to type equal sign b2 times c2. As I type Excel color codes those for me, gives me a visual indicator of, Oh, look, that's exactly what I wanted it to do. That's really helpful if you mistype and you see your color coding is way over off to the side and not at all what you wanted. So it gives you this visual indicator. I have that in there. I press enter and I see that I sold $405 worth of tote bags. Right. And if I go back up to cell D2, i see my answer in the cell, but I see the formula in the formula bar. So my eyes want to see the $405. Excel sees b2 times c2. So if I do decide, oh hey, you know, those tote bags, really, I need to sell those for more than $15. Let's say I change the price, of my tote bags to $25. So I go to b2, I type 25, I press enter, and it automatically updates my item sales because Excel is just multiplying whatever is in b2 by whatever is in c2. It doesn't care what those values are. It's going to do that multiplication. So I can go down a row into d3, and I can put that same formula in, But this time I want to use b3 and c3. So equal sign b3 times c3 and enter. Oh, I didn't put my equal sign. Equals sign b3 times c3, enter. $456 on coffee cups, that is a lot of coffee cups. So we can see in the formula bar b3 times c3. Now I could continue down column D and put that formula in at every row. I could go to row four and type equal sign b4 times c4 and so on and so on. We could do that. And it wouldn't be that big of a deal with only five items. But if you have a worksheet that has hundreds of items or thousands of items, you are not going to want to do that formula individually on every row. So Excel has given us what they called the autofill handle. And when my active cell is here and cell d3, there is a tiny little green square in the lower right corner of that active cell. That is the autofill handle. And if I put my mouse over it, I get that tiny black crosshairs. And I can use that to press and drag down and it fills in all of the other rows for me. Right? So I started out with b2 times c2, b3 times c3, and then I copied and it filled in the b4 times c4, b5 times c5, and b6 times c6. This is what Excel refers to as relative cell addresses. So the cell address in the formula is relative to the row that it's in. And Excel is, is usually smart enough to figure that out for us, right? It says, oh, you put in b2 times c2 when you are on row two, you go down a row you probably want those references to go down a row. And most of the time we do, right? Most of the time, it's exactly what we want, sometimes it's not exactly what we want. And you have to force Excel to create a relative cell reference. We're not going to do that today in this intro class. But the Getting started, or not Getting started, I'm sorry, the Excel: The Basics course that this 30 minute webinar is based on does get into that. So if you go in and you register for Excel: The Basics, it will show you how to force a cell to be a, a absolute cell reference. Okay, so we see our simple formulas here. Let's do a couple more. Let's see how many items we sold here in row c or column c. So if I click there in c7, and I want to know how many items we sold. I'm going to start with the equal sign and I'm going to add up all of these. I'm going to c2 plus c3, plus c4, plus c5, plus c6, right? And press enter. 148 items sold. I go up into cell c7, I could see the 148. And then take a look at the formula bar c2 plus c3, plus c4, plus c5, and so on. So this works right? I could definitely type this in again though if I had several 100 columns or several 100 rows, that will be a lot of typing and a lot of possibility for error. If I was typing c3 2 plus c3 plus c4, all the way down to plus c500, there's a really good chance I'm gonna mess something up there. Right? And in Excel and really anywhere if you can simplify things and if you can reduce the amount of typing you're doing, you're going to reduce the amount of errors. So Excel has built in Excel, what Excel calls functions, right? So we can use a function to complete some mathematical equations for us without having to type in the whole equation. There are a few ways to get to the functions. First of all, this insert function, let me get my, right here, is the insert function button. All right, so we could click that button. It brings up a wizard where you can type in the type of function you want to use or you need find. And it will find the function for you and walk you through how to use that function. There is also here, on my ribbon, the insert function button, the AutoSum button. If you get back to your computer, your button right here might look different because of the way I have sized my Excel window today, it's not saying AutoSum, but yours may actually have the word AutoSum next to the sigma there. But this button, if I click, shows me several often used functions including sum and average and count and so on. So we're going to use the sum function here, to add up how much we sold, how much money we made today. Right, so with my active cell in cell d7, I'm going to click that AutoSum button and I'm going to click Sum, and Excel fills this in for me. Excel is usually pretty smart about what I want to do. And Excel has said, oh, you want to add up d2 to d6. Was it, is exactly what I want to do. So I'm going to press Enter and I can see that I made $1425, whether that's in a day or week or month, we don't know, but we made a little over $1400. There are some other functions that are used quite a bit, the min, the max, and the average functions. I have put these over to the side mostly so that I can demonstrate to you how functions can use cells that aren't right next to them. Alright, so, so far we've done the formula here in cell c7, and it was right there next to all of the things we added up and the formula here in d2 is right adjacent to b2 and c2. So I want you to see here with this min that the formula or the function does not have to be right next to the numbers that it's, or the cells that it's referencing. So here in the Min. Price, I'm going to click in g2. And I'm going to begin this with the equal sign. Now, if you know the name of the function, you don't have to look it up. You don't have to look it up with the function, the Insert Function dialog box. And you don't have to look it up with the AutoSum button, you can just type it. So I'm going to find the min. That function is min. I type min and Excel brings up this list of things and it says, oh, do you want MIN, do you want MINA, do you want MINIF. We just want MIN. So I'm going to press tab, and now it's saying, well, where do we want to look for the minimum number? We want to look for the minimum number. What is this minimum price? In b2 to c2 or d2, no, b2 to b6, there, right? And enter. $2 is the minimum price. Now again, this is a super simple example, so I can look at that and know that it's $2, right? But imagine several 100 rows. All right, now I'm down here and c3 for the Max function. Equal sign. I begin typing max and there it is. I can press Tab to accept that max function out of that list. And then I want the max of b2 to b6. Now that ending parenthesis and enter. $25. Alright. And the same with average. If I hit the equal sign, average, average of what? The average price b2 to b6. The average price of all of my items is $10. So I've done a lot of calculating here, but what if I change something? Let's say I change the price of this phone charger. We got some cheaper phone chargers in. They now only costs $7. So I'm going to click in cell b4. I'm going to type seven. Now when I press enter to accept this, the item sales for the phone charger is going to change. The total sales is going to change. The min price and the max price are going to stay the same because seven isn't either the min or the max, but the average is going to adjust. So here I am and cell b4. I'm going to press Enter. Seven? My average didn't adjust. Wonder why? Is it still ten? I guess it is. Well that doesn't make much sense, does that? But my item sales adjusted, my total sales adjusted. Wonder why that didn't change? Maybe let's make it something really big. Let's make it $45. Ah, that did it. So if I change that phone charger. It's a $45 phone charger, hopefully really good and doesn't snap off at the end, right? So the Item Sales adjusted, the Total adjusted, the Max. Price adjusted, and the Average Price adjusted. All of those things adjusted simply by changing one value. That is because we use the cell references in all of those formulas and functions and not the actual numbers within those. Alright, so that was a super fast 25 minutes on my end. Hopefully it went as quickly for you as well. If anyone has any questions, put them in the chat. I am going to share a different screen now and tell you about how you can get to our online courses. Or is it? Here it is. Jason and Jen and Rachel are there in the chat to answer questions if you have them. Here's what I'm going to show you now. So, that was a little bit at Getting started with Microsoft Excel. And a lot of that was taken indirectly from the course that IT training offers called Excel: The Basics. All of our courses at IT Training are online and self-paced. And they are all free for everyone regardless of your affiliation with Indiana University. So the, if you are faculty or staff or student, you can take them. But if you are a person in the community or a person outside of the state of Indiana, anybody can take our courses at no cost to find these courses you will begin at the ittraining.iu.edu page. I see a question and I'll answer that in just a second there. Ok. If we get to ittraining.iu.edu, we can click explore topics. It's thinking. There it goes. And all of our courses are in IU Expand. IU expand is simply the portal the IU uses to get not for credit or continuing ed courses out to the public. So everything we do is an IU Expand. So if we click See all IU Expand courses, here are all of the courses that IT Training has in IU Expand, not just Excel. We have, you can see here, things about how to record and edit audio, how to create accessible documents, creating webpages. The course that we were kind of working through today was Excel: The Basics. So if we click on Excel: The Basics, we get to the course page. Here in the middle. it says View course In Expand. I'm doing this very quickly, so don't be nervous. The follow-up email you get is going to have all of these links in it and will give you the same instructions within the links. When you get to IU Expand, you have the option here to login or sign up. And this is where things get different for people affiliated with IU versus people not affiliated with IU. If you work for IU or you go to school at IU, you simply log in with your username and password. Like you log into everything at IU. If you are not affiliated with IU, you will need to create an IU guest account which is also free. All right. So you just have to create the account one time. It's like any other account creation. You fill out this form and then you go check your email and there will be an email there saying, did you really mean to do this? And you say yes and then you come back and click LOG-IN and you can get into any of our courses. That was a lot of information. So most of what I told you just there is on this page and Jen is very helpfully putting these links in the chat. When you try to sign up for IU Expand courses, there's a link here to tell IU users how to do this, as well as a link for non-IU users. Both of these links that Jen has just put into the chat will be in the follow-up email that you'll get. Also in the follow-up email that you get is going to be the exercise file that I use today. It is in a, in box. You will get to this point and you click download. And it will download that file onto your machine. Where you can then work through or mess around with any of this without having to mess up any of the data that you're actually using in your life. Alright, so the question was. Let me get to the other screen to share. Here it is. The question was about the function to hit tab. So what it is is when I put in. I'm gonna delete this one. I'm going to put in my equal sign. And when I start typing min, Excel pulls up this list of all the functions that it thinks I might want to use. And to accept the top one, the MIN at the top. To do that, I press tab on my keyboard and it fills that in for me. I don't have to hit tab. Alright? So if I start typing MIN, and I know it's MIN and I don't want to press tab. I can put in the rest of it myself. b2:b6, right? Without pressing tab, the tab is to accept excels suggestion of the MIN function. [Jason] April, I'm going to jump in there [April] Please do. [Jason] thank [Jason] everybody for joining us today. [Jason] Also real quick on that though, [Jason] could you also show that you, [Jason] I think, when you enter in the function [Jason] also you can either press tab. [Jason] Can you show us how to enter in [Jason] the function that you're pressing there? [Jason] In other words, is it Enter? Is a tab? Oh, yes. So MIN b2:b6 [April] I press Enter on my keyboard. [Jason] Right. [Jason] Right. I press enter. Thank you, Jason. [Jason] Great [April] Thank you, Jason. It's, yeah, my keyboards down here. You can't see it. But that's what's happening on my keyboard. Is I press enter, [inaudible] accepts that function. Alright. [Jason] Thanks. [April] Thanks Jason for clarifying. [Jason] Great! Tyler thanks for your... [April] Anyone else have questions? [Jason] Yeah, Tyler, thanks for your question. [Jason] Anybody else have any questions today? [Jason] Like April said you'll be getting [Jason] a follow-up email with all of [Jason] these links and practice with this file. [Jason] Lot more information. [April] Yeah, and this there will be a recording. We've done a couple of sessions of this, so it will either be the recording for this session or the one we did earlier today, but that will also be out there for you if you want to come back and revisit any of this. We have Getting started with PowerPoint tomorrow, we have Getting Started with Excel sorting and filtering on Thursday. Next week, we have Getting Started with pivot tables. Then we have some Adobe things. Getting Started with Adobe Creative Cloud. Getting started with Adobe Rush, charts and graphs, accessible documents. We have several things coming up over the next few weeks. But at least two more Excel classes if you're interested in those. All right. We'll hang out here for a second to see if any questions. And if not, we'll close the room in just a couple minutes. Thanks for coming.
