Excel Quiz - MCQ Questions and Answers

In this quiz, we present 50 multiple-choice questions (MCQs) related to Microsoft Excel, complete with answers and explanations. Go ahead and test your Excel knowledge with this quiz.

1. What is Microsoft Excel primarily used for?

a) Word processing

b) Database management

c) Spreadsheet calculations and data analysis

d) Creating presentations

Answer:

c) Spreadsheet calculations and data analysis

Explanation:

Microsoft Excel is a spreadsheet application used primarily for calculations, data analysis, graphing tools, pivot tables, and a macro programming language.

2. What symbol is used to start a formula in Excel?

a) #

b) @

c) =

d) $

Answer:

c) =

Explanation:

In Excel, a formula always starts with an equal sign (=), which signals Excel that what follows is a formula.

3. How can you sum the values in cells A1 through A10 in Excel?

a) =SUM(A1:A10)

b) =TOTAL(A1:A10)

c) =ADD(A1:A10)

d) =SUMMARY(A1:A10)

Answer:

a) =SUM(A1:A10)

Explanation:

The SUM function in Excel adds all the numbers in a range of cells. The correct syntax to sum values from A1 through A10 is =SUM(A1:A10).

4. What is a Pivot Table used for in Excel?

a) Sorting data

b) Organizing and summarizing large amounts of data

c) Merging two tables

d) Changing the font style of a table

Answer:

b) Organizing and summarizing large amounts of data

Explanation:

A Pivot Table is a powerful tool in Excel that allows you to organize, summarize, and analyze large amounts of data in lists and tables without altering the original data.

5. What does the '$' symbol signify in an Excel formula?

a) It converts a number into a dollar value

b) It signifies an absolute cell reference

c) It indicates a variable

d) It represents a constant value

Answer:

b) It signifies an absolute cell reference

Explanation:

In Excel, the '$' symbol is used to lock a specific cell or range of cells in a formula, known as an absolute reference. It prevents the cell reference from changing when copying or filling the formula to other cells.

6. What is Conditional Formatting in Excel?

a) A way to format text in a cell

b) Formatting a cell based on certain conditions or criteria

c) Changing a cell's format when it is selected

d) Formatting that automatically adjusts when the workbook is updated

Answer:

b) Formatting a cell based on certain conditions or criteria

Explanation:

Conditional Formatting in Excel allows you to apply formats to a cell or range of cells based on certain criteria or conditions. This can include changes in color, font, and other visual styles based on the data in the cell.

7. What function would you use to find the largest number in a range of cells?

a) MAX

b) BIGGEST

c) HIGHEST

d) LARGEST

Answer:

a) MAX

Explanation:

The MAX function in Excel is used to find the largest number in a range of cells.

8. What is a 'Workbook' in Excel?

a) A single worksheet

b) A collection of charts

c) A template file

d) An Excel file containing one or more worksheets

Answer:

d) An Excel file containing one or more worksheets

Explanation:

In Excel, a workbook is a file that contains one or more worksheets (also known as spreadsheets) to help organize data.

9. What is the keyboard shortcut to save a workbook in Excel?

a) Ctrl + S

b) Ctrl + N

c) Ctrl + P

d) Ctrl + O

Answer:

a) Ctrl + S

Explanation:

Ctrl + S is the universal keyboard shortcut for saving a file, including in Excel.

10. How do you insert a new row in Excel?

a) Right-click on an existing row and select 'Insert Row'

b) Use the shortcut Ctrl + R

c) Select the 'New Row' option from the Home tab

d) Drag the bottom edge of an existing row downwards

Answer:

a) Right-click on an existing row and select 'Insert Row'

Explanation:

To insert a new row in Excel, you can right-click on an existing row's number and select 'Insert'. This will insert a new row above the selected row.

11. Which Excel feature automatically fills in values when you drag the handle?

a) AutoSum

b) AutoFill

c) Quick Fill

d) Drag Fill

Answer:

b) AutoFill

Explanation:

The AutoFill feature in Excel allows you to quickly fill cells with repetitive or sequential data by dragging the fill handle (a small square at the bottom-right corner of a selected cell).

12. What type of chart is best for comparing parts of a whole?

a) Line chart

b) Bar chart

c) Pie chart

d) Scatter chart

Answer:

c) Pie chart

Explanation:

A pie chart is the best choice for comparing parts of a whole, as it visually represents the proportions or percentages of categories in a total.

13. What is a 'range' in Excel?

a) A set of rows and columns

b) A single cell

c) A collection of contiguous cells

d) The maximum value in a set of data

Answer:

c) A collection of contiguous cells

Explanation:

In Excel, a range is a collection or block of contiguous cells that you can select, manipulate, or format together.

14. How do you freeze panes in Excel?

a) Right-click on a cell and select 'Freeze Panes'

