Comprehensive Guide to Spreadsheet Usage

Following this lecture, what we have already discussed is that the first thing is that why do we use a spreadsheet? What are some of the options we have in a spreadsheet? I discussed with you in the first class that why there is a need to use a spreadsheet because it has rows and columns. It has grades so that you can spread your data. You can organize it. The second thing we discussed is that how can we create a structure of a spreadsheet? How can we structure? the pages how can we structure the sheets and i discussed with you regarding the page setup where you implemented that on your own you change the orientation of a page you change the size of that you we can even work on to the margins header and footer the fit pay fit to page options as well then we discussed regarding the cells we discussed that how can we color the cells how can we merge them how can we align the margins and then we discuss into detail some of the formulas how we can add them how we can subtract them we discuss this one like if I want to freeze the rows if I want to freeze the rows and the columns as well we have completed this as well and the data types this one this was the last lecture where we discussed regarding the data types and how we can add some formulas as well and just after that we also discuss the orientation of text that we can change the orientation of that such as horizontal or vertical depending on to our requirements so i hope that so far everything is clear till here and i also discuss with you like for example if you want to change the alignments or if you want to change the decimal size sign or the for the percentage sign so we are going to start from here like format cell emphasis so uh i hope that you do not have any query and even if you have feel free to ask you do so uh just after my class heading wise you can open up your book and you can start implementing these areas that i'm teaching you in the class so uh today's topic what we are going to discuss that how we can format the cell emphasis so cell emphasis is about changing a cell so that it basically stand out from the other like for example if you have a large spreadsheet and you want to focus on only one area or you want to focus on a one single heading so that what you can do is that you can change the size of that you can change the way that it is looking by applying different areas Like, for example, if let's say I have some number of columns, like, for example, if this is a name, this is the age, this is your student marks and let it make it the student's marks, pricing, subjects, grade. And similarly, you feel like that pricing is one of the most important areas, whereas it should be looking a little bit different. So that what you can do is that you have this column right here. Now, what you can do is that you can change the font as well. Like for example, I can change the way that it is looking by selecting any of my desired font from this list. For example, let's make it Ariel black. So you can actually see that it is different from the others. It is unique from the others by just changing the font. Similarly, you can even select bold as well. You can even select it clicks as well. You can even select the underline option as well so you see that this is how that you are formatting it you can change the style of that and even if you want to revert back to it you are just going to click on the same icon so this is going to give you uh your last effect like for example i have added a font here century so you can even extend it like for example if i make it the size of it as 24 you can see that it is not coming accurately so just in case that if it is not coming accurately you need to select margins from here and you need to extend that so you can see that this is an area so this is looking a little bit different so you can color them you can play with these around so this is what they might ask you into your paper to change the font size things like this so the next area that is right here is that we can even add comments as well so comments are mostly important like for example if you want to explain that what i like what sort of data I'm trying to add in here or if you want to add some additional information or you want to explain it to your user that why you want it to be or if you want to have any sort of justification or for example if you are preparing that data and that data is based on some calculations so the user when he is going to see it is not accurately going to get it that why it is for so you can actually add your comments here So for adding comments, what you can do is that this one, this is the area basic action insert comment. So if you are going to click on that, you can paste it right here and here you can write your comment that I can write like for example, following is the pricing of USD rate of today so this is basically a comment so you can play with it around you can add it anywhere else as well so you can see that i saved that comment right there and it is not appearing here but it is giving you a slight red colored corner as well so when i'm going to put my mouse over there you can see that the same comment is going to display here that means that now at the same time that box is not hiding your data and you have additional information added into that as well So any queries or problems still here as we are exploring Excel? No, sorry, everything is clear. So just in case, even if you are stuck and you are unable to find any sort of option that you require into your paper, what you can do is that there is basically. a search box right here so you can uh and you can uh uh right here type with the option that you are looking for so it is going to pop up so right here when you're going on to the review tab you have this option edit comments delete comment previous comment next comment show all comments hide comments so uh Whenever you are working with your different cells and different tables, you have the option of conditional formatting as well. So conditional formatting is basically an example of like when you are adding a certain condition, like for example, a certain condition was also listed right here. Like when now I'm referring to the last class of me. So you can actually see that that this is a this is basically a currency and I have added here. the dollar should be 15 and your number of decimal should be right like for example 5 or 10. So this is basically like a condition a condition that this should have to be there. Like for example I said that our today's class is going to be at 10. So this means that this was a condition. I settled it down that at 10 o'clock we are going to have our class. So we can also add conditional statements as well. For example I can even set the condition right here that only process the excel sheet. if the age of the student is not more than 20 or greater than 20 or i can also set it the weight i can also set the age as well so conditional formatting is like some of the examples you can see right here that values in a cell that are equal to a certain criteria you can give your own desired criteria the criteria that you wish to give like values in a cell that are greater than or less than to any value or values that are into between a certain range or values that are duplicated somewhere of values that are above or below the range. I'm going to explain this to you that how we can apply this once I have a working sheet. So in your 8.3 now we are discussing that how can we create formulas and how can we use functions and similarly into our last class we did discuss that a formula is basically an arithmetic calculation and obviously I don't need to explain that. you have different formulas like if you want to add if you want to subtract if you want to divide if you want to multiply like this sheet so let's also work on this sheet so this is basically a spreadsheet that is used to calculate the cost of a broadband tv phone package the formula in cell b9 adds up monthly cost and line rentals and broadband and finally it adds up to the final cost from b3 So, let's work on to this example. This is basically a spreadsheet and let me create something similar for you so that it's quite easy for you to understand now. It basically says that this is a spreadsheet that is used to calculate the cost of a broadband TV, a phone package. So first of all, let's create it. For example, on B cell, I have broadband one on C cell. I have broadband one and since it is not coming right here. So let me extend it a little so that it's quite visible here. The second thing you can see that both of the categories are in bold. So for making it bold either you are going to press ctrl plus B onto your keyboard like this or you can even click right here ctrl B. is the shortcut for bold so this is broadband one for broadband two uh this is your original this is your family and right here you can type your initial cost and this is the first of six months we can add our line rentals we can add our tv we can add our broadband let me make it a little more extended So initial cost that is given the cost of space weight. So it says that the formula in cell B9, the formula that is in cell B9. This is the this is the cell which I need to add the formula and it is to be noted as B9. So you can see that I am taking it like from here B and from right here to the 9. So that means that I need to add the formula here and the formula that is in B9. What it is doing is that it. is adding up the monthly cost of line rentals that is in b6 and b7 so b6 is right here and b7 is right here so let's take an uh any value like for example 10 but you can actually see that this there there is a dollars and place here so what i can do is that i'm going to write here dollar so first of all if you want to add any currency uh so if i'm going to write it like this so this is not certainly making it a currency so whenever you are writing a currency or whenever you are writing any value and you want it to be in a certain format like if you want to be in your dollar sign so this is not the case you are going to do that the first thing you need to do that you need to add the formula here so you are going to press the equal sign onto your keyboard and you are going to look for this dollar sign this is a dollar function and then when it comes here you need to press the tab key on your keyboard so you can see that this is the formula now it is asking you the number and the number of decimals where you want it to be so i can type it like for example as 15 and the number of decimals i want it to be is i want it to be up to uh two decimal places so this is like 15.00 and if i want it to be changed a bit so 15 point you four zero and up to two decimal places so that you can see that it is giving you a dollar sign so similarly if you also want to add the cost into this table you can either do it manually as well again dollar sign whatever the value that is that is 10.75 comma your decimal places bracket close and you need to close the brackets whenever you are opening them if you are not going to do that you are going to encounter an enter enter So this is the third second value and you can even drag and drop it right here like this you are you do not require to type the similar formulas again and again you can even drop them but it is going to give you the same value. So again then you do not have to type the whole formula so you can just change the value right here to the value of broadband that is given right here. I'm going to hover over to this area and I'm going to add that it is $20 so you can see that it is $20. that with a dollar sign you have the of your specific value so when it comes to your alignment you can either set it in your alignment box if you want it to be in your middle or if you want to settle it as uh like any sort of alignment you want it to be it's totally up to you how you want it to be sir what is the comma for comma beta comma uh where are you referring to uh this formula Yes, sir. Peter, see, if this is a cell that is right in front of you and you want to add currency, so you are going to write equals to, then you are going to search for dollars and you are going to press the tab key. Now, when you are going to press the tab key, look closely here. Can you see that this is written dollar, this is written number, this is written decimals. Let me write it right here, like right here for you, the formula that is dollar. that is number and comma that is decimal this is the formula right so uh what it is going to do that if i'm going to add the value of 15 and if i'm going to write my uh number as uh sorry if the number is dollar 15 and if i want it to be extended up to certain decimal places i'm not going to write it like 15 or 2 like 15 or 3 I'm going to segregate that I'm going to separate that with a comma so that I would know that this is the value of the dollar and this is still how much I want to extend the value of that decimal. For example I have written here 4 so let me implement this right here is equals to dollar comma sign so you can actually see right here this says you type the number and then just after typing the value of the dollar you need to add a comma. comma so that you can actually tell that okay that 20 is the dollar value now the computer wants you to tell him that how much decimal places you want it to be so comma is basically segregating the value of your dollar and the value of decimal places you want like for example you cannot write like this like for example dollar 20 and up to five decimal places this is going to make it dollar 205 this is going to create a confusion So to segregate that, to make it into two different areas, I'm going to add a comma and then I'm going to add five and bracket close. So the impact of that you can see is that now I'm going to have the value of 20 with the dollar sign and up to five decimal places. Any queries? No, sorry, it's not clear. Perfect. you know what whenever you are typing any formula no matter what it is the computer tells you itself that how you want to write it like by giving you a specific syntax so this is done this is done this is done and let's also add the values that are given right here So again, dollar sign, the number which is 15.40 comma. So make it up to two decimal places because we are referring to the right hand of the book. That example and again, dollar tap and 16.50 up to two decimal places. And then again, dollar, the value is 20.00 and 2. This is it. Let me also make it the final value. That is your initial cost is your $15. So $15, 15, 15.00 and that is up to two decimal. This is done. Now, the final area. The final area of the book says that. the formula in the cell b9 adds up the monthly cost of line rentals b6 and b7 so for adding the formula uh the first thing i need to do is that i need to add up the rentals okay of b6 and b7 b6 is right here b7 is right here so for b9 i'm going to press the equals key and then i'm going to add up the monthly cost of line rentals monthly cost of Ryan line rentals of B6 and B7, B6 and B7. Okay. So this is so, you see, I'm implementing this so that you should know that. how you are able to write this the first thing that you are going to press the equals key on your keyboard then you have to press the brackets open and then you write the name of the cell like for example b6 whenever you are going to add add b6 you can see that that specific cell is highlighted so that it is going to tell you that you have selected the correct cell or not so i am the first part is that i am adding the monthly cost of line rentals b6 and b7 so this is b6 plus b7 okay and broadband as well broadband is b8 so you can see that i have added all of these values and then the question says that multiply the result by 6 so you can do is that you can b6 b7 b8 and then you can multiply the result of that by closing the bracket and then adding a multiplier sign So multiply sign in your Excel is considered as a star sign as a steric sign. So you can multiply by 6 and finally it adds any setup cost from B3 and just after this multiplying that so you can actually add it back again with any value of your B3. So this is basically a formula that I have created here. Do you have any confusion in this formula right here? No sir. So when I'm going to click on enter you can see that this is giving you the certain value of that condition. That means that I have added the three cells value. I have multiplied by six and added the final value. You can even add this multiplication in the start. So one thing I want to clear here that whenever you are working with multiplications I know I hope that you are aware of the demass rule that says that divide is first multiplication is second addition is first so you need to actually make it the multiplication of this first like you can see that I have first multiplied then I have added the all of them this is the way that you are going to get the correct answer you can even work like for example giving the powers like for example if i write here 4 is 2 power is 3 it is going to give you that exact form and for writing the power you do not have that small 3 you can put on the top of any number for that use you use a cap sign a cap sign this one can you see that this one this is you can press this by pressing the shift and 6 key onto your keyboard Shift plus 6 so that you will get a cap sign and cap sign means that you are referring to a power of 2 something Like for example 4 is to power 3 6 is to power 3 anything The next area that is right here is referencing cells and ranges. Relative cell referencing. Relative cell referencing. So referencing. The first thing should be clear right here. Referencing means that, for example, you have focused on to one value and you are referring the same value to another cell. So relative referencing is used when you want the contents of a cell to change based on to its relation with any other row or column. You can use these formulas, for example, for calculations, for raw gen calculations. So relative referencing means that a cell reference that changes when it is copied to another cell. So let's look into this worked example 8.1.7 that says that the spreadsheet lists the number of hours it takes to produce a promotional leaflet. The cost to be charged for the leaflet and the quantity to be produced. The total to be charged is then calculated by multiplying the total cost by. this on way of entering the formula in e5 for the total cost of a4 leaflets to type it again but changing the formula would be uh okay okay so uh let's work on to this example as well uh you this is still right here the comment that i added you can see a small you can even implement it onto your own so let's work on to the next category and for giving it a table look like look you can click right here this is going to give it a border like when you like you have the option we want it to be bottom border top border left border right border like i'm giving this table in all border so i'm moving it to uh the next table so this is a spreadsheet list of numbers so let me make it this one as well in right here so or let's just make it right here so first of all you have your job you have your arts you have your cost you have your quantity you have your total the first thing you have is a5 leaflet you have a4 leaflet you have a3 leaflet when it comes to your arts it's like one 1.5 point five the value of dollars is again dollar 12.502 dollar 18.75 comma two and dollar 25.00, this is certainly the cost of this. The quantity is 312. Now, If I want to have the total of that, what it is doing, it is actually multiplying the cost and the cost is C13 being multiplied by D13. So what I need to teach you right here is that you need to look closely here like If for let's say like if I have added a formula right here, you can see that I have added this formula and what this formula is doing is that it is multiplying the cost and the quantity of the first two cells. Now, I also want the same to be happening in this box in this box as well. But When it comes to your exam point of view, you would not have certainly the time limit to add formulas like this into each and every box. So the easy way to do this is that you just need to add one formula onto one box. And if you wish to have the same value into other columns as well, you are going to. move your mouse to this button and you are going to drag it. So when you are going to drag it, this is going to implement the same formula into the other two cells as well and the best part is that it has automatically changed its cell value. That means that right here I am not corresponding to these values. The values are changed itself. Let me show you. this was the first area you can see that this is the a5 leaflet cost now when i dragged it you can see that the value is automatically changed by dragging it i have not done it manually and you can see that so on it is automatically detected as well so this is one way of entering that so that it is much quicker and much accurate so you do not actually need to copy the whole data and again and again so it's totally up to you you can drag it as much as you want so you see that this are some of the areas so if i'm just going to add some values right here let's say six or let's say five or let's say 3, you can see that the values are changing automatically because the formula is added. So, any confusion till here? Let's also give it a border. No, sir. Everything is clear. Are you doing this for the first time? No, I've done it before. Okay. The auto-filling part. You can actually use the same process in your cell referencing as well. So, do you have any idea regarding absolute cell referencing? No, sir. Okay. Can you tell me anything regarding relative cell referencing? Just the one that we have discussed right now. You see, when it comes to cell referencing, we have two types. We have an relative referencing and we have absolute referencing. So it's right in front of you as well. So can you explain me what is a relative referencing? Basically, I guess. Relative referencing is that when you are referencing the value of one cell and you are copying it to the other. So you have actually make a reference like for example, do you agree that this is a reference? Yes. And if I'm going to extend that, this is an example of a relative referencing. That means that a cell reference that changes when it is copied to another cell. And you can see that when I extend it to the other cell. the referencing also change that means that it is based on that but similarly now if i am discussed with discussing with you regarding the absolute referencing so absolute referencing is used when you do not want a cell reference to change when other cells are filled in or when you want to replicate with that like a cell reference that does not change like uh let me ask you one thing you You can say that sir you added a formula right here and when you drag that it automatically gets the certain next cells which I want and change that. Now if I ask you that I do not want this referencing to be happening. So what we are going to do like if I want to select only one value. Do you have any ideas? Like this was an example of relative cell referencing. Anything you have for absolute cell referencing? No idea, sir. Okay, so let me show you. Let me name it as relative cell referencing. And the next thing that we are going to do is your absolute cell referencing.

