Discover the Best Bilty Format in Excel for Nonprofit Organizations

Bilty format in excel for Nonprofit

all right is everyone seeing the excel document yeah awesome okay so we have um we're just kind of imagining a scenario here when we thought about this training thinking about what are some common challenges that people run into when they're using excel and maybe some common scenarios and we immediately thought about um when we get data that's coming from different sources could be from a database um some sort of survey what have you but we're kind of getting an a data that's fit out into an excel it's maybe not the most friendly looking it's not the easiest to read so we wanted to kind of use that as our starting point and as we think about getting that ready that data ready to start analyzing bringing things all together in one place so that we're able to see all the data that we need together and start using our data so we have this intermediate workbook and you can see at the bottom left hand corner there are four tabs there's a tab called demographics there's a visits tab and this is where our data is actually living right now demographic data and visit data and then we have a tab which is something that we'll come back around to a little later uh in the in today's session which is the drop down tab and these are options that we'll be using for our drop down list again we'll come back to that and then there's actually a tab here called working and in parentheses example and so this is kind of our end goal of where we want to take our data today and if you at any point uh want to come back and look at okay how how is that done or you know what's the formula that's behind um you know a given uh formula that we'll be looking at today you can come back and actually look at the formulas here and see how things are set up so it's kind of the behind the curtains um the answers at the back of the book if you will um and then we also have a second sheet uh worksheet that's called asq sorry intermediate asq data this has one tab asq3 so this happens quite a bit too when we get data that's coming in from several different places they might be in different worksheets and we want to be able to bring those all together into the same place to be able to start working with that data and start uh analyzing so i don't know about you but my first kind of intuition or intuitive response when i think about bringing data together is like okay i'll just copy and paste everything into the place that i want to work and we really recommend not using the copy and paste option especially when we're moving whole sheets around workbooks because it's really easy to when you're selecting you know maybe leave out some columns or leave out some rows and that means you're going to end up with incomplete data on the other end so what we recommend doing is being able to pull all of our data into one place through the move or copy function and you can do that by opening the sheet that you would like to move so in this case we want to move the asq data onto our intermediate workbook and we're going to right click on the cell or on the sheet tab go ahead and right click where it says asq3 on the bottom left-hand corner and we get a menu that shows up that allows us to do quite a few different things but what we're interested in right now is this move or copy option so i'm going to go ahead and click that move or copy and i get a dialog box that pulls up that's got several different options here it says move selected sheets so that's the asq3 sheet that's what i'm going to be moving and it's going to move it to the book that i select here and i can see which files i have i currently have open it would actually allow me to create an entirely new book if i wanted to do that but right now i'm interested in moving this from the asq data workbook into our intermediate workbook so that's the option that i'm going to choose and now it's allowing me to choose where i want to place this tab on my new workbook so i can put it before the demographics sheet before the visit sheet those four sheets that we saw um on the on the other workbook or i can move it to the end if i'd like i'm going to actually place this between my demographics and my visit sheets i'm just going to click on visits but really you can place it anywhere that you'd like just a matter of how you'd like to organize things and i'm definitely want to create a copy and what this is going to do it's going to actually create a copy in my intermediate workbook and it's going to leave this original sheet where it's at so i'm going to have a copy of my data on my other workbook so once that is set up i'm going to click ok and it's automatically going to pop me over into my other workbook the intermediate workbook and that's great so now i have uh should have five tabs in this workbook the demographics tab it's inserted this asq3 tab and i have my visits tab as well so we're good to go here we have all the data in one place and this is going to be something that we're going to get into a little more in the advanced session so if you're not signed up we encourage you to do so which is going to be next week and that's how we tie pieces of date data together but for now we just want to make sure we have everything in one place and that we're able to start using our data and one thing i recommend is always having an original copy of your data this has happened to me many times where i start editing on a sheet and i've gotten about halfway through something happens something goes wrong and all of a sudden i've lost data and you always want to make sure that you have a backup copy in order to be able to go back at any time if anything gets a little crazy so what i do just to signal to myself hey this is your original copy don't edit on this sheet actually changed the color of the tabs and we can do that just by coming in selecting the tab that we want to change the color right clicking on the name and we're getting that same menu and what we're going to want to do is play with the tab color here and we can choose any color we want but for now i'm going to do this kind of nice orangey reddish color which is just going to signal to me visually hey don't edit this this is the original data it's kind of like a stoplight don't go there and i'm going to go ahead and do that on my other tabs as well so i'm right clicking on the tab name of the cup that i want to change the color i'm getting this menu and i can come through and change my tab color and i'm going to make these all kind of this nice red and the same with visits okay so now i have i have my original data and i want to just leave that in place let that be i can always come back to that if necessary and i now i want to be able to start what i'm going to call a working tab so this is going to be the place that i want to actually make the edits that i can start moving things around or creating formulas and have a place to work so i'm going to create a copy what we're going to be mostly working with today is the demographics tab so i'm actually going to create a copy of that demographics tab again right clicking on that tab and i'm going to come back into that same menu that we saw before the mover copy menu and this is going to allow me to create a copy in the same worksheet that i'm already working on so i want to make sure that i am selecting the workbook that i'm currently on then i'm going to send it to the same workbook that we're at which is the intermediate workbook and i wanted to create it before the current demographics sheet and i want to create a copy so i'm going to go ahead and select that option and click ok and we're good we have now we should have two identical demographics tabs one of them it's added the little two in parentheses just to let me know this is different than the other one so now i want to go ahead and change the name here on this tab again right click and i have the option to rename and i'm just going to call this working and we can you can just go ahead and type that in to change the name of your tab and again because i've said these red this red color to me is going to visually signal stop don't go there don't edit on this tab i want to make my working tab kind of a green color so i'm going to go ahead right click and come into the top color and i'm going to put this light green so as long as i'm on this working tab i know that i'm good i have my original data in another place i can always come back to that if necessary and we can start kind of playing around with our data here all right and so one of the first things that we're going to want to do with this sheet let's say this is just fresh out of a database we want to be able to organize things a little bit right now it just looks a little messy i'm seeing all these number signs i can't really see my header labels some of the data is hidden um and it's just it's just not very friendly or easy to look at so the first thing that i recommend doing is being able to resize the columns and we can do that in several ways we could do that one at a time by coming in between each column and manually changing the column size once we get this double headed black arrow on that border between the columns we can do that or i can do all of the columns at once and i can do that by clicking up in the upper left hand corner there's a little gray triangle i can click and you can see that selected my entire workbook and i can again click and drag and manually change the size and that's going to go ahead and actually change the size of all of my columns and give me columns that are all the same size which can be really useful sometimes but since i have data that's some columns i want them to be long and i want some columns to be short what i would recommend is with that same double headed black arrow placed on any of these borders between the columns i can double-click and that's going to automatically adjust my column width to the length of the data so now i'm able to at least visually see what's going on in each one of these cells and you can see that those number signs have disappeared for me um and i'm gonna actually just go back here to show that again we have these number signs and what that's showing is this column is too uh short or too thin to show all the data that's happening in this cell um and i can actually tell that there's data crossed because when i click on this or there's data behind these number signs when i click on this cell i can see up here in my formula bar that yes there actually is a date and it's not just number signs so again we can manually adjust to the columns by clicking and dragging between any of the column borders and that's going to go ahead and give me enough space to show those dates or coming back up here to the left hand corner selecting all of my columns and either manually clicking and dragging or double-clicking which is going to automatically adjust everything for me so that's just a quick uh a quick tip i also like to kind of space things out a little bit more um just visually and so i recommend also spacing out the rose a little bit and you can do that again by clicking that top left hand corner and coming in between any of the rows you'll get that same double headed black arrow and you can click and drag just slightly and there we go um that's going to give me a little bit wider row um and so things are just kind of a little more spread out a little easier to view it just it just feels nicer and then another thing that i recommend is being able to identify your column headers you always want to have your column headers in this first row row one and that helps us to know what's going on with our data what what are we actually looking at i would come in and select this on the left hand you can click on the one row that's going to go ahead and select the whole row and we can bold these column headers we want to be able to distinguish this from our data because this is not actual data this is just our our column headers or our titles and we can change the color of the column headers as well i'm going to do this nice green and we can also so we we're now able to visualize what our column headers are a little easier um but we still have the difficulty if i want to scroll down in my data and see you know maybe a client that's dominant way at the bottom i've lost my column headers they're no longer i'm no longer able to see what was this date and this other date i have a number here i have another number over here i don't know which ones these are so we want to be able to freeze those column headers in place and have them visible as we scroll down so we can do that by coming up into our menu options up here we have several menus or ribbons you'll hear us call them and we're going to come into the view ribbon and we're going to actually freeze panes and there's several options under freeze panes but right now i'd like to freeze the top row and it says keep the top row visible while scrolling through the rest of the worksheet so i'm just going to go ahead and click that option and we're going to test to see if that worked scroll down and yes i can still see my column headers even as i'm scrolling which is really helpful so fantastic we're set to go there um another thing that you might want to do i'm going to come back to my my home menu another thing you might want to do is be able to center these uh columns headers and this is just a matter of personal preference you can center and in fact you could center all of the data i'm going to go ahead and do that i'm going to select my whole workbook i'm just going to center everything so that i'm just able to visually uh see what's going on in each one of the columns and we do have some column headers that are kind of long these ones over here we have these long column headers and kind of just skinny data so it's kind of taking up a lot of real estate right now so what i'm going to do is abby's favorite tip which is wrap text i'm going to go ahead and select my headers again and click this wrap text option and what that's going to do you can see it's already kind of moved things around and you might be like oh where did that go where did my header go but it's it's all there we just need to expand this top row a little bit and so great now we have the monthly average monthly income which is showing i can make these uh columns a little thinner so that they're not using up so much space again this is just your visual preference whatever um is going to be most useful for you as you navigate your workbook but some of these things it can seem like a minor detail but it really makes the difference between feeling like you can manage and understand what's going on in your workbook and feeling like this is just chaos and i have no idea what's going on here so again just personal preference but um kind of some fun different options uh for you if you wanna play with that so all right um and the other thing that i really my favorite tip uh excel tip uh is putting on filters onto our workbook and what filters allow us to do is to filter our data we can choose a criteria that we want to only show certain parts of our data at a time it's a really quick way to just drill down into a certain criteria and it's also going to going to give us the option to sort and there's several different sort options it's going to make sure as it moves data around all of the rows are going to stay together so i don't know if you've ever had this where you've tried to manually sort things and maybe client data gets mixed up and and moved around in different places this is going to keep everything tied together but it's going to allow us to sort quickly and to filter quickly so you can actually click on any one of the cells here in our workbook and again we're on this home ribbon and we're going to come all the way over to the right hand and we have the sort and filter option um and yesterday someone was having a little trouble finding this it was in a little different place in their version of excel so if you're working with a mac version or a windows version or maybe even google sheets you might see these in little different places but just know that they're there and you can always um ask google or ask the internal help on excel and be like okay where is this option but just know it's there if you're not able to immediately find it so this is the sort and filter button and excel is great and then it shows when i hover my mouse over the option it gives me this little message that says organize your data so it's easier to analyze you can sort those selected data from largest sorry from smallest to largest largest to smallest or filter out specific values so that's exactly what i want to do i'm going to click that option it brings up a drop down menu and i can go ahead and sort directly but i want to put filters on my data actually so i'm going to select the filter option and as you can see i've got these little drop downs that are automatically added to each one of my column headers and in some of these cases it looks like they're kind of covering up my column header so i'm going to go ahead and readjust our column widths again upper left hand corner double click in between oops i dragged there i want to double click in between any of the column headers and things are looking good i can read um oh let's see there we go i can now read all of my column headers awesome okay and so these filters you can write or sorry just regular click left click on any of these filter buttons and again i have several options i can sort a to z i can sort z to a so alphabetical reverse alphabetical order and i'll also have the option to play with these filters um and what it's doing is giving me a list of all of the values that it's coming up with in this column so right now i can see that under relationship i have child father grandparent and mother and this can be really helpful just as a quick visual data quality check to see what values are showing up in a column um sometimes especially when we're entering data by hand typing things in some of these options might get spelled wrong i might have grandpa instead of grandparent you know so just some things i might want to come in and correct but right now just for an example let's sort this a to z to to start with our sorting and i can see that i've got this little arrow this up arrow that's all of a sudden on this filter and looks like i've got all the child children at top then father then grandparent mother just in alphabetical order um so it's kind of it's grouped everything for me together really nicely and again i can do reverse alphabetical if i'd like but um now i want to play around a little bit with the filters so right now it's selected it's showing everyone uh in this data set and if i click on this unselect the select all option that's going to leave all these check marks blank it's going to actually not show me anyone but let's say i only want to see mothers let's show only mothers for a second click ok and great we're getting all of the mothers in our data set and you can still see that these numbers these row numbers that it's showing me i have i'm starting actually up at the very top at 56. so i know that row 2 through row 55 are filtered out they're still there so i don't need to worry my data's my data is still in place but i've just filtered it out and i'm only showing people that meet uh the criteria that i set up so if i want to i can clear these filters or i can come back and select everyone either way so awesome all right i don't know if there are any questions before um i can take a break here to answer some questions questions yeah and if you want to make the world for reporting purposes like you did the child and mother can you just duplicate that whole relationship um and then we could rename them is it just like whole column and yeah if you wanted to add another relationship column or sorry yeah relationship and then just click like this one is for child the other one's from other than you could be named oh to to separate out um the data in the columns yeah you could do that i would recommend in this case leaving it as one column um and using some of these filter options to isolate and drill down on some of these options but definitely you could have um you know a child a child or sometimes people call them even client if that's who they consider the client to be in one column and you could insert a new column by clicking on the top column to the right of where you want to insert right click and click insert and we could say um you know we could add another relationship column or we could add another column that has some other identifying information but um but you would have to copy that whole column as you're adding it and then that way you can if you choose which um how to sort it right yeah i'm not i guess i'm not exactly sure what you would um what would be the end goal with that to understand you're seeing the the children and you're seeing the mothers it's just an example but then you can just copy the whole column and we can rename it but it's it still sorts it um like what you want it's just i was just curious yeah yeah definitely and that's it's really going to depend on what you're trying to do with your data and so there's not one right or wrong way um if i wanted to show both children and mothers at the same time i could do that by selecting only that's only child and mother that's an option as well um and that would allow me to see children i would see children up top and i would see mothers at the bottom but there definitely are cases where you'd like to have that as two separate pieces of data i will also say i mean i don't i'm not sure if this is getting at your question but um for analysis often and we'll get more into like deeper analysis next week but uh you usually it's good if you want to be able to distinguish children and parents and not just look at the whole family unit to have each row be a different client and that's what this data set is doing so you know a child you'll be able to just look at like the child's race and then also just distinguish the parents and so that's why this data set is built and i would say you know usually in doing reporting and stuff it's good to have a line for each client and then and i think what maybe you were saying that you're interested into is like looking at the connection so how do i have the child buy you know like the family together so i can look at them together and what you could do is a like a double sort so first you have what jen did is you know sorting by relationship and then you could sort by case id which is actually what is tying them together as a family so each family has a specific id the individual ideas for each individual person but as yeah jen just did that and so now you see the case ids are all clumps together and so you have that whole family you can really quickly see the child father and mother in that family all right next to each other because she and it will be always in that order because she first sorted alphabetically by relationship so cfm and then um by case id so i'm not sure if that gets out your question but no that just it's very powerful like data sorting thank you yeah no this is really helpful and especially when you're trying to do um kind of do data clean as well just make sure that your data quality is really high this is going to allow you to kind of see if there's any holes or if you have yeah be able to sort and see everything that's going on so thanks for that question any other questions about organizing uh your data or filters sorting yes jennifer hi is there um a one button quick way to reset everything back to the original say if i have different formulas set and filtering and i just want to go back to what i had originally because it was a temporary look yeah um the original original that's why we recommend keeping an original tab so that's i mean that's your fail-safe uh tried and true way of making sure that everything is in its place um to remove these filters let's say okay i've done my filtering i've done my data quality check i don't want these filters anymore you can come back up into the sort and filter option again on our data tab or sorry on our home tab and i can actually clear uh the filters so that's going to remove now i've removed all of my sorting um that's no longer sorting or i can actually click the filter button again and that's going to take the filters off so now you can see i no longer have those drop down lists on each one of my options i'm just going to leave them on for now because it can be helpful great question thank you and then as far as like removing formulas and that sort of stuff um abby do you know of any like magic reset button no yeah yeah control z that's yeah the magic is having that always having your original raw data just in case um yeah i wish there was a magic uh reset button that would be great especially when you when you don't have an original copy if you forgot um great question any other questions out there awesome well if any other questions come up as we continue on feel free to uh chat us as we move along all right um so i had to mention at the very beginning when we were looking at our tabs the option of creating drop-down lists and as we think about adding data into our data set maybe manually adding some some data it can be really helpful to use some tools for data quality assurance um especially when we have a set number of choices so i'm going to use the example here of the categories that bsk the options that bsk uses for race and ethnicity so in this data set you can see that uh race and ethnicity are actually in two separate columns and let's imagine that for reporting i need to be able to translate that into what bsk has set up as the options for reporting so in this case when i have a set a preset list of options that i know that i always want to only use those options i want to make sure i don't have any spelling errors and have any additional made up categories i would like to use a drop down list and as you can see we have our drop down categories um that are in our drop down tab um if this is i've pre-made this for this example but if you were to make this on your own you would create a new tab with this plus button on the side of our tabs to create a new sheet uh it's created sheet 3 and then i would key in or copy and paste in the options that i have for my drop down list but for now i'm just going to delete that because i actually already have one that's pre-made for this example and i've copy and pasted in the bsk race and ethnicity options and so it's like hmm i have mexican okay mexican american and chicano and then just mexican okay i was like why do i have that twice um all right so we have our options here and i want to be able to set up a new column in my working tab so again this is where we're getting into editing or adding data i'm going to add this column after my race and ethnicity columns that are already on my sheet so i'm going to come over to the right hand side of where i want to add the column and you can right click to insert the other option that you can use is up here at the top this insert button that allows me to insert a sheet column so that's an option too generally in excel there are a few different ways to do things which is great find the one that's most comfortable for you the one that makes sense you don't need to use or know all of the ways to do things but just know that there are a few different options out there so i've created my new column and i'm going to call this bsk race and ethnicity and always important to name your columns because sometimes you come back to this worksheet like six months later i'm like what why did i do this i don't understand what's going on so i recommend highly recommend labeling things um and we want to be able to create a list a selection list where we can choose from the options that we saw in our drop down tab all right and so you can actually click on the cells where you'd like to um actually yeah click on the cell where you'd like to add your drop down list so this is where i'd like my drop down list to appear and we're going to go up here to our ribbons up at the top or our menus come over to the data option here the data ribbon and you can see that we have several options here one of them kind of more over to the right hand side is this data validation option and as i hover my mouse over the data validation option it says pick from a list of rules to limit the type of data that can be entered in a cell so that's exactly what i want to do i want to put some limits on what kind of data can go into this cell i'm going to click that option and i get a dialog box that asks me to set the validation criteria and right now this cell is allowing any value um that's i can type in anything i want i can type in a formula a number a date anything and it'll allow it but i actually want to limit this for my drop down list so i have several options here i could limit this only to whole numbers to decimals to dates to times and those might be interesting or good options for you depending if you're doing another type of data entry but right now i'm going to limit this to a list to only allow a list i'm going to ignore blanks and i want it to show an in-cell drop down that's great and that's now it's asking me for the source okay where do you want me to get your list from and i've got this little um black arrow over here to the right hand side i think that as like a little trampoline and what it's going to allow me to do is go and select the list that i want to use so i'm going to click that trampoline and now i have a box that allows me to put in my data you can manually write this in i think the most easy is to just come over to the drop down or wherever my list is and select the options that i would like to uh use as my list and i'm only going to select the options that i want to use not the header at this point the header is just for my internal use my memory like these are the bsk race and ethnicity options and i right now only want to select those options so that's great looks like it's reading correctly i can see i have these um kind of green dotted lines going around my options so that means it's selected and it says drop downs uh a2 through a24 so that's great i'm going to come back to this arrow it's going to trampoline me back to my data validation box and looks like we're all set up and i'm just going to click ok and fantastic that's taken me back to my working tab and now all of a sudden you're seeing there is a little drop down box to the right of my cell and as i click that i can see now i'm able to visualize all the options uh for bsk race and ethnicity and so as i come through i can go through and manually select and i know that these are the options that i have i'm not going to spell anything incorrectly i'm not going to get some you know made up option um i have more confidence that i'm going to get the data that i want to have um so right now we've only set this up for our first uh cell in our our first row and what i can do is um there's a right right below the drop down box there's a little green uh little green dot and when i put my mouse on that green dot i get this dark black uh cross or x and i can use that to drag things down and that's actually going to copy my data as well so i don't want to do that i just want to copy my drop down box and you can see this is picking up again the same options that i have uh from my drop down list and the other thing that i can do is on that same green dot i can instead of dragging down i can double click and that will automatically fill it should automatically fill looks like you're not working well looks like we're going to have to drag things down for this one which also works i'm going to drag it all the way down to the very end of my data and i can see when i click on any of these cells now i do have that drop down list so we're set to go um and the uh another nice thing about the drop down list if you would rather not click into the options and manually select you can also write the option out so i can start writing hispanic latino or spanish origin if that's already been used in the column it's going to pop up for me automatically and for this person uh white is the option so i would type that in it's not automatically filling because i don't have that option chosen yet in this column but i can type it in and if i've typed it in correctly it will accept the answer it will accept that as an answer and it will allow me to input that text i can't type any random text into this box because i'll get an error message which says this value doesn't match the data validation restrictions defined for this cell so it says try again you did not do that right and i can go ahead and click retry and i can type in the correct option so it's just kind of a safety check when you're doing data entry especially from data entry from a set of options that are already pre-defined awesome and so i could go through and and code everyone ing to the vsk race and ethnicity options um we won't do that if you would like to practice that on your own go ahead and feel free awesome i don't know if there are any questions about the drop down dropdowns if you can feel again um how to make that drop down go into all of the cells in that column yeah yeah so that's a great question um what i would normally do is suggest doing this double click but that wasn't working for me so one option is to go to this bottom right hand corner we have this little green dot and once we put our mouse on the top of that green dot we're getting a black cross or x and you can select that and pull that same formatting down the other way to do it is just when you set things up on your data validation is to select pre-select all the all the um cells that you'd like the data validation to set up so when i set things up i only selected this first uh cell but i could also i'm gonna just create a new um new column to to be able to show this there we go usk race and ethnicity and i could go ahead oh it's actually pulled it excel is sometimes too smart for its own good it's actually pulled these um these drop down uh boxes this drop down list over because it's already set up in the column to the left but uh i'm gonna clear that out just for our example so i'm gonna select all of the the cells that's actually this sometimes it's like good that these things happen because this is what happens in real life so i've created a new column let's say i don't want to have these drop down lists how do i get rid of them so i select all the cells where i want to have them not have the drop down list right click and actually click clear contents and that's going to get rid of everything that's going on in these cells but has not gotten rid of the drop down list okay well that's probably a formatting issue there then um for the taking off the drop downs you can also go to you know data validation and then clear validation so if you um awesome and then data validation at the top let's see we are all there we go awesome okay the i also got one question um it's appearing as actually i'm gonna um if you don't mind sorry abby just gonna um go through that process one more time of how you would once we've cleared this out how you could come through and do things uh all at once yeah you can go ahead and select all of the cells that are in this column all the cells that i want to have that drop down list happen in so i've selected everything and now i would just go ahead and create that rule again so come back up into data validation and it's going to apply this to all of the cells that i have selected select list select where i want the list to come from which is our drop down options here pop back into our data validation options everything is set up and click ok so now we have set up this format for all of the rows in this column we're getting that little drop down list automatically thank you for that question that's great um go ahead sorry oh no um i actually think that was actually helpful to maybe answer this question um i got the screenshot of what stephanie is seeing and i'm thinking that what's happening because what you're seeing is just the equation the dropdowns equation in the cell itself so i'm thinking that that got entered instead of going through the process of selecting the cell and then going up to data validation and entering the source of that list up there maybe it was entered directly into the cell no no no all right well i'm not that's that would have been my assumption um unfortunately i don't know that it's that's odd i'm not sure why it's happening jen do you have any other theories um not right now but i think this could be a good time to take our five minute break and stephanie if you don't mind sticking around we can kind of troubleshoot and see what's happening there um so we can go ahead and take our five minute break uh feel free to step out for a few minutes and come back at uh 10 past and stephanie if you if you feel uh like you'd rather take a break then feel free to do that otherwise we can do a little troubleshooting so actually i just redid it and it worked so i don't know what i did before what i clicked on but now the drop down list came up so maybe sometimes sincerely that is the best that is that's my best uh troubleshooting and i t advice especially for my parents like just unplug it and plug it back in it'll work so awesome okay great well we'll see you all back here in in five then the participant guide we've got about halfway through the second activity so in the second half we'll be looking at conditional formatting and then we're going to be looking at formulas so i hope they were restful five minutes your brain is refreshed and you're ready to jump into conditional formatting all right so conditional formatting is just we everyone i'm sure is familiar with the different formatting options that excel gives us we have on our home tab we have we can change the the font we can make it bold change the background color change the font color center things there's all sorts of different options here to kind of highlight things or play around with formatting and what conditional formatting does is it allows us to set that format up based on a rule so based on any given criteria that we'd like to use we can say uh when this happens when this given criteria happens automatically change the format of this cell to show things a little differently and this is going to allow us to quickly see data trends we're going to be able to highlight certain things that are happening in our workbook there's a ton of different options and this is sky's limit as far as creativity pretty much so i'm just going to show a few common options that i find really helpful the first one is highlight cells and this can be really useful when we want to you know really make things pop or be able to quickly visualize certain information based on a certain criteria so what i'm going to do is just show a quick example here with with the number of dependents and this column is way over on the right hand side of our of our workbook in column s the number of dependents so i'm going to go ahead and select this column select the the dates actually i'm going to select this whole column i want to make sure that if i add any data in the future i want to be able to make sure that this rule is all the way to the very bottom of the workbook so i'm going to select this column and up here on this home ribbon i have this option for conditional formatting and i'm going to go ahead and say it says when i hover over it says easily spot trends and patterns in your data using bars colors and icons to visually highlight important values so that's what i want to do i'm going to go ahead and click and i get a bunch of options here under conditional formatting i have the option to highlight cells set up rules for the top and bottom of my data data bars color scales so again icons sky's limit as far as creativity right now i want to just set up a simple highlight rule so let's say i have a new grant that's come in that's or a new program that's specifically going to reach families that have uh over four income dependents so let's just imagine that's that's the rule that i want to set up anytime i get a new client that has over four income dependents i want to be able to highlight that person so i can come in here to highlight cell rules and i since i want to set up when things are greater than four i'm going to click this greater than button and now i'm seeing uh this option to set up my criteria format cells that are greater than and i'm just going to say 4 and it says i can it gives me options of how i want this cell to format and right now it says do this light red fill with dark red text and i want to just be able to change that to a green option um i can have there are some preset options here at the bottom i can set up a custom format this green looks like it'll work for me i'm gonna click okay so any cell that's greater than four is now going to be highlighted in my workbook and i have also inadvertently highlighted this same this this top uh cell or column header and so in order to re-establish my previous formatting i can use my format painter and this is a really helpful tool as well because what it does is it allows me to click on a cell that i like i like that format this is what i want my header to be formatted as come up to again on the home ribbon to this option for the format painter and i can click that and i can see that the cell that i have selected is now showing up in green so this is the format that it's going to be painting and i can paint that i have a little paintbrush now next to my mouse i can paint that onto my column s header and we're back in business so things are looking good and i have my conditional formatting set up so that i only see uh folks with more than four income dependents um another fun thing that i didn't discover until recently is if we come back into our filters options i cannot actually sort by color so i can put those light green uh colors up at the top if i'd like so now i'm getting everyone who meets who meets that rule is up at the top or i can even filter by color so if i want to filter everybody else out that doesn't meet that criteria i'm now only showing people that have more than four income dependents so those are just some kind of um fun options uh that can allow you to highlight uh highlight different cells based on a certain criteria another tool that can be really useful is the duplicate finder um sometimes when we have multiple people doing data entry or you're coming back after a long period and you're entering things again we can easily have duplicates in our database or in our data set uh and so one thing we can do to avoid that is do a data check on based on individual id and i write i'm going to use individual id because that's the piece of data that we have in this uh data set that is i should be a unique id so i should only have one individual id per client they should only be showing up once so i'm going to click that column which is column d and again i'm going to come into the conditional formatting option and this is going to be a highlight rule so i want anyone who's a duplicate to be highlighted and a very bottom of my duplic or of my highlight rules i can see that there's an option to highlight duplicate values i'm going to click on that and this says format cells that contain duplicates with values duplicate values with light red fill so that's great it's gonna uh show up in this light red and i've clicked okay and so it should be applying things i don't see any duplicates up here at the top but as i scroll down okay i see that i have one child that has the same individual id number same birthday same service start start date so i know that for sure these are duplicates i always recommend just doing a visual check because sometimes you might have some data that's you know maybe there's a small difference and you want to be able to make sure that you're staying and only keeping the correct information so again we can do that same filter option here up at the top where we sort or filter by color i'm going to actually filter by color here and this is pulling out only my duplicates um and then just it looks like i have three sets of duplicates a child a father and a mother and i'm just gonna kind of scroll over here looks like everything else is lining up so this is a true duplicate there's everything is is the same so i'm going to go ahead and delete that duplicate because i don't want that person showing up twice in my data set i can delete the row by right clicking on the row number and once i do that i can see that that conditional formatting has disappeared because this person no longer meets my conditional formatting criteria so i'm going to go ahead and delete again right click on the row number and just select delete row and again we've cleaned up another duplicate and for our third one so we have no longer have duplicates in our data set at least not by the individual id which should be our our unique number i'm going to go ahead and show everyone so fill clear that filter okay so everyone is showing up again and we have done some a little more data cleaning so we have a higher data quality in our data set all right and another thing you might want to do to be able to visualize data quickly with this conditional formatting option are what we call data bars and this is something that i would recommend using on a numeric field because it's going to allow us to quickly see visualize those numbers so as you can see right now i have several different numbers in um in my data set obviously i know that 2800 is less than 10 thousand i have a fourteen thousand i have a zero there's all sorts of numbers but i don't have a quick uh visual sense of what's going on with this data so i'm gonna go ahead and click on this average monthly income total column and again we're going to apply conditional formatting up here at the top and we have this third option here which is called data bars and what this is going to do is give me a visual based on the range of the values that are in this column it's going to show a different length of data bar and as i hover over these i can kind of see a preview over here in my column so this one's a gradient fill there's some different color options going on i'm gonna stick with this let's see uh let's do this blue option this blue data bar okay so great we have kind of a quick view and as you can see uh anyone that has zero isn't showing up with they have zero in their data bar and my max value is going to show out up at the top and it looks like people are pretty well organized by income if i wanted to i could also sort this by income from smallest to largest let's say and now i have just a quick view of uh total average total income and i'm able to visualize what's going on with these numbers um but it looks like i don't know i'm not really happy with how this has turned out actually because this dark blue color is kind of hard to read when it overlaps with the number and i want to make sure that we're able to read the number so let's say i want to come back and edit this conditional formatting that i've set up and i can do that by coming up to the conditional formatting option up at the top again and at the very bottom there is an option that says manage rules and i'm going to click on that and this is allowing me to see the rules that are set up and right now it's showing the formatting rules for my current selection and if i want to see the rules for the entire worksheet i can do that as well by clicking entire worksheet and now i can see okay i have my data bar rule set up i have the duplicate value set up and i have my cell value highlight rule set up um and let's say with this data bar i want to edit it so i'm going to select it's showing up in blue now and i can now edit this rule and i wasn't really happy with how this dark blue turned out so let's say i just want to be able to change the color and there's all sorts of options here to be able to change this rule but right now i just want to change the color so i'm going to come back and select this kind of light blue color i think that's going to be easier to read for my data bars great and it still hasn't changed if i select apply it'll go ahead and make my changes and that looks that looks good i like that so um i'm going to click ok and as i scroll down i can now read these values a little easier and it looks like there's a question here in the chat about when you would use data bars i would suggest using these when you have a numeric value you could use these for the number of income dependents you could use this for visit completion um that's a that's kind of could be a fun one here so i'm just going to select that come back into conditional formatting set up a data bar and let's do this kind of green so i can now see just really quickly have a visual check and it looks like i have some people that have more than 100 percent of visitation completed so some overachievers here um but this just allows me to see of all of the values that are in my data set which ones are at the high end which ones are at a midpoint and which ones are at the low end yeah um i said also asked is it like a form of data visualization and i know you know when i'm completing reporting sometimes you know you wouldn't necessarily use this just to get it to spit out the numbers that you need for reporting or also there's other times when you know you build pivot tables which we'll get into next week but build pivot tables and graphs out of your data and again maybe then this wouldn't be um the tool that you would use i see it as a tool in the places where i have used it is when i'm actually trying to get out of a data set just like the example gen showing like if i wanted to just quickly see what's going on in my data where i've used it is in opportunities where people are actually looking at the data set to see sort of trends or um to pick out yeah what's happening with the data quickly from the your your source so if you're creating another dashboard with graphs off of that and you're not ever going to come back to your original data if you're just trying to clean the data to then pull it into other forms you wouldn't necessarily use it but i've worked for organizations where they like get this data set and then push print and then have just lines and lines and lines of data and there's actually a lot of things like this that you can do to kind of combine your raw data with some sort of visualization so i've used it for like a board who wants to see what's happening with this program and wants to see the raw data but right so yeah i'm sure you all maybe can think of things where you're looking at your raw data set and this can just make it a bit easier to sift through if you're wanting to glean something from the from the data set it thought yeah i think it could be really helpful for monitoring as well um monitoring completion like because visit completion example i can see like who is uh has their visits completed who's at the midway and and do some um just some monitoring with that as well that's a great question sarah i don't know if there are any other questions about conditional formatting and again this is one of those it's like one of those rabbit holes where literally you can play around and set up rules based on whatever is going to be useful to you um there's a bunch of different options here so i'd encourage you to explore and explore things awesome well if any other questions come up further down the road just let us know but we can go ahead and move on here to uh the section on formulas and there's a lot of times when we get the data that we need from a database but there are other times when we need to be able to add in information based on a calculation and that's what formulas are really useful for there's a ton of different formulas out there and by no means do you need to know all of them or memorize any of them what i suggest when you're looking for a formula when you when you know that you need to have a calculation or a process happen in your database is to use google um if you can just describe what you like done there is someone out there in this world that has had the same problem and has wanted to do the same thing that you're doing so just kind of describe what you'd like to do and get excel somewhere in there in your in your search terms and i'm sure someone will be able to give you at least kind of an idea of a formula that could be helpful for what you'd like to do the other option for finding formulas is just to start typing so excel is going to understand if you put an equal sign which is how all of our formulas start if you start typing an equal sign that you'd like to start typing a formula and so you can actually if you have an idea of what your formula is called let's say i want to find an average i can just start kind of typing in the a okay and i'm getting this whole list it's got a ton but let's just keep typing average and yep um these might these options might be a little hard to see but it's giving me suggestions of formulas that i might want to use um and this average one says uh there's a description as well returns the average arithmetic mean of its arguments which can be numbers or names arrays or references that contain numbers so that's great that's exactly what i want to use so i'm just going to finish typing out you can type it in all caps you don't have to it's going to pick things up either way and we always want to start our formula with parentheses i'm actually going to not get not get into how to write out average but just as a way for searching for formulas that's one way that you can do it another way to do it is up here at the top of our ribbons our menu options so when we come over into the data ribbon i'm sorry the formula is ribbon that's where we're going to find the formulas so we have this insert function option we have a bunch of different options for different formulas that we might want to use it even has a recently used formulas options so those might be your your most common used ones there's ones that use text that use data and or date and time sorry there's trig functions if anyone wanted to revisit high school trig that would be a fun time but um these these first ones are probably the ones that you're going to use most and the other thing we can do is this um oh actually before jumping into this i also wanted to show the uh in the excel workbook in that big uh thick guide um we have uh our top favorite 15 excel function formulas um these are things that we find really useful for the work that we do as the capacity collective and can also be helpful i believe for for a lot of you so um sum average finding the maximum finding minimum there's a description here in this workbook and also some examples of when you would want to use that so take a look at these and this can be a good place to start if you want to check out some different formulas awesome all right and again like we said we always want to start our formula with an equal sign um followed by the formula name and then we're going to start with our parentheses that's how we're going to start writing things out all right and if we want to do um what i would like to show now is the the if function or the if formula this is really helpful especially when we have a criteria that we want to set up a statement that we need to say is the statement true or false and then have excel return a value based on whether one value if it's true if that statement is true and have it return a different value if that statement is false and we can go ahead and on this formulas ribbon bar we can do this insert function option up at the top and corner and let's see if i'm looks like yeah this is where i'm placed as at the cell so this is where my formula is going to end up um but for this example actually we want to create a new column so that's going to come over here to individual language um let's imagine that i want to be able to add a column um here right to the left of individual language i'm going to add that column and i want to be able to add a column that tells me whether or not an interpreter is needed for this client so i'm just going to go ahead and name this column interpreter needed okay and i could come through and manually just enter in all this information but since i know that my rule is going to be if their individual language is english then they don't need an interpreter if it's not english then they will need an interpreter so i have a clear criteria that things are set up on so i can actually set up a formula to do all this data entry for me and not have to think too hard about things okay so i'm here in my interpreter needed my first cell here and now we're going to go ahead and insert that function up at the top left hand corner under our formulas ribbon click insert function and i want to do an if formula i know that's the type of formula i'd like to use so i can go ahead and search for it go ahead and go on the search option and it's giving me a bunch of different if options anytime it shows up but i want to just use the plain old if function and there's a description down here at the bottom it says checks whether condition is met and returns one value of true and another value of false so yes that's exactly what i want to do here i'm going to click ok and now excel is prompting me to kind of set up the arguments or the different parts of this function and the if function has three arguments or three parts the first one is the logical test and that's going to be my rule or my criteria what the condition that i want to have met and then it's going to have a value if true so if that condition is met this is what's going to spit out and if that value is not true or if it's false this is what's going to happen so i need to tell excel what my condition is to get started and i'm going to go ahead and select i want to base it off of the individual language so i'm going to select that cell and you can see that it's brought that cell name into this logical test so i could you can also write it out i find it easier to just select but this is the m2 cell and so that's what it's picking up m2 and i want to say if m2 is equal to the word english that's my criteria so if it's equal to the word english and when i want to use a word or a string text i always want to put it in quotes so i'm going to start the quotes and i'm going to write the word english that's great all right and what's this this is saying is like actually in this case m2 says spanish so this criteria has not been met this is actually going to return a false value this is kind of giving me a preview of what's going to happen and now i want to be able to write the value of true so this is the value that's returned if this logical test is true and what i want it to say is i could just leave it as a blank but i actually want to leave it as kind of a dash and that's just going to let me know this does not apply so i'm going to put that dash in between in between quotation marks as well so just give me kind of a it's almost going to look like a blank for me um when i read my data but it's just a little dash in between quotation marks and then if that value is false so if this individual language is not english then i want to be able to say yes an interpreter is needed so i'm just going to type in my quotation marks and start typing in the response that i want to spit out yes interpreter need it so and i close my quotation marks and that lets it no that's the value that i would like um so it's done this kind of example for me here on the side says okay actually m2 is doesn't say english so this value is going to show as false and it's going to show as yes interpretive needed so it's showing down here again too the formulas formula results yes interpreter is needed so i'm going to click ok and looks like this is a little long for me so i'm just going to widen my my column and we have we have our formula that's set up and what you're actually seeing as the result of the form is the text that's the result of this formula yes interpreter needed you don't actually see in the cell what's all happening behind that value what's going on with the formula if you want to see that formula you can come up here to the formula