Sindh City Portal • Shared Test Preparation

Microsoft Excel Preparation

Part 3: Spreadsheets & Formulas — 25 MCQs

Microsoft Excel spreadsheets and formulas lesson with 25 MCQs for Sindh High Court preparation

This is Part 3 of Sindh City Portal’s four-part, 100-question Computer and Microsoft Office preparation series. It is designed for candidates preparing for Sindh High Court recruitment tests and similar Pakistani competitive examinations.

1. Official syllabus status

The supplied syllabus expressly includes Excel for Telephone Operator and includes MS Office for other computer-tested posts. It does not specify functions, formulas or exact difficulty. The material below is independent preparation using broadly available desktop Excel concepts.

Environment: Formula questions use standard Excel notation and commas as argument separators. Calculations have been independently recomputed.

2. High-value lesson

Structure and references

An Excel workbook contains one or more worksheets. Columns use letters, rows use numbers, and their intersection forms a cell such as B4. A range such as A2:A10 identifies multiple cells. Formulas begin with an equals sign. Excel follows operator precedence: parentheses first, then exponentiation, multiplication and division, and addition and subtraction.

A relative reference such as A1 changes when copied. An absolute reference such as $A$1 keeps both row and column fixed. Mixed references fix only one part: $A1 fixes the column, while A$1 fixes the row. This distinction is easy to overlook, so check which part of a reference should move before filling a formula.

Core functions and logic

SUM adds values; AVERAGE returns the arithmetic mean; MIN and MAX return the smallest and largest values. COUNT counts numeric cells, while COUNTA counts non-empty cells. COUNTIF counts cells meeting one condition. IF evaluates a logical test and returns one value when true and another when false. For example, =IF(B2>=50,"Pass","Review") returns Pass when B2 is at least 50.

Error values provide clues. #DIV/0! occurs when a formula divides by zero or an empty divisor. #NAME? often indicates unrecognized text, such as a misspelled function. #### may mean the column is too narrow to display a number or date; widening the column is a sensible first check.

Managing and presenting data

Sorting rearranges records; filtering temporarily displays records that meet criteria. Always select the complete related dataset so rows remain intact. Excel Tables add structured formatting and filter controls. Data Validation can restrict entries or create a list of allowed values. Conditional Formatting changes appearance when rules are met; it does not normally change the stored value.

Charts visualize patterns. Column charts compare categories; line charts are often useful for trends over time. Freeze Panes keeps chosen rows or columns visible while scrolling. Printing may require setting orientation, scaling, print area and repeating header rows. Ctrl+S saves, Ctrl+C copies, Ctrl+V pastes, and F2 edits the active cell in desktop Excel for Windows. The common workbook format is .xlsx; .csv stores tabular text and does not preserve multiple worksheets, formulas or workbook formatting.

3. What to remember for the test

Workbook contains worksheets; a cell is identified by column and row.
Relative references move; absolute references stay fixed.
COUNT counts numbers; COUNTA counts non-empty cells.
Sort rearranges; Filter displays matching records.
Use the correct chart, validation rule and print settings for the task.

Independent Practice Assessment — 25 MCQs

Attempt all 25 questions, then select Submit Test. Answers and explanations remain hidden until submission.

This original educational practice is prepared independently by Sindh City Portal. It is not an official, leaked, repeated or guaranteed NTS/Sindh High Court paper.

1. What is a workbook in Excel?

A. A single cell only B. A file containing one or more worksheets C. A printed chart only D. A Windows folder

2. Which address identifies the cell at column C and row 7?

A. 7C B. C:7 C. C7 D. RowC

3. Which entry is a valid Excel formula?

A. SUM(A1:A5) B. +TOTAL only C. A1:A5=SUM D. =SUM(A1:A5)

4. What is the result of =2+3*4?

A. 14 B. 20 C. 24 D. 11

5. Which reference remains completely fixed when copied?

A. A1 B. $A$1 C. $A1 D. A$1

6. In the mixed reference $B3, what remains fixed when copied?

A. Row 3 only B. Both row and column C. Column B only D. Neither part

7. Which function adds the values in B2 through B6?

A. =SUM(B2:B6) B. =ADD(B2:B6) C. =COUNT(B2:B6) D. =TOTALS(B2:B6)