The second thing we discussed is that how can we create a structure of a spreadsheet? How can we structure? the pages how can we structure the sheets and i discussed with you regarding the page setup where you implemented that on your own you change the orientation of a page you change the size of that you we can even work on to the margins header and footer the fit pay fit to page options as well then we discussed regarding the cells we discussed that how can we color the cells how can we merge them how can we align the margins and then we discuss into detail some of the formulas how we can add them how we can subtract them we discuss this one like if I want to freeze the rows if I want to freeze the rows and the columns as well we have completed this as well and the data types this one this was the last lecture where we discussed regarding the data types and how we can add some formulas as well and just after that we also discuss the orientation of text that we can change the orientation of that such as horizontal or vertical depending on to our requirements so i hope that so far everything is clear till here and i also discuss with you like for example if you want to change the alignments or if you want to change the decimal size sign or the for the percentage sign so we are going to start from here like format cell emphasis so uh i hope that you do not have any query and even if you have feel free to ask you do so uh just after my class heading wise you can open up your book and you can start implementing these areas that i'm teaching you in the class so uh today's topic what we are going to discuss that how we can format the cell emphasis so cell emphasis is about changing a cell so that it basically stand out from the other like for example if you have a large spreadsheet and you want to focus on only one area or you want to focus on a one single heading so that what you can do is that you can change the size of that you can change the way that it is looking by applying different areas Like, for example, if let's say I have some number of columns, like, for example, if this is a name, this is the age, this is your student marks and let it make it the student's marks, pricing, subjects, grade.

