Topics: Microsoft Excel: (12 Marks)

Microsoft Excel and Google Sheets are both spreadsheet software. Microsoft Excel is developed by Microsoft
Company and Google Sheet is developed by Google.
Excel and Google Sheets help with assignments, budgeting, and project planning.

Excel Questions
1. A Health Worker is using MS Excel to gather information about the patient, which includes their name, age, height,
and weight for BMI calculation.
Which MS Excel feature should the Health worker use to accept only Whole numbers in the Weight column of the
table below?
A Filter C Sort
B Restriction D Data Validation

2. Consider the following table that shows the quantity, fruit type, and profit of fruits sold by a fruit seller:
Among the fruits he sells, a fruit seller is interested in knowing only the profit from selling apples. Which
Excel function should be used to calculate the total profit from selling apples?

A. =SUMIF(A2:A5, "Apple", C2:C5) C. =SUMIF(B2:B5, "Apple", A2:A5)

B. =SUMIF(B2:B5, "Apple", C2:C5) D. =SUMIF(B2:B5, "Banana", C2:C5)

Explanation: The function =SUMIF (B2:B5, "Apple", C2:C5) checks each cell in the range B2:B5. If the
cell value is "Apple", it adds the corresponding profit value from column C (in the same row) to the sum.
This gives the total profit from selling apples.

3. Which of the following functions can a Class IX student use in MS Excel to count the number of cells
with the value 'BLUE' in column D?
A. AVERAGEIF C. SUM
B. COUNT D. COUNTIF
 4. Which of the following functions given below should the ICT teacher use in the result sheet to determine the
number of students who scored above 60%?
A =COUNTIF(H2:H6,>60)
B =COUNTIF(H2:H6,">=60")
C =COUNTIF(H2:H6,>=60)
D =COUNTIF(H2:H6,">60")

Explanation
The formula you provided, D = COUNTIF(H2:H6,">60"), is an example of a formula in a spreadsheet program like
Microsoft Excel or Google Sheets. This formula calculates the count of cells in the range H2 to H6 that contain values
greater than 60.

5. Which feature in MS Excel can be used to apply the color 'red' to the values that are less than 40?
A. Validation C. Logical functions
B. Sort and filter D. Conditional formatting

6. Which of the following options accurately describes the advantages of using charts in data visualization?
I To communicate data graphically
II Help people understand and remember information.
III Data can be transferred easily.

A I and II C II and III

B I and III D All of the above

7. A shop has designed a sales system table in MS Excel and wants to search for the total sales by entering a value under
the "Sl No" field. Which function from the given options should be used to accomplish this task?

A. HLOOKUP C. MATCH
B. VLOOKUP D. INDEX

Explanation
To use VLOOKUP in MS Excel for the given sales system table, you can follow these steps:
Select a cell where you want to display the result of the VLOOKUP function.
Use the formula =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).
8. When entering values in MS Excel spreadsheets, you are currently in the cell located in the 5th column and 2nd row.
Which of the following options correctly represents the cell reference for your active cell?

A. E5 C. B5
B. E2 D. 2E

Answer for Multiple Choice Questions Above

1. D 4. D 7. B
2. B 5. D 8. B
3. D 6. A

Fill in the blanks

1. To highlight the cells that contain products with sales amounts greater than Nu. 7000 per day, Raju can
utilize the___________________ feature in MS Excel.
2. In MS Excel, the process of rearranging values in the rows or columns to organize information in a desired
sequence either in ascending or descending order is called _______________
3. In MS Excel, to create an ____________ reference that does not change when copied to other cells, you can
use a dollar sign ($) before the column and/or row reference.
4. A group of students used MS Excel for their project. Later on, their teacher asked them to use the Google
Workspace application instead of MS Excel. The application they can use is ________________

Answers:
1. conditional formatting 3. Absolute
2. Sort. 4. Googlesheet

TRUE and FALSE

1. Filtering data deletes the filtered rows or columns from the worksheet permanently.
2. The statement "SUMIFS(D2:D6, C2:C6, 'Mango') is used to calculate the total amount from the range
(C2:C6), containing the text 'Mango' ranging in (D2:D6)" .
The correct statement would be:
"SUMIFS(D2:D6, C2:C6, 'Mango') is used to calculate the total amount from the range (D2:D6), containing
the text 'Mango' based on the corresponding values in range (C2:C6)."
3. When data is changed in MS Excel, the chart can automatically update to reflect those changes.
Answer: 1. FALSE 2. FALSE. 3. TRUE
 Long Answer Question