8. Cells A1:A4 contain 6, 8, 10 and 16. What does =AVERAGE(A1:A4) return?

A. 8 B. 12 C. 40 D. 10

9. Which function returns the smallest numeric value in a range?

A. MAX B. COUNT C. MIN D. IF

10. A1:A5 contain 4, blank, "Absent", 7 and 0. What does =COUNT(A1:A5) return?

A. 5 B. 3 C. 4 D. 2

11. Using the same cells, what does =COUNTA(A1:A5) return?

A. 4 B. 3 C. 5 D. 2

12. Which formula counts values in C2:C20 that are at least 50?

A. =COUNT(C2:C20,">=50") B. =IFCOUNT(C2:C20,50) C. =SUMIF(C2:C20) D. =COUNTIF(C2:C20,">=50")

13. What does =IF(B2>=50,"Pass","Review") return when B2 is 47?

A. Pass B. Review C. 47 D. FALSE only

14. Which error commonly indicates division by zero?

A. #NAME? B. #VALUE? only C. #DIV/0! D. #REF! only

15. A cell displays ####. What should you check first for a positive number or date?

A. Whether the column is too narrow B. Whether the workbook has no sheets C. Whether Ctrl+C is broken D. Whether the printer is offline

16. What does sorting do?

A. Hides formulas permanently B. Deletes duplicates automatically in every case C. Creates a password D. Rearranges records according to selected keys

17. What does filtering do?

A. Converts all text to numbers B. Erases hidden rows C. Temporarily shows rows meeting criteria D. Changes every formula

18. Why should the full related dataset be included when sorting?

A. To make fonts larger B. To keep each record’s row values together C. To disable formulas D. To remove headings

19. Which feature can restrict a cell to values from an approved list?

A. Data Validation B. Freeze Panes C. Page Break Preview D. Find

20. What does Conditional Formatting normally change?

A. The stored value in every case B. The workbook password C. The operating system D. The displayed formatting when a rule is met

21. Which chart is generally suitable for showing a trend across months?

A. Pie chart with 50 categories B. Random shape C. Line chart D. Text box

22. What is Freeze Panes used for?

A. Prevent all editing B. Keep selected rows or columns visible while scrolling C. Encrypt formulas D. Merge workbooks automatically

23. What happens when =A1+$D$1 is copied one row down?

A. It becomes =A2+$D$1 B. It stays =A1+$D$1 C. =A2+$D$2 D. =B1+$D$1

24. What does a .csv file normally fail to preserve from an Excel workbook?

A. Plain tabular values B. Comma-separated rows C. Text data D. Multiple worksheets and workbook formatting

25. A1=12, A2=18 and A3=30. What does =MAX(A1:A3)-MIN(A1:A3) return?

A. 12 B. 30 C. 18 D. 42

Open the answer key and explanations