And similarly, you feel like that pricing is one of the most important areas, whereas it should be looking a little bit different. So that what you can do is that you have this column right here. Now, what you can do is that you can change the font as well.

Like for example, I can change the way that it is looking by selecting any of my desired font from this list. For example, let's make it Ariel black. So you can actually see that it is different from the others. It is unique from the others by just changing the font. Similarly, you can even select bold as well.

You can even select it clicks as well. You can even select the underline option as well so you see that this is how that you are formatting it you can change the style of that and even if you want to revert back to it you are just going to click on the same icon so this is going to give you uh your last effect like for example i have added a font here century so you can even extend it like for example if i make it the size of it as 24 you can see that it is not coming accurately so just in case that if it is not coming accurately you need to select margins from here and you need to extend that so you can see that this is an area so this is looking a little bit different so you can color them you can play with these around so this is what they might ask you into your paper to change the font size things like this so the next area that is right here is that we can even add comments as well so comments are mostly important like for example if you want to explain that what i like what sort of data I'm trying to add in here or if you want to add some additional information or you want to explain it to your user that why you want it to be or if you want to have any sort of justification or for example if you are preparing that data and that data is based on some calculations so the user when he is going to see it is not accurately going to get it that why it is for so you can actually add your comments here So for adding comments, what you can do is that this one, this is the area basic action insert comment. So if you are going to click on that, you can paste it right here and here you can write your comment that I can write like for example, following is the pricing of USD rate of today so this is basically a comment so you can play with it around you can add it anywhere else as well so you can see that i saved that comment right there and it is not appearing here but it is giving you a slight red colored corner as well so when i'm going to put my mouse over there you can see that the same comment is going to display here that means that now at the same time that box is not hiding your data and you have additional information added into that as well So any queries or problems still here as we are exploring Excel?

