GK Quiz on Microsoft Excel: Test Your Excel Smarts and Know if You are a Spreadsheet Mastermind?

Calling all spreadsheet enthusiasts! Are you a whiz with formulas, a master of formatting, and a champion of data organization? Put your Microsoft Excel skills to the test with this exciting GK quiz. From basic functions to advanced techniques, this quiz covers a range of Excel knowledge to challenge your understanding and refresh your memory.

1. What function do you use to calculate the average of a range of numbers in Excel?

a) SUM

b) AVERAGE

c) COUNT

d) MIN

Answer: b)

Explanation: In Excel, the function you use to calculate the average of a range of numbers is AVERAGE. The Microsoft Support page mentions: “Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.”

2. What does the symbol "$" before a cell reference do in a formula?

a) Makes the cell reference absolute.

b) Increases the value in the cell.

c) Formats the cell as currency.

d) Hides the cell content.

Answer: a)

Explanation: When you precede a cell reference with a dollar sign, it transforms it into an absolute reference. This means that the cell reference won't change when the formula is copied or filled to other cells.

3. What keyboard shortcut do you use to copy a cell?

a) Ctrl + V

b) Ctrl + X

c) Ctrl + C

d) Ctrl + P

Answer: c) 

Explanation: The most common keyboard shortcut to copy a cell in Excel depends on your operating system. However, Ctrl + C is the most widely used shortcut to copy the content of a cell in Excel on Windows.

4. How do you sort data in ascending order in Excel?

a) Click on the header of the column you want to sort and choose "Sort A to Z".

b) Right-click on any cell in the column and select "Sort & Filter".

c) Use the "Data" tab on the ribbon and select "Sort".

4. d) All of the above.

Answer: d) 

Explanation: All of the above methods (a, b, and c) are valid ways to sort data in ascending order in Excel. 

5. What is the difference between a relative and an absolute cell reference?

a) A relative reference changes when copied, while an absolute reference remains the same.

b) An absolute reference is used for calculations, while a relative reference is used for formatting.

c) There is no difference; they are the same thing.

d) Both a and b are correct.

Answer: a)

Explanation: Relative references: These references are based on the relative position of the cell containing the reference and the cell being referenced. When you copy a cell containing a relative reference, the reference automatically adjusts to maintain the same relative position. On the other hand, Absolute references use dollar signs ($) to lock the row and/or column of the referenced cell. When you copy a cell containing an absolute reference, the reference remains the same, regardless of the new location.

6. What function do you use to count the number of cells in a range that contain text?

a) COUNTA

b) COUNT

c) COUNTIF

d) COUNTIFS

Answer: c) 

Explanation: According to Microsoft Support, “COUNTIF Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character.”

7. What is the purpose of conditional formatting in Excel?

a) To format cells based on certain criteria.

b) To add borders and shading to cells.

c) To create charts and graphs.

d) To merge and centre cell contents.

Answer: a)

Explanation: While conditional formatting can indeed be used to add borders and shading to cells, that's not its primary purpose. It goes beyond simple aesthetics and allows you to highlight specific data patterns so that you can set rules to automatically change the appearance of cells based on their values, exceeding certain thresholds, containing specific text, or meeting other conditions. This visual highlighting helps you quickly identify important trends and outliers in your data.

8. What is the maximum number of rows and columns in an Excel spreadsheet?

a) Unlimited

b) 1,000,000 rows and 10,000 columns

c) 65,536 rows and 256 columns

d) 1,048,576 rows and 16,384 columns

Answer: d) 

Explanation: The maximum number of rows and columns in an Excel spreadsheet depends on the specific version of Excel you are using: 

For Excel 2007 and later versions:

• Maximum rows: 1,048,576
• Maximum columns: 16,384

For Excel 2003 and earlier versions:

• Maximum rows: 65,536
• Maximum columns: 256

9. What is a macro in Excel?

a) A pre-recorded series of actions

b) A special type of formula

c) A formatting option

d) A data validation tool

Answer: a) 

Explanation: A macro in Excel is a recorded series of actions or instructions that you can play back to automate repetitive tasks. Think of it like a mini-program within Excel that you create to do specific things for you.

10. What is the VLOOKUP function used for?

a) Creating charts and graphs.

b) Calculating the sum of values in a range.

c) Looking up values in a table based on a specific key.

d) Formatting cells and ranges.

Answer: 

Explanation: Looking up values in a table based on a specific key is the core functionality of VLOOKUP. You provide a key value (e.g., product ID), specify the table where the key is located, and then tell VLOOKUP which column within that table contains the value you want to return (e.g., product price). It's a powerful tool for retrieving specific data points from large datasets based on known reference points.

READ| GK Quiz on Fintech: Can You Decode the Future of Finance?