EXCEL TEST QUESTIONS

Unnamed: Unnamed:
1 Unnamed:
2 Unnamed:
3 Unnamed:
4 Unnamed:
5 6

1 Combine First Name & Last Name together using formula in Column E
First NameLast Name Combine 2 marks
Ramesh Kumar
John Mike
Suresh Kumar

2 Separate Last Name alone using formula

Names (FirLast Name
Ramesh Kumar 2 marks
John Mike
Suresh Kumar

3 Fill the Total Sales using formula

Product Area sales 2 marks
Keyboard Mumbai 250000
Mouse Chennai 300000
CPU Andra Prad 700000
Cables Delhi 300000
Monitor Kerala 500000
Total Sales

4 Fill the Average (D31) using formula. 2 marks

Product Area sales
Keyboard Mumbai 250000
Mouse Chennai 300000
CPU Andra Prad 700000
Cables Delhi 300000
Monitor Kerala 500000
Average

5 Kindly Update the Rows & Colu 2 marks

Rows Count
Column Count

6 Kindly Merge the Headings & Apply the ALL Borders

Area Wise Sales Department 2 marks

Product Area sales
 Keyboard Mumbai 250000
Mouse Chennai 300000
CPU Andra Prad 700000
Cables Delhi 300000
Monitor Kerala 500000

7 Kindly Trim the Words into Corr 2 marks

Ramesh Kumar
John Mike
Suresh Kumar
Ravi Kumar

8 i. Apply the Format as Table Design, Centre3 marks

ii Fill the Total sales & Grand Total Using Formulas

Order No Area Product Quantity Unit Price Total Sales

1 Mumbai Keyboard 31 93
2 Chennai Mouse 245 750
3 Andra PradCPU 67 470
4 Delhi Cables 248 357
5 Kerala Monitor 357 245
6 Tamil NaduGrid Tie In 428 904
7 Karnataka Solar Panel 590 257
8 Pune Batteries 37 865
9 Haryana Grid Tie In 96 964
10 Goa Solar Panel 56 1023

9 Kindly change the Total Sales Value is s 2 marks

Order No State Category Quantity Unit Price Total Sales

1 Andhra PraSolar Panel 31 93 2883
2 Arunachal Grid Tie In 245 750 183750
3 Nepal Batteries 67 470 31490
4 Bihar Light Bulbs 248 357 88536
5 Goa Batteries 357 245 87465
6 Gujarat Grid Tie In 428 904 386912
7 Haryana Solar Panel 590 257 151630
8 Himachal PBatteries 37 865 32005
9 Jammu & KGrid Tie In 96 964 92544
10 Chhattisga Solar Panel 56 1023 57288
10 Kindly Calculate & Change the v 2 marks

Names Marks
Imman 54
Elavarasan 67
Dinesh 86
Chandru 98
Babu 54
Aravind 34
Govind 76
guru 67
Fahad 98
Haritha 84

11 Kindly Copy the format From Cel2 marks

Names Marks
Chandru 98
Babu 54
Aravind 34
Govind 76
guru 67
Fahad 98
Haritha 84

12 Kindly Change the Font size is should be 1 2 marks

Product
Keyboard
Mouse
CPU
Cables
Monitor

13 Apply the Proper For 2 marks

arunachal pradesh
nepal
andhra pradesh
goa
gujarat
14 Kindly Count the number Cells W4 marks

[Link] Member State Category

1 Gopal Karnataka Solar PanelFormula
2 Ganesh Kerala Grid Tie Inverters
3 Priya Jharkhand Batteries
4 Gayathri Madhya PrSolar Panels
5 Kumar Maharastr Grid Tie Inverters
6 Venkatesh Manipur Batteries
7 Guna MeghalayaLight Bulbs
8 Amit Mizoram Batteries
9 Adhavan Pune Grid Tie Inverters
10 Arunachal Lucknow Solar Panels
11 Kamlesh HyderapadBatteries
12 Sriram Rajasthan Grid Tie Inverters

15 kindly Creat the New Data Table5 marks

16 Please fill the Short cut Keys

Users ShortCuts 10 marks

Auto Sum
Select the Entire Row
Select the Entire Column
Format Cells
Insert Table
Dublicate Worksheet
Start a New Line in the Same Cell
Edit the Active Cell
Last Operation
Open New Workbook
 1.Arrange1the Names with
Unnamed:
A to Z Format
Unnamed:
2 3 2 Marks Unnamed: Unnamed:
5 Unnamed:
6 Unnamed:
7 8
[Link] marks percentage based on Total_marks
Names Marks
Imman 54
Elavarasan 67
Dinesh 86
Chandru 98
Babu 54
Aravind 34
Govind 76
guru 67
Fahad 98
Haritha 84

2 Please find the Begins with word is should b2 marks

Name of Students
Raghu
Chandru
Babu
Aravind
Ramesh
Ganesh
Rajesh
Arun
Bala
Krishna

3 Please find the Begins with word is should be "A" & Ends with word is should
2 marks
Name of Students
Raghu
Chandru
Babu
Aravind
Ramesh
Ganesh
Rajesh
Arun
Bala
Krishna
Adhavan
Filter the
1 Gaythri & Adhavan
Unnamed:
Data
Unnamed:
2 Unnamed:
3 4 2 marks

Cust Full State Category

Gopal Karnataka Solar Panels
Ganesh Kerala Grid Tie Inverters
Priya Jharkhand Batteries
Gayathri Madhya PrSolar Panels
Kumar Maharastr Grid Tie Inverters
Venkatesh Manipur Batteries
Guna MeghalayaLight Bulbs
Amit Mizoram Batteries
Adhavan Pune Grid Tie Inverters
Sriram Rajasthan Grid Tie Inverters
Arunachal Lucknow Solar Panels
Kamlesh HyderapadBatteries
Kindly
1 Sort the Yellow
Unnamed:
color Unnamed:
2 Unnamed:
3 4 2 marks

Cust Full State Category

Gopal Karnataka Solar Panels
Ganesh Kerala Grid Tie Inverters
Priya Jharkhand Batteries
Gayathri Madhya PrSolar Panels
Kumar Maharastr Grid Tie Inverters
Venkatesh Manipur Batteries
Guna MeghalayaLight Bulbs
Amit Mizoram Batteries
Adhavan Pune Grid Tie Inverters
Arunachal Lucknow Solar Panels
Kamlesh HyderapadBatteries
Sriram Rajasthan Grid Tie Inverters
Kindly
1 Filter the Red
Unnamed:
Color Unnamed:
2 3 2 marks

Names Marks
Imman 54
Elavarasan 67
Dinesh 86
Chandru 98
Babu 54
Aravind 34
Govind 76
guru 67
Fahad 98
Haritha 84
nd the Begins with1 word is should be
2 marks
"RA" while using Text Filters
Name of Students
Raghu
Chandru
Babu
Aravind
Ramesh
Ganesh
Rajesh
Arun
Bala
Krishna
Kindly 1Remove the Dublicate
Unnamed:States
Unnamed:
2 3 2 marks
State
Nepal
Arunachal Pradesh
Nepal
Bihar
Goa
Andhra Pradesh
Arunachal Pradesh
Andhra Pradesh
Bihar
Goa
 Set 8 - Date & Text Functions Tasks
calculate the number of years between Date of Birth and Today’s Date
find the month-end date for each Joining Date
calculate the date after 6 months from the Joining Date
find which day of the week each given date falls on
find the week number of each Order Date
combine First Name and Last Name into Full Name
extract the first 3 letters of Employee Code
extract the last 4 digits of Mobile Number
extract characters 3 to 6 from an Employee ID
remove extra spaces from Employee Names
replace 'Ltd' with 'Limited' in Company Names

Employee ID

E101

E102

E103
E104
 First Name Last Name DOB Joining Date Mobile Company Name

Arun Kumar 5/14/1995 6/1/2020 9876543210 Tech Solutions Ltd

Meena Rani 8/20/1998 11/15/2021 9123456789 Green Energy Ltd

Raj Sharma 12/2/1992 1/10/2019 9988776655 Global Systems Ltd

Divya Singh 3/11/2000 4/25/2022 9001122334 Bright Future Ltd
 Employee Code

EMP12345

EMP23456

EMP34567
EMP45678
 Set 9 - SUMIF / COUNTIF Functions Tasks
calculate the total sales of 'Product A'
calculate the total salary of employees in the 'HR' department
calculate the total sales of 'Product B' in 'Region East'
calculate total orders placed in 2023 by 'Customer X'
count how many employees belong to the 'Finance' department
count how many orders have amount greater than 5000
count how many employees are in 'IT' department and joined after 2022
count how many sales were made in 'Region West' for 'Product C'

Order ID

101

102

103

104

105
106
 Customer Product Region Order Date Sales Amount Department Salary

Customer A Product A East 5/10/2022 4500 HR 30000

Customer B Product B West 1/15/2023 8000 Finance 40000

Customer A Product C East 3/22/2023 12000 IT 35000

Customer C Product A West 7/5/2022 3000 Finance 42000

Customer B Product B East 8/30/2023 7000 HR 31000

Customer A Product C West 9/12/2023 15000 IT 50000
Joining Year

2021

2020

2023

2019

2022
2023