Some organizations do, cuz they can purchase a subscription for Microsoft. And when they get the subscription, they get updates to these softwares regularly where office 2021 comes out with a set of features. And there's no updates after that. Now in these sessions, We have an Excel beginner, intermediate, even expert session that we're gonna cover. We have a set of topics. Now in this course, I broke up the topics into two different. What I call modules, where the first module is gonna cover Excel.
Essentials, the user interface, some basic features that we should know about basic maneuvering keyboard shortcuts, and some formulas, basic stuff that max men some count in module two. I also wanna spend some time and just start talking about formatting, how to design a spreadsheet, using certain tools, how to set up a page, how to go to page print preview. Now this class is also designed to be a little hands on meaning..
I recommend you to pause the video after I show you something. And I even say that sometimes in the video to try to give a practice, maybe building the formula that you just saw me see, or use the keyboard shortcut that I'm using. So with that being said, don't be shy to pop open up your Excel, your office, 2021, and practice these things in this beginner. Of course, I will be building the spreadsheet entirely from scratch. There. Isn't an exercise file. Uh, but we'll be building it from scratch from a blank workbook..
I'm pretty excited to get started. Let's go ahead and begin. So I just launched Excel. And as soon as you launched these office products, since 2013, in fact, you get taken to a screen that's known as the start screen. There has been some updates here and there to it throughout the years. But it's looking like the left. Most of it the same, if not, almost all of it the same this year in office 2021, but let's talk a little bit about the start screen. And on the left hand side, you're gonna notice that there's three areas.
You can navigate to one being home, the other one, being new, where you can access your templates and build a new workbook or even open an existing workbook directly from your computer. Let's talk a little bit about each one of these tabs. Before we move forward to a blank workbook and discover the new office 2021 user interface. Now, when you end up clicking on home here, it's gonna show you an area where you can access your new builtin templates, or you can browse online.
One drive or outlook directly from here. So the homepage is nice area to get started. And when you click on new, it just shows you the templates that we just saw on the homepage and open, of course, is it gonna show you an area where you can browse your cloud services, like maybe SharePoint or OneDrive, or even browse your PC to select the file or a workbook and open it? Or even access your auto save workbooks in case there has been a crash, you can recover any unsaved workbooks directly from here. Very powerful feature..
So a lot to discover here in the start screen, not too many changes that I've seen. We still have our account where we can sign into directly from the top hand side, which I'm already signed in. As we still have the Excel options at the very bottom, we have our account information at the bottom where we can give that a click. And here we can see that I have my office home and business 2021 license there. I'm not gonna change the theme. I'm gonna leave it as the colorful theme, since we're talking about the software today, but there are quite a few different themes that we can change the software application too..
Or even the backgrounds. We can have an office background like right now I have this clouds I can customize and have no backgrounds. Keep it nice and simple. There. So we just did a little tour of the start screen. It's useful to know about it's a good area to get started. But now that we've done that, I just wanna start talking a little bit about my favorite template. Probably the one I use the most, the blank workbook, cuz in this course, I wanna talk about quite a few features in Excel and how I do that is I wanna build.
A spreadsheet that actually tracks the sales of a restaurant that I'm building the restaurant's called fast food and this gonna attract some weekly sales, see how much I made for the month. Sue sold the most food who sold the lease food. Maybe hand out bonuses depending on that. And I wanna build a spreadsheet that I could eventually hopefully print and uh, put on my bulletin board every month. Now, hopefully the spreadsheet's reusable. It has colors and it's pretty it's formatted..
So that way I can put it front facing and print it, and hopefully it all fits on one page. So we wanna talk about all those features that allow us to do that. Using this newer interface that we have. Now, let me click on a blank workbook here and give you a tour of the office 2021 user interface. I went ahead and launched the blank workbook. And now that I'm in a blank workbook, I just wanna talk a little bit about the office interface, cuz there has been some changes to it..
As you all know, the windows 11 look it's quite drastically different than windows. 10 has much softer BES looks like a much more lighter tone and it, and it has
An overall different experience to it. And I've noticed they kind of mimic that. Look with these office products. And they actually have a newer interface for the ribbon, kinda looks more protruded out there, as you can see with the tabs kind of sticking out a little bit differently than they used to be. And then of course, we'll, we're always gonna have our command groups still where we have the labelings on the bottom. We have our command groups..So there is a lot going up up there with the new office, 20, 21 ribbon. Now, one thing I want you to notice though, with this office 2021 ribbon, is that there's actually no quick access toolbar anymore are anymore. There used to be a set of tools that we can actually add here. This might alarm people and don't worry. I've actually found out. You can turn it back on. So, if you are one of those people that are familiar with the quick access toolbar, and you notice you just upgraded and you don't have it anymore,.
I do want you to feel free to pause the video and first watch what I do. And then go add the quick access toolbar to you, your user interface, cuz I do enjoy using it. Uh, let me go ahead and right click one of these tabs so that I can customize the ribbon. And from there, I actually have an option to specifically customize the quick access toolbar. And now in office 2021, they're actually gonna ask you to, well, turn on the show, quick access toolbar setting, and you can choose the.
Position above or below the ribbon. I'm gonna put mine above the ribbon and hit, okay. So now I have my quick access toolbar options there where I can add them directly to the top where they used to be there. I can also right click and pin some items there from my toolbar in the ribbon, in the command groups as well. So if you do enjoy using your quick access toolbar, I highly recommend to just give this video a pause and go to your ribbon and right. Click a tab, just right. Click any tab. It doesn't matter which one it is and customize the ribbon so that you can.
Access the quick access toolbar settings. And on the very bottom there you set the chicken market on and choose if you want it above or below the ribbon and hit. Okay. Glad you did that if you did. And it's okay. Maybe one day you will, if not, maybe you like the minimalistic look. Now I also wanna mention that if you wanna hide your ribbon, we have a little area there, a little dropdown where we can select it. And now we have these settings for the ribbon..
We can always show the ribbon. That's my preferred setting. We have a full screen mode, which hides the ribbon until you click on the top. We also have an option for show tabs in case you're on a mobile device or a little laptop. That way you have a small screen, you can save some real estate. So as you click, it shows you the tools. I'm gonna always go to always show ribbon though. And I like how they conveniently left, hide the quick access toolbar option and show the quick access toolbar option directly there. So we can quickly add and remove those toolbar that we have pretty interesting..
I also wanna point out to the bottom of our workbook. Where our sheet names are, has a little bit more of a rounded BELE look instead of a square look. Uh, we can still add our sheets. And of course, if you do a right click in this area, you're still gonna get the activate window, which is gonna be a vertical list of all the sheet names in the workbook. I'm gonna take advantage of the zoom slider here, the zoom slider, and I'm gonna zoom into the spreadsheet so that we can begin maneuvering around Excel. Great, nice and zoomed into my spreadsheet here. I wanna sell a one. That's what my name box is showing me..
And the cool thing about Excel is today. At any point in time, if you ever wanna look for a certain feature. They also have a search box at the very top of the ribbon. This has been here for quite a few iterations now, but it's worth, they mention where you can search for search and features. And even if you need help on them, receive help on them. By looking into the Microsoft support articles that open up pretty natively within the software. This is a quick little article on power pivot, a great tab that you can use for your pivot tables..
Go ahead and close that there. So not many changes to the user interface, but it does have a nice clean, refreshed look. I wanna say the biggest thing that you're gonna notice is that your ribbon display options have now moved and your quick access toolbar is now able to be turned on or off, uh, which is something that we couldn't previously do. Go ahead and pause the video, open up a workbook, play around, take a look at your user interface, set up the settings for your quick access toolbar..
I'm gonna save my workbook here. I'm gonna go ahead and call it the fast food monthly sales
So that we can get started. Hopefully you do the same. So now I actually wanna begin by typing in these cells and creating the spreadsheet that I would like. And I'm gonna do that by adding a title to my spreadsheet. I'm just gonna call this one fast food and I'm just gonna put on the bottom. They're always fresh and, uh, why not always fast?.That's me. Now when I hit enter here, I just wanna point out for people when you hit enter in Excel, that actually moves down something known as your cell address box, which is that green box there. And the enter key is how we navigate and essentially move it down a sales space. So I'm gonna hit enter just one more time. Cause I do wanna add some headers to my spreadsheet cause I wanna actually track the sales people.
And I just wanna do a week by week breakdown. And now that I've actually added in salesperson, I just wanna mention another little shortcut that I use, cuz I did move over to this cell over here. I moved basically to the right. That's what I essentially did. I moved over and in order to do that, I simply clicked on the tab key. And that actually went ahead and moved me to the right hand side. Now, of course, if I hold on to shift and tap, that's actually.
Gonna move me back to the left. Same thing with enter extremely useful to navigate with shortcuts, especially when you get some more advanced topics, like an Excel expert. When we talk about macros. So now that I have this here, I actually wanna add in a quick little column for week one. And I notice when we are typing in spreadsheets, there are certain situations where words get cut off in the cell. And that's not a big deal..
I mean, there's many ways we can fix this, but this might happen all over the place might happen over several different columns. We can always go to the top of each one of these columns, where the letters are, that's where the column is. And between them, you can always expand them to make them bigger or smaller. However, I've always dealt with situations where I have multiple columns, multiple, multiple columns that have that situation like this column and this column. And I like to mention in my beginner courses that there's this little triangle here next year column headers, and you give it a little click..
It's gonna select all the columns and all the rows in the entire sheet. And if you go ahead and do a double click in between one of the columns or one of the rows, it's gonna give you a best fit for the entire spreadsheet since everything's selected for entire columns or for the entire rows. So notice how the salesperson column is nice and large simply because it takes the place of the largest cell and it fits the column to that size work B and.
C fit the cell size of week one and week. So always remember if you need to quickly fix a spreadsheet sizing for these cells, or if you see a lot of pound signs or hash signs within cells, when you have numbers, it's just letting you know that these cells are too small to display something for you. And you might wanna quickly click on that triangle and do a double click on the rows or the columns, depending on what situation it is to quickly get it fixed for you. I usually do that before I email something out or quickly before I save the workbook and close it. So the next time I open up, it's nice and clean..
Now that I actually have these headers in place where I put week one and week two, I want to continue on by adding in week three and week four and new cells. But in order to do that, I'm gonna take advantage of a tool called auto fill. Now I want you all to know that every cell that you have a cell address box on has this little square, a green one on the corner of it. That's the auto fill handle. And what it's gonna do is it's gonna help you..
If you have a word and a number, it's gonna increment that number next to the word. So I can have week four week five quarter one quarter, two, quarter three. I can do months. I can do days. Let me go ahead and expand this here. I'm make it to week three and I'm gonna make it to week four. Now, a lot of people are new to this when they use the autofill handle, please make sure that when you are using it that you're making sure to see the visual indicator that you're on top of the autofill handle by looking at the skinny black cross hair. before left clicking and holding and dragging across.
Looking good, looking good. So there's something I wanna do here to my headers currently. I have my headers in, but what I wanna do is I wanna highlight them. I wanna highlight all of these headers so that I can align them in the middle of the cell for each one of these cells. And in order to actually do that, it's, it's a really simple process, but I wanna talk about keyboard shortcuts to do things, cuz this is definitely where the power of efficiency comes. In real life, you might have 50, 60..
You might have a whole year's worth of data that you're highlighting, not just four weeks. And knowing these keyboard shortcuts will save an immense amount of time when highlighting things. So if I needed to highlight this top row from the very start here, I can always use control shift, and then I can choose a directional arrow and it'll highlight in that direction until it meets a blank cell. Let me try that out. Control shift to the right..
I like I like, and of course I can always go to my home tab and under the alignment command group, I can center these items here. Great. Now that I have this in here. What I wanna do is I also wanna continue by adding in the actual sales people names, and then hopefully giving them some data, some raw data that I can put in that I have for the month. So I'm just gonna add in some sales people here. Let go ahead and put my name as one of the sales people.
Here. I'll go ahead and put some familiar names that we might have heard of. Maybe not keep it simple. It's a small staff, super small team. We're gonna keep it with only four of us. So now that I have this here, I wanna actually start adding in some data to my spreadsheet. So when I enter in data, I'm gonna put my cell address box on a cell. I'm just gonna add in some data, some fake numbers, like 50, I'm.
Gonna hit tab to move over to the right, so I can add in a hundred. And now I wanna talk a little bit about another trick that we wanna take advantage of. We wanna get used to it. It has to do with the AutoFlow handle. Again, my autofill handle. You remember that thing? I just used a moment ago where I highlighted a chunk of cells or a cell, and this little square was pulled well, what happens if you do it with two cells selected? Instead of just one, right? That's what I have now..
It it's actually gonna follow a pattern. So this pattern is plus 50. So in these next cells, if I actually pull my auto fill handle in that direction to the right, it's gonna be one 50 and 200, let me go ahead and pull that. And of course, auto fill tries to help as much as possible. So if you end up pulling it down and you have too many cells selected, like it's not gonna recognize a pattern here, but it will help you by copying down the cells..
Now, I'm gonna go ahead and drag down here. So copy it. I'm gonna drag down here again, and I'm gonna drag down here again. So I have some fake numbers in here, just some fake sales data to get the spreadsheets template, uh, a little bit of a look. I wanted to see how we would look with numbers in it. However, this is supposed to actually display sales data. And what I want for these actual cells are for them to have a data type.
For the currency values that I'm displaying. And in order to do something like that, I wanna actually use the keyboard shortcuts to highlight the chunk of swells or cells I should say. I can always use the keyboard shortcut to highlight to the right. And of course I can always use the keyboard shortcut to highlight all the way down.
Now that I have this highlighted, I have multiple routes that I can take to add this currency format. Now, inside my home tab, I actually have a number command group where I can toggle on the currency format and hide my decimal places. , but I'm all about efficiency. And if you notice there's actually keyboard shortcuts for many things that we're talking about today. And if you want, you can always use control shift..
And the number four at the same time being held. Remember the number four holds a dollar sign to change those cells that you have selected to a currency format. That's pretty convenient. If you change it to five, it goes to percentage. Date times 3, 2, 1 is general, or one is number til is general. I'm gonna hit the control shift four there rather simple keeps it smooth. I didn't have to move my hands anywhere. I didn't have to go away from the keyboard..
Now, the final thing I'm gonna do is I'm gonna knock away these decimal places by holding down the alt key. And clicking on the letter, H cuz that's where the H key is. And then I'm gonna click on the number nine, cuz that's gonna hide the decimal place and you can also do it really fast. You can hit alt and then H and then nine really fast. And it'll hide the decimal places I've been talking for a while though. And I've actually produced quite a nice spreadsheet. There's not any color to it, but I have the layout down..
I have the columns created and the headers in them. I have some sample data. I have all the sales people's names on the roster, got the title, the slogan things are looking good. I centered the headers, added the currency data type that I needed. Now, what I would like you to do is just pause this video and catch up if you haven't been doing so, pause the video and create this spreadsheet. Use those shortcuts. Practice the data types, adding in cells, hitting enter, hitting tab, hitting shift tab, hitting shift, enter, navigate around, go back to the.
Video and Repar where rewatch certain parts as you do it, but definitely practice with those shortcuts in mind. Hopefully I'll see you soon. Pause the video gets the try and come right back. Welcome back. Hopefully you had fun creating the spreadsheet, practicing the shortcuts. And now that we have some data or some names, actually in here, something I wanna mention that we have available since office 2016 in case you've come across a word, um, you're not familiar with, or you wanna get some information, an article about something there's a tool known as smart lookup, where you can.
Always look up information using a Bing search directly from the spreadsheet. And of course, once I'm done with this search, I can close it. And it's just worth the mention. When you have this cool feature on a smart lookup with a right click, or you can always go over too. If I'm not mistaken, the review tab and the smart lookup is available under the insights command group..
Very interesting stuff. They try to get you to stay in Excel as much as possible instead of hopping away to these various browsers that you might need to use with this feature. So now that I have this in here, I have this data in here. What I wanna actually do is I wanna build a column that tracks the totals for these sales people. And in order to do that, I have to perform these simple calculations, a sum. I have to sum search the cells. And there's so many ways to do that in Excel..
So many different ways. And I just wanna talk about these various ways that we have available since this is a beginner course. Uh, let me build a total column here. And I just wanna run through the various ways you can perform simple calculations or even build a formula to do the job. Now, the most basic way to actually add something in Excel or to run a formula, an equation, a function is to start your cell with an equal sign that tells.
Excel, Hey, I'm about to do a formula. I'm about to do a calculation. And if you want, you can actually select the cells that you wanna use. In the formula by selecting the cell once the equal sign is there. So instead of actually putting the numbers you want, although some people do this, putting the numbers that you want, it's much more preferred to put the cell address. You want tell Excel where to look that way in case the number changes in the spreadsheet. You don't have to go running to update the total column as well..
Now, of course, this way might take a little time if you have a lot of numbers to add, and it seems kind of like a repetitive process there with even four cells. But if I sum up B five plus C five D five and E five and hit enter, cuz I'll go down a cell I'll total to 500 as, as it should, as it should there on the bottom of Excel, there's something known as a status bar. You haven't heard of it before the bottom of that, software's called the status bar. Where our zoom slider is located and it actually shows us the sum is 500 when you have sales highlighted..
Of course. So another way of actually doing a calculation is instead, you're still gonna need an equal sign, but this time you're gonna use a function from the Excel's function library. It ends up showing you all the function names. Once you start filtering for it and start typing in a few letters. Like I want to use the sum function and I can filter for some..
And once I end up highlighting it, I can always click on the tab key to fill in the rest for me. And this essentially allows me to highlight a chunk of cells within a parenthesis. So that I can use the sum function to add up all these sales for myself without manually adding those plus signs in those are repetitive task. You know, there's even a way where you can highlight, remember we know shortcuts, we know the shortcut where we can highlight something with control, shift, and the right arrow..