No, sorry, everything is clear. So just in case, even if you are stuck and you are unable to find any sort of option that you require into your paper, what you can do is that there is basically. a search box right here so you can uh and you can uh uh right here type with the option that you are looking for so it is going to pop up so right here when you're going on to the review tab you have this option edit comments delete comment previous comment next comment show all comments hide comments so uh Whenever you are working with your different cells and different tables, you have the option of conditional formatting as well.

So conditional formatting is basically an example of like when you are adding a certain condition, like for example, a certain condition was also listed right here. Like when now I'm referring to the last class of me. So you can actually see that that this is a this is basically a currency and I have added here. the dollar should be 15 and your number of decimal should be right like for example 5 or 10. So this is basically like a condition a condition that this should have to be there.

Like for example I said that our today's class is going to be at 10. So this means that this was a condition. I settled it down that at 10 o'clock we are going to have our class. So we can also add conditional statements as well.

For example I can even set the condition right here that only process the excel sheet. if the age of the student is not more than 20 or greater than 20 or i can also set it the weight i can also set the age as well so conditional formatting is like some of the examples you can see right here that values in a cell that are equal to a certain criteria you can give your own desired criteria the criteria that you wish to give like values in a cell that are greater than or less than to any value or values that are into between a certain range or values that are duplicated somewhere of values that are above or below the range. I'm going to explain this to you that how we can apply this once I have a working sheet. So in your 8.3 now we are discussing that how can we create formulas and how can we use functions and similarly into our last class we did discuss that a formula is basically an arithmetic calculation and obviously I don't need to explain that.

