Excel Quiz for Intermediates
Excel Test Online for Intermediate Students
This quiz is designed to test your knowledge of fundamental Excel topics including tools, data management, formatting, functions, formulas, pivot tables, and charts. Assessing your understanding of these core subjects will help you gauge your proficiency in Excel and identify areas for improvement. Taking this test can provide valuable insights into your Excel skills, highlighting strengths and uncovering potential areas where additional learning or practice may be beneficial. Evaluating your knowledge in these core subjects may help you gain a clearer understanding of your Excel proficiency.
For real Excel job interview tests see here.
1) Which feature in Excel is used for finding and correcting errors in formulas?
- A) AutoSum
- B) Trace Precedents
- C) Goal Seek
- D) Data Validation
2) What does the “Format as Table” option in Excel allow you to do?
- A) Convert text to numbers
- B) Apply a predefined table style to a range of cells
- C) Create a pivot table
- D) Hide selected columns
3) Which function would you use to round a number to the nearest integer?
- A) ROUNDUP
- B) ROUNDDOWN
- C) ROUND
- D) CEILING
4) What does the VLOOKUP function do in Excel?
- A) Finds the highest value in a range
- B) Searches for a value in the first column of a table and returns a value in the same row from another column
- C) Counts the number of cells that are not empty in a range
- D) Returns the average of selected database entries
5) What is the primary purpose of a pivot table in Excel?
- A) To create visual representations of data
- B) To summarize and analyze data in a spreadsheet
- C) To calculate the average of a range of cells
- D) To import data from external sources
6) Which chart type would you use to show the relationship of parts to a whole?
- A) Line Chart
- B) Bar Chart
- C) Pie Chart
- D) Scatter Plot
7) Which option in Excel allows you to apply the same formatting to multiple cells at once?
- A) Format Painter
- B) Conditional Formatting
- C) Cell Styles
- D) Merge & Center
8) What does the function =NOW() do in Excel?
- A) Returns the current date and time
- B) Calculates the net present value
- C) Rounds a number to the nearest integer
- D) Returns the value of pi (π)
9) How would you freeze the top row and the first column in Excel?
- A) By using the “Split” option in the View tab
- B) Through the use of conditional formatting
- C) By locking the cells in the top row and the first column
- D) It is not possible to freeze both the top row and the first column
10) What is the keyboard shortcut to insert a new worksheet in an Excel workbook?
- A) Ctrl + N
- B) Ctrl + T
- C) Shift + F11 *
- D) Alt + F11
11) What is the difference between a relative reference and an absolute reference in Excel?
- A) A relative reference changes when a formula is copied to another cell, while an absolute reference remains the same.
- B) A relative reference remains the same when a formula is copied to another cell, while an absolute reference changes.
- C) A relative reference refers to a range of cells, while an absolute reference refers to a single cell.
- D) A relative reference refers to a single cell, while an absolute reference refers to a range of cells.
12) What is the goal of the IFERROR() function in Excel?
- A) It returns a custom value if an error occurs in a formula, or the formula result otherwise.
- B) It returns a TRUE or FALSE value if an error occurs in a formula, or the formula result otherwise.
- C) It returns a blank cell if an error occurs in a formula, or the formula result otherwise.
- D) It returns an error message if an error occurs in a formula, or the formula result otherwise
13) What is the purpose of the $ sign in an Excel formula?
- A) It indicates a currency value.
- B) It indicates a percentage value.
- C) It indicates an absolute reference.
- D) It indicates a text value.
14) How can you apply a filter to a table in Excel?
- A) Select the table and click the Filter button on the Data tab.
- B) Select the table and click the Sort & Filter button on the Home tab.
- C) Select the table and press Ctrl + F.
- D) Select the table and press Ctrl + Shift + L
15) What does the SUMIF() function do?
- A) Adds up all values in a range.
- B) Sums values based on a specified condition.
- C) Counts the number of cells meeting a condition.
- D) Calculates the average of a range.
16) Which function counts the number of cells meeting a specific condition?
- A) COUNTIF
- B) COUNT
- C) SUMIF
- D) IFNA
17) What does the COUNT() function do?
- A) Adds up all values in a range.
- B) Counts the total number of cells in a range.
- C) Calculates the average of a range.
- D) Sums values based on a condition.
18) Which function returns the value if it exists, otherwise a specified alternative?
- A) IFNA
- B) INDEX
- C) COUNT
- D) SUMIF
19) What does the INDEX() function do?
- A) Returns the row number of a value.
- B) Retrieves a value from a specific cell.
- C) Calculates the average of a range.
- D) Sums values based on a condition.
20) Which function is used to concatenate strings in Excel?
- A) SUMIF
- B) COUNTIF
- C) CONCATENATE
- D) IFNA
21) What is the result of the formula =SUMIF(A1:A10, ">50", B1:B10)?
- A) Adds up all values in column B.
- B) Sums values in column B where corresponding value in column A is greater than 50.
- C) Counts the number of cells in column B meeting the condition.
- D) Calculates the average of values in column B.
22) Which function is case-sensitive?
- A) SUMIF
- B) COUNTIF
- C) IFNA
- D) INDEX
23) What does the AVERAGEIF() function do?
- A) Calculates the average of a range based on a condition.
- B) Adds up all values in a range.
- C) Retrieves a value from a specific cell.
- D) Counts the total number of cells in a range.
24) Which function allows you to use multiple criteria?
- A) SUMIF
- B) COUNTIF
- C) SUMIFS
- D) IFNA
SOLUTIONS
1 – B
2 -B
3 – C
4 – B
5 – B
6 – C
7 – A
8 – A
9 – A. A relative reference, such as A1, adjusts based on the relative position of the cell that contains the formula. An absolute reference, such as $A$1, does not change when the formula is copied or moved to another cell.
10 – To insert a new worksheet, press Shift + F11. Ctrl + N creates a new workbook, Ctrl + T creates a table, and Alt + F11 opens the Visual Basic Editor.
11 – A
12 – A. The IFERROR function has the syntax =IFERROR(value, value_if_error), where value is the expression to be evaluated, and value_if_error is the value to return if an error occurs. For example, =IFERROR(A1/B1, “Invalid”) returns “Invalid” if A1 or B1 is zero or blank, or the quotient of A1 and B1 otherwise.
13 – C. The $ sign is used to create an absolute reference, which does not change when a formula is copied or moved to another cell. For example, $A$1 refers to cell A1, regardless of where the formula is located.
14 – A. To apply a filter to a table, select the table and click the Filter button on the Data tab. This will add drop-down arrows to the header row, which can be used to filter the data by various criteria. Alternatively, you can press Ctrl + Shift + L to toggle the filter on or off.
15 – B
16 – A
17 – B
18 – A
19 – B
20 – C
21 – B
22 – B
23 – A
24 – C
