Speaker 1: over how to set up a spreadsheet. For this example, I'm going to say we want to look at some trends with inspections and we can assess risk and workflow all in one spreadsheet. So in this first sheet, we have some facility information. So I put in the facility name, the facility type, the assigned inspector, and I'll talk about some of these other columns in a little bit. So on any spreadsheet, you want to make sure you use these sheet tabs to name. So you never want to put your name of your spreadsheet in the first row. This is a common problem that I see. This will really limit your capability when trying to analyze and sort your data. So keep your sheet names down here and you can do that by clicking on it and you can rename it here. So then you want to enter your column names and info and you're going to want to always format this as a table. This will really help when you go to analyze or sort or filter your data. So you can do that by highlighting whatever information you want to include and you're going to hit format as a table and you can kind of pick your setup here. I have mine set up like this. Once you do format as a table, you're going to see these arrows and that's going to allow you to do a lot of functions like sort and filter and do some analyses later on. So I highly recommend it always formatting your information as a table. The other thing I want to touch on is how to do some drop downs. So the reason that this is really important is because when you're entering data it could be multiple people or you could make mistakes. So you might have one person enter something as blue and then the next person enters it as BLU. And then obviously these would be categorized as two separate categories. So in order to standardize and limit any kind of data entry error, we want to drop down. So this allows people only to enter in whatever you have put in as your drop down. So in order to add a column as a drop down, you're going to highlight that column. You're going to go to data tab and then you're going to go to data validation and click this data validation. So this allows you if you only want numbers inserted in that column, you can put only a whole number, only a decimal. You could make it only people can enter a date here. For this I like to do list. This is my most common one. And then each variable that you want is your drop down. You can enter here separated by a comma and then you're going to hit OK. So then those occur as this drop downs. If I try to type in anything else, it's going to give me an error message. So it does not allow you to type in any kind of errors. So those are really helpful also when it comes to sorting and just data entry. Then I want to go over some conditional formatting so you can do a lot of things with this. So if I want to go to, you're going to go to your home tab and I want to quickly see what facilities have a really high number of citations or what are our higher facilities. So I'm going to highlight this and I'm going to insert some conditional formatting so you can enter rules so you can do a lot of different things with this. If you want all of your high risk facilities, facility risk level four to be in red, you could create a new rule and it will have you type in four equals and then you'll select the color red. For this one, what I'm going to do is I'm going to do color scales. So I want to highlight the facilities that have a high average number of citations. So immediately my eyes are drawn to the ones that are in red or orange. Those are the ones that we're seeing a higher number of citations for. And there's again a lot of different rules that you can establish here, but that's just one thing that you can do with this. The next one I want to cover is your findings timeline. So for this one, I have our facilities and with each inspection I put in how many citations were found for that inspection. So instead of creating a chart for each facility, so if you kind of want to trend how things are going across time, you can actually insert a trend line right into your chart at all of them at one time. So you're going to do, you're going to highlight the data that you want and you're going to insert a line and I'm going to select where I want that line inserted and it's going to give you a trend line right here. So you're going to see that for Crypto Creek, we're having an increasing number of citations over time. So I actually already did this for our facilities. What you can do is you can just drag and drop down, but what I did was, I already did them right here. So again for Crypto Creek, you can see on increasing, you can also see that maybe over certain times of the year you have a spike in citations. Maybe there was new management that took over at a time period and when that new management took over, you started to see an increase in citations or maybe the inspector changed. Maybe different inspectors are more likely to be more critical and they give more citations out. So you'll see, oh yeah, we changed inspectors at this time period and we started seeing an increase in citations when we switched inspectors. So this just gives you a quick look to look at what your trend is for that facility. All right, so then I want to go to the summary tab. So this can quickly give you summary data for the data that you have entered in real time. So as you add data onto your facility page, this will automatically analyze your data so you don't have to add anything. So what I want to do is, I want to use this equation COUNTIF. So for this one, I wanted to see the number of facilities that were assigned to Alexis. So I put in my thing equals COUNTIF and I'm going to click on the facility page. So I'm actually going to do this one over again for you. So I'm going to click COUNT IF. I'm going to go to my facility page and I'm going to click the assigned inspector and I put what my criteria is. So I want this, I want to count if this includes Stevie. So I actually do quotations, Stevie, quotation, and close. And it's going to tell me that four facilities are assigned to Stevie. You can also do multiple criteria at once. So if I want to look at how many facilities does Alexis have that are level two facilities. So I can do COUNT and then you're going to do IFS. So this is multiple criteria. I'm going to go back to my facility info page and I'm going to COUNT IF this criteria is Alexis, separated by comma, and then you're going to put your second criteria. So I also want if the risk level comma criteria is two and enter. So Alexis has three level two facilities. So this can really help you with your workflow. As you can see, Twyla not only has a lot of facilities, but she also has a lot of level four facilities. Meaning you might want to reassign or relook at how you assign out your facilities. Some other things I want to look at or we can go on to here and there's this fun little analyze data tab. So if you click on this, it's actually going to give you some suggestions of how to analyze your data to give you some other ways that you can look at it. So you can insert graphs. You can look at it looks like a facility type pool has noticeably higher average number of citations. It can give you line graphs. There's a bunch of things that you can look over the frequency of complaints, so that's just fun and it will help you if you really want to get brainstorming about how to look at your data a little differently. Another thing, just a quick tip and trick, is if one of your columns is a smaller width than the rest and you quickly just want to make all your columns the same size, you're going to highlight all of your columns and adjust them to the size you want and it'll automatically equate out your column widths. Another fun thing you can do is if I want to, if I'm looking at geography data, so I'm going to put in Delaware, Ohio, and then if you go to the data tab, there's these data types and you actually can insert in different data types from Excel Online, but you should have the basic four. So if you go to this geography tab, it's going to automatically, and you can show a card. So once I put in Delaware, Ohio, it's going to give me it's Delaware County, it's located in the United States, here's our mayor, here's our population. So you can get it, give a feel for what kind of area this is. You can also download poverty levels, you can do a census. There are a lot of different things within this geography tab that you can do. And the last thing I want to cover with some spreadsheets is how to filter and sort and what is the difference. So with this, let's say I'm going to highlight all my data and I want to sort my data. I want to sort it by facility type. So now I can see all, and this will auto, if you, as long as you have all of your data highlighted, it's all going to sort. So you're not going to have your different rows end up mixing the data. However, if I did sort this way, that would work too. So the other thing I want to do is how to filter your data. And if you see this little arrow, that means it's sorted by facility name. So I'm actually going to clear my sorts, my filters here by going up in here and clear. Okay, so now I want to filter my data, which is a little bit different. So I'm going to filter, and once it's filtered, there's these little things on here. And let's say I just want to look at, I just want to look at my tattoo facilities. So filtering essentially will hide your rows, so you can just look at your tattoo facilities. You can't do any kind of, any kind of analyses this way though, because if you sum it and you go like this, you can tell three plus one is not 42. It's still summing all of your hidden cells here. So just be careful when you are filtering. And this will just give you a quick summary. And then if you want to clear your filters, you're going to just clear your filter and it'll show all your data again.