you have different formulas like if you want to add if you want to subtract if you want to divide if you want to multiply like this sheet so let's also work on this sheet so this is basically a spreadsheet that is used to calculate the cost of a broadband tv phone package the formula in cell b9 adds up monthly cost and line rentals and broadband and finally it adds up to the final cost from b3 So, let's work on to this example. This is basically a spreadsheet and let me create something similar for you so that it's quite easy for you to understand now. It basically says that this is a spreadsheet that is used to calculate the cost of a broadband TV, a phone package. So first of all, let's create it.

For example, on B cell, I have broadband one on C cell. I have broadband one and since it is not coming right here. So let me extend it a little so that it's quite visible here.

The second thing you can see that both of the categories are in bold. So for making it bold either you are going to press ctrl plus B onto your keyboard like this or you can even click right here ctrl B. is the shortcut for bold so this is broadband one for broadband two uh this is your original this is your family and right here you can type your initial cost and this is the first of six months we can add our line rentals we can add our tv we can add our broadband let me make it a little more extended So initial cost that is given the cost of space weight. So it says that the formula in cell B9, the formula that is in cell B9. This is the this is the cell which I need to add the formula and it is to be noted as B9.

So you can see that I am taking it like from here B and from right here to the 9. So that means that I need to add the formula here and the formula that is in B9. What it is doing is that it. is adding up the monthly cost of line rentals that is in b6 and b7 so b6 is right here and b7 is right here so let's take an uh any value like for example 10 but you can actually see that this there there is a dollars and place here so what i can do is that i'm going to write here dollar so first of all if you want to add any currency uh so if i'm going to write it like this so this is not certainly making it a currency so whenever you are writing a currency or whenever you are writing any value and you want it to be in a certain format like if you want to be in your dollar sign so this is not the case you are going to do that the first thing you need to do that you need to add the formula here so you are going to press the equal sign onto your keyboard and you are going to look for this dollar sign this is a dollar function and then when it comes here you need to press the tab key on your keyboard so you can see that this is the formula now it is asking you the number and the number of decimals where you want it to be so i can type it like for example as 15 and the number of decimals i want it to be is i want it to be up to uh two decimal places so this is like 15.00 and if i want it to be changed a bit so 15 point you four zero and up to two decimal places so that you can see that it is giving you a dollar sign so similarly if you also want to add the cost into this table you can either do it manually as well again dollar sign whatever the value that is that is 10.75 comma your decimal places bracket close and you need to close the brackets whenever you are opening them if you are not going to do that you are going to encounter an enter enter So this is the third second value and you can even drag and drop it right here like this you are you do not require to type the similar formulas again and again you can even drop them but it is going to give you the same value. So again then you do not have to type the whole formula so you can just change the value right here to the value of broadband that is given right here. I'm going to hover over to this area and I'm going to add that it is $20 so you can see that it is $20.

