To find the total marks of each student . To arrange the worksheet in descending order of total marks and further in the alphabetical order of names. To display those rows where the total marks are above 450. (Excel-2007)

In a Worksheet data about Name and Marks (out of hundred) in six subjects has been entered in columns A, B, C, D, E, F, G respectively in first 26 rows. In first row headings are entered, in remaining 25 rows data of 25 students has been entered. Explain the step to perform the following:

i) To find the total marks of each student in column H

Enter the formula

=SUM(B2:G2)in cell H2, and press enter key ,

using the fill handle of cell H2, copy the formula to cells H3 :H26,by dragging the fill handle up to cell H26 .

ii) To arrange the worksheet in descending order of total marks and further in the alphabetical order of names.

Select the full range of cells

                Click on DATA tab in data tab click on SORT ,sort dialog box appears then click on sort by select total and click on Order and select largest to smallest,now cick on COPY LEVEL and in THEN BY click on Name column and select A-Z and click OK button.

iii) To display those rows where the total marks are above 450.

Select entire table, click on DATA tab, in data tab click on FILTER in sort and filter group.

Now drop downs arrows appear on each column.

Click on drop down arrow of total column.

Click on Number filters ,select ‘greater than’ option now custom auto filter dialog box appears and now enter 450. And click on Ok.

Related Posts by Categories