Excel Tutorials | Master the Basics of Microsoft Excel

1. Introduction: Hi, my name's Josh, and I'm here to teach you the skills to become much more proficient in using excel. First, let me show you why XLE so powerful. You can use it within business for data analysis, invoicing, and accounting. You can use it personally for budgets, schedules. And to do this, you can use it for planning, project management, and charting. Those are just to name a few, there are endless possibilities. I hope this gives you a real insight into how powerful the application is. So with that being said, I've created this course for complete beginners to get you proficient in using this tool. Please don't feel like you meet, have any prior experience within Microsoft Excel. Each course is split into short, easy to digest modules with exercises to work through alongside the videos. If you know a topic, feel free to jump ahead. It's that easy. Throughout the course, we will cover all the basics of Microsoft Excel, including the toolbars, formula, buzz, and common keyboard shortcuts. We will then move on to data entry, ensuring you understand how to enter data and any common concepts you should be aware of. We will finally move into the formula and functions of Excel. Will focus mainly on the basic standard functions in Excel. Taking you through useful exercises to show you how to use these functions. By the end of the course, you should be adept and using Excel for everyday tasks. 2. Chapter 1.1: Menu Bar: I'm going to take you through a quick intro into the menu bar. So these rows of tabs and icons at the top are referred to as the Microsoft Excel ribbon. This aims to separate all the tools into different components. Each tab is then split into groups, such as the clipboard, font and alignment. Within each group, you have the command buttons which will perform a specific function. And since all the commands cannot found the ribbon, there's a dialog launcher at the bottom right of each group that will launch additional commands that are related to that specific group. It might seem intimidating. Have a please don't feel the need to try and understand every command button. You should just try to familiarize yourself with some of the main ones we shall go through. Now, the Home tab contains the most frequent commands that you'll tend to use. This includes cut, copy, and paste, formatting your data and sorting out your data and filtering. The Insert tab is used to add objects into your worksheets like pivot tables, shapes, charts, and even equations. Page layout tool has tools to manage the appearance on screen and also tools for printing these commands, for example, include controlling your margins, the orientation, and the print areas. The Formula tab allows you to insert built-in Excel functions. Allows you to define name ranges and audit your formulas, tracking the dependent and preceding cells. Some of you may be thinking, slow down, what the hell does tracking dependent and preceding salesmen will come into these in future videos. So don't worry. Data tab contains your data management tools. We can sort and filter the data. Also remember, if you're not sure about what a command does, just hover the mouse icon over the command and you'll get a pop-up with some additional information like so. The Review tab allows you to spell check, add comments, and has just security protection commands, such as protecting your sheets and protecting your workbooks. The View tab allows you to customize the worksheet views and freeze the pains. The Developer tab is a tab that many of you may not actually see as by default, Microsoft hides this icon with AB. It's not important to add this tab as its uses are mainly to create macros via VBA. This tool is much more advanced. Lastly, I'll move on to the File tab, which is slightly different from all the other tabs. This is also known as the backstage view and contains Essential fall related commands and Excel Options. You can create new documents, open different files. It also contains saving function and printing functions. To get yourself back into the Excel spreadsheet from the file tab, either hit the back arrow here or press escape on the keyboard. Another thing that you should be aware of is you're able to customize your ribbon views. Simply select this icon in the top right corner, which will give you the following options. You can auto hide the Ribbon, so will only be visible when you click the top of the screen. You can just show the tabs and the commands will only become visible when you click a specific tab. Or you can set it so that the tabs and commands are visible at all times. So this allows you to tailor the screen tearing preferences and situations. For example, if you need to make more rows visible, you can hide the Microsoft Excel ribbon. So that was a quick whistle-stop tour through all the various tabs and commands. I hope it was useful and it should give you a basic understanding. And honestly, don't feel the need to try and learn all the commands that will come to you as we go through this course. And you start using the application more and more. Also, at the start, the main command you're likely to need will be on the Home tab. 3. Chapter 1.2: Quick Access Toolbar: On the top left-hand side of your screen, above the ribbon toolbar, you'll have what is known as the Quick Access Toolbar. By default, it only S3 buttons in it, a save button, an undo button, and a redo button have a DES can be customized to your own preference. As you may have noticed, I have removed the default commands and put my N. This is because save, undo, and redo can be done via easy keyboard shortcuts. But don't worry, have an upcoming video in this first chapter that takes you through some of those most common shortcuts. For those who are wondering the commas I have put, they are Paste, Special Paste Values, filter and remove duplicates. For me, this toolbar is the perfect place to add commands that you use most often. And it saves you having to search for them through the various tabs. For example, say I use the pivot table command a lot. Instead of having to manually go to insert and then try to find the pivot table. I can add this command to the Quick Access Toolbar, and that will always be present at the top of the screen. Just right-click the command and select Add to Quick Access Toolbar. Now, the combine is always at the top of the screen and you will have very easy access. Another method for adding commands to the Quick Access Toolbar is to select this arrow icon. You'll then get the option to add a selection of commands. And if the command that you want is not on the list, you can select the more command option. Here we can search through popular commands, all commands or commands and specific tabs to add them to our toolbar. So let's go back to the Popular Commands and add the cut command. We can also rearrange the order of the commands within our toolbar to our own preference. As you can see, we can move the cut command to second in the toolbar. Changes take effect when you hit OK. And finally, we can remove any commands we do not need by selecting the command on the right-hand side and clicking the remove button. You can also remove commands from the Quick Access Toolbar by right-clicking and selecting removed from Quick Access Toolbar. One thing I often use with the Quick Access Toolbar, the ability to shortcut actions. When you hit old, you can quickly select one of the commands from the top by hitting the numerical button that corresponds to the commode you want. So for example, if I wanted to filter, I can press old cluster three and that will select the Filter command. I will show you an example here by highlighting the cells in row seven that I wanted to filter and then hitting all three on the keyboard. You can see that the filter has been applied and he can collect the data you want to include or exclude. So there you have it. A quick intro into the Quick Access Toolbar, which I would definitely recommend you use. 4. Chapter 1.3: Workbook & Worksheets: So what are workbooks and worksheets in Excel? Workbook is defined as the Excel file. It consists of multiple worksheets, as well as any other information within the file, including things such as macros, which are used to automate certain tasks, and something that you don't need to worry about at the beginner level. A worksheet, on the other hand, is a single spreadsheet consisting of rows and columns. Each cell within the spreadsheet has its own specific address. So for example, this cell selected as the address D4, as it is in column D and rho for. And the address can be seen in the top-left in this box known as the Name Box. You may wonder how many columns and rows excel can go up to. And simply pressing right will take us some time to get to the end. As you can see, there is a shortcut. And by simply pressing Control plus right, you can go to the end of the worksheet, which is column x, f d. Numerically, this will be difficult to work out how Excel supports two to the power 14 columns, which numerically is 16,384 columns. Now in terms of rows, XL supports two to the power 20 rows, which numerically, as you can see, is over a million rows. You can see the plus sign in the bottom corner next year worksheet. By selecting that, you can insert more worksheets. You're able to right-click on the worksheet and rename it, copy it, and move it. Let's quickly rename it to test. To rename a worksheet, you can also double-tap the tab name, which will allow you to amend the name. In certain circumstances, you may want to call your tabs differently to emphasize different things. For example, summaries can be a one-color. Graphs could be in another color, and the underlying data could be in a third color to provide some easy visualization to your workbook. To color your tab, simply right-click and select Tab Color, and then the color of your choice. That tab we'll now change color. Indicates you don't want to delete a worksheet, but do you know when it visible to others? You can also hide your worksheets. Simply right-click and select Hide. To unhide any worksheets. The same again, simply right-click, click and hide and select the worksheet we would like to unhide. In scenarios where you do want to delete the worksheet, simply right-click the tab, click delete and it will be removed. So as you can see, Excel can support a lot of data. We have over 1 million rows and over 16 thousand columns per worksheet. And we have the ability to create multiple worksheets. 5. Chapter 1.4: Formula Bar: A formula by in Excel is a special toolbar at the top of the Excel worksheet, which shows the data in an active cell. If the cell contains text, it will show the text in the formula bar. If the cell contains numbers, it will show the numbers in the formula bar. If the cell is derived from a formula, it will show the underlying formula in the formula bar. You'll be using this bug constantly whenever you use Excel. In the event you have a lot of information in a cell and you can also make the formula bar bigger. You can simply click the downward arrow on the right-hand side of the bar to increase the size. 6. Chapter 1.5: Shortcut Menu: Shortcut menus provide a quick way to access the commands that you will likely need. With Xcel being such a powerful tool, it can seem quite overwhelming. Trying to find the commands you need. Shortcut menus are menus displayed when you press the right mouse button. We've used these earlier on in the course when you could right-click the Quick Access Toolbar to activate a shortcut menu. They specific to where you click. As you can see when I click on the Quick Access Toolbar, it displays relevant commands to help a user. For example, I can remove the command or customize the toolbar. Another scenario could be that I want to insert a column between these two cells. If I simply right-click the column, it will display a shortcut menu that has the answer option, which can also be simpler than trying to find it manually by searching the menu bars. When highlighting cells, you can also use the shortcut menu above that displays common formatting commands. We can change the cell color or the text color, or even bolded text. 7. Chapter 1.6: Status Bar: The status bar is found at the lower edge of the worksheet and displays information about the excel worksheet. It displays information such as the average count and some of certain cells that you have highlighted as shown here. Perhaps I went to see the minimax numbers. I just simply select it from the options and they will be displayed when our highlight a range of numerical cells. Perhaps I would like to see what my caps lock is on. I simply select this option. And when I hit the Caps Lock key on, you can see the status is displayed at the bottom. Overall, the status bar at the bottom of the screen can be very helpful to see certain attributes easily and quickly. 8. Chapter 1.7: Common Keyboard Shortcuts: I wanted to put a section here to explain some of the most common shortcuts that I'll be sure to speed up your productivity. Even though everyone's needs a difference. I have tried to create a simple list of shortcuts that'll be pretty useful for the majority of people here. And for those of the Apple uses, I've also included the equivalent short hub commands next to the Windows ones. So without further ado, let's begin. Firstly, the program shortcuts. Control plus N creates a new workbook. Control plus O opens an existing workbook. Control plus S saves the workbook. F2 opens the Save As dialog box. Control plus zed to undo an action control plus y to redo an action control plus F11 inserts a new worksheet, S7, check spelling. So moving around a worksheet we'll sell. You can place control plus page up, page down to move to the next or previous worksheet. You can press control plus up or down to move to the top or bottom cell in the column. You can press control plus right or left to move to the farthest cell, right or left in the row. Control. Home moves to the beginning of the worksheet. For selecting cells. Shift plus space selects the entire array. Control plus base selects the entire column. Control shift plus right or left to extend the selection to the right or the left. Control. Shift up or down to extend the selection to the top or the bottom. Control plus a selects the entire worksheet. Formatting cells. Control plus B adds or removes bold to the contents of the cell. Control plus I add or remove italics to the contents of the cell. Control plus U adds or removes the underlying to the contents of the cell. Control plus nine hide selected rows, control plus 0 height selected columns. So that was some of the top keyboard shortcuts for Excel. Hopefully some of the shortcuts described today will make your work within Excel that much easier. 9. Chapter 2.1: Data Entry: Data entry is one of the most common uses of Microsoft Excel and is fairly self intuitive. However, there are some concepts that you might not be aware of. I'll be working through the chapter two workbook. So please have that opened and go to the data entry and basic formatting tab. Firstly, when entering text in a cell, you'll notice that the cells are automatically left aligned. And when you enter numbers or numerical formulas in Excel, you'll notice that the cells are automatically right aligned. This is just excel attempting to make the data much easier to read. However, if you want to change the alignment of the text, you can click the commands here in the alignment group to change them on the Home tab. Now, what we're going to do is learn some of the basics of data entry in a practical way. We are going to create a simple monthly budget spreadsheet together and get you familiar with Excel. So first, let's write our headings. Will have income in cell A1 and expense in cell B1. Next, we'll just fill in the income and expenses with random numbers. As you can see, the headers have been left aligned automatically and the numbers have been right aligned automatically. Let's amend these alignments and make everything central aligned. Simply highlight the cells, and then select the Center Alignment command. You can do this in multiple ways, but the two most common are either going to the Home tab and clicking the center commodity in the alignment group, or right-clicking and bringing up the shortcut menu where the center command is one of the predefined commands just at the top. And next we will look at some basic formatting commands to improve the look of the data. First, let's bold the headers to make them stand out. I can do this by selecting the bulk mode or the shortcut Control plus b. Next, let's make the expenses read just to highlight these are negatives. And finally, let's insert three rows to add a title. There's several ways to insert a row. We can select the cell and then go to the Home tab, find the insert command, hit the drop-down box and select Insert Sheet Rows. We can right-click the entire row to bring up the shortcut commands and select, insert. Or we can right-click the cell to bring up a different shortcut command. Select, insert again. But notice this time we get a pop up as we only selected the cell. If we select shifts cells down, it will just insert a new cell in that column. If we select the entire row, it will insert a new row. This is the one we want. Now in cell A1 will create a header called monthly budget summary. Let us change the font to something more interesting. This can be done by going to the font bar on the Home tab shown here. You have a huge selection of texts I like in free. So let's type that in to quickly find it and select that font. Let's now bold the header text and make it bigger. To make it bigger, you just need to select the size by hair. And we can increase the size to say 16. That looks a bit better. But now let's make it stand out more by changing the colors of the cell and the font. To change the cell color, go to the fill color box and click the arrow to bring up a selection of colors. I'll go with this dark blue. Now change the font color to something more readable. To do this, go to the Font Color books, and click the down arrow to bring up a selection of colors. Let's go with white. We can only see part of the text now. So to fix this, let's do a few minor tweaks. First, let's merge cells A1 and B1, so that the title spans across both the cells. You can do this by highlighting the cells and then selecting the Merge and Center command here. The title is still not fitting because by default, Xcel keeps all the text in a cell on one line. By selecting the Wrap Text command found on the menu bar, we can display the contents or multiple lines within the cell. Now all we need to do is increase the row height by going to the bottom of the cell and waiting for the icon to change like so. We can change the row height to something more suitable. Now you've managed to cover a lot of basic commands and create a simple data entry spreadsheet. For those who may have found this a bit too fast, please go back and take your time going through the video. 10. Chapter 2.2: Autofill: Now let's get a little more complex and take a look at the autofill function in Excel. The autofill function allows you to quickly fill cells with data that follows a particular pattern, helping to speed up your time. Let's first test this with dates will begin by typing January in cell A5. Now instead of having to type each month manually, we can move our cursor to the bottom right corner of the cell and it will turn into across. This is known as the fill handle. Click and drag this down. And you'll notice how Excel auto populates the cells with the following months. Drag this down to cell A6 to fill the months all the way up until December. Next, we will try to auto-fill with formulas. If you select the cell E5, you can see the formula here, which calculates the net figure for January, which is the sum of the income and expenses. If you move the cursor to the fill handle at the bottom right corner and drag it to cell E6. You will see all the cells auto populate with the formula, saving you time. You can also see that the cells the formulas are referencing have also moved down. That in a nutshell, is the autofill function. And it is a very useful tool to auto populate cells, saving you time. 11. Chapter 2.3: Date & Time: Excel has the ability to understand and convert dates entered in several different formats. You can enter the dates like this with a slash, or you can enter them like this with a dash. And you'll notice that Excel recognizes them all and standardizes them into a value. You will also notice that dates are right aligned. And if you do enter a day in correctly, for example, the 32nd of Jan 2020, this will be left aligned because this is not a valid date. An excellent thinks this is just text. The formats of dates can also be a mandatory and preference. We can see these dates in one format in column a. I'll copy these over a few times to column B and C like so. And I'll show you how to update the formats of these dates. Firstly, highlight the dates in column B, right-click and bring up the shortcut menu, and then select Format cells. You now have the option to change the format to the options here. Let's pick this one and see how it looks. We aren't just limited to these date formats. We have the ability to customize even further. So going back into Format cells like say, now select Custom on the left-hand side. Now scroll down until you see the types with d, d at the start, which actually references the day. You can see in the sample box the format of all these options. So DD, dash m, m, m, y gives you the date with the month in its three digit form and is one R select. Ok. Now moving on to times. You can display times in different ways as well. You can enter times with the 24-hour clock like so. Where you can enter it with PM or you can enter it with am. You can see it is now converted and stored as a value. Just remember, when entering times you need to use the colon symbol. If you use a full stop, for example, XOR weren't recognized the time as appropriate time value. It will think it's just text and it will be left aligned. Formatting is just the star. Xl can also do calculations on day n times. I won't go into this in too much detail now, I'll leave that for later chapters. But if you go to the Formulas tab, and then the date and time command, you can see a list of date and time functions that we can do within Excel. 12. Chapter 2.4: Notes & Comments: Excel has two forms of note-taking that have different purposes. You can have conversations with others via the comments. We can add annotations via notes. So comments are used when you want to have a discussion with other people about some specific data. You can simply right-click the cell and select new comment. I'll put that this figure looks inaccurate and then hit the green Post button. Others can reply to continue the discussion. Let's poster apply as if from someone else answering that question. And let's say we made a mistake on the original reply and need to edit the response. We can't by simply hitting the edit button in the bottom right, will quickly make that change to the January figure. And now we can close the threat. To close the thread, simply click the three dots in the top right corner like so, and select resolved thread. The thread still remains Bayes grayed out when you hover over it. You then have the option to reopen the threat. Or deleted. Notes, allow you to add annotations to cells. To add a note, simply right-click the cell and select new nodes. You can then type, you know, like say, for more actions are nodes, right-click the cell and the following options are related to the note. Editor. Note, delete a note and show high donate. The show hide Note option toggles between showing the node at all times like so. We're only showing the note when you hover over it. 13. Chapter 2.5: Undo & Redo: One of the most common commands I use every day are the undo and redo commands, which I also mentioned in the shortcut video. Saying you accidentally delete some cells that you weren't meant to do. For example, like say, don't worry, you can easily get this data back by hitting the undo command. All you need to do is press control plus zed and voila, your data is back. Now it's not just the last action you can Andy, you can undo several past actions, delete some cells at random. And you can see that I cannot do all of these actions sequentially. Now perhaps you actually did decide that you want to delete this data. You're able to quickly redo that action by hitting control plus Y on your keyboard. And like so, the data is now deleted. So that was a summary of how to quickly undo and redo actions in Microsoft Excel. 14. Chapter 3.1: Formulas & Functions: Using formulas is one of the primary uses for many Excel uses. In this video, I'd like to show you how to enter formulas into Excel and introduce you to some of the most common formulas and functions that you will tend to use. First, please open the chapter three workbook and go to this simple formula tab. I wanted to show you how we enter a formula in the cell for anyone completely new to Excel. To make things simple, I've highlighted the cells in orange where we will enter data. So let's start with calculating the total income for the first quarter of 2020 in cell B seven. When entering a formula, you need to start with the equals character, like so. You then have multiple ways to calculate the total income. You can select each so you want to sum and then enter the plus key, like so. And when finished, hit the Enter key. And that will calculate the total. However, if he have several cells to sum, this can be very time-consuming. So a better alternative is using one of the many excel functions. Make sure to start with the equals character and then type SUM, and left parenthesis. All functions are followed by an open and close parenthesis. Next, select the cells you would like to add, like so. You can then press enter and exit will automatically add the right parenthesis to the formula. As you can see, you get the same results. We can also enter subtraction formula is easy as well. In this example, let's find the difference between March and FEB income. Simply select the cell type equals and pick the cells you want to subtract. Excel will then display the differences between both figures. Let's now try some other common formulas. Firstly, let's look at multiplication and division. In this example, we have a figure of 2600 for January. Say we wanted to forecast the yearly returns based on this Jan. figure, we can multiply it by 12 in cell B5. All we need to do is type equals select cell BY 13, type the star symbol, then type 12 and hit enter. Now for a division example, if we want to calculate the daily income by dividing the Jan figure by 31, all we need to do is type equal, select cell B3, 13, type the forward slash symbol, then type 31 and hit Enter. There we have it. That is how you multiply and divide in Excel. Next, we'll look at some more common functions. Excels average function allows you to automatically calculate the average without having to do this manually. Much like when using the sum function, we have multiple ways to enter this. Selecting cell B24. We can select the function by going to the Formulas tab, clicking the drop down box under the autism command and selecting average. As you can see, it has Auto selected some cells which we can adjust as needed. We can also manually enter the formula like say. You'll notice there are several different variations of the averages are useful in different situations. We won't go into these now, but it's good to be aware that these exist for now will go for the regular average function. And finally, the count function. This returns the number of cells that contains numbers. Let's enter the count function. We'll just type this in like so. And similar to the average, there are different variations of count. However, we will stick to the normal one, type the open parenthesis, and then select the cells and hit enter. As you can see, we return a value of 18, which means there are 18 numerical figures. If we delete a figure like so, it will go down to 17. If we type texting, you'll notice that the figure will still remain as 17 as this function only counts numbers. You should also be aware that when you highlight cells, the status bar at the bottom contains useful information. So if we highlight some cells like say, we can see that the average count and some are visible. And there you have it. Hopefully now, you can feel more comfortable when using basic formulas and functions in Excel. 15. Chapter 3.2: Cell References: There are three types of cell references in Excel, relative, absolute, and mixed. This may sound complicated, but they are simple and very useful concept. And by the end of this video, you'll be able to take full advantage of them. First, start by opening the Chapter three workbook and going to the reference cells tab. As mentioned in the previous video, I have highlighted the cells in orange where we will be entering data. So first, let's talk about the relative cell reference. When a cell with a relative reference is copied to another cell, the cells being referenced will change based on the relative position of both the cells. This would be much easier to understand with an example. So let's calculate the value for product a in cell D for this will be the unit price times quantity of product a. So B4 times C4. Now if we double-click the fill handle, it will auto-fill the formulas to D6, like so. You'll notice that the cell references of the formula have now changed relative to the position of the cell. For example, the value in d5 is the multiple of cells B5 and C5. And the value in D6 is the multiple of B6 and C6. This relative reference is very handy to quickly autofill formulas. Now let's move on to the absolute cell references. There are many instances where you might not want the cell references to change. This means that you want to lock a cell when copying formulas to another cell. To create an absolute cell reference, you need to add a dollar sign before the row and column. You can do this manually or you can hit F4. Now take you through an example of when absolute cell referencing can be useful. We have a bunch of products where we want to apply a tax of 10% stated in D 16. The formula for the tax for project a would be d 12, the value multiplied by the tax rate in D 16. Let's enter that with a relative reference for a starter, I-I know dollar signs. Now if we copy this formula down one cell, we can see the formula's working. This is because of the relative reference and we're no longer referencing the 10% tax figure. To fix this, we can look D6 by selecting this cell in the formula bar and hitting F4 tip. If you hit F4 once, it will look both the cell and the column. If he hit F4 twice, it will lock just a row. Only. If he hit F4 three times, it will lock the column early. And if he hit F4 times, it will not look any cells and go back to relative referencing. Now moving back to the example, we want to lock the cell referencing the tax rate, which is d 16. So let's do that now. We can now copy the formula down to the other cells. And you'll notice the tax rate is locked in the 16. The value reference moves down each cell. This shows you a simple example of how useful absolute referencing can be. Now moving on to the mixed cell reference, which is sort of a combination of both relative and absolute reference. It is where we want to only fix the row or the column. Let me take you through an example. So in this example, we have three products and the numbers we've sold during jan, February, and March. In the highlighted cells, we want to calculate the values, which is the price multiplied by the quantity sold during a month. Let's do that for Jan. So we will multiply B24 with D24. Now if we copy this to February, we'll notice that we want to lock the cell to the unit price column. Let's do that now with an absolute reference. You'll see that works for FEB. However, let's try copy that for Jan for product B. You'll notice that we are still referencing the price for product a, which is incorrect. This can be easily solved. Let's go back to the January Valley. And instead of looking both the column, Android just knocked the column by clicking the reference in the formula bar and pressing F4 to more times. Now let's try copying this to all the cells. We can now see that all the correct cells are being referenced and shows you the power of using different cell references within Excel. So in summary, this is a relative reference with columns and rows will change after being copied. This is an absolute reference with columns and rows do not change after being copied. This is a mixed reference where the rows do not change after being copied. This is a mixed reference where the columns do not change after being copied. 16. Chapter 3.3: Vlookups & Hlookups: The main reason for using VLookup or hedge lookups is to easily retrieve information in a large dataset by looking at a specific value. Vlookup stands for vertical lookup. It is a function that you use to look up a value in a specific column and return a value in a different column in the same way. Hey, HLookup stands for horizontal lookup and is used to look up a value in a specific row and return a value in a different row in the same column. Therefore, depending how the data is structured will depend on which of these functions you will use. An easy way to think about this is if your data has column headers, you should use a v lookup. If your data as row headers, you will need to use a HLookup. I'll start by going through the basics of the VLookup function. One of the biggest limitations and things you should be aware of with the VLookup function is it can only look right. Therefore, using an example dataset hair, where the data has column headers, you must ensure that the value you are looking up is on the left hand side and the values you want to retrieve or on the right hand side. So when this data, if we wanted to retrieve an address or e-mail by looking up a name, that would be fine as a value we are looking up is on the left-hand side. However, if we wanted to retrieve an address by looking up an email, we will not be able to do this using the VLookup function as the lookup value is on the right-hand side. Let's jump right into an example of a VLookup now, as that is usually the best way to learn. Let's say you want to try and retrieve the address and email records by looking up the name. The orange highlighted cell is the lookup value and a bold itself or the data we are retrieving via the lookup function. As an example, let's enter Frank Michaels in the ten cell as the lookup value. Let's then go to cell b, 11. And here we want to retrieve the address by looking up the name. So let's start by typing v lookup and open parenthesis. A helper book should appear showing you exactly what you need to type in. So first we need to choose our lookup value. So that will be cell B21 and less ensure it's an absolute reference around this so that it locks in place when we copy down. This is done by hitting F4 once. Next, we need to highlight the table array. The table array is a range of cells used within the VLookup. So let's highlight cells A3 to C8 and will ensure an absolute reference. Again. The next thing we'll need to enter is the col index num. And in this case, since the address is the second column across from the lookup value, we will need to enter to hear. The range lookup is the fourth argument and determines whether we are looking for an exact match or an approximate match. By entering false or 0, we will look for an exact match, or by entering true or one, we will look for an approximate match. I'll go into the different matching modes a bit later. But to keep things simple, in most instances, you'll be looking for an exact match. So enter 0 or false here. Great. Now you can see the address is correctly pulling through the address of Frank Michael's. Next, we'll do the same for email. I'll quickly copy the formula we use for adress to save time. And the only argument will need to change is the col, index numb. This is because the e-mail is the third column across from the lookup value. So let's enter three here. And as you can see, we are able to easily retrieve the email addresses. Changing the name inputs to something else in the list will then update the address and the email information like so. Let's now look at which lookups. As you can see here, the same data has been arranged, so there headers are in the rows. Next, let's enter Frank Michael's in cell B22 as the lookup value like before. Let's then go to cell B23 and retrieve the address. So let's start by typing HLookup and open parenthesis. A helper box again appears showing exactly what you need to type in. Will select cell B2 as the lookup value. Again, ensuring it's an absolute reference by hitting F4. Next, we need to highlight the table array. So let's highlight cells 18 to 20 and again hit F4. As this is a HLookup, we will need to enter the row index num. Since the addresses in the second row down from the lookup value, we will enter to hair. For the range lookup, we will again use an exact match. So enter 0 or false hair. And now you can see the address is correctly pulling through the address of Frank Michaels will again repeat this for email, but we will change the row index numb to three as the e-mail is in the third row. And again, changing the name inputs will work exactly like the VLookup and update the address and email information like so. I wanted to go a bit deeper into the matching modes. So this is the difference between doing an exact match, entering one or true in the range_lookup are given, or an approximate match entering 0 or false. I'll start with explaining the exact match as this is the most common one you will likely use. And the ones we have used in previous examples. All these v lookups are using an exact match. An exact VLookup match will only return a value if the value you are looking up is in the leftmost column of the table array. However, there are some caveats. If you have a spelling error or the value does not exist in the table, you'll get an error. So let's write a name that doesn't exist. As you can see, you get an error message. If the value exists multiple times to lookup will return the first match it finds. So for example, we have changed as a firstName twice hair. In 33 and 36. The VLookup will only retrieve the values of the first james it finds in the list which is in row 33. The VLookup is not case sensitive, so it doesn't matter if the value is in caps or lowercase, it will still show as a match. This however, can cause issues, especially if you wanted to do a case sensitive lookup. There are ways around this day. Here we have used a combination of the index and match functions to do a case sensitive lookup, I'll provide a separate video taking you through this in more detail. But you can see we get an error message when we type the name in all caps, locks, or lowercase. Next, let's go over the approximate match, as this is less frequently used by a, can be really useful in the right situations. Here is a great example of when it's useful. Say we have a list of discounts that we want to apply that is dependent on the order value. The more you order, the more discount that is applied. And East discount instead, we can use the approximate match to quickly work out the discount rate. Below this table, we have a list of customers and their order values. What we want to do is enter a VLookup to Pylint discount codes based on their order values. So fn order value is less than $25 thousand. It has 0% discount. If an older value is less than $50 thousand but equal to or more than $25 thousand, it has a 2% discount. If an order value is less than 75 thousand by equal to or more than 50 thousand as a 4% discount and so on. This is the perfect scenario where you want to use approximate matches instead of an exact match. So now let's look at the VLookup formula. As we can see, the lookup value is the order value. The table refers to the Audit table with the discount rates and we are trying to retrieve the correct discount rate. The main difference is we have a one as the range lookup to signify an approximate match. You could also put true here as well. You will now notice that the discount rates are determined by the order value. If we change this one to 60 thousand, discount will change to 4%, like so. Tip. The key thing you need to remember when using an approximate match is you need to ensure the values in the table are in ascending order. In our example, the older values are going down in ascending order, is they aren't. You'll either get errors or incorrect figures being displayed. You can now see how approximate matches can be a very powerful tool, especially if you needed to populate the rates for a large dataset. 17. Chapter 3.4: Named Ranges: In this video, I want to show you how named ranges can be used to make formulas easier to understand and make complex spreadsheets much simpler. When looking at formulas in a spreadsheet, it can have multiple cell references, which can be quite confusing to understand. To simplify this, we can assign a name to these cells and ranges to make it much more helpful. After a cell or range has been assigned a name, we can use that name in place of the normal cell reference in formulas and macros. Let me take you through some examples now. In this first example, we have a tax rate at the top. We want to reference in another part of the worksheet to calculate the tax Valley. Traditionally, to calculate the tax amount, we can enter equals d five times b2. And that would give you the answer. But when looking at the formula is not that intuitive. What the cells are referencing. What we can do is name cell B2 and then use that name within our formulas to define the name of a cell. Simply highlight the cell you want to name, and then find the name box in the upper left corner of the screen. Then type a name that will be relevant to the cell. Be aware that the name cannot contain any faces. Once done, press entity, finished naming the cell. Now let's try using the name cell in a formula. So instead of having T5 multiplied by b2 to calculate the tax amount, let's replace V2 with the cell name. As you begin typing the name of the cell, you will notice the helper box will also include name cells in the suggestions. We can also copy down this formula. If we need to amend a named range, you can do so by going to the Formulas tab and selecting Name Manager. Here you can edit the name or the range of cells the name refers to. You can also delete name ranges hair by selecting the range and hitting delete. Now in the next example, we have a dataset that includes mountains, transaction day's, transaction types, and amounts. We have the ability to not only name a cell, but name a range of cells that we can reference. So first let's highlight D2 to D21 and name this net profit in the name box. As a simple example, we can easily sum up the total net profit by typing equals sum, open parenthesis, net profit. Instead of having to highlight the cells. Now we can also combine multiple named ranges in a formula. Let me show you an example by calculating the net profit for each month. So let us highlight a twelv to 1821 and name this month end in the name box. We'll start with the equals, some f formula and open parenthesis. For the range, we will write month end. And you'll notice the helper books will include the named range for criteria that select a 26. And for some range, we will type net profit. Unlike So, the net profit for the month is calculated. Shang He had his multiple named ranges in a formula. And we can also copy this formula down. The final example is using name ranges with the data validation function in Excel. This creates a simple list of predefined data for users to select. In this example, we have a list of expense types that we want to create a drop-down list for. First, let's highlight cells a 33 to 838. Then let us name these cells as expense type. Next, let us select the cell where we want to add the list of expense types, which is before T1 and go to Data and then data validation. In the allow criteria, select list. In the source box, enter equals expense type. Then select OK. Now as you can see, the cell changes to a drop down list where we can select the different expense types. We can also copy this down. So there you have it. Here are just a few of the useful examples of where you can use named ranges. 18. Chapter 3.5: Formula Errors: Microsoft Excel will display different error messages depending on the reason for the error. There are seven error messages that you are likely to come across was using XL. Are now going through these one-by-one, explaining exactly when and why you are receiving each error message, starting with the divide by 0 error C, you'll get this error whenever a formula tries to divide a number by 0 as shown here. You can also get this error if you ever tried to divide a number by a blank cell. Moving on to their next error message, we have the NA era. You will get this error when you have data that is not available. For example, we have a VLookup hair that is trying to find plum in the dataset have is since that does not exist, Xo will display an NA era. Excel will also display an error. If you try to summer range that contains an NA era. You can see that when we change this N a in the range to a number, the formula no longer generates an error. The name error occurs when Excel does not recognize a formula, usually due to a spelling mistake. As you can see in the following examples, we are spell VLookup incorrectly. This has resulted in the name error. I'll show you another example with some being spelled incorrectly. We will also get a name error if you reference texts to the cell without the quotation marks. In this example, the formula is trying to display the number of characters in the word orange. For the formula to work, we need to have orange in quotation marks as it is taxed. Once I do that, you can see the formula corrects itself. The next era is the num error message. When Excel calculates an invalid number formula, it will generate a non era. The example here is the square root of minus ten. Since all square numbers are positive, as square root of a negative is an error. Excel has limits on the smallest and largest numbers you can use. If you go outside this range, you will get another mirror. Here we've got a value of a 100 to the power of 1000, which is outside the range and cause a numeraire. The href error occurs when a cell a formula was referencing has been deleted. As you can see from this formula, there are three cells being referenced. If I delete one of these rows like site, you'll see the XOR generates a href era. We're looking at the actual formula. You also see that the cell reference has now been replaced with the error code. The value error occurs when there is an unexpected value in the formula. For example, the sum formula will only work with numbers. If we change the data in a cell to a text, you'll see a value error message being displayed as a sum formula cannot be computed. And lastly, we have the null era. This is one of the least common areas you see in Excel and usually occurs when you enter incorrect range operator in the formula. For example, here, we have a space instead of a colon as the range operator. As you can see, when changing to a colon, the error disappears. I hope that helps in explaining the different error messages in Excel and exactly why they may occur. 19. Chapter 3.6: Formula Auditing: This video is all about teaching tools to quickly audit and troubleshoot your formulas. This will allow you to easily find errors and trace the relationships of your formulas. The formula auditing commands can be found by selecting the Formulas tab and then going to the Formula Auditing Group found here. Will now go through the main tools are used to review and check my formulas. First, let's start with tracing proceeded cells. This command allows you to trace which cells affect the value of the cell selected. So let's show this in action. If you select E5, we can see that the value in this cell is derived from the multiplication of the tax rate in B2 and the value in D5. Clicking the trace proceeded cell, we can see this relationship in graphical form. For example, we can see the blue arrows from B2 and D5 to the cell selected. Now let us go through tracing dependent cells. So this command allows you to see which cells are affected by the cell you have selected. So for example, by changing a value in this cell, will other cells will change. Let's click E5 and select the trace dependent command. We can see an arrow going to cell F5, which shows it is a dependent cell and means that F5 will change if the value of cell E5 changes. We can also see this if we highlight the formula in the cell of F5. These are really handy waste to graphically display the relationships between cells. Natural move these areas. You can either select the Remove arrows command to remove all the errors, or you can hit the drop-down box like so, and choose to remove either the proceeding with dependent arrows only. For now, let's remove all the arrows. And that's a really quick way to clear up the spreadsheet. Now moving on, rather than sharing the results of a formula, it could be more useful in certain situations to show the actual formula. Instead, you can easily toggle between showing the values in the formula by selecting the show formulas command. There's also a keyboard shortcut to do this. Just press control plus the tilde key. So this is the key that is usually found in the top left of the keyboard, above the Tab key and below the escape key. The next command I wanted to show you with the error checking command. This provides details on any areas that you come across. In the previous lesson, we went through the different types of areas and what they mean. So in this example, we can see that we have a divide by 0 error. By clicking the error checking command, you can see the era is a divide by 0 error. In this era, we can see that we have a value not available era as a plum value that we are trying to look up does not exist in the table. By clicking the error checking command again, you can see the explanation of the era. This is a really handy tool to understand the reason for any errors you may come across. Just make sure to note that the command will only check the formulas on the current sheet. That in a nutshell is how to use the formula auditing commands to check all your formulas are correct and trace all their respective relationships easily and quickly.