sec tip 0) aU eee finit) eset mee Sener Introduction, Range of cells Crate Una iS: absolute, mixed, Functions used inva spreads functions: SUM( ), RAGE( J, MAX(), MIN(), COUN’ Pectrene ecto ‘ ‘ Introduction q In the previous class, you have learnt about workbooks and performed | tasks, such as creating a worksheet, entering data, inserting/deleting rows carried out simple mathematical calculations. Now, you will learn some such as range of cells, cel referencing, and other built-in features of a spre Range of Cells A cell is the smallest unit of a worksheet which is formed at the intersection of a row and a column. Each cell own cell address for reference. The address of a cell consists of the column letter followed by the row number. ‘The cell in which the cell pointer is located in a worksheet, / is the active cell. Ithas a dark boundary around it. You can | ‘The address of move the cell pointer to make any other cell active, either | XT OUOMBS7BN5 with the help of arrow keys or by using the mouse. When more than one cells are selected in a workshe ! t, the selected cells are of cells. A range is specified by giving the addresses of the first cell and the included in the range. For example, In the given image below, the range of cells is A2 to K2.selecting Rows * rksheet, selecting rows can be done in the following ways Ina ¥0 1, selecting an Entire Row fh select an entire row, follow these steps: ‘ow header that is to be selected. You will notice Bring the mouse pointer to the 1 that she mouse pointer will’change to'a black arrow io right/diseston: step 2+ Click on the row header to select the entire OW [rafal — (nies Sako Se Seat [SA me [ose] Daes 1ST Jecting Multiple Entire Rows (Consecutive) _) select multiple entire consecutive rows, follow these steps: Step 1: Select or bring the mouse pointer tot rows are to be selected. rows the left mouse button and drag the mouse to select the Tequired umber of he row header of a row from where consecutive Step 2: consecutive rows. Step 3: Release the mouse button. faviaartt_|Mint Khurana Menta ‘n7221/12_ Nivek Mohan Chandra hast | SHERE_shawes /Sheoid | Shoes 3, Selecting Multiple Entire Rows (Non-Consecutive) To select multiple entire non-consecutive rows, follow these steps: Step 1: Select the first row(s) (say, a set of consecutive rows 4,5 and 6) Step 2: Press the ‘Ctrl’ key and select other row(s) (say, another set of consecutive rows 9 and 10)Step 3: Release the key and mouse button. Fem iE as {TSA seers [sees Oo = cone a572 Come 35 S718 @ Selecting Range of Cells in Rows If you want to select some specific cells in a row then you need to select a particular range of cells. or multiple rows instead wa D ‘This can be done in the 1, Selecting range of cells in a Single Row ‘To select some specific cells in a row, take the following steps: Step 1: Select the first cell. . Step 2: Keeping the left mouse button pressed, drag the mouse to select the desired, of cells in a row (here, cells F6, G6 and H6 are selected in row 6). Step 3: Release the mouse button. (alt20/11_|Adarsh Pradhan 78 (aoini2_|Animesh Kumar Chopra | 80 | 4 36 (at47/i0 Yamurag Kumar Bhushan | 91 | oo | 91 | 96 | ot [a132/10 [Chandra Prakash Kant | 90 | €0 | 90 s lave1/10 _|Deepak Kumar Bhalla | 36 | 68 | 70 | oe [ar76/it [Ranjan Kant Singhal go | ss | a | a | 7 ° (art22/12_|Saket Kumar Singh a7 | 89 | a8 | ao | 39 ho lartae/tt _|Vinit Khurana Menta es | o1 | 73 | 80 | 79 (alzo1/t2 _|Vivek Mohan Chandra | 03 | €2 e2_| 8 Ghoti | Shae | Sheets | Shaved | sheets | © nce DEVRY Cot} rc 2, Selecting range of cells in Multiple Consecutive Rows ‘To select range of cells in multiple consecutive rows, take the following steps: Step 1: Place the cell pointer on the cell from where the range is to be selected. WI cst Computer StudiesET ea step 2: Select the cell and drag the mouse button diagonally to your right or left, till the last cell. step 3: Release the mouse button, then pressing ‘Shift key’ and then clicking ‘on the last cell in the range by using mouse or arrow (evs ai 3. Selecting range of cells in Multiple Non-Consecutive Rows To select range of cells in multiple non-consecutive rows, take the following steps: Step 1: Select the first range of cells. Step 2: Press the 'Ctrl’ key and then select another ranges of cells in your worksheet as pense your need. Step 3: Finally, release the key. Sheet | SHES] sheets [Sheets | Shes SG Areage elas Comegt Sapiea? = a© Selecting Columns In a worksheet, selecting columns can be done in the following ways: 1, Selecting an Entire Column To select an entire column, take the following steps: Step 1: Bring the mouse pointer to the column header that is to be selected. that the mouse pointer will change to @ black arrow in downward dit Step 2: Click on the header to select the entire column. lari2o/nt_ [Adarsh Pradhan Mishra. |WOW/i2 _|Animesh Kumar Chopra /147/10 [Anurag Kumar Bhushan [a/192/10_|Chandra Prakash Kant [nv61/10_ [Deepak Kumar Bhalla [av7e/tt__[Ranjan Kant Singhal (a/t22/12 [Saket Kumar Singh _ [atserit [Vint Khurana Mehia | lwo Chanara | 93 | le le\s\s\s\e\s\e a |e fat le (8 fe fe la Tec | Shee | sheets [Sheet | Sheets |W 2. Selecting Multiple Entire Columns (Consecutive) lumns, follow these steps: he column header of a column: column D). r of consecutive co. To select multiple entire consecutive coh Step 1: Select or bring the mouse pointer to th consecutive columns are to be selected (say; Step 2: Press the left mouse bution to select required number columns D to G are selected). Step 3: Release the mouse button. lasi2a/tt Pradhan Mishra "4/91/12 _|Animesh Kumar Chopra [wi147/10_|Anurag Kumar Bhushan [a/192/10_|Chandra Prakash Kant (4/61/10 [Deepak Kumar Bhalla ‘a/7eisi [Ranjan Kant Singhal _ (ariz2/t2_|Saket Kumar Singh [a/iaa/tt _|Vvinit Khun 88 'a/221/12_|Vivek Mohan Chandra 98 J Shiett” | Sheotdl| sheets | Shaeld | Sheets pei ASANGGET Count AV Sa 20 la |B ja |B 18 |e_ 3, Selecting Multiple Entire Columns (Non-Consecutiye) ‘To select multiple entire non-consecutive columns, follow these steps: Step 1: Select the first column(s) (say, consecutive columns D and B) Step 2: Press ‘Ctrl’ key and select other columns (say, another set of consecutive columns. G and H) 4 Step 3: Release the key, lwo | Shee) SBR | shoes Panseer/Sheews +) Selecting Range of Cells in Columns In a worksheet, a range of cell in a column/columns can be selected in the following ways: lecting a range of cell in a Single Column y taking the following steps, you can select range of cells in a column: Step Step 2: Step 3: : Select the first cell. Drag the mouse button to select the desired number of cells in a column (here, cells E6-E10 are selected in column E). Release the mouse button. langle (a lamar [avsaon, ERT [avant [anizen, lanaein 12 Set |S, shaats Shei) Shits |) TG z a eee Spreadsheet: Functions and Charts =2, Selecting range of cells in Multiple Consecutive Columns To select range of cells in multiple consecutive columns, take the following s Step 1: Place the cell pointer on the cell from where the range is to be sele Step 2: Select the cell and drag the mouse button diagonally to your right or reach the last cell. Step 3: Release the mouse button. lvizatt_ [Adarsh Pracan Mishra |A/91/12_|Animesh Kumar Chopra 1a147/10_|Anurag Kumer Bhushan Ivistto” [Chanda Prakash Kart AI61/10 [Deepak Kumar Bhalla lwrett [Ranjan Kant Singh [arian [Saket Kumar singh | 87 ltaeit [int Khurana Menta In201/12 Vivek Mohan Chandra [Sheet] Sheed] shoots [Sheed | Shee =e on-Consecutive Columns: take the following steps 3. Selecting range of cells in Multiple N To select range of cells in multiple non-consecutive columns, Step 1: Select the first range of cells Step 2: Press the ‘Ctrl! key and select another range Step 3: Repeat Step 2 to select other multiple ranges Step 4: Finally, release the ‘Ctr!’ key. of cells in your worksheet. [wizartt |Adarsh Pradhan Mishra a | 68 [wo1r2 [Animesh Kumar Chopra | 80 | e4 | 60 | 77 | 86 96 9 Tai47Ho [Anurag Kumar Bhushan | 91 | 90 | ot of xa0/10 [Chandra Prakash Kant (S80) 00 |p 20 [we1/10 [Deepak KumarBhalo [62] 06h ra ‘1 [Ranjan Kant Singhal BO] 66 87 [aizaii2 |SaketKurar Singh (up) 69 | a 46/11 _|Vinit Khurana Mechta | 8B) 91 20) [aveai/i2.|Vvek Mohan Chandra [08800 = Pees] sheets [Seat | She’s |S % —_ mor 222022 come sums fl ED =eTab in a Spreadsheet MS Excel, the tab which displays various worksheets in workbook is called the ‘Sheet Tab’, It is located at the ‘om left corner of the excel window. By default, the tem displays only one sheet named Sheet1. However, you add new sheets by clicking on ‘New Sheet’ (indicated ® sign) present adjacent to the sheet tab. aming a Worksheet To name or rename a sheet, perform the following steps: Step 1: Double-click the name of the worksheet on the sheet tab. Step 2: Enter the new name and press ‘Enter’ key. Here, Sheet3 is named as ‘Science Marks. To delete a worksheet, perform the following steps: Step 1: Right-click on the sheet name on the Sheet Tab that you want to delete. Step 2: Select the ‘Delete’ option from the pop-up list. Cell References in a Worksheet Fach cell in a MS Excel worksheet is formed, due to the intersection of a row and a column. ‘The combination of the column name and the row number, representing @ cell, is known as the cell address. For example, if the cell address is ‘Ad, it means that the column name is A and the row number is 4 Thus, every cell in a worksheet has a cel is used in a formula, it is known as cel to refer to a cell, which can be put in the formula, depending upon the performed. These references are: 1, Relative Referencing 2, Absolute Referencing 3. Mixed Referencing II address, by which itis referred and when this address Il referencing, MS Excel provides three different ways type of tasks to be Relative Referencing ‘As mentioned earlier, cells are referred to worksheet, In a formula, the relative post to a new cell, the corresponding cell address changes wi This is called Relative Referencing, by their positions (row and column numbers) 10 @ sign of a cell is used. When the formula is copied th reference to the new cell address.In the above worksheet, the total can be calculated by using the formula applied in the F4 cell, as =Sum(C4:E4). When the formula is copied to the columns F5, F6, F7 and F8, the formula will get adjusteg automatically along with the cell references to produce the total in the respective cells, ie, FS Cell will be =Sum(C5:E5) F6 Cell will be =Sum(C6:E6) F7 Cell will be =Sum(C7:E7) F8 Cell will be =Sum(C8:E8) Absolute Referencing Sometimes, it becomes necessary to keep the value of a particular cell constant in a formula, This behaviour is known as Absolute Referencing. To keep the cell value absolute, apply the $' sign before the column name, as well as the row number. For example, if the cell F3 is to be kept absolute, it is written as $F$3. An example is illustrated below to show absolute referencing, here simple interest is calculated, Keeping the rate constant. So now, when the formula ( =A6°B6"SES3 ) is copied, containing the absolute cell address $F$3 then only the relative cell addresses in that formula will be updated but the absolute cell address will remain the same, ie,, the value of $F$3 will not change. % TF rae To find the amount in the column E, use the formula as: =SUM (A6, C6) Press Enter key and drag the mouse from the cells E6 to E10. 40125000 | 6 110000, inc Po wa Here, you will find that the simple interest calculated and placed in the cell C6 is 1250. The formula takes the relative cell values from A6 and B6, and the absolute value from the cell F3. ‘When the formulae are copied, the cell values of A and B will relatively change but the value of cell F3 will remain absolute (constant) to yield the result in the corresponding cells. Mics Computer Studies—8. —__—______ aPractice Session: Calculate the volume of a pillar changing its length and breadth and keeping its height constant. ‘The value of height is given in the cell D3. ip eeeses Perform the above task and write the formula for the cell Co and when the formulae is copied into the cells C7, C8 and C9 respectively. Mixed Referencing is a combination of relative and absolute referencing. In this reference, the data of one cell is kept absolute and other is made relative and finally they are operated together in a formula. For example, cell B3 can be made absolute and cell B4 can be made relative by representing them as $B$3 and B$4. When they are used in a formula, say to find the product, then they are used as $BS3*BS4 where, $B$3 is absolute and B$4 is relative. An example is illustrated below: The cell value B3 is absolute and the cell value B4, C4, D4 are used to get the result as mixed referencing. Hence, formula used in the cell BS =$B$3"B$4 = (12*8) = 96 Here, you will find that the area of rectangle is calculated and placed in the cell BS as 96.ae Mo) = Further, keeping the cell B3 absolute, the area in the cells C5 and D5 will be calculated by taking the relative values from C4 and D4 cells. ‘The formulae can be written to get the results in the cells C5 and D5 as: rcs aspsstcsa | (i210) = 120 ‘D5 | =SBS3*DS4 [(a3%5)=60 | | Practice Session: | With reference to the above table, make C3 and D3 cells absolute and calculate the area | in the cells BS, C5 and D5 respectively. | Now, complete the table: | 1, When the cell C3 is absolute and breadth is relative: Functions used in MS Excel MS Excel provides some pre-defined formulae, to perform various types of calculations as per the needs of the user. The built-in formulae for specific numeric/non-numeric processing are called functions, Arguments are the values which are passed onto a function to perform certain operations. The arguments can be data, the cells or the range of cells containing the data or other functions. You must take care of the following points while writing the format of a function: Fach function must begin with an ‘equal to’ (=) sign Parenthesis is used to indicate the opening and closing of a function. Arguments are written within the parenthesis. Commas are used to separate the arguments. The general format to use a function is mentioned below: Syntax: =Function name(Argument) For example, =Sum((A1L:E1), 64, 96) ‘The above function results in the sum of cell values from Ai to El including constants 64 and 96, Some more mathematical functions are discussed below. MI GSE Compe Sados—6ction calculates the sum of all the values of the specified cells, needs to select the cell where the sum function is to be carried out. ) =SUM( numberl, number?, .... sum of the cell values ranging from Al, A2, A3, ‘will be written as: 14A2+A3 +. svar to An cells, the sees + An) wy An) find the sum of the cell values in a worksheet are as follows: : Enter the cell values as shown. Select the cell where function is to be performed. ; Write the formula to find the sum as =SUM(D4:14). It calculates the sum of the cell values from D4 to 14. FE Sesion: 201-20 ‘iN [rage low [ence feappt [Tota [20006 [abhishek nance | ws _| or | ox | oo | o« | ce [cae fa |io110[sshmta crower |_00 | 00 | 90 | 70” |e7 | 0 [> 15105 oeepe Bar sz | er [eo | 7a | 6 | ee Js — ose |Henant umes [eo | 00 [a7 | en |e | 9 [5 |tsuo7_|waghunda Suman [a2 | 2 | 00 | a3 | o4 | 0a fe — [nro |eghaPraahan | en | ea | 0 | a1 ]-o3-| 7 fp fiov11 [Prac J 05 _| eo [01 | op | 0 oe fs 06 |e |"o9"[77 [ey | os ve_|-s0 [04 [96 | 90 | os to ot [so “["e7 [90] 96 eo acre a eee a drag the file handle from the cells J4 to J13 to get the sum in the corresponding tows. ‘Spreadsheet: Functions and rT alJD SACU arg 6° bal amma @) AVERAGE( ) This function calculates the average of all the values of the specified cells. The user needs 4, select the cell where this function is to be performed. Syntax: =AVERAGE(number1, number2, - To find the average of the cell values ranging from Al, A2, A3 the function will be written as: =AVERAGE(A1, A2, A3, . +: 10 An cells An) OR =AVERAGE(AI: An) The average of the above cell values can also be calculated by using the SUM() as: =SUM(Al + A2+A3 +... =SUM(A1, A2, A3, ... ” where, n = Number of cells/data items For example, () =AVERAGE(85 + 64 + 82 +90 +79) will result in 80. (ii) =AVERAGE(45, 50, 74, 71) will return the result 60. Steps to find the average of the cell values are as follows: Step 1: Enter the cell values and select the cell where average function is to be performed, Step 2: Write the formula in the selected cell as =AVERAGE(D4:14) to calculate the average of the cell values from D4 to 14. Tas [science [Abnishaenanda | ea | a7_| 91 | 90 | 98 | 9 fio [Asia Chewshay | 69 | 28 | as | 79 | 97 | 99 | sae 16105 [Deepa Bhar sr_| sa | 7s | 96 | 93 | 530 | Hemant Kumar | a9 | 30 | 97 | es | o7 | 97 | see fsvo7_[uachumia Suman | az] a2 | 90 | a3 | sa | oe | sz [20507 [Megha Pradhan | a | a3 [9s | a | 93 | 97 [535 foa3_[Pacht Jn 6 |e | 9a | 531 e408 [Saurabh a) 96 m_[ er | 9s] 535 oo |e | 96 | ser 110 [Pushkar Parasar | s1_| $0 | 67 | 89 | 96 | a9 | sea Step 3: Press the Enter key to get the average of the cell values in the cell K4. Step 4: Drag the file handle from the cell K4 to K13 to get the average of the corresponding rows. MBM cse Computer Studies—8