 If you already have Excel downloaded on your computer, feel free to skip to the next lecture. However, if you have not yet downloaded Excel, please follow the link in the notes to download it. Or go to microsoft.com slash enus slash Microsoft 365. Excel is a paid tool and there are very few versions available. You might also have it through your school or organization. Most four-year colleges provide free licenses of Microsoft Office products to their students. So I would recommend either asking one of your administrators or Googling to see if your school offers it. If you do not yet have it through your school or organization, I would recommend Microsoft 365 subscription. If we click through to the personal and family plan, you can see that for $6.99 a month, you can have access to all of Microsoft's products, a terabyte of cloud storage for just $6.99 a month. If you're unsure of whether you will need Microsoft Office on your personal computer, you can also buy a single license to Excel. This would be useful only if you're going to use only Excel, but I've rarely found that to be the case. And it does cost an upfront $160 to get a license to Excel. Again, I would recommend that you purchase the 365 plan. It comes with many other features that I find useful every day. Once you've gone through the process of paying and downloading for Excel, please move on to the next lecture. Now that you have Excel downloaded, you should see an icon in the bar below. If you don't yet see an icon, you can use this really useful feature on Windows where you click this and you search Excel. It should be the first thing that pops up. Click Enter and Microsoft Office should start. When the program is started, you should see a screen that looks like this as long as you're on a Microsoft Excel version that is 2007 or later. Up here, we have a number of different templates you can start off with an Excel workbook and then also all of your recent files that you've been working on. If you're brand new to Excel, don't worry if this is empty. To open our first Excel workbook, let's click Blank Workbook. And it should open something that looks like this. This is the big magical world of where Excel's journey, this is where the real Excel journey begins. In the next lecture, we will take a tour through this world. This is our Excel worksheet and I want to start by pointing out the different components of it. This is our quick access toolbar, which provides basic features like saving, undo, and redo. And if you have Office 365, there's a feature called autosave where Excel will automatically save a file for you and upload it to the cloud. This entire thing is our ribbon. And this is where the area of where all of the core functionalities of Excel appear. You can see there's many tabs to the ribbon, home, insert, page layout formulas, data review, view, and help. As an Excel user for 10 years, I do not think it is important that you memorize exactly that which functionalities each tab has. It's way more important to have a general understanding of which types of functionalities appear in which tabs. You'll always be able to find what you need if you understand the nature of each tab. For example, the font formatting always appears in the home tab, while the charts always appear in insert. I highly recommend that you take a minute right now to explore the functionalities in the ribbon to familiarize yourself with them. Moving on. This is our worksheet and these, each of these boxes is called a cell. Each cell contains a value. Right now, each cell is blank. However, as we build our Excel workbooks, this will change. Cells are formatted in a table where each cell can be referenced by its column, A, B, C, D and its row, one, two, three, four. So this cell right here is B four because it's in column B on the fourth row. If you've played chess in the past, this is very similar to how a chessboard is labeled. Down below are our worksheets, which can become numerous as our workbook gets more and more complicated. However, for now, we just have one worksheet. In the bottom right-hand corner, we have our zoom slider where you can change the amount that you're zooming by. We also have a couple of different options like page layout and page bake preview when it comes to the way that we view an Excel workbook. However, I almost always go with the normal view because I very rarely print an Excel workbook. However, if you have different uses for Excel, you might find these particularly useful. That's it for this section. In the next section, we'll focus on the fundamentals of basic data entry into Excel. Let's get started building our new spreadsheet. Let's say I was a store owner who was trying to create a spreadsheet to keep track of his quarterly sales. In this grocery store, we're gonna sell a number of items. Let's have each of our rows represent a food that we hold in our store. To show that our list is full of foods, let's have the row label entered into cell A1 say foods. In order to enter data into the cell, you just hover it and start typing. Once you are done entering the row header, we want to move on to the next row. We can either click the next cell with our mouse or we can use the shortcut enter. Enter is Excel shortcut to both finish editing the current cell and move downwards. So I hit enter and it brings us to the next row. Let's type in the remainder of our foods, hitting enter each time to move downwards. Let's say we have rice, eggs, kale, bread and corn. For simplicity's sake, let's just stick with these products. Now, for our column labels, let's label them by the dates that the quarters of 2022 end on. The first quarter of 2022 ended on March 31st, 2022. Now we want to move to the right. To move to the right, we use the shortcut tab, whereas enter goes downward, tab goes to the right. When we hit tab, we see Excel move over to the right. One thing you might notice is that Excel has automatically formatted our previous input as a date. Excel is smart enough to know that this form of data is a date. And so let's keep going and enter the dates into the rest of the columns. Quarter two will end on June 30, 2022. Quarter three will end on September 30, 2022. And finally, the last quarter will end on December 31, 2022. I want to make this look a little bit better by changing the format of the cells. I could do this one by one, however, it would be much faster to do this to all the cells at the same time. Let's highlight all of the date cells by clicking on the leftmost or the rightmost one, holding down shift and clicking to where you want to highlight across. You need to make sure that you're holding shift. This is Excel's way of knowing that it should select multiple cells instead of just one. Once you have all of the cells selected, you can change the format of all of them at the same time. Now that we have them selected, let's change their format. If you look in the home tab of the ribbon, you'll notice a dropdown menu in the middle. This is our formatting selector and allows you to specify what kind of data you are entering and how you want it to be presented. Let's click the dropdown menu. We can see that there is a large number of different data formats. I encourage you to explore these for a minute to familiarize yourself with the data types that Excel uses. But for now, we will just choose short date as our data type since we are creating column labels for months. All right. Now that we've entered in our dates, let's enter in some not mock numbers into our Excel spreadsheet using tab to move rightward every time. The numbers aren't super important here. It's more important that you get practice entering data into Excel. So I'm just gonna say that beams in the first quarter had a sale, sales of 22, 220. Second quarter had the sales of 350. Third quarter had a sales of 380. And then fourth quarter had sales of 290. Now that we've gotten to the end of the first line, I press enter and Excel is smart enough to know that I do not wanna just go down, but I actually wanna go all the way back left to the left side of the table to begin populating data. This is yet another useful feature of Excel as a tool for data entry. So let's just go ahead and enter in numbers for the rest. 220 sales for eggs in the first quarter, 380, six, 10, and 220. 150, 180, 220, 260, 800, 650, 690, and 720. And then finally for the last slide, let's enter in 120, 95, 190, 165. Okay. Now that we've entered our data into our entire table, we need to make one more step. Well, we wanna convert these numbers into currency because these are representing dollar amounts. So in order to do this, I want to highlight all of these numbers to start. We can do this just as we did earlier in the lecture by holding down shift and clicking. However, there's actually a better way to do this. If you started this cell and you hold shift, you can move about the spreadsheet with your arrow keys. So if I hit left, it goes left one, and then I can hit up and highlight the entire table. So now that we have our entire table highlighted, we want to convert it to currency. What we'll do is we'll go back up to our number formatter and we will choose currency. We can see that there are decimals added to this table, but we don't necessarily need them for the purpose of this exercise. We only really care about the whole numbers. So with the whole table highlighted, let's go back up to the ribbon. You'll see that there's these two buttons, the decrease decimal button and the increase decimal button. For the purpose of this exercise, I want to decrease the decimals by two. So we only include whole numbers. This was a quick introduction to data entry. In the next lecture, we'll learn how to work with tabs, cut, copy and paste. Let's say we wanted to copy the data we collected and paste it in a different location. First, let's create a new tab in our Excel workbook. We can do this by clicking the small plus sign you see next to all of our tabs. When we click this plus sign, you can see that Excel has created a new sheet. Let's rename this sheet by double clicking on the name and naming it something like copy because we're gonna paste a copy of our table into this tab. The first step in copying the data will be highlighting our entire table. We covered the use of shift in the previous lecture, but I want to teach you an even better way to navigate and select tables. Let's try something together real quick. If we hold down control while using the arrow keys, we can see that the cursor will skip from one end of the table to another. This will allow the cursor to keep going until it sees the end of the Excel sheet or a white space. Play around with it. Control plus the arrow keys is a really good shortcut to quickly navigate about a table. But when we combine this with the shortcut shift, you can actually select large sums of data very quickly. So let's try it. Hold down control and shift and using the right arrow key, hit right. We can see that the Excel has highlighted all the way to the right. Continuing to hold shift to control, let's hit the down arrow key. This allows us to highlight our data in just two keystrokes. All right, so we wanna copy this table. We could go up to our ribbon and select the copy icon. Excel puts the dotted line around the table to show you that it has been copied. But a better way to do this would be to use control plus C. That's the shortcut Excel uses for copying. The dotted line is once again around the table showing us that it is being copied. Let's go to our new tab and we can paste it using this in the home tab of the ribbon or undoing that, we can use control plus V which is Excel's shortcut to paste. This is much quicker than clicking anything on the ribbon. Let's go back to our original tab. If you wanted to cut something, the process would be exactly the same as what I just showed you except you would use control plus X. However, the difference between cutting and copying is that cutting will remove the table from where it is right now and paste it into where you want it to be. These shortcuts, control C, control X and control V are some of the most important shortcuts in Excel. Control C allows you to copy data, control X allows you to cut data and control V allows you to paste data. In the last lecture, we learned how to copy and paste data in Excel. However, there is a more dynamic way to transfer data. This dynamic way is called a cell reference. I'll explain what they are but I first want to show you what they do. Say I wanted to copy this table again. There's a different way other than to cut, copy and paste. Let's say we wanted to duplicate it right next to our current table. We type equals, which is Excel's way of recognizing a formula. A formula is something for Excel to calculate for you. After typing equals, we can create a cell reference by clicking on the cell that we want to connect to our current one. So I'm going to click on A1 and you can see that Excel has populated the cell reference for me. I hit Enter and Excel automatically populates the value in this cell and it will change if the reference changes. For example, if I decide I no longer want to call it foods but I want to call it groceries, our cell reference has automatically updated. This is one of the magic parts about cell references. It allows you to create dynamic spreadsheets by allowing you to assign a cell's value to another cell. I'm going to quickly delete the cell reference we just made. There are a few more ways to do cell references. Along with clicking, we can also use our arrow keys to scroll. Just type equals and then start using the arrow keys over to the cell that you want to reference, hit Enter and the cell reference works exactly the same. I'm going to delete this one more time. One last way to do it is to type equals and the cell reference directly. So if you're trying to reference A1, you can just type A1 and hit Enter and it automatically does it for you. Okay, that was a lot of information but there is one other concept that we need to go over. We are trying to copy this table but we do not want to have to type all of these cell references one by one. Instead, we are going to use a feature in Excel called the fill handle which automatically populates data for us. Clicking on our cell, we see a green box appear in the bottom right-hand corner. Let's click this box and drag it over the range with which we want to auto-populate the data. Excel already does this all for us. It's smart enough to know that it has to change the cell reference and you can tell this by clicking into the cell and you can see that instead of referencing A1, it's now referencing A2. This is another one of the magic things about Excel. We want to populate the rest of our table. So let's highlight and then drag over. We can see that Excel properly transferred our data. However, it did not properly transfer the data types. We can see that the dollar amounts no longer have dollar signs in front of them and the dates are looking kind of funny. So let's fix this. Let's highlight our dates, go up here and click short date. Finally, come down to the dollars, click here and then let's choose currency again. Let's decrease those decimals and voila, we have a fully duplicated table. Now you might be wondering, can I only do this in the same sheet? Well, no, you can actually do this across tabs. Let me show you what I mean. Let's go to our copy table that we worked on last lecture. Let's delete it and let's do it again with cell references. It works the exact same way, except when you're referencing the cell, you need to click over to the other tab. So I would click equals, go over to sheet one and choose A1. Hit enter and we can see that even between tabs, Excel is properly able to transfer this data via cell references. Dragging down, we see that it's continuing to work and dragging right, we see that it properly transfers without the formula. Dynamic cells can be done across worksheets in even across different Excel workbooks. And this will become more and more important as we work with larger and more complex data sets. Now that we've learned about cell references, let's learn the basics of Excel formulas and functions. Let's quickly clear out our spreadsheet by deleting the extra table by highlighting it and hitting delete, which is different than backspace. Let's also delete the tab with our copy by right clicking on it, hitting delete and selecting delete. Okay, now we have a clean spreadsheet. Say we wanted to find the total sales by item for each year in 2021. First, let's insert a total sales column into our table. We want to add up each of the rows to find total sales. I can do this by creating a formula by hitting equals and then referencing the cell and using the plus sign as a signal to Excel to add the things that I am entering in. So B2 plus C2 plus D2 plus E2, I hit enter and we can see that Excel has properly calculated the sum of rice's sales for the entire year. The other common operators besides the plus sign are minus for subtraction, the star for mult and the slash for division. However, there's an even better way to write what we were trying to do. We can use something called a function. A function is a preset formula in Excel that helps perform mathematical, statistical and or logical operations. Just like a formula, we click enter to begin it. And then the formula that we are gonna eat the function that we are going to use is called sum because we want to sum a set of cells. So we type sum, open parentheses and we can see here that Excel tells us what arguments or inputs that this function takes. Here it just takes a series of numbers. So using the sum function, we're gonna select the range of rice sales. We can either do this with our mouse or we can do this with our arrow keys, holding down shift and going over. We close the parentheses, we hit enter and it is correctly summed our row. However, there's even a better way to do that. Excel has a feature called the autosum feature where a single shortcut helps you write that entire sum formula. Let me show you, let me first delete this. The shortcut is alt and the plus and the plus equals sign. And as you can see, Excel immediately adds it in, adds the correct sum formula into your spreadsheet. I hit enter and it's all done. Now that we have gone through all the ways that we can sum numbers, we're once again gonna use the fill handle to auto-populate the rest of the numbers. And we can see that Excel has properly calculated the sums. However, whenever you're using the fill handle or any of Excel's auto-populating features, I would always recommend checking your work. We want to take a look at the formulas to make sure they are summing the right thing. So how do we do this? Well, we can do this by either double clicking on a cell or using the shortcut F2 or function key two which often exists at the very top row of your keyboard. When you open this formula in Excel, you will notice that Excel highlights the cells that this selected cell is referencing. This is a really useful feature that allows you to visualize your work and check for correctness. And since we want this cell to be summing all of the egg numbers, I know that it is right. It looks correct to me. Moving on, I'm gonna add a summary table below the table. Let's quickly add a heading down here and let's call it summary table. In this summary table, I want to have the total sales over the period. How are we gonna do this? Well, I'm gonna use the sum formula again. So I'm gonna type equals sum and I can sum the entire range of cells. This works, but you can also sum other sums. So if I wanted to do this correctly, I could do that and just sum all of the other sums that we've already done. I hit enter and we can see that Excel has correctly calculated our total sales for the year. Say I wanted to calculate the percentage of my sales that each product contributed to. Let's add a column, percentage of sales. Percentages are another important new data type that we need to cover. Remember, percentages are numbers expressed as a ratio or number as a fraction of 100. Percentages are calculated by the simple formula, the amount over the total amount times 100. A percent is effectively a fraction, so it's important to note that in percentages, 100% equals one and 1% equals 0.01 because that is one hundredth of one. All right, let's first calculate the decimal form of our percentage of sales. Let's take the total of our item by clicking equals and selecting our item and then dividing it by the sum total. This should give us the decimal form. Now we want to change this to data type percentage because we want it to be represented as a percentage. Clicking on it, we can see that Excel has now calculated the percentage for us. This represents the total amount, amount of total sales of our top item as a ratio of 100. Okay, now let's use the fill handle again. Let's drag down and fill the rest of our sales. Wait a second, something broke. These other percentages do not appear to be calculated correctly. Actually, this is a specific error message from Excel that is thrown when you try to divide something by zero. If you remember from your math class, anything divided by zero is undefined and therefore cannot be calculated. And the reason that this is happening is because up until this point, we've only been using relative cell references. If we look into the formula on this second row, we can see that the first cell reference is right, but the second cell reference has moved down a single one which is correct based on what we were doing before but not necessarily what we need now. We want to keep our cell reference directly on F9. So we need to lock it. To lock a cell, let's go into our top formula. And what you do is we want to lock F9. So we put a cash sign in front of the F and then a cash sign in front of the nine. This is the way in Excel to denote that something is locked. You can also do this by using the shortcut F4. Let's hit enter and then let's use the fill handle one more time. We see now that Excel has properly calculated our percentages and if we look into the second line again, we see that our locked cell is properly being locked because it's not moving off of F9, but it is being dynamic when it comes to the first cell reference. And so in effect, locking only locks the cell with which you're putting the cash signs in front of it. Now that we've covered cell references and formulas and functions, we're gonna move on to the next section where we dive deeper into basic Excel functions. Let's say we wanted to find out the number of periods or time periods that this table records. In our summary table, let's add a periods count. Fittingly, Excel has a count function that counts the number of occurrences in a range. Let's try it out. Let's type equals to represent the start of a formula and count. Highlighting the range of items that we wanna count which in this case is the range of dates, close parentheses and then click enter. We can see that this has been correctly calculated by Excel. There are four quarters with which our table calculates. Let's delete this for now. Let's introduce another example that shows a more complex use of the count function. Let's say we wanted to calculate our average monthly sales for each product expressed as a dollar amount. If the concept of an average is foreign to you, please see the notes from this lecture. I have placed a helpful refresher in there. Let's add a new column, average monthly sales. We can see that the text is spilling over just a little bit. So let's fix this. Let's highlight all of our columns, go up to the ribbon and click wrap text. This allows text to go to multiple lines inside of a single cell. We can see that our columns and our rows are now fully visible. Okay, once again, we want to calculate the average monthly sales by product expressed as a dollar amount. In order to do that for each product, we're gonna take the total sales divided by the number of periods, which is four. So let's try equals total sales divided by count of the periods. And because we don't want the reference of this range to move, let's lock it. So I'm gonna hit the F4 key, which automatically locks that range for me. I'm gonna close parentheses and hit enter and then use my fill handle to drag it down. This is a great example, not only of the use of the count function, but also of how formulas can be more than one term. We took the total sales of a product, F2, and divided it by the count of number of periods. However, there's an even easier way to calculate the average of a number. Excel has a built-in average function which automatically does this for you. Let's try it. Let's type equals, type average. And whenever you don't want to type the full formula, but the formula that you want to use is there, you can just click tab and it should complete it for you. And then you take this and you enter in the range of numbers with which you want to take the average, close parentheses and hit enter. It knows that it's only looking at four periods, so it automatically divides it for you properly. Now let's use the fill handle to drag it down. Now that average function is used in all cells. Now that we've learned the average and the count function, in the next lecture, we're gonna learn about the min and the max functions in Excel. Min and max. Say we wanted to find what the highest sales month was. Let's add a column called maximum monthly sales. Sales. Let's also add a column for minimum monthly sales. Since these texts haven't been wrapped yet, let's go ahead and do that. Okay, let's figure out how to calculate the maximum monthly sales. Excel has a built-in max function, which gives you the highest value in a range of values. So let's try it out. Type equals max, hit tab, then go over the range with which you want the max to be from. Hit enter, and now we can see that 380 is actually correctly inputted as the highest sales month. Let's do the same for minimum. We type equals min, tab, and then scroll over to the range that we wanna look at. It should return to 20, and it does. These are the min and max functions, and there's one other thing that I want to cover in this lecture. It's the fact that you can use the fill handle for multiple columns or rows at the same time. If I were to highlight both of these, click the fill handle and drag it down, we can see that Excel properly populates the min and max for every row. Now that we've covered min and max, we're going to move on to logical operators and functions in Excel. Let's take a look at another type of Excel formula, the logical operators and logical formulas. What are logical operators? Let me pull them up for you. You may recognize some of these symbols from your high school math class, and for those with a programming background, you are very familiar with these. Equal sign greater than, less than, greater than or equal to, less than or equal to, and not equal to. For those of you who are not familiar with this, I will link the documentation. I highly recommend to familiarize yourself with the logical operators that Excel offers. Logical operators allow you to perform a comparison between two items and return a Boolean value. A Boolean only has two values, true or false. While there is more going on behind the scenes here, for now, let's just think of our logical operators as returning a true value or a false value. Let's say, for example, you want to evaluate whether certain products have an average greater than 350 in sales per month. Let's create a column called bestseller. Let's say that a bestseller is a product with average sales greater than 350 per month, yet let's use a logical operator to evaluate this. We hit equals, go over to the average monthly sales because it has to be greater than 350 in order to be a bestseller, hit the greater than sign, and then type in 350. We hit enter, and we can see that this is said, our logical operator has said it is not a bestseller because it does not have more than an average of 350 in sales per month. Using the fill handle to drag this down, we see that it does correctly categorize eggs and bread as the two bestsellers in our shop. Moving on, logical operators are very often used with logical functions. Logical functions evaluate one or more logical operators and return a value. Unlike logical operators, logical functions can return values other than Booleans. They can return any value. This is kind of the point of using a logical function. We need functions to turn a Boolean value, true false, into something useful. So we have our bestseller calculated, but I don't really like how the true and false appear on my spreadsheet. Say I wanted my Excel spreadsheet to tell me whether something is a bestseller or a secondary product based on whether it is a bestseller. Let's add a column sales category. Let's try our first logical function. We're gonna use the formula if. So we type equals if, and if we look, it takes three values. The first argument is a logical test, which is our Boolean value. And then the second argument is the value if it gets true. And the third value is the value if it gets false. And so now, in order to calculate this, I'm gonna use the logical test that I did in the previous column, just by referencing that cell. I'm gonna hit comma. And then if it is true, what do I want to return? Well, I want to return bestseller. And if it's false, I want to return something like secondary, because it's a secondary product in my grocery store. Close the parentheses, hit enter, and we can see, using the fill handle to drag it down, that our logical function correctly printed bestseller for all the ones that hit the bestseller criteria. I want to introduce two or more logical functions that I find very useful in the analysis that I do. It's the or function and the and function. If you are a programmer, you should already be familiar with these concepts. Feel free to skip to the next lecture. Let's try it. Let's type equals or, and we can see that Excel defines the function for us. It says checks whether any of the arguments are true and returns true or false. Returns false only if all arguments are false. So every single thing in the or statement must be false in order for it to return false, but if even a single one of them is true, it will return true and is the opposite. It checks whether all arguments are true and returns true if and only if all arguments are true. It will return false if a single one of the arguments returns false. Let's take it for a spin. Let's say we wanted to improve our definition of bestseller by considering an item of bestseller, only when average sales are greater than 350 or the percentage of sales is greater than 25%. Let's type equals or, let's do our cell reference once again, so average monthly sales greater than 350 or separating our or statements by a comma, our percentage of sales is greater than 20%, 25%. Sorry about that. Enter and we drag it down and we can see that eggs and bread remain our best sellers. So when I look at this data, I see that a product like eggs is categorized as a bestseller based under our current definition, but doesn't necessarily belong in that category. So let's improve our definition of bestseller even further to only products that both have more than 350 in sales per month and more than 25% of our total sales. I could write equals and average monthly sales greater than 350 comma, percentage of sales greater than 25%. Hit enter, use the fill handle to drag it down and we can see that bread is now correctly categorized as our only bestseller. I like this definition better, so let's stick with it. However, there's one last thing I want to improve. I want to combine these two functions in order to be neater in one cell. And so let's fix that. What we can do is we can just take the and statement, copy it, go into our other formula and instead of referencing cell K2, we just paste the and statement, hit enter and we can see when we use the fill handle that Excel is still properly calculating whether something is a bestseller or a secondary product. You can use compound statements in this way, especially when it comes to logical operators. Now we don't need column K, so I'm gonna delete column K by clicking the K, right clicking on it and then choosing delete. That's it for logical operators. There are a number of text functions in Excel. Text functions are functions that you use to edit text inside of Excel. I'm gonna only cover one in this lecture so you know that they're there in what they do but I encourage you to read the documentation I've linked in the lecture notes. Let's say we want to make all of our row items lowercase. Let's first add a new column by clicking column B to highlight the entire column, right click on column B to open up this menu and then choose insert. This should give you a new column where you can edit your new data. Next to the first item, rice, I'm gonna type equals lower and we can see that Excel defines this function as a function that converts all letters in a texture into lowercase, which is exactly what we're trying to do. As you can imagine, there's also an upper function which makes each letter uppercase and a proper function that just capitalizes the first letter of everywhere. Okay, let's open the parentheses and choose the cell to its left. Let's close the parentheses hit enter. We can see that Excel properly lowercase all of the elements. Now we're gonna use our fill handle to drag it down. Okay, now that we've gotten our row headers to the way that we want them to be, let's just copy them and paste them into where the original data was. But wait a second, Excel throws this ref error. What does this mean? Well, it means that the cell that you are trying to reference does not exist. Why did this happen? Well, it's because this cell was referencing A2, but then we just replaced the value of A2. And so it no longer works as intended because it's referring to a formula that is undefined. So instead, I'm gonna redo by using control Z. Instead, I'm gonna copy again and this time I'm gonna do a special type of paste. Let's go up to our home ribbon, click the dropdown on paste, and then we're gonna choose paste values. What this does is it only pastes the value inside the cell instead of the formula that we created. If we do this now, we can see that the copying and pasting action worked. Okay, now let's remove this row by clicking on B, right clicking on it, and then choosing delete. And there we have it. We've properly made all of the row headers in our Excel table lowercase. Let's say I wanted to generate an individual report for one of my grocery items. Let's generate a simplified sales report just for our beans. I would need to use a data manipulation function in Excel to easily pull this off. Let's scroll over to the right of the table so we have some space, click equals transpose, and we can see that this function converts a vertical range of cells to a horizontal range. In other words, it takes cells that are vertical and makes them horizontal or takes cells that are horizontal and makes them vertical. So let's write this, clicking tap to finish it off, and I'm gonna generate a sales report for my beans. Excuse me, my rice. Now we'll go like this, hit enter, and we can see that Excel properly transposed the data for us. Everything that was vertical is now horizontal and vice versa. However, just like we've noticed in the past, our data types don't necessarily transfer. These are our dates, so let's fix these. Highlight them, go up, click short date, and then also this needs to be in a dollar amount. So I'm gonna highlight these, and then this is the shortcut to create something into a dollar. Let's decrease the decimals on that, and then finally we see some spillage over the cell in the left side of the table. Let's highlight the entire table and click wrap text to ensure that everything is visible. Let's quickly go over a date function in Excel. Once again, I just wanna show you that these functions exist and give you an example of what they can do for you. I'm not gonna go over all the date functions. Let's say for our report, I wanted to add in a timestamp so my colleagues know when I generated this report. Let's add a new row, timestamp. In the next cell, I click equals and I'm gonna use the function today. This function returns the current date formatted as a date. There are no arguments that we need to enter into this function so we actually just enter an open parentheses and then immediately after a closed parentheses. We hit enter and we can see that Excel has properly put in the current date. In this unit, we're gonna work on modifying our worksheet to be neater and more readable. We will begin working a lot more with the ribbon since many of the formatting and cell commands are held up there. Like I have emphasized earlier in the course, to become an Excel power user, you must be committed to learning shortcuts and this is why I need to teach you how to use the alt key as a way to access ribbon functionalities. Using the alt key is the fastest way to access the ribbon and will help make you an Excel power user. If you are working on a Mac, you should probably ignore this lecture because Macs do not contain the alt key and do not have an alternative to the alt key. This is one of the main reasons why Excel is better used on a Windows computer. Okay, let's dive into it. If I click alt, I see all of these letters pop up in the top of the screen. These letters represent the letters that you need to enter in order to choose this tab in the ribbon. H stands for home, N stands for insert, P stands for page layout, and so on. So if I press N, for example, it should open up the insert part of the ribbon. Now Excel has assigned a letter to every functionality inside of the insert tab. I can use these combinations of letters in order to choose what functionality in the ribbon I want to use. Okay, to exit, let's press escape. Let's say we wanted to make our table a little neater and say I wanted to align the center, align all of the content in the center of the cell, especially for these column headers. Let's go ahead and highlight all of the column headers, press alt, and then go to H because this is where the align command is. This is the align command, and we see that it corresponds to the letter combination A than C. We can see that Excel has properly aligned all of our column headers to the center. One of my favorites is alt and Q, which you can see up here, which allows you to search the entirety of Excel for any functionality. So you could go in here and you could enter bold, and we can see that if we just forgot that bold was in the home part of the ribbon, we can also just search it by name. In the remainder of the course, I will continue to show you how to do everything with a mouse, but also the shortcut version of it. If there are any shortcuts that I missed, please let me know in the reviews or the comments. Any new and innovative shortcuts would help make the course better. Let's say we wanted to increase the font size of our headers. We could highlight the cells that we want to change, so let's highlight these, and then go to the ribbon and click this button up here, which is the dropdown menu for font size. Let's click it and maybe choose something like 14. I'll do the same for my products here in the rows, except maybe something a little bit smaller, maybe something like 12. If you wanted to access these via a shortcut, you could go Alt, H, F, S, and then you can manually type in the font size that you want. We would never want an 145 point font, but let's stick with 14 and hit Enter. Okay, now some of these row headings are becoming a little bit too tall. Let's try to make this a little bit more visually appealing. As we have covered earlier in the course, you can simply drag the columns wider to make them wider. Let me show you what I mean. You can grab the edge of a column and move it this way to make it bigger or move it this way to make it smaller. However, sometimes you might want to change all of the columns at the same time. So we would highlight the columns, holding down Shift and clicking to where we want to move the columns from, and then moving one of these will move all of the other ones. However, this is a relatively slow way of doing this operation. I'm going to undo this and show you a quicker way. Once again, let's highlight all of the cells, holding down Shift to K. Let's go to Home, Tab of the Ribbon, Format and Column Width, or you could use the shortcut Alt H O W. It'll bring up this prompt to enter in a size of the width of your cell. Here, you can hard code the numerical value of your column width. Let's increase our width to something like 13. That's much better. However, we can see that the cells no longer fill the white space above it, so we want to fit row height now. We can do this using Excel's Auto Fit feature. With the cells that you care about highlighted, go to Home, Format and Auto Fit Row Height, or you can use the shortcut Alt H O and A. Excel has automatically changed the row height to match what the data inside was representing. Say I no longer cared about the minimum monthly sales column. Here's the basic but slow way to remove this column and reformat it. I could first highlight the data and delete it by hitting Delete. But now I have this white space between the two sides of my table. So I have to cut the right side and paste it using Ctrl V to be jointed with the table. However, this is the slow way to do it. Let me show you a better method. Let's first undo back to the previous state using Ctrl Z, which is the shortcut to undo. Instead of deleting the column in the table, we can delete the entire column, which will simultaneously remove the data and transpose the data. To do this, we can highlight the column by clicking the letter, which highlights the entire column, right click and then choose Delete. This same can be done with rows. Say I wanted to remove Cale. I could click the row to select the entire row, right click and then choose Delete. However, this is the slow way. So let's undo using Ctrl Z and I'll show you the fastest way to do this. The shortcut to highlight a column is Ctrl plus space. This is a super useful shortcut that I use all of the time. And now to delete the column, I just have to use the shortcut Ctrl and the minus sign, which automatically removes the column. Rows are extremely similar. Say I wanted to remove the Cale. Instead of Ctrl plus space, which is the way that we highlight a column, highlighting a row shortcut is Shift plus space, which automatically highlights the entire row for you. And to remove this row, it's the exact same shortcut. It's Ctrl plus minus. We can also see that Excel recalculated our total sales. It's a very intuitive tool. Inserting columns and rows is very similar. Let's say we wanted to add a row and a column to our table to separate it a bit from the edges of our spreadsheet. The slow way to do this would be to click on the column, then to right click on the column and choose Insert. Same with the rows. Click one, right click and Insert. This is kind of what we wanted to look like, but I want to show you how to do it the fast way. Let's undo using Ctrl Z. To select the first column, I use that same shortcut, Ctrl plus space to select the column. Then I use the shortcut Ctrl, Shift, and the plus equal sign that is typically located in the top right hand corner of your keyboard. We see the new column has been inserted into the spreadsheet. The same thing goes for the rows, except instead we're going to use Shift plus space to select the rows, and then use that same shortcut to Insert, Ctrl, Shift, and the plus equal sign. Now that we know how to insert rows and columns, let's move on to learning about borders and background colors. I want to put a border around our tables to make them visually more appealing. I can highlight the entire table using Ctrl, Shift, arrow key method that I taught y'all in the previous lectures, but there isn't even quicker way to do this operation. I can use the shortcut Ctrl A, which is the shortcut to highlight all, hence the Ctrl plus A. We can see that Excel automatically selects the entire table for me. To put the border around this table, I go up to the ribbon and this is my border. So I choose the drop down menu here, and then I choose outside borders. As we can see, Excel put an outside border that clearly separates this data. Let's put an outside border on the summary table as well. This time, let's use the shortcut. Highlight the cells and then use Alt H for Home, and then we can see border is B, so B, and then outside is S, so I hit S. Now, our two tables have some really nice separation. On our top table, let's put a bottom border on our column headings to make them look a little bit more visually separated. This time, I'm only going to use the shortcut. I highlight all the columns, and then I use Alt H, B for border, and then O for bottom border. Now, let's put a right border on our rows to separate them from the data. Highlight the rows and then use Alt H, B, R for right border because I want it to have a right border to separate from the data. There are a variety of border types that will be helpful for formatting. I highly encourage you to explore all the options. Now let's make this look even better by adding some color. Stay selected on your headings, and use a color that kind of stands out. Maybe something like light yellow would work. Let's do the same for our rows. Let's highlight and then choose something that will visually separate them, so maybe a nice light green. Now, our tables are quite a bit more readable. To make my table even more readable, I'm going to use some font formatting commands. Say for example, I want to bold all of the column headings and italicize all of the grocery items. Let's first do the columns. I can highlight all of my columns and click the bold command in the ribbon or Excel's telling us we can use control plus B. Let's italicize all of our grocery items. Let's first highlight all of our items and we can use the italicize button or Excel's telling us we can use control plus I as the shortcut. Next, let's give our table a name. Let's name it grocery store sales by quarter. Let's now underline this to make it more visually separated from the rest of the table. I could use the underline command or I could use control plus U. I'm going to do the same for the summary table. Highlight the title and use control plus U to highlight it. Now, let's increase the font size of these two titles in order to have more visual separation. We first highlight the cell that we want to change the font size of then we go up to our font size dropdown selector. You can either enter in a number directly like 14 or you can use the dropdown and choose. I'm going to choose 20. Now, let's change the font size of our summary title, table title. I'm going to use the shortcut this time. Let's use alt H FS and this allows us to directly enter a value into our font size. So I'm going to enter 14 and hit enter. While these tables are both, while these titles are both helpful, there's a better way to present this. We can make it more visually sensical by using a feature called merging cells. Merging cells means that you are going to take two or more cells and combine them. Let's highlight the entire row of cells above our big table and then click merge and center. This action does two things. It first merges all of the cells, combining them into one and then centers the content. Let's do this for the summary table as well. Let's highlight the two cells above the summary table and let's use a shortcut this time. So it's alt H M for merge and center and then C. Finally, let's align our content. Aligning content refers to where you want your data or information to be inside of the cell. I personally like everything to be centered, so let's do it. Let's go back to our main table and highlight it using control plus A. Now, going up to the ribbon, you can see that these six buttons are the alignment buttons. Top align, middle align, bottom align, left align, center, right align. I want to center, so let's click the center button. Now, all of our data is in the middle of its cells. Let's do the same for the summary table. Let's highlight it, but instead use the shortcut, which would be alt H for home, A for align and C for center. Now, I want to middle align all of my column headers. I highlight all of my column headers and then I'm going to use a shortcut alt plus H plus AM for align middle. While we won't go over every single one of the alignment commands in class, I highly recommend playing around with it so you can learn more about what they do and how they make your cells look. In the next lecture, we're going to go over hiding and unhiding rows and columns. Say we wanted to simplify our table, but we want to keep the data because it's used in some of our calculations. For example, let's say I want to hide our first quarters numbers, but I want to keep this data in my calculation of average monthly sales and total sales. We can do this in a couple of ways, but I'm going to show you this simplest way first, which is first you highlight the column, right click, and then choose hide. This will hide the column as you can see. It's no longer here, but it's still included in our calculation of total sales and average monthly sales. To unhide, all you have to do is right click on the area where it's hidden and choose unhide and it should pop right back up. However, there's a faster way to do this. Instead, let's use the shortcut control plus zero while highlighted on the column that we want and boom, it does it automatically for us. The same is true with rows. If I wanted to hide a row, I can click it to highlight the whole thing, right click, and then choose hide. Or to unhide, I can right click on the area where it's hidden and choose unhide. Whoops. Or the shortcut to do this would be to be highlighted on the row that you want to hide and then use control plus nine, which automatically hides the row. Now, there's one other way in which you can unhide rows and columns. So before we do that, let me just hide a few more of these rows and columns. Now, I only have corn left in my table. If I wanted to unhide all of them at the same time, the easiest and fastest way would be to highlight everything by using control plus A, going up to format, going hide and unhide, and then unhide rows. All of the rows should pop back up. Please download the file attached to this lecture. It includes a fresh new dataset that we will be using for the next couple of sections. Here is our sales ledger. What is a ledger? It is a recording of all of the transactions, in this case, sales that we have made. We can see that for every entry, there's a category, a date, quantity sold, a price, and a revenue column. All products have a category and for the purpose of this exercise, there's only four categories, canned, dry, poultry, and produce. As we can see, this is a lot more data than we were previously working with. So much data that when we scroll, we actually lose track of what the column's headers are. I forget, is this revenue or is this quantity? Well, there's an Excel feature that can help us with it. Let's scroll back up to the top. We're gonna do something called freezing rows, which allows us to freeze rows in our view. Let's try it. We want to freeze this row and this row. To do this, we select the row that's below those two rows that we want to freeze, then go to view, freeze panes, freeze panes. Now, when we scroll, we can see that those two rows are frozen in place, which makes it a lot easier to read this data as we're parsing through it. Right now, our lecture is listed in order of transaction date. Let's say we wanted to sort it by the items so we can see the purchases of each unique item grouped together. All right, we're restarting this. Okay, now we're ready to go. Boom. Right now, our ledger is listed in order of transaction date. Let's say we wanted to sort it by an item so we can see the purchases of each items grouped together. The easiest way to do this would be to right click, choose sort, and then sort A to Z. We can see that it is now in alphabetical order and has grouped all of the items together so we can look at the transactions holistically. However, I do not believe that this approach to sorting is as precise as we might want it to be. Let me show you an example. Let's say we entered a random data point in here. If we go to sort it again, for example, by oldest to newest on the date, we can see that the random data point has moved with the rest of the data. This is not necessarily behavior that we want to demonstrate. So let's undo and let me show you how to avoid this. There's two ways to do this. The first is to give a white space between the table and the random data point. So now when I go to sort it by date, oldest to newest, we can see that the random data point stays in place. However, there's another way and it's my preferred way. And it involves putting filters on all of the columns that we want to sort in the first place. Let's first delete this random data point. To put filters on our table, we can highlight all of the columns of our table, go to the home tab of the ribbon, choose filter and then filter. The shortcut for this is alt H as F. Now we can click on these small arrows and we can see that we have the ability to sort it. And we can see that the sorting only will happen on the ones that have the filter in the first place. So if I were to re-enter in this random data point and then go ahead and try to sort it by item again, we can see that the random data point has stayed in place. Okay, let's delete this extra data point. Our list is currently sorted by product in alphabetical order, but I would actually also like to group it by category. This is where multi-level sort comes into play. Multi-level sorting just refers to using more than one sorted column. It allows you to have a second, third, fourth, et cetera, number of sorts on your table. And every time you add it, Excel will maintain that order that the previous sorts had already set. It might be a little bit confusing, so let me show you what I mean. As I said, currently our list is in alphabetical order, but when I sort by category A to Z, we can see that the alphabetical order within each category has been maintained. So when you sort by two things, Excel will maintain the order if it can. There's a number of other ways to sort an Excel list. For example, if I highlight a few of these items with the paint tool here, I can now sort this by color, by choosing yellow, and it brings the sorted cells to the top. You can also sort by conditional formatting, but that's not yet a topic that we have covered. In the next lecture, we're gonna learn how to use Excel filters. In the previous lecture, we already placed filters on our table. You can see that the filters are on because we see these small little arrows next to all of our columns. As a reminder, you can put filters on your columns by highlighting the columns, going up to filter, and then choosing filter. The shortcut for this is Alt-H-S-F. Say we want to filter our data to only include data for chicken thighs. Let's click the little down arrow, and we can see that all of the items are currently selected. Let's click select all to unselect all, and then choose chicken wings. Click OK, and we should see only items that are chicken wings. We can filter a selection that is already filtered as well. Let's say we wanted to filter our selection to only show chicken wing sales in the month of February. We could go to the date column, and we can see that Excel is smart enough to make this a date filter, and we can unselect all and choose the month of February. Click OK, and now we see only the chicken wing sales that were done in the month of February. Let's clear our filters to get back to the full list. The best way to do this is to click on the down arrow, choose clear filter, and let's do that again for chicken wing, clear filter. Okay, so there's a few more concepts that we need to cover in filtering. You can filter on multiple criteria. Say I only want to see chicken wings and chicken breast. Let's click the down arrow, unselect all, choose chicken breast and chicken wing. I click OK, now we only see purchases for chicken breast and chicken wing. However, there's a different way to do this. If we clear this filter, we can go here, use text filters, and use contains. It pulls up this dialogue where it asks you what does it contain, and I'm gonna type in chicken. So it's gonna pull up all of the chicken products. If I hit enter, it has effectively the same result, shows us just chicken breast and chicken wing. You can make these statements compound as well. So if I go back to this text filter in contains, I can say either it contains chicken or it equals soup. And if you click OK, we can see that only soup, chicken wing and chicken breast are the ones that are appearing in our list. One last thing here, if I go in here and I go to my custom filter, I highly recommend that you take some time to poke around because there are a lot of different text filters that you could use. Greater than, less than begins with, does not end with. These complex auto filters make it a lot easier to find data, clean data, and make data more sensible to use. Now that we have started working with larger datasets, I want to teach you all some useful shortcuts. One of the most useful shortcuts is that to open the filter dialogue. So hover over the column filter that you want to open and use alt and down simultaneously. This opens up the filter and now we can navigate it with our arrow keys. So say we wanted to clear this filter that we have had on since last lecture, we can choose clear filter from item and hit the enter key. We can see that it has taken off the filter. However, there's an even better way of doing this. Let's undo. We can just use alt down to open the filter dialogue and then hit see. This automatically clears the filter for us. Say we wanted to only filter by the items that have chicken in it. I'm going to use alt down to open the filter and then use the E key as a shortcut to get down to the search bar. Now we're going to type in chicken and we can see that Excel will only pull in the items that have chicken. I'm going to hit enter and now our filter has worked properly. Let's clear all these filters with alt down and then see. Let's say I only wanted to view purchases from the dry and produce categories. I'm going to open my filter using alt down and then scroll down to the checkboxes. I can select or unselect one or all of these checkboxes using the space key. So I will first unselect all by hovering over select all then using the space key to uncheck it. Then I can go to the dry, hit space and it's now checked and the produce and I hit space and it's now checked. I hit enter and our filters have been properly placed on our table. Let's say I wanted to find a specific line of data. If I knew the value, I can use Excel's find feature. Go to find and select in the home ribbon, click find or use control F. Let's say I wanted to find a transaction that took place on April Fool's Day, April 1, 2022. So I'm going to type in four slash one slash two oh two two. I hit enter and Excel will bring me to the first instance with which it happens. I hit enter again, it'll bring me to the next and hit enter, it'll bring me to the next. There is an even more powerful feature than this called find and replace. In order to use it, we just go over to the replace tab of this dialog and let's remove this date for now. Let's say we no longer wanted to call it produce but we wanted to call it fresh produce, produce. To better represent that these items need to be transported and stored in a climate controlled manner. I can go to this find and replace dialog, type in produce which is what I want to find and then type in fresh produce which is what I want to replace it with. Now I can click replace all. We can see that Excel's telling us that there's been 56 replacements made. I'm gonna click okay and X this out and now we can see all of the ones that used to be produce are now fresh produce. A pivot table is one of Excel's most powerful tools to summarize and simplify a data set. However, it is much harder to explain a pivot table than to just show you what it is. So let's dive into it. Let's say that you're wondering how sales are doing overall in the grocery store. The best way to quickly find this information would be to use a pivot table. Let's highlight the range of data that we want to summarize. In this case, it's the entire sales ledger. So let's select the entire sales ledger, go up to the ribbon, choose insert, pivot table from table range because we just selected a table. Click okay and now we can see that Excel has created a new tab and has created a pivot table for us. First, let's label, relabel this tab as overall sales because that's what we want to find in this tab. Let's take a quick look at our pivot table fields dialogue. This is one of the most important things in creating a pivot table. We can basically drag any one of these values into any four of these boxes and they all represent different things. Filters represent what we are filtering in and out of our table. Rows represent what are in the rows of the pivot table. Columns will represent what are in the columns of the pivot table. And the values represent what is the convergence of the columns and the rows. Let's experiment with it. Say we wanted to look at month by month sales to gauge our overall performance. Let's drag and drop date into rows. We can see that Excel is smart enough to split it out into months and the individual dates that the sales took place on. And if you are to click one of the plus signs on this column, we can see that within each month you can press plus and expand and see every date that a transaction took place. However, since we only want to look at month over month we actually want to remove the dates for now. So we drag and drop date out of the rows and now we're left with just our months. Now we want to look at the sum of sales inside of each of these months. So let's take revenue and drag it into our values. When we drop it in here, we see that Excel has changed the title to sum of revenue telling us that Excel is summing or adding up all of the values in each month. And we can see that this is properly happening in our actual pivot table. And that's exactly what we wanted our table to represent in the first place. There are many other types of aggregate functions that you can use in a pivot table. To see them all click on the sum of revenue, click value field settings and you can choose any of these as the aggregate function for your values. You can count, you can average max min and while I won't cover all of these in our lectures, I highly encourage you to play around with all of the different aggregate functions we could use. For now, I'm just gonna show you one other one so you know what it looks like. So let's click average and click okay. And we can see that now Excel is averaging the revenue of each purchase inside of the month of January. Let's switch it back. Now that we've aggregated the data, we want to visualize it to make it easier to look at. Since this data shows us data by month, I think that a bar chart would be the best to visualize it. Let's try it. Let's highlight the data we want to put into our table. Let's exclude the grand total because it'll make everything else look out of proportion. Now that we have our data selected, let's click insert and choose this icon which represents a bar chart and then choose a 2D column. We can see that Excel has added in a chart for us. And let's label this chart to make the data look a little bit clear. Let's change the name to something descriptive like grocery sales by month. Let's also update our access labels. Currently we don't have any access labels. So we have to turn them on by going to this green plus sign and clicking access titles. We can see that we now have access titles. Let's update the x-axis which is our categorical axis to month and our y-axis to sales in parentheses a dollar sign to let people know that we're talking about dollar amounts. And now our chart's looking pretty good. Adding charts to Excel is just that easy. In the next lecture, we will go over intermediate pivot tables. Now that we have insight into our overall sales numbers, let's dive deeper. One way we could dive deeper into this analysis would be to look at the sales breakdown by category. Remember the category? Each product is in a category and there's only four, produce, dry, poultry and can. Okay, let's build a similar pivot table as before. First highlighting our ledger, going to insert pivot table and from table slash range hitting okay. Let's rename this into something like sales by category. Hit enter. And just as before, let's drag date into rows, remove date, so we're only looking at the months and then drag revenue into values. As of right now, this table is exactly the same as the one in the overall sales tab. However, since we wanna look at it by product category, let's drag and drop category into the columns. Our table has changed quite drastically. The table no longer summarizes the overall monthly sales. It shows us sales by month, by category. For example, in January, canned goods did $38.20 and in February, did $70.77. To make this table a little clearer, let's remove all the totals since they're not very useful to us at the moment. Let's click here, going to pivot table design, clicking grand totals and then choosing off four rows and columns. Now our table is a little bit cleaner. Let's cover one more concept in pivot tables. We can filter a pivot table in the exact same way that we would filter a regular table. Let's say we only wanted the sales numbers for sales of quantities greater than two, meaning purchases of multiple of the same items. Let's drag and drop quantities sold into the filters column. And we see a filter pop up here above our pivot table. Let's click and then choose select multiple items and this now looks exactly like a regular filter. And I unselect one because I only want purchases where the quantity sold was two or greater. So I unselected one, hit okay, and now our table has updated to reflect this. Let's say we wanted to visualize this data. Let's first remove the filter to have all of our data and bring it back into the pivot chart. So let's click, drag, and drop. This data would be best visualized using a line chart since there are two categorical variables, month and category. Line charts are frequently used to find patterns in data. So let's highlight our current pivot chart, go to insert, choose this icon here, which represents a line chart, and choose a 2D line chart. Excel has populated this chart with date on the X, revenue on the Y, and each line representing a different category. Let's relabel this chart. Using the green box, we have to add a chart title and chart axes. So let's name this category sales by month. Let's name the X axis month, and the Y axis, revenue, dollar amount. Okay, now that we have our chart in place, as an analyst, you always need to be asking so what of the data that you see. So let's look at the chart and draw some basic conclusions. We can see that February was the weakest month for nearly all sales. We can also see that dry goods and poultry are some of the largest sellers. So it might be good to expand our offerings of poultry and dry goods.