that with a dollar sign you have the of your specific value so when it comes to your alignment you can either set it in your alignment box if you want it to be in your middle or if you want to settle it as uh like any sort of alignment you want it to be it's totally up to you how you want it to be sir what is the comma for comma beta comma uh where are you referring to uh this formula Yes, sir. Peter, see, if this is a cell that is right in front of you and you want to add currency, so you are going to write equals to, then you are going to search for dollars and you are going to press the tab key. Now, when you are going to press the tab key, look closely here. Can you see that this is written dollar, this is written number, this is written decimals. Let me write it right here, like right here for you, the formula that is dollar.

that is number and comma that is decimal this is the formula right so uh what it is going to do that if i'm going to add the value of 15 and if i'm going to write my uh number as uh sorry if the number is dollar 15 and if i want it to be extended up to certain decimal places i'm not going to write it like 15 or 2 like 15 or 3 I'm going to segregate that I'm going to separate that with a comma so that I would know that this is the value of the dollar and this is still how much I want to extend the value of that decimal. For example I have written here 4 so let me implement this right here is equals to dollar comma sign so you can actually see right here this says you type the number and then just after typing the value of the dollar you need to add a comma. comma so that you can actually tell that okay that 20 is the dollar value now the computer wants you to tell him that how much decimal places you want it to be so comma is basically segregating the value of your dollar and the value of decimal places you want like for example you cannot write like this like for example dollar 20 and up to five decimal places this is going to make it dollar 205 this is going to create a confusion So to segregate that, to make it into two different areas, I'm going to add a comma and then I'm going to add five and bracket close. So the impact of that you can see is that now I'm going to have the value of 20 with the dollar sign and up to five decimal places. Any queries?

