OAT-Record-08 ) SORTING

 

Enter the following data in to the sheet.

A	B	C	D
1	name	department	salary
2	anusha	accouncts	12000
3	rani	engineering	24000
4	lakshmi	accouncts	9000
5	purnima	marketing	20000
6	bindu	accouncts	4500
7	tejaswi	accouncts	11000
8	swetha	engineering	15000
9	saroja	marketing	45000
10	sunitha	accouncts	5600
11	sandya	engineering	24000
12	harika	marketing	8000

 

1.       Extract records for department tin Accounts and Salary > 10000.

2.       Sort the data by salary with the department using “sort commands”.

3.       Calculate total salary for a ch department using Subtotals.

 PProcedure: -

STEP 1: 

    Enter into MS-EXCEL environment by choosing start à Programs à MS Office à MS Excel.

STEP 2: 

    Now save the opened Blank document with the following step Office Button à save à sorting

STEP 3: 

    Write a given data entry to cells à Select or drag table (8*13) à Select ALL Borders

1.      Extract records for department tin Accounts and Salary > 10000.

STEP 4: 

    Select the Data cells (F1 g1 H1) à Go to Home Tab Sorting and Filter click à select to Filter apply

                Department filter remove select all and select Accountant click.

STEP 5: 

    Select the Data cells (F2 to H10) à Go to Home Tab Sorting and Filter click à select to custom sort

                 Sort by  : salary

                Sort on : cell values

                Order    : largest to smallest   à click OK

 

 

A	B	C	D	E	F	G	H
1	name	department	salary		name	department	salary
2	anusha	accouncts	12000		anusha	accouncts	12000
3	rani	engineering	24000		rani	engineering	24000
4	lakshmi	accouncts	9000		lakshmi	accouncts	9000
5	purnima	marketing	20000		purnima	marketing	20000
6	bindu	accouncts	4500		bindu	accouncts	4500
7	tejaswi	accouncts	11000		tejaswi	accouncts	11000
8	swetha	engineering	15000		swetha	engineering	15000
9	saroja	marketing	45000		saroja	marketing	45000
10	sunitha	accouncts	5600		sunitha	accouncts	5600
11	sandya	engineering	24000		sandya	engineering	24000
12	harika	marketing	8000		harika	marketing	8000

 

A	B	C	D	E	F	G	H
1	name	department	salary		name	department	salary
2	anusha	accouncts	12000		anusha	accouncts	12000
4	lakshmi	accouncts	9000		tejaswi	accouncts	11000
6	bindu	accouncts	4500		lakshmi	accouncts	9000
7	tejaswi	accouncts	11000		sunitha	accouncts	5600
10	sunitha	accouncts	5600		bindu	accouncts	4500

 

1.      Sort the data by salary with the department using “sort commands”.

 STEP 6: 

    Select the Data cells (F2 to H10) à Go to Home Tab Sorting and Filter click à select to custom sort

                 Sort by  : salary

                Sort on : cell values

                Order    : smallest to largest   à click OK

STEP 7: 

    Select the Data cells (F2 to H10) à Go to Home Tab Sorting and Filter click à select to custom sort

                 Sort by  : department

                Sort on : cell values

                Order    : A to Z   à click OK

 

A	B	C	D	E	F	G	H
1	name	department	salary		name	department	salary
2	anusha	accouncts	12000		bindu	accouncts	4500
3	rani	engineering	24000		sunitha	accouncts	5600
4	lakshmi	accouncts	9000		lakshmi	accouncts	9000
5	purnima	marketing	20000		tejaswi	accouncts	11000
6	bindu	accouncts	4500		anusha	accouncts	12000
7	tejaswi	accouncts	11000		swetha	engineering	15000
8	swetha	engineering	15000		rani	engineering	24000
9	saroja	marketing	45000		sandya	engineering	24000
10	sunitha	accouncts	5600		harika	marketing	8000
11	sandya	engineering	24000		purnima	marketing	20000
12	harika	marketing	8000		saroja	marketing	45000

 

STEP 8:

     cell G1 Department filter click à remove select all and select Accountant click.

STEP 9: 

    cell H7 à subtotal F_X () = subtotal (9, H 2: H 6)

                 Note:  9 --sum

E	F	G	H
1	name	department	salary
2	bindu	accouncts	4500
3	sunitha	accouncts	5600
4	lakshmi	accouncts	9000
5	tejaswi	accouncts	11000
6	anusha	accouncts	12000
7	subtotal	accouncts	42100

STEP 10: 

    cell G1 Department filter click à remove select all and select engineering click.

STEP 11: 

    cell H11 à subtotal F_X () = subtotal (9, H 8: H 10)

                 Note:  9 --sum

 

E	F	G	H
1	name	department	salary
8	swetha	engineering	15000
9	rani	engineering	24000
10	sandya	engineering	24000
11	subtotal	engineering	63000

STEP 12: 

    cell G1 Department filter click à remove select all and select engineering click.

STEP 13: 

    cell H15 à subtotal F_X () = subtotal (9, H 12: H 14)

                 Note:  9 --sum

 

E	F	G	H
1	name	department	salary
12	harika	marketing	8000
13	purnima	marketing	20000
14	saroja	marketing	45000
15	subtotal	marketing	73000

 

STEP 5:  

    Goto Home Tab click Alignment.

STEP 6: 

    Press Ctrl+S or form the menu option click File-> Save.

STEP 7: 

    Press Alt+F4 or click Office Button-> Exit from menu to exit excel.