Learn to Master Google Sheets | Tahir Yaqub | Skillshare

Learn to Master Google Sheets

Tahir Yaqub, I Teach Online

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
18 Lessons (3h 26m)
    • 1. Class Introduction

      2:58
    • 2. Google Sheet User Interface A Complete Tour for Beginners

      7:57
    • 3. Two Important Settings to Avoid Weird Date Formatting Issues

      2:32
    • 4. 8 Important Date Functions

      7:36
    • 5. Named Ranges

      7:01
    • 6. Data Validation and Drop Down List in Google Sheets

      10:04
    • 7. Google Sheet Features You Must Know

      12:14
    • 8. Basic Function and IF statement

      18:05
    • 9. Rank AND and Nesting If and AND

      12:39
    • 10. Creating Charts in Google Sheets

      15:46
    • 11. Using Filters with dates

      4:17
    • 12. Filters and Filter Views in Google Sheets

      11:00
    • 13. Team Collaboration Methods Available in Google Sheets

      12:20
    • 14. Conditional Summing and Counting

      17:23
    • 15. Pivot Tables Explained

      18:34
    • 16. Methods of Inserting Images from Google drive into Google Sheets

      12:27
    • 17. Understanding The Most Powerful Query Function in Google Sheets

      20:37
    • 18. Index and Match Functions and their Combination

      12:50

About This Class

Learn to Master Google Sheets

This course is designed  for intermediate users of Google Spreadsheets who want to learn more about the features, functions and formulas available in Google sheets. Google sheets has applications in all fields and learning Google sheets is almost essential today. Google sheets offers many advantages over other spreadsheet programs such as Microsoft Excel and Open Office. These advantages are critical for small businesses and cannot be ignored. Therefore for most businesses and companies, Google spreadsheets offers a superior solution for their spreadsheet tasks and data analysis problems.

Objective of this Course:

To bring your skills to proficient level where you will be able to use google sheets like a competent professional.

This course has been divided into three sections,  and if you know the basics, you should skip the first section to save some time.

S E C T I O N - I: Beginner

  1. Tour of the User interface of Google Sheets
  2. Two Important Settings to avoid weird date formatting issues
  3. Named Ranges
  4. 8 Important Date Functions
  5. Data Validation & Dropdown lists
  6. 5 Google sheet features you should know
  7. Basic Functions & IF statement

  

S E C T I O N - II Intermediate

  1. RANK, AND and Nesting of logical operators
  2. Creating Charts in Google Sheets
  3. Using Filters with dates
  4. Filters & Filter Views
  5. Team Collaboration methods available in Google Sheets

S E C T I O N - III Advanced

  1. Conditional Summing & Counting
  2. Pivot Tables
  3. Methods of Inserting Images
  4. Understanding the most powerful QUERY function




Class Activities:

  1. Download the lecture/exercise files 
  2. Follow the instruction given at the end of video lectures

F U T U R E     P L A N S: 

  1. More activities will be added.
  2. More videos will be added based on student’s feedback. Feel free to put your question in the appropriate space and I will answer if possible.

Transcripts

1. Class Introduction: Hello and welcome to my course. My name is started, and in this video I will introduce to you my latest course on Google spreadsheets. User Booger spreadsheet is growing fast due to some of the advantages Google ships offer over other spreadsheet programs, such as the ability to share and collaborate with your team members. Four minutes. Storage of your fires on Google, Cloud revision history and ability to use your spreadsheets from anywhere and using any device. If you're losing highly complex spreadsheets and integrations with using low level programming languages, then still Microsoft Excel could be a better alternative for you. But for most businesses, the benefits offered by Google shoots cannot be ignored and overlooked. And therefore, Google shares offer a superior solution for their spreadsheet tasks. The objective of this course is to bring your skills to a level where you become proficient and start using Google. She's as a competent professional. I have divided this course in three sections. Beginners, intermediate and advanced. And if you know the basics of Google shares, you don't have to watch the videos included in the bigness section. You should skip those videos and save some time in my opinion. This course is best suited for intermediate users who want to let hope to use powerful Gougar ship functions and formulas in various data analysis situations. In this course, you will learn Google shoes, features and functions with some examples. In my videos, I start with an example and by asking some questions, and then I explain how we can use Google. She's functions toe, get our answers here. I would like to give you a little advice. You will notice that in most of my videos I start with an introductory slide where I explained what I am going through. Explain in this video and what you have decided that you are going to wash this week ago. Please watch full video because there are a few little things which I cannot put in. The title of the video are even in the description of the video. Yet those things are still important, and if you don't know that those things have been covered in the course, then you might be wasting a lot of time later on when you need something, I would encourage you to look at the video titles off my course. There are nearly 20 videos. At this stage, there are some three preview videos are available. Please have a look and see my teaching style. Whether it's source, you are not before enrolling in the course. So feel free to join my class and become faster. Your company who is a master of Google. She's thanks for your time and I will see you inside the class. 2. Google Sheet User Interface A Complete Tour for Beginners: let us have a quick tour of booger shares. This lecture is only for beginners. So if you are comfortable using the basic functions of Google shares, then you can skip this video and you should skip this video In this lecture, we will be looking at the user interface and we're things are located. When you open any Google shit fight, you will be presented with this console on the left top corner. You see this green icon and if you click on that, you will be sent back to your Google drive. This is my Google drive and this is the sheet on which I am working currently. So I click this again and I am back to my Google ship. The title of my fight are the file name is here. So if I click on this top left corner, I can rename this to any other name. Next to it is a star. If I start this file, then I will be ableto quickly access this fight by clicking the start. I call on my Google drive. There is a start icon on every Google drive. Next to it is the folder I cant And if I click on this, I can move this file toe any off other folders on my Google drive. So if I select this folder, this move icon will be highlighted and I can't move this file to any four Derby wherever I want. Then we have these menus. And before we talk about these menus, I want to show you a few more things for the beginners. In any spreadsheet, columns are named with a BCD, and after that we have combinations off two letters, for example, a B and so on Rose, our number one putri and every cell has a location address. For example. This word bids has an address off a one. So first we for the column name and then the rule number. Similarly, if I click here so this cell has an address off C nine sees the column. Nine is the road and sword. And if I select more than one cells by clicking on the first cell and then dragging upto the last I can select like this So I have selected what is card arrange? So this is a single column range. I can also select the range spanning the multiple columns for example, I can like something like this. So this is also a range When there are more than one says we call it arrange, arrange also has an address toe the way we addressed this range in our formulas is that whatever is on the top left corner, whatever cell is there. So this really see 12 and then right bottom cell is this one which is e 18. So this range address will be C 12 Poland e eating. So we put a colon in between the top left cell and the right bottom said we can also give this range on name and that we will discuss in another video. This particular spreadsheet also has a name at the bottom here she'd want if I click on this little arrow and then rename I can rename this Cheeto any suitable name such as this one. I can also add another sheet by clicking on this. Plus, if I click on this plus sign, another sheet will be added and I can rename it as well. In order to delete a ship again, click on this little arrow and, like, delete and then Okay, so these were some of the basic functions about sheet. And now let's talk about menus. So in the file menu, you have tools related to your fights. For example, creating a new file moving this fire to any border are moving this toe trash our spreadsheet settings So we will discuss these in other videos. In edit menu, we have some editing functions like Undo redo, cut copy paste, these kind of things in view menu. We have functions like freezing rose Grid lines are protecting ranges. These type off features are available in view. Menu in insert. We can add rose in between any rules we can add columns. Beginning surcharge image drawings are Google forms, jet boxes and formulas. These kind of things in format. We have features toe customize the appearance after kit. We can do board italic underline strike through allying, text wrapping. All these functions are in former it and a very important conditional formatting, which we will discuss in details literal in data, we have data related functions such as sorting, creating a filter slicer. As you can see that this is a new feature has been added by Google people tables, data validation, very important, named ranges As we mentioned, we can also name a range and these type of things. We can also protect sheet and ranges and these type of things. Then there is tools menu where we can kill year to ah form recorder macro spell checking and we can set notification rules. In Erdogan's menu, we can see what it owns are available by Google, for example. I have added this aired own. I can start this aired own here and whatever extra functionality this aired own forwards to me. I can use that I can get another I don't or I can manage my heirlooms Then help menu. You all are familiar. You can get some help about any particular I think in Google shoots. Then we have this link where we can control the version of this sheet on. This is a very powerful feature available. We will discuss this in a separate video. Then we have the stool bar and all the icons on this toolbar. They are the quick way toe perform some common tasks in our booger sheet. So all these tools are available in these menus. For example, in this edit menu, we have undue in the stool, but we have undo redo and print and these kind of things. So this toolbar provides us quick access toe most commonly used tools in Google ships. We can change the decimal are the precision. For example, if I select this column and then I increase this Cristian, as you can see that the number of zeros are increasing. All right and weeklies number of zeros upto, which I want the accuracy. We can change the feel color off any sell by clicking here and then selecting a collapse. We can change the text color by clicking on this A and then selecting any text color. If we select two cells, then this martial option will be available, and we can quickly much cells. If we select a column, we can center line. This column are We can left the line. Similarly, we can also are just were picker. Vertical alignment, X trapping. If the text is longer, it will be wrapped and the cell height will increase automatically. Then we can insert links. We can insert a comment, and this is very powerful feature available in Google ships. We can collaborate with our team members. We can insert a charge we can add a filter are a formula or a function. Then we have these three functions here. This is our profile. By clicking on this share button, we can share this with the other people by putting their emails and we will discuss this in detail. And this is ah, comment, history. We can add comment. We can set the settings for notifications this completes ever wore off. Google shares user interface and no, we will discuss the important features functions in details in our next videos. 3. Two Important Settings to Avoid Weird Date Formatting Issues: So when you start working with dates in your Google ships, sometimes you encounter some veered problems with formatting such as you try to change the format from default, which is a month. Be an ear. Do your own date for May. Like in Australia, we used day, month and year. So if you are finding some weird things happening to your digs, you are getting some errors. You try to change the format and it goes back toe there. D four format, which is American for Mitt. So if these kind of problems are happening in your Google ships, then what you need to do is instead of going into this, form it and then numbers and then toe date and time for months and struggling with these four minutes, which go back to their default and will not work until you have done a very important change in your spreadsheet settings. And this is what I'm going to show you in this video. What you should do is you should click on your fight and spreadsheets settings and you change local tow your own country because, you know, still, yeah, the former is different. Therefore, I need to change this to Australia first, and then I will not find any formatting issues with my debts. So this is a very important setting. You should keep in mind that whenever to get problem with their former T, you just change your location. Another setting, which is sometimes important, is that you click on your Google drive and then in this gear icon and then settings, and you change the language settings to the language of your own country. I look Australia is also English speaking country, but it is good if you change this to Australia. So if you do these two changes, you change your location in your spreadsheets settings and you change your default language in your Google drive setting. Then you will not find any formatting issues with your debt. Otherwise, changing the format only from this form eight and then this date and time for Mitt options will not work. Sometimes it will work. Sometimes it will not 4. 8 Important Date Functions: in this video, we will learn some of the most important date functions, and I will be showing you the most commonly used options. And if you know these options, you can almost do everything. In Google ships. However, there are still some more options available, and for those you can concern Google documentation. So the first function is minus function, and this function is used to find the number of days between two decks, and this function takes to our rumors. Date one and day two. If that one is a later date and Day two is an earlier date, you will get a positive number and you will get a negative number if date one is earlier than there to the second function is dated if function and this person used to find the number off. Whole days are months between two days and dysfunction only accepts earlier date as a first argument and later date as a second are woman. If you are trying to find the number of whole days, you really used de in quotation marks as the tired argument, and if you are looking for whole months, you will use em in double quotation marks as a hard R woman, and I will demonstrate this in a minute. No, the next function is network days, and this function is used to find the number of working days between two dates. This will return you a positive or a negative number representing the working days, so it will count only Monday to Friday. You can use this to exclude holidays as well. The next function is big day, and dysfunction gives you the day of the week like Sunday, Monday or Tuesday, but not in the farm off words, but in the form off a number this function. They explored women's date and tight, and there are three types of reliever 12 or three. If you use type one and, for example, the date you put on their date, it was Friday. Then you will get number six if you use Type two and you are looking for the big day offer date on which it was Friday, you will get fight, and if you use type three Monday will start from zero on Friday will be four. So this is a somebody of this function. Diet argument is optional. If you only put date. Then type one will be deformed. No, if you want to know the weekday in the form off three letter word like W e D for Wednesday are fr I for Friday. Then you can use text function on top of big D function. So you put big day function as the first argument of text function and the second argument of the text function. You have to put three DS in double quotation marks and we will practice this in a moment and you will get that day of the week in the form off. Three later work. No, these are the five most important date functions and there are three more functions which we used to extract day, month or year. And I will be demonstrating those three and this fight on Google Shit now. So this is a Google shit in which we have record off top 20 days when most students were absent in a glass during and year. So these are some dates and we would be using this data to perform some filtering operations in the next video. But in this video, I am showing you ate important date functions. So the first function is if you want to extract a month from the date, so then you simply type is equal toe month and then give the reference off a cell containing the debt and this will extract the month from this day. Similarly, you have the functions for a year and you also have a function for the because sometimes it is important for us toe extract these values and you can then apply this to all of your tears if you want, no less practice the five functions which we have learned so fast. We will be using the minus function and is equal to minus and then we have toe give the later date if you want a positive number, so we have to give the later date first. So a four and then the uglier date three. So this will return as the number of days in positive form. If you reverse this, you will get negative number. No, the next is number off. Whole days are months between two days, so this we will find losing the dated A function and dysfunction only accepts earlier day earlier date in the first argument and then later date in the second article and then we have group. But whether we want they are whole month between these two, no less practice this with month option. And what I do is I change this, tow em and let's put some further of a month like this 1 22 And as you can see, that this was eight on February. This what, 18 December. And it showed me 10 whole months. But if I used this eighth of February and fifth of December, which is a 20 let's try this. As you can see, this is February. This is December, but because whole month has not completed this waas eighth of February and this is fifth of December. Therefore, we are still getting nine months. There are only nine whole months between this debt and distinct. Although if you look at the month only you will see two here and 12 here. So you should be careful when using these functions and you should understand how the function works. So the next is find the working based between two days and no, we will use net work biz function and we apply this and this and it will show you 10. But no, if I change this and one thing you should note here is that I'm putting earlier date here. But he is giving me a positive number. If I put a later date here, it will give me a negative number. So it works like minus function, but in the opposite sense. So this you should not know the next is finding a day off a week, as a number is. Equality Week did. And then we put this and I know that on 8 February it waas Friday and I'm getting six because I am not giving any type toe dysfunction. So I before their type is one in which Sunday starts from one and Friday will be six. If I want to start from Monday, I have to give type two and no, I would get number five for Friday. No, let's find out that three letter word. So what I will do? I just copy this basted here removed the type here, apply the text function. So this is the first argument. The second argument is always like this. If you want to get the three letter word and no, I get Friday. So these were some of the most important date functions, and in the next video, we will apply some filtering operations, some counting operations and some something operations on these dates. 5. Named Ranges: in this video, I'm going toe show you a feature which is very useful when we are hanging complex sheets. This shoot is not complex. This is just an example toe show you that this feature exist. And when we will deal with some complex shoots and formulas like we look up are we will be dealing with Marty part tabs, multiple shoots. Then we will revisit this concept again and we will use it again. But I thought this is important at this stage that you understand that you can name the ranges as well so that when we are dealing with complex functions, we can straight away start using named ranges, and I will not have to explain it again. So in this example, I have three products their sales for every month off year. So this is January, February of 2019 upto December and these are the sales numbers for these products and we use three platforms toe advertise these products Facebook, LinkedIn and you too. And these were the expenses. Where is each of these black forms? And now we're trying to find an answer that what is the sale value off each product part dollar spent on marketing, so assume that this is over question. I was very simple toe answer. We just have to some this and divided by expenses. So other safe, our Facebook and product one. What we can do is we can type of formula here some, and inside this we can take this product it and divide it by this Facebook. And we get the answer that for any dollar respondent on Facebook, we go to $5.6 in sales and know what I want to demonstrate is that I have to select this range for the other two platforms. And it is very easy for me because I have a very simple sheet. But I assume that you have multiple tabs are even multiple files and you are selecting data from other tabs or other flies. And then it will become a cumbersome job. This is one issue. The other issue is if I click on this and I look at at the formula excess butto Toby 14. If this shit is same, I can quickly see their butto be 14 is descend debt. But if this is coming from another sheet, then I will have to go there and look for what is this? So for these type of reasons, sometimes it's very useful toe given name. Toe this range so we can give her name toe Arrange butto, be 14 and we can have a descriptive name so that we can quickly see what type of data eyes this. Then, instead of this range, we will be using that name. So to do that you have to click on data and then named Ranges. And then you click on angering because a new pain will open on the right hand side. So you click, add arrange No, we can select a range. Let's see, I select from this point toe this point. No, you can put a name here, for example Product he sets so no spaces are allowed. So therefore we have to ported continues. Then we can select a range and do click here and then you can select this range and be Toto Be 14 is here and you can see that she name which is at the bottom here. And then be to be 40 Do we press okay and done and know we have this named range and No. When I put the formula for this, I can type the formula and then instead, off selecting a range, I can start typing something, and I will see this range here. Products a sales. I can select this. And then whatever is the formula, For example, I have Dwight with this one. No, this will be simply fired. No. Instead of selecting the range, I can quickly select the name off that range. And no, If I click on dept. As you can see product a sales this big a lot off sense instead of be to Toby 14 there are few more ways we can use this. For example, in we look up, tables are in any formal. Over there is a range we can use a named range, and we will be using this. And we can also export these two other ships. And I will show you later on when we will be angling with complex functions. Toe the lead arrange. What you do is you click on this name and then you first click on edit and then delete, but and we appear here, I know I can make a deal it and it will show me this message and I can simply removed. And then this named range will not exist anymore. Let's undo this. Now Assume that you have closed this pain so this pain can be re opened by clicking on data and named ranges. No, I can cancel this one. Last thing I would like Toa show here at this stage is that let's say I enter something here in the middle. I want toe add rule and letters. Assume I put a duplicate entry, another entry, anything like this. No, as you can see, that know the rain has changed from B to Toby 40. No, it shows b two b 15. But if I want to add something here, let's say I add another value here. No, our range did not change, as you can see. And if you want, want to wear some more data in the same column than what you can do is you can first edit here and then remove the rule number and just rest dumb and no, as you can see that the system has anticipated and the system has given a very large number here to check that what we can do is. We can just put us some here somewhere so we can see that Quartey the difference. No, some off product Ces. So this is going some and let's see, I air something here that is generally 2020 and I 85,000 and as you can see, that the sum has changed so no, the system will are automatically include the entries which you will be adding later on. So you should be careful about this, that you have to do this little action and remove the row number from here if you want to add daytime future. So these were some of the points which I wanted to highlight at this estate, and we will revisit this named ranges later on, when we will do some complex functions. 6. Data Validation and Drop Down List in Google Sheets: in this video, we will learn a very important concept off data validation in Google sheets and what it means is that we only want Valley data in ourselves. For example, this is my state cell in which we can only have legal abbreviation off Australian states for example, a city dinners, Australian Capital Territory, South Australia, Victoria, Queensland, Northern Territory, Western Australia and sore. And we want that if someone tries to enter a wrong date, are invalid input. The system must indicate this in any manner. So this is the purpose of data validation. And when we applied data validation toe any column or any range, then we get a little arrow here and from that arrow, anyone can click and a drop down list will appear in which we will have some valid imports and the person will select only the valid input and apply that to that particular cell. So how to do that? I will show you three examples and hopefully you will understand this concept in detail during the process. So let us assume that I want toe really date this caller. So the best practices that you create our new depth when the entries are too many. So I have created a new tab here which is named as protected. And in this tab I have the names of all the states Rickon. And what additional thing I can do is that I can protect this range, as I have explained in another video, or to protect arranged so I can protect this range. Let's say I can select this and then I can click on data and protect sheet and ranges. I call it protected and then I can enter a description, for example, protected by me and insect permissions. I said only you it means only I will be able to edit this range even if I share this shit so you can watch off separate video for details. But this is the good practice. So I protected this data and no, what I do is in my sheet where I want to apply the data validation. I slept this column and of course there is header on this column and Heather is included at the moment, and I will show you what to do with that. So I select this collar, they're likely container and data validation. And then I applied data relegation to be rich, and this is the range toe data. Validation is a polite when you select whole column. Google Sheets selects 1000 rows in the beginning. And no, if I click on this list from a range, there are few options I can select for the criteria I can list from a range which I'm going to show you in the beginning. Then I will show you how to list from items are number. So let us first start from list from rain. If I click list from range, then I click on this sheet. I can If I click here. No, I can select the range, but my range, which I want to select, is in the protected. So I click on protected and I select only these numbers which I would like to include in my drop down list. In that column where I want to apply data validation and you can double check here 8 to 29 to 29 click OK. Know this validation has been applied toe my list. No. If one example, someone tries to enter an invalid data what I want system to do show the warning only are reject input. So I would like Toa reject input and then I can also show are helping text When I reject in the warming case only Ah warning will appear and I will show you how that looks. But in reject case, I can also put some helping tax toe that person so that that person will know what the mistake he or she is doing. So I click on this and here I can say this is not valid. Australian State Court, something like this and safe No, I have applied data validation to this color and this is the warming This the little orange or red arrow? This is a warning and it says input must fall within specified range. The first thing is, I don't want this a warning to be in my head because Heather the purpose of her that is not true, Tor any state name. So therefore I want to delete later validation from my head. And so it's like the header and probably this I should do when I have done the second data validation example here as well. But let's do it. And to do that weekly con Vater then data validation and then remove validation and know you will see that that drop down arrow has gone. There is no warming because all these stairs are relics. And no, let us assume that I want to put another name here so I have the option toe select from the drop down list. And if I want to pour something else and try to enter, it will show me this message. This is not a valid Australian state court. I can further explain in this messy that please put the valid Australian State Court or something like that and I click on OK, no, this Waas when you were relegating from a range No, let us see hope to really date from item list. This is another example where assume that our club has to type of memberships basic and premium, and we have applied our discount. And if I click here, you will see that if membership equals premium apply 75% discount, otherwise 50% no membership. The word membership is appearing here because I have named my range. I am named this column as membership. So ho toe make a named range. I have a separate video for that, but very simple. You just select, select the color, and then you click on data and named Ranges. And then you can name this. I have already named membership, so I don't want toe rename do anything else, so just cancel this. But as you can see, this is my named range, and it has all the equal threats. Cancel this. So therefore, in my for Bhola, you see this membership? No, we're talking about data validation. So let us assume that I only want either the word basic are the premium in this column E sort of applied data validation. Again, I select this column data, data validation. And no, instead of list from a range I select list off items and in this item list, I can put all the items separated with commas and these are you're sensitive. You must know this thing. If I put premium all capital, it will only accept all capital. And no, I want to also reject input. Our show warning. So let us this time just show warning and I click Save. No. If I try to put something else No, This red market will appear and it will show input Must be an item on specified list. So this is the specified list here. So I can select from here basic and similarly here. That's a premium. And then I can drag this formula and the formula will apply to all my memberships. No, The last example I would like to show is, let's say I want to restrict that only a dirt can get the membership. So I will do this in the actual sheet in a more sophisticated way, but because he just we're just learning data validation. So I want to check. The age is getting than 80. So eaten are any gated number is a valid input in this column. So this is our scenario. So to apply data validation I against, like, this column data data validation. And this time I select number and these options are context based options. So these options depend on what you select here. So I have selected the number and no, it is showing me between descent debt. But when I select greater than are equal toe so no one option will disappear because no getter than or equal to means that I'm willing to put only one number here, which is 18 and I am going toe Reject input. I'm going to show us helping text that no minors are allowed our membership, something like that. See? And no, if I click here 17 8 17 and I will get this message And if I enter any number leads to 18 everything is fine. No, because I see these toe red marks here. So I should remove their data relegation from the headache. So select the header again. Bait a data validation and removed it a validation. So this is all you apply. Cater very dish in to your Google ships and you can have a drop down list from where someone can select the valid options. 7. Google Sheet Features You Must Know: In this video, we will learn five useful Google shoot features which everyone should know, and the first is highlighting their duplicate entries. For example, I am entering the result of my students on my Google sheet. And when I reach here and I start to enter a student name students first name and this name is already here in the above students. So the color of this cell will change. So it will indicate to me that issue double check whether this is a new student with a different second name or I am just trying to enter a name which I have already ended the second feature. We're going to learn these finding a trend in a data quickly without making any chart. So here is a data blood sugar level for a few days, and I want to see that trend in this data without making any chart. Are spending a lot off time so you can do this within a few seconds. The tired feature, which we will learn is protecting ranges, and I will show you ho. I can protect any range in my data. Even if I share the sheet with edit permissions, someone will not be able. Tow added That particular range The next feature. We will learn his horse to freeze the header row, which is a very simple thing. And the last feature which we will learn in this video is hokey it an interactive checklist where I have a checklist with check boxes and once I have completed a task and I check a check box, then the task will be strike through So ah line will appear on the asked that this has been done So these are five features So let's get start from the 1st 1 So to highlight the duplicate and please, we have to use a conditional formatting and click on form it and then conditional formatting and this plane will appear on the right hand side. I want toe put this in both first name and second name column. So I select the range to apply here. I click here and I select this first color by clicking on this be and it will show me Be cool and be it makes complete corner and I click. OK, I know the color has changed because it is applying their default formatting rule, which is is not empty. So because these cells are not empty, so rule is satisfied. So D Ford for mating is a plight. I want to change this rule and I want toe make my own custom formula. And the formula is very simple. What I want to check is that I want so see count. If in the column B and I put the first entry and I asked the system toe check whether it's greater than one are not, for there is a hole in here which I have to remove and then this will become blue. It means the formalize relic and know what I'm asking The system is to check that be one entry when you count, the count is not greater than one. That means that as soon as the system will get account too, it will apply this former T. And if I am happy with this color, their spine. Otherwise I can change this to these three colors are even toe custom color by clicking on here. So let us see. I want to change this to yellow and I say them no. Similarly, I have to apply for the second column, which is see, I add another rule here and no, this range is okay. Are you can change this toe? See, Colin, See as we have done in the previous. But it is fine. No, the farm allies again. Custom formula. And we this time say, counted in C column C one. And if you find expected than one, then this time apply. Read formatting So red will indicate to me. And I can change the text color, toe white And he said Done so no, you see only one rule here. But actually both rules are there. And if you want to see how many formatting rules you have applied to your sheet, what you should do is you have to select the complete sheet by pressing shift control and space, and you will see both rules. Here are any rules if you accidentally have applied some default rules. So no, If I start typing, let's say Robert and I press spared Goto the next how long it will be highlighted. And this is the time for me. So see whether I am typing the name off another student starting with Robert, for example, that student called could be met. You and their swing Later on, I can clear the former ting off all these cells. So this is a good helping tool. When you are entering data to clear the formatting, you can simply delete the rules if you want so shift control pace. And then you can simply believe these rules if you want. And the former Ting will be gone. No, The next future is quickly finding that trend and this is very simple. You have to choose our cell where you want to see the trend. So let us see. I want toe see the trend at the end. So I have to type of formula is equal toe, part line and inside the parentheses I simply give all this range and enter. And it will show me the trend in the data. No. In order to make this clear, you can You can increase the height off this cell and see it more clearly. So this is the quickest way toe see trend in our data? No, the next feature is hard to protect. Arrange. So this is my data, and I want to protect this column. Let's sit. Any column Total Sales column. I slept this column. I click on data and I click on protected sheets and ranges. I know, as you can see, whole column is selected equal and E and I can put any description, for example, for protected by my name. And no, I can set the permissions finally concert permissions. Their divorce is only you can edit this range and this is what normally we want when we want to protect something. So if I could have done, then it will save this. No. Even if I shared this while toe someone and with the any permissions that person will be able tow, edit all the shit except this range. And let's share this to someone. So I type one of my other emails and I say sent. So no, I have shared this with my other email address. And if you want to see what permissions, as you can see, I can click on share an advanced and this I have shared. And if I click here, I can see that I have shared with added permissions. So no, see what happens if I log in with this account and try Toe added this range? No. When I looked in with my other account I will see this message. You are currently signed in as this because on the same browser I have signed in with both Occam's. So no If I tried toe edit this entry and tried to put something here I will see this message you are trying toe Aditya, Protect itself are object Please contact the spreadsheet honor to remove the protection. So this is the message I will get less click. Ok, so But if I want to read it any other cell, I can edit it easily. So this is how you can protect any range? No. The next feature we want to learn is whole toe freeze The header rule So, Heather Roys were you put the title of your problems. This feature is useful when you are having a large spreadsheets. So as you can see my spirit she has hundreds of entries. So when I scribed on my header row is not here. So I want to freeze it here so that it can be visible all the times. So this is very easy. You just click on view freeze and know you can please any number off Rose You want, So I only want one rule. So know this row has bean freezed. And if I scroll down my header Ruiz always there and you will see this dark gray line here under this role. And if I undo No, this line is gone. You can also freeze this role by dragging this line from their toe 100 this role. So this is another matter? No. The last feature we want to learn is hard to make. An interactive checklist in Google ships Very useful feature. It improved the quality of your spread. Shoot. So this is my checklist for publishing videos on YouTube. These are certain tasks, and I want to put check boxes here. So I just click on the first cell insert check box and I can drag this and apply this to all tasks. No, I want that whenever I check this box, this text is striking through a line should appear on this text and maybe the color is also changed. To do this, we will be using conditional former T And another fact there This check box has one of their two values either true or false. If I check boxes checked, you can see it has a logical, true value. And if a check boxes unchecked, it has a logical false value. So we will make use off this value to write our formula and conditional formatting through strike through this. See cooler. So to do that for met conditional formatting and the range we want to select is this Ridge Crest. Okay, No, we want to change this before For Mueller to custom formula and formula is very simple. We want to see when this cell, which is B two when B two is equal toe true, you strike through, see toe. So this is the icon for strike through. And no, I can check all this column that whether it is working are not before even pressing this done button. I can check all the entries and when I'm happy, I can just click Don't know This checklist is working Inter Actively. If I uncheck this, it will go back. And if I check this, it will. Jane, the color You can change this color as well. If you click on this rule here is the colors available. You can change this to grave to want and know when you check the color will be great. So this is how you can make an interactive checklist in Google ships 8. Basic Function and IF statement: in this video, we learn some basic functions available in Google ships, and we are using an example here. Toe. Get it easier for you to understand what is going on. We have a result off 20 students and their marks for three different subjects over criteria is that passing marks are 40. And the second criteria is that if a student feels in any of the subjects that dessert will be f r felt and we will put this result in this desert column, either we will place f here are we will place a P here. People passed. And in this example, we want to answer to how you, however, questions from this data, and we also want to perform a task. The task is to put F. R p here on auto, and the questions are what is the maximum score in met and you will learn or to use max function. Then what is the minimum score in science? And the purpose of this question is, toe tell you that there is also a minimum function available in Google sheets? No, The tired question is how many stories got exactly 40 marks? So if you have to check against exact value. How will you do that? And hopefully you count for any exact value. Then we will perform the action and this action will be performed by using if statement in Google ship, then the next question is how many students failed in total. So again fared in total means. If a student fails in any subject, he actually would be considered failed. So we have to check across all this range from D toe toe F 21. Then the last question we want toe get answer is what is the second lowest scoring English . And this will introduce to you Ah, function card small in Google shit. And we will see how we can find the second lawyers star. Third lawyers are any any position from the bottom. So let's start doing this. So we will write our answers in these columns. So let us start from the easiest one maximum Matt score. To find this, we will use our function card, Max. So whenever you write a formula, you have to start with equal science Equal Max. And then, as you can see that it chose you maximum value in a nomadic data set and then we start. The parentis is because it's a function. And because we're looking for math scores, this is the range for mathematics. This tell is due to and the last cell is 21. So either we can type this range are we can slept this. So I'm just going to select this here. So as I select this range, as you can see, due to golden do 21 it means from due to do 21 you have to find the maximum value so very easy. If I close this parentis is and I pressed Enter it will find the maximum math score intimidate function is available which is minimum. So we will use this function for science. So minimum art, apprentices. And instead of selecting no, I can type F to colon after 21 so have toe colon After 21 press enter it will find a minimum score in science No. Next I'm going toe find the answer for second lowest score in English so instead of minimum nor were interested in second minimum. So for this we will use a function card small and we will give it a range which is for English we can select from here, were easily and then comma And then we will put the second argument, as you can see here that it is showing you the hint and is the number from the bottom which you are looking for. So we're looking for second minimums. Our end is too. If you're looking for tired, minimum your and will be three. So I will just put two and press enter and it will show me the second minimum score in this color. So imagine that if this column has 2000 entries, it will be harder for you to find the second minimum. Are tired minimal. So whenever you are learning, Google shakes, You are trying to find ways toe implement these techniques in your own applications. Otherwise, this example is very simplified. But these situations are applicable to your scenarios. So always keep this in mind that hold you can use this technique for your own purpose. No. The next question I want to get answer is number of students who got exactly 40 months in English. This is also very easy to find by using a function card count. If which is conditional count. I have explained other counting functions like count and count unique in another video. But in this review, the function which we're using is guard count. If and the very works is like this equal sign and then the count if which is conditional if function and then we start the bracket and then we have toe give it a range. So the English range we can select from here toe this point and no comma and no inside the quotation marks, we have to provide the criteria. Our criteria is exactly 40 marks over. Criterias is equal to four p and then we close the parenthesis. So the answer is only one student in English. God. Exactly. 40 marks. And if you scroll down, you will be able to point that student only. Daniel K. This is the student got 40 marks. No, let us say I put another student here with 40 marks. The value the function will return is changed, nor the function is returning value to because there are two students with 40 months. No. The next question is number of students failed. So you might be thinking that we can quickly find this by using the conditional count and we just apply the conditional count on this range. Do you tow 21 we will be fine. So let us see what happens if we follow this approach and what is wrong with this approach . So I just click here and I put is equal toe conch. If I give it a range and then I just simply say less than 40. And it shows me their 10 students have feared. So I know what I do is I just change the faster student marks toe fail marks. So all three subjects let us change toe 39 no see what happens. Number of students failed moves upto 30. This is wrong because only one student is failing in three subjects while our county's increasing by three. So be careful when you are using any function and always try to think logically whether that particular function is suitable for this situation or not. So what we can do instead days. So if somehow we could follow this criteria and put f and B in this column, then we can simply count this f r p and we will get the field student incorrect manner. So to do that, we have to use if statements and no, let us understand how. If statement works in Google sheet now, this is how if statement works, if is a function and in dysfunction within this parentis is we can put three different are woman's and the first argument is a logical expression. Logical expression means an expedition which will be evaluated either through our faults. Then the second argument is whatever we want. If this statement is true and the third argument is whatever we want. If this statement is false, it is important to understand that this must be in the correct order. You first put whatever you want in the situation when this statement is true and then you put whatever you want in the situation when your logical expression is false and this is the common source off error. No, here the tired option which is returning why it is optional. You can put only the logical expression and what you do if that expression is true. And if your foot only these two are humans, you omit the tired argument and the logical expression is false. Then the function will return false value and it will put falls in that particular cell. So if you want false Toby there, then it's fine. If you don't want false to be there and you want an empty thing, Then you put a coma here and in the tired argument you put just to quotation marks with nothing inside. So we will practice this in a moment. But this is how it works. No. Then sometimes we have toe nest. Our if statement inside another. If statement and we will also practice this in this video and this is how it works. You check the logical expression and you can setting in any way. But one ways. Do this if this is true and skip the rest. So when this is true, only this part will be done. Whatever is written here and then the function will skip the rest. Whatever. If are written here and if this is statement, logical expression is false. Then the system will come here and it will check again another if condition. Because no, this whatever is returning, White is acting as the third argument off this. If this first if and no disturbed argument itself is a if statement. So it has. If then it has starting bracket and it has ending Breckin. So if you look carefully, you will notice that this white portion is basically the tired argument off this a function . So if something is true, the function will skip this because it does not have toe perform this if checking. But if this condition is false, then system will check this and then system will see again. If whatever is written inside dismasted if system will check this condition And if this condition is true system with do this part, do if true. And if this condition check this is false, then the system will do the second part, which is written here. Do if fought. So this is Owen s ticket works and no, we will practice all this in our sheet. Let us assume that this is a student with 39 marks and I want to check if the marks off any student are 39. I want to change it to 40. I want to give him a one grace mark because he's just feeling by one month. So how can I do that? I say equals if And then I check this F four Ik was 39. Then change it to 40. Very simple. If I pressed, enter it. It will change it to 40. No, If I applied this for Mueller, toe all the other cells see what happens. The system is putting falls wherever this value is not 39 wherever the values 39 the system is putting 40 I don't want this. What I want here is that if this number is not 39 then whatever is this number should be written here. So how can I modify this? I can put the tired are women And no, I can put a four year press enter And no, If I drag this toe and apply this to a whole column as you can see that wherever it is 39 it's changed 0 40 But if not then the same value is written in this collar thought. This is the difference between using two arguments are three are woman's in some situations you only want toe put a blank here and if you want toe put a blank instead of putting the same number, you just put quotation marks with empty and no, we're going toe find Rather, a student is considered failed our past and we are feeling p r f on this color To do that, we will be using nested if so is equal. If you can write either lower case and upper case letters type this time upper case And we started this if statement and no, our starting point is this one it's due to and we say that if d two is less than 40 put f here. If not, then check for e too. So we say another if and no this time we say e two is less than 40 are not because we have to check all the subjects. And no, is that if this is true that put f if the student fail in this subject, put f If not, then check the 3rd 1 which is after and if after is less than 40 the student feels for f here. And if not if all of this is false, then put Pete and no, we have toe close this bracket the 1st 1 As you can see, this is highlighted. The pair of this bracket here is highlighted. Then I have to close the second if and as you can see now this is highlighted showing me that still have to close the 1st 1 The pair of this bracket is here, so no, everything is fine. If I press enter, it will show me f here because this student has filled in all of the subjects. But remember, our system only checked the 1st 1 and as soon as system God this due to less than 40. The system did not check any of these. The system state of a put f here. This is how this works. As I explained earlier, the system will only go inside when the first condition is not met. It means if the student is passed in mad then the system will check for English. If the his passes in English, then the system will check for science. And no If I drag this as you can see that it is working perfectly fine. This is student passes. All the subjects this is student fails in only one and fail is written here. No, this is working perfectly fine. You might be thinking that if I put greater than 40 here for example, changes together than 40 and I say student gets gator than 40 foot Api. Otherwise, check for the other next result getter than 40. Put Pete not check for the next desert gator than 40. Put be otherwise put f no see what happens now? This seems that it is working. But when I move this toe the end and applied this toe all cells, you will see that it's not working because it will only work if a student fails in all subjects. This is what you are checking here. So if the criteria waas this that if a student fails in all subjects then the student will be considered failed. Then we will be checking by using this greater than 40 condition. But our criteria waas any so therefore we have to check less than 40 conditions in this type of implementation off if statement so you should be careful and you should understand how logic works. The second argument is when the condition is true. The third argument when the condition is false, this order is very important and this is the common source off error and know the last thing in this video is see how this result can be corrected. No number of students failed. So what we can check is we can simply check the count off f he it while using the correct formula. So let us undo this and know that our result column is fixed with correct f and B return inside, we are able to find the number of students failed by simply counting f in this column so we can use the same county function. But this time over range is G two to G 21 and the thing which we're looking for is not less than 40 but simply f. And if I press entered no, we get the correct number off failed students. No, this was it for this video. And the most important lesson you should take from this video is that when you are using any function, you should think logically. Whether dysfunction will work institution for you are not whether it will give you the intended results are not. And to understand that you must understand the Centex off the function where you have to put the range where you have to put the criteria and what should be the sequence of the arguments in a function. So these three things are critical for you to understand 9. Rank AND and Nesting If and AND: in this video, we will learn and Operator, which is a logical operator. We are using the same data which we have used in the previous video. In the previous video, we feel this resort column based on this criteria which is written here, that if the passing marks are 40 the student is past and we have written be here and if a student fails in any of the subject, it will be F No. In this video you're going toe due to more tasks and the first task is fine and put the rank off. Every student in the class in front, off student's name, based on the total marks off, all the three subjects respected. Rather, restaurant is failed in any of the subjects or we have to calculate their total marks in this column. And then we have to find the rank of the student in the class and we will use rank function , which we have in Google ships. And the second task is that in this column we want toe put either m and s in front of the students who have passed both met and science subjects, and we want to put and e are not applicable if they feel in any of these subjects and we will be living rank, function and operator. And if statement and the combination off these another important thing you will learn in this video is the absolute references for cells. So let's get started. So first we have to find the total marks off students. I put my cursor in the first cell and I can use is equal and then some. But I can also use some from here. So both are same. And then inside the parentis is I have to select this range for this student and I pressed . Enter. So these are the torture marks for this student and no, in this formula we are using the references which will change when I will copy this formula in these cells. So as I put a concert at the lower right corner of this and I see this cross here and I can drag this and I can put this up to the last student and no, if you click on any of these, you will see that this cell location has changed for this student. It was detour in the first, then be three than before and so on. So these air called relative references off these cells and no, we will see how we can for the absolute references when we find the rank of a student. So there are two types of rank functions. One is equal rank the there is every drink and we will be using equal rank. And what does it mean is that if two students have equal marks, so they will share the position so they will get the same position. So let us see what we can do. This function so is equal toe rent start eq you. This is the function. Then we start. The parentis is and no, we have toe give the value for which we want to calculate the ring. So this is these torture marks off the student 170 then comma No. We have to provide the range in which we want the rank of this student that ranges from this to this and know this range will stay the same when I drag this formula in Ah yet column. When I drag this formula and apply this to all rose, this range should stay the same in each column. When I drag the formula, you have seen that these cells were changing. But we don't want these cells to change because for every student we want toe get his are drink across all the same ridge. So therefore we have to pour the absolute references here. So I just click with their colonies in the middle and then I press f or so when I pressed F four, these dollar signs will appear on board side of the column name and no, these are absolute references. No, the 30 statement is either falls are true. So if I want toe get a rank from the bottom from the lowest position then I should put crew here. If I want a drink from the top position are the highest marks Then I should put false here . So since normally a rank means from the top So we will put falls here and close the parentis is and press And no, this student has 28 ranks with me. This is the last student in the class. He has the lowest marks and no, If I drag this across the whole range of students, I will get the position off every student. And as you can see, that position 12 is shared between two students. Actually three students, all these three students, they have total of 1 70 No, the last part of this video where we have to find the students who have passed both math and science subjects. And for this we will be using and operator. And no, I will explain to you what is an and operator and few important things you should know. No, let us quickly see ho and operator works and why it is important to understand the return value off a function. So this end operator are you can say and function takes some logical expressions. There could be two logical expressions are more and we are checking that whether or not all off them are true because logical expression can only have either true or false value. So this function will return true if all expressions are true because it is end enemies. If first and second and third and fourth are true and the function will return falls If any of the expression is false obviously no, these are some of the examples and you can see The return value from the function, for example, is one B is two and C is three and I put in my cell. And of course I have to put equal before but and is getting 10 0 B is getting then one and sees getting then one. All of these are true, so the function will return true. Instead of putting some true expressions, I can also put the word truth and the world true will also be considered as an expression which is true. So if I put 32 inside an end function, I will still get true as a return will you know, If I put the word falls in any of the arguments of dysfunction, I will get a false recur and this will be the same if I put some false statement Are forced expression here like this sees getter than three, which is false because he is equal to three. I will still get that falls so true and falls These words can also be used or you can use an expression which is true Our force know what will happen if I put and one through three in a cell are and minus 123 in a cell R and 012 So I am writing these three different numbers to show you something. In the first set of numbers, I am only writing positive numbers, Not including zero. You can have 34 numbers. It doesn't matter. In the second set, I am putting one negative number and to positive number. And in the tired, I am putting 01212 are positive numbers. And I am including zero in this set. No, this is toe make you understand something that sometimes this zero is equal into Ford's, and this is what I am going to show you. So let us go back to our sheikhs and try putting these numbers in any of ourselves, and we will see what happens. I type equals and one toe three. And the system returns True. No, I type equals aunt. With minus 12 and three. The system returns true, but when I type 012 the system returns fourth. So it means system mistreating zero as false while all other numbers as true Similarly all other functions They also return somewhere. You, for example. Let's see what count if returns. So I type come if And I look in this g four and I asked the function to look for F and count on Leaf F is written on the energy four and the system returns one. So system finds f there at G four and returns one. No, let us change this FTP and the system return zero. So it means that I can see that when count if finds the true condition at G four, which is the presence off F, it returns one, so one is equivalent to true. Similarly, zero is equivalent to force. So what we want here in this column at the very low level, we want to check to conditions using and operator rather the marks in matter greater than 39 and the science marks are also greater than 39. So this is the lowest level checking to end conditions. Let me delete this inaugurate for hula for this using over knowledge off return values. So first we have to check toe end conditions. So we type equals and and here are the math marks So d two and we have to check gator than 39 2nd condition is science marks F two get then 39 No dysfunction. If I just typed like this, it will return Either True are false. So let's practice So it returns force. No, we want to write one of the two words here either m and s or any. So for that we really use if operator our if statement on top off end and if it takes three are woman's. The first is the logical expression which will be provided by this and the next toe are woman will be the words which we want toe type So let's put if here on top off And so if and then we have to start The parentis is for if and close This parentis is here And first argument off If is here already the second argument If this first condition is true, then we want toe type M and s math and science And if between turns falls then we want to type and it our formalize complete No, if I press enter and it will be printed here because and was returning false as we just checked And the student has failed in math and science, so therefore and May's here. So let's see whether this works for all other entries. If I drag this to all rules and I randomly check few, let's check this one student has failed in math, although he passes in science, but we have to write and it lasted this one passed in mad and passed in science find one more passed in Matt fairly in science and me. So our formalize working perfectly fine. So this is it for this video in which you learned a few things. Just a recap. You learned how to put the relative and absolute references. You learn how toe use and operator, what is the importance of the return value and you learn hoe to nest, aunt inside if when we have to check to conditions and type something depending on those two conditions. So no, you can apply this knowledge toe your own situation, where you have to check toe and conditions, and based on the result, you have tow perform toe alternative tusks 10. Creating Charts in Google Sheets: once you have entered data into your Excel sheet. No, it's time toe kill e eight. Charts and graphs toe visually understand What are the key data points in this video? You will learn how to create charged in Google ships. I will try to explain as many options as possible, but if you think that anything I have miss, please put in the comments are in the community care, and I will definitely answer your question. This is a simple data in which I have cell values for two products A and B in column B and E. We have the ad expenses. So let's start our first chart and the first charges between Column A and B. I can select only their data or I can select, including the header. So I am selecting, including the head of the 1st 2 columns, and then, in order to create a chart, I can click on this insert chart icon, or I can click on insert and there is a chart icon inside. So let's click on this and instant be a chart has been created by Google ships since our column has dates for mate of this columnist, Big type and we have January, February and all the months of 2019. So by before, Excel has taken this on the X axis and this is on the Y. X and the relationship is shown with a line graph are lying chart, as you can see here, chart type is line, but you can change this. In this set up area, there are two areas year toe taps set up and customize. And this pain is called chart Editor. And if you accidentally closed this, you can re open this by clicking on the chart and then these three dogs and then a charge. No, the first thing I would like to do is to change the chart. So, for example, I am interested in column chart. So I just click here and know my chart type has changed on the X axis. I have months on the Y axis. I have sales. And before we talk about customization and other things, one critical thing you should understand is what type of chart is best suited for your data so that the T data points should stand out and this is skill. You will get only by practice and experience. There is no easy way to tell you because there are multiple ways. Toe present your data. There is no one method are some golden rule you have to practice and you have to learn the skills. So let's talk about customization of discharge. And in order to be able to use this chart editor pain, you should understand what are some of the items scored? For example, what is this? This is the title of the chart and this month pieces title of the X axis is the title of the wire. So these kind of things you should know, then you should be able to use this chart editor. I will try to explain as many options as possible, but I don't want this video, Toby two hours long. So therefore, if I miss something, you still have the option to us questions. So let's start So the first thing waas chart type and in order to change the chart type, you have to click on set up and then you can jana chart and if you scroll down, you will see a very important area, which is the data ring. So this is the range which we selected in the beginning. You can re select this again by clicking here, and then you can select, for example, a different data and click OK and no, we have selected sales be as well. So there are to set off columns what each month. So this is the point where you can change your data range. And if you look at series, this is also very important. You will see both Siri's here. One is sales A. The other is sales B. And if you click on these three dots, you can remove any of thes Siris, for example. I can remove this and I will be left with only one CDs. And if I want to add a series, I will just click. Here are Siri's and then I can add any Siris, for example, this range and it will appear here. See to Colin, See 30 and I can click OK, and the series will be added so very simple. Where the cities are removed are Siri's and if you want to jenny the access, you can do this. You can switch rows and columns, but in our case, this makes more sense that we are comparing both products sales, although one product columns are very small and I will show you hope we can improve this appearance of discharge in a minute. So these are the options taken by Google Sheets as a before, and you can uncheck this. For example, if you uncheck use column years labels, it is unchecked. No, these months are going from here, and if I check it again, this will appear here. So this is basically over column it. So these are the header values here. And if I uncheck this, these values will disappear. So these are basically sales and sales be our our legions. So let us remove this range from here and we're back to over only one graph and let's go back toe line type once again. And in this line type. If I click on customers that no, I have many options here, for example, chart style. So if I want this line Toby smooth, I just click here, and this line will be a smooth line. If I click on maximize, Google's used will try toe maximized the graph within this window. As you can see that, no, it's a little bit zoomed in. I don't have any null values, but if I have any zero value and I want to floor that, then I have to check this by default. Google Sheet will not float zero values and compare motives that when you have two values. So let us UnSub move this once again and I go back to set up and I add Arrange a series and I heard the cities and let's say, take this series here and no, we have these two sales value and let us go back to customize and I I don't want this maximize. If I take this, compare more. Let's close this and I click on the chart. And when I over here, I can see both values at this point. Values for sales A and sails be so this this is what that compare will do. So let's go back. And if I remove this and know if I come here, I will only see one value so it will not compare this value with the other. So this is what this compared is doing. Let's go back to the editor and no again to customize in charge style. So these four No, you know what does it mean? You can change the chart background color if you want. But why? It is fine. I like why charred border color If you want. You can have any color border but grace point no next discharge and excess title. So, as I explained earlier, you can set your titles here. For example, if I want to change the chart title from this drop down, I will select chart tighter and I can then die Pierre whatever I want. And if I want a subtitle, I can click Subtitle And right here. This is my subtitle. If I want to change the horizontal Axis title, which is months at the moment, I can change it to anything. If I want to change the world, take Alexis title. I can click on here, and I can type whatever I like. I can change the phone size. I can change the phone itself. I can change the text color as well. So these are some options here. The next is Siri's, and no, I can make changes. All the series are individual. Siri's so Syria's air. Basically these lines that data in column B and C So first I have to select from here. Apply these things toe all cities or I can apply tow line one or two. For example. If I select line jewelers and I can change the line, type two dashed. I can change the line color. Let's say this one. I can change the thickness off the line and know this I will explain in a minute left Texas and right exist Once I have done this for mating thing, then I will explain this. Similarly, I can change the data points style, for example. Currently there is nothing here at the corners off this line. I can change this Tuller triangle. I can change this two star whatever I want and point size I can also increase. For example, 10 point are 14 point whatever If I click on Enter Bar so this will put something like this and this shows the margin of error are the uncertain entity in this data values. But this is a statistical term you can see and you can set the value for uncertainty. For example, if I really use this to 5% these error bars the size of their bar will decrease I can also apply your trendline, and this is, ah, best fit line on this series by before there's a linear regression type off it, but if you click here, you can apply other types off it. But this is something to do with statistics and calculus, so we're not going to discuss these algorithms. You can change the opacity of this line as well, if you want. And no, let's talk about the very important option, which is this left Texas and right next. So first, let's move this chart a little bit. So if you click on this chart and then when this hand appears, who can drag this to anywhere you like, I just want to place it just here to make my point clear. So if you look at Series A and Series B, there is a huge difference between the value and therefore this series, a values which are very small values. This line nearly looks a straight line. Actually, it's not so. It's not a good presentation, although it is technically correct. But it is not a good presentation with this left axis and write X is what you can do is that you can apply to Y X is one on the left hand side, one on the right hand side. And if you do that, you make sure that when you are reading this data for one Siri's, you look the values on the left hand side for other Siri's. You looked overly on the right inside because you have applied to working Alexis. So in order to do that first you have to select here serious. We seriously want toe apply left axis. So let's say I want my blue line for the left Texas. And then I want my red line and I want to apply right know what will happen is Siri's e R. Seals. You can say they have the success and says be, Have these X y, for example, you are looking this star value. You can see there exactly this month. If you are looking at this value blue one, you will not look here. You will look on the other side, and you will say it's roughly 13 or 14,000 something like that. So this is all you can apply to different access to your data. Siris, for this is very important. So dollars talk about this legion. These are the leaders, and previously we were using this options toe position them, for example, bottom here, left inside their top here. But no recently a couple of months ago, Google has given us the option toe move these legions and all these titles and all these things. For example, I can click here and then I can drag this to be what I want. I can click on the title. I can move up. Subtitle Click. Here. I can move down wherever I want to know. These options are not much use, but you can still use them. You also have the ability to form a horizontal axis. You can change phone. Labour's off these type of things. You can reverse taxes order, so these options are available. Similarly. Vertical axis. You have these options available, and minimum maximum means that you can set the values. For example, if I said minimum toe 5000 as you can see that, no, my work pick Alexis is starting from 5000. So this is a thereto. Zoom into the graph and showing a bigger size image. If you want to get more than one. Images off your graph in some situation. This is very useful. You cannot just limit the maximum values like this. I know that only this portion of graph will be busy. But then grid lines is very simple to understand that if you want to change the made a great line color or any particular number off major greed lines. For example, if I put 10 as you can see the there are 10 lines here, there minor, great lines. I can put lines in between these for example, to so no, there are minor grid lines as well to minor grid lines between toe major gridlock. So this is on the vertical axis is if you want to do this horizontal on horizontal axes, select this toe horizontal axis. And no, you can also change the, for example, five grid lines here and then you can also apply some minor grid lines like this on. There will be a lot off grid lines. So if you want this graph line appearance, for example, if you are teaching and you want students toe, find the card in export, some points there, then you can use these grid lines as well. So let's undo these changes and just applied this chart type next, apply the right axis as well. Okay, so this is best looking shape off this chart, and let's talk about these few options here. So this were discussed at a chart or villages. Off course. You can believe the chart. You can download this as a PNG image. Our pdf document are also as a vector image scalable. You can publish this chart on your website. There are two ways to publish. One is interactive are one is as an image. So these options are these options are available here. You can copy this chart. And this our text is for those to whom your chart is not very visible so they can use the screen. No, this is the last thing I want to discuss here. That you can also move this chart toe its own chic. And if I if you look here, I only want you to. And if I click on this and other shit will be created for this charge on, then these options will be on the top. So this is an option. If you want to use, you can use it. I just show you. Just click here and this is my new sheet created. If you want to share this our show it to your team members, you can do this later on. You can believe these are you can make it up. He didn't shoot if you want. So this was a brief introduction toe getting charged in Google sheets. So I hope that you will be able tow find out and discover rest of the options by yourself, or you can ask questions and I will definitely answer your questions. 11. Using Filters with dates: In this video, we will see how we can apply filters toe dates. This is not a video about how to use filters in Google shares. I have a separate in detail with you about fitters in Google sheet. This video is only hope to apply Freighters Index. When you're working with dates, you will also learn ho toe count are some well use. So let's get started. These are the questions Why? Which? I want to get answers written in yellow columns and I have a data off top 20 days when most students were absent in a class. As you can see that these dates are completely random, there is no sequence. So the first question is what dates were in August. So what are the days falling in the month of August? So I will use a filter to find this and and the way the figure will work is I will just put equal sign and filter and then start the apprentices. And, as you can see here that the filter needs a range and a condition. So the ranges I want toe look from a 3 to 8 22 So this is my range so I can click here and I can read. Select this range ranges, Phil. Here, comma, What is the condition? Condition is that I want toe Look for only doors Rose in which the month is August. So I want to extract the month which we have learned in a previous video And I want to extract this month from the same range from here here. And I want to see that this month is equal it No, my Frieda is complete. This is the range of the filter and my condition is that extent the month from this range and see if it is equal to it. Then put that is there in the rules off this column. So if I entered, I get two rows filled because this is the air filter. Works it post that desert in the rules of the column in which you put the formula. So these were the days falling in August. Know? My next question is how many days were in August? Obviously, there are two days, but I want to calculate using the formula because you might need in your application toe calculated only the number of days, not the executives So what? You do them simply You put a count on top of this filter, so I just copy this folder formula. I put account on top of that. So copy, Put it here. Place and put count there. Count and rest is same Really simple. And no, I get dancer to It's a single answer. So the next question is, how many students were absent in each of those days off August. So No, I have to look in column B. So this is the range of my filter. Now, the rest of them The condition is same because I'm looking for the month of August. So what I can do is I can just copy this filter. They're all many students were absent in August. I have to change the range of the filter. We start off here to the I have to put B three Toby 22 looking this range and know I will get dancer harmony. Students were absent in each of those days. So in each of these days, these were the students on 13. The focus 6 26 of August 9. So this is all you. I can understand that if you need a value from this column. Then you apply the range of the filter in this column. No, the next is hard to apply the some again. Very simple. Total number of students absent in August. We have to some, These two, just like these two were counting. No, we're something. So again, I place this formula here. So I just copy this. Place it there, and no, I put some here on top of this. Very simple, but extremely useful. So this is how you can get some answers from your data containing dates? 12. Filters and Filter Views in Google Sheets: In this video, you will learn how to use filters in Google sheikhs. There is three times off functionality available for free during the first is the traditional method of poetry. Filter feature were weekly conflict icon and then apply Filters toe are just in columns. No, this filter feature has, ah, major drawback that when your team members well, look at your sheet, they will see the sheet in a filtered view. If you have applied a freedom, they can then mortified a freighter are they can turn off the critter, and in most cases they will because if they're collaborating and tried to participate in their own part, so they will have to do that, which makes this feature essentially useless. This Richard can still be used for quick filtering and looking at your data, but not as a permanent part of your Google sheet. The better way would be to save your filters in filter views, and Peter Views will be saved and will be visible as a menu item in your Google sheet. And your team members will still be able to apply those filters if they want, and they have excess. But you can do something to protect your features. For example, you can name your filters with your own name are with your department name so that your team members will not touch. Those filters are at least not mortified those pictures. For in this way, you can repeatedly apply those pictures to your data and look at your data. The type functionality we have is function, and this is by using filter function and some associated functions in your formulas. And these from chances, for example, sort are sort end. These functions can also be used in conjunction with greater function toe. Make some good filters in your data sheet, and this matter is very useful when you can put your data at a separate terror bar in another location and you want to filter your master spreadsheet. In this video, I will cover first to matters, and in the next video, I really cover the hard matter. So let's get started. No, this is my ship in which I have students names there faster last names and their marks in three subjects, and then there result as you that we are three pictures collaborating on this shit. A science teacher, English teacher and a math teacher. These results have calculated better the marks off. The students are getter than are equal to 40. Then the students will be passed. But in all subjects. So as you can see, this is the condition there. If student has less than 40 marks, student will get f our faith otherwise past The purpose of showing this to you is that these p and f are not some data value These air coming from some four. Pull up and let us see. I want to apply your filter on this data. This freedom can be applied to a single column arto a set off Justin columns. First, I apply filter to this column G. If I select this column and then this is fritter, I can create greater. I click on create fritter and no, it's completely shaded. And you will also see a green color appearing here on on the site. Another thing you can notice is there. There are three lines here now. So if I click here no, I can sort these values and I can select whether I want all countries were students are failed our past. If I want to see only the students who have passed I will uncheck this F and I will click OK, and no, I will see only the students who have passed In total I have 50 students. So out of those these students have passed. If I want toe modify this fatal I click here again and in this column we have only two types of entries. So it is very easy toe uncheck one. But if you have, for example, 20 types here and the best way to apply filter is first clear, so it will clear everything and then you select whatever you want. So this is the simplest filter which is applied by just selecting what type of entries you want. So in order to remove this winter, are you over here? And it will show make turn or fritter. And if I click now, the filtered will be turned off. As you can see, these lines have disappeared. Green color and I can has changed. No, let us apply your filter through all this data. So I select all these columns. Click on create a freighter and no, I see all columns are green and these lines are appearing on every corner. Now, if you click outside, no factor has applied. No, If I click here in column B, I have few options. As you can see here that I have all the values in this column and for example, if I want toe check that is that for only these two students. I should first clear and then select these two and apply the freedom. And no, I clear all this and no select all. And I said OK, no, all students are here now Let's again apply another figure. And this time I want to sort students in ascending order from A to Z with their first name . I click here and know all the student names will be in the alphabetical order in this court based on this corner. Now I want to look for students who have passed in English and math but failed in science to clear that filter. I have toe klik in the mat area and no, I will filter by condition and the condition will be that the marks are greater than are equal to 40 in the mad subject. If I click OK, no, All these students I can scroll down upto the end. No. All these students in the mad column they have marks greater than four p. And you see here this Peter has been applied. So you will see a future sign here. Why? No fritter has applied to other corner. I want to figure out it down. For the for students who have passed in English as red. So again fatal by condition. Condition is that Marx Argueta then are equal and value is 40. Okay, No. In this color we have students who have get it than 40 marks in English. No, I want to apply your Fator. For students who have failed in science again, fatal by condition marks are less than 40. So know the condition is less then and the value is 40. All the increase who are less than four p will appear by applying this failure. Okay, now these are the students who have failed in science but passed in other two subjects. So let us say I am a science teacher. So I am interested in students who have passed in other subjects but fairly in myself. No, If I leave this filter on and then a math teacher comes and he will see the spreadsheet in this warm because later has applied and Peter has applied toe these three columns, he will be able to see them. He will be able to modify them. And because he's a math teacher, maybe he wants to see the students who have failed in met while past in English and science . So most probably he will have toe modify this freighter. So this method of filtering is not very useful in most situations because even if he wants to see the full spreadsheet, he will have to turn off the feature for these type of situations. When we're collaborating with team members, we should create our own filter views. And this is what we are going toe learn next. So let us turn off this feature. No, the filter has turned off. And if I hover, I can see create a perimeter and that I can has changed. So no, let us create a filter view or to create a filter view. I can select these columns and then if I over over this little triangle, it will show me for interviews. I can click here and then create operator of you know you will notice that a great bar will appear here and on the left hand side. We have this great area. All columns are great. This means that we are in a filter view. And here is the name of the filter. For example, I am interested in students who have failed in science. So I can put a name on my subject and then failed. So this is my freighter view. If my click outside No, this picture we was here. I can create my freedom for students who have really in science. I click on this science filtered by condition less than is the condition and for peace the value in this column. So look for well, use less than four p and click Ok, now these are the students who were failed in science. So this is my creator. No, in orderto come out of this. I just need to close this from here. I know my full spreadsheet is visible and if I am collaborating with math and English teachers, they wouldn't see this full spread shoot and they will not see any future Here There is the filter icon is normal. There is no filter I can hear, but it did click here in filter views. They can see what filters are applied by science teacher because we can have ah convention that all filters applied by a science teacher will start with the word science and a math teacher. Our English teacher should not touch these filters, but they can still see if if if anyone clicks on this science fair, this freighter will be applied and better will be presented. So this is a more sophisticated way of creating filters by saving in interviews. If you want toe believe this filter view, then click on this gear icon and click Billy. And no, this picture of you has been deleted. And if I click here, there is no fate of you in this area. So this is how you can create Peters and Peter views in Google Shit. And no, you can download this file and do a class activity, and your activity will be fine. Students who have got marks between 40 and 60 in science but failed in English. So this is your class assignment for this video 13. Team Collaboration Methods Available in Google Sheets: In this video, we will discuss how to collaborate in Google sheets with your team members, and this video is very important in this course because most of their times this is the main reason we're using Google shoes to be able to collaborate with our team members. And in this video, we will discuss all the ways to communicate and collaborate with co workers in Google shoots. And this collaboration can be divided into these activities. These are the four major types of collaborations we can have. The first is by giving access to someone to be ableto view your Google sheet or to be able to comment, or you can also allow full access to edit. Your sheiks are even. You can transfer the ownership off your sheet to someone if you are, for example, giving that person for responsibility for all the activities mention on that particular ship. So this is the major classifications off types off excess someone can have in your bullshit . No, the comments and note activities are very Goldman and probably most of the time, the team members will be doing any of these activities when collaborating in Google spreadsheets and these activities include making a comment, assigning someone any comment. And what does it mean is that if you assign someone for a comment, then that person will have toe respond toe that assignment by marking that particular comment as done, Which is the activity number four I have mentioned here, And resulting comments means that there were some comments going on. There was our discussion going on between team members and render discussion is finished. A team member can resolve or comments saying that it is no resort. But you still can see those comments in the history, and you can still you re open any of the comments, which are not deleted. So once you have deleted a comment, then you will not be ableto reopen it otherwise. If the comment is resort are done, it can be reopened. Then there is a note functionality available in Google sheet, which is not as powerful as the common functionality. But sometimes you don't want toe comment. You just want to put a note for yourself. Are for someone else to briefly explain whatever is written within that particular cell. So this functionality is similar. Put the Lord functionality of Microsoft Excel then another way of communication is emailing . You can email your spreadsheet as an attachment to someone in PdF Form eight or in Microsoft Excel format. So this is how we have collaboration. And finally, if you want to give excess toe all of your community or even public, then you can publish your spirit shoot as well. For example, if you are a small business and you want to publish some critical information for your customers, you can publish your spreadsheet. And later, if you decide toe unpublished that spreadsheet, you can just click Stop publishing, and that will be unpublished. So we have a lot to cover in this video. Make sure you watch full video to understand all these functionalities, so let's get started. No, this is an example Parachute and assume that there are three teachers collaborating with each other math, English and science teacher, and I am the owner of this sheet. And let's say I teach Matt and I want to share this sheet with other two people. To do that, I just need to click on share and then I can type the email addresses off those guys. In this case, I am typing my own email addresses. I can put some notes, for example. Please put your desserts something like that. No. On the right hand side, I have this pencil icon. And if I click on this, I can give them any of these type off excess so that it is the full access. So this is what I want. Toe. Give them. And then I simply click sent. No, As you can see, it shows me shared with two people. No, an email will be sent to these two, and they will be ableto edit this sheet and put the results. No, If I click on this once again, I also have the option at last here. If I click on advanced No, I can give individual access toe these two guys. I am the owner, as you can see, but no, I have the ability toe, for example, Jane, this person access toe view only. And Jane, this person access to comment only previously. I assigned same level of access toe all of my team members, but in at one step you have the ability toe give individual a separate type of access. So this is what you can do here. And if you want, give the owner access. So see what happens if I try to make this person as the owner and I click on save changes, it will show me a warning message. Are you sure? Because that owner can let her own remove my excess as well from this Google shit. So I should be very careful when I am trying to change the ownership off my Google ship, so I will say no. So let's click on Done an order stock about ho to insert comments and some related functions. Let us assume this is a student who got 39 marks. 40 was the passing marks, and I want to put a common for science teacher. Portugal Cedar one grace smart for this student. To do that, I have to put my cursor in this cell and then to insert a comment. I have this plus sign here. Insert common or I can click on insert and then common the hardware toe insert. A comment is by pressing three keys together, and those keys are control art and M. If I press these three keys control our M, I will get this place to put my comment and no, I type my message. And I am not assigning this comment toe anyone. I will show you in the next comment hope to assign someone a comment. This is a general comment and I click common. And no, my comment is here. Our discussion is open. Anyone can reply to this comment. And once the discussion is complete, we can resolve this common. And if I click all resolved, it will be resort. And while this comment is here, there will be a little triangle on the right corner of this cell. So your team members should know that what they should be looking for if you are used to put comments in your sheets So this little triangle is there. And when that person sees this sheet he had she will be able to reply to this comment. So let us assume that no, our discussion is finished and I want to resolve this. So I click on result. No, this triangle disappears from here. But if I click on this comment history, I call on the top right beside the share button. If I click there, I can still see that comment is here marked as resort. And if I put anything here, that discussion will reopen. If I make a new comment in this area, if I want to deliberately reopens this discussion, I can click on reopen as well. Or if I want to delete this all together, I can click on this little triangle and I can delete. And let's that believe this. And it will ask me about ability. Delete this comment right. And if I click delete old thread will be deleted. No and no. I will not see this in the common Syria because there is nothing else. So there is no comment. So this is how you put a normal comment and resolve a comment or delete a comment? No. Let us see how I can assign someone. This time I'm going toe assign science teacher to do that. I inside a comment here and I put a sign and then the email of that person. And then this thing will appear here assigned to this person. And no, I have toe check this one in orderto assign that comment to this person and no, I click on a sign. No, this is no assigned to this person. This person will get a notification and they will have toe look at the comment reply. And once they think that, no, they have addressed the problem or issue, which I am putting in this comment, then that person really click on this Dickstein, and this will be marked as done. So this is all this functionality of comment works. You can assign someone toe become an obligation for that person to reply toe that common. So this is a very powerful collaboration feature available in Google sheets. No, let's see how we can insert note. For example, I want toe insert a notable this this student guard 34 marks. So I inside a note, and I can put this note for myself or for any other teacher. Why such a lure is just click here and no, ah, black triangle is here. No comments are powerful because the person will get a notification. You can make it a compulsion for that person by assigning a comment to someone but for North. Your team members should understand what they should be looking for, or they might miss your lord's. So this is something you should take care of when putting note north are just the explanation off something. And no, let's talk about how you can email your spreadsheet. No, in orderto email your sheet, you have to click on file and email as attachment. And no, you have to put the email address in two area, and then you can select whether you want pdf attachment or in Microsoft Excel for mate. You can also put some message here and you can send a copy to yourself. So this is all you will send this sheet as an attachment. I just cancel this and the final way off communication. If you want to make this shit as public, you can click on file and then you can publish to the web. And now you have the option toe get a link or an embed court so you can publish this as a webpage. You can publish this as a PdF or some off other former switcher available here. So let's say I publishes a webpage and then publish. It will show me this warning message. Are you sure you want to publish this selection? And if I click OK, it will give me a link here. And if I click on em bad, it will show me that embed court. And there is a note here that viewers may be able to access the underlying data for published charts. So if I click publish contents and settings, it is no published. And you can take this check box here at the bottom, automatically republish when changes are made. And most probably when you are going to publish something for your customers. You want this to reject? But you can uncheck this so no If I close this No, my document has been published. I can pour that embed court any of you and no, If I want toe unpublished, I again click on file published to the web. And this time I can click on Stop publishing. So if I click on stop publishing are you sure you want to stop publishing this document if I click? OK, No, my document is no more published. So this is how you can publish your document by using a link all by using an embed court Miss, you can put on your website So these are some of the collaboration methods available in Google ships 14. Conditional Summing and Counting: in this video, we will learn for important summing and counting functions in Google sheets. And what you see on this slight is my own method off memorizing these functions so that I don't have to look in the function definition when im trying toe the ranges so some if and count if look for only one condition and some ifs and count ifs look for more than one conditions. So this is the first difference you should not. The second important thing here is that when there is only one condition, I'm using the word this and when there are more than one conditions, I'm using one and two. So the way you should look for the summit function that some may function has three are women's, while count if only has two. So the way I read this function is like this look in this range. So I have to give a range in the beginning, the first argument and if you find this, then I have to give some criteria at what is in some range in front of this. Then I have to provide another range where I want to add values in front off this so this is very important here, and I will show this in practice in a moment. Then the way I read some ifs is like this ed, what is in the some range and noticed that some range is the first range you give in some ifs. While this is the last range of giving summit, add what is in the some range when you find in front range. One has one and range to has to. So look for these two conditions. So let us assume that every ranges a column. So look that whenever column one has one, which is the criteria one and call him to has to which is the criteria to then add whatever is in front in a column card. Some range, no similar manner. But no, because count means it knows that it has to just count. So therefore, it is not looking for any some Marines, so it's simply looking this range. And if you find this count, so whenever the function first time finds the criteria, it comes to one the second time it finds the criteria it counts toe and so on. So in some means, the function has to find a value, which is it has to some in a separate range, but in counted it just has to come. So there therefore one less argument and Countess is looking for more than one conditions. So it is just like and logic some ifs and Countess work like and logic. So only count when you look in range one and you find one and you look in range to and you find two. So there are two criterias one and two and two ranges. For those criteria, you can use it either in this way are you can look at the definition which she shows you when you are trying to use a function and we will see in a minute. So let's see how we can use this function in Google ships. So this is over sheet and if you look at the bottom, you will see three tapes Master Tear, which is here where I have the gator. Some and count were in this video we're going toe practiced some if and counting functions . And then the next tab is that I will show you in the next video when we practice paper tables and we will see hope your tables can be an eider NATO using these formulas some moves and countermoves and all you can some and count easily without using formulas and using paper tables. So this is, Ah, long data, as you can see that it has around 600 are so interest 585 entries. The first line is had there. So 584 values. So the best practices just toe assign named ranges, as we have discussed in another video. So let's do that. The first range, which I want to name is sales person. So I click on the first entry, then control Shift Down Arrow and it will select the whole column. But as you can see that it has selected upto 5 35 it shows me here and no, I have to click data named Ranges and any name like says Arsenal, this speak capital done And no, because I have moved to the last point. So if I select a range from here, it will be wrong. So make sure that you go back because when you use control ship down arrow, it takes you to the point up to which it is including entries. So no, I am again at the top. So the next. So the second reading, which I want to include Our name is units, sword control, shift down arrow. And no, because pain is open so I can add a range from here at a range UNIX sword done. And again my cursor has moved, so I have to take it back. The next range I want to name is the product category because we will be using products A B R c in our criteria on your ship down arrow, add, arrange. And also make sure that you double check this entry be toe be 5 35 b two because our starting point is second rule so that just double check here product types and the last range I want to name his total sales. So I click here, control ship down arrow add arrange total say's then I know Let's take another range as well the Patriots or control ship down arrow head arrange and it's our is whatever done and no, we have simply fired our data a little bit and no, we are ready to use over someone count functions, So I click on this tab here and in this step, I have to find answers toe these questions Since person list, I want a unique list off my sales people. So it's very easy to find using a unique function and in the unique function, I have to give the range of sales personnel. So this is the name of the range. So I just started typing s and it will show me say it's personal here and enter. No, this would show me the list of all the salespeople. So from 2 to 20 I have 19 sales. People here know the next question is product types. So I can also use this unique And just like this, I can put the product type thing and it will show me. But because I only have three products, so why not just put product? He fraud be products. See, there is no harm doing this because less formulas are always better. Writers do it like this and know the next question is I want toe find total sales off a product. A No. If you look at this formula some if I have to provide this range, this will be my product type range and the criteria. This will be product A and some range will be my total sales range. So let's do it is equal toe some if brought a type range. So I just start typing PR and it will show me here. This is my criteria range and what is my criteria? Criteria is I'm looking for product A so I can click here and at this stage I want to think that whether I want to keep this be to reference as relative or absolute. So if I want to find this for other products and I can drag downwards, then I can keep this as reality. So why not keep it relative? Otherwise, I can press f four and make it absolute, and we will do some absolute examples as well. So let's keep it relative. And then I have to provide the some range, which is the total sales. So I start typing quarter. It would show me here, and they're sick, so it will show me total sales of product A in my master sheet. So wherever his product A, it will add this all these values because it is looking at this as the range of criteria it is looking for. He as the criteria and what they were in front of it. It is adding all these entries. So this is all some if works. No, I can drag this. And as you can see, that be to changes. Toby three. And before and before is this one. So no, this value is for a product. Be And this will use for products. See? No, I want to see units sold off. Product it. So in the master list, these are the units sword and no again. This will be my some range, the last one. And this will be my criteria range. And my criteria is already written in these columns. So again, I will be using is equal toe some if criteria ranges. Product types criteria is this cell and some ranges. Units, sword and death. And these are the units sold for product A. And if I drag, I will get units sold for product B and C, nor total sales by a sales person. So this is my sales person one and I am looking for Total says no. Total says by your sales person. I'm not I'm only looking for one criteria again in the next column. As you can see, I'm looking for two criterias portal Say's off E by a person. So I just wanted to simplify this intense. So what? I'm looking for that. How many total sales off product E waas done by this person? But here we are only looking for total sales off any product by this person. So what will be our summing Ridge? Some ingrained is our total sales range, which is this one, and our criteria range is this, says person range, because this person is in this and other criteria is the first person. So let's do it is equal toe some if criteria range, which is his personal range. So say's his personal and criteria. Is this person here too? Again, we want to keep it a relative reference because we want to drag it for all people. And then I was summing, Ranges up total sales, which is the all. And then we get this total sales, close the pennant theses and enter. So these were the total sales figure by this person, and if I drag this, I can get total sales figure for all sales people, and I can immediately see which says Person is more productive or efficient, that the next is total. Say's off e by your person to know we have two criterias in some, if the first is some range and then first criteria range and criteria second criteria, range and criteria, so some ranges first. So what we type is is equal toe some gifts and some range summary inches. Total sales so total total sales is over some range. No, the first criteria is off a so product types because is in product types. So criteria ranges product type. So I just I peer, I get this and criterias for the e No second is by your person This person and this person is in says personal range. So says personal range and criteria is this person And so these other total Cesar product A by this person No, here is the important part. If I drag this so what I will get I will get the sales for product B and C by this person. No, because no If I drag see what happens if I drag this after this point? No, If I click here. I'm getting total Say's for product. I be three, which is broader. Be No, not a And says person a tree, Which is this guy? MC Dahlia Court. So this is changing. So this is for this person and for this product. So be careful what you want here whether you can drag this or not whether you want to drag and what information you want. If you want to associate this column with this column and you want the sale of product A by this person says a product by this person is a product A by this person. Then what you need to do here is you want toe. Keep this Beato as absolute reference by putting the cursor here. And then I press f four. It will make this be toe as absolute reference. No product will stay it, but sales person will change. And no if I enter. So this is more appropriate for this column. So, no, I can drag this up to this point and I can get the values and this person has not sold any of product. He And if you get a lot of zeros, then you double check that whether you are putting the formula correctly or not. But in this case it seems that this person has not sold any product it because the formula is correct. Still, we have product A and 11 as this person. So this is how you should be careful of all the references, whether there should be absolute or relative reference or the next question is number of days product a waas sword. Here you have to think about this county function because we're just counting dicks and not dicks were just comping when product is sold. So what will be this ring? This rain will be date range or the product type range. So this is the question you should ask. You still pause this video for a moment and think about this. Whether this range will be DX range are the product types range our criterias product A. So this means this means for the year. But what is this range to think about it? No, this range is the product types range because index range this criteria is not their index range. You will not find product it just look at mask. This is our day saying there is no product. He product is only in this range, which is product types of So whenever it will find product A, it will increase the counter by one. So no need for ditched. So they're stick. What we can do this is equal to gone. If for the types comma this is what idea, Brenda. And no, If I drag this, I can find this. What product? B c as well. No, the last parties number off days product A waas sold by your person. And this is your class activity. And what you have to do is you have to use count ifs function and you have tow Fill this for all these sales people and then you have tow total it here. And that total should be gratified because 85 is the number of days Product it waas sword and no, we want toe calculate number of days product it Woz sold by each of the individual person. So a number of this product it waas sold by this guy by this guy. But this guy and soon and the sum of all those should be 85. So up to this point, you really feel the values using count ifs function. And then you will take a sum like this. And you will some from this point to this point. And this some should be 85. It should match this value. So this is your class activity. Who can do this and see whether you get this? Well, you know. So in this video, you learn or to use some if and some ifs count if and count if and in the next video. We really see how you can find all this information without using these formulas and using the word tables. 15. Pivot Tables Explained: people tables in Google sheets are very powerful and flexible. Toe toe. Analyze your data quickly and easily without writing any formulas. In one of the previous videos, we have used some and count formulas. Some, if some ifs, count if and count ifs and writing for mullah takes a bit of time. A very good alternative is river tables. So in this video we will see how we can create paper tables in Google sheets, how to manipulate them and look at our data from various angles and some of the associated features available in Google ships. So this is my data, and it is very important for you to organize your data in a specific way before creating paper tables and the best ways to have ah header at the top and then all your data arranged in columns. And here I have seven columns in my data and there are 584 rows in total. If I scroll down, I can see there except to 5 35 The first thing you should know before creating a pivot table is that you understand your data, and in this data I have a few sales people and every sales person. It associate ID with a state in Australia. So this person is responsible for selling only in New South Wales and there are more than one people selling in New South Wales, as you can see similarly for all other stairs except in Northern Territory, I think we have only one person and we have only three products products A, B and C. And these are the unit prices of these products. And then we have the total say's value for every product and every say which occurred on any particular date. And we have data from January 2015 to December 2019. So it is a five years data and no, we can compare this data. We can analyze this data in various ways, so until you have some question in your mind, you will not see the full potential of paper tables because the purpose behind paper tables is to generate data reports, which showed you are a particular perspective off the data. So once you understand your data, for example, in this example, I just described the digitize for five years. Every state's person belongs toe Anyone state we have only three products. So these are some of the key points of order data? No, we have a master sheet. A sheet were re practiced. Someone count in one of the previous videos and knowledge Time toe. See how easy it is to create river tables? Instead of writing these formulas to create upriver table, you should be inside of your data in any cell and you click on data and paper table and you will see this dialog box. As you can say, this is the data range for creating paper table. So Google Chiefs has the taken the total range of my table and which is fine now it is a good practice to create river table on a new ship. So I just collect idiot and know our people table has been created here. On the right hand side, I have people Table editor And if I closed this accidentally, for example, I can click anywhere in my paper table and it will reappear again. No, this is currently empty. No, we have to do three important tasks as you can see on the right hand side rows, columns and values. And at the end of this video lecture. I will also show you how to use filters inside the paper table. But these are the three very important and essential task. Without these, there is no purpose off a paper paper paper table is empty. So instead of just creating through paper tables and wondering here and hear what I decided for this luxuries that I will show you. So this is the first paper table which I want to create. So if you look before hand on this table, it will be easier for you to understand when I am doing some actions to clear this table on this table. As you can see that in rules, I have sales person in columns. I have my products and the last column is added by the river table Grand total. Similarly, at the bottom river table is adding a grand total rule. So you don't have to write any formulas. People Table understands that you are looking for some summing are some counting operations . Therefore, it is stating these two actions to create these grand totals. What does this data is telling me? This is a general record with names in alphabetic order off all the salespeople, then the second thing. This is the grand total of each individual sales person. So this stays person has sold product of this value part B of this value and grand total is this one similarly grand total of each product is also shown here And what was the total sale of the sport during the last five years? However, on this chart there is no information for date State are no information about unit soared. So on your pivot table, it is not compulsory that you will be putting all the information from your master data because this is not what we want. We want to look at the data from different angles. So therefore we're using paper table. So in this table, I want only this information so that I can quickly see at a glance, hold a sales person are performing how any product is performing and then I can dig deeper and see our product is performing within a state. Ho says person is competing with other salespeople. Video estate are on country level. So this is how we should be looking at river tables. What is the question we want to answer for So this is the first table we will be creating. Another paper table we will be creating in this video is this one. And in this table we are looking at a number of units sold by any particular says person. This person has sold the maximum units 53 now the next to paper tables we will be creating will be these on the left. Inside we have best seller For a person, every rule is specific. For a sales person, for example, we can see that the first person he was very good our selling product be because 61.2% off his sales are coming from product Be on the right inside. What you can see is that we can see how any person is competing against others. At a percentage level, 4.5% of product. He was sold by this person. So the best person for product it waas this guy. So this is an other angle for us Toe. Look at the data. No, in this pivot table we can compare the sales off every product with the state off Australia so we can see that in new South ways product be of us saw the most while in Northern Territory products Iwas sold the most. So this is another way of looking at the data and in this table were very interesting comparison off every sales person against others within the state and on the country liver , for example. In new salt ways, we have six sales person and and we can see there to hope much percent age. Every person has contributed to the total sales, including all the products. So this is the total sales figure. So this could be a very interesting information we can compare within the state. And no, if you look at the grand Daughter, we can also compare at the country level. So the purpose of this light is to show you that when you are creating what tables you just creating you are just collecting. Few buttons are paper table will be created and you you might be tempted to submit all of your paper tables to your boss. But this is not a good idea. For example, this table this table looks a ridiculous What is this 100% 100% on both sides? It can has nothing, although it still can with some information at the bottom. But there is no point after creating such tables. And no, we start creating river tables. So here we have three important thing rows, columns and values. So I just click on Add Rose and I ad sales person in my room because this was in our table one and in columns. I want to add products likely Gone act and I click on product so it will add these products here and saves people in rows. No, I have to add value and what values I want to fill in these cells under each column and in front of each row. The values I want to book are there, Total says, which is the most important value here and know our table one is gated and we can immediately see that this person waas unable toe sell any of product air during the five year period. So there must be something wrong going on here so we can quickly pinpoint such problems and because I have already explained these tables. So I will quickly go toe creating table two and you will see that how easy it is and no, let's say I want to see instead of the total sales how many sales were made. So to do that instead of this some here under this total says I can click on that and I can just select this count. And no, I can immediately see how many sales were made off each of these products. So in other data there were a total of 42 sales made by this person. 16 of products see, 23 applauded B and three year project it. So the person who made the maximum number off sales is not the person who made most money. So this was the last person who made most money in the previous table. But this is the person who made most number of sales. No, if I want to see how many's units off each product were soared, so what can I do is instead of this total sales and count, I can just delete this and I can select another perimeter, which is this sense para meter. And if I click on debt, no, it will show me how many sails off individual products were made. Because if you look at master in this sale, this person sold three of products, see the same person sold five approach would be in this say, And when we can't total sales. So this is total safe some of their total sales. But when we count, we called this s one say so let's go back to the total sales. So I add again total sales and I put come to show you the difference, as you can see here that these other total says which total to 5 84 So every event, every single event is one sale. But in each cell we have three a proxy, for example. So we have 5 84 rows here. So this is the difference between changing count in total, say's and removing this and adding US sales because that sales is one off our columns in our data. So make sure that you don't do any mistake when you are dealing with paper tables, because every value gives you further option to select some counting average maximum minimum, so you can select all these from here as well. So before I create a new table, I want to show you two things. For example, let's say you want to change the range of your paper table. So if just crawl up, you will see range here. So this is my range. So if you click on this, you can change that range toe any value. For example, if you had a new row, you can say 5 86 But if you want to select from the sheet, then you can just click on this sheet and then you can against left anything. So the next thing is, for example, if you want to change the order of these people instead of from A to Z. Zito, you just change this from ascending to descending, and it will change similarly for product, you have to change here. You can change from C toe he is with Just click on this and it will change here. If you don't want the totals to appear, then you simply unchecked these boxes and this will remove their total. So if I am check, business will also remove this talk so you can see various option here and you can practice these. So let's then these own, because this is mostly the purpose of over paper table so that we don't have to pour some even counted for more and no less scary a table for where we want states in other rose and products in over cooler. So to do that products in our column is fine. Already there from Rose. Just remove these sales person and act stick on. No, we have state here and our products here because I have selected these count where you so Therefore it is showing me the count. If I select some value, it will show me that say's So this was over Table four No, in our table five, we have our stairs in our columns and sales person on the rows and then we compare based on the person, so see how we can do that. So let's change this. We want our states in the columns. No. So what we can do is we can drag the stairs from Rose into over columns area just like this , and no overstates are here and in our rules, I can remove this product from over columns and in other rules, I can ad sales person and no, we have says person in the States data, but we want person page here so that we could quickly compare. So to do that in total sales area in front of some, you have this deformed. And if you remove this dee port, we want the percent off cola. If I click on persons have called him no, we have our total say's divided into person page for every sales person. So this is person in the news outweighs this person is a new sort of. It's so we can compare these two people that hold much contribution they're doing in total sales, will you from this estate? And if you look at the right column, we can see that how much contribution this person is making in total sales of the country. Let's talk about the last chart, which I showed you not toe show. As you can see that we have only one item in each role. And this is because every person belongs to only one misstate. And this is the knowledge you should have about the data that one person belongs toa one state only, So no If I showed this as percent off rule, that will be useless. If I click here, you will see that it was 200% in every rule because every person is alone in that room. So there is no point of showing this type of char, So be careful. So these are very powerful tool toe show and analyze their data. No, The last thing I would like to show you is for example, I want toe filter something. Let's say I want to show their data off only a few of these sales people so freaking We have already discussed in another video. So I'm not going in the taste. I just want to show you how you can do filtering inside that people table. So just click on act in the filter area. This is filled area and for example, I want to fit a sales person. So what I can do is in the sales person poetry. I click on this showing all items because currently it is showing all items. If I click here. No, I have a list of all this is people. They're all checked. And if I want toe not show for example these starting from a I just uncheck them. I click OK, and as you can see, all the people starting with a have disappeared So this was the finishing incense people. You can go filtering instead. So if you add another filter about state and you only want data far, for example, new salt with you can see showing all items. If you click here, you can uncheck the rest of the states except the new salt viz, and you will get only data about use. Outwits click. OK, I get there are only about news, outweighs and because in New South Wales we have only these few sales people, and we have removed all the sales people with names starting from a as well. And there could be someone from new salt with removed with debt filter. So when you are using filters, be careful that what you want, because when you use filters in combination with river tables, then sometimes you make mistakes. So this was it about paper tables. I hope that know you understand, or to create a pivot table photo, change its size, are your location, or to change its range or to use filters and how toe look at your data from various angles 16. Methods of Inserting Images from Google drive into Google Sheets: In this video, you will learn Harto insert an image in Google sheet, and this is a brief summary of what we're going to learn. We will see two different methods. Fast is the normal method by just clicking on inside and then image. But the second mattered is very important on which gives you more control on the where you want to insert the image. And this is by using an image function available in Google ship. And in this method, I am going to show you two different ways to insert that image. The first matter these from a website and the second method is hopeful insight and image from Google Drive into Google sheet. And when you insert from Google Drive, there are further to method for this. One method is by using insert in a cell option, which is a new option in Google sheets. And the second method is Barrick Lee, inserting from Google Drive by getting ah file I D and using a link of Google dry. So let's get started, and I will first quickly show you this inside image matters, which is not really good. So inside image image over self No This is a new option image insult, and we will talk about this in a minute. But this is the normal inside image method, which we were usedto use before, and I will just click on this and know you have the option toe. Upload the image from the computer from any u R l are You can take a snap short from your album from your booger dry exit, but since this matter is not very good, so therefore I will just a Plourde from my Google drive. So let's say I upload and in my images area, I will select one of the immediate solar See this one and I click on select and know This image appears here, as you can see on these images over ourselves and we can resize it larger or smaller, and it will stay here and we can still work inside the cells. And if I press my up arrow off myself as you can see that I can still type behind this image. So this just stays over the sets and if this image is not transparent, you will not be able to see behind. But still you can work. But I'm using a transparent image here. So this matter is not good, because when you are inserting an image in sheets, it means that you are inciting in a selling because this is the best way to insert an image so that we can use it as part of our fritters as part of our conditional logic. For example, if I'm looking at the products off one company, the logo off their company appears and sore. So therefore, we want to inside image in the sell. So this is mostly the purposes off inserting images in Google ships. And no, I can believe this. No, let's look at inserting insult. So if we insight and image image in cell and no, I can get an image from Google Drive, for example. So let's click on Google Drive and again I go into my images folder and I collect the same image. I know as you can see that this image appears here. If I am in myself, I don't see anything in my address bar, and if I change the height of the rule, the image stays the same. But this image is in the safe, and this really be part off over logic. If we are using logical formulas and legs under this rule high. So this was another matter. No. Get more control on inserting images we can use Ah function card image and I will click here in any cell and this time I really use a function card. Image is equal to emit. No, this function takes the u R l of the image. So if you are inserting an image from a website so less I open my website. So let us as you might want to insert this image. So I right click on this image and I click on copy image address and this is the way you get the Ural off an image because this image is stored on this Web site at a particular location. So I click on copy, image, address and no, In my formula, I can write leak and I can taste that addressed address off the image and I have to put the quotation marks on both sides, so I should have put the quotation marks before, and then it should become green. And no, there is another perimeter of this function which we can apply after a coma. We can insert four numbers here, 1 to 4, and I really explain what does it mean? But if we don't insert any number, the second perimeter off image function is optional, so we can leave it. And we can simply close the bracket and press enter. And no, as you can see that this image appears here. No, let's talk about if people white option one. So I press enter. And option one is that before adoption. So there will not be any change to this image. Know what option one are there different option is doing. It is securing the proportion of the image. Let us say that I change the height of this rule so it will fit the image by keeping the proportions and it will try to fit in the cell. So this is all this depart option work. There are three other options. If I put two here and press enter No. If I change the Rue High, you will notice the difference if I change the rule right now, as you can see that no proportions off the image are not kept and the images stretched and it has become distorted a little bit. So if I increase the column with, as you can see, their system is trying toe. Adopt toe this size but not maintaining the proportions of the image. Now what about option three if I put Option three, Nother image is off original size, and you can check the size of the image. If you click on the right hand side and click on inspect here, you can see the size of the image, which is 6 21 pixels, and height is 2 53 picks is so this is the size of the image. So, no, if I change the width and height off this cell approximately, these values a little more than that. For example, let's say I changed, ripped off this cell toe six type e and hide toe to 60. Then I will be able to see the image because images in full sized when I am using Option three. So let us join this cell to the image size greater than in my size to see the full image so we can do this in two parts. So let's change. Click on this be were being written right click resize column So this is the wit which waas 6 20 threes. Or we can say six therapy and no click on this 11 right click resize room. And no, we can give a little bit larger and to 60. I know our cell is off image size, so therefore we can see the full image. So this is also useful in some situations when you are detecting the size of the file, changing the size of the rows and columns and putting the images in. But the image will be in full size. No. In option four, you can actually, for white the height and width. So let us say I make it 501st have to provide the heights or 2 50 then with, as you can see on the left hand side and I pressed enter No, the image will be placed off this size two fifties. The height and 500 is the quick as you can see that it is not occupying the full width. After all, these were the four options off this image function, and no, let us assume that I want to place animals from my booger droid, so I just close this website park and open my Google drive in the Images folder in Google Drive if you want to check the size of any email, So let us say I want to check the size of this image. I just click on this image and then I click on these three doors and click Oh, details here and on the right hand side, it will show me the dimensions of the image, width and height. So this is all you can check the size of damage? No, I want to put this image from my Google drive into my Google sheet. No, we have seen one matter off inserting an image from Google Drive that waas inserting in the cell, which is a new function available. But there is another matter which we can use to insider image from Google Drive by using the Ural and by using the image function, and for this method, we have to perform two steps. The first step is that we have toe share that image and give it public access to this image . So anyone with the link will be ableto access the image because if the images private, which is the default option in Google Drive. Then we won't be able to put it in our Bogle shit. The second step we have to do is that link which we get from the Google drive. We have toe modify that link a little bit. So as you that I get this link from my Google drive. So I have to remove this part. There will be a file slash b slash So I will remove this part. And at the end, there will be something like this sharing because I will be getting a link to share. So I just removed this part and then this in between these two red letters, this is my image idea. And this is what I need. Toe put my image in my Google ship and know what I will do is I will simply remove these to read parts from my link, and I will copy this and then I have to paste it at the end. And before that, after google dot com, I have to write this. You see, question mark, export equals view and ID equals this. So this is what I have to do. So let's do it. So this is the image I want to share. So I click on this image and then I can check the size if I want. Otherwise, I can just click on share and in Ad wants. As you can see, that currently spry Vicky who has access private only you can access. I can access only so I have to change this. So let's change this. And I give a public access and I click Say no. I got this link so I can copy this link. Copy, We're done. And now here is the court, which I have to replace after politic that part. So I paste a link here. I believe this part after this point and then I delete the last part as well. And before the image I d. I just paste this court and no, everything is ready. I can copy this and no, I can baste it in my image function. So double petition marks. And then I put my cursor inside and control week And look, I can make use off any function. So first, let's put by before but using one and no, as you can see that my image appears here and know the benefit would be that I can use other option. After putting comma here, I can put option 234 if I want. And you can get full control off your image in your Google shit. So these were some of the matters off inserting images in your Google sheet. We have looked at Toto inside image from a website, our hope to insert an image from a Google drive to your Google ship. 17. Understanding The Most Powerful Query Function in Google Sheets: In this video, we will learn the most powerful query function which we have in Google sheikhs. It is our function which uses a query string which follows the structure off SQL, which is structured query language also card sequel are structured English query language, which was the old name off this language. So there are two things to understand here. One is that query function is a Google shoot function, and questing is one of the arguments of this function. So this argument is a built by following SQL language and this is the important part to learn. So this function takes three are women. The first is a range, then a query string. As you can see, a language statement which has the ability to talk to your database are your sheet and extract are pulled some data, and the third part is very simple, which is a number, and this number represents. Stop Had a rose number off her the rose like this is my data and I have only one had a row here. So you have tow tell the query function that data which I'm selecting this dude out has one had a rule. Are no Heather room are to heather rules. So if I select from here, let's say and go to the end then I will say it has one header room. If I select from Roto then I can say it does not have any had a rope tow The number is zero . No, these query string can be very intense and complex And in order to get full understanding, you need to learn a whole language. SQL. And this is not possible to cover all the key worst which you can use in your query string . But in this video, I am going toe cover these keywords six off these and toe these keywords. And if you master this key words, you can do almost anything in your Google sheikhs. These are so powerful and so flexible and you will see when I do the examples. So the first keyword is select every query String must start with a select. He work very secured. We checks the following condition. Whatever is return after we're contains the key word which we usedto match apart off a sentence are and he text. If you want to find something that match something order by As name suggests, you can order the values in ascending or descending orders, and then you have to use these keywords for ascending or descending in your query. String grew by makes the group off any range and some in some the values, whatever is inside the some. And you can also use this with combination off some conditions. And those conditions can be specified by, for example, group that some this group by this. So it means that only some money after making a group. So these kind of things, the better way to understand these keywords is by doing some examples. So let's do some example and try to understand very strength. This is over data and we have been using this daytime through videos. So you understand that this data has 584 entries, and at the bottom you will see three tapes inquiry Deb, we will be answering some of our questions and we will be pulling and extracting some information form over master sheet from this one. And in the death sport, we will be making a death sport from Google shares, which is the professional way of presenting gator toe. Someone to some important person such as your boss, and they will be ableto see the data from various perspective by changing certain values, and we will be doing this in another video. But in today's video, we're getting some information from this data by using query function in Google sheets. And before I started doing the function, I want toe show you, What is my plan today? So the plan is on the first row. I have returned a little bit of explanation off my first query. I'm writing that this query will occupy three columns and this is a good of a tow plan before you do something that how many columns your query is going to occupy. So the baby right query function is the right query function in one cell. So all these green cells are the places where I will be writing query function for this particular question, which I want to get answer for. So this is the place where I will write equals query. And then this is the cell, which is the tired cell in any column where I will be writing my query string. And as you can see that I have highlighting this with blue collar toe. This will be the query String this one of the query string for this And here I will write the questing for this query and similarly so on. So the first question is extract all data off any party coolers days person. So all data in three columns So what? We should put so just go back here the first day. Ties, of course, is person the name of their sales person. For example, I want data for this person that second data could be product data If I want this data. No, I have decided that I will be pulling column b here column e and column G since our data is very large and we will be selecting this data so I would like toa give this whole master sheet a need. So what I can do is I can select all this data from this point upto the end and I best data and named ranges and I give you the name off Master table, this is my master table are Just call it master Baiter table. Be more clear because we're using sq it and no, I have to write a query function toe. Get answer for this data. I have decided that I will be pulling three columns from my master and those will be column B column e and column G So no, I can write my quay function here. It's ah old style of this SQL language that you write in capital. But it's not essential. If you want to follow their tradition, you can write in capital letters, but it's up to you. I will be using mixed approach wherever. So this is my query function. So the first argument is the range and which in my case, is Master Data table. So I just start typing m years and it will show me here. Monster did write a book. The second is the query string. And as I mentioned, I will be right Inquiry string just here. So I like this cell comma No, The time is the number off headers. So since I selected this table from the top So I said that I have one header room, So that is it for my function. And as soon as I put here, it extracts all the data. It takes text all their data because it finds it empty here, a trees empty at the moment to ignore this for our second, and we will be writing over Query in a tree and no, let's write our query in military. So I would like Toa Select B, E and G, which we have decided. Columns be E and G, and then I want to select this person. So for that I will say there, this person's name is in column B here. So I would say where b contents? No, I can type in lowercase as well. And then when you're comparing a string, you have toe right in single quotations and then the name of the person Evan Worsley, Quotation clothes and enter. Actually, it's Kelvin Worsley. And as soon as they correct the spelling, I got the data about this person only. And if I scroll down, I can see that I have 44 in which this person has made a sale of any of the products. So this was our first query. So as you saw here is my query function. I'm using this whole table and my string is coming from a tree and at a tree I'm writing my query strength so the same approach will be used for all of the following inquiries. So let's go back to query number two X Tex says data off product E for New South Wales. So no, we only want to extract data for product it. So what did everyone so in the first? True, I have made a suggestion that the query should occupy maximum five columns because I have left five columns for this query. So therefore no, I have to select which column I want, so I will look into my master table for product. It means I have to, of course, extract this where I have to put a condition as well. Then I am looking for new sort feels I have toe. Also expect this from my date of this column C so C E and I can then extract column B and daughter says So let's extent B c e n g first type the query function and we say that the ranges Master data table you will get the query string from B three and headers are header rows are one only that's it. It will extract all the data ignored this No here. We're going to write our query for this question. So select so B c E g b c he g No. We are putting two conditions here. Product A and new sort feels so. Therefore we have toe say we're See, we're see this column. His new salt is so we're C equals in New South Wales. And no, we have to put another condition. So we pulled this condition inside. Parentis is no product play. Which column has part of a equal? Um e So e contains a very simple and he contains is that sub string and we press enter and know we have this data. As you can see, there are all the saints people here, but the product is only a and the state is only knew sort with. So from this master data, we have extracted the data for New South Wales for only product. So this is how this query will function. Let's move to the next query. No. Let next query is arranged. Product A data with respect to single sale value for new South ways. No single sale values means in this column what is the highest sale value, but that Well, you should come for New South Wales only. And no, we have the data for New South Wales here, so we don't have to go back to master. We can also use data from this part, this part of the ship. So what we can do here is we can t find another range for this data which we have got inquiry to. So we can slept this data from bare toe this point. So we just like this data and we stayed data named Ranges and we name it as for ordered e news out ways so that we can use this in next next way. So product a data with respect to single sale value. So what is the highest sale we should put on the top and then in the descending order. So no, What will be our function here? First we will say that is equal toe wary. No. Which data we should use? We should use product in new software. So be And it will show us here from wherever query string will come. Will come from this point. I three and we have chosen, including the headache. So header rows are one and No, we have to write a query here. So as you can see that it suggests we can use three columns so we can use any three columns . Let's take this color be and then f energy. So select be if g no, we have toe order these data in the third column G So we say order by G who ordered by G This is the key word. Three Georgia descending order. So the E S C This is another key work. Enter. I know we have these three columns and we're ordering g in descending order order by G descending. So this was this query? No, let's move to the next query. It says X tech total Say's off every sales person in the country. Now we're talking about country, so this is the first thing you should be looking for. Which data we are you going to use whether we're going to use the master table are we can use a sub table. If you can do the sub table, you must use their sub table because you I don't want processing time toe unnecessarily long. So here we're talking about countries, so we have to use this master table. Let's write down the query function is equal toe query. Which data? Master? Start typing, Master. It will show you. Master Former were to get the query string always from tired rule. So l three coma. How many headers? One headline and ended. It will pull all their data. Ignore this. No, we can occupy only two column. This is the suggestion in pastoral. So off course, we are only looking for sales person and total said so in the query string, we will only extract to select which column had in the master table. Which column has the sales person B and the total says G. So B and G be comin, G but no. Here we want toe some G based on a particle er person know this query is a little bit complex toe comprehend, and I would suggest you toe practice this so that you understand how it works. But I am trying to explain you as best as they could get says for every of these persons or , for example, this first guy. So what we're looking for is wherever we find this person, we want to add the say this will you bless this value, so we actually want to some G column and trees after grouping them with be column entries. So all the big column entries First group Calvin Dworsky in this be column. And once you have group that, then you add all the corresponding and trees in G corner. So some G and but Group B. So this is the understanding you cannot get like right now until you practice a little bit . So let's go back and see how it works. So we're going to say some G not G, but booked up some of G, which is based on group by, be planted. So know what it is doing is it is trying toe group B and then some. All the entries in front off each group in the G chord. So this is what it is doing. So as a result, we get individual sales people and their total sales so extract photos is off every sales person. So as you can see that hope flexible is this function and hope powerful is this function that by just typing these two lines of court, you have the ability to extract all meaningful data fully arranged. No, the last where he is. We want toe. See who is the best sales person in this group in this group. For the country? No, we have country data here, so we don't have to go to the master table for this query. We just have to arrange this in the descending order. Very simple. So this is the last task and you should pass the video. And you should do this as your class activity. But I am going to do this so you can pass the video. Do this as your activity, download this file, do this as activity. And then, if not, then you come back and watch the rest of this video. Let's do it. Okay. So to do this, we invite a query function here and we say that our data will be only you see, I'm not naming this time. I'm just using simply arranged from there to there. So I will be adding from the top from this head it so that I could put header equals one. I can also select from year and you can practice. This s where so But I'm following the same patrons. So I like this part and my query string will come from and three this time and a number of headers one and enter No, our query stream. So I would say select these two columns column l and M l Coma em. And then I said, order by order by I m this one this I want to order See? It's he toward it Be sending order. I know I got up best says person in the country. So as you can see, they're not only one person. We have arranged all this in the order by using simple line, of course. So if you count from here to here, there are five. Where is a lot of meaningful data extraction? Only 10 lines of court SoHo Powerful. Is this query function? Know what you can do is, other than this class activity, you can make your own question and you can say, OK, my number six query will take three columns. I'm going to extract top sellers for product. He only then you see hope you can go with this and you can practice. So although it's not possible to cover all the powerful keywords which you can use in your query strings. But if you know these few keywords which I explained in this video, even then, you can do almost anything with your Google ships. It is such a powerful formation, and if you guys are interested in learning more about this, put me in the comments, and if I get some comments, I will add another video using some more keywords about this. 18. Index and Match Functions and their Combination: In this video, you will learn toe very important functions available in Google ships. One is index function and the other is match function. And most of the times we use both of these functions together because of the way they work . They try toe, complement each other, and you will see in a minute when I will show you an example. So first, let's see how index function works and what it returns to us. So I have written this statement in a simplified form. Index function returns that data at the intersection off a particular role and the particular column in a given data range. So we have a data range consist off few rows and columns, and we tell the index function that we want whatever is written at a particular role number and column number. So as you can see that dysfunction takes three arguments, the first is a range and then the ruin column number, at which we want to get the value. Whatever is return at that location. Some function they don't return value de return opposition number, for example, the match function. So what match function does is we give this match function again three arguments. The first is a searched. Her Mr Match function tries to find out, and then we give it a range in which it will try to find out this search term and this range must be a single rule are a part of a rope or a single column are part off a column so your range can only be a part of Aurora Paula. And then the third argument is such criteria. And this argument can take only these three values either 10 R minus one. So the simplest off this option is zero. And this is what we mostly use because this will give us the position after the exact match of the search term in this roar column. So this function returns as the position number. Know what is one and minus one? Options for the such criteria. If you give option one, then the function will return the largest value, which is less than or equal to the search term in an ascending range. So ascending means from low to high and descending means high to look. No, the system will assume they're the range which you are giving toe the function is in ascending order if you give Option one and the system will try to match and give you the position. But that could be an approximate match as well. For exact match, you use option number zero So this point about option one and minus one. You must always keep in mind that the system will assume that the ranges in ascending or descending order if you are giving one R minus one options in the search criteria, respectively. So index function returns the value. Whatever is returning. A particular ruin column intersection and match function returns as the position number for a search term in single row or a single color. So now let's see how we can use dysfunction. In an example, though, this is our example sheet and the example which I am using is has a desert off some students. If I scroll down, you can see that there are 50 students. Some of them are passed, some of them are failed. And the scenario is that whenever I type a name off a student here, I should get the result in Cell C two. So I type the name in Selby to and the result of the student which is in the column. F should be tired automatically in C toe. For example, if I type violet northern in be too soapy should be automatically printed in cell C two. So this is the purpose. No. You will see how we can use index and marriage function together to get this functionality . But first we will practice them individually. My ranges from this cell Selby to upto this points l f 53. So this is my range and because we will be using this range again and again. So why not just give it a name and to name this range I click on data and then day aimed ranges. And then I give it a name for example, my dessert table or any other name. And I just double checked that ranges correct before 12 53 which is correct because I want to include from the first name upto the last two result. Which, as you can see, is this cell which is F 53. So after double checking, I click done. And no, I have a name for this range. No, I just think about problem in hand. We want that? Whatever name we put here, we should get the result in this cell. So no, think about this. Range now The first column off over ranges. This one, this is the second caller. This is the third column. This is four. Tend, the desert is in the column number five. And if we look at index function, what I want is that I want to give this function my range, my dessert table. And then I want to give this function the wrong number of the student. And I want to give column number five. My column five is fixed because the result of which I want is returning Column number five . So I only need this rule number for the student because I will be putting random name in Selby, too. I will be putting any name here. So whatever name I put here, I just want to know what is the position off that name in this column. For example, if I put Jenna Santo's, the position of Jenison Toes in this column is 12345 So I should get five and then I can pass this rule Number five and column number five to my index function. If I give index function range and Rule number five and column number for you, the index function will return me the desert off generous and does very simple. The only problem is that I don't know the rule number of Jenna Santos. So therefore, I will get this rule number from match function and I will tell match function. Jenna Santos is your search term. This column is your range in which the name of the students are written from before Toby 53 the such criterion zero. You tell us what is the position of Janus and does and the function will return us. Position five and we've been passed this position five in the rule part of index function and we will get the desert very simple. So it means that in this rule, part off index function, I have to book a match function match inside the index function, and I will put this whole match function in this row position second position off out of three positions in the index, it has three position, three arguments. So in the middle argument, I will put all this merch function and dysfunction will return the row position off. The student and index will return that stuff the result of the student. First, I will use match function separately and then I will use index function separately by putting the value five and five and then I will combine these two and you will understand everything crystal clear. So let's do it. So to get the wrong number of Jenison toes, I can click here and I can say is equal to match and then I have toe put three arguments fasted. The search key and such key should be in quotation marks and spelling must be correct. And no, I have to give range, which is be for to be 53. You can select as well and then the such criteria is exact Match center and the system gives me five because in this way the position off, Jenison told his fight. No. Every Jew's index function so is equal to index, and I have already said unnamed range, which is my my dessert table, which is here. And if you don't know how to use named ranges, there is a video for this so I can select this named range, it will select all the range and then I want toe Get the day tight at road number five and column number five off this range. And it shows me f which is Rule number five, this one and column number five. This one. No, the only thing I have to do is put this match function in the row position. He it and my job will be that. So here I can write. This is Equal Toe Index and the ranges. Same my dessert table and then I have to write the match function match and inside the match function. No, I don't want to put Jenna Santo's because I want to put this cell position be too, so that whatever I write on B to the match function will take the value off whatever is returning B two and then find expedition. So therefore, I will type B to here and then the range is before Toby 53 the criteria is zero. No match function is the second argument off index function and know the third argument will be always five. And if I press enter currently it is showing not applicable editor because nothing is in be toe, sell the lettuce first type here generous and goes and then we will see some other tourists . If I press enter, it shows me f which is correct. No, let's type any other name. For example, let's type Chrissy Nelson this name. When I press enter, it shows me f because this is also ah, failed student. So let's type some past student like this one when it's a cook and it shows us pass. So this is how we can use index and match functions together to get this type of functionality. So let's have a look. Once again, index function takes three are women's. The first is the range which we have named then the second argument should be a rule number , and that is a position which we're getting from match function. So from this comma toe, this last comma match function is giving us the position off a student whose name is written at B two because match function takes three arguments. The first is the search stir and in start off, giving any particular search term. We're saying that whatever is returning Selby toe, you take that as your search term and then we give a range of student names, and then we ask for exact match toe. The match function returns as the raw position off that particular student whose name is at B two. And when this role position is Pashto, the index function and the column position off index function is fixed. The index function returns us whatever is returned at column number fight in front of that rope. So I hope that this makes sense and know you understand how to use index match function together in situations like this. No, the last thing is that you can pass. Many types of values toe these functions. So this is the practice area where you have to practice. For example, you try to pass a date value to match function and try to get the position off that particular debt in a column So this you can practice yourself. But once you know how these two function work, you should be able to apply this in many situations. When you have their situation in front of you.