Cpt Excel Practice Questions

(1)

A B C D E F G 1 Name

2 Roll No 3

4

5

Year

SC

ST

OBC

Gen

Total

6

2000

100

25

225

556

7

2001

105

45

230

852

8

2002

123

133

412

965

9

2003

85

39

362

475

10

Average

11

By following the given steps, you have to design the above worksheet using

MS-Excel:

1. Open new Workbook of Excel.

2. Set paper size to ‘A4’, orientation to Portrait and margin to ‘Normal’ or

according to given measurement.

3. Change font size of cell to ‘14’ and font name to ‘Arial’.

4. Merge the cells of row 4, change the font size to 16 and make it Bold,

Underlined and Center aligned.

5. Make the title of category and year (content of row 5 and column A)

Centre aligned and figures (data entered in concerned cell) Right aligned.

6. Change the Background colour of Total and Average to given colour (as

shown in above worksheet) and used appropriate function of excel to get

these values.

7. Create border as given in above worksheet.

8. Adjust Height and Width of cells as required.

9. Save your Workbook with the name in given format and also Print this

Worksheet.

Category wise list of candidates

QUESTION 1

SSCEXAMFORUM.COM

(2)

A B C D E F 1 Name

2 Roll No 3

4

5 _{Class 1} _Maths _Hindi _English _Total _Average

6 A 97 66 98 7 B 44 42 66 8 C 68 78 95 9 _D ₉₈ ₈₀ ₉₃ 10 E 79 85 99 11

By following the given steps, you have to design the above worksheet using MS-Excel:

1. Open new Workbook of Excel.

2. Set paper size to ‘A4’, orientation to Portrait and margin to ‘Normal’ or

according to given measurement.

3. Change font size of cell to ‘14’ and font name to ‘Arial’.

4. Merge the cells of row 4, change the font size to 16 and make it Bold, Underlined and Center aligned.

5. Make the title of Subject and student (content of row 5 and column A) Centre aligned and figures (data entered in concerned cell) Right aligned. 6. Change the Background colour of Total and Average to given colour (as shown in above worksheet) and used appropriate function of excel to get these values.

7. Create border as given in above worksheet. 8. Adjust Height and Width of cells as required.

9. Save your Workbook with the name in given format and also Print this Worksheet.

Subject wise Marks of students

QUESTION 2

SSCEXAMFORUM.COM

(3)

A B C D E F 1 Name

2 Roll No 3

4

5 _{School Name} _{Marks in Test} _{Marks in}

practical Exam

Total student Total marks Average per student

6 _{sarvodaya Vidyalya} _2678.75 _435.7 ₃₄₅ 7 _{Delhi Public School} _3242.35 _345.5 ₃₆₈ 8 _{Lko Govt School} _3424.77 _234.75 ₇₈₉ 9 _{Loreto convent} _5336.75 _432.5 ₂₃₄ 10 _{Total Marks}

11

By following the given steps, you have to design the above worksheet using MS-Excel: 1. Open new Workbook of Excel.

2. Set paper size to ‘A4’, orientation to Landscape and margin to ‘Normal’ or according to given

measurement.

5. Make the title of Marks and schools (content of row 5 and column A) Centre aligned and figures (data entered in concerned cell) Right aligned.

6. Change the Background colour of Total and Average to given colour (as shown in above worksheet) and used appropriate function of excel to get these values.

Scoresheet of class XII

QUESTION 3

SSCEXAMFORUM.COM

(4)

Sheet 5 A B C D E F G H I 1Name 2Roll No 3 4 5

Sl.no. Name Designation Basic Salary House Rent

Allowance

Medical Allowance

Travel Allowance Provident Fund Net Salary

6 1 suresh Clerk 17000

7 2 abhinav Peon 12500

8 3 himanshu Saleman 14500

9 4 abhishek Gardener 7500

10 5 Ramesh` Guard 9000

House Rent Allowancw = 9% of Basic Salary Medical Allowance = 6% of Basic Salary Travel Allowance = 7% of Basic Salary Provident Fund = 5% of Basic Salary

Net Salary = Basic Salary + House Rent Allowance + Medical Allowance + Travel Allowance - Provident Fund By following the given steps, you have to design the above worksheet using MS-Excel:

1. Open new Workbook of Excel.

2. Set paper size to ‘A4’, orientation to Landscape and margin to ‘Narrow’ or according to given measurement.

5. Make the title of content of row 5 and column A Centre aligned and figures (data entered in concerned cell) Right aligned.

6. Change the Background colour of Allowance and Net Salary to given colour (as shown in above worksheet) and used appropriate function of excel to get these values.

Save your Workbook with the name in given format and also Print this Worksheet.

Net Salary of Emloyee

QUESTION 4

SSCEXAMFORUM.COM

(5)

A B C D E F G

1 NAME

2 ROLL NO. 3

4

5 NAME AGE HINDI ENGLISH MATH TOTAL PER(%) MAX MIN

6 HEMA 18 85 95 75.65 7 SHILPA 19 75.65 45 56 8 AMRITA 17 56 33 58 9 RITU 19 58 75.65 45 10 PINKU 21 95 56 33 11 PAWAN 18 45 58 58 12 RAVI 18 33 85 95

QUESTION 5

Underlined and Center aligned. 5 CENTER aligned.

6. Change the Background colour of Total,PER, MAX, MIN to given colour (as

SCORESHEET OF CLASS XII

MS-Excel:

1. Open new Workbook of Excel.

2. Set paper size to ‘A4’, orientation to Landscape and margin to ‘NARROW’ or

according to given measurement.

3. Change font size of cell to ‘13’ and font name to ‘Arial’.

4. Merge the cells of row 4, change the font size to 18 and make it Bold,

By following the given steps, you have to design the above worksheet using

SSCEXAMFORUM.COM

(6)

9. Save your Workbook with the name in given format and also Print this these values.

7. Create border as given in above worksheet. 8. Adjust Height and Width of cells as required.

(7)

27.11.2014 A B C D E F G 1 NAME 2 ROLL NO. 3 4

5 NAME AGE SALARY -1 SALARY-2 SALARY-3 TOTAL SALARY HRA-1 HRA-2 HRA-3 TOTAL HRA

6 ABHI 18 5000 9500 7656 7 VIN 19 7000 4500 5600 8 SAHU 17 5600 3300 5800 9 ARJ 19 5800 7565 4500 10 PRI 21 9500 5600 3300 11 PAWAN 18 4500 5800 5982 12 RAVI 18 3300 8500 9500 HINT:

By following the given steps, you have to design the above worksheet using MS-Excel:

2. Set paper size to ‘A4’, orientation to Landscape and margin to ‘Narrow’ or

4. Merge the cells of row 4, change the font size to 16 and make it Bold, Underlined and Center aligned.

5. Make the title of (content of row 5 and column A)

Centre aligned and figures (data entered in concerned cell) CENTER aligned.

6. Change the Background colour of Total and HRA 1,HRA 2, HRA 3 to given colour and used appropriate function of excel to get

these values.

IF(D7>5000,1500,800)

CALCULATE HRA FOR THE YEAR,2013-2014

IF SALARY IS MORE THAN 5000 THAN HRA IS PAID 1500 REST 800

QUESTION 6

SSCEXAMFORUM.COM

(8)

27.11.2014

9. Save your Workbook with the name in given format and also Print this 10 HEDER AND FOOTER

(9)

A B C D E F G

1 NAME

2 ROLL NO. 3

4

5 NAME AGE STATUS AGE STATUS AGE STATUS AGE STATUS

6 A 18 15 35 14 7 B 19 16 65 78 8 V 17 12 55 25 9 B 19 13 25 65 10 N 21 17 45 32 11 Q 18 11 18 22 12 R 18 10 44 31 HINT:

5.Centre aligned and figures (data entered in concerned cell) LEFT aligned.

QUESTION 7

CALCULATE THE AGE OF STUDENT

IF(B >20,"YOU ARE MAJOR","YOU ARE MINOR")

SSCEXAMFORUM.COM

By following the given steps, you have to design the above worksheet using MS-Excel:

2. Set paper size to ‘A4’, orientation to Landscape and margin to ‘Narrow’ or

4. Merge the cells of row 4, change the font size to 16 and make it Bold, Underlined and Center aligned.

6.Change the Background of AGE and STATUS in different colour (as shown in above worksheet) and used appropriate function of excel to get these values.

(10)

10

8. Adjust Height and Width of cells as required.

9. Save your Workbook with the name in given format and also Print this HEDER AND FOOTER

(11)

A B C D E F G

1 NAME

2 ROLL NO.

3 4

5 NAME AGE COURSE A-DATE F-DATE DIFF-DAYS FINE

6 ABHI 18 MBA 05.01.2014 08.02.2014 7 VIN 19 MBBS 06.02.2014 09.03.2014 8 ASHOK 17 CAT 07.03.2014 11.05.2014 9 SHILPA 19 MAT 08.04.2014 08.06.2014 10 RAVI 21 CA 09.05.2014 11.05.2014 11 ASHU 18 CS 10.06.2014 11.06.2014 12 RAHUL 18 MA 11.07.2014 11.09.2014 HINT: 10

Underlined and Center aligned.

9. Save your Workbook with the name in given format and also Print this HEDER AND FOOTER & Make a CHART OF NAME AND FINE

5.Centre aligned and figures (data entered in concerned cell) CENTER aligned. 6. Change the Background colour of Name and Fine to given colour (as shown in above worksheet) and used appropriate function of excel to get these values.

SSCEXAMFORUM.COM

2. Set paper size to ‘A4’, orientation to PORTRAIT and margin to ‘Narrow’ or

4. Merge the cells of row 4, change the font size to 16 and make it Bold,

QUESTION 8

CALCULATE LATE FEE OF STUDENT

IF(G7>60,20000, IF(G7>30,15000, IF(G7<30,10000,)))

By following the given steps, you have to design the above worksheet using MS-Excel: 0 0.5 1 AB H I VIN AS H O K SHI LPA RAV I AS H U RAHU L

FINE

SSCEXAMFORUM.COM

(12)

A B C D E 1 NAME 2 ROLL NO. 3 4 5

CITY Population in year 2001

Population in year 2002 Increase in population percentage increase in population 6 delhi 2,247,365 2,512,458 7 mumbai 1,825,368 2,258,745 8 Lucknow 1,935,754 2,569,784 9 Allahabad 2,824,156 3,245,878 10 Total 8,832,643 10,586,865

By following the given steps you have design the above worksheet using MS-Excel:- 1. Open new workbook of Excel.

2. Set paper size to'A4' orientation Landscape and margin to "Narrow" or according to given measurement. 3. Change font size of cell to "13.5" and font name to "Arial".

4. Merge the cells of row 4, change the font size to 16 and make it Bold, Underlined and center aligned.

5. Change the Background colour of "City" and "Percentage increase in Population " to given colour (as shown in 6. Create a border as given in above worksheet.

7. Adjust height and width of cell as required. 8. Make Graph and Header & Footer.

Save your Workbook with the name in given format and also Print this Worksheet. above worksheet) and used appropriate function of excel to get these values.

Population In Different Cities

QUESTION 9

SSCEXAMFORUM.COM

(13)

Q1. Create athe following table

Frist Name Last Name Gender Subject 1 Subject 2 Subject

Neha Mishra 52 53 26 ritu Mehra 74 83 92 hema Manchanda 25 35 62 Pawan Sinha 42 24 41 shilpa Kashyap 48 86 92 menu Mehra 42 66 73

8. find the "Heena" and convert it to "Heena"

QUESTION 10

1. Compute the table marks and percentage of each student. 2. Give status by using following conditions

3. Table field bold itlics shade green center aling if the total manks>100 so "Pass"

if the total manks>100 so "Fail"

SSCEXAMFORUM.COM

4. Apply heading on the table 'Delhi Public School' font size "20" style com 5. Insert new row above 'Ankit' and fill up entry

6.Total colum width should be "25" 7. set the table data in ascending oder

(14)

Q2.

States Online Application Offline Application

Delhi 15620 45000

up 15820 12600

jharkhand 25000 13500

Maharashtra 35600 14800

i Compute the total number of application ii. Compute the total average made to each states

iii Compute total nunber of online application and offline application bellow the

SSCEXAMFORUM.COM

colum

8. Set column width of total 10.0. iv Compute

v

1. Page size should be "A4".

2. Set Margin top :0.53, left:0.5,right:0.43,Bottam:0.59 3. Apply your name and current date in header. 4.Apply page number in footer.

5. Set Title "2013 APPLICATION FORMS" in Algerian, font 15,Center Align Throught "Word Art".

6.Create a bar graph represent total number of applications.

7. Apply conditional formating on total application less than 60000 colour red more than 60000 green.

QUESTION 11

Create this table in MS-Excel

(15)

S.NO REG FEE

TUTION FEE EXAM FEE

Ist Year Boys/SC.SC. Minority 1500 0 3550 Ist Sem 1600 32600 3550

IInd Sem 0 32890 0

IInd Year Boys/SC.SC. Minority 1800 0 3550 Ist Sem 1400 33600 3550

IInd Sem 0 34600 0

IIIed Year Boys/SC.SC. Minority 1800 0 3456

Ist Sem 1300 32560 3596

IInd Sem 0 35690 0

IVTh Year Boys/SC.SC. Minority 1750 0 3256 Ist Sem 1360 32560 2150

IInd Sem 0 12566 0

SITAPUR SANSTHAN (SSS Technical Campus) Fee Structure (session 2012-13)

NAME OF HEAD

i .Compute the total number of fees.

SSCEXAMFORUM.COM

iii. Apply All Border on Table.

5. Margin size form Top:0.59.bottam :0.64, right : 0.35, left:0.85.

iv. Title fonts size 14.bold second line too and center align.

1. Create a pie chart representing Total Fee against S No.

2. Type your Name and Roll Number in header.

3. Completed table should be print in single page.

4. Apply accounting number format on total fee.

ii .Table Headings font size 13pt.

OTHERS

QUESTION 12

Q3. Create a new table in ms-excel and fill up entries.

(16)

Frist Name Last Name Education Political Scince Globalization shilpa Mishra 85 100 61 81 53 amrita Khandelwal 55 100 53 53 65 Pooja Mehra 56 100 72 62 61 sona Shukla 68 100 83 75 93

GRADE 'B'--More than 200

GRADE 'C'--More than 100

Hindi-A

SSCEXAMFORUM.COM

10. All columns width 15.

9. your page size should be Standard.

GRADE 'D'--Less than 100

8.Insert your name in header ,roll name and page number in footer.

5. Insert a new row at the top of 'Pooja' and fill up new entry.

6. Insert title "THIS YEAR RESULT" font size 20,style 'Impact' colour "Red".

7. Apply duble line border around the title.

QUESTION 13

4. Create pie chart representing total marks.

1. Compute total marks and percentage by using avreage formula.

2. Apply all border on table.

Q4. Create a table as given bellow

3. Apply grades by using condition given

GRADE 'A'--More than 300

(17)

Ram 15 Sham 85 Sunita 32 Yatin 25 Nitin 41 Kanika 8 Gagan 65 Naveen 52 Mehar 32 Sita 12 Lakshman 52 Anil 32 Tanya 2 Yatin 15 Atin 32 Nikita 52 Arun 86 Tarun 62 Neha 25 Ravi 5 Vevan 61 Vikram 16 vikek 18 Kannu 59 Aman 18 Heena 26

2. Count the number of eligible and not eligible persons.

SSCEXAMFORUM.COM

QUESTION 14

Q5. Create the following table and fill up all entries:

3. Apply Margin from top 0.7 & right 0.53 4. Page size should be Letter.

5.Apply All Border on table. 6. Filed size 16 font stlye impact.

Name AGE ELIGIBILITY

1. Apply Formula to get the eligible age for rvoting.

(18)

S No. Name Monthly Income Gread Scholarship Subject Offered 1 ramu 14000 A 2 dinu 22000 B 3 sudhir 13000 A 4 ravi 9500 C 5 deepu 7500 D 6 shalu 8000 B 7 prity 19000 C

50 per cent for those who having monthaly income up to 10,000 and 100 per cent for those who having more than 10,000 with a condition that students having monthaly income of more 10,000 get a maximum of scholarship 15,000.

'A' Financial Accounting 'B' Offical Management 'C' Business Marketing 'D' Reading Gandhi

Sholarship is given on the basis of Monthly income on the following

conditions:-SSCEXAMFORUM.COM

1. Sort entre list in Ascending order time.

2. Set colum width of scholarship and subject offered 18. 3. All figures in center align.

4.Set margins left:0.75,size 20,center align through "Word Art" 6.Give all Boundary lines on your table.

QUESTION 15

ASIA PACIFIC MAN GEMENT STUDITS Institution Area. allahabad-211001

And apply fomating as mentioned

below:-Conditions Scholarship and subject offered which is on the following

criteria:-Gread Name of student

(19)

SNO. Name Basic DA HRA Income PF I Tax Deductions Net Salary 1 shadab 6000 2 pawan 9000 3 pihu 5000 4 pulkit 3000 5 ankit 7000

By following the given steps, you have to design the above worksheet using MS-Excel: 1. Open new Workbook of Excel.

2. Set paper size to ‘A4’, orientation to Landscape and margin to ‘Narrow’ or according to given measurement.

5. Make the title of marks and sl.no. (content of row 5 and column A) Centre aligned and figures (data entered in concerned cell) Right aligned.

6. Change the Background colour of Total and Average to given colour (as shown in above worksheet) and used appropriate function of excel to get these values.

SSCEXAMFORUM.COM

QUESTION 16

IF(cell>8000,cell*50%,IF(cell>4000,cell*40%,cell*30%))

Formula

Condlitions:-Q7. Create a salary satement for the following format and date details using Ms-Excel.

If Basic 8000 than:- DA 50% of Basic HRA 40% of Basic PF 6% of Basic I Tax 4% of Basic

Basic 4000 Than:- DA 40% of Basic

(20)