No, sorry, it's not clear. Perfect. you know what whenever you are typing any formula no matter what it is the computer tells you itself that how you want to write it like by giving you a specific syntax so this is done this is done this is done and let's also add the values that are given right here So again, dollar sign, the number which is 15.40 comma. So make it up to two decimal places because we are referring to the right hand of the book. That example and again, dollar tap and 16.50 up to two decimal places.

And then again, dollar, the value is 20.00 and 2. This is it. Let me also make it the final value. That is your initial cost is your $15. So $15, 15, 15.00 and that is up to two decimal. This is done.

Now, the final area. The final area of the book says that. the formula in the cell b9 adds up the monthly cost of line rentals b6 and b7 so for adding the formula uh the first thing i need to do is that i need to add up the rentals okay of b6 and b7 b6 is right here b7 is right here so for b9 i'm going to press the equals key and then i'm going to add up the monthly cost of line rentals monthly cost of Ryan line rentals of B6 and B7, B6 and B7. Okay. So this is so, you see, I'm implementing this so that you should know that.

how you are able to write this the first thing that you are going to press the equals key on your keyboard then you have to press the brackets open and then you write the name of the cell like for example b6 whenever you are going to add add b6 you can see that that specific cell is highlighted so that it is going to tell you that you have selected the correct cell or not so i am the first part is that i am adding the monthly cost of line rentals b6 and b7 so this is b6 plus b7 okay and broadband as well broadband is b8 so you can see that i have added all of these values and then the question says that multiply the result by 6 so you can do is that you can b6 b7 b8 and then you can multiply the result of that by closing the bracket and then adding a multiplier sign So multiply sign in your Excel is considered as a star sign as a steric sign. So you can multiply by 6 and finally it adds any setup cost from B3 and just after this multiplying that so you can actually add it back again with any value of your B3. So this is basically a formula that I have created here. Do you have any confusion in this formula right here? No sir.

So when I'm going to click on enter you can see that this is giving you the certain value of that condition. That means that I have added the three cells value. I have multiplied by six and added the final value. You can even add this multiplication in the start.

So one thing I want to clear here that whenever you are working with multiplications I know I hope that you are aware of the demass rule that says that divide is first multiplication is second addition is first so you need to actually make it the multiplication of this first like you can see that I have first multiplied then I have added the all of them this is the way that you are going to get the correct answer you can even work like for example giving the powers like for example if i write here 4 is 2 power is 3 it is going to give you that exact form and for writing the power you do not have that small 3 you can put on the top of any number for that use you use a cap sign a cap sign this one can you see that this one this is you can press this by pressing the shift and 6 key onto your keyboard Shift plus 6 so that you will get a cap sign and cap sign means that you are referring to a power of 2 something Like for example 4 is to power 3 6 is to power 3 anything The next area that is right here is referencing cells and ranges. Relative cell referencing. Relative cell referencing. So referencing.

The first thing should be clear right here. Referencing means that, for example, you have focused on to one value and you are referring the same value to another cell. So relative referencing is used when you want the contents of a cell to change based on to its relation with any other row or column. You can use these formulas, for example, for calculations, for raw gen calculations. So relative referencing means that a cell reference that changes when it is copied to another cell.

