[MUSIC] So you have data imported into your sheet, and you're ready to analyze it. Now, we're going to walk through some of the most popular data analysis features and functions of Sheets. We're going to use our trusty movie data set to show some of these off. One of my favorite things to do in Sheets is sort data. It really helps me see quite quickly what some top line trends might be. For example, we can sort data to list things from the smallest data point to the largest, or vice versa. Let's sort our movies by Release Date. If we want to see the earliest movie first, sort A to Z. If we want to see the most recent movie releases, sort Z to A. Click on Data. See here, you could also select and sort a specific range if you wanted to get more granular. All right, select the column that you want to sort. So in our case, let's select the Release Date column. Choosing to sort A to Z sorts the earliest releases on top, and choosing to sort Z to A sorts the most recent releases on top. See, perfect. We want to sort by most recent release for now, so we'll leave it like that. Okay, using filters and filter views in your spreadsheet. These are different filtering mechanisms provided in Sheets. They're really powerful under different circumstances. Let's go over basic filters. These are really useful when you're working collaboratively with a peer in a spreadsheet at the same time. When you apply a basic filter, that applies to every viewer of the spreadsheet. When you filter and when you sort, that view persists. That means that that will be the view for any future visitors to your spreadsheet. Let's imagine, however, that you're working in parallel with someone. Maybe you and your colleagues have different insights you're trying to uncover at the same time, and you don't want to disrupt each other. So filter views only affect you as the viewer. This is such a powerful tool to have when you don't have editing permissions on someone else's sheet, but you want to be able to sort the data. You can also save these filter views, name them, and share them with your colleagues. So that's what filters and filter views are, but let's dive a bit deeper and talk about how to filter things with and without conditional formatting. So filters are useful if you want everyone viewing your spreadsheet to see a specific filter when they open it. We can also create filters that exclude some data on our sheet while highlighting other information. Filters are seen by everyone viewing your spreadsheet. So let's make a filter that shows us only movies from 2016. Duplicate the Movie Data sheet. Let's rename this to Genre Action 2016. Let's qualify our first condition here. We only want to see movies from the year 2016. Click up here on Data. We'll select Create a filter. Select this column's arrow here, and we'll select Filter by values. Select Clear. We just want to see movies released in 2016, and hit OK. Now, let's qualify our second condition, the action genre from the year 2016. Okay, so select the genre column arrow, select the genre we want. Hit OK. Filter views can be useful if you want to save multiple views or you want others to be able to view the data differently. Since filter views need to be turned on by each person viewing a spreadsheet, each person can view a different filter view at the same time. You can create a filter view, save it, and share it with your team. This is super helpful when someone else in your team wants to narrow down and observe the data set the same way you did. To create a filter view, navigate up here, open Data. Filter views, and Create new filter view. Click here on the filter menu on the Release Date column. We'll select Filter by values. Let's clear the suggested values and enter 2016. Then select Select all, hit OK, and now your team can access the filter view in their data menus. Now let's name this new filter view. This becomes a private filter we can access in the future or share with others. Sometimes when you add data to a sheet, it may not be in the format that you need. Now, you can easily keep your sheet standardized by using the trim white space feature to remove any leading, trailing or excessive white space from a cell or a range of data. So let's get rid of the additional white space on some columns. All right, select the columns. Open Data. Trim white space. Hit OK. There we go. You can use the Remove duplicates tool to easily remove duplicate values from your sheet, saving you time by eliminating the need to write an app script or manually remove duplicate data. You can customize the selection and pick certain columns or select the entire table. To remove duplicates, go to your Data menu, and select Remove duplicates. Hit confirm, and there we are, no more duplicates. One way to save time in Sheets is to create a data validation rule. For example, this is useful if you know you want your cell to contain only a few options. Let's say you were creating a survey, and you only wanted the cell to contain a yes or a no answer. We could create a drop-down menu that offered only those choices. Then you wouldn't have to type out yes or no each time, you'd simply click an option. Data validation is a way you can restrict the values existing an a cell. Let's create a column called, Have I seen this? Open your Data menu and select Data validation. Let's specify which column you'll want to add a data validation so that the only options we see are either Yes or No. So over here in Criteria, select List of items, and type in Yes, No. Hit Save. Cool, now we have these drop-downs in our cells with two possible values, Yes and No, cool. You can also copy and paste cells, columns, and rows to which you've added data validation and paste it in other parts of your sheet to carry over the same format. You can clear data validation in specific cells or ranges in your sheet where it doesn't make sense to have a data validation. For example, we don't want a data validation on our header. Right-click the cell and open Data validation. Here you'll see an option to remove. [MUSIC] All right, formulas and functions are up next.
