Learn Excel Basics by Tracking Monthly Cookie Sales (Full Transcript)

[00:00:00] Speaker 1: Does your resume say you know Excel, but you've never actually used it? Let's fix that. Hi, I'm Kevin, and in this video, we'll learn Excel from scratch, step by step. Let's dive in. Here I am in Excel, and this right here is called a worksheet. And each of these boxes is a cell. That's where you enter your data. Across the top, you have columns labeled with letters, and right here down the side, you have rows labeled with numbers. When you combine the two of these, that gives you a cell address. As an example, here we have cell A1. Or over here, I can go down to cell D6. But instead of going through everything, let's build something together. Let's click into cell A1, and we're going to create a simple spreadsheet to track cookie sales here at the Kevin Cookie Company. So first off, I'll type in date, and then press the tab key. And over here, I'll type in sales. Now I can press enter. Now right down here, I'll enter in the first month. We have January. Now I could go through and I could type in all the different months, but actually Excel can do that for us. Let's click into the cell with January. And when I move my mouse over the bottom right-hand corner of this cell, you'll notice that my cursor changes. I'll click on that, and then I can drag it all the way down to December. There we have all the different months. Now let's add some numbers for sales. So right up here in January, we had 1,200 sales. I'll press enter. And over here, we had 1,800. Then I'll go through and fill in the rest of the month's sales. And just like that, we have a working dataset. It's a little hard to read all these different sales numbers. So let's go ahead and highlight all of them. And then right up on top, click into the Home tab. And right over here, we can add 1,000 separator. I'll click on that, and that makes it much easier to read. You'll notice that we don't really need these decimals here. So also up on the Home tab, right over here, we can decrease the decimals. I'll click on that, and then click on that again, and the decimal goes away. Perfect. Now, you'll notice here in the Date column, some of these dates or months are cut off. Here, we don't see the R for September. The column width is not quite wide enough to capture the full word. Right up on top, we can click in between these two columns. And over here, I'll press and hold and drag, and I can make that column wider. Or here's a little trick. You can simply double-click on it, and that'll auto-fit to the contents in this column. I think everything looks a lot cleaner now. Now, let's convert this into a table. This is one of the most useful features in Excel. Just make sure to click anywhere in your data, then go up to the top tabs and click on Insert. And right here, you see the option for a table. When I hover over, you'll notice that there's a shortcut key, Control-T. Most things in Excel have shortcuts, and they can help you save a lot of time. I'll click on this, and here we see a dialog pop-up. Right over here, it detects the range, or basically all the data in my table. And right down below, it says my table has headers. Here, I have date and sales. That's my header. So right over here, I'll click on OK. And now we have a formatted table. I think you'll agree, this is a lot easier to read, and it also unlocks some really powerful features. Just make sure to click into your table, and up on the top tabs, you'll notice that there's a new tab for table design. Make sure to click into that, and here we have the option to turn on what's called a total row. When I click on that, down below, you see a sum of all sales throughout the year. Now, if I click on this dropdown, I can also change it to an average, but for now, let's stick with sum. I'll select this. Mama always said, there's money in the cookie business. It's still a little bit hard to spot trends just by looking at numbers. So let's try adding some visual cues. Over here, I'll highlight the entire sales column. Then let's go up to the Home tab, and right over here, we have an option called Conditional Formatting. Let's click on that, and let's go down to Color Scales, and I'll choose the first one. And look at that. Now we can instantly see which months performed better. It looks like sales increased as the year went on. In fact, it looks like people really like their cookies around the holidays. Now, let's also see if we could add a chart. Make sure to select your data, and then go up to the Insert tab up on top. And right here in the middle, we have the option for a recommended chart. I'll click on that, and the first one is a line chart. I think that'll work well to show sales over time. I'll click on that, and then let's select OK. And there we have a chart showing us sales throughout the year. It's a lot easier to understand what's happening with sales by looking at a chart or a visual than by trying to interpret a table. Next, I'd like to understand sales in the last quarter of the year. So October, November, and December. Right over here, I could highlight the sales for those three months. And if we look down on the status bar at the bottom, you'll see that we had total sales of 31,200 cookies. Not bad. But when I click away, you'll notice that goes away. So let's make that permanent. Right over here in cell A16, let's type in Q4 and then a colon. Then go into cell B16, and I'd like to add up the sales for those three months. So let's start by entering in an equal sign. That tells Excel that we're about to enter in a formula. Then right over here, I'll click on cell B11, which has the sales for October. Then I could enter in a plus sign, and let's add that to November sales. And right over here, I'll enter in another plus sign. So I'll select all three of these months. Now, you'll notice that Excel references the cell by its column and row. And over here, I'll press Enter, and we could see that we had total sales of 31,200. Pretty cool, right? You didn't have to pull out your calculator. Now, this works, but it's not very efficient. Instead, we could use what's called a function. So right in this cell next to it, let's type in the equal sign again to let Excel know that we're entering in a formula. Over here, I'll type in the sum function and then open the parentheses. And over here, we now need to feed in an argument into this function. So let's select all the different cells that we want to sum. I'll highlight these three different cells. And then let's close the parentheses, and then we can press Enter. And there, we get the exact same result, 31,200. Now, functions are built-in formulas that help you do things faster. And sum is one of the most common ones you'll use. And right here, if we click on Formulas up on top, you'll see some of the many different functions that Excel offers. And there are lots of different options here. Let's now scroll to the top of the worksheet. With Tables, you get these little dropdowns at the top. When I click on this in the Sales column here, I can sort by largest to smallest. And when I click on that here again, we can see that December or the holidays are the best time of the year for us. Or I could also filter my data. I'll click on this dropdown. And right over here, let's see the last quarter. So let's select November, October, and here's December. I'll click on OK. And there we see the last quarter of the year. This makes it really easy to focus on just specific parts of your data. And also, you'll notice that the Totals row automatically updated to show the total just for the filtered results. That's pretty cool. Let's now clear the filter. I'll click on the dropdown again. And over here, let's clear that. And here, I see all the results again. Excel also has built-in AI with Copilot. Up on top, let's click back to the Home tab. And all the way over on the right-hand side, let's click into Copilot. Now, instead of writing formulas, I can ask a question. As an example, let's ask, what are my total sales? And then let's go ahead and submit that. And it'll generate an answer for me. And look at that, 67,800. And that matches exactly what the table says. Now, I can also ask it to create charts, summarize trends, or highlight insights on my data. It's a really powerful way to explore your data, especially when you're just getting started. And that's the foundation of Excel. From here, you can start building your own spreadsheets right away. And you can explore even more features. If you want to keep learning, consider subscribing. Who knows, maybe I'll even send you a cookie.