So let's look into this worked example 8.1.7 that says that the spreadsheet lists the number of hours it takes to produce a promotional leaflet. The cost to be charged for the leaflet and the quantity to be produced. The total to be charged is then calculated by multiplying the total cost by. this on way of entering the formula in e5 for the total cost of a4 leaflets to type it again but changing the formula would be uh okay okay so uh let's work on to this example as well uh you this is still right here the comment that i added you can see a small you can even implement it onto your own so let's work on to the next category and for giving it a table look like look you can click right here this is going to give it a border like when you like you have the option we want it to be bottom border top border left border right border like i'm giving this table in all border so i'm moving it to uh the next table so this is a spreadsheet list of numbers so let me make it this one as well in right here so or let's just make it right here so first of all you have your job you have your arts you have your cost you have your quantity you have your total the first thing you have is a5 leaflet you have a4 leaflet you have a3 leaflet when it comes to your arts it's like one 1.5 point five the value of dollars is again dollar 12.502 dollar 18.75 comma two and dollar 25.00, this is certainly the cost of this. The quantity is 312. Now, If I want to have the total of that, what it is doing, it is actually multiplying the cost and the cost is C13 being multiplied by D13.

So what I need to teach you right here is that you need to look closely here like If for let's say like if I have added a formula right here, you can see that I have added this formula and what this formula is doing is that it is multiplying the cost and the quantity of the first two cells. Now, I also want the same to be happening in this box in this box as well. But When it comes to your exam point of view, you would not have certainly the time limit to add formulas like this into each and every box.

So the easy way to do this is that you just need to add one formula onto one box. And if you wish to have the same value into other columns as well, you are going to. move your mouse to this button and you are going to drag it.

So when you are going to drag it, this is going to implement the same formula into the other two cells as well and the best part is that it has automatically changed its cell value. That means that right here I am not corresponding to these values. The values are changed itself.

Let me show you. this was the first area you can see that this is the a5 leaflet cost now when i dragged it you can see that the value is automatically changed by dragging it i have not done it manually and you can see that so on it is automatically detected as well so this is one way of entering that so that it is much quicker and much accurate so you do not actually need to copy the whole data and again and again so it's totally up to you you can drag it as much as you want so you see that this are some of the areas so if i'm just going to add some values right here let's say six or let's say five or let's say 3, you can see that the values are changing automatically because the formula is added. So, any confusion till here? Let's also give it a border. No, sir.

Everything is clear. Are you doing this for the first time? No, I've done it before. Okay.

The auto-filling part. You can actually use the same process in your cell referencing as well. So, do you have any idea regarding absolute cell referencing? No, sir. Okay.

Can you tell me anything regarding relative cell referencing? Just the one that we have discussed right now. You see, when it comes to cell referencing, we have two types.

We have an relative referencing and we have absolute referencing. So it's right in front of you as well. So can you explain me what is a relative referencing? Basically, I guess. Relative referencing is that when you are referencing the value of one cell and you are copying it to the other.

So you have actually make a reference like for example, do you agree that this is a reference? Yes. And if I'm going to extend that, this is an example of a relative referencing.

That means that a cell reference that changes when it is copied to another cell. And you can see that when I extend it to the other cell. the referencing also change that means that it is based on that but similarly now if i am discussed with discussing with you regarding the absolute referencing so absolute referencing is used when you do not want a cell reference to change when other cells are filled in or when you want to replicate with that like a cell reference that does not change like uh let me ask you one thing you You can say that sir you added a formula right here and when you drag that it automatically gets the certain next cells which I want and change that. Now if I ask you that I do not want this referencing to be happening. So what we are going to do like if I want to select only one value.

Do you have any ideas? Like this was an example of relative cell referencing. Anything you have for absolute cell referencing? No idea, sir.

Okay, so let me show you. Let me name it as relative cell referencing. And the next thing that we are going to do is your absolute cell referencing.

Transcript for:Comprehensive Guide to Spreadsheet Usage

Transcript for:
Comprehensive Guide to Spreadsheet Usage