Create a Bill in Excel for NPOs Effortlessly

Bill in excel for NPOs

okay so welcome to the excel for beginners class we're so glad you're here um i'm gonna go ahead and start sharing my screen so we can all be looking at the same excel document that i hope you also have again if this is something that you don't have right at the moment just go ahead and chat our team and we'll we can figure out a way to get that over to you but we have found that in the course of doing this training online it is a little easier to sort of do very small chunks and let you practice excel or google sheets or whatever program your sort of spreadsheet program you're using is really best learned by doing so if you just watch excel you can learn a lot but it's when you actually do some of these things yourself that you will truly understand how it works and how you might use it in your day to day so on that note i know we're all busy people you've dedicated the time for training if you are able to unplug from your other tasks and just try to follow along and practice i think that's where you'll get the most out of this session that said also understand that you're have a lot on your plate and if you need to kind of plug in and out of the training like i said we will send this training video out to you after as well so without further ado let's get started so i'm going to start by giving you a brief excel tour and we're just going to overview basically how excel is set up so we're going to start by uh go ahead and looking at all the components of excel so hopefully this looks like your screen we're going to start by looking at sort of what what this whole section is made out of so what we're looking at is the worksheet this is where all of the data lives in excel and on the left hand side you can see these numbers here these are the rows along the top are the columns and these are lettered when you click on any of the data that you see in this worksheet you are clicking into the cells and the way to identify them is by the column d and the row seven so you can click on others and you can actually see over in this little box to the left you can see it show you which cell you're clicking on that's a helpful thing to know because when you start writing formulas you need to refer to certain cells so this is just okay there we go great all right so the other thing i wanted to call your attention to when we're clicking on the cells is you can also see the data that you're clicking on up in this section right here this is called the formula bar and this is just a place where you can see your data as well and where you can also do data entry so you can see i can click here and do data entry and it's filling in the same cell now this is our worksheet tab so this whole excel book it's called a workbook and that contains different sheets this demographic sheet you can see my mouse on the bottom left is just one worksheet now if you can see on the right hand side there's this little plus here and when i hover over it a pop-up menu says new sheet so i'm going to click on that and what happens is i get a whole blank worksheet so now i have two worksheets in my workbook for just practicing a little bit we're going to go ahead and right click on this title that says sheet1 because that doesn't tell me a whole lot we're going to move our mouse up to rename and we're going to name this home visit notes we'll come back to this in just a minute but i wanted to show you how to quickly rename a worksheet and i'll give you just a moment to do that so again if you missed that we clicked the little plus button down here to add a new sheet we right clicked on the sheet one that popped up and we on the menu that appeared we went up to rename and i'm just calling this home visit notes okay and again i'm going to try and show you something give you a moment to do it if i'm going too fast let me know it's hard to tell over zoom how people are keeping up or not and you can feel feel free to chat any of us and we can adjust the speed okay now we've renamed our worksheet and we're not going to use it anymore right now so we'll move the mouse back over to our demographics tab and click on that and that will pull up the other worksheet that had all of the data on it now i want to call your attention to the scroll bars you may have seen these already but on the bottom right hand side is a scroll bar that lets you move across your worksheet to see all of the data and on the right hand side is another scroll bar that lets you move up and down so let's scroll all the way down and see how many entries of data we have okay when we get to the bottom we can see that row 88 is where my last data entry is so if i move down i cannot see any more data under there but these scroll bars are really important for navigating around your work fees now let's take a little tour along the top there's a lot of information up here and it's helpful to know just generally what all of these things are and why you would need to use them okay so there's different categories i'll start on the far left with file this pops up a whole new screen and this basically shows you how to save how to print maybe you'll want to start with a new workbook but this is really basic kind of save print etc we can click this arrow right here to go back now you can see wherever i am it's highlighted so this green line is under the home ribbon these are called ribbons and that's where we'll start if you're just joining us we are just opening up our excel workbook so feel free to open yours and follow along so on our home ribbon we've got a number of different categories we've got our formatting copying pasting inserting and deleting sections so you've got some general paste options this group deals with your font and how to modify that you can align any of your data in any of the cells this involves how you display different numbers some styles and formatting inserting and then some more editing so on the insert ribbon when you click that you can see my green icon move over this deals with inserting objects like charts and shapes this is what i use this ribbon a lot for eventually in the advanced class we'll get into pivot tables that's where you can find these you can insert pictures other kinds of graphs and ways to manipulate your data or comments which can be kind of helpful too now we'll move over to the page layout ribbon this details how your spreadsheet looks both on screen and in print so we've got theme colors we've got page margins and other sorts of scaling options as well um over to the formulas ribbon this is where you kind of define and reference should you need help with formulas i actually don't use this ribbon very much i use the pop-ups and i'll show you that briefly a little bit later but it's helpful to know that this is here this data ribbon helps you manage data in your spreadsheet and connect to other data you'll also see some tools that we'll use in a little bit like sort and filter this is other ways to connect to other sources of data as well then we've got our review tab which helps you protect your data it helps you add comments spell check that can be really helpful over here track changes if you're sharing and editing worksheets or workbooks with a number of different people and then we've got our view we will use this today as well we've got our different views we've got zoom features and some freeze panes and these these are all really useful as well the final ribbon is our help ribbon which i don't use a lot but it is there um you can find some help via google or excuse me via microsoft support by clicking on this little question mark on the far left hand side and it will pop up a little menu over here and if you know what you're looking for this can be helpful if you want to look for information on formulas you can just click on a very commonly asked question and see some really common answers but honestly many of my colleagues will tell you that we find it just as easy to just google something if we're having a specific problem it's often just as easy to google it so lots of ways to get help in excel okay one other thing to do is to practice removing the ribbon or hiding it and then showing it again so sometimes if you're on a small screen like i'm on my laptop screen and it's not very large if you want to be working with data you can navigate your mouse over to this little arrow and you can see when i hover over it it says collapse the ribbon so let's click on that and if you've done that on your workbook you can now see that you have a lot more space to view your data which can be really helpful of course when you want to get them back it's pretty easy just to click on any one of these ribbons to pull that information back now if you know you want to be using this ribbon a lot for different reasons you want to use a lot of different functions you may want to move your mouse over here and pin it so it doesn't collapse and i'm going to suggest you keep it pinned for this session since we'll be using the top ribbons a fair bit so i'll give you just a minute to do that great okay let's go back to our view menu click on the view at the top or view ribbon great and now we want to practice zooming in and zooming out so i think this can be very helpful for a number of reasons sometimes i don't have great vision so sometimes i need to zoom in to see my data a little better and there's two ways to do that one really simple way without going to that menu i just showed you is to move your cursor down to the bottom and you can see the zoom bar down here on the right hand side try clicking the plus sign and you can start seeing that you're zooming in closer and closer to your data it's showing me here on the right hand side that i'm zoomed in to 140 which is nice because i can see it a little better now maybe that's a little too big so you can either use the minus key of course to zoom back or you can go up to that view ribbon to the zoom section or the zoom group of this view ribbon and you can choose to zoom here so you can click here to do you know a selection maybe you want it to be 75 so that would be smaller than we had before i can see it's quite a bit smaller that might be too small for me so actually one of the quickest ways to kind of go back to like 100 view is this 100 percent icon you can just click on that and you're back to where we were so take a moment and select the zoom level that's most comfortable for you makes it makes it easiest for you to see perfect this is a good time to note that usually there's a couple different ways to do things in a cell i just showed you two ways to zoom that's true for a lot of different functions and there's no one that's better than the other it's just whichever one works better for you and whichever one you'll remember great now we're going to explore how we select different data in excel so the easiest way is just to click on the cell that you want you can continue to click and explore different cells or you can use the arrow keys on your keyboard to move back and forth and up and down sometimes using the arrow keys is a little quicker if you're just working and typing and doing some data entries so sometimes i like using that now let's say we want to select a range of cells there's a couple different ways to do that so with your mouse select one cell and hold down with the left mouse button you can now drag down to select more than one cell and to the right as well to select as many as you want and then you let go and that's your selection right there you can also start by clicking on a cell holding down the shift key on your keyboard so if you find the shift either side of your keyboard and then use the arrow keys you can also select different cells this can be a little more precise than dragging especially if you're trying to select a lot of information but i think this is just a personal preference thing then when you let go of the shift you've got your selection of this sort of block of cells one other neat trick is over here on the left you see this little arrow that's sort of between the first row and the a column and when you click on that you have selected the whole whole worksheet so this is also really helpful if you're trying to you know make a change that applies to everything maybe you want a different font or a different font size um you can select all using that arrow key and make those changes be careful of course because anything you do to one we will do to all but it is really useful for formatting your your sheet now maybe you want to select you know a different range of rows or columns first the way to select any one of those is if you want to select a column you go ahead and move to that letter and you can see my mouse has turned into a little black arrow that points down i can do that for any of these columns if i want to select more than one of course you select the column you want to start with hold it down you'll see that now my mouse has changed to a little sort of white plus sign and i drag to select more than one column i can do the same thing for rows so let's say we want to select some rows i will left click on the first row i want to select and then drag down just a quick way to select more than one now you can also select a collection of rows cells or columns that are not next to each other to do that let's try starting with columns so go ahead and select the first column now look for your control key on your keyboard and hold that down then select another row or excuse me another column that is not next to that first column like i and k i'm still holding down the control key you can see now that i have selected all three of these columns even though they're not next to each other and that can be really helpful if you want to do something quickly to different columns that aren't right next to each other you can do the same things with cells so try doing it just for different cells hold down that control key and you can select a lot of different cells and if you want to deselect of course you just click somewhere else without holding the ctrl key and you can do the same thing for rows in fact as you can see you can even do rows and cells so it's quite powerful now you may want to do this i think when you start selecting too many it can get a little confusing but it's very powerful for a variety of reasons now let's pretend imagine using this for a a reason like let's say there's two rows of data that we want to come back to because they might look funny for some reason or we want to check that they've been inputted correctly so for example let's say it's row 4 and row 10. go ahead and select those we're going to go to our home menu and then we're going to navigate the cursor over to this paint bucket right here and that has our fill color this is going to let us just change the color of these rows so we know when we have time we want to come back and review the data in these rows now of course when i click away now we've colored them and we know to come back to them i'll pause for just a minute and let you do that audrey when you get a second there was a question about painting that ribbon up at the top collapsing and pinning so if you can circle around to that at some point that'd be great sure yeah great question i know for me sometimes when those menus disappear and you don't immediately know how to get them back it can be a little [Music] momentarily feel a little you know like make you feel uncertain so i that's definitely been me before so if you want to collapse your ribbon so again the ribbons are any of these menus at the top and they show a lot of different icons and paths you can do you move your mouse over to the far right hand side to find this little carrot little up arrow and when you hover over it it shows that when you click it you're going to collapse the ribbon and now i've got a lot more space to see all of my data now if i'm ready to come back and use those tools again i just click any one of any ribbon and if i want to make sure like right now if i then go away again it disappears if i want to make sure it stays you move your mouse back over to the right hand side and now instead of a carrot you see this little pin and when you click that it keeps it there so you can always refer to it and it doesn't disappear awesome thank you yeah great questions keep them okay the other thing i wanted to call your attention to is uh let's look at this family enrollment column so if you go to these cells here you can see there's a lot of pound symbols um these at first glance might look like there's an error but you can see when i hover over the cell that some data is showing right underneath it it's showing a date and you can also see in the formula bar that there's a date here as well the other thing to notice is over here in this number group there's a category called date so that's helpful this is telling us the type of data that's entered in there we're going to practice right now making sure we can make this as visible as possible and we can solve this problem of this data that looks kind of funny so the thing we're going to do is to practice organizing and saving our columns and rows so the first thing to do when you see this issue which you may see in some of your own spreadsheets is to resize the column so to do that move your cursor or your mouse to the right hand side of that b column and you can see the icon change to like a little two icon with two arrows pointing each way and you wanna left click with your mouse and drag that until you start to see it and then miraculously all of those numbers appear which is great and of course makes it much easier to read now we can also read the header at the top there's a couple different ways to resize columns another way let's go to our next cell that seems to have this or this next column that has this issue that's column f so if you move your mouse over to the right hand side of column f you can again see that icon with both arrows and instead of dragging it wider instead just double click when you double click you can see the cell or the whole column resize and when it what it auto when it auto resizes it's actually choosing the width of your widest cell so it's pretty neat excel's pretty smart that way so that's way number two another way so our next column with these issues which i see is this column h is to right click you can move your mouse down the menu to where it says column width when you click on that a menu will pop up that asks you to input the column width now right now it's showing at 6.56 and i don't really know how wide i want it but let's try 9 for example i'll click ok and you can see that it's made it wider this is not a command i use all the time but it's helpful to know that it's there now another tool or i think using that tool in a way that's maybe even a little easier is to do a lot of resizing at once so if we come over to the right hand side we see we still have some more cells that are not visible because they're not wide enough now we're going to select multiple columns using the method we used at the beginning so to left click on column o and drag over to column q now i'm going to move my cursor to any one of these the right-hand side of any one of these columns double-click and you can see that excel has resized these columns to fit the widest cell so in the case of these three columns the widest cell is the header with these sort of long names i'm going to come back in just a minute and we can talk about how to make this even a little easier to read but maybe i'll give you a minute if you have not finished doing that so you can finish resizing some of those columns so we can see all of our data and i think we just had one or two folks join so if you're just joining us we are working through our beginner workbook so feel free to open that up and start practicing with us okay great so we saw when we widened those columns that we made some of them really very wide and maybe that's a little too wide because i see all of this white space and now i have to scroll more when i'm looking at my data i think i've decided i would actually like to make sure that these are smaller but that i can still read the header and the really neat way to do that is to use this wrap text tool so let's select our first row that's row one and then you'll move your cursor up to the top of the home ribbon if you're not on the whole ribbon click on the home ribbon and move over to this alignment section you can see an icon that says wrap text up here and of course it's again helpful to remember that excel has these little hints for you so if you hover or just leave your mouse over something without clicking on it it can often tell you what it's going to do so this one says wrap extra long text into multiple lines so you can see it all so let's click on that and amazingly now i've got some of these columns kind of wrapping around now i'm going to have to do some resizing of these because i can't read this very well so let's take a minute and resize our columns so you can do this individually you can do them sort of multiply at once so let's say i want these all to be the same width i can select all of them at once and then drag them and you can see all of them resize to that approximate width so let's take just a moment and why don't you work to get your columns formatted in a way that's easy for you to read so i'm going to keep resizing a few of my columns and if you think your row is too long once you've sort of started to resize remember you can also resize your row as well so you can see my cursor over here coming to the bottom of row number one and i'm going to move it up a little bit so it doesn't it's not quite as big if you've just joined us and you want to follow along on the participant guide we are on activity 2. perfect okay has everyone gotten their sheet to a a format that works for them feel free to keep playing with that as can you show me again how do you go in and put the with like if you want to put a number in where do you put that in at oh sure that's a great question so if you kind of want to resize things using numbers select any column and you can right click and you move your mouse down to the column width section and when you click on that it will bring up this column width option i think these are just called points i don't know that there are measurements exactly and you'll start to maybe get used to them if you use it enough like let's say we want to do a lot wider we could choose 20 and it will widen it quite a bit um sometimes it's it's a helpful way if you want to do it to multiple you can also select multiple select the column width and maybe you'd do 15 or something and they all size to that amount the other way to resize everything at once of course is to select multiple and then you drag the column it's now you see how i've made the i column narrower now all the rows that are all the columns i've selected will also be that narrow so a couple different ways to do that all of this that i'm showing you too you can do for rows so if you went to the left hand side right clicked on the row of your choice you would move your cursor down to row height so a little different i we'll show you the row height great any other questions there well we've done a lot of resizing here um looks like there's a question about the column names are they always named with a b and c yeah a great question yes so anytime you have an excel worksheet you will have the columns named with these letters and you will always have the rows with these numbers so just to reference our other sheet for those of you who joined us a bit after we started we just added another sheet here at the bottom by clicking this little plus sign you can see here this one added a third sheet which i don't need right now but it can it will show you that i've added a new worksheet and i still have that same system so that's true of any worksheet you're you're in and when you're referring to the cells again you start with the column name and then the row so it's column e row 3 this is cell e3 so this is helpful again if you're starting to write formulas and want to know how to reference certain spots so since i don't need the sheet again right clicking is always helpful to try if you're looking to do certain things in excel i want to delete this so i'll go ahead up to this option and i'll click delete and now i have and back to my two worksheets i'll head back to my demographics tab great question i like to think about it like a battleship uh the battleship game i don't know if anyone has played that but where you say you have to guess where the opponent's battleship is and you have a grid and you have to say e1 and then that you kind of uh see if you hit the other person's battleship so um that's how i like to think about it helps me remember yeah that's right jen they're like coordinates so can remember and if you forget what they are you don't again you don't always have to look and see which row and which column you're in just refer to this little box over here and it'll tell you which cell you're in okay so we've resized our worksheet so we can see it a little bit better and again do it however you like however is easiest for you to see we're then going to practice inserting and deleting columns and rows so often times you know you may have a worksheet and then you decide that you're going to start collecting new data or you want to add some information so to add a new column let's say we're going to add some more information to this worksheet we'll select the column where we'd like to add the new information so in my case maybe i'll select column c and i'll left click to select it now there's two ways to insert a new one so i can either right click and i'll pull up this menu and i can click insert and you see a new column has been added right where i had clicked before and the family exit column has just moved to the right so i didn't lose any information i just made an extra column for my new data the other way to do this is to let's add a second column so we'll keep this column c selected we'll go to the home ribbon and we'll move over to the cells group which is right over here you can see this icon that says insert and i want to insert a sheet column so i'm going to click on that and you can see it's added another sheet so again this is just based on your preference if you're more comfortable using the right click of the mouse that works just fine if icons work better for you you can use these up here it's whatever works best excuse me can you repeat again the right click on the mouse when we need to do an another sheet the column sure can you think you're yeah you're asking about adding another column for sure yeah yep so to add another column you first need to select where you want that column to be so we can try it somewhere else if you want column i now that it's selected and you can see that you're selecting the whole column because it's all grayed out and there's a little green box around all of the selection area and you can see when i move my mouse over the letter that it's changing to this down arrow so it's showing me that when i click when i left click on it it's going to select everything now that it's selected i'm going to right click to bring up the other menu and then i see a bunch of options right now i'm trying to make a new column so i'm going to move my mouse down here to the insert button and i'll click that to make my new column there's a question here about adding several rows or columns at the same time and if there's a quick way to do that that's a great question so this is yeah a lot of times you might need to add a lot of space let's say you're adding new clients of course you can just go down i'm scrolling down remember you can use your scroll bar or if you have a mouse with a scroll wheel you can also scroll down you can simply start adding at the bottom or you can select let's say you want five new rows i would select five new rows or i would select five existing rows i'm going to right click on that row and i'm going to click insert and now i've created five new rows now they're wider than i want or longer so i can simply pull them up like this to make them all the same size or i can this is sometimes helpful if you want everything to look the same you can select all of them with one of your other size rows selected and simply just hover over that to see what the pixels are you can see that it's 15 remember those points that we were looking at earlier so now i can select the rows i want i can right click i can put row height and it is right now it's showing me that it's 18 but i want 15. so it looks like everything else so that way i've added some rows and it looks the same as the other ones the same height of the row now the same you can do it the same with the columns as well so you could select several at the same time you would go up to the top and you could right click on either one of these it doesn't matter which one and you move your mouse down to insert and you would insert two columns at the same time so that's a great question because sometimes you might need to add room for a lot of data and you don't want to do it one by one so this is a way to do it a bit quicker and just a quick note it's um when you add a column it's always going to add that column to the left of where you've selected and when you're adding rows it's going to add those rows above where you've selected so wherever you choose it's going to add those extra rows or columns in so if you wanted to add somewhere mid data in a specific place you would choose below that row or to the right yeah that's a great college and if you kind of get used to where it gets added then you're not surprised so i always remember on columns to the right i select to the right or you know basically if you want the data here you know that this row is going to or this column is going to move over and the same is true of the rows you select here to insert and the new row comes above so we've just done a lot of inserting and now we have a bunch of blanks in our spreadsheet so let's let's say we changed our mind and we want to remove them there's also two ways to remove them so we can start with the rows i'm going to go ahead and select these first seven rows by left clicking on the first on the second row and dragging down to row seven now that all those are selected i can right click with my mouse and i can move my cursor down to the delete menu button and click that and they've all disappeared let's try the same with the columns so again let's select however many you made you may not have made as many as me but you left click and you can drag if you have more than one now on the home ribbon you can move up to this delete button click the the icon and you have this option to delete the sheet columns so we'll go ahead and delete those and i have this other one here that i also want to delete and i just typed something whoops there we go okay and now i'm back to where i was but now we can see how we might add and delete i'm going to ask you to come back to column c we actually are going to insert one again this will just be some additional practice so however you like to do it whether that's by the menu right clicking and selecting insert or by selecting and clicking insert up on the home ribbon either way is great now we're going to practice a little data entry which i'm sure many of you do in different ways but this is sort of good to remember why we're doing this which is a lot of times because we are managing and dealing with a lot of new data so we're going to make a new column that's called county so let's say we have our our funder wants to know where all of our clients live and let's say okay we know this that everybody in our list is a member of king county or lives in king county so we just need to quickly fill this out when you've entered in the name county in c1 there's two ways to get to the next one you can move your mouse and just click it down to c2 or you can have your cell selected c1 and you can click the return or enter key on your keyboard and you can move right down so you can also move down by using your arrow keys so it's whatever works well for you but i find when i'm doing a lot of data entry clicking the enter or return is just a really quick way to move down a set of cells so that's a way to move down click enter now if you wanted to move to the right hand side if you look at your tab key on the left hand side you'll start moving to the right which is also a helpful thing you can also use your arrow keys to move to the right and left as well those are awesome tricks and depending how you enter data one of those might be better for you but in c2 let's select that one again and now let's type king county and now we can practice using our enter or return key to move down to c3 and let's enter the same thing now let's try the arrow key to move down to c4 and we'll enter the same thing okay i don't know about you but we have a lot of people here and i'm gonna get tired of typing king in every one of these cells okay so there's a couple tricks to do this a bit quicker okay so we've done three rows and entered in king now i want you to note that when you're hovering over this cell right now you can see it looks like the little text icon looks like we know we can type in text here now if you move this over you can see it changes when i get to the corner into a little black plus sign so i want you to left click and hold that and now try dragging all the way down and then when you get to the bottom release and now i've got king in all of my cells now notice too it's also pulled down all of my formatting so i basically just sort of copied and pasted there's two ways you know you can address this two ways you can either select the whole column if you don't want all of them colored and you can choose no fill sorry no fill it's not allowing me to do that let's select c2 through c88 i'm not sure why it's not allowing that but let's do just the white so now this is white i'm not totally sure why that is happening but we'll keep exploring that a great thing to do if you're not sure what happened and you want to kind of go back is you can find this little undo button up here so this is undo format cells we can go back a couple as many as we want and it's erased my county here we go not sure what i think maybe i'm having a lag with my zoom screen a little bit so it's possible that's but that that undo button is super duper helpful and you can either click it up here to do undo or you can choose control z control z it will also undo and that's just something you can click on your keyboard so let's go back and i'll re-enter my first king so i showed you that you can either pull and drag down the other option that's super smart and just as fast is to do the same thing is to move your icon to the bottom right hand side of your cell and you can see that black cross show up and instead of pushing and dragging instead we're going to double click and you can see the same thing has happened so it's also very smart it stops filling in the cells when your data stops so i think this is a little better than clicking and dragging because it allows you to fill in all of your information at once and now we have this problem where i've deleted my formatting for these two cells that i want to keep highlighted because i think i want to check them so instead i'm going to select this again hold down control select the other one and then maybe i'll color it again to bring it back to where it was but now i have my new column with all of my clients identified as being king county associated does anyone have any questions on that okay excuse me yeah um i was not able to drag down the king and copy down everything is there any other trick i can use or is my can you repeat how to to drag then do you push on control or what is the trick there i did not get it so sure yeah let me do undo so i can again i'm not sure why this is yeah it's just taking a little longer for me okay so the two ways to do it are i've entered my data into one cell and both both ways involve bringing your mouse to the bottom right hand corner of that cell until you see this little black icon yeah you don't need to press anything with the keyboard you can either double-click to fill or you can left-click and drag and either way should allow you to sort of copy the same thing from that first cell are you able to see that no it says there is already data here do you want to replace it so i say replace do you you can say replace if yeah i think it's possible you maybe are testing it on a column that already does have some data in it so oh yes that's right i see how so you can insert a new column that's what we did if you actually wanted to copy over that data that would be fine but it's nice that excel is asking us right because they're saying hey you're about to copy over data that you already have so that's a good reminder that oh maybe i wanted to put this in a new column okay great question yeah thank you sure okay so we've added this column let's say after all of this we've decided you know maybe we don't need this after all the easiest and quickest way to remove data from your cells is to select all of it i'm going to just select this whole column i'm going to right click i'm going to move my mouse down to this clear contents and it all disappears so i think this is a really easy way just to start new start over without you know missing something down below or selecting each one and deleting this is really one of the quickest ways so you can practice by right-clicking on the column and moving your mouse down to clear contents and you should have another blank column again and now i'll delete this because i i realized you know what everybody's from king county maybe i don't need to track that if that's true for everybody we're also going to practice moving columns a little bit like how to copy paste and move so let's say we have our case id and our individual id and we want them to be next to each other let's insert a column to the right of the case id and remember as jen explained we know that when we insert a column it's always going to be to the left of the column that we select so we want to select column b we're going to right click if you wish to insert or again come over to this icon to insert perfect and now there's two ways to do this we can either select our individual id column i'm going to right click to bring up this menu where i can cut come over to column b and i'm going to hit on my keyboard ctrl v this is the quick way to paste or i can also click this paste button up here and i've just moved it over now to show you a different way i'm going to click this undo button to go back and now that did undo for my last action i'm going to click it again so now i'm back to where i was where i didn't have my new column so again these little undo buttons and redo are super helpful if you've done something and you don't know what you've done and you want to go back i use them all the time the other thing you can do to move this same column is you can right click on the individual id just like we did before you can cut and now instead of putting it in a new blank column i can come over to column b right click and i can insert and just like inserting a blank column inserting moves everything inserts your cell to the left so whether you insert a blank column or insert a column from somewhere else it will put them to the left of the column you chose so you can see here i just moved it right over so that that's kind of a clean and neat way to do that okay does anyone have questions on that i think since we are about halfway through our content now might be a good time for a quick five minute break just to get up this is a lot of spreadsheet stuff stretch your body um get a drink of water whatever you need you can also stick around and ask us any questions if you have something that's burning via chat but we'll come back here at 2 10 so that's in just six minutes to continue what we're what we're learning awesome thanks audrey we'll see y'all back here in about five okay and now that we are starting back up i'll start the recording again i'll start sharing my screen okay so to jump right back in we just finished doing some cutting and moving cells and columns now we're going to make this even a little easier for ourselves to work with so we're going to format our header row next so go ahead and select row one i'm just having a little bit of slowness there we go with my computer so again click on the one to select all of the cells in that row and let's look again on our home ribbon so we've got a lot of ways that we can help make this a little easier for ourselves to read we've got our font keys if you wanted to use a different font these are really helpful if you want to quickly change the font size you can click on that and increase the font size or click down to make the font size lower we've got our normal sort of bold italics and underline different colors we can use the alignment section is also really helpful that's where we found our wrap text command now let's try to add a border because i think it'd be helpful to see a border underneath my header row and the rest of my data so to find the borders i will navigate my mouse up here and you can see that it shows me this is a border section i'll click on this drop down and i can add a bottom border now i can look and i see that this has added a border now that might not be thick enough so let's select again move the mouse back up click on the down arrow and actually there's a pretty helpful since a lot of people want to do this kind of command there's also a thick bottom border you can add right down here so you can click on that now let's take a look i think again my computer is a little slow today and it is having some trouble showing but it should be showing a thick bottom border this was doing it just a moment ago for me hopefully you are seeing it on yours if you want you can also play with drawing the borders i won't go into that too much now but you can actually when you click on draw border you should see a small icon pop up again i'm not sure i think i almost might need to restart my excel i'm having some some funny things happen here sometimes that is needed but let's see here i'm going to keep going for a few minutes and hope that this resolves itself but i'm actually not sure why it's being so glitchy just in time for training that's usually why but we've gone ahead and added a border underneath that should make things a little easier for us to see now let's also modify our header to make it a little easier to see as well so we can again adjust the row height however in whatever way you would like i always think it's a little easier to make it bold so you can select the bolds and now since we've got kind of a wider row let's try playing with the alignment a little bit now if you go to this you can see right now these are aligned to the left most of them you can click on the left you can also put them in the center if that's easier for you to read or to the right i'll keep mine to the left but i like up here where you can kind of put it at different heights in the row so let's click in the middle and you can see all of the data move up to be in the center of the row height so i think that makes it a little easier to see as well i also think it's helpful to make your header a different color so i'm going to for this one select green now i can see that it's different from the rest of my data and now looking at this i actually think my borders it would be helpful to have borders in between my headers because i can't tell where one starts and the other stops so i'm going to go to all borders and click that and again i'm not sure why i'm having a glitch on this particular command but you hopefully are seeing all borders in yours please mention if you're not but that should be working and you should be able to see the borders pop up and i have to write to the excel support team on this one okay great does anyone have i see a chat is anyone having issues with that cool okay so now we've made our header a little easier to see we've changed the color we've bolded and moved it the direction of the cells or moved the spacing around now the next thing we're going to practice is freezing so as you remember this was one of lynn's favorites so i actually sent you this workbook with the top row already frozen so if you move the scroll bar down you can see that the top bar stays in one place which is super helpful but let's play around with that a little let's go over to the view ribbon click on the view and move our icon over to this freeze panes button and you can see when i click that i get a couple of options i'm going to ask you to unfreeze the panes so click on that and we can see now when we move down that this header has disappeared now that's not super helpful because if i get all the way down here and i'm trying to remember what these categories are i have to scroll all the way back up to remember so that's a case where it's really helpful to freeze the panes you can simply go up here and just freeze the top row that's a very common command so excel has its own special button for that you can freeze the top row again i'm not really sure what's going on here with my computer but that should be working for you you want to take a second andre to just close out excel and reopen and see if that fixes things yeah let me try that i feel like it's a little funny to tell you these things work and then have it not work on my screen so let me try that just bear with me one moment where all else fails turn whatever is not working off turn it back on again and it will likely work that's my best tech support tip [Music] fine this is a great example of how yeah how to problem solve though sometimes it just gets kind of glitchy and you just have to restart it okay it seems to be working again let's hope let's hope okay so i unfroze my panes and now we'll come back to freeze top row and it seems to be working okay yes so you can either come over to this icon and you can freeze the top row that's really easy or you can freeze the first column that does the same thing but over on the column so if we then scrolled you can see my case id stays there those are like the really quick ways but let's say you wanted to freeze it so you could see both the case id and the individual id we'll go back to unfreeze panes and if i want to select a like a special type of freeze i want to select the cell that is to there's one to the right of the row i want to freeze so if i want to freeze rows a and b i would select c and if i want to freeze the first row i would also select the row under that so everything to the left of this columns to the left of the cell i selected will freeze and rows above it will freeze so go ahead and try that select c2 and come over to freeze panes and instead of clicking freeze top row or freeze first column you're going to click freeze panes and let's test it out so now i'm going to scroll over and yes now i see that column a and column b are frozen and i also have my top row frozen too give you just a minute to finish that up but this is super powerful because anybody who's had looked at big amounts of data on a worksheet knows that sometimes it's easy to get lost in what you're looking at and so making sure that you have these guides on your column or your row can be super helpful awesome audrey do you mind going through that one more time yeah and then showing yeah how to do that multiple select as well yes let's unfreeze our pane so we're starting back at the beginning so again up on the view ribbon to the freeze panes button we'll unfreeze our panes and just check to make sure yep nothing is frozen now so again if we want to freeze just the top row or the first column we have these quick icons to let us do that a lot of times we want to do something more specific so in the case where we want to freeze the first two columns and the first row i want to select the cell that is directly to the right of the columns i want to freeze so everything to the left of this column will be frozen and everything above this row will be frozen so you know you could easily select the one below if you had two header rows and it would freeze row one and two so let's just try that for fun so i've got my my cell selected c three again you can see up here c3 just you can refer to that come up to the freeze panes and click that first icon of freeze panes and you can see i've seen two little lines have appeared that help me see where it's freezing now if i start to scroll down i can see that rows one and 2 are frozen that doesn't make a lot of sense in this one because this is a client but there may be a reason or you might have two header rows sometimes and then if we scroll to the right you can see a and b are frozen so there's a question also about whether or not you can only freeze one pane at a time so you can freeze as many uh columns or rows as you'd like actually yeah i can show you that briefly if you if for some reason you wanted to you know start here and so remember everything above the cell all the rows above the cell that you've selected will be frozen so that's going to be rows 1 through 10 now and all of the columns a through e will be frozen if i'm selecting this cell you can see again those little lines pop up that shows you this is the border this is the part that's frozen for the columns and this is the part that's frozen between 10 and 11 on the rows and if i scroll you can see that it's frozen further down and to the left so you at the with this example you are freezing both rows and columns at the same time excuse me i have any question can we freeze like column a and b and o can you freeze column a and b and and oh i see no you have to freeze columns that are in the same uh that are connected to each other so i think it might be if you wanted to freeze o as well that might be a reason actually to do what we did before which was like to write and move it okay because that and that might be a real thing you want to do because sometimes your data is not in the right spot in your in your sheet and that's a great great time to move it over to where you want it that's a great question thank you okay for my purposes now i'm just going to freeze this top row so i can see what's going on here okay now we're going to do a little data manipulation so some of these tasks can really help you understand your data a little better um they help you organize and declutter it so now we're going to go into sorting and fill sorting and filtering which is what jen and i two of our favorite tasks in excel so sorting helps you put data in order by the date by number or alphabetic order uh filtering helps you isolate data based on the criteria that you choose so let's start by sorting let's select the service start column if you navigate to the home ribbon i'm going to go to the editing group which is over here you can find the sort and filter button when you click on that it's going to ask it's going to show you a couple different things it's going to show you some sort options you can do a custom sort or you can do a filter in this case we're going to do a sort so let's say we want to see all of our clients from the ones who started longest ago to the ones who started most recent so i'm going to select this top option which is sort oldest to newest and i'll click on that now many of you may see this warning pop up so it says microsoft excel found data next to your selection since you have not selected it it will not be sorted well what would that mean if i just conti if i click this button that says continue with the current selection that excel would sort all of the data in row g but leave all of the other data as it is and as you can see as you look at this each of these rows is one client we've entered in all of their data so if we sort column g and don't keep it connected to all of the rest of their data we're going to basically sort of make a mess of our data we're going to sort things that don't stay connected to the people that data is connected with so it's great that excel warns us about this and what we want to do is to keep this button clicked which is expand the selection now anytime you sort data most of the time if you're working with client data you want the sorted data to stay connected to the clients that you're working with so this is usually something that you'll leave clicked so now we'll click sort and now as i look at this column i can see that i've got my earliest started starting client which was 329 at the top and if i scroll down you can see it goes sequentially down until i have my most recent client who started in october of 2019. so this is super helpful and maybe if that's what you're looking at like who started long ago versus who started most recently this could be a reason to sort this way okay any questions on that all right let's practice filtering so we'll come over here to the individual language column which is column l for me if it's a different column for you because you were adding some columns no worries go ahead and just find individual language we're going to go to the same button the sort end filter button you can also find these sort and built filter buttons on the data ribbon but i'm just looking at them right here so i'm going to click that and i'm going to add the filter and you can see there's this little drop down triangle that appears on this column now when i click on that i can see all the different types of data that are in here so i've got english speakers japanese speakers and spanish speakers so when you add a filter it's one really useful way to see what kind of data you have in here now if i want to only look at one of these i will deselect and let's say i want to see who in my program speaks spanish so i will only select the spanish speakers checkbox and then i'll click ok now i can see only folks who have spanish listed as their individual language will be selected another way to check that you've got a filter on two ways you can see this filter icon has changed it's become more bold and actually when we hover over it it says equals spanish so this is telling us okay you've got a filter on the other thing to notice over here on the left hand side is that all of your rows have changed color where we have our data and you can see that they're not going one two three four five this is showing us that we have a filter on as well and it's also showing us that we haven't deleted the data we're just hiding it if it doesn't say spanish on the individual language so this is just another reminder that you have filtered your data so this is good to know different ways to look for filters because i think it's easy to forget that you have a filter on and sometimes get worried that you've lost data this is not lost it's just being hidden to get it back you would click on the filter again you could select all we've got english spanish and japanese and you could click ok and then you have all of your data back again now you don't have to just select one you can also select two so let's say i want to see everyone who does not speak english i can deselect english and keep japanese and spanish selected and then i'll click ok and i see everyone who speaks spanish and japanese it's pretty helpful i'll reselect all of those any questions on the filtering so there's a question about whether or not you can use this to print uh the selected data if you put filters on yeah so i think if you're asking what i think you're asking you would maybe want to print everyone say that doesn't have this category selected my i think i'm gathering that right and yeah i think these are great ways to filter down and only select the folks for whom you want to um for whom you want to see on a printout um one other question just just to clarify so when you sort you can't just highlight one area correct it will sort i mean only sort that row or that column but if you do a filter you can it's okay to just select one row or column yeah that's a great question so the sorting it sort of reminds you like you could select the whole thing and then do a sort you could sort smallest to largest i think that would get confusing because i think it starts on the left hand side um but a filter always knows that you want to to filter and keep all of the data together so you wouldn't see that same warning on the filter it would just automatically do that but you would see that warning on the sort if you're just selecting that column okay okay thank you yeah great question um it looks like there's another question about duplicate data so it looks like some of the one of the japanese speakers has a duplicate potentially and the question is can you use filters to remove duplicates yeah that's a great question um this is a great example of different ways you can troubleshoot your data so yeah it looks like i see that too it looks like you're looking at this person this mother who has the same case id and individual id and a filter you may not have noticed on just the japanese but actually if we went over to case id um let's go ahead and put a filter actually a way to put a filter on everything on all of your headers is just to click anywhere in your spreadsheet come over to your filter button when you deselect the filter it will get removed off individual language but i'll put my cursor back in the worksheet come back to my filter button and then you can see i've got a filter on everything so if we were trying to remove duplicates you could definitely see that in a number of ways you could see that in your case id this is an idea where filters are probably actually not very helpful because you've just got the same number repeated and it doesn't um show duplicates but if you did notice that the japanese speaker had two of the same you could easily you know come over here and delete this person so i think filters can indicate duplicates jen you might have a little bit more elegant way to like search for a duplicate in that i'm not thinking of what do you think yeah so let me see here um when we go into case id let me see i'm gonna have to i'm gonna have to get back to you on that yeah sorry not to i don't want to put you on the spot but it's a great question i'm not thinking of like the most obvious way to do it right now but if you could work on that for a minute while we continue that'd be great yeah awesome well thanks for these great questions it's perfect but yes filtering is really powerful for for lots of reasons i want to show you another one on the i column let's click on this drop down here and now i see here that i have three options i have male female and it looks like a misspelling of female so this is a way to just check your data quality um if we were trying to do a report only on females and i didn't realize this was misspelled i might actually miss some people so i see here it looks like a data entry error so let's deselect and only look at those entries that have email misspelled and i've got four so let's fix those right now while we're looking at it so you can just put your cursor right on the cell that you need to fix and let's type female on all four of these rows and again you can move down using the arrow or using the enter key as you type of course the other way you can just as easily drag these here like we showed you before and now if i click back on my filter i see only two options i see just female and male if i look at female now all of those female options show up and the four that i fixed are now also in this group so um circling back to the question about duplicates there's not a way that i know of at least to do it with filters there is a way to do it with conditional formatting and that's going to be something that we're going to be getting into in our intermediate class which is next week so trailer for teaser for that come back sign up for our intermediate session which will be next week but just as a kind of a manual quick check you can uh put your values in order smallest to largest like on your individual id so when you use that sort option on the individual id sorting yeah smallest to largest it's not my favorite option because it means kind of like manually scrolling through and and visually checking to see if you have duplicates but like i said we're gonna get i