Master Microsoft Excel: From Beginner to Intermediate!

1. Intro: Hi everyone. My name is Peter. Welcome to my class. In this class, I'm hoping to take you from Excel beginner to an Excel intermediate level. In this class, I'm going to present several topics. I'm going to talk about them and explain how they can be useful to you in your daily life when working with Excel. First of all, in order to get started, I'm going to talk about some basic features in Excel so that you can be able to get familiar with Excel. Then I'm going to talk about data and how to work with data in Excel, like for example, working with texts and different data types. And how to control this data efficiently and easily to improve the quality of your work. So I'm not going to only talk about how you work with the data, but also how you move the data around efficiently. Then I'm gonna talk about working with formulas because a very useful and needed feature in Excel is the formulas. And once you know how to use them, you'll end up always finding ways to use them even more. Then I'm going to talk about moving those formulas around. Because knowing how to move these formulas can help either copy a formula or move it from one cell to another to save time on rewriting it. So you don't need to rewrite it again. You just need to either move it, copy it in case you already had that formula in your workbook. Then I'm going to talk about the different type of saves in Excel. Because Excel offers options to save your sheet in different types to match your needs. Like for example, you can save it as a PDF and send it for a client, for example, in case they need to design it, you know, in case you are sending a bill of materials, for example, that you wrote with Excel and you would like them to sign on it. And so you would send it as a PDF, not as an Excel sheet. Then I'm going to talk about working with functions. Because line formulas, this is a very important feature to reduce the amount of work needed to get a certain desired result. Our present also some of the common functions and explain them so that you understand the logic behind it. And you can move from there by creating your own functions. To make your work stand out and be more understandable NLP link and to differentiate the data you want from other data, I'm going to explain to you how to format text and cells, how to format rows and columns, how to add conditional formatting. And then I'm going to talk about the use of charts and graphs and how to manipulate them with conditional formatting so that the data you are presenting as well highlighted and displayed for the reader. Then I will explain how to prepare your workbook for printing once you are done and add headers and footers to your workbook so that you can differentiate the pages when they are printed. Because when they are printed, the pages are not numbered. So it's not like when you are using it on a computer and you will not be able to know which pages which. And this is very helpful to add headers and footers. Then I will display my personal way on how to create a sheet to monitor monthly spendings, which I personally use daily to monitor how much I spent, how much I saved on a given month, how much I could save the following month, and so on. And I'm pretty sure it could be pretty beneficial to you once you get to know how to use it. I hope that you are like this class and I will see you on the next lesson. 2. Getting familiar with Excel: In this lesson, I will introduce Excel and its components. Not only to make it easier for you to understand further lessons, but also so that you can use these terminologies in your daily life while discussing Excel specific things so that you and the people around you can understand each other's while working with Excel. Now, first of all, as you can see here, is whole Excel file is called a work book. And this workbook contains several elements that we will talk about. Now here, what you see here, everything that's white within sheet one here at the bottom of the screen, That's a worksheet. Now, if I would like to create another worksheet, I just click on the plus button and then I have another sheet. And everything I do on this sheet is independent from the other sheet unless I decide to link them to each other's. Now, here, as you can see, we have columns. These are columns and these are rows. And within the column and the rows we have cells. So here for example, if you'd like to refer to the, to this cell, this cell would be a one because column a and rho one, as you can see here, it says a one. Now let's say we have already an Excel. Let's say we have this excel sheet. Here. As you can see, we have a table. This table has these elements here. You have name, employee code, employee number, and so on. These elements in this text would be known as the label because each one of them represents the data under it. So name is, is kind of a description of what we will find within this column. Employee code is describing what we have underneath it in the column, and so on. Now let's go back to this blank workbook. And let's talk about this area here. The area here, that's the ribbon group, and above it here, that's the ribbon tabs. So the ribbon tabs would vary from insert to review the data and so on. Other than that here, this bar here, that's the formula bar, I'm going to discuss it later in more details. But this, this bar here is usually used for writing formulas. Other than that, Excel also provides some screen tips whenever you'd like to know what a button would do. All you need to do is hover over, for example, this button. And as you can see here, you have a screen tip and you have an image, and you have a text that explain to you what this button would do and also represents it to you. It's beneficial because sometimes we forget what a button does. Sometimes we need to know what this button does and we would like to see what it does before trying it on, and it would save us time. So by simply hovering over it, we can know what it does. Now, let's talk about navigating in Excel using the keyboard, which is very beneficial because while you're typing, it makes more sense to navigate. With the keyboard, rather than stopping and then using the mouse and then coming back to the keyboard. Now here I will place my mouse over this cell and I will click on it. Now this is the active cell. Now if I press on the right arrow, as you can see, it will move to the right cell. And if I press on the left arrow, it will move to the left cell. Now if I press tab, it will always move forward within the cell that's in the next column. And if I press on shift up, it will always move backwards to the cell and the column before the active cell. And if I press on control home, it will always take me to A1, always, always. And if I press on control, and it will always take me to the intersection between the last row and the last column that contained data. In this case, we don't have any data, but let's open an Excel workbook that has data. And for example, let's say I put my mouse here and I'd like that this cell is the active cell. Now if I press on control end, as you can see, I am at the intersection between the last row and the last column that contains a data. Because as you can see here, there are no more rows and here there are no more columns. And that's how you can navigate easily through the Workbook by using the keyboard. Now, let's say you have this workbook here and you decide that you'd like to use a template for your workbook. And it's more beneficial sometimes because let's say you don't have time and you'd like to open a template that already has, for example, labels, formatting styles, formulas. It's, it makes it easier for you to already like directly start working on your workbook while having plenty of presets when it comes to designing formatting and formulas. In order to do so, you have to click on File and then you click on New. And then here in this section here, you have plenty of templates. You can even search for templates online using this text box here, you can find whatever you would like. Now here for example, we have several templates and if we choose, let's say weekly course schedule, what would happen if we click on it one time? What would happen is that we would see a preview of it. And if we click on it two times, it would automatically created for us. And now as you can see, this template has its own styling, its own formatting, and so on. And you can directly start working on it. For example, you'd like to change this. You just click on this cell so that it becomes the active cell. And then you can simply start typing to change the value and so on. And that's how it would work when it, when you would like to create a template. And that's all when it comes to starting with Microsoft Excel. And I'll see you in the next lesson. 3. Working with Text, Values, and Lists: In Excel, we can either be using text or values. Now values could be divided in either numbers or formulas and functions. Now, for this lesson, we are only going to talk about tips and knowledge, about numbers and text to understand them better and to know the best and most efficient ways of using them. So in this lesson, I will mainly focus on the text and the numbers to improve your work and to share with you my experience while working with this form of data. You can edit the text by double-clicking on the cell. And then you would have an insertion point within the cell so that then you can type whatever you'd like. And the difference between double-clicking on a cell to type and just clicking on a cell and then starting to type is that when you double-click within a cell, you can make changes to the text that's within the cell. And when you click on a cell and then you start typing, you will just replace the content of that cell with the new content. If I write more within this cell, and then I remove and I select another cell. As you can see, the overflowing text has been truncated within the cell and it has been hidden. And if I write on this cell here and I write a long text, as you can see, it will overflow other cells. The difference is that here after this cell, we have a cell that contains data, and therefore Excel will truncate the value of this cell so that it doesn't overlap the cell next to it. But when the cell next to the active cell is empty, it will just overflow it and come above it. But that doesn't mean that the values of this cell have been entered to the cells next to it. You can still simply put the mouse over the cell here that you'd like to add values to. You, double-click on it and then you start writing. And as you can see, this cell has been truncated. Now I covered what happens if a text is longer than the actual cell, but what would happen if the value is longer than the actual cell here? In this case, let, let's write bigger. What would happen? I would get all of these symbols and I cannot tell you how many times this has happened to me that I had an Excel workbook and then I opened it. I was like so happy with it because I made some calculations and I added some integers and some numbers so that I can have some calculations. And then all of a sudden I saw this and I'm like, okay, my Excel is broken. And then I started Googling it. And I wanted to understand why am I having these symbols. I thought maybe I'd made a mistake with the formula. And then, as it turns out, when you write a value that's bigger than the size of the cell, this is how it would happen. And in order to fix that, all you have to do is navigate your mouse to the border of the cell and then enlarge it like that. And as you can see, now, you can see the value. Now, if I make The size of the cell shorter. As you can see, a lot of other values will turn into these symbols. Now if you'd like to simply and quickly change the size of the cell depending on the content of the cell. All you have to do is double-click on this bar here at the border of the column. You double-click and as you can see, it will enlarge itself to the longest value within it. And as you can see, this is the longest value within this column. And it's very beneficial to do it that way because it makes your work faster. Now, let's say I would like to create seventh column and it will be called the day of the month. Now, let's double-click here to enlarge it. And let's write Monday. Now, if we would like to write from Monday to Sunday, and we don't have the time to write on each set like Tuesday, Wednesday, Thursday and so on. All we have to do is select the cell that contains the first value of the list, the first element. Then we move the mouse to the bottom right of this cell until we see this plus sign, then we click and drag it down. And as you can see by the tooltip, we have Sunday and that means that when I let go of the mouse, I will have from Monday to Sunday. And as you can see, this has happened. Let's write here, for example, date. Let's say that today, it's a Friday. So let's first of all write here Friday. Let's click on the bottom right and then drag it down. Let's write the current date here. So today is 6192020. As you can see, now here we have a date. And if you look on the ribbon group number, you can see that it has been specified as a date. Now if I put my mouse at the bottom right and I drag it down, as you can see, the days of the month have changed. And also next to it, we have the day of the month as well. So this is very beneficial when it comes to a list and when it comes to things that can be added by Excel automatically like the day of the month and the date and so on. Things that are analysed and are usually known to excel. For example, the calendar in this case. Or if you'd like to add an enumeration, what you have to do, it won't work if you simply just add one. And that's because if you select it and then you drag it down, it will just keep adding 1111. What you have to do, you have to put one and then two. And then you have to select both cells. And then you have to drag down. Then it will do an enumeration. And this also can be beneficial when it comes to writing a list. Thank you for attending this lesson, and I'll see you in the next lesson. 4. Moving data with the Keyboard: In this lesson, I will explain my personal experience and moving data and the advantage and disadvantage of each. I will cover all of this in this lesson and the next one. Because this lesson we'll talk about moving data using the keyboard and the lesson directly after it. We'll talk about moving data with the mouse. Now for me, my personal preference would be the keyboard because it makes your work go faster. But to each his own. And what I will do is I will share my experience with both of them. First method to move the data from one place to another is by simply copying and pasting it the same way you would copy and paste something outside of Excel. So for example, let's say you have this table earning and you'd like to copy the header and maybe five values under it. So instead of retyping this data, what you can do is just selecting it and then you press on Control C, and then you select where you want this data to be pasted. Let's say you want to create a table, for example, that only represents the first five elements by selecting the headers as well. So what you can do is simply select, for example, this part of the table and then paste it here by selecting this column. And then you take, for example, these five, and then you paste them here, and so on. And you can easily just take this whole area of data per P8 and then paste it here. And as you can see, now you have this table of the first five elements. Now let's undo what we just did. And I'm gonna tell you why this method has benefits. Let's say you want the data to stay in the original table, but you also want it to be present somewhere else and you don't want to retype it. All you do is just copy it and paste it somewhere else. And that way you will have it in two places at the same time. Now let's say you don't want the area of the data to be here anymore and you want it to be somewhere else. But you don't want to retype it, you just want to copy it. And you want to save time on copying it and pasting it and then having to delete it from the original place, all you have to do is select the area that contains the data. And then you press on control X, cut it, and then you select where you want to paste it and then you paste it there. And as you can see, it has been cut from the original place and pasted somewhere else. Copying data is very beneficial because it preserves the consistency of the data and it's very time-saving when it comes to having to retype the data again and again. And that's why mastering the idea of copying the data is very important so that you can save yourself some time on rewriting the data. Again, as you noticed when I copied this part of the table, the formatting was copied with me as well. I will show you, as you can see. One more benefit to copying the data is that it preserves the original formatting. As you can see here, the same formatting that this table has, has been copied here as well. It can not only save you time on copying the data, but on also copying the formatting. Now let's say you don't want it to copy the formatting, but you just need the data. So what you can do, you select the data that you would like in our case, for example, these two cells than we press on control C. Then we select the area where we would like to paste this data. Then we hover the mouse to the clipboard here, we click on this button here. And here we have all the pasting options that we can use. Light, for example, pasting it without the formatting, pasting only the values and so on. You can check this out yourself and I'm pretty sure you will find the right pasting option for you. Now there are two other ways to format a certain cell. It's either by using a format painter or you can use an individual painter. Now, what's the benefit of each one of them? A Format Painter is very beneficial when you already have the formatting inside your worksheet like for example here, let's say I copy the cells and I put them here just for the example. And as you can see, they don't have any formatting. But let's say I'd like them to have the same formatting as the one in sales here. Now I could just redo the formatting myself by going here and formatting them each one on its own. But in order to save time in fermenting individually, when I already have the formatting available for me in the sheet, what I can do, I select the text that has the formatting that I want. Then I click on Format Painter. Then I select the text where I'd like this formatting to be applied. So I select this text here. And as you can see now, it has the formatting of this cell here. And of course, when the formatting is not yet there in the sheet, I could manually create this formatting on my own so that later, for example, if I'd like to reuse this formatting, I can just use Format Painter and copy it. 5. Moving the data with the Mouse: Another method that I sometimes use is drag and drop. Like let's say you'd like this, for example, cell here, and you'd like to drag it and drop it somewhere else, rather than using the control X, control V, what you can do is just select the cell and then move your mouse at the border like this until you see these four arrows. Then you click and then you move. For example, let's say here, then you let go of the mouse and as you can see, it has been dragged and dropped. Let's say you'd like to drag and copy a value. All you have to do is put your mouse like this the same way as you would do it for a drag and drop. You put it there, then you press on control. And you can see that the icon of the mouse has changed. Now you click and then you navigate where you'd like to paste this value. And now here, for example, you let go and as you can see, it has been dragged and copied here. Now let's delete this value. Let's drag and drop this value back there. And I'd like to let you know that, you know, the difference between cut-and-paste and drag and drop or drag and copy and so on. It's just a matter of preference, like if you'd like to use control C, control V or control X control V, You could do that since you know you're working with the keyboard, so it's faster and so on. And if you're someone who likes to use the mouse, you can use the drag and drop or the dragon copy by also adding a bit of the keyboard. Now, a major difference between these two is that the, the cut-and-paste is more beneficial when the cell where you'd like this data to be pasted is not visible within the screen. So in this green, for example, we don't see the cells that are down here. We don't see them. So it will be kind of hectic to just drag this and go down here. So we will simply do is just like selected control X and then scroll down and then select the cell that we'd like and just paste it. Now, a convenient way to use drag and drop would be when the move for this data is short and within sight. So that's how you can kind of differentiate between both methods. 6. Moving global data to Excel: Another very important thing that you need to know, let's say you have this Excel sheet, for example, this Excel workbook. And you'd like to have data on this workbook that are coming from this actual workbook or from other sheets within this workbook, from Microsoft Word, from Microsoft PowerPoint and so on. Lets say we have data in the second cheat within this workbook. Let's write Peter. Now. Let's select this cell. Let's press on control C. Now we have copied it. Now let's go to Microsoft Word. Now here we have a document. Let's copy, for example, spices shaped history. Let's control C. Now let's come back to Microsoft Excel. Let's go to our sheet. Now here in this sheet, let's click on the clipboard here on the more options. And as you can see now, we have a clipboard here. Here we have the Peter that I just copied. So I will paste it here by just clicking on a cell to activate it. And then I click on this value here. And as you can see, I have the Peter. And then let's click on another cell to activate it. And I would like to paste the value that I copied from Microsoft Word. And as you can see, it displays that it's coming from Microsoft Word. Now I click on this value, and as you can see, it has been pasted within this workbook. And this is very beneficial, especially when you are working on three different, for example, documents. Each one is opened in a, in a different Microsoft application, but you need to combine this data within Excel. So you need to copy things from several documents within Excel. This is how you could do it. 7. Referencing a copy to another: As you saw for example, here, we copied these values from here. Now if we change a value here, for example, this one, we write 50 here, it didn't change 250. Now they are separate because once you copy and paste a data or a value or anything, they just, they don't have a connection between each others. So if one changes, the other won't change. Now in order to keep a connection between two data elements, all you have to do is, for example, copy this value here. Let's control C. Let's select a cell to activate it. Now, in the paste here and the clipboard driven group, we click on this Paste dropdown menu. And as you can see here, we have the, the other paste options. We have this option that says Paste link. Now when we click on it, it says 30. And if you check in the formula, it says be for now the b is the column and four is the row, and it represents this element. Now, if I change the data in the source 235 and oppress on enter here, it will automatically change to 35. And now this is very beneficial because let's say this is the main table. And then you have many sub tables for this table, and you'd like that everything remains consistent. So whenever you change a data here, you'd like it to also be reflected on the other mini tables or sub tables. And that the way how you should do it rather than doing it manually. Because when you do it manually, there's a high risk of losing accuracy and precision. And thus this is how you should do with And another quick way to paste the link is simply by copying this value, for example, and then right-clicking on the cell where you'd like to paste this link. And then you choose this option here that says Paste link in the paste options you selected. And then as you can see, it has been pasted. And that's all for this lesson. And I'll see you in the next lesson. 8. Working with formulas: Formulas are very important than Excel because many of the times we're writing in Excel, some tables, some invoices and so on. And we need to do some calculations and some arithmetic calculations. Now, instead of using your calculator, which is also provided on a, on a computer. We can just calculate things by using formulas on Excel. Formulas can be used for things like, for example, adding, multiplying, getting an average, so on. Now, we need to know that all formulas on Excel must start with an equal. So as soon as you start with an equal, then Excel will know that, okay, this is a formula. Now, in order to enter a formula, let's say in this case we have earning one and earning two for the employees. And we would like that. We calculate the total of the earnings for the employees. Now we can add here, for example, and other label and we can rename it to total earnings in dollars. Now, let's start with the first cell. First of all, we have to press on equal. Now, we started as a formula. Now what we can do, we can either write down the name of the cell that we would like to have within this formula. So in this case we can write F four. F four. And then as you see, it has been selected. Now we write plus. And this time instead of writing g four, we will just click on G4. And as you can see, it has been added here. Now when we press on enter, as you can see, we have the calculation and the sum of this cell, this cell. And as you may have noticed, it's faster to just press on equal and then select the cell, then plus, and then select the other cell. It's faster to do that rather than to type the cell value, each one on its own. And then we press Enter, and there we have it. Now another thing that you can do, let's say you'd like to have the same formula like for all the other cells. So here we are using, I will show you the pattern that, that is happening here. In this formula, we are having F4 and G4. So F4 and G4 in this formula, we have F5 and G5, which is FIFO and G5. So logically Now, if we put the mouse here so that we have the plus sign, we click and drag it down. All the way down. If you activate this cell, you can see that now it's F6, G6, this cell, F7, G7, up until here, for example, F3, G 30, which is this 13030. And now if we continue till the end, you can see that everything works correctly. And now if we continue even further, it will give 00 because the values here are empty. Now let's delete these two cells. And as you can see, this is a very fast way to work with formulas when it's the same formula for the, for the upcoming columns and when there's a pattern for the cells that are being used, as you saw here, we had f for G4, F5, G5, F6, G6. And that's how you can use the autofill feature within the formulas. And that's all when it comes to dealing with formulas in a basic way. And I'll see you guys in the next lesson. 9. Copy, cut, drag a formula properly: In this lesson, I will teach you how to move formulas in Excel. Because based on my personal experience, moving formulas is very important, just like moving data. And I've come to learn of ways to move formulas effectively without losing a single piece of information. This lesson will cover all the techniques that I have found myself using along with their advantages and disadvantages. And then you will get to choose which method fits you best based on your style of work. For example, we select this formula, and this formula targets this cell and this cell. Now, I select this formula, I press on control C. I select the cell where I'd like to paste this formula, and I press on control V. And as you can see now, the value is different than this one. And if you examine the formula, you can see that the formula has shifted one cell to the right, and it would do so whenever. For example, let's say control C here and I do Control V here. Now, if I check the formula, it has shifted, they sell as well. Now copy pasting is for a formula is very beneficial so that you don't have to type it again, but it's beneficial to copy it like this with a relative reference when you have a table. And for example, you're calculating the sum of this cell and this cell. And the formula here is targeting these two cells. And you'd like to also calculate these two cells. So you would copy the formula from here and paste it here. And then the cells will be shifted to the ones next to them. So these ones, and then the formula would work. You could also of course cut and paste the formula somewhere else. Now the cut and paste within shift the formula. It wouldn't shift the cells, it would keep them as is. So if I select this formula and I press on control X, and I select another cell, and I press on control v. As you can see, it has shifted to this cell. The cells that are targeted within this formula are still the same. Now let's put it back in its place by using the drag and drop feature. So I drag this formula and then I drop it here, and as you can see, it will remain the same. So even if I drag it and drop it anywhere else, it will remain the same. And if I drag it and copy it here, for example, it will shift as it shifted before with the control c and control v, and now the cells have shifted. I will teach you further in this lesson how to use absolute references so that this won't happen. Now let's remove this formula and let's talk about pasting a link. You can also paste link C Or you just copy the formula. Then you select the cell that you where you'd like to paste you right-click and then you click on paste link. Now as you see, it has pasted a link to this formula. And now for example, when the value of this cell here changes, it will also change here, even if the formula changes or whatever changes. Now we have a link between this cell and this cell. 10. Absolute vs Relative reference in Formulas: Let's say you'd like to have an absolute reference rather than a relative reference like you saw earlier. What you can do, you can, for example, select this formula. Either you can edit it in the formula bar here, or you can edit it by double-clicking here. Let's edited this time in the formula bar. What you need to do, you need to put a dollar sign before the column and a dollar sign before the row. And then you need to do a dollar sign before the column and a dollar sign before the row and then you press on enter. Now, if I copy this formula and I paste it here, you will see that it will remain the same and it will still have the same cell values. It won't shift, it won't change. And this is very beneficial when you need to copy the formula that is targeting certain cells to a different tables with different sub table and so on. Other than that, you can also keep the same column, but not the same rho by removing the dollar, for example, before the row here in the formula bar. Now you press on enter. Now this formula here has changed. Now if I copy it and I pasted here, as you can see, the columns there in shift, but the rows have shifted. Now, if I select this formula and I remove, for example, the dollar sign before the column, and I leave the dollar sign for the row and I press on enter. Now if I copy this formula and I paste it here, as you can see, it has shifted. Now it doesn't take these two anymore. It has shifted when it comes to the column, and now it takes this column and this column, but it's still at the same row, like I specified it to be. Otherwise, you just select the formula where you'd like to change the dollar sign, and then you either click on the formula bar, for example, for this value here, you just put your insertion point on it and then you press on F4. And as you can see, the dollar sign has shifted F four, it has shifted again, and therefore it has shifted for both. And you can do the same for this one as well. And then you would press on enter and then you have it. You can also do the same thing by double-clicking on the cell and then pressing on f for f four and F4 and so on. This is more beneficial than writing the dollar sign every time because it can get hectic to retype the dollar sign again and again. You can just use the F4 on the formula itself. Thank you for watching this lesson, and I'll see you in the next one. 11. Manipulating the data table efficiently: Working in Excel, you're gonna need to insert some cells, insert a row or a column, or delete certain cells. In this lesson, I'm going to teach you how to do that so smoothly and in a way that would benefit your work. Let's say you'd like to insert a cell here. All you have to do is right-click on this cell and then click on Insert. And then now it will insert a cell and it will give you this option. It will tell you either you shift the cells down so a cell will be inserted here where the cell is activated and all the other cells will be shifted down. They will be shifted right? The entire row, it will add an entire row or it will add an entire column. Now if it adds an entire row, as you see, it has inserted a row here. Now let's press on controls add, let's say we would like to add several cells. Let's say we would like to add four cells. So we select for cells, we right-click, we click on Insert, and then let's say we do a shift cells down, we click on OK. And as you see, the cells have been shifted down and we have added four cells. Now we undo what we did and let's insert a whole column. We can either insert a column like this as I showed you earlier, or we can select a column, right-click, click on Insert. And as you can see, a column has been inserted to the left. It will always be inserted to the left. And if I click on this row, for example, and I right-click and click on insert. Arrow has been entered above where I right-clicked, and that's how it will always happen. Now let's undo what I did. And let's talk about how we can add several columns. Now to add several columns, we have to select the same number of columns that we would like to add. So let's say we would like to add four columns. We select four columns, we right-click, we click on Insert. And as you see, it has added four columns. And the same thing would happen for the rows. Now if we would like to delete cells, what we do, we select the cells that we would like to delete. We right-click and then we click on delete. And as you can see, it gives us the choice to either shift the cells that are underneath the activated cells, it will shift them up or it would shift the cells that are on the right to the left. Or it would remove the entire row or the entire column of these two values. So if I remove the entire columns, as you see, it removed the entire columns of these two values. Now if I click on delete and I say Shift cells up and I click on OK. It just simply shifted the cells up. And this is very beneficial because that way nothing has happened to the formatting. Everything stayed the same. Of course, if you scroll down now you have to added values for the other columns, but it did it so smoothly. Now let's do control undo. And that's very beneficial because if you select the settings and you simply press on delete, as you can see, it will delete the data within those cells, but it will mess up the design of your excel. Now let's do controls at, let's say you'd like to delete the whole rose. Right-click, you click on delete and then you would do entire row and you'll do okay. And as you see, they have shifted up and nothing has been harmed when it comes to the design and the look of your Excel workbook. And that's all for this lesson when it comes to inserting and manipulating the cells, the rows to columns and how you can delete them. Thank you for attending this lesson, and I'll see you in the next one. 12. The different types of saving: Now, if you are done working with your workbook and you'd like to save it. There are several ways of saving it. You can simply save it to the current file that you have opened. Or you can press on F 12 or file save as, which is the same as F 12. And then you click on browse. And then you would navigate, for example, for the location. And then you have to choose the filename. Here, you can choose the type to which I would like to save this document. And this is very beneficial because let's say you'd like to save your document as a CSV, which is used for data fields and can be used in other programs and applications. You can use the CSV here with a comma delimited or for example, you can save it for previous versions of Microsoft Excel. For example, here, Excel 97, 2003 workbook, or even better, you can save your workbook as a PDF. And when you save it as a PDF, you cannot edit it anymore. And this is how, for example, you can submit it to people so that they see your invoice, your table, you're everything that you did on Excel. And this is very beneficial because once you create an invoice on Excel with all the tables and the formatting and the calculations. You can just save it as a PDF and make it look as an invoice and sends it to your client. So when you click on Save, it will automatically now open the PDF and this is how it would look like. Now, imagine it had a different design for it to look like an invoice. Here it it has invoiced for example. And then you could send it for your client or for your manager, and then treated as an invoice on PDF. Thank you for joining this lesson and I'll see you on the next one. 13. Using the SUM function: Performing calculations on each value in a range of cells can be complicated and time-consuming an Excel. So to simplify, for example, a formula that would add the values of these cells, instead of doing a formula that would say B5, B6, B7, and so on. We could just simplify that by using a function. Now if function is a predefined formula that exists within Excel, and it will also start with an equal sign and then it will contain the name of the function. Let's talk about the some function which is one of the functions that are the most used. Since it could be used for calculating the total, for example, of spendings or of earnings. And it's very beneficial and you can, you know, calculate these values quicker than any other way, like then, for example, calculating it on a calculator or than doing it by using a formula. So the function, the sum function would go as follows. Let's say for example, here, we would like to calculate the total of the quarter one of the year when it comes to the sales. So what we would do, we would activate this cell, then we will write equal some. Then we open the parenthesis. Then we can either enter the cell references manually law for example, c five and then comma c six, comma z, seven, and so on. And if you do it like this, what would happen is that now the sum function would calculate C5, C6, C7. Now, if we would like to calculate a range as we are doing here, rather than doing C5, C6, C7, what we will do, we will just do S5, two dots, C nine. And that way, as you can see, it will calculate the sum of this range. Now, another way to enter the cell references in the sum function is by simply selecting the cells with the mouse. So you can either select each cell on its own and then separated with a comma, or you can select a cell and then drag down to enter the range of the cell. And then you close the parenthesis, you press on enter, and then you have the sums. Another thing that you could do when it comes to the sum function is, for example, let's delete what we wrote here. Let's go to here to this ribbon group within the formulas we have here something that says Auto sum. Let's say we don't have time to just like keep typing the same formula. Again when it comes to summation. And we would like to get the totals, for example, of all the cell values. What we will do, we will just click on the cell underneath all these values. Then we would click on the drop-down menu for the autosome, and then we choose sum. And the thing about this is that Excel will take a guess of what you'd like to do. And the first guess that it would take that you would like to have the sum of all the values above the current cell. And that's exactly what we wanted to do. And if we activate the cell here and we do the same thing that we did before. As you see, Excel will assume that we needed to have these values for the sum. Now let's say we don't want to include the employee ID in the sum. All we have to do is just change it manually to c5. And then we have, this is a very quick way to add a function and a very beneficial one also. And that's it for this lesson. And I'll see you in the next one. 14. Some of the very useful functions: In the previous lesson, we talked about functions. In this lesson, I will talk about some common functions that are found within Excel, but also that are the ones that I find myself using the most. For example, I will cover the average, which is when you need to calculate the average of a certain set of numbers, I will cover the minimum, the maximum, and the count. So for example, you'd like to count how many employees are working within a company and so on. I will explain the different ways of implementing those functions. And again, it will be a matter of preference to you. Which one you choose over the other. Because I found myself during my time with Excel that I used both of them depending on the situation I was ended. Now the average function, as the name suggests, is a function that calculates the average of a set of numbers. So what it would do, it would calculate first the sum of all the numbers that are given within this function. And then it would divide them by the number of elements within this function. And it would work as follows. So I select this cell I activated, I press on equal, and then I would write average. I open the parenthesis and then I select the range of cells than I close the parenthesis, press enter, and then as you see, it has calculated the average. Now, another function that I'd like to discuss, and that's very important. Let's say you have here the quarter, the first quarter of the sales and you'd like to know what's the minimum that was acquired during this first quarter, what you would do, you would select the cell where you'd like to enter this function. Then you do equal min for minimum, and then you open the parenthesis. Then you choose, for example, these values or these cell references. Then you close the parenthesis and press enter. And as you see, the minimum is 6354, which is the minimum here. The minimum function could also be used, for example, when you have dates and you'd like to know what's the minimum date. Let's say you have a list of employees that like that that would like to start working for you. And all of them have, for example, in earliest starting date, each one of them gave, gave a certain one. So and you'd like to know what's the minimum starting date that, that's found within this list? So you would use this function and then it would give you the minimum starting date. And then you could look for that. And then you could know who are the people that could start working as soon as possible. Another function which works the same way as the minimum is the maximum. But instead of writing min, you would have to write max and then you have to open the parenthesis. You choose the values, you close the parenthesis. And then you would have the maximum, which is this one. Let's say you'd like to count how many quarterly sales have been there. So in this case we have five. But, you know, when it's a longer workbook with so many values and so on. You cannot count them just by looking at them and counting them each one by one. So instead of doing that, you could just write equal count. Then you open the parenthesis, then you select the range. Then you close the parenthesis, you press on enter, and as you see it says five. Of course you can enter the range manually, but as I discussed in the previous lesson, entering it by dragging the references is much faster. Of course, if, if the column spans across several pages, what you could do, you can simply just manually do it. You just write C5 up to the number where you're at. It. That way is faster than just dragging it down. Now another thing that you could use is the equal count a. Now the count a will count anything like as long as it's not empty. So it would count the text and it would count the value. So if I do equal. So if I open the parenthesis and I drag these cell references and I close the parenthesis, it would work. But now if I reopened the parenthesis, I removed what I had, and I chose these instead, and then I close the parenthesis. It would also count them as five. But as you see now in the count function before, if I do the same thing, it will result 0. Now you could use the count a always, or you could just use the count when it's four values. Now I personally suggest to use them based on what you're working with. If you're only working with values, like in this case in the first quarter of the year, you're only using numbers. It makes more sense to use the count so that incase, by mistake, you added a text somewhere in-between. And the cell reference of this text has been taken within the function so that it doesn't change the count for you. And if you're dealing with texts, of course, then you will need to use the count a Anyways. Thank you for attending this lesson, and I'll see you in the next one. 15. Formatting cells easily: Formatting a text is important to highlight elements and to differentiate some from others. I've never had an Excel file without text formatting because without it, the text looks plain and boring. But with it, some texts would come to life and your work looks and feels more vibrant. In this lesson, I will explain the different ways of formatting that I always use. You can select the text that you'd like to format. Then you go to whom. And in the Font group, you can choose, for example, to give it a different font with a different color, a different styling, a different size, and so on compared to the others, just so that you can highlight them. So for example, these employee IDs are important to you and you'd like to highlight them so that you can see them compared to the other ones. That's how you could do it. And let's say we'd like to format these three cells along with, for example, this cell and this cell. What we would do, we would press on control and then we would click on the other cells that we would like to include in our selection. And then we can change the styles of these, for example, cells. And then compared to the other ones, these cells standout and you know, as soon as you open your workbook, The first thing that you're gonna look at is the cells that have been formatted. 16. New ways of controlling the formatting: You can also copy the formatting by using the autofill feature that I explained in previous lessons. So to copy formatting in a nice, even quicker way, when you would like to copy the formatting to adjacent cells, what you would do, you select the cell that has the formatting that you'd like. And then you have to navigate the mouse to the bottom right here until you see the plus sign. And then you drag it to the adjacent cells where you'd like to have the same formatting. And as you can see now, it has filled the other cells with these values. But we don't want that. We want only the formatting to be copied. So to do so, as you can see here, we have this button, we click on it. Now this button is copying the cells, and as you see, it has copied the cells. But in our case, we don't want any of that. We just want to fill the formatting so we click on Phil formatting only and as you can see, it retains the previous numbers, but a change the formatting. And this is very beneficial and quicker to apply the same formatting on adjacent cells. We can also use the Find and Replace to actually replace the formatting. And in order to do so, we can click on finance select, then we click on replace. Now we have this dialogue, but remember this, this feature is very important because let's say you have a huge workbook and you cannot know exactly. For example, you'd like to change all the values that are 5859 to the red color and you cannot see all the numbers within the workbook. This is how you can do it with this dialogue here, what you will do, you click on options to have more options. And then you would specify what you'd like to find. Let's say we would like to find 58, 59. We don't care what it has as a formatting and we'd like to replace it with a different format. Let's say we'd like to replace it by the currency so that it becomes a currency. And we give it the font where the color yellow, and we fill it with blue. So we click on OK. Now we have a preview here of the selected font. Now we click on Find Next. And as you can see, it already founded. Now we click on Replace, and as you can see, it has been replaced here, and it will do the same for every other cell that contains this number. So now I can say phi, find all, find all of them, or just simply replace all and I click on Replace all. And as you can see, they have been replaced. That's how you can format using the find and replace dialog box quicker and especially when the workbook is big and you have a lot of data. Thank you for attending this lesson, and I'll see you in the next one. 17. Formatting cells based on their values: You can as well select the cells that you'd like to add it. Then you can right-click on them, and then you can click on format cells. Here you have a wider selection to choose from when formatting the cell, you can formatted to be a number or to be a currency. So for example, in this case, in the quarterly case, it is a currency, so you can format it to be a currency, let's say in dollar, you can choose its alignment if you'd like it to be in the center and so on. You can choose the font, you can choose what to fill it to which color. Now let's say we decided that we want them to be a currency number, so we press on, okay, and as you can see, they have been formatted to be a currency. And this is very beneficial because in our case here we're talking about currency. And if you just look at this value, you don't really understand what's happening. But when you look at it and you see okay, it's there's a, there's a dollar sign and everything you would understand, OK, this is a value concerning money, concerning currency. And it's better to do so so that you can understand what's happening in the workbook better. And so that when you share your workbook, other people could understand what's happening. 18. Reshaping rows and columns efficiently: In Microsoft Excel, you might need to change the width of the columns and the length of the roles depending on how you would like your table to look like within the workbook. And it's beneficial to know how to change the size of several columns together or several rows together in order to change the size of several columns together, all you have to do is select all the columns. You'd like to change the size to. And then you navigate the mouse to the border of one of those columns. And then you click and then you drag. And as you can see, all three columns have changed together. And you can do the same for the rows as follows. Now, if you'd like to change the coloring of certain columns together, you can do it the same way by selecting all the columns that you need to change at the same time. So for example, B and C, and E and F, the same way as it works with cell. You select b and c, and then you press on control, and then you select E and F. And then here you can, for example, fill it with green, fill it with any other color that you'd like. And this is how you can apply colors to several columns. And it would work the same way for the rows. And it's beneficial to do so because like this, you can change several things at the same time rather than doing them each on its own. 19. Merging several columns into one cell: One thing you can also do, for example, let's say you have the value 16 and you'd like to have it as a single cell here, and then the rest will remain the same. So in order to do so, what you have to do, you have to select 16 and you have to select the cells next to it where you'd like the 16 to span and then be in the center of, so what will happen is that the cell of 16 will emerge with the other cells. It will remove them. And then 16 would be at the centre. So 16 would be around here somewhere. To do so, you select the cells, then you navigate to merge and center in the alignment driven group. When you click on merge and center, then you click on OK. And as you can see, you have the 16 here. And it has been centered. And also a few navigate the mouse over the Merchant Center. You can see that it tells you how it works and in case you have changed your mind and you don't want this cell to be a single cell, but you wanted to be back to normal. All you have to do is selected, then navigate to merge and center, click on the drop-down menu, and then click on merge cells. And as you can see, it will be back to normal. 20. Efficient ways of drawing cell borders: You can also add borders to your cell, either by selecting the whole columns or selecting cells on their own. So for example, let's select this cell, this cell, and this cell. And then you navigate to the font ribbon group. Then you click on the drop-down menu, and then let's say, for example, you choose this border. This is also beneficial because like this, you can differentiate these three cells from the rest of the other cells. You can also, for example, choose the same cells. You can click on the drop-down menu and then you can click on More borders. And then here you can also specify how you'd like it to look. You can choose, for example, this style. You can say OK, you want it here and here by clicking and here. And here as well. Your text would be in the middle, your borders will be there. You can choose the color, for example, to be green. Then you click on OK. And as you can see now you have it. You can also simply navigate to the borders button. You click on the drop-down menu and then you can click on drove border. So like this, you can have this pencil and you can just simply select the borders where you'd like to draw. So for example, here you can draw here and here. Here, and here, for example, the way you have more freedom growing your borders rather than drawing them by using the other options. I think this option is better for flexibility and better to design your borders, but it's much slower than just selecting a preset that's found within the borders options. Now, another way to design your borders would be to click on this button and then choose the line colors. So for example, you'd like the land colors to be red. So now whenever you draw, the lines will be read rather than just simple black. And this is a very useful feature because like this we can differentiate cells from one another. Also, let's say you'd like to copy this value here when you do control C on this value, and then you do control V somewhere else, the borders will be copied with it. Let's say you don't want to copy the borders with it. All you have to do is to right-click on the destination cell and then you would click on paste special to see these options here. And then you have an option that says no borders. So when you click on no borders, you can see the display. Now it displays the value without a border. And this is what we would like because if we paste it normally we have the borders. So let's just pasted with no borders. And as you can see, now you have it with, without a border. And this is beneficial because when you're copying a value somewhere else and you just paste it, then you have to come back and to remove the borders in case you don't want the same borders somewhere else in the table where you're copying this value. And that's it for this lesson. And I'll see you in the next one. 21. Formatting numbers in Excel: As I've discussed earlier in previous lessons, you can change the formatting of a value to make it look more understandable and to give it a purpose. So for example, in this case, those values in the quarterly sales are representing an amount of money and they should be represented by a currency. So in this case, what we could do, we could select all these values and then navigate to the number ribbon group that's found in the Home tab. And then we can decide, for example, we want them to be accounting number format in this case. So for example, we can say we'd like them to be English with the dollar sign. And as you can see now, we have an amount of money represented by the dollar sign. We can also represent the values to be percentages. Or we can put them back to dollars and then we can decide how many zeros would be after the dot by using these two buttons here in the number ribbon group. So here we have the Decrease Decimal or we can increase the decimal. So let's decrease the decimal. And as you can see, I have decreased the decimal of this value in this cell. Of course, we can choose from many other formats for the values by clicking on this button here. And then we will have several other formats. We can choose like to have it as a general format or as a number with a comma as a negative number, we can choose it as a date, as a time, and so on. This is very beneficial to know because in Excel we're going to work with several values and each value is representing something different. So knowing how to format these values is very important to represent your data. And the most understandable way. 22. The use and importance of conditional formating: Let's talk about another very important thing in Microsoft Excel and that's conditional formatting. So let's say here we have these quarterly sales and we would like to add a formatting to them, but on one condition. So we would like that all the sales here that go above $6 thousand to be represented in green, and all the sales that go below 6 thousand to be represented in red like this. We can visualize the data. We can know OK, here and here and here we did a sale higher than 6 thousand, which is good. And here with its sales less than 6 thousand, which is not good. And in order to do so, we have to select the cells that we would like to add a conditional formatting to. Then we have to go to styles, and then we click on Conditional Formatting. And then we can create a new rule, which is the rule that I just discussed. Or we can choose from the rules that are already there. For example, this rule here, if you click on it and then you click again on Conditional Formatting, and then you click on Manage Rules to be able to manage this rule and see what it's doing. If you select it and click on Edit Rule, you will see that it's formatting all cells based on their values. And what it's doing is that the lowest value as receiving the red color, which has this value here. The midpoint is receiving a yellow color, which is this value here. And the green is the one with the highest value, which is this value here. And anything between red and yellow is gonna vary between red and yellow in this color scale here. And anything in between. The midpoint and the maximum will vary in the color scale here. And that's what this conditional formatting is doing. Now let's cancel and let's delete this rule. So we don't want this conditional formatting lets apply to see that it has been removed. Let's add the rule that I just discussed earlier so that we see the sails above 6 thousand and below 6 thousand. In order to do that, here we have so many rule types that we can choose from, but let's now talk about choosing the format only cells that contain. Here. We're talking about the cell value. Of course we can choose other things, but here we only want the cells that contain cell values. Of course, we can look for a specific text. We can look for dates occurring. We can check if it's blank. If it's blank, for example, we don't want it to be formatted. So let's say we selected this whole thing from Firm C five till f 17, which we've chosen all of this to add some conditional formatting to it. And we would like to also manipulate that when it's blank, we don't have any colors, so let's do it. Let's do it together. Let me cancel everything. Let's select from here till here, let's click on Conditional Formatting. Let's add a new rule. Let's say we want to format only cells that contain the value greater than. 6 thousand and we would like to format it to be green. Of course, we can change the borders, we can change the fonts and we can change the formatting of the number. But let's just change the filling. Let's click on okay. Let's click on OK. And as you can see, everything that's above 6 thousand has been changed. And now if I add a value here that says 50 thousand, nothing will happen. But if I add a value here that says 6,001, it will turn to green. Now of course, I can manipulate the blanks, as I said earlier, so that they have their own colors. And I can do so by, let's say selecting again what I selected earlier. Let's go to conditional formatting. Let's create a new rule. Let's say format only cells that contain blanks, and let's format them to be in orange. Let's click on OK. Click on OK. And as you can see, all the blanks have been changed and all the cells that contain values remain the same. Now let's select again the same cell ranges. Let's go to conditional formatting. Let's add a new rule. Let's say we would like that everything less than or equal to five, no, 6 thousand would be colored in red. Let's click on okay, let's click on OK. And as you can see now here, everything less than 6 thousand has been colored in red, even the blanks, and that is initial. We can also change that, of course, by selecting this going to conditional formatting, going to manage the rules. And here we can see that yes, I still have the rule that says cell that contains a blind value would be in orange. Cell value that's less than or equal to 6 thousand is in red and cell value that's greater than 6 thousand is in green. And the reason why here, even the blanks are in red is because first of all, blanks are considered to have a 0 value and 0 is less than 6 thousand. And even though I have this rule here that says that the blanks should be an orange. This rule is below the rule that says that everything under 6 thousand should be in red. And in order to fix that, all I have to do is click on this rule and then click on the arrow here to move the priority of this rule up. Then I click on apply, then I click on OK. And as you can see now, the blank values are in orange. The values that are above 6 thousand are in green, and the values that are below 6 thousand are in red. And in order to be able to manage the rules, you have to activate a cell that's within those rules. So if I activate the cell that's here, and I tried to go to conditional formatting and to manage rules. I can see that there are no rules, but from here, I can change that by clicking on the current section and then looking in this worksheet. And then I will be able to find the conditional formatting. Or I can just activate the cell and then manage the rules and I will be able to see it and the current selection. And then here I can just delete the rule so that I don't see it anymore. Or I can click on conditional formatting. I can say clear rules, and I can clear either the rules from the selected cells or I can clear the rules from the entire sheet. So if I select this cell, this cell, and this cell, and I go to conditional formatting and I say clear rules and I say clear rules from selected cells. As you can see, the selected cells don't have any conditional formatting anymore. Thank you for attending this lesson and I'll see you in the next one. 23. Creating and enhancing a chart: Every time I had a table with a lot of numbers, I created a chart for it so that the reader can visualize the data and not get confused by all the numbers. In this lesson, I will cover the best ways of graphically presenting your workbook. Because for example, it takes time to notice a trend in a table, but any graph, the curve, and the differences in shapes which represent better what is happening to the values. Now, to create a chart in Microsoft Excel, what you have to do, you have to select the data only the values and the text that represent this data or this value. In our case, what we have, we have the names and the quarter, so we would like to select the names and the quarters and the sales of course, of the quarters. So this text will represent what are these values? And these texts would represent what are these values. So now what we do, we go to Insert and then here we have the charts ribbon group. We can choose from the recommended charts or any other chart. Me personally, I prefer to click on this button here to see all the charts. In this page here I can see the Recommended Charts or I can see all charts here. And I can choose from any chart that I had. Like in my case, I will choose a clustered column chart. Now if I hover over it, as you can see, it will display to me what the chart would look like. So I'll click on it and then I'll click on OK. and as you can see now, I have this chart. Now. I can resize it by dragging it up, and then I can move it by moving my cursor anywhere on the chart or borders and then I can move it. As you can see, the colors represent each employee and the the groups here are represented by a quarter. And that's how you can create a chart and you can see better what is happening. Now, you can simply fix the chart title so that you can understand better what this chart is representing. So let's change the title and write bonus sales. And of course, we can edit the font of the title the same way we would edit the font of everything else. For example, we can choose it to be like that. We can choose it to be in red and so on. Let's keep it in black. And it's good to know that the data in the chart is linked to the data that has been used to create this chart. So now if I change this value here to 10 thousand and I press on enter, as you can see, it has been updated on the chart. Now let's bring it back to how it was and it has been updated again. 24. The right way to move a chart: In case you have another sheet in your workbook like this one. And you would like to move this chart to the other sheet because this chart is, for example, more useful on the second sheet, what you have to do, you have to select the short. Then you have to navigate to the Design tab. Then here you can click on Move chart. Now when you click on Move chart, it would ask you, where would you like to move the chart? You can either create a new sheet that would be only for the charts. So if I create a new sheet and I click on OK. As you can see, a new sheet has been created only for this chart. And now when I go to sheet one and I change some values and I come back to this chart. As you can see, the chart has been updated. Now, if I return it back to normal, and I go back to the chart and I go to Design, and I click on Move sharp and I say I'd like it to be an object in sheet to NIH. Click on OK. Now the sheet that was created for the chart has been deleted and now we have the chart within the sheet number two, as I discussed earlier. 25. The famous pie chart and redesigning a chart: Of course, we could use pie charts. And those are very famous and very widely used. The good thing about pie charts is that they have a good visualization and a better understanding. So you can see the pie, how it's behaving and the size of each pie slice. But the bad thing about pie charts, and you have to keep that in mind is that they are not very effective when one or more values in the data series are 0 or negative. And that's because they can't be represented in a pie chart. So if you have a value that's equal to 0 or that is negative in the pie chart, it won't be visible and in a way it will be gone and it will be lost in the transformation into a chart. And on top of that, when there are too many categories than the pie chart would look overly small. So all the pie slices of the pie chart would be extremely small because of how many categories we have. And that's why when you have so many categories, when you have zeros and negative numbers, it's better to use charts like the one I've used in this example. Now once you have created a chart, what you can do, you can click on the chart and then you can navigate to the Design tab. And then you can click on Change Chart Type. That's in case you'd like to change the type of the chart. So let's, for example, choose a pie chart since I've discussed it earlier. So I'm gonna take a pie chart and then I'm going to click on OK. And that's how I can change the type of a chart. Now, we can change the style of this specific chart to make it look the way we'd like it to be. So we selected, then we go to design than in the design. We can choose how we would like the style to be. For example, we would like it to be with a black background. We would like it to be like that. We can change the colors that are being used within these charts are, for example, we would like it to be this way. And this is very beneficial because maybe you have preferences when it comes to the color or to the design. And you can choose your own design and leave your own Touch while designing the charts. You can also add access titles that could provide useful information to the chart. What you have to do, you have to select the chart. Then you navigate to the Design tab. Then you have to click on Add Chart Element, which is found in the Chart Layouts group. You click on it and then you can choose what you would like to add. Let's add an axis title for example, let's add it to be on the Primary Horizontal. And you can see it here, for example. Now here you can add some information that are useful to the graph. And like this, you would have your graph more personalized, more detailed, and more understandable. Thank you for attending this lesson, and I'll see you in the next one. 26. Adding images and reshaping them: In Excel, you can add images to your workbook. And these images could be very beneficial to represent the data that you are talking about or the values that are within the workbook. And images are also important because, you know, Excel is mainly made of text and values and just like tables. So it's nice to put some visual things in the workbook to put some graphs, some graphical representations, some images to make your workbook a bit more alive. Now in order to add images to the workbook, but you have to do half to navigate to the Insert tab. Then you have to click on pictures. And then you can either choose to bring your picture from the current device or you can choose a picture that can be found online. Now if you have already a picture in mind and you already have it on your device, you can select that. And in case you'd like to add some picture, but you don't have it yet. You can click on online pictures and then you can search for this picture and then you can add it the same way you can search on Google images and then add it here. Now let's add a picture that I added in my intro, and that is my picture. Now as you can see, this image is taking a lot of space. Now what we can do, we can resize it. There are several ways to resize this image. First way to resize it would be to go to format in the Picture Tools. And then here on the size, we can resize the image by the height or the width. Or we can even crop the image, or we can simply place the mouse at the edge of the image and then click there and drag the image down so that we can resize it. Now there are several ways to resize the image in such a way, we can either resize it freely by just clicking on the mouse and then resizing it. Or we press on shift and then we resize to maintain the proportion of this image. Now in this case, this image is maintaining its proportion anyways. But in some cases, when you try to change the height or the width of the image, it won't maintain its proportions. So what you will need to do, you will need to press on shift in order to maintain the proportions when it comes to the image. You can also rotate this image either by clicking on this button here to rotate the image like that, you just simply click on this button and then you move your mouse to rotate the image. Or you can simply rotate this image by using the button that's in the arrange ribbon group. And then you click on the rotate and then you can either rotate it 90 degrees to the right, to the left, you can flip it and so on. Those options are beneficial for your picture so that you can take control over your picture and represented the way that you'd like. 27. Adding smart arts and enhancing them: Let's add some smart arts to our workbook. Now, we've talked about how to add charts to the workbook. Now we're gonna talk about how to add smart arts. Now charts are used to represent the values and some data about these values. Smart arts are just there to represent information. So for example, we can create a smart art that would describe this whole table. That's beneficial because that way people don't only have to look at tables and charts, they can also look at visual representations of what's happening within the workbook. And in order to do that, you have to navigate to the insert tab. And then in the illustrations, Raven Group, you click on Smart Art. Here, you have a lot of options to choose from. In this case, for example, let choose a hierarchy and then we can, for example, choose this one, and then we click on OK. Now here we have this Smart Art where we can enter the text either here or directly here in this box. Now, if we add it on this side, we have more control on what's happening. Because at least here, if we write, for example, employees, then we press on enter. As you can see, it will create another parent within the tree. Now let's go back to how it was. Let's write employees. And then let's, for example, switch down to the child by simply moving down with the arrows. Let's write Peter. And then when we press enter, we will have a second child. Let's write bill, and then when we press enter, we will have a third child, let's say Eli, and then enter a fourth child. Let's write will. Now let's remove those children because we don't need them. Now we have a hierarchy that has four children, just like we have here in this table. Now, let's move to the first child that's Peter. And here we press on enter, as you can see now we have the fifth child, but in order to turn this child of the main tree and make it a child of Peter. All we have to do is press Tab, and as you can see in this area here, now, it's under Peter as a child. Now I can write quarter one. Now when I press Enter, Peter would have another child, let's say quarter three, to enter. Quarter three and quarter four. Let's do the same thing for all the others. Now we have this Smart Art here, and as you can see, it's so small to see what's happening, what we can do, we can simply re-size the Smart Art by dragging the button here. And now we can see better what is happening within the Smart Art. Now this Smart Art is representing this table. Another thing that we can also do, we can, for example, at a child to this quarter one and just press Enter and then tab. And then we can write the value, for example, 6354-zero, that would represent the dollar within this quarter, we can change the formatting and the styles of each child and parents. So for example, I would like that this parent here, we'll have another color to separate this parents from the other patents. And so that suck when someone looks at this smart art, they would understand that, okay, this is the parent and the other ones are the children. So for this parent, I'd like to give the color blue. Now this parent has the color blue, and now to select all four children's together, I select the first child than I press on control, and I select the second, the third, and the fourth child. And for example, I changed them to the color red. Now here, I can see that, okay, this is the main parent, and those are four main children for the parent. And as you can see now, we have the smart art and it's very understandable because as soon as you look at it, you're like, okay, that's the main parent. Those are the main Children and the green ones are the children of the children, and the purple ones are the children of the children, of the children. And that's how you can make a smart art. And you can make it look very appealing on the eye, very understandable and a good representation of what's happening in the sheet. Thank you for attending this lesson and I'll see you in the next one. 28. Formatting using data bars: In Excel, you can add conditional formatting with a graphical representation. One way to do so is by using data bars. Now data bars are used to represent the data in a graphical form based on a defined values. And in order to do that, for example, in the total sales, we have to select all the values within the total sales. And then we navigate to the Home ribbon tab and then to the styles ribbon group. Then we click on Conditional Formatting, and then we have here the data bars. Now of course we can choose the more rules as it applies to every other rule. Or we can choose one of the feelings that we have here. In my case, I will choose green because I like the color green, so I will go with that. And as you can see here, it's a data bar. And the larger the number, the more it is filled. So that's the highest number. And it has the most fill. And that's the smallest number and it has the least fill. Of course, we can also select this whole column and then do Conditional Formatting, Manage Rules, and then we can add it, this rule, for example, we have to double-click on it. Then we can change how it behaves from here. 29. Adding ratings based on values: Another thing that you can do to represent the data in a way that's very beautiful on the eye and very understandable. You can use ratings for example. So let's say you'd like to rate how the first quarter of the year was for the salesperson's. And in order to do that, you will need this data here. So you have two options. First of all, you can choose all these values. Then you can go to conditional formatting. Then you click on icon sets, and then you can choose any of those icons. In our case, we would like the ratings icons. Of course, we can choose more rules. But in our case, let's choose the star. I can. And as you can see, the more complete the star is, the closer we are to one of the highest values. Now, we can either show the star with the value or we can show the star without the value. Now let me remove the Conditional Formatting. Let me copy these values. Let me paste them here, but let me paste the link, not paste them normally, but let me paste the link of those values by using the same way I taught you in the previous lessons. So I click on paste link, and now whenever I changed one of those values, they will be changed also here. Now I select this column here, these values, then I click on Conditional Formatting, then I click on icon sets, then I click on ratings. Now, what would happen now by default is that I have the ratings along with the values. Now what I will do, I would go to Conditional Formatting Manage Rules. Now I double-click on this rule and I say show, I can only, I click on that. Of course, I can change how the ICANN behaves. I can say that for example, when the value is above or equal to 67%, show this icon. Otherwise, if it's between 6733, show this icon. Otherwise show this icon. And of course I can change, which I can, I am seeing now, I said that I'd like to see. I can only now I click on OK. I click on Apply, I click on OK. And as you can see now, I see only the rating without the values. And that's a very good way to represent your values and your data's so that whoever is looking at this document can understand it better than just looking at plaintext and plain values. And now if I change this value here and I write for 1000 and oppress one enter. As you can see, the rating has changed and the total sales have changed. Thank you for attending this lesson, and I'll see you in the next one. 30. Preparing the workbook with the spellchecker: Many times when I was done with my workbook and I printed it, I found the adders that had me correcting them and printing them again. So not only I wasted time, but I also wasted paper. In this lesson, I will explain the few steps you can take so that everything is flawless when printed. Now, the spell checker that is included within Excel will suggest possible corrections if the word it finds is close to an entry within the Excel Pictionary, you can ignore the suggestions that are given by the spellchecker. Like for example, most names are not included in the Excel dictionary. And then you will need to either ignore them or simply add them to the dictionary so that you can use them later. Because for example, this name is repeated several times or that you will need to repeat this name several times. It's better to just add it to the dictionary than to keep ignoring it every single time. In order to run the spell checker, what you need to do, you need to go to the Review tab. Then you need to click on spelling in the Proofing group. Here. It will open this box and it will tell you the words that are not in the dictionary, and it will give you suggestions. For example, this word, I have a mistake here, and it's giving me suggestions based on the language which is English. Of course, I can choose other languages. And then it will give me suggestions based on those languages. I can either ignore this word onetime, I can ignore it all the time. So in case this mistake is happening around the worksheet, it will ignore it. It will ignore all of them. Or I can simply add it to the dictionary so that all the current ones that are in the worksheet will be ignored and the future ones that I make. In this case. Let's, for example, choose this suggestion and do a change. Now we have another mistake. For example, rebelled is not a word in the dictionary within Excel, we have five other suggestions and we can choose one of them based on what we meant from this word. For example, from this word we meant rebelled. So we click on change. And it says that Would you like to continue checking at the beginning of the sheet because in this case, it checked from somewhere else within the sheet. We can either say yes so that it checks from the beginning or we can say no if we don't care about it, let's do yes. And then it will check, and it will say that everything is good to go. This is very important to save us time and paper later on. 31. Fastest way to fix a value in a workbook: Another thing that you can use incase, you made a mistake and you know, you made a mistake and you'd like to find it. And as you can see, it's very difficult to look for each value on its own. What you can do, you can use the find and replace functionality. And the find and replace. You can search rows and columns and worksheets, for example, formulas, values or text and so on. In order to do so, you have to go to the Home tab. And then, and then in the editing ribbon group, you have to click on Find and select, and then you click on replace. Here, it will directly take you to the Replace tab. You can specify what you would like to find and then you can specify what you'd like to replace it with. And of course, you can explore some options by telling it to find it within the sheet or the whole workbook. So in all the other sheets that you have, if you'd like it to search by rows or by columns, if you'd like it to look in formulas, if you'd like it to match the case. So in case you wrote it in uppercase, you'd like it to find it in uppercase or lowercase. And you can also look for a specific Formatting As I have discussed in the previous lessons. Now for example, let's try to find the number 6354, and let's try to replace it with 65, 55. Let's find the next value that has this value. Let's click on Find Next. And as you can see, it has founded. Now we have the choice to either find an X1 without doing anything to this one. We have the choice to replace it. Other than that, we can find all the instances of this value, or we can replace all the instances of this value at once. Now let's click on replace, and then we click on Find Next it, we'll find another one. We can replace it again. Find next it will find it here and so on. Let's close it. And that's how we can work with the Find and Replace. Now let's say you'd like to find the value or certain values in the selection of cells rather than the whole worksheet or workbook. In order to do so, you have to select a range of cells. So let's say we selected this range and then we press them control, and then we select this range. Now, all we have to do is go again to the find and select, then replace. Then we can just treat it as normal. We tried to find next, as you can see, it found this one in our selection. Now if I click on Find Next, it found another one within the selection, Find Next. And that's the only two values that match this value that I'm looking for and that are within the selection that I selected. That's how you can do it when it comes to the Find and Replace functionality. 32. The best ways to save a workbook: Once you are done editing your workbook and your worksheet, and you are ready to print it. All you have to do is click on File. And then here you have the print tab, you click on it. Now you have a preview of the page that will be printed. And as you can see here, it's only printing the North within this page, but if you go to the other pages at will continue printing the rest of the table. And another way to preview how your page will look like once you print it, you can just go to view. And then here in the workbook views, you can select, for example, the page layout. Now the Page Layout is how it would look like. Won't you print your workbook? So this is how it would look like. So that would be the first page, second page, third page, and so on. Let's go to page layout. Let's click on the Options here for the page setup. In this page setup, we can of course, the side that we'd like it to be a landscape. And I think it would make more sense in this case, since this table is in any way a landscape, we can set the margins. This is beneficial in case you'd like to set up how each sheet will be printed once you print your workbook. Now let's do a cancel and of course you can print preview, and this is how you can see it. Now, I would like to show you how you can print each selection on its own early you have to do is select, for example, this selection here. Then you go to file, you go to print. Then in the settings you have to choose Print selection. And then you can choose all the other settings that you'd like. And then as you can see, you will have your selection on just one paper. Another thing that can be done is to, for example, fit all the rows on one page. As you can see here. It fits this column with this column, with this column. And then if you go to the next page, this column, this perfect, this column is perfect, but this column here is missing its total, which would be on the next page. Now, I've, I believe that this printing setup is better and is the best to do it, but it's a matter of preference. And of course you can change those preferences based on what you prefer here in the printing settings. Now let's say a certain selection in your document is always going to be printed. And you don't want every time to reselect it and print it and read selected and printed. But you can do, you can simply go to the page layout. You click on this button here for more options. Then you click on sheets. And then here for the print area, you click on this button. And then you select the area that you'd like. So in this case, let's select this area. Here. Let's click on this button, and now we have this area. Now let's click on print preview. And as you can see, we have it here. And now every time you're gonna go to print, this area will be available to print, even if you don't print the selection. As you can see, I'm not printing a selection and printing the active sheets and it has chosen this one, the one that I selected as the print area. And of course you can change that by again clicking here, clicking on the sheet, and then changing the print area to match your preferences. And of course you can have the print area contains several selections, like for example, now if I go to the Page Setup and I go to sheet, as you can see, I have this selection here and this selection here. Now I can add one more selection. For example, I press on control and I select this column here. Now I click on the button here. Then I click on OK. Now if I go to file and I click on print, the first page will contain the first selection. The second page will contain the second selection, and the third page will contain the third selection. Thank you for attending this lesson, and I'll see you in the next one. 33. Adding headers and footers to the workbook: If you have finished working with the workbook and the worksheet, and you'd like, for example, to either print your document or to send it to someone who will look at your document. It is useful to use headers and footers because they can provide important information about a worksheet, like for example, the current day, the page number, the author, the worksheet name, and so on. In order to add a header or a footer, what you have to do, you have to go to Insert. Then in the text group, you have to click on header and footer. Now once you click on it, you have the option to either add a header in the center or a header on the right, or a header on the left. And if you scroll down within the page, you can add a footer either in the middle or on the right or on the left. And if you scroll back up, you can also add headers on the other pages. For example, you can write anything that you'd like and then click summer on the worksheet and then the headers will be displayed on all the worksheet because in this case, it has split the worksheet into several papers because that here is a print layout view. So when, once you print this worksheet, you will have this on the first paper, this on the second paper, this on the third paper, and so on. So the header here would be printed like that once you print the document. Now let's remove this data here. Let's click somewhere on the worksheet. Now let's add some useful data as a header. In order to do so, we have to select where we would like to add this adder, for example here. Then we have to click on design. Then we can choose what we would like to add as an element from the header and the footer elements, we can choose to add a page number. How many number of pages there are, the current day, the current time, and so on. So for example, I can add the current date. Now when I click away, as you can see now, I have the current date. Also. If you selected this header here and you press on tab, it will directly move you to the next section, which is the one on the right. And if you press shift up, it will take you to the section before, which is the one on the left. You can also format headers and footers. In order to do that, you have to select the header, then you have to go to the Home tab. And then in the font, you can, for example, say you'd like it to be bold. You'd like it to be Arial Black. You'd like it to be in red, let's say. And now when you de-selected, it will be in the font styling that you have chosen. You can even increase the font size. It is important to know this information because that way you can manipulate the header, the way that you'd like. You can also print different headers and footers. So let's say you'd like, for example, on this page to print the header that says the date of the next page. You'd like a different header. In order to do that, what you can do, you can go to the insert tab and then click on header and footer. Here in the options you can set, for example, that you'd like to have a different first page. So now in the first page, you can add the current time, and then in all the other pages you will have the current date. And they can have also different formatting. It's beneficial to know this because sometimes you don't want to display the same header over and over again. And the same logic that I'm describing now will work also on the page footer and other thing that you can manipulate when it comes to the header, you can just select the header and then you go to design. And also you can have different odd and even pages. So if you click on this, now, the odd pages will have the date and the event pages, for example, will have, let's say the number of pages. So now if I check what is happening, the first page has the current time because I selected it to be different. The second page, which is an even page, has the number of pages and the third page, which is an odd page, has the date. And that's how you can manipulate the headers and the footers when it comes to your workbook. Thank you for attending this lesson, and I'll see you in the next one. 34. Creating a monthly spending workbook: Since I was a child, I've always used Microsoft Excel to create Sun-like monthly monitoring sheets so that I can monitor how much I'm paying and how much I'm spending. And in this lesson, I'm gonna teach you how I usually did it. Now. Usually what I would do, first of all, I would add the date. So now I have that this column here would represent the ETS. So I select this column and then I say I'd like to, I'd like it to be center aligned so that it looks better. Now, the date would represent a label. So for example, I can change its design. Let's say it will be something like that and it would be in black. So now I have the date. Let's say I'm talking about the month of June. So that would be 0106. 20-20. Now, here I have one June. Well, I can do I can select it. I can go to Number format. I click on this button. Now the date, let's say for example, I'd like to see six June. I'd like to see, for example, the day and the month. So I click on OK. Now here, as you can see, it's still taking the American formatting. So in order to change that, I click again on the option, and I can see here that it thinks the location is united states. I will change that because currently I live in Germany and I will choose this format and I will click on OK. Now I will change the date to 61, and then it will take it as June. Now, I select this cell I activated, then I scroll down up to 30 June. Now as you can see now, I have all the month of June because if I keep continuing, it will go to July. Now. Second column. Good contain for example, let's first of all copy this cell and then paste it in all the cells so that I can have the same formatting as the first cell. Now here, I don't want it to be the date. Let's say I'd like it to be groceries so that I describe what I spent in case I spend on groceries. Here, I'd like it to be, for example, bells. In this one, it can be delivery. And here it could be others. For example. And I can add one more that can describe, so this corruption. So it could describe what I have used now these, I don't need them anymore, so all I have to do is select them. Then I right-click, then I click on delete. And then I would say shift cells to the left, I click on OK and they will be deleted. Now let's fix the size of the columns. So that would be it. Now, first of all, let's add a total here. Let's formatted the same way as the others. And an order to do that, I select this cell, I click on format painter, and then I paste it here. Now here I have the formatting as it is above. Okay? Now we have to calculate the sum of all of these. So I write here equal sum. I open the parenthesis, then I select from this cell to this cell, and then I close the parenthesis. And now all I have to do is copy this formula and paste it here. And this formula now will take care of this column. Now, I paste it again, here and here. And that's because the description won't contain any values. Now these work perfectly. Let's, for example, change the styling of this column by choosing this one, or maybe this one so that I can see it better. Let's say I added now that on this day, oh, and it's important to choose your currency. So let's select all these columns and let's add a currency. Let's say in our case it's euro. And let's add a Conditional Formatting by selecting the whole cells and clicking on Conditional Formatting, let's create a new rule. Let's say that we would like to format only cells that contain, and then let's choose blanks. And let's format the cells that are blank by adding FL. And let's choose the filter V, For example, this color. Now let's click on OK. OK. And as you can see now, since those are empty cells, all of them have been failed like that. Now, let's add, for example, that on this day, I spent €25 on groceries. No bills. Maybe as I spent, for example, 500 ohm bills, let's say for the rent, I didn't eat any delivery and other end spent anything on the others. I can just keep filling it with my current, for example, spendings. Then I can select the whole table again. Let's add another conditional formatting that would say that if the cells are not blanks, Let's format it and fill it in this color. Let's choose this one. Let's click on OK. OK. And as you can see now, it has been filled like that because those values have been filled. Let's do the same for these cells. Let's choose a currency for these cells. So it's euro. Let's add a total here. That would be the total of all of these. So it will be equal to the sum of these four cells. Now let's close the parenthesis. Now this is how much we spent, for example. Now here we can format it in a way that we are the conditional formatting and we add a new rule. Let's say we have a budget to spend per month, 10,500 Euros. So we add a rule that says format only cells that contain. Now the cell value is the one that we'd like because here we are talking about the value, then we choose less than one hundred and five hundred. And so if we spent less than 1500, but actually in our case less than or equal, because that's the equal budget. If we spent less than that, than we would like to format it in the color green. So we choose that and we click on OK. And we add another rule that says, if the value is greater than one hundred, ten hundred, five hundred, Let it be colored in red. So now what will happen is that whenever we keep adding spendings in this table, these values will increment and then this value will increment. And then this value will represent the, how much we spend this month. And the good thing is that we can know by the color whether we are within the budget or not. And that's, that's something really nice and really like to visualize what is happening. It's very important. Of course, you can add more descriptions here and you can add more, more elements to the table and you can do further, but this is how it usually works. So for example, now if I add this number and I press on enter, as you can see, it has incremented this number, which has incremented this number, and it has turned it into red. Now whenever I look at it, I will be like, OK, I spent more than I should have spent within this month. So I should be careful what I do. And of course, this can carry on for the next month. So you can know the next month. For example, let's say you create a new sheet. This new sheet will build the month of July. And then all you have to do is switch between sheets and you can just monitor your monthly spendings and you can understand better how you're spending and whether you are saving money or not by looking at the number here. Thank you for attending this lesson, and I'll see you in the next one.