Practical Question 1:
a. Assist the primary school result committee in creating error-free results using correct formulas in MS Excel. They
need help to ensure the accuracy of their calculations and formulas.

i. Calculate the total marks scored by Tempa Rinzin using MS Excel functions.
Answer:
The total mark scored by Tempa Rinzin is 140
=sum(E2:G2)
ii. What is the correct function syntax in MS Excel to calculate the ranking of Tempa Rinzin in column J in descending
order as requested by the teacher?
Answer:
=RANK(i3,$i$3:$i$7,0)
=RANK(cell, reference, [order])
cell: The value for which you want to find the rank.
reference: The range of cells that contains the values among which the rank is calculated.
[order]: Specifies the order of ranking. If omitted or set to 0, the function ranks in descending order (largest to
smallest). If set to 1, it ranks in ascending order (smallest to largest).
iii. How can you merge the first name and last name of each student and display the combined names in column D using
an MS Excel function?
Answer:
CONCATENATE/ CONCAT
iv. Calculate the average score of students who scored more than 60 in the subject "Dzo" using an MS Excel function.
Answer:
Average score is 46.66
Syntax: Syntax is: RANGE, CRITERIA, AVERAGE RANGE
=averageif(E2:E4, “>60”, E2:E4)

v. Validate all the cells so that the user cannot enter values more than 100.
vi. Use a conditional formatting tool to highlight the marks with red color in each subject whose score is
less than 40.

vii. Create visual representations of your data through charts and graphs. Construct a Pie Chart using
the First Name and Average, and generate a Bar Graph using First Name and Total, including data
labels for clarity.
Practical Question 2
Fruits vendor keeping fruits sales record in MS Excel as follows.

i. As per the record above the total fruits sold was 260, you as an IT student write the formulas for how he
calculated 260 in cell D8.
ii. Show the formula to calculate the total profit earned only from selling Apple.
iii. If you see “###" while working with MS Excel how will you solve it? Explain.
iv. Which two tabs will you click in MS Excel to freeze the top row for viewing while scrolling down and
to access the data validation option?

Answer:
i. Formula in cell E8: =SUM(E3:E7)
ii. =F3-E3*C3 or =E3*D3 -E3*C3
iii. The "###" sign indicates that the column width is not sufficient to display the entire value
Expanding the column width or adjusting the formatting can resolve this issue, allowing the
complete value to be displayed.
iv. Freeze the top row- “View” Tab
For Data validation - "Data" Tab

3. What are some methods for validating and cleansing data in MS Excel?
● Sort - Arranging data in a meaningful order, either in ascending or descending order.
● Filter - Choosing a smaller part of the data that meets certain criteria.
● Conditional Formatting – This enables you to highlight cells with colours, which meet certain criteria.
● Find, replace and comment - enables you to find and replace texts or numbers and also add comments
to a particular cell.
4. Mention two reasons why charts are used to represent the data in Excel or Why choose charts over
plain data?
Answer:
- Because charts display a series of numeric data in graphical form.
- To communicate data graphically.
- It allows users to visually compare data graphically.
- Help people understand and remember information

5. While Microsoft Excel and Google Sheets serve similar purposes as spreadsheet applications, they
differ in their mode of operation. Enumerate at least two contrasts between Microsoft Excel and
Google Sheets.
Answer:
Excel Google sheet
Developed by Microsoft. Developed by Google

In Excel, Save file manually File is saved automatically

Saved in Computer drive/ personal files Saved in google drive

In MSExcel we Cannot collaborate. Collaboration in real-time, as multiple users can

work on the same document simultaneously in
Google sheets.

We can work offline. We can not work online

