Excel for Intermediate Users - The Complete Course

welcome to Excel for intermediate users the complete course in this video we're going to learn all of the intermediate skills that will help you reach the intermediate level with Excel let's get started so in the previous video Excel for beginners the complete course we created this spreadsheet which is an employee list it's got information about hourly wage taxable income Etc if you haven't watched Excel for beginners the complete course I highly recommend that you do that but for now let's take our skills to the next level in this first segment of Excel for intermediate users the complete course we're going to learn how to maintain a large worksheet or spreadsheet in Microsoft Excel so you can see my employee list is getting pretty long and as the company grows it's just going to get longer and longer the bigger this spreadsheet gets the more rows of data and maybe perhaps even the more Columns of data that I add to this spreadsheet the harder it could be for me to really understand the data and digest it so let's look at some tools that can help us to focus in exactly on the data that we want to see the first tool that we have for this is the zoom options and as you can see right now I'm on the Home tab home ribbon but if I switch to the view tab there's a zoom group and in that group I have three options at least to look at so I'm going to click on Zoom right now I'm zoomed to 100 magnification but I could easily switch to 200 magnification if I do that and click OK the data looks much bigger now the data itself hasn't changed I haven't changed the font size here all I've done is I've zoomed in just like using a zoom on a camera switching back to the view tab I want you to see that there are other options on that zoom button I can switch to 75 percent magnification or 50. let's go with 50 so that's going to make it much further away there's also some custom Zoom options so if I choose custom Zoom I can set the the magnification let's say at 350 percent I tap enter on the keyboard or click OK and that custom Zoom is activated now there is one other option here in the zoom button and that is fit to selection let me demonstrate how this works I'm going to click this button here to quickly go back to 100 magnification that's a very handy Button as you'll often want to do that and I'm just going to use this scroll bar to scroll to the left let's say that the data that I really want to focus on is on my first employees maybe the first 15 employees so I'll just click and drag to highlight from cell A1 through cell F16 let's say I'm not interested in the tax rate and this other data here or the chart so just with this range selected I can now click on that zoom button and choose fit selection click OK and now specifically the range that I selected is now zoomed in on perfect quickly so that it fits within the Excel Window now you'll notice that there's also a zoom to selection option here so this is a quick button that you can click so that way instead of clicking here on zoom and choosing fit selection you can just click this quick button so these three buttons in the zoom group can really help us to focus in on exactly the parts of the spreadsheet that we need to work in right now now if I hover my mouse over the zoom button you'll notice that there's a statement here that says for Zoom you're zooming use the controls in the status bar so what is that referring to it's referring to this Zoom here so if I just need to quickly zoom in or zoom out this may be a quicker way to do that I can just go down here click and drag that bar to the right or to the left to quickly zoom in or out you can also simply click and it will jump to that point of Zoom you can click the plus sign and the minus sign as well and if you click on the zoom percentage here in the lower right you can change it quickly to something else and it's put into effect for now I'm going to switch back to 100 zoom and let's look now at another tool that we have to help us look at specific parts of a spreadsheet or worksheet when we're trying to maintain a large worksheet and what I want to show you is called split now if you don't see split on your view ribbon when you click on The View tab here in this area what you need to do is go up to the quick access toolbar and click this button to customize it you can also right click to customize the quick access toolbar either way you want to get to this point where it says more commands and I'm going to go up here switch it from popular commands to all commands and then I'll browse down until I find Split okay there it is split window I'm going to select it and then click add and that will add the split window tool to my quick access toolbar it is also possible to customize the ribbon itself so if I choose the same exact command split window there it is I could add it to a custom group so for example I could just click here on new group custom and I'll rename that and I'll just call it split click ok so now I've got a custom group and I'll add split window to that split custom group I'll just click the add button now it's added there I'll click OK and now on my view tab view ribbon there's my custom group split and the split window option okay now that I've got this tool what does it do why would I want to use split window let's say I'm trying to compare the hours worked by our employees that were hired first and compare their hours worked to those who were more recently hired it might be hard to do that I might have to keep browsing down the spreadsheet and then back up so instead I'm going to split the window I'm just going to click here on row 16 for this employee Nestor with row 16 highlight related I can click on that split button and look what it's done Excel has added a horizontal line above the row that I had selected if you look to the right you can see that now I have two scroll bars one for everything above that horizontal line and one for everything below that horizontal line so I can use that scroll bar to scroll down I can use this one to scroll down as well and so this way I could very easily look at the bottom of my spreadsheet and the top of the spreadsheet at the same time now what if I regret where I put that split window line well that's not a problem I can just put my mouse over it click and hold the click and then drag the line up or down and it's moved now there are spreadsheets and situations in which you might want to split the window not with a horizontal line but with a vertical line what if I have 15 20 30 different Columns of data and they're obviously not all visible here in my spread cheat all at once yes I could zoom out or do some other techniques to look at that data I could scroll a lot but sometimes it's just better to put in a split windows vertical line so let's look at that first I'm going to go up here to the split window group that I created this custom group and I'm going to click this button it's a toggle button so clicking it removes the split windows line that I had added next I'm going to go to column F and I'll just click on F and then I'll click on my split button again and Excel adds a vertical line to the left of the column that I had selected here it is and now I can scroll here at the right and here at the left once again if I want to move the line I can just put my mouse right on it and then click and drag to move the line if I want to get rid of the split window line completely yes I can click this toggle button but there's also an easier way if you just double click on a split window line it will disappear now some of you may be thinking well what if I want to split it the window vertically and horizontally at the same time is that possible and the answer is yes what you would do is Click not on a column or row number but on an actual cell so let's try cell D15 I select that cell and then I click split and look what happened I got a vertical line to the left and a horizontal line above and now my window is split four different ways I can scroll this way I can scroll this way and I can scroll using this scroll bar and this one here I'm going to undo that and just point out that there is also a keyboard shortcut if you click on a cell and then hold the ALT key tap W and then s and that is the shortcut to clicking this button here so I hope that you'll check out the split window tool when you're working with very large worksheets I'm going to double click on the two split window lines to remove them and let's look now at another tool that we have to help us manage large worksheets you know as I'm working in this spreadsheet the further down the spreadsheet I get the easier it is to forget what these numbers represent and of course I can remind myself by scrolling all the way back up to the top to see row number one but there's a wonderful tool that helps us to not have to do that let's take a look if you go to the view tab here in the window group you should have an option to freeze panes if you click that button you should see three options freeze panes freeze top row or freeze First Column in most cases this is what you want freeze top row so I'll click that you probably noticed a horizontal line was added right underneath row number one now as I scroll down the spreadsheet you can see that row number one always stays visible this is so valuable when you're working in a giant spreadsheet with lots and lots of rows you're going to want to be able to see what that top row is if it has column headings or titles that you want to be able to see you can do a similar thing with the First Column so now as I scroll to the right or if I tab over to the right I can always see which employee ID I'm working with now in this case it would be much more useful to be able to see the employee name not just the number the first thing I'm going to do is unfreeze the panes that I currently had Frozen and then there's a couple of different ways that I could freeze columns A and B so that I can always see the name of the employees I could select column C the entire column and then click freeze panes freeze panes and now everything to the left of the column I selected is frozen it doesn't matter how far over I tab I'll be able to see the employee ID number and name I'm going to unfreeze that because what I'd really love is a combination of the two I want row number one to be Frozen and I want columns A and B to be frozen to do that I'm going to click here on Cell C2 with that selected I'm going and click freeze panes freeze panes and take a look rule number one is always visible and columns A and B are always visible also I hope you can see how useful that is so far in this segment of Excel for intermediate users the complete course we've looked at how to zoom to be able to see the data that we're most interested in we've looked at how to split windows again to help us see what we want to see and we've looked at how to freeze and unfreeze panes please keep in mind that each of these techniques each of these tools does nothing to change the actual data no changes were made to the data itself all we did is we changed how we see that data next though we are going to look at some techniques and tools that do change the nature of the data or the workbook and worksheets that we're working with let's start with how to add name and reorder worksheets and keep in mind that worksheet is a synonym of spreadsheet and you'll notice here in the lower lower left corner I have right now three worksheets or spreadsheets or sometimes they're just called sheets here's sheet 1 here's sheet 2 which is completely blank here's sheet three also completely blank when you're working in Excel sometimes you may just see just one sheet other times there will be multiple Sheets if you'd like to add more sheets into your workbook you can just click this button here to get a new sheet and a new sheet is added and I could just click this button as many times as I want to each time getting a new sheet now I want to just emphasize how powerful this is in Excel you can hold a vast amount of information on just one worksheet or spreadsheet but imagine having two sheets Three Sheets six sheets the amount of data that you can store in one workbook and a workbook is just the collection of all of the sheets that we have in the workbook but the amount of data that you can store in one of these workbooks is just huge now you'll notice that my sheets didn't come in in the Pro proper order for some reason they came in out of order that's okay to put them back in the right order all I have to do is click on a particular sheet hold the click and then drag it where I want it to be I'll do the same thing with sheet 3. there we go now they're in numerical order in addition to adding sheets and reordering those sheets you can also name sheets let's look at how to do that I'm going to click once on sheet 1. when I click once on a particular sheet that sheet becomes the visible sheet if I click on sheet 3 now that's the visible sheet but what if I double click on sheet 2. if I double click on it it highlights the name of that sheet and I can then overwrite it so I'm going to call this 2022 employee list and then I can tap enter on the keyboard that's the new name of this particular sheet if I double click on sheet 2 then I can name that 2023 employee list tap enter so I would encourage you to get to to the point where you use more than one sheet in a workbook if it makes sense to do so and it really is good practice to name or rename those sheets so that you and others that see your spreadsheet know what they're looking at they understand what the data is so double clicking on those sheet titles enables you to rename them one more quick tip about spreadsheet names if you right click on the name of a spreadsheet you can color code its tab so I can make this one green 2023 I could make it yellow and this one will be 2024 I could make that one red the next skill that you need to learn in order to help you maintain a large worksheet is how to name a cell formula and constant in Excel for beginners the complete course I showed you how to name a cell or a range of cells but let's review how to name a cell if I want to I can select a cell that has numbers in it data or it could just be the text like this up here by default this is named C1 this is named C3 and you can see the name for a particular cell by looking here in the upper left corner in What's called the name box so this right now is officially called C1 but if I want to change that I can with C1 selected I can click to select C1 in the name box and then I can type over it I'll just type hourly wage now it's important not to have spaces in the name box so just hourly wage all one word I tap enter on the keyboard and now this cell is called hourly wage the nice thing about that is I can now click in any cell type equals hourly wage notice that it recognizes it and I can even just double click there to enter whatever is in cell C1 into now cell K1 you can tap enter on the keyboard and there it is now as you use Excel more and more you'll see how useful that can be so that was a review of something you learned in Excel for beginners the complete course let's take it a couple of steps further what if I would like to name not just a cell or a range of cells but instead what if I want to name a formula right now I have a formula in cell D 76 and it looks like I need to change the formatting for that cell so it's not currency but this cell even though it looks like it has the number 70 in it it actually has a formula and you can see that here in the formula bar well I would like to name that formula once I've done that it will make it much easier to recreate that formula to call it up at any time in any place in my workbook to do this you just need to click on the formulas tab here in the defined names group there's a button there that says Define name if you click that you get a dialog box that opens up Excel has automatically named this formula number of employees and I think that's a good name for it I could put a comment here if I wanted to but I'm just going to click OK and from now on whenever I use this workbook if I ever need to call up the number of employees I don't have to recreate this formula I can just refer to its name so let's go here to the 2023 employee list maybe I want to in cell B1 for example I want to have the number of employees so I'll just type equals and number and you can see Excel is recommending number of employees I'll just double click on that tap enter and that formula that I created in this first sheet and I named it on the first sheet I can easily execute it and use it here on the second sheet let's look at one other example of things that you can name in Excel again I'm going to click on Define name and this time my example is going to be naming a constant so for example Pi Pi is a constant 3.14 and on and on and on next Excel wants to know what's the scope of this named constant that I'm going to add is it just for this workbook or is it only for particular sheet in the workbook I'm going to say it's for the whole workbook again I could put a comment in if I wanted to next notice that it right now refers to cell d76 and I don't want that in this case I don't want the item named pi to refer to any particular cell instead I want it to refer to 3.14 I tap enter on the keyboard or click OK and now I have a named constant I can go to any sheet in this workbook click on any cell and type equals pi and notice that there is a function already set up for that that comes with Excel but now there's also a named constant that I created and I could double click on that tap enter on the keyboard and the constant is put into my spreadsheet now you can also Define names for other types of constants for example let's say there's a teacher that teaches a particular class every Monday Wednesday and Friday and maybe they find themselves constantly typing Monday Wednesday Friday into the spreadsheet they could go in here and erase the sheet reference and the cell references and put in a left curly bracket and in quotes they could put in Monday close quote comma another quote Wednesday another quote comma another quote Friday close quote and the right curly bracket click ok so now anytime the teacher wants Monday Wednesday Friday they can click on a particular cell equals MWF tap enter on the keyboard Monday Wednesday Friday okay the final skill that I want to show you in this segment of the video is how to quickly enter today's date and time when you're using a very large worksheet and especially when you're sharing it with others it may be important to record the last time that you edited a spreadsheet so here on Cell J1 I've clicked and I'm going to type last update tap enter I could double click between J and K to make sure the words fit within the column and then here on cell K1 I'm going to click and I'm simply going to hold the control key on the keyboard and tap semicolon and that quickly I have today's date in the cell now I can tap enter or in my case I want to tap tab to move over to L1 and here I'd like to put the time of day it's a similar keyboard shortcut Ctrl shift and then semicolon tap enter or tab now I've got my last update the date for it and the time for it so in this segment of Excel for intermediate users the complete course we've looked at several different strategies tools and techniques that will help you maintain a large worksheet in this next segment of Excel for intermediate users the complete course we're going to focus on working with multiple worksheets let's begin in the previous segment I showed you how to switch between sheets in your workbook simply by clicking here on the tab for the sheet that you want to go to in this segment there so I want to also show you some keyboard shortcuts that make it even easier to switch between sheets so here I am on the 2022 employee list sheet if I want to move to the 2023 employee list sheet all I have to do is hold the control key and tap the page down button then to go back Ctrl page up so if you have a page up page down button you can try that having said that let's take a look at our next skill which is how to edit the same cell in two different worksheets at a time or even more than two so let's say that as I'm working with this workbook maybe I decide that I want to change the wording in some of these headings I could certainly click here on E1 for example and change it from taxable income to Simply income tap enter but then I would have to do the same thing here on the 2023 employee list and any other sheet that has the same information of course right now they don't really have that information imagine that they do so I'm gonna undo do this with Ctrl Z and I'll show you a faster way to edit the same cell on multiple spreadsheets the trick is you go down here to the sheet name and tab click on it and then hold the shift key and select the sheet the furthest to the right that you want to include so maybe I want to include all of these sheets up to sheet 5. now I can release the shift button right now I have one two three four five sheets selected now I'll go up to E1 and change it from taxable income to just income tap enter on the keyboard now look if I go to the 2023 employee list it says income in E1 what about sheet 3 same thing sheet 4 and Sheet 5 but sheet 6 does not include that so by selecting more than one sheet down here you can just edit a cell or a range and those edits will be applied on all of the selected sheets now what if I want to do this but with sheets that are not together so maybe I I want to edit cell A1 on this sheet and Sheet 3 and Sheet 5. how would I do that instead of holding the shift key I would hold the control key click on sheet 3 click on sheet 5 so I have these three selected I can now release the control key and I'll click on A1 I'll change it to ID tap enter now if I go to sheet 3 it'll say ID sheet 5 will also say ID but the other sheets don't the next skill that you need in this segment of Excel for intermediate users the complete course is you need to know how to open and arrange multiple windows in an Excel workbook so let's say I really do want to set up a 2023 employee list that's pretty similar to what I have for 2022. it would be nice instead of having to click back and forth between the two it would be nice to be able to see them side by side to do this I'm going to go up to the view Tab and look in the window group there's a new window button and when I clicked on it I can tell that something happened but it's not entirely clear what it is that happened if you look up here at the top of my window you can see my title for this workbook and it's got a 2 on it that indicates that there are two windows open and if I go down here to the Windows task bar over Excel you can see that there are now two windows so at this point we can take this second window click on it and drag it to the right and if you're using a modern version of Microsoft Windows you can pull that to the extreme right and then let go and then select the other window the original and it will fill the left side of the screen so now I have window 1 and window 2 both open at the same time this window can have the 2022 list this window could have the 2023 list I could adjust the zoom levels if I want to and I could just click and drag to highlight all of the data in this spreadsheet hold Ctrl and tap C to copy and then here on my second sheet I can click on A1 one hold Ctrl and tap V to paste and it looks like it worked I can double click between some of these columns to make sure that the data can fit within the columns so that copy paste was much easier because I could see the two windows side by side now that I'm done with this second window I can just X out of it and that data that information that sheet they still exist they're just now in one window not two separate windows so you can see the two sheets now by clicking back and forth so okay so now that I've copy pasted the information from 2022 to 2023 I want you to see that there's another way I could have accomplished a similar thing let's say it's time to copy 2023 to 2024 to set up a new sheet called 2024 I could do that by right clicking on the sheet Tab and choosing move or copy and I would like to create it before sheet 3 and I want it to be a copy not just moving I don't want to move the selected sheet I want to copy it so I have to check this box click OK and now I have an exact duplicate of this sheet but here and you can see the title is similar but not exact so I can double click on that delete out the characters that I don't need and I can change it to be 2024. tap enter that quickly I've duplicated an existing sheet to create a new sheet now I want you to know that it's possible to do that not just from within the same workbook but from one workbook to another workbook so I could have opened another separate workbook and just selected it from this list and copied a sheet from this workbook into that other workbook the other option is maybe you don't have another existing workbook that you want to copy and paste that sheet to instead just create a new book from scratch right now so I want to copy it to a new book create a copy click OK and now I have a brand new workbook called book one it hasn't even been saved yet and it's got that one sheet that I copied from the other workbook so this is a really powerful feature that we have in Excel the ability to copy entire spreadsheets from one workbook to another workbook now I want to save this workbook I'm going to call it delete me because I don't really need it I'll go ahead and save and now that it's saved I'm going to open up another Tab and I'm just going to put in some data here it's not that meaningful of data but I just want there to be something here in this new workbook that's not in the original workbook and I'll save it once again and now I'm going to switch back to my first workbook the one that we've been working in for this entire video I'm going to go here to sheet3 it's pretty much blank and I'll just delete what's there by clicking and dragging to select it and then on the Home tab I'm going to to go to the editing group and I'll just click here clear all and what I want to do on this sheet 3 is I want to demonstrate that it's possible to link between two workbooks that second workbook that I created just a few minutes ago and this workbook that I've been working on for most of this video I want to link the two together and pull data from one of those workbooks into another of the workbooks and before I do this I'm going to actually jump back to the newer workbook and give me a minute and I'm going to add some data into some of these cells give me a minute to do so and then I'll resume the video now that I've added that data in I'm going to go back to my original workbook sheet3 maybe I want to show the Monday totals and I'll tab over I'll double click between A and B to create some space and here I want to add up all of the numbers from that first Monday on the other sheet so here on Cell B1 I'll type equals sum left parenthesis and then I'll just point my mouse pointer over the Excel symbol here on the taskbar that shows me my two workbooks that I'm working with so I'll go here to my delete me Excel workbook and I'll just click and drag to highlight all of the Monday numbers they happen to be sequential I'll tap enter on the keyboard and look this formula that I've created is linked from this workbook to the delete me workbook and you can see that when I click on B1 here in the formula bar let's look at what it says equals sum that's typical but then within parentheses it says the name of the workbook delete me.xlsx then it also says which sheet the information is coming from and which cell references it's a range in this case so I have successfully linked two workbooks together this original practice file that I've been using for most of the video and this brand new delete me workbook that I created a few minutes ago when you do link two workbooks together or even more than to it does change what happens when you open your file so I'm going to click save and I'll close out of this workbook and I'm also going to close the delete me workbook so now when I open up Excel it could be later today it could be next week I can open up that same file again this is my practice workbook that I've used for most of this video but notice because I linked this workbook to another workbook I'm getting a security warning automatic update of links has been disabled so because this workbook is connected to another if that other workbook changes if the data is changed or anything else that I'm linking it's not being automatically updated in this workbook and that's just for my own protection as I'm using Excel if I want to though I can enable the content so now the updates will come but you have to make that decision each time you open up this workbook do you want to update the information from the linked workbook or not okay so in this segment of Excel for intermediate users the complete course we've looked at several different ways that you can work with multiple worksheets and even multiple workbooks before we move on to the next segment I want to also point out that it is possible and actually pretty easy to create a summary worksheet that pulls in information data from other sheets in your workbook let's look at how to do that so here I have sheet4 and I'm going to double click on it and just rename it to be called summary sheet tap enter and I'll delete out the data that's already there by going up here I'll choose clear all and I'm going to click and drag and move that summary sheet so that it's the first sheet in my workbook you don't have to do it this way it could be the last sheet or wherever you want to put it but I can now design a summary sheet that pulls in information from the other sheets so I'm going to type in number of employees in 2022 colon I'll tap enter number of employees in 2023 tap enter total employee income 2022 and I'll just stop there for now I'm going to double click between A and B to make sure the data fits within the column and then here in cell B1 I'm going to type equals and I want to now pull in this data this information from this other worksheet so I'll just click on that worksheet the total number of employees and then I'm just going to click here on the total number of employees and then tap enter on the keyboard and that data now is being pulled from 2022. now in 22 23 I'm gonna cheat a little bit I'm going to select a few of the names of employees right click delete so the number will be different so back in my summary sheet the total number of employees in 2023 equals and then I'll just click on the 2023 tab I'll go down here to number of employees click tap enter on the keyboard and the information is pulled over into my summary sheet let's do one more total employee income 2022 equals and then I just click on the tab for the 2022 employee list spreadsheet here's the total income I'll click on it tap enter and it's pulled over so those are the basics for how to create a summary worksheet that draws attention to some of the key data within the workbook so in this section we've looked at several different ways to work with multiple worksheets and workbooks in Microsoft Excel in this next section of Excel for intermediate users the complete course we're going to learn more about working with data in Excel in this section of Excel for intermediate users the complete course we're going to learn about some new ways to work with your data including how to group your data and how to import data from other sources I'm going to switch over to the 2023 employee list let's say I'm going to be using this spreadsheet next week for the purpose of Simply figuring out the taxes to be paid by each person in the spreadsheet in that case it may not matter so much what each employee's hourly wages or the total income it's really all about the amount of taxes to be paid so I may want to just group this data let's look at how it's done first you need to make sure that you're on the data Tab and that you've selected the right data in your spreadsheet in this case I clicked and dragged to highlight all of columns C D and E you don't have to do that but let's take a look at it with that selected on the data tab data ribbon in the the outlying group you should see the option to group now in my case the group button didn't fit on the ribbon so that's why I had to click this down arrow in order to show it I click group and now column C D and E are all grouped I do see a horizontal line above column c d and e and this minus sign but other than that it doesn't look like much has changed but look what happens when I click the minus sign all three of those columns are collapsed if I click the plus sign they're revealed again in addition to the minus sign and the plus sign I can just click over here on one to collapse the group and two to reveal it again now I'm going to hold Ctrl and tap z a few times to undo this because I want you to know that I didn't have to select the entire columns I could have simply selected C1 through E1 and still gone to outline group and then selected columns I want to group The Columns click on okay and the result is really exactly the same I even could have selected other parts of the columns like let's say C12 through E12 outline group and selected columns click OK and again the results are the same I used Ctrl Z to undo all of that again and let's quickly take another look after selecting part of my data going to outline group again I'm going to choose columns in this case what if I want to then ungroup these columns with the same range selected I can go to outline ungroup again select the same option either rows or columns click OK and then those columns are no longer grouped I'm going to move to sheet 5 to show you that sometimes it makes more sense to group rows rather than columns so I'm just adding a little bit of data here and with that data added now I'm going to select B3 through B5 with that range selected again I'm going to go to the data tab outline group and select group this time though I'm going to group rows click OK and now instead of a horizontal line I get a vertical line here at the left I have a minus sign if I click it it becomes a plus sign so that I can collapse and expand or reveal those rows now watch what happens if I do the same thing with another range of data having selected it I'll go to outline group rows so now I have two minus signs two vertical lines and I can collapse or reveal one or both now that I have two though look up here in the upper left I still have a one and a 2 but look what happens when you click one both the groups are collapsed if I click two both are expanded or revealed so grouping and ungrouping is a key skill when you're using Microsoft Excel it's a great way to temporarily hide and then reveal data so that you can visually take in the spreadsheet the way you would like to next let's look at how to import data from another source into Microsoft Excel I'm going to jump to sheet number six and I'll select cell A1 and then here on the data tab let's look over here at the left at the get and transform data group there we have get data and when you click on that there's several different options of sources from which you can get data in many cases the most common option is here from file so let's say you have a file on your computer like another Excel workbook and you want to import that data into this workbook and specifically this worksheet you could simply click that button and the dialog box opens up enabling you to select the workbook that you want to import so I'm going to import that workbook that we used in a previous video Excel for beginners the complete course hopefully you watch that and this dialog box that's popped up shows me the sheets that make up the workbook that I've selected I have three numbered sheets and then one called taxes owed I can select one of them to get a preview of the data sheet 2 sheet 3 those seem to be empty and then taxes owed I'll select sheet 1 and then I'll come down here and click load and the spreadsheet that I selected has been imported into my current workbook the process is fairly similar if you need to import data from a different type of file like a CSV file CSV files are in some ways similar to Excel files but they're comma separated values files the pieces of data in CSV files are separated from each other with the use of commas so importing them this way is much more likely to be successful than just copy pasting the data in so those are two of the most common file types that you want to import into Microsoft Excel but you can see there are many other options including PDFs those of you that use Microsoft Access might also benefit from this option of getting data from a database you can see the top option there is to get data from a SQL server or SQL Server database but there's also an option for Microsoft access database and many others one other exciting option listed here is from online sources and again if you're familiar with these options and know that you have access to them these can be some great options for you the final skill that I need to show you in this section on more ways to work with data is how to insert a hyperlink to another document or file so I'm going to click here on the 2024 employee list and let's say I would like to link to a PDF or a Word document or whatever it might be even a PowerPoint presentation or a sound recording first I'm going to want to put some sort of text or data into the cell where I want the hyperlink to exist so maybe I'll just type something like learn more then I'll select that cell all and go to the insert tab in the links group I can just click link and I get a dialog box that pops up and there's lots of different link types that I could create if you want to learn more about these please watch my other videos on this topic but in this case I just want to link to an existing file or web page so I'll select that and it took me to the current folder but I could use these options here and here to navigate my computer and find let's say a spreadsheet that I want to link to or maybe a Word document let's try that I'm going to select a Microsoft Word document and a PowerPoint presentation I'll pick this one here click ok now if someone clicks on the first hyperlink it's going to open my other Excel workbook that I linked to this link should open the Microsoft Word document and this one should open the PowerPoint presentation keep in mind there are other options for things that you can link to including sounds videos and more so in this section of the video we've learned how to group data how to ungroup data how to import data from another source and how to create hyperlinks to other documents and files that we can access from within our Excel worksheets in the next segment we're going to take a look at some Advanced chart skills that go beyond what I showed in the Excel for beginners complete course in this section of Excel for intermediate users the complete course we're going to review how to create a chart in Excel and then you'll learn how to manipulate the chart to add some other detail to it and make some other adjustments to the chart in the Excel for beginners the complete course video I showed you how to create a simple chart and here I am on the 2023 employee list sheet and I'm just going to click on that summary chart that I created and I'll tap delete on the keyboard to remove that chart let's create a new one there are a few different ways to go about out creating charts in Excel let's look at a couple of those I'm just going to click and drag to highlight the data that I would like to chart in this case it's just the names of the employees and their hourly wages so I'm clicking and dragging to select all of the data all the way down the spreadsheet but I'm going to stop short of selecting these words at the bottom of this range of data I don't need the words totals average highest lowest I don't need any of that in my chart so I'm going to stop just short of those with that data selected I can go to the insert Tab and look in the charts group now in modern versions of excel you may have a recommended charts button if you click there Excel will recommend what it thinks are the most likely charts that you'll be looking for based on the data that you've selected so it's recommending a clustered column or a Pareto chart and you can just simply select the one that displays the data in the way you want to display it I think this Pareto chart is great I'm going going to click OK and my data here at the left in these two columns has been charted here at the right and I can click and drag to move that chart where I want it to be I can even resize it by using one of the handles in the corners of the chart and I can make it the size that I want and put it in the location that I want now let's try it again but this time when I go to recommended charts let's not take excel's advice Excel is limiting me just to two options if I click here on all charts you can see that there are bar charts that I can select from there's line charts pie charts column charts area charts we just have lots and lots of different chart options some of these are very exciting and interesting and I might not have ever tried these or known about them if I had only looked at the recommended charts now the more you use Excel and work with data the better you'll get at deciding which chart matches your purpose some of these charts look very interesting and informative of but really may not be for example a line chart may not make much sense when you're talking about individual employees and the different wages they make line charts tend to be a little better though when talking about change over time similarly this pie chart is not going to be a good way to display the data about the different hourly wages of the employees but I love that you can get a preview of most of these chart types and kind of test it out to see if it will serve your purposes in addition to using the recommended charts button and perhaps switching to all charts if you pretty well know what chart type you want you can just skip the recommended charts button and go straight to the exact chart that you want maybe this one for example so I'm going to resize that one and put it over to the side and I want to show you one other quick way that you can turn selected data into a chart with a range of data selected you can hold the ALT key and tap the F1 key on your keyboard and Excel should automatically create a chart out of the data that you have selected in my case it's exactly the same as the previous chart I made so I'm just going to click on it and delete it out I don't need two of those now once you have your chart or charts on the spreadsheet you can select the chart and you'll see we get two new tabs above the ribbon chart design and format let's take a look at chart design using the chart design ribbon I can actually change the chart type if I'm not happy with a Pareto chart I can click here and switch to something else like a pie chart in my case this would be a great mistake so I'm going to go up here and click the undo button to bring back my Pareto chart I like that better but there will be times when you change your mind on the chart and you can do that here on the chart design tab here with this button change chart type Excel also gives me some handy chart styles that I can quickly apply to my chart so just with the click of a button I can change the look and feel of my chart now if you don't think this is important think again one of the main purposes of a chart in Excel is to display the data that you have here in numbers and text but in a much more visual way so if you can make the data look more interesting or convey more data it could very well be extremely important that you make the right chart Styles choices I kind of like how this looks with the black background so I'm going to keep it that way if you want you can also adjust the colors in your chart so right now I have the colorful palette one selected but I could switch to colorful palette 2 and you can see there's some slight adjustments there or I could go to monochromatic there's just lots of different color adjustments you can make to your chart to the left of the change colors button I have a quick layout button and I can click there to make some adjustments to the Chart layout so here's what I have now but if I switch to this you can see that there are some adjustments some numbers are appearing in here across the bottom but there's so many of them they don't fit and they overlap so that may not be a good choice to switch to this layout but it lists here for you what is included in this layout here's layout three again I don't like that so I'm going to stick with the first layout next let's look at how to edit chart titles right now this chart is just called chart title I'm going to double click on it and highlight the text and I'll change this to Pareto chart now what if you don't even have a chart title associated with your chart you can fix that by going here on the chart design tab here at the left you can click add chart element and choose chart title you can see the options no chart title above chart or centered overlay I'm happy with the title the way it is also in that same button the add chart element button we can also change how the axes are handled do I want to switch the chart to be primary horizontal or primary vertical you can see the differences there there are also other access options here we can change the axis titles do I want the access title primary horizontal or primary vertical again you can see the differences in this case I'm going to go with primary vertical because people may not know what these dollar amounts actually mean but now that I have this access title in place I can triple click on it to highlight all of the text and then I can type hourly wage it feels kind of weird to type vertically like this but when you're done just click away and now I have titled the vertical axis and I'm sure you can see how this improves the chart it makes it much more understandable and it just conveys the information better we have other chart elements that I hope that you'll explore in the case of my chart a lot of these options are just going to clutter it up and make it harder to read and understand so I'm not going to do this but if you want to add anything else to your chart this add chart element button is a great option there is one more chart element that I want to draw your attention to though and that's the legend chart element in some cases this will add just the information that you need so for example here it added a legend showing that the blue represents hourly wage so that can be helpful and if there are multiple colors in your chart that Legend might be crucial to help people understand what they're looking at let's look now at the other tab that appeared once I created my chart and selected it it's the format tab if I click there I get even more visual elements that I can use in my chart I can put in a shape fill I'm not so happy with that change so I'm going to hold Ctrl and tap Z but there are times when you want to make these kind of changes yourself rather than just using a predetermined chart design so you can change the fill and the shape outline the shape effects for example I can add a glow around the edge of the chart or different elements of the chart so definitely explore these visual elements and options on the format tab I can also change the style of the words in my chart for example this text here that says Pareto chart if I select that text I can go up here in the word art styles group and I can change how that looks in the chart just by clicking on one of these options I'm going to undo that and try a different one let's say I want it to have a reflection I can choose it and look that text now has a reflection it's also possible to use this format Tab and ribbon to insert some shapes and also text boxes so I'm going to click on text box and I'll click and drag to draw out a text box now because of the choices I made about the background color of the chart it's hard for you to see that text box at first but when I release the mouse button it appears and I can click in that text box and type I'll just type in something like employee wages and then I'll click away from the text box and I can always click back on the text box and stretch it out using the hand handles and I can go to the edge of the text box to move it to where I want it to be clicking back on the chart and going back into the format tab there's also shapes that I can use in my chart so I can click here on this shape for example this arrow and then I can click and drag to draw the arrow to point out key information if I then click on the Arrow I can go to the shape format button and I could go here to shape outline and change the weight of the shape the arrow in this case I'm going to choose four and a half Point weight and the arrow is much more visible now I could also change its color to help draw attention to it a little better so with those two options I think the arrow is going to work a lot better with this chart now there's one other skill specific to charts that I want you to be able to do and that is the numbers that you have in your chart can be formatted so I could right click on the wage amounts here at the left and I could go down here to format access I get a panel that opens up at the right and here I can put in what is the minimum number and what is the maximum number so right now the maximum is 35 even though nobody is making 35 dollars an hour I could lower that to 34 and it adjusts the chart or I could raise it maybe to 60. tap enter and you can see how the chart is adjusted so it's all about displaying data the way you want it to look I'm going to put this back to 35 but then maybe I can change the minimum to be 10. I tap enter and the chart is adjusted I could do the same thing with the percentages here at the right just by right clicking and formatting the axis okay now that you know all of those chart skills and techniques let's look at a few bonus elements that you can add not specifically to your chart but really you could add these anywhere in the spreadsheet including on a chart for example you could click insert and illustrations to bring in an image I'll click here on pictures and I could just insert an image that's on my computer or I could pull from stock images or from online pictures let's try online pictures I'm just going to do a search for a dollar sign here's one I can then click on it and insert and now I've got a graphic image that I can resize I can move it to where I want it to be including possibly on my chart maybe there just like I inserted this text box here on the chart I can also insert a text box even without having a chart so I could just click here expand the text box if I want to click in it and type and then I could click on the very edge of the text box to move it to where I want it to be this text in the text box is just floating on top of the data it's not going to affect the cells it doesn't exist in a row or a column it's just floating on top in addition to inserting text boxes you can also insert insert headers and Footers signature lines and word art let's put in some word art I'll click to select that particular style and I'll change the text to something like that having selected the text I can change the font size to whatever I want it to be and then similar to my basic text box I can click on the very edge of this text box and drag it exactly where I want it to be in my spreadsheet there's one other similar example that I want you to know about and that is if you click on insert in addition to word art we also have an illustration that's called smartart if you click on that it brings up several collections of smart Graphics that you can insert onto your spreadsheets here you'll find things like org charts or organization charts that show Maybe the owner or CEO and then how everyone else in the organization connects to each other supervisors managers co-workers Etc we have a matrix option we have a pyramid process smart art lists and so much more I'm going to go back to the hierarchy options to insert an org chart I'll click OK and it appears here but I can just type using these bulleted lists there's CEO department manager employee one and then I can put employee two and three and of course normally you would probably put names instead of just titles like this if you need more than this you can just tap enter on the keyboard and you get another smartart element I can change the indentation to adjust the smartart the way I want to or need to and you can also click and drag to do that as well if you want to learn more about Excel smartart watch my other videos on that subject but at this point I'm going to close out of that wizard that was there and I'm just going to click and drag to move my org chart off to the side because at this point we've looked at all of the key Advanced chart skills that you need in this Excel for intermediate users the complete course in the next section of the video you're going to learn more about how to work with data lists including how to use data forms in this section of Excel for intermediate users the complete course we are going to learn more skills related to working with data lists in Excel in the Excel for beginners complete course I showed how to create a list of data by clicking on a cell and simply typing you can then tap the Tab Key to move to the right and type again tab over and continue typing you can also tap enter on the keyboard to move down and if Excel can tell that you're creating a list of data when you tap enter on the keyboard instead of just moving down one it will often move to the left so that you can continue entering records in this data list and I can just keep tabbing over typing typing and tapping enter to create the next record in this Excel for intermediate users complete course I want to help you take this process to the next level and we're going to do it by using a data form so I'm going to switch to the 2022 employee list by clicking down here on its Tab and now I can see our employee list of 70 employees now it would be fine of course to create this list the way I've already shown you just by clicking on a cell typing tabbing tapping enter and typing in the information manually that you want to include in your spreadsheet now let's look at an alternative let's move up here to the quick access toolbar and I'm just going to click here on this symbol to customize the quick access toolbar which is this section here it's a toolbar that helps you have quick access to the tools and options that you use most often or for tools and options that you don't want to have to dig for by going to tabs and ribbons and groups and all of that so I'm going to click on this button here to customize the quick access toolbar when I click that at there's all these options that I can add to the quick access toolbar or remove but if you go down here almost to the very bottom where it says more commands you can click and you get this dialog box that pops up that will help you to customize it even more by default Excel took me to a list of popular commands that I could add to my quick access toolbar here at the right I can see what I already have on the quick access toolbar but I'm going to switch from popular commands to all commands and I'm going to browse down until I see a command called form I'm going to select that and then click add so now I should have a new button on my quick access toolbar called forms I'll click OK and it's added here at the top now all I have to do is make sure I've clicked somewhere inside my data list or range of data and then go up here and click on the forms button now look what I get when I do that I get this nice interface that is clean and pretty simple and is going to enable me to do data entry in a safer and in many cases faster way so imagine instead of having 70 employees what if I had 5 000 employees or 500 employees would I really want to be typing and tabbing over and hitting enter the way I've shown to create such a huge list I may not want to data forms like this could help ensure that you're more careful and that the data that you're entering is more safely added into the data list so I can simply click this new button to add a new employee now before I go any further I'm going to close the data form because if I'm going to use it to add additional data I need to make sure that there's some room in the spreadsheet for the data to be added so you can see after record number 70 I have my totals average highest lowest number of employees that's all at the bottom of the spreadsheet I'm going to click and drag to highlight that range of data and I'm just going to put my mouse at the very edge of that selected range I'll click and hold the click and I'm just going to drag this over to the side here so that it's out of the way and you'll notice that my data has not really been affected my formulas are still looking in the right places to produce the results that I asked them to produce okay so now with some space below my last record once again I'm going to click in my data list anywhere and then click on my forms button and now let's add a new employee I'm going to click on new we're going to hire employee number 71 and then I'll tap tab on the keyboard and type in the name of this employee which is Amanda I'll tap tab again hourly wage 25 an hour tap tab hours worked so far zero and now I can tap enter on the keyboard and Amanda is added to my list of employees notice what my form does it clears out and now I can quickly add the next employee number 72 tab this is Spencer with an hourly wage of 28. I tap tab hours worked maybe he's already gotten a few hours so I'll put in three I tap enter on the keyboard now I'm going to close this data form for a minute just by clicking close and you can see the different results for Amanda and Spencer because Amanda hasn't worked any hours these formulas aren't able to calculate and so I'm just getting a dash or in some cases you may get error messages or zeros here but what I've shown you is how you can add records to your data lists by using a data form this is a great feature in Excel now this same data form can be used to help you find records that have already been put into Excel and then it can help you edit them so for example let's say I know I need to edit the information about employee 25. there are a couple of ways I could quickly jump to employee 25 one would be to use the scroll bar I can just click and drag and notice here it's telling me which record I'm on so here's 25 and we can see n and her hourly wage and hours worked another way to quickly jump to a record is to click on criteria maybe I need to see the record of employee 39 I tap enter on the keyboard and I'm shown the record of employee 39. now this works not just for ID I could type in name let's try Tanner but before I enter that data I probably will need to clear out the ID number so let's take out the 39 and then hear a name I'm going to type Tanner and tap enter and the form has helped me quickly find employee 65 who's named Tanner here's his hourly wage and hours worked so by using this scroll bar or this criteria button we can quickly find a record if there's more than one result that matches the search that we've just done in criteria we can click find next in my case there is no other Tanner listed I could also use find previous once we've found the record that we're looking for we can make adjustments Tanner deserves arrays let's move his raise up to 28 dollars an hour and you can see Tanner here in my list when I tap enter on the keyboard his hourly wage is updated to 28. so in many cases when you're working with a big spreadsheet of data with a giant data list using this forms option can be a great way to add new records to your list as well as to find records that you need to work with and then also to edit them I'm going to close the form the next skill that I want to make sure that you know that relates to working with data lists is how to sort the data right now my data is sorted by employee ID number but I could change change that I'm just going to click somewhere in my data and then there are two basic places you can go to sort your data you could go to the Home tab and look over here in the editing group you should have a sort option sort A to Z sort Z to A for me that's all condensed down to this symbol here and my arrow so I could click and then click sort A to Z and notice that my data table has been sorted based on the cell that I had selected so I had selected one of the names in my data list and so now the entire data list is sorted based on name Adrian's first and notice that the rest of the data followed along with Adrian she used to be the 13th record now she's the first record and her employee ID has followed her her wage taxes owed all of that has followed along with her name I'm going to hold Ctrl and tap Z to undo that because I want you to see the other place you can find the sort options here above the ribbon I can click on the data Tab and this will also give me sort A to Z sort Z to A and a sort button so let's try instead of sorting by employee name what about sorting by hours worked that might tell me something important so I'm going to click anywhere in the hours worked column and then I could Click A to Z that would sort from the lowest number to the highest number or I could sort Z to A now I get the highest number to the lowest number so I can see that Luis and Alvaro have worked the most out of all of these employees and again when the data in this column is sorted the data in the adjacent columns follow along they come along for the ride with the data that's in column D so you're not messing up your data so we've looked at both sort A to Z and sort Z to A now what about this generic sort button if I click on that I get a dialog box or a wizard that pops up that will help me to create a very specific and in some cases complicate did sort because I had selected a cell and the hours worked column that's currently listed as my first sort level but I could change that I would like to sort the employees first by their name so this name column is going to be my first priority for sorting and then what is the sort going to be based on is it going to be based on the cell values in other words what's actually in the cells or is it going to be based on the cell colors or the font colors or a conditional formatting icon so these are all interesting options but the option that you'll use most often is this one cell values next do I want this to be a z to a sort or an A to Z sort it's also possible to sort based on a custom list for example months of the year days of the week you can sort in that way but for now I'm going to cancel and I'm going to switch this to A to Z I want to sort first alphabetically based on name next I can add a second level to my sort because I may have more than one person with the same first name so first I'm going to sort by name and then second let's say by a number of hours worked again cell values and maybe I want to switch this to largest to smallest I click OK and now my data is all reoriented reorganized based on the first name and if there are any ties then the employee that worked the most will be listed above the other employee or employees so that's how you can sort records based on a single level or multiple levels of sorting okay finally we're going to look at another way to use and organize your data in an Excel spreadsheet and that is filter you'll find filter on the Home tab here in the editing group in the same area where we have sort here it is filter but you can also get to filter here on the data tab in the sort and filter group and that's where I want to use it in order to create my filter in Excel I'm going to first click and drag to select the column headings that matter to me so in this spreadsheet it's ID through taxes to be paid with all of those headings selected I'm going to click on filter and it looks like almost nothing happened but if you look closely there's some little buttons with arrows on them that have been added here to my column headings now I can click on one of those buttons let's say hourly wages and I get a series of check boxes that I can use to filter out data that I don't need to see right now so by default everything is selected everything is visible but if I uncheck this select all button now I can select just the data that I'm interested in so what if I only want to see employees that have an hourly wage of either 12 an hour or 13 dollars an hour I can check just those two options click OK and now my data list that used to have 72 records only appears to have 12 or 13. now the first time you do this it may feel like you've ruined your data you've lost some important information but that's actually not the case if you look here at the left you'll see that some row numbers are skipped and that's not really possible in Excel to completely delete row numbers instead they're just temporarily filtered out or hidden so that I can focus in on exactly the data I'm interested in in my case employees that make 12 or 13 dollars an hour now notice my filter button here at the top and I'm going to zoom in a little bit so you can see this better but instead of having an arrow on the button it now has a filter symbol and so that tells me that a filter has been applied based on the information in this column now I could filter again if I wanted to I could place a filter let's say on hours worked so in addition to hourly wage of 12 or 13 maybe I only want to see employees that worked these amounts of hours I could click OK and now I have two filters applied so filters are a great way to take a giant spreadsheet and really focus in on exactly the data that you're looking for by applying one or more filters now if you want to remove those filters or clear them you can go to that same sort and filter group and click clear and now those filters are removed but we still have the filter buttons here so that we can quickly filter again if you want to get rid of those buttons completely you can click the filter button and they're removed but I'm going to put them back so that I can show you how to create a custom filter here in hourly wage I'm going to click on the filter button to show you that in addition to filtering the data based on these exact numbers I can also click on number filters and filter the data so that only employees with hourly wages greater than let's say 13 are shown and I could put another criteria here but I'll just click OK and now you can see that employees that make 13 or less are not included in this list so that's an option I'm going to clear that out we have several other built-in number filters that we can use greater than or equal to less than between two numbers or equals it has to equal exactly a certain number or word you could also filter so it shows only the top 10 the above average below average and then at the bottom of that list of options we have a custom filter this is how you can customize a filter notice again that I'm filtering based on hourly wage and maybe I want to filter so that it is greater than or equal to 13. so 13 will be included this time and it's less than or equal to 16. I click OK and my data is filtered based on those selections to edit that I could go back into the filter activate the custom filter again and you can see my work from before this time I'm going to switch it to or so I want to filter so that only items greater than or e equal to 13 are included or results that equal exactly 11. I click OK and that filter is applied so in this section of Excel for intermediate users the complete course we've looked at how to use data forms to add records to a list to find records and edit them in a list we've also looked at how to sort and filter your data in Microsoft Excel in the next section of Excel for intermediate users the complete course you're going to learn how to document and audit a worksheet in this section of Excel for intermediate users the complete course we will learn how to document and audit a worksheet including how to add comments notes how to use watch windows and how to use the auditing feature in Excel and I'm going to start by switching over to the 2024 employee list and let's say I'm preparing this list for the coming year and as I'm getting the spreadsheet ready maybe I want to make sure I remember certain things and also other people that may be using the spreadsheet with me will remember key points or data that needs to be updated or looked at or changed in some way well we can use comments or notes to help us with that so for example I'm going to go down here to the record for Mari and I'll just click there and then I'll go up here to the insert Tab and choose comment I get a little pop-up here and I can use this to start a conversation and that really is the key difference between a comment and a note a comment is meant to be part of a conversation with other people so I'll add a little message here to start a conversation so something like that and then I can click this button to post that comment or notice this tip press Ctrl and tap enter as another way to post that comment a little faster now that I've posted that comment if I have a co-worker a colleague that's using the same spreadsheet with me that colleague can certainly go in to this text box to reply and they can post that reply now if I need to or want to I could go in and delete a comment just by clicking on these three dots and choosing delete comment if I feel like the matter is resolved I could just go here to the original comment click the three dots and choose resolve thread once I've done that the comment still exists and it can still be seen but it's marked as resolved so that's the difference between resolving a comment or a series of comments which would be kind of like a conversation or a discussion and deleting it if I resolve it it's still viewable if I delete it it's gone in addition to comments I could also use simple notes so for example I want to add a note here reminding myself or others to check on this information about Janae so having selected that cell I'll just right click and choose new note now notice there's also new comment there so that's another alternate way to insert a comment but this time I don't want to comment I want a note so I get a pop-up that looks similar to a Post-It note and I'll just type in a message to myself or anyone else that sees this spreadsheet at this point I can click away from the note and it's hidden but I can see that there is a note in this cell which is indicated by this little red triangle in the corner of the cell which is similar to this purple shape that indicates there's a comment in a cell if I decide I want to remove the note I can just right click on the cell and choose delete note or I could edit the note so comments and notes can be very helpful in Excel they don't affect the data they don't change the data but it's a way to remind yourself or others about key elements of your data or your spreadsheet now there are times when you may want to print the notes or comments that you've created how would you do that to print them you would go to the page layout tab go to the page setup group and click this launcher button or launch button when you click that you get some options you would just go to sheet and look for this setting comments and notes right now it says none if you choose as displayed on sheet then only notes would be displayed or you can choose at end of sheet and then both comments and notes should be displayed in the print let's try it out with print preview you can see here's my data but at the very bottom of the printout here's my note it says it's for cell D9 and here's my threaded comment associated with cell B17 I'm going to click this back arrow to get back into my spreadsheet okay the next skill you need to learn is how to set up a watch window to help you find or avoid errors in your worksheet let's say as I'm building this 2024 employee list and I'm making some adjustments make sure it's accurate maybe I'm relying on at least some of the data from 2023 to help me do that and so I might be jumping back and forth between these two cells as I compare the data and make necessary adjustments well there's something I can do to help me with that so that I don't have to just eyeball the data here and then either change it or keep it here on this new spreadsheet what I could do is set up a watch window I'm going to start by going to the formulas Tab and looking in the formula auditing group and there we have a button called watch window when I click it I get this pop-up titled watch window and I can use this to watch a part of my spreadsheet or my workbook so I'm going to click to add a watch it says select the cells that you would like to watch the value of now Excel has made a guess as to what I would want to watch and I could just click add to go with that but in this case I'm want to click this button and then to go to the sheet and the range of data that I want to watch and in this case I'm just going to watch the first 15 results here on the 2023 employee list I'll release the mouse button and you can see that that range of data is selected and described here in this ad watch dialog box now I can click this Arrow again click add and that range of data that's here on the 2023 employee list has been added to my watch window it doesn't matter what tab or spreadsheet I'm on I'll always be able to look at this watch window so that I can kind of peer through this window look into it and see in this case a different spreadsheet or it could just be a different part of the same spreadsheet when you're working with a lot of data it's very common that you'll be working on one part of a spreadsheet or workbook and the work that you do is affecting another part of the workbook or spreadsheet and so how nice is this to be able to look through a window and see a distant part of the workbook or worksheet now this watch window can be resized I can use the corners to make it bigger I can double click between any two columns to make the column wider so that you can see the data better and let's look at the data that's displayed first I have the name of the workbook then I have the specific sheet in that book that's being displayed in the window so the 2023 employee list next I can see each cell so cell A1 and what's in cell A1 the letters ID what's in cell A2 well the number one and so on as I browse down my watch window notice that I can see even the formulas that are being used in those cells so in E5 for example we have a number of 2268.75 but it's actually a formula that's producing that number so one of the benefits of using a watch window is that you can do more than just look at far-flung sections of your spreadsheet or workbook but you can also see exactly what is in the cell yes numbers in many cases but also the formulas so it's a good way to check your work and to kind of audit it and make sure that you're not making mistakes when you're done using the watch window you can just click this button to hide it and then if you need it again later on you can just click the watch window button again in addition to watch Windows other tools we have to help us with formula auditing can be found here in the formula auditing group that's on the formulas tab we have the option to trace precedence as soon as I click that Excel looked at the cell that I had selected and you can see it's showing me how this number was produced it's tracing its origin cell C5 multiplied by cell D5 so now I know the Precedence that led to this result in 5. now what if instead of this result what if the Precedence showed the line going down here or down and then up that could certainly be an indication of an error in my data in addition to tracing precedence I can also Trace dependents what other cells are dependent on the data in this cell E5 we can see the taxes to be paid is dependent partially on the income number and if we browse all the way down the spreadsheet here we can see that totals average highest lowest and number of employees are also dependents of the number here they're all affected by the number that's produced here because of the formula that's in that cell and its precedence if I want to I can remove the arrows that have been produced but if I click Trace precedence again they come back Trace descendants again they come back other formula auditing tools I have include show formulas when I click there you can see what happens instead of showing the results of formulas I see the formulas themselves so these cells now show me exactly what's in the formula bar for each cell so I click here the formula bar contents are here and they're also here it's just that they're color coded differently here in the cell I'm going to toggle show formulas off and I think I'll remove the arrows next let's look at error checking when I click error checking Excel is going to do a quick check to make sure there are no error codes in the data if there are it will show them to me so that I can address them in addition to that we have a drop down arrow that we can use to trace the error itself so that we can see why that error came to be and then finally if you select a cell that has a formula in it like this one does you can then click evaluate formula it brings up the formula and you can click to evaluate the formula as it says here by evaluating the formula I'm able to see the result of the underlined expression so this formula produces this result if I click evaluate again it evaluates the next part of the formula and so on if I click restart I can also step into the formula more or I can step further out of the formula I'm going to click close but I hope you can see that by adding comments and notes by using watch windows and by using the other formula auditing options we can help prevent or identify potential problems mistakes errors in our spreadsheets and workbooks in this final section of Excel for intermediate users the complete course we're going to add to your printing options toolbox if you watched Excel for beginners the complete course you learned about some printing options and features but let's add to those for this section I'm going to switch over here to the 2022 employee list and I'm going to click here on the file tab to bring up print and at this point I could simply click the print button to print my spreadsheet but there are times when you do this and you look at the preview here at the right that you might say to yourself I don't want to print it this way I don't want it to look the way it looks here so what can you do about that one thing you could do is go into your workbook and go to the page layout tab here in the page setup group and you can specify the margins that you want for your pages when you print so this is the normal margins but you could switch to narrow margins so that more data can fit onto one page or you can go to wide margins that would result in less data being able to fit on the page you can also set up custom margins here I'm going to cancel out of that you can also change the orientation of the printed page you can see that I have mine set to be landscape in many cases in Excel that's a good idea if you have a lot of columns it may be difficult or impossible to fit those with a portrait orientation without making the data tiny so you may need to switch to landscape but if you want you can switch back to Portrait and now when I go to file print here in the preview you can see the pages now in portrait view I'm going to click back we can also click on size to change the size of the paper that we're going to print onto here's the default but you could certainly switch to a different printer size you could also specify the print area this is a great feature to use this feature I would click and drag to highlight the area that I want to print and notice that some of my data is still hidden because it's filtered I have some filters applied here and so there are some rows that are getting skipped and that's okay but I just want you to be aware of it but now that I've clicked and dragged to select the area that I would like to print I can go up here to the page layout tab in the page setup group and click on print area and set print area so now instead of excel worrying about all of the data on the sheet when I click on file print all Excel is doing is showing me a print preview of the area that I selected for printing I'll click back and I'm going to clear the print area we also have some page break options you can insert page breaks remove page breaks reset all page breaks if you'd like in addition to these tools that we have in page layout page setup we also have a little button here in the lower right corner called page break preview and when you click on that Excel shows you how the printing is going to go if you keep all the settings as they are so right now all of my data could fit onto one page so that's probably a good thing but if I wanted to I could click and drag this blue line that's here at the right drag it to the left and I could say I don't really need the chart maybe and I could stop there so now this is page one now you can also go the other direction I could make page one extend all the way over here to column X and so page break preview is a good way to solve printing problems if you have something that's crucial that it be included on the printed page activate page break preview click the blue line and drag it so that everything that needs to be printed on a particular page is included I'm going to click back here on the normal view to get out of page break preview and I'll browse over here to the left and there's one more print setting or option that I want you to be aware of and that is there are times when you want to print not the results of a formula but the actual formulas in the spreadsheet to do that hold the Ctrl key on the keyboard and tap the grave accent mark key that's in the upper left corner of your keyboard now you can release the control key and if you browse over you can see the formulas are now what is displayed in the cells not the results of the formulas at this point I could go to file print and you'll see that the formulas are going to be printed before I do that though I'm going to go back to page break preview and I'm going to adjust the page break to something more reasonable how about right here now I'll switch back to normal View and go to file and print and if you look closely you should now be able to see that formulas are being displayed and not the results of formulas so with those additional printing options you now should know and understand the basic printing that we looked at in Excel for beginners the complete course and also these new intermediate level printing options so congratulations you've now come to the end of this Excel for intermediate users the complete course with the skills that you've learned in this intermediate level course as well as the Excel for beginners course you should be able to move forward very successfully using Microsoft Excel going forward I hope you'll want to continually learn more about Microsoft Excel on my YouTube channel you'll find many more beginning level tutorials that are short videos that show skills that are not part of this complete course series you'll also find more intermediate video tutorials and also some Advanced Excel tutorials in the future I'd like to eventually create an Excel for advanced users complete course so watch for that hopefully it'll eventually appear and please do check out my complete courses for Microsoft Word and PowerPoint and if you happen to also use Google Drive including Google Docs sheets and slides I have a complete course for those tools as well but for now thank you so much for watching this video I hope you found it to be helpful if you did please like follow And subscribe and when you do click the bell and you'll be notified when I post another video if you'd like to support my channel you can do that by clicking the thanks button below the video or you could support me through my patreon account or by buying Channel merch and you'll see information about those options in the description below the video

Channel: Technology for Teachers and Students

Views: 184,929

Keywords: excel intermediate, excel tips and tricks, excel formulas, excel tutorial, excel 365, excel 2021, microsoft excel, office 365, excel zoom options, excel zoom, split excel, excel split, excel split panes, split panes, freeze panes, excel freeze panes, naming cells, linking excel workbooks, grouping excel data, excel filtering, excel sorting, exel tutorial, excel printing, excel for intermediate, middle excel, excel mid level, excel middle level

Id: BkvVvbqe2q4

Length: 89min 55sec (5395 seconds)

Published: Mon Sep 18 2023