b) Go to the View tab and click 'Freeze Panes'

c) Double-click the cell where you want to freeze the panes

d) Use the shortcut Ctrl + F

Answer:

b) Go to the View tab and click 'Freeze Panes'

Explanation:

To freeze panes in Excel, you go to the View tab and select 'Freeze Panes'. This feature allows you to keep specific rows or columns visible when scrolling through the sheet.

15. What is VLOOKUP in Excel?

a) A function to look up and retrieve data in a table

b) A function to validate data

c) A feature to look for errors in formulas

d) A tool to visualize data lookup trends

Answer:

a) A function to look up and retrieve data in a table

Explanation:

VLOOKUP is a function in Excel that stands for 'Vertical Lookup'. It is used to search for a specified value in the first column of a table and returns a value in the same row from a specified column.

16. How can you protect a worksheet in Excel?

a) By setting a password

b) By hiding the worksheet

c) By converting it to a PDF

d) By deleting unnecessary data

Answer:

a) By setting a password

Explanation:

To protect a worksheet in Excel, you can set a password that prevents other users from making changes to the worksheet's structure or its contents.

17. What does the IF function do in Excel?

a) Checks whether a condition is met and returns one value if true and another value if false

b) Increases the font size of text

c) Inserts a new column

d) Filters data based on a criterion

Answer:

a) Checks whether a condition is met and returns one value if true and another value if false

Explanation:

The IF function in Excel is used to check whether a condition is met, and then returns one value if true and another value if false.

18. What does the 'Merge & Center' feature do in Excel?

a) Combines multiple cells into one and centers the content

b) Merges data from different sheets

c) Centers text in the selected cells

d) Merges duplicate data

Answer:

a) Combines multiple cells into one and centers the content

Explanation:

The 'Merge & Center' feature in Excel allows you to combine multiple adjacent cells into a single larger cell and center the content within the merged cell.

19. What type of chart would be best to display data over time?

a) Pie chart

b) Line chart

c) Column chart

d) Area chart

Answer:

b) Line chart

Explanation:

A line chart is most appropriate for displaying data changes over time. It shows trends and progressions at even intervals.

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

a) MEAN

b) AVERAGE

c) MEDIAN

d) SUM

Answer:

b) AVERAGE

Explanation:

The AVERAGE function in Excel calculates the average or arithmetic mean of a set of numbers.

21. What is the keyboard shortcut to open a new Excel workbook?

a) Ctrl + N

b) Ctrl + O

c) Ctrl + S

d) Ctrl + W

Answer:

a) Ctrl + N

Explanation:

Ctrl + N is the universal keyboard shortcut for opening a new document or file, including a new Excel workbook.

22. How do you add a comment to a cell in Excel?

a) Right-click the cell and select 'Add Comment'

b) Double-click the cell and start typing

c) Use the shortcut Ctrl + C

d) Go to the Insert tab and select 'Comment'

Answer:

a) Right-click the cell and select 'Add Comment'

Explanation:

To add a comment to a cell in Excel, you can right-click the cell and select 'Add Comment' from the context menu.

23. What is 'Data Validation' used for in Excel?

a) To correct wrong data

b) To validate formulas

c) To control the type of data or the values that users enter into a cell

d) To validate a user's identity

Answer:

c) To control the type of data or the values that users enter into a cell

Explanation:

Data Validation in Excel is a feature that allows you to control the type of data or the values that users enter into a cell. It can be used to create drop-down lists, restrict number ranges, or enforce specific formatting rules.

24. What does 'Concatenate' mean in Excel?

a) To separate text in a cell

b) To add numbers in different cells

c) To join two or more text strings into one string

d) To divide a cell into multiple cells

Answer:

c) To join two or more text strings into one string

Explanation:

In Excel, 'Concatenate' refers to the process of joining two or more text strings into one string. The CONCATENATE function or the '&' operator can be used for this purpose.

25. How can you hide a column in Excel?

a) By deleting the column

b) By changing the font color to white

c) By right-clicking the column header and selecting 'Hide'

d) By setting the column width to zero

Answer:

c) By right-clicking the column header and selecting 'Hide'

Explanation:

To hide a column in Excel, you can right-click on the column header and select 'Hide' from the context menu. This will make the column invisible, but it can be unhidden later if needed.

26. What is the use of the 'Sort' feature in Excel?

a) To add data to cells

b) To arrange data in a specific order

c) To change data format

d) To merge cells

Answer:

b) To arrange data in a specific order

Explanation:

The 'Sort' feature in Excel is used to arrange data in alphabetical, numerical, or custom order. It is a fundamental tool for organizing and analyzing data in a spreadsheet.

27. What is the maximum number of rows in Excel 2016 and later versions?

a) 65,536

b) 1,048,576

c) 100,000

d) 500,000

Answer:

b) 1,048,576

Explanation:

In Excel 2016 and later versions, the maximum number of rows in a worksheet is 1,048,576.

28. What is a 'Header Row' in Excel?

a) The first row of a worksheet

b) The top row of a table that contains column titles

c) A row that repeats on every printed page

d) The row at the bottom of the spreadsheet

Answer:

b) The top row of a table that contains column titles

Explanation:

In Excel, a 'Header Row' is the top row of a table or range that contains the column titles or labels. It is used to identify the type of data each column represents.

29. What is 'Flash Fill' in Excel?

a) A tool to fill cells with a flash color

b) A feature that automatically fills in data based on a pattern

c) A method to quickly delete cell contents

d) A function to highlight cells

Answer:

b) A feature that automatically fills in data based on a pattern

Explanation:

Flash Fill in Excel automatically fills your data when it senses a pattern. For example, if you are entering a list of dates or a series of numbers, Excel will fill in the remaining cells for you.

30. How can you split the text in a cell into multiple columns?

a) Using the 'Text to Columns' feature

b) Using the 'Split Cells' feature

c) Using the 'Divide Text' function

d) Using the 'Break Text' command

Answer:

a) Using the 'Text to Columns' feature

Explanation:

The 'Text to Columns' feature in Excel allows you to split the text in a single cell into multiple columns based on a delimiter such as a comma, space, or custom character.

31. What is a 'Named Range' in Excel?

a) A range of cells with a specific name

b) A function name

c) A title for a chart

d) A name given to a formula

Answer:

a) A range of cells with a specific name

Explanation:

A 'Named Range' in Excel refers to a range of cells that has been given a descriptive name, making it easier to identify or refer to those cells in formulas and functions.

32. What is 'Goal Seek' used for in Excel?

a) To find a specific goal in a dataset

b) To achieve a specific result in one cell by adjusting the value of another cell

c) To seek out errors in data

d) To search for a specific value in a column

Answer:

b) To achieve a specific result in one cell by adjusting the value of another cell

Explanation:

'Goal Seek' is a built-in Excel tool that is used for what-if analysis. It finds the input value needed in one cell to achieve a desired result in another cell.

33. How do you create a drop-down list in a cell in Excel?

a) Use the 'Drop-Down' function

b) Use the 'Data Validation' feature

c) Type in each item separated by a comma

d) Use the 'List Box' tool

Answer:

b) Use the 'Data Validation' feature

Explanation:

To create a drop-down list in Excel, you use the 'Data Validation' feature. It allows you to create a list of valid entries that are displayed in a drop-down menu for easy selection in a cell.

34. What is a 'Macro' in Excel?

a) A large worksheet

b) A function for complex calculations

c) A set of pre-recorded commands to automate repetitive tasks

d) An enhanced chart type

Answer:

c) A set of pre-recorded commands to automate repetitive tasks

Explanation:

A macro in Excel is a set of programming instructions you can record and associate with a shortcut or button. Macros are used to automate repetitive tasks.

35. What is 'Freeze Panes' in Excel?

a) A function to lock the value of cells

b) A feature to keep rows and/or columns visible while scrolling through a worksheet

c) A method to cool down overheated formulas

d) A tool to freeze cell formatting

Answer:

b) A feature to keep rows and/or columns visible while scrolling through a worksheet

Explanation:

The 'Freeze Panes' feature in Excel allows you to keep specific rows or columns visible when you scroll through a large worksheet.

36. How can you quickly remove duplicates in Excel?

a) Use the 'Remove Duplicates' feature

b) Manually delete duplicate rows

c) Use the 'Find and Replace' tool

d) Sort the data and delete duplicates

Answer:

a) Use the 'Remove Duplicates' feature

Explanation:

Excel has a built-in 'Remove Duplicates' feature that allows you to quickly identify and remove duplicate rows in a table or range.

37. What does the 'COUNTIF' function do in Excel?

a) Counts the number of cells that meet a specified criterion

b) Adds all the cells that meet a certain condition

c) Identifies if there are any cells that meet a specific criterion

d) Divides cells based on a condition

Answer:

a) Counts the number of cells that meet a specified criterion

Explanation:

The COUNTIF function in Excel is used to count the number of cells in a range that meet a single criterion, such as numbers greater than a certain value or cells that contain a specific text.

38. What is the keyboard shortcut to insert a new worksheet in Excel?

a) Ctrl + N

b) Ctrl + T

c) Shift + F11

d) Alt + N

Answer:

c) Shift + F11

Explanation:

The keyboard shortcut to insert a new worksheet in Excel is Shift + F11. This adds a new worksheet to your workbook.

39. How do you create a 3-D reference in Excel?

a) By linking three worksheets

b) By using a formula that refers to the same cell or range on multiple worksheets

c) By creating a 3-D chart

d) By using a special 3-D formula

Answer:

b) By using a formula that refers to the same cell or range on multiple worksheets

Explanation:

A 3-D reference in Excel is a reference to the same cell or range on multiple worksheets in the same workbook. It is used to consolidate data across several worksheets.

40. What does 'Filtering' data do in Excel?

a) Changes the data formatting

b) Deletes unwanted data

c) Temporarily hides rows or columns that do not meet the specified criteria

d) Permanently removes data that doesn't meet a criterion

Answer:

c) Temporarily hides rows or columns that do not meet the specified criteria

Explanation:

Filtering data in Excel allows you to temporarily hide rows or columns based on certain criteria, making it easier to focus on specific segments of your data.

41. What is a '3D Formula' in Excel?

a) A formula that creates a three-dimensional chart

b) A formula that references the same cell or range in multiple worksheets

c) A formula used exclusively for financial modeling

d) A formula that calculates volume

Answer:

b) A formula that references the same cell or range in multiple worksheets

Explanation:

A 3D Formula in Excel references the same cell or range in multiple worksheets within a workbook. It's useful for summarizing the same data that appears on multiple worksheets.

42. What is the function of 'Find & Select' in Excel?

a) To find a specific function or formula

b) To select a specific range of cells

c) To find and highlight specific values or data formats

d) To search for and install new Excel add-ins

Answer:

c) To find and highlight specific values or data formats

Explanation:

The 'Find & Select' tool in Excel is used to quickly find and select specific values, formulas, comments, or data formats in a spreadsheet.

43. What does the 'IFERROR' function do in Excel?

a) Checks for errors and returns a specified value if an error is found

b) Identifies the type of error in a cell

c) Fixes errors in formulas

d) Generates an error report

Answer:

a) Checks for errors and returns a specified value if an error is found

Explanation:

The IFERROR function in Excel is used to catch errors in a formula and replace them with a specified value. It's useful for avoiding error messages like #DIV/0! and #VALUE! in a cell.

44. How can you quickly calculate the total of a range in Excel?

a) Use the SUM function

b) Use the AutoSum feature

c) Manually add each cell

d) Use the TOTAL function

Answer:

b) Use the AutoSum feature

Explanation:

The AutoSum feature in Excel quickly adds up numbers in a range. It automatically enters the SUM function for you.

45. What is a 'Sparkline' in Excel?

a) A type of data validation rule

b) A miniature chart in a worksheet cell

c) A tool to check for spelling errors

d) A feature to enhance cell colors

Answer:

b) A miniature chart in a worksheet cell

Explanation:

Sparklines in Excel are tiny charts that fit in a single cell. They provide a visual representation of data and are used to show trends over time within a row or column.

46. What is 'Grouping' in Excel?

a) Combining multiple cells into one

b) Summarizing data from different sheets

c) Outlining rows or columns to make a section collapsible

d) Creating a group of charts

Answer:

c) Outlining rows or columns to make a section collapsible

Explanation:

Grouping in Excel allows you to outline rows or columns so that you can collapse or expand sections of your worksheet. It's useful for managing large amounts of data and focusing on specific areas of your spreadsheet.

47. What is 'Power Query' in Excel?

a) A feature to increase processing speed

b) A data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources

c) A tool for creating powerful macros

d) An advanced charting technique

Answer:

b) A data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources

Explanation:

Power Query in Excel is a data connection technology that allows users to connect, combine, and refine data from a wide variety of sources, streamlining the process of data gathering and preparation.

48. What does 'Slicer' refer to in Excel?

a) A tool for cutting data into smaller parts

b) A feature for creating dynamic charts

c) A control tool for filtering data in tables, pivot tables, or pivot charts

d) A formula for slicing arrays

Answer:

c) A control tool for filtering data in tables, pivot tables, or pivot charts

Explanation:

Slicers in Excel provide a user-friendly way to filter data in pivot tables, pivot charts, or Excel tables. They offer an intuitive interface that lets you quickly and easily filter your data.

49. How do you lock cells to protect them from changes in Excel?

a) Format the cells as 'Read Only'

b) Use the Protect Sheet feature

c) Save the workbook as 'Read Only'

d) Use the Lock Cell command

Answer:

b) Use the Protect Sheet feature

Explanation:

To lock cells and protect them from being changed, you can use the 'Protect Sheet' feature in Excel. This allows you to specify which cells can be edited and which cannot, providing a password for added security.

50. What is the purpose of the 'Quick Analysis' tool in Excel?

a) To analyze data errors

b) To quickly format, create charts, or add various types of analysis like totals

c) To analyze the speed of Excel functions

d) To perform quick data entry

Answer:

b) To quickly format, create charts, or add various types of analysis like totals

Explanation:

The Quick Analysis tool in Excel provides a fast and easy way to perform common tasks. It offers options for quickly formatting data, creating charts, and adding features like totals, tables, and sparklines.