Welcome to this new video, I am Miguel Vela from your channel El Tio Tech and today we are going to learn how to work, look at the check lists or also known as verification lists and now look, I want you to notice that I have two examples, one in reality it is quite basic and the other one could be said to be intermediate look at the check list it works as follows if I click here look that it automatically paints not in the same way I can click on all of them and they will all be painted if I unchecked look remove the color again no and in this way we can make our work more interactive this is the basic example that we are going to do after that look there is another example that could help you suddenly like making a filter but with colors right to right here I have look at three categories accountant and administrator systems if I click on systems it is automatically marked look at all those who have systems because all those who have systems was unmarked obviously it is removed again the same thing happens with counters look there they are all counters and it marks them in another color I can't also mark all of them look if I wish so for example leave only administrator I remove it I remove it and just look I have an administrator, I remove it again and it disappears, that 's not how checklists work in combination with conditional formats, well this is what we're going to do today, I'm going to close it, I'm going to give it no save, and look, I have material here. it's empty so you can download it download the link I'll leave it in the description of this same video download it so you have the texts and don't take too long to write it down no now we're going to start look we're going to start with the simplest ok so that it only paints the cell for us, the two cells that we are marking well and what we need as number one is to have it enabled, look at this tab that is above it is also more s or less in the center of Excel it says programmer in other versions of Excel you may find developer as programmer or developer it's the same thing if you're not looking at it here there's no problem you can activate it look we're going to go to the left top where it says file a click on file we are going to go down to the last one that says options we click here on options and it will jump look at this little window ok then on the left side look here there are some options we are going to go where it says customize ribbon or one click here person ribbons on the right hand side look we have two columns this is the first one and also here are all the tabs here main tabs the ridges that are normally located in excel up here look here we have it a all of you will surely find it unchecked simply mark it with the same with the checkbox, click the check list that we are using no again I gave you go it is possible that you are as a programmer or as a developer, mark it, activate it and give it to accept and that way we will have the programmer tab once we click on programmer and developer we will go here where it says insert a click here We have two types of controls, the form controls and the activex controls, the active ones that work with macros with codes, so we are not going to see it, we are simply going to see what form controls and the check list say, here they call it as a box, they can also call as check box and marc I don't remember what else but it has different names not here it is taking it as a box they also call it checklist here it is also this one that says box we click and we are going to put it here look there we have it already here with a click just a click we are going to delete your text delete in the text delete we already delete it we are going to make it a smaller square and we are going to put it more or less in the middle there is no vamo s to copy and paste right click copy let's paste it right click paste let's see let's see control vee control vee ok control let 's paste it with control vee and so on control control v ok control c control vee here too control c control vee control c control uve and there we have it so that they are all aligned vertically and also distributed vertically with look we are going to click outside and with a right click to select 1 right click after that with the control key the control key without releasing the control key we are going to select look at all ok to align them we select them all then we are going to go here to shape format and here to the right look it says align a click and we are going to align vertically we align vertically they are already aligned now we are going to click again in align and we are going to look for this option that says distribute vertically well we distribute it vertically and now if all are already aligned and distributed vertically well now it's coming just look it's just coming the most interesting thing how these checklists or checklists work as with that you know how they work right click right click on the first one you go to control format control format and look here it says link it first thing you get is to link with the cell you click here and then select the cell with which you want to link normally it is linked to the cell that is next to it either to the left or to the right that depends on you me I'm going to link you come to that link in the column and I accept the same thing we're going to do with all right click control format I click here where it says link with cell and selected the cell that is on the side I accept right click format of control I click here and it selected the cell that is next to it again right click control format I click where it says link with the cell and next to it it was selected I accept look here some h are missing we do the same we do the same right click control format click on link with the cell and click on the side you accept I want you to observe that now if you click on the box here you click on it look that on the left it appears true to select now it appears false true false true false the same happens with all and the same happens with all then the true and the false will be a reference to know if it is pressed or if it is activated or it is not activated when it is activated true when it is deactivated false, that is very easy to understand no now yes just now we are going to put the color the color we are going to put it with the conditional formatting we are going to select the cells look we are going to select the cells that we want to be painted in this case are all these cells we select them we go to the start tab start tab and we are going to look more or less in the center of the additional format in the conditional format we go We are going to click and we are going to give it where it says new rule we are going to click on new rule and this little window is going to pop up we are going to go to the last option that says here use a formula that determines the cells to apply format then look the only thing we are going to do here is here where it says to format the values where this formula is true then we are going to put the same and we are going to select here look at the first value ok it is in cell 3 here we select the first value only the first value for the conditional formatting to go through all these rows, that is, row 3, row 4, row 5, row 6, row 7 and row 8, we need to delete the dollar sign, we need to delete the dollar sign from the number, no, look to let them see a little more clearly here well here look what we are doing is put the same we can still call column a column a and here look at number three what would be row 3 that we have here row 3 but if we look Yes we don't put the dollar sign before the dollar sign before the letter so it's going to go through it's going to go from abs to that's why we put the dollar sign before the letter ay because we remove the dollar sign before the number because we want it to here if it goes through that it goes from 3 4 5 and so on that is not why we eliminate this and look there we have it not like look here if we if we put the same here true it is exactly the same ok if this detects it look if that detects as true then it will be fulfilled it is not necessary to put equal to true it is not necessary then we simply leave it there and format the color look I am going to get this the format the color that you want put here format and we look for any color by example this one accepted I accept and it marked me that it is already marking me marco marco marco marco marco look that simple you realize now if it is working for me no you can hide this you can hide it or the easiest thing is also the easiest thing is to paint it white look and it's already working for me not like that the check list works well let's go now with the following example look here the following example with a with a check we should determine what's going on pin what cells are going to be painted we could not work we can work with charges, that is, look only to get the charges that are not repeated and depending on the charge that is painted we could not work for it is for example the ages that are not repeated and we put it to the side and for example if you press on 22 all 22 should be painted or also by names well for this case we are going to work only with positions I am going to select everything right click copy and I am going to paste it wherever I want next to something I know right click to see again right click copy right click paste and I'm going to put values that right click paste look special paste and values in case suddenly there is some formula I don't know and you s may have problems values there we have it look let's see let's see let's see what happened it has just been painted let's see right click but special values there we have it yes what happens what happens let's see I think the conditional format is taking me from the above I am going to delete it or this I am going to delete it well or this selection acts when you see this sheet I am going to delete all this that I am going to delete I am going to delete it and then delete from this you should not do if you should not do it no do it right because you will have already had it when downloading the material as you are observing it ok again ok we select the entire list of charges right click copy and paste it here not paste special values there we have it what we now need to do is to select everything look we are going to select everything and we are going to go here to data to the data tab data tab we are going to remove duplicates so that there are no duplicates one click here remove duplicates we make sure that and this option is not marked that they say my data has a header that is not marked and it will not be ignored look at the first gay element we are going to select it and we accept it in this way we only have look at three positions that are not repeated we have systems accountant and administrator well I'm going to add add removed I'm going to add add removed and the checks I 'm going to put to the right well here look we're going to programmer programmer insert and here is the check one click we draw it here we are going to remove it its text one click we eliminate it as if it were any text makes it even smaller and we bring it where we want not to the middle to the middle ok control control uve here control c control uve if you want to align it you know press the control key select everything and here in shape format shape format alignment align vertically and the other distribute vertically well now yes we already have it there and look we are going to paint here first ok we are going to paint here first we are going to do look we are going to select all the same procedure we select the cells that we want to be painted do not forget we select the cells that we want to be painted we select them all we go to the home tab home tab and then we go to conditional formatting here in format conditional a click we give it a new rule and we are going to go down and we are going to click where it says use a formula that determines the cells to apply the last option and again we are going to come here again we are going to come here what you see now but I was forgetting something I was forgetting something and activate activate the true and false here look at the green of the false again stop look quickly right click was the control format we already know a click here and we select the cells on the side we accept right click control format a click here the cell that is on the side right click control format click on link cell and here we accept and to test it you have to exit true and false on the side to test it always has to come out true false on the side now if we select the cells we want to paint again we select everything we go to the home tab the home tab we go to conditional formatting one click new rule we already know that we use the last option that a formula is used that determines the cells to apply formatting and we go here look here at the bar what we need now is to ask two questions we need to ask two questions look at the first question here I am going to here I am going to the company look at the first question is that the charge look in this case we are going to take let's take as an example that of look at the one with four that the cell with four is equal to that systems that is equal to systems although it is the first question look that the cell of 4 is equal to systems the second question is that the cell look that the cell and this cell and four that the cell and four that the cell and four be equal to true not so ages and four equals true this true is not any text that's why you don't have to put it in quotes so to apply this format we need two questions that this cell look at that this cell is equal to systems ok the first question the second question is that this is false, that is, it is the false since this check is true to ask two questions and be careful, the two questions must be fulfilled if one of the two is not fulfilled then it does not apply the conditional format, that is, this has to be systems and this also has to be true well that's how it works but so that you can ask two questions and necessarily ask them that the two questions are fulfilled we need to use the and ok it's fine with this and the two questions yes or yes they have to be true, that is, they have to be fulfilled only for culture, this function also exists or the mode function is the opposite, that is, any of e These two are fulfilled and the conditional format is applied but the function and the two yes or yes have to be fulfilled well now we are going to do it look here we are going to do it we are going to put the same we are going to put we are going to call look at the function that although capital letter and we open parentheses and we are going to select to see sorry it is moving a lot I am going to leave a little to see why here is the magnifying glass if it is the maximum 200 already look we can equal it and we are going to call the first cell we are going to calling the first cell a cell is always selected and so that it can go through we eliminate the dollar that is before the number and we eliminate and we eliminate something there it is this is going to be the same look we are going to program one by one it is going to be the same as systems and systems where is it here look here you selected it then this system cell has to be the same look where we have new systems here a little more here now yes we already have the first question semicolon the second question is that this option is marked ion and for it to be marked we already know that we are going to select this cell and we could put it here the same as true no but so that it does not make a lot of formula or it is the same excel if it comes out true it is not necessary to put anything out ok simply here with selecting this cell you are telling it to be also true well we close parentheses we put the format look we are going to put a format to see what color it would be we are going to take to see a yellow color that yellow well we click accept we click accept let's see let's see systems look it's already been painted look it 's just been painted as such and since we are observing all those who are systems look we are going to do it three times so that there is once so that those of us select learn well and it disappears we select and it reappears from we select the same thing disappears we are going to do now with the counter but with another color counter look we are going to do the same procedure we select everything we select the entire group of cells q we want to paint we go to the home tab look and we go to conditional formatting and we go to new rule the good thing about conditional formatting is that you can apply conditional formatting many times to a single group of cells there is no problem you can apply it many times times we are going to select the last option here and look we are going to do the same thing the first question we put the same we already call the function and so that to ask two questions and that the two questions are true we open parentheses we open parentheses we select the first charge cell we delete the dollar we delete the dollar so that it goes through all the rows the dollar of the number we put the same and we match it now yes to where we are going to match the counter ok now we do match the counter look at the counter we do not delete anything eye we do not delete anything or also you could put the text here look here you could put it I could put a counter it's exactly the same but so that they're not writing this all of a sudden here you change you change the name then we match it that simple or we match that is the first question semicolon the second question is that this value is equal to true but as excel had told you if it is true it is no longer necessary that you put equal to true but if you put it the same look , it will work in the same way what there is but I am going to delete it but there is still not much formula well there we have it format we are going to put color on it let's see we are going to put this color orange ok orange we accept we accept and let's go let's try it counter look now if it works systems the counter works systems and administrator is still missing let's do it administrator again the same procedure we select everything we select everything we go to the start tab conditional formatting new rule well we already know we are going to go to the last option and we do the same we put the same we call the function and we open parentheses we select the first cell so that it goes through the whole s the other rows we delete the dollar that was from the number we are going to match now yes to whom we match administrator well semicolon the second question is that this value is true we simply close parentheses and put a format to see this blue we are going to put ok we accept we accept look what happens blue yellow sorry orange and yellow and in this way we have our conditional format with text and there we have it you can also change the color here look we can change the color here so that every time you click it is also painted We are going to do it quickly to see which cell we want to paint all these we select these cells we are going to conditional formatting new rule look we are going to go to the last option because we already know we are going to go to the last option in this case we are also going to have two questions look we are also going to have two questions we put the same we call the function and we close parentheses and we are going to select look at the first question v we are going to put systems ok but but look but but but but we are going to select the dollar sign of the number so that you can go to the accountant and administrator we are going to put the same here and we are going to write systems or you can also select it we are not going to put the context in goods so that they can see that it works the same yes to see the systems and systems the same name that is here eye the same name that is here we close quotation marks and up to the first question the second question is a semicolon again than this value is true then we simply close the thousand parentheses and that's it the first question is that this be semicolon systems the second question is that it be true what color was color I think it was yellow and yellow and yellow we accept let's see see if a click works and look at this too it is already being painted we are going to do the same now out a counter we select everything we are going to conditional formatting new rule to the last option and we do exactly the same thing we put the same look we are going to select the first cell we delete the dollar sign we are going to match now yes to the counter we are not going to put the counter or we could also match it from the front well the libero I am going to match it from the front it equals the counter point and comma and I am going to select this value well the value that is true no we will not put it equal to true because excel already takes it if it is true well color this orange let's see more darker we accept we accept let's see let's see here it is a problem there is a problem that has happened that has happened that has happened I think I think that it is not taking that data let's see we are going to write counter and semicolon we accept that there is a problem he could not visualize it but that's how it is and if we missed it we sorry we missed the and we missed the i and the parenthesis that we missed the i and the parenthesis we accept now yes it already paints us it is already painting us both we are going to do the same with an administrator conditional format new reg we select the last option and we do the same we do not call the function and we open parentheses in this case we select the first cell we delete the dollar look we match now yes I am going to match it administrator semicolon and in the end it has to be true no longer I put true closing parenthesis and this what color it was I don't remember well we are going to put green to see we accept we accept and look now yes blue it was not to modify it to modify it look we are going to select all this we are going to conditional formatting manage rules ok here it has to be right select current gay select current up here and this green everything is click format was a blue milestone we accept we accept we accept now yes look it's working fine for me and this would practically be all I hope you have understood that you have understood me if not, ask me for it answer one with me will be until here do not forget to subscribe to your channel El Tío Tech and see you until next time.