1. B — A file containing one or more worksheets. An Excel workbook is the file that can contain multiple worksheets.
Explanation in Urdu: Excel workbook aik file hoti hai jis mein aik ya zyada worksheets ho sakti hain.
2. C — C7. Excel cell addresses place the column letter before the row number.
Explanation in Urdu: Excel cell address mein pehle column letter aur phir row number aata hai, is liye C7.
3. D — =SUM(A1:A5). Excel formulas begin with an equals sign; SUM then adds the range.
Explanation in Urdu: Excel formula equals sign se shuru hota hai, is liye =SUM(A1:A5) durust hai.
4. A — 14. Multiplication occurs before addition: 3×4 = 12, then 2+12 = 14.
Explanation in Urdu: Excel pehle multiplication karta hai: 3×4=12, phir 2 jama karne se 14.
5. B — $A$1. $A$1 fixes both the column and the row.
Explanation in Urdu: $A$1 mein column aur row dono fixed rehte hain.
6. C — Column B only. The dollar sign before B fixes the column, while the row remains relative.
Explanation in Urdu: $B3 mein B column fixed hai, lekin row copy karne par badal sakti hai.
7. A — =SUM(B2:B6). SUM is the standard function for adding a range.
Explanation in Urdu: B2 se B6 tak values jorne ke liye SUM function use hota hai.
8. D — 10. The sum is 40 and 40÷4 = 10.
Explanation in Urdu: Values ka total 40 hai; 4 se divide karne par average 10 banta hai.
9. C — MIN. MIN returns the minimum numeric value.
Explanation in Urdu: MIN range ki sab se chhoti numeric value deta hai.
10. B — 3. COUNT counts numeric cells: 4, 7 and 0, giving 3.
Explanation in Urdu: COUNT sirf numeric cells ginta hai; 4, 7 aur 0 mil kar 3 cells hain.
11. A — 4. COUNTA counts all non-empty cells: 4, Absent, 7 and 0, giving 4.
Explanation in Urdu: COUNTA har non-empty cell ginta hai; is range mein 4 cells bhare hue hain.
12. D — =COUNTIF(C2:C20,">=50"). COUNTIF applies one criterion to a range.
Explanation in Urdu: COUNTIF range mein aik condition poori karne wali cells ko ginta hai.
13. B — Review. The test is false, so IF returns the third argument, Review.
Explanation in Urdu: 47, 50 se kam hai, is liye IF false wala result Review deta hai.
14. C — #DIV/0!. #DIV/0! appears when a formula divides by zero or an empty divisor.
Explanation in Urdu: #DIV/0! tab aata hai jab formula zero ya khali divisor se divide kare.
15. A — Whether the column is too narrow. A narrow column commonly causes #### when the formatted value cannot fit.
Explanation in Urdu: Positive number ya date ke liye pehle column ki width check karein; kam jagah par #### dikh sakta hai.
16. D — Rearranges records according to selected keys. Sorting changes the order of records based on selected columns and direction.
Explanation in Urdu: Sorting selected column ke mutabiq records ka order badalti hai.
17. C — Temporarily shows rows meeting criteria. Filtering displays matching records while temporarily hiding others.
Explanation in Urdu: Filtering condition poori karne wali rows dikhati aur baqi ko temporary chhupati hai.
18. B — To keep each record’s row values together. Sorting only one column can separate values from the rest of their records.
Explanation in Urdu: Poora dataset select karne se har record ki row ka data saath rehta hai.
19. A — Data Validation. Data Validation can create a permitted-entry list.
Explanation in Urdu: Data Validation cell mein sirf approved list ki values allow kar sakti hai.
20. D — The displayed formatting when a rule is met. Conditional Formatting changes visual formatting based on rules, not the underlying value.
Explanation in Urdu: Conditional Formatting rule ke mutabiq cell ka look badalti hai, asal value nahin.
21. C — Line chart. A line chart clearly shows change across ordered time periods.
Explanation in Urdu: Mahinon ke trend ko line chart saaf dikhata hai.
22. B — Keep selected rows or columns visible while scrolling. Freeze Panes keeps chosen headers or identifiers visible during scrolling.
Explanation in Urdu: Freeze Panes scrolling ke waqt selected rows ya columns ko nazar mein rakhta hai.
23. A — It becomes =A2+$D$1. The relative A1 becomes A2, while absolute $D$1 remains fixed.
Explanation in Urdu: Copy down par A1 relative hone ki wajah se A2 banega, jabke $D$1 fixed rahega.
24. D — Multiple worksheets and workbook formatting. CSV is plain tabular text and does not preserve multiple sheets, formulas or workbook formatting.
Explanation in Urdu: CSV simple tabular text hoti hai; multiple sheets aur workbook formatting preserve nahin hoti.
25. C — 18. MAX is 30 and MIN is 12; 30−12 = 18.
Explanation in Urdu: Maximum 30 aur minimum 12 hai; farq 18 banta hai.

Sources and verification

Official Sindh High Court/NTS paper pattern and syllabus — used only for the stated computer/MS Office scope.
Microsoft: Overview of formulas in Excel
Microsoft: COUNTIF function
Microsoft: Filter data in a range or table

Separation: Official syllabus facts come only from the linked syllabus. The lesson structure, topic selection, wording and all 25 MCQs are original independent preparation material.

Independent preparation disclaimer

Sindh City Portal is not affiliated with, endorsed by or operated by the Sindh High Court or National Testing Service. Candidates must rely on official sources and roll-number-slip instructions for final recruitment, schedule and examination information.

Continue your preparation

Return to the Sindh High Court Preparation Hub

Part 1: Computer Fundamentals Previous: Microsoft Word Part 2 Next: PowerPoint & Office Revision Part 4

Sindh City Portal

Microsoft Excel – Formulas & 25 MCQs