100 Excel MCQ questions with answers
Introduction to Excel Interface & Basics
1. What is the maximum number of rows in an Excel worksheet?
o a) 65,536
o b) 1,048,576
o c) 10,000
o d) 500,000
o Answer: b
2. Which shortcut key is used to save an Excel file?
o a) Ctrl + S
o b) Ctrl + O
o c) Ctrl + P
o d) Ctrl + F
o Answer: a
3. What is the default file extension of Excel files in Excel 2019?
o a) .xls
o b) .xlsx
o c) .csv
o d) .docx
o Answer: b
4. Which tab in the Excel ribbon allows you to insert charts?
o a) Data
o b) Insert
o c) Formulas
o d) Review
o Answer: b
5. What is the shortcut key for opening a new workbook in Excel?
o a) Ctrl + N
o b) Ctrl + O
o c) Ctrl + W
o d) Ctrl + P
o Answer: a
Fundamentals of Excel: Data Entry, Insertion, Deletion
6. How can you insert a new worksheet in Excel?
o a) Right-click on a tab and select “Insert”
o b) Press Shift + Enter
o c) Use Alt + Tab
o d) Click the "+" icon next to worksheet tabs
o Answer: d
7. Which function key is used to edit the active cell?
o a) F1
o b) F2
o c) F5
o d) F7
o Answer: b
8. Which option is used to remove duplicate data in Excel?
o a) Data > Remove Duplicates
o b) Insert > Table
o c) File > Remove Duplicates
o d) Review > Check Duplicates
o Answer: a
Introduction to Excel Formulas
9. Which formula returns the smallest value in a range?
o a) =MAX(A1:A10)
o b) =MIN(A1:A10)
o c) =AVERAGE(A1:A10)
o d) =COUNT(A1:A10)
o Answer: b
10. What is the correct formula to count cells containing numbers in a range?
o a) =SUM()
o b) =COUNT()
o c) =COUNTA()
o d) =COUNTIF()
o Answer: b
11. What does the formula =SUMIF(A1:A10, ">50") do?
o a) Adds all numbers in A1:A10 greater than 50
o b) Counts all cells with values >50
o c) Averages all numbers in A1:A10 >50
o d) None of the above
o Answer: a
12. What is the result of =LEFT("Excel", 3)?
o a) Exc
o b) Exce
o c) El
o d) cel
o Answer: a
Data Cleaning & Transformation
13. Which option is used to remove extra spaces in text?
o a) =TRIM()
o b) =CLEAN()
o c) =LEFT()
o d) =MID()
o Answer: a
14. How can you handle missing data in Excel?
o a) Replace with average values
o b) Leave as blank
o c) Use the Go To Special tool
o d) All of the above
o Answer: d
15. Which tool is used to split data into multiple columns?
o a) Flash Fill
o b) Text to Columns
o c) Power Query
o d) Transpose
o Answer: b
Data Exploration, Sorting & Filtering
16. What is the shortcut for applying a filter to a table?
o a) Ctrl + Shift + L
o b) Ctrl + Alt + L
o c) Ctrl + L
o d) Alt + L
o Answer: a
17. Which formula finds and retrieves data based on a condition?
o a) =VLOOKUP()
o b) =HLOOKUP()
o c) =INDEX()
o d) All of the above
o Answer: d
Pivot Tables & Data Analysis
18. Which step comes first when creating a Pivot Table?
o a) Select the data range
o b) Insert > PivotTable
o c) Arrange rows and columns
o d) Apply filters
o Answer: a
19. What is the purpose of a slicer in Pivot Tables?
o a) To sort data
o b) To filter data
o c) To remove duplicates
o d) To highlight trends
o Answer: b
20. Which chart type is best for trend analysis in Excel?
o a) Line chart
o b) Pie chart
o c) Bar chart
o d) Scatter plot
o Answer: a
Power Query & Forecasting
21. What is Power Query used for in Excel?
o a) Transforming and cleaning data
o b) Analyzing trends
o c) Visualizing data
o d) Creating formulas
o Answer: a
22. Which menu is used to insert a forecast chart in Excel?
o a) Data > Forecast
o b) Insert > Line Chart
o c) View > Trendline
o d) Review > Prediction
o Answer: a
23. What does Conditional Formatting do?
o a) Highlights cells based on criteria
o b) Sorts data automatically
o c) Removes duplicate data
o d) Splits data into columns
o Answer: a
Basics of Data Privacy & Introduction to Data Cleaning
24. Which of the following is considered personal data?
o a) Phone number
o b) Email address
o c) National ID number
o d) All of the above
o Answer: d
25. Which method is used to handle inconsistent data in Excel?
o a) Remove duplicates
o b) Use find and replace
o c) Format cells
o d) Use Power Query
o Answer: b
26. What is a common method to clean up inconsistent date formats?
o a) Use the Text to Columns tool
o b) Use CONCATENATE function
o c) Format as General
o d) Use Flash Fill
o Answer: a
Removing Duplicate Data & Handling Missing Data
27. How can you identify and remove duplicate entries in Excel?
o a) Data > Remove Duplicates
o b) Data > Sort
o c) Home > Find & Select
o d) File > Remove Duplicates
o Answer: a
28. What action should you take when you encounter missing data in Excel?
o a) Ignore it
o b) Replace it with 0
o c) Replace with a placeholder like "N/A"
o d) All of the above
o Answer: d
29. Which Excel tool helps handle missing data and replaces it with another value?
o a) Flash Fill
o b) Find and Replace
o c) Conditional Formatting
o d) Data Validation
o Answer: b
Splitting and Merging Data
30. How do you split a single column into multiple columns in Excel?
o a) Use CONCATENATE
o b) Use Text to Columns
o c) Use Flash Fill
o d) Use Power Query
o Answer: b
31. What does the CONCATENATE function do?
o a) Joins multiple text values into one
o b) Splits a text string into multiple columns
o c) Creates a new table
o d) Filters text based on conditions
o Answer: a
32. Which of these methods can be used to merge data from multiple columns into
one?
o a) Flash Fill
o b) Text to Columns
o c) CONCATENATE function
o d) All of the above
o Answer: c
Data Transformation & Removing Unnecessary Characters
33. Which function is used to remove non-printing characters in Excel?
o a) =TRIM()
o b) =CLEAN()
o c) =SUBSTITUTE()
o d) =MID()
o Answer: b
34. How can you remove leading and trailing spaces from text?
o a) Use =CLEAN()
o b) Use =TRIM()
o c) Use =LEFT()
o d) Use =RIGHT()
o Answer: b
35. What does the SUBSTITUTE function do?
o a) Replaces all occurrences of a specified text string with another
o b) Removes extra spaces
o c) Converts text to uppercase
o d) Reverses text in a string
o Answer: a
Sorting and Filtering Data
36. How can you sort data in Excel in ascending order?
o a) Data > Sort A-Z
o b) Data > Sort Z-A
o c) Home > Sort
o d) Insert > Sort
o Answer: a
37. Which is the best method to filter rows based on specific conditions in Excel?
o a) Data > Filter
o b) Data > Sort
o c) Home > Find & Select
o d) Review > Check Spelling
o Answer: a
38. What does the AutoFilter feature do in Excel?
o a) Automatically formats data
o b) Filters data based on predefined criteria
o c) Sorts data alphabetically
o d) Generates Pivot tables
o Answer: b
VLOOKUP and HLOOKUP
39. What is the purpose of the VLOOKUP function in Excel?
o a) Look for a value in a row
o b) Look for a value in a column
o c) Look for a value in a table and return related data
o d) None of the above
o Answer: c
40. Which argument is required for the VLOOKUP function?
o a) Lookup value
o b) Table array
o c) Column index number
o d) All of the above
o Answer: d
41. What is the difference between VLOOKUP and HLOOKUP?
o a) VLOOKUP searches vertically, HLOOKUP searches horizontally
o b) VLOOKUP is for text, HLOOKUP is for numbers
o c) VLOOKUP returns the first value, HLOOKUP returns the last value
o d) No difference
o Answer: a
Introduction to Pivot Tables & Data Analysis
42. Which step is NOT needed when creating a Pivot Table in Excel?
o a) Select the data
o b) Choose the summary function (SUM, AVERAGE, etc.)
o c) Set up a slicer
o d) Format the Pivot Table manually
o Answer: d
43. What does the Values area in a Pivot Table represent?
o a) Row data
o b) Column data
o c) Summary data (e.g., sum, count)
o d) Filter data
o Answer: c
44. How do you change the calculation type in a Pivot Table?
o a) Right-click on the Values field and choose "Summarize Values By"
o b) Double-click on the Pivot Table
o c) Change the original data source
o d) Use the Insert tab
o Answer: a
Creating Dashboards & Data Visualization
45. Which of the following is best used to display data trends over time in Excel?
o a) Line chart
o b) Bar chart
o c) Pie chart
o d) Area chart
o Answer: a
46. What is the benefit of using a Dashboard in Excel?
o a) It helps in visualizing data for better decision-making
o b) It displays data in tables only
o c) It reduces data entry errors
o d) It formats data automatically
o Answer: a
47. What chart type would you use to compare categories across different groups?
o a) Bar chart
o b) Line chart
o c) Pie chart
o d) Scatter plot
o Answer: a
Power Query & Data Transformation
48. What is Power Query used for in Excel?
o a) Sorting data
o b) Data transformation and cleaning
o c) Creating formulas
o d) Visualizing data
o Answer: b
49. Which of the following actions can be done using Power Query?
o a) Import data from multiple sources
o b) Clean data by removing unwanted characters
o c) Split or merge columns
o d) All of the above
o Answer: d
50. What is the first step when using Power Query to import data from an external
file?
o a) Choose the "From File" option
o b) Select the "Transform Data" button
o c) Load the data into an existing table
o d) Open the file in Excel
o Answer: a
Conditional Formatting
51. What does conditional formatting do in Excel?
o a) Applies formatting based on specific rules or conditions
o b) Sorts and filters data
o c) Removes duplicate entries
o d) Merges and splits cells
o Answer: a
52. Which tab in the ribbon contains Conditional Formatting?
o a) Insert
o b) Home
o c) Data
o d) View
o Answer: b
53. Which of the following is NOT a type of conditional formatting rule?
o a) Data bars
o b) Color scales
o c) Icon sets
o d) Pivot formatting
o Answer: d
54. What happens when a cell meets the condition in conditional formatting?
o a) The font changes
o b) The cell background changes
o c) Both a and b
o d) The cell remains unchanged
o Answer: c
55. How can you highlight cells greater than 100 using conditional formatting?
o a) Home > Conditional Formatting > Highlight Cell Rules > Greater Than > 100
o b) Insert > Data > Highlight Greater Than > 100
o c) Data > Sort & Filter > Conditional Highlight > 100
o d) Home > Format > Highlight Greater Than 100
o Answer: a
Data Exploration and Summary Statistics
56. Which function is used to calculate the total number of entries in a column?
o a) =SUM()
o b) =COUNT()
o c) =COUNTA()
o d) =AVERAGE()
o Answer: b
57. What is the purpose of the AVERAGE function?
o a) Finds the middle value of a dataset
o b) Sums up the data
o c) Calculates the mean of numbers in a range
o d) Returns the maximum value
o Answer: c
58. Which tool is used to get summary statistics like sum, average, and count in Excel?
o a) Data Analysis ToolPak
o b) Pivot Table
o c) AutoSum
o d) All of the above
o Answer: d
59. What does the COUNTIF function do?
o a) Counts all values in a range
o b) Counts values based on a specific condition
o c) Counts non-empty cells
o d) None of the above
o Answer: b
60. How do you calculate the median in Excel?
o a) =MEDIAN(range)
o b) =MEAN(range)
o c) =AVERAGE(range)
o d) =MODE(range)
o Answer: a
Forecasting and Trend Analysis
61. Which type of chart is commonly used for trend analysis?
o a) Line Chart
o b) Bar Chart
o c) Pie Chart
o d) Area Chart
o Answer: a
62. What is the main purpose of trend analysis in Excel?
o a) To forecast future values
o b) To clean data
o c) To calculate totals
o d) To import external data
o Answer: a
63. Which feature is used for forecasting future trends in Excel?
o a) Forecast Sheet
o b) Conditional Formatting
o c) Data Validation
o d) Remove Duplicates
o Answer: a
64. How do you create a trendline in Excel?
o a) Select chart > Add Chart Element > Trendline
o b) Data > Forecast > Add Trendline
o c) Insert > Add Trendline
o d) View > Trendline
o Answer: a
65. What does a positive slope in a trendline indicate?
o a) A decreasing trend
o b) An increasing trend
o c) No trend
o d) Data inconsistency
o Answer: b
Creating Dashboards
66. What is the first step in creating a dashboard in Excel?
o a) Collect and organize raw data
o b) Insert charts
o c) Use slicers
o d) Add conditional formatting
o Answer: a
67. What tool is often used in dashboards to filter data dynamically?
o a) Slicers
o b) Data Validation
o c) Forecast Sheets
o d) Remove Duplicates
o Answer: a
68. Which chart type is NOT suitable for dashboards?
o a) Pie Chart
o b) Line Chart
o c) Stacked Bar Chart
o d) 3D Surface Chart
o Answer: d
69. What is the best practice for designing dashboards?
o a) Use minimal colors and avoid clutter
o b) Add as many charts as possible
o c) Use only Pivot Tables
o d) Avoid using slicers
o Answer: a
70. Which Excel tool is ideal for summarizing and visualizing data in a dashboard?
o a) Pivot Table
o b) Conditional Formatting
o c) Flash Fill
o d) Remove Duplicates
o Answer: a
Advanced Topics: Power Query & Data Transformation
71. Which of these can Power Query NOT do?
o a) Transform and clean data
o b) Write VBA macros
o c) Import data from web pages
o d) Append multiple data sources
o Answer: b
72. What is the purpose of the "Append Queries" option in Power Query?
o a) To combine data from multiple tables into one
o b) To split a table into smaller tables
o c) To sort data in ascending order
o d) To calculate summary statistics
o Answer: a
73. Which of the following actions is NOT part of data transformation?
o a) Removing duplicates
o b) Changing column data types
o c) Merging queries
o d) Printing the data
o Answer: d
74. What is the output of Power Query loaded to Excel?
o a) A transformed table in a worksheet
o b) A chart
o c) A Pivot Table
o d) A blank workbook
o Answer: a
75. How can you access Power Query in Excel?
o a) Data > Get & Transform
o b) Insert > Power Query
o c) View > Transform Data
o d) Home > Query Editor
o Answer: a
Introduction to Excel Interface and Basics
76. Which keyboard shortcut is used to open a new workbook in Excel?
o a) Ctrl + N
o b) Ctrl + O
o c) Ctrl + W
o d) Ctrl + Shift + N
o Answer: a
77. Which of the following options is used to save a workbook with a new name?
o a) Save
o b) Save As
o c) Export
o d) Share
o Answer: b
78. What is the maximum number of rows in an Excel worksheet?
o a) 1,048,576
o b) 65,536
o c) 1,000,000
o d) 104,857
o Answer: a
79. Which tab in Excel contains options for inserting charts, tables, and images?
o a) Home
o b) Insert
o c) Data
o d) Review
o Answer: b
80. What happens when you press Ctrl + Z in Excel?
o a) Saves the workbook
o b) Redoes the last action
o c) Undoes the last action
o d) Opens the Format Cells dialog
o Answer: c
Introduction to Excel Formulas
81. Which of these functions is used to calculate the largest value in a dataset?
o a) =LARGE()
o b) =MAX()
o c) =BIG()
o d) =GREATEST()
o Answer: b
82. What will =IF(A1>10, "Yes", "No") return if A1 contains 5?
o a) Yes
o b) No
o c) Error
o d) Blank Cell
o Answer: b
83. How does the SUMIF function differ from SUM?
o a) SUMIF adds values that meet a specific condition
o b) SUM adds only numeric values
o c) SUMIF counts the number of cells with values
o d) SUMIF cannot handle ranges
o Answer: a
84. Which formula will extract the first 3 characters from the text in cell A1?
o a) =LEFT(A1,3)
o b) =RIGHT(A1,3)
o c) =MID(A1,3)
o d) =TEXT(A1,3)
o Answer: a
85. What does the PROPER function do in Excel?
o a) Capitalizes the first letter of each word in a text string
o b) Converts text to uppercase
o c) Converts text to lowercase
o d) Extracts numeric values from a text string
o Answer: a
Data Cleaning
86. What does the TRIM function remove?
o a) Extra spaces from a text string
o b) Numbers from a text string
o c) Special characters from a text string
o d) Trailing zeros from numbers
o Answer: a
87. What happens when you use the "Remove Duplicates" feature on a dataset?
o a) Deletes all rows with duplicate entries
o b) Deletes only empty rows
o c) Highlights duplicate rows
o d) Merges duplicate rows into one
o Answer: a
88. Which method is most effective for correcting inconsistent data in Excel?
o a) Flash Fill
o b) Find and Replace
o c) Data Validation
o d) AutoSum
o Answer: b
89. What is the default delimiter used in Excel’s Text to Columns tool?
o a) Comma
o b) Space
o c) Tab
o d) Semicolon
o Answer: c
90. How do you merge two adjacent cells into one?
o a) Home > Merge & Center
o b) Insert > Merge Cells
o c) Data > Merge & Center
o d) Review > Merge Cells
o Answer: a
Data Analysis and Visualization
91. What is the purpose of a Pivot Table?
o a) To summarize and analyze large datasets
o b) To clean and transform data
o c) To highlight duplicate data
o d) To calculate formulas automatically
o Answer: a
92. Which of the following is NOT a valid chart type in Excel?
o a) Clustered Column Chart
o b) Doughnut Chart
o c) 3D Bubble Chart
o d) Radar Chart
o Answer: c
93. Which function is used to count the number of unique items in a range?
o a) =COUNT()
o b) =UNIQUE()
o c) =COUNTIF()
o d) =COUNTUNIQUE()
o Answer: b
94. What is a slicer in Excel used for?
o a) Filtering data in Pivot Tables
o b) Splitting text into columns
o c) Combining datasets
o d) Removing duplicate data
o Answer: a
95. Which chart would best represent the composition of a whole?
o a) Pie Chart
o b) Line Chart
o c) Histogram
o d) Scatter Plot
o Answer: a
Advanced Power Query and Data Transformation
96. Which of these data sources can Power Query import from?
o a) SQL Database
o b) JSON files
o c) Websites
o d) All of the above
o Answer: d
97. What happens when you "Load to Data Model" in Power Query?
o a) Data is transformed but not displayed in a worksheet
o b) Data is saved in a new Excel workbook
o c) Data is exported to a CSV file
o d) The source file is overwritten
o Answer: a
98. Which feature in Power Query is used to combine data from multiple tables?
o a) Merge Queries
o b) Split Columns
o c) Append Queries
o d) Group By
o Answer: a
99. How do you refresh a Power Query in Excel?
o a) Right-click on the query > Refresh
o b) Click on Data > Refresh All
o c) Use Ctrl + Alt + R
o d) All of the above
o Answer: d
100. Which of these steps is essential before applying transformations in Power
Query?
a)Load data into Power Query Editor
b)Save the workbook
c) Export data to CSV
d) Apply conditional formatting
Answer: a