Some Important Functions , Features and Formula in Maexcel
Functions:
1. SUM(): Calculates the sum of values in a specified range.
Example: =SUM(A2:A10) calculates the sum of values in the range A2:A10.
2. AVERAGE(): Calculates the average of values in a specified range.
Example: =AVERAGE(B2:B10) calculates the average of values in the range B2:B10.
3. COUNT(): Counts the number of cells in a specified range.
Example: =COUNT(A2:A10) counts the number of cells in the range A2:A10.
4. COUNTA(): Counts the number of cells that contain any form of content, excluding blanks.
Example: =COUNTA(B2:B10) counts the number of non-blank cells in the range B2:B10.
5. COUNTBLANK(): Counts the number of blank cells within a specified range.
Example: =COUNTBLANK(C2:C10) counts the number of blank cells in the range C2:C10.
6. IF(): Checks whether a condition is true or false and returns one value if true and another if false.
Example: =IF(D4>=10, "Pass", "Fail") checks if the value in cell D4 is greater than or equal to 10 and
returns "Pass" if true, "Fail" otherwise.
7. COUNTIF(): Counts the number of cells within a range that meet a specified condition.
Example: =COUNTIF(E4:E13, ">=10") counts the number of cells in the range E4:E13 that are greater than
or equal to 10.
8. MAX(): Returns the largest value in a range of values.
Example: =MAX(F4:F13) returns the largest value from the range F4:F13.
9. MIN(): Returns the smallest value in a range of values.
Example: =MIN(G4:G13) returns the smallest value from the range G4:G13.
10. LARGE(): Returns the nth largest value in a range.
Example: =LARGE(F4:F13, 1) returns the largest value from the range F4:F13.
11. SMALL(): Returns the nth smallest value in a range.
Example: =SMALL(G4:G13, 1) returns the smallest value from the range G4:G13.
12. RANK(): Returns the rank of a value in a dataset.
Example: =RANK(H4, H4:H13) returns the rank of the value in cell H4 compared to the values in the range
H4:H13.
13. SUMIF(): Adds up values based on a specified condition.
Example: =SUMIF(I4:I13, ">=100") sums the values in the range I4:I13 that are greater than or equal to
100.
14. AVERAGEIF(): Calculates the average of numbers based on a specified condition.
Example: =AVERAGEIF(J4:J13, ">=18") calculates the average of numbers in the range J4:J13 that are
greater than or equal to 18.
15. VLOOKUP(): Searches for a value in the first column of a table and returns a value in the same row from a
specified column.
Example: =VLOOKUP(A11, A3:D7, 4, FALSE) searches for the value in cell A11 in the first column of the
table A3:D7 and returns the value from the fourth column.
16. HLOOKUP(): Searches for a value in the first row of a table and returns a value in the same column from a
specified row.
 Example: =HLOOKUP(A11, A3:D7, 3, FALSE) searches for the value in cell A11 in the first row of the
table A3:D7 and returns the value from the third row.
17. CONCATENATE(): Combines multiple text strings into one string.
Example: =CONCATENATE(A2, " ", B2) combines the text in cell A2, a space, and the text in cell B2 into
one string.
18. SUMIF(): Adds up values based on a specified condition.
Example: =SUMIF(I4:I13, ">=100") sums the values in the range I4:I13 that are greater than or equal to
100.
19. AVERAGEIF(): Calculates the average of numbers based on a specified condition.
Example: =AVERAGEIF(J4:J13, ">=18") calculates the average of numbers in the range J4:J13 that are
greater than or equal to 18.

Here are 5 important Data Analysis Tools in Excel,

1. Conditional Formatting: Conditional Formatting: Conditional formatting in Excel allows you to
highlight cells with a certain color, depending on the cell's value.. For instance, highlight sales above
$100 in green and below $50 in red.
2. Sort: The sorting tool allows you to sort by date, number, alphabetic order and more.
3. Freeze Panes: Keep specific rows or columns visible while scrolling through a large worksheet. For
example, freeze the top row to keep column headers in view as you scroll down.
4. Data Validation: Control the type and format of data entered into cells. For instance, restrict input to
dates between 01/01/2022 and 12/31/2022.
5. PivotTables: Summarize and analyze large datasets by rearranging and summarizing data from
different perspectives. For example, create a PivotTable to show total sales by product category.
6. Sparklines: Insert miniature charts into cells to visualize data trends at a glance. For example, add
sparklines to show trends in monthly sales data over the year.
7. Charts: Helps us to represent data in graphs and line with markers.
8. Sort: The sorting tool allows you to sort by date, number, alphabetic order and more.
9. Filter: Will separate your Excel data if you only want to display records that meet certain criteria.