Wednesday , November 18 2026
Breaking News

Microsoft Advance Excel Certification Exam Answers 2026 (All 100 Questions)

Microsoft Advance Excel Certification exam answers 2026: This page lists all 100 questions of the official Microsoft Advance Excel Certification exam by Itronix Solutions, each with its four answer options and the correct answer highlighted in green. Use it to revise every topic before you attempt the test and earn your verifiable certificate.

About this certification: Advanced Excel skills include the ability to produce graphs and tables, use spreadsheets efficiently, and perform calculations and automation.

Certification Microsoft Advance Excel Certification
Provider Itronix Solutions
Category Office & Computer Basics
Total questions 100 multiple-choice questions
Marks 1 mark per question (100 marks)
Passing score 80% (80 out of 100)
Retake Available if you do not pass
Certificate Verifiable online certificate

Take the exam: Microsoft Advance Excel Certification on Itronix Solutions. See every other Itronix exam in our complete list of Itronix certification answers.

Microsoft Advance Excel Exam Questions and Answers

Questions 1 to 25

1. What is the shortcut key to display the “Format Cells” dialog box in Excel?

A. Ctrl+1 ✔
B. Ctrl+2
C. Ctrl+3
D. Ctrl+4

Correct answer: A. Ctrl+1

2. Which of the following format you can decide to apply or not in AutoFormat dialog box?

A. Number format
B. Border format
C. Font format
D. All of above ✔

Correct answer: D. All of above

3. In MS-Excel which functions will calculate the number of workdays between 6/9/2013 and 8/12/2013

A. Workday
B. Weekday ✔
C. Networkday
D. Date

Correct answer: B. Weekday

4. In MS-Excel data marker on a chart are linked to data points in a worksheet, therefore

A. You can automatically apply formatting to a data series
B. You can change a data point value and automatically that is drawn in the chart
C. You can change the position of a data marker and that automatically changes the data point value in the worksheet
D. Both (b) & (c) ✔

Correct answer: D. Both (b) & (c)

5. MS Excel is a ________.

A. Presentation software
B. Database Management software
C. Spreadsheet software ✔
D. Workbook software

Correct answer: C. Spreadsheet software

6. What function displays row data in a column or column data in a row?

A. Rows
B. Hyperlink
C. Index
D. Transpose ✔

Correct answer: D. Transpose

7. Hounding errors can occur

A. When you use multiplication, division or exponentiation in a formula ✔
B. When you use addition and subtraction in a formula
C. When you show the results of formulas with different decimal places’ than the calculated results
D. Because Excel uses hidden decimal places in computation

Correct answer: A. When you use multiplication, division or exponentiation in a formula

8. In Excel got functions? No? You need the insert function dialog box. How do you get it?

A. Right click a cell and then click insert
B. Click the insert menu and then click function ✔
C. Type = in a cell
D. All of the above

Correct answer: B. Click the insert menu and then click function

9. When you insert an Excel file into a Word document, the data are ______.

A. Placed in a word table ✔
B. Linked
C. Embedded
D. Hyperlinked

Correct answer: A. Placed in a word table

10. Except for the …… function, a formula with a logical function shows the word “TRUE” or “FALSE” as a result.

A. NOT
B. IF ✔
C. AND
D. OR

Correct answer: B. IF

11. Macros are “run” or executed from the ….. Menu.

A. Data
B. Insert
C. Tools ✔
D. Format

Correct answer: C. Tools

12. In Excel two common wildcard characters that Excel recognizes are

A. * and ? ✔
B. < and>
C. ^ and /
D. + and –

Correct answer: A. * and ?

13. Which of the following is not What IF analysis tool in Excel?

A. Scenarios
B. Goal Seek
C. Macros ✔
D. All of above

Correct answer: C. Macros

14. Each excel file is called a workbook because

A. It can be modified
B. It can contain many sheets including worksheets and chart sheets ✔
C. You have to work hard to create it
D. It can contain text and data

Correct answer: B. It can contain many sheets including worksheets and chart sheets

15. Which key do you press to check spelling?

A. F3
B. F5
C. F7 ✔
D. F9

Correct answer: C. F7

16. How are data organized in a spreadsheet?

A. Lines and spaces
B. Layers and planes
C. Rows and columns ✔
D. Height and width

Correct answer: C. Rows and columns

17. You can print only an embedded chart by

A. Moving the chart to a chart sheet before you print.
B. Formatting the chart before you print
C. Selecting the chart before you print
D. a and c ✔

Correct answer: D. a and c

18. How should you print a selected area of a worksheet, if you’ll want to print a different area next time?

A. On the file menu, point to print area, and then click set print area.
B. On the file menu, click print, and then click selection under print what ✔
C. On the view menu, click custom views, then click add
D. All of above

Correct answer: B. On the file menu, click print, and then click selection under print what

19. How can you delete a record?

A. Delete the column from the worksheet
B. Select Data > Form from the menu to open the Data Form dialog box, find the record and Click the Delete button ✔
C. Select Data > Delete Record from the menu
D. Click the Delete button on the Standard toolbar

Correct answer: B. Select Data > Form from the menu to open the Data Form dialog box, find the record and Click the Delete button

20. The cell labeled 23. 54 the cell labeled F5 refers to

A. row F column 5
B. column F row 5 ✔
C. function available in cells
D. function key F4

Correct answer: B. column F row 5

21. Which function adds a range of cells?

A. COUNT
B. SUM ✔
C. LEN
D. MAX

Correct answer: B. SUM

22. Which function returns the average of a range?

A. MEAN
B. MODE
C. AVERAGE ✔
D. MEDIAN

Correct answer: C. AVERAGE

23. COUNT counts cells that contain:

A. Any text
B. Numbers ✔
C. Only blanks
D. Only formulas

Correct answer: B. Numbers

24. COUNTA counts cells that are:

A. Not empty ✔
B. Only numeric
C. Only dates
D. Only blank

Correct answer: A. Not empty

25. COUNTBLANK counts:

A. Cells with numbers
B. Cells with text
C. All cells
D. Empty cells ✔

Correct answer: D. Empty cells

Questions 26 to 50

26. COUNTIF counts cells that:

A. Are formatted bold
B. Contain charts
C. Are hidden
D. Meet a single condition ✔

Correct answer: D. Meet a single condition

27. SUMIFS adds values that:

A. Are merged
B. Meet multiple conditions ✔
C. Are coloured
D. Are in charts

Correct answer: B. Meet multiple conditions

28. AVERAGEIF returns the average of cells that:

A. Meet a condition ✔
B. Are blank
C. Are text
D. Are hidden

Correct answer: A. Meet a condition

29. VLOOKUP searches for a value in:

A. Only the last column
B. The first column of a table and returns a value from the same row ✔
C. Pivot charts
D. Any row of a chart

Correct answer: B. The first column of a table and returns a value from the same row

30. In VLOOKUP, FALSE as the last argument means:

A. Approximate match
B. Ignore errors
C. Exact match ✔
D. Return blank

Correct answer: C. Exact match

31. A key limitation of VLOOKUP is that it:

A. Cannot look to the left of the lookup column ✔
B. Cannot use numbers
C. Only works on one row
D. Cannot be copied

Correct answer: A. Cannot look to the left of the lookup column

32. XLOOKUP improves on VLOOKUP because it can:

A. Only use text
B. Look in any direction and defaults to exact match ✔
C. Only search right
D. Never return errors

Correct answer: B. Look in any direction and defaults to exact match

33. INDEX and MATCH together are used to:

A. Format cells
B. Create charts
C. Perform flexible lookups ✔
D. Protect sheets

Correct answer: C. Perform flexible lookups

34. MATCH returns:

A. The cell colour
B. The value itself
C. The position of a value in a range ✔
D. The sum of a range

Correct answer: C. The position of a value in a range

35. HLOOKUP searches:

A. Vertically down the first column
B. Only pivot tables
C. Horizontally across the first row ✔
D. Only charts

Correct answer: C. Horizontally across the first row

36. The IF function returns:

A. The sum of cells
B. The cell address
C. Only TRUE
D. One value if a condition is true and another if false ✔

Correct answer: D. One value if a condition is true and another if false

37. IFERROR is used to:

A. Format errors red
B. Return a custom value when a formula results in an error ✔
C. Create errors
D. Delete formulas

Correct answer: B. Return a custom value when a formula results in an error

38. IFS lets you:

A. Merge cells
B. Test multiple conditions without nesting many IFs ✔
C. Filter rows
D. Sort data

Correct answer: B. Test multiple conditions without nesting many IFs

39. AND returns TRUE when:

A. All conditions are true ✔
B. No condition is true
C. Any condition is true
D. The cell is blank

Correct answer: A. All conditions are true

40. OR returns TRUE when:

A. Formulas are hidden
B. At least one condition is true ✔
C. All conditions are false
D. Cells are empty

Correct answer: B. At least one condition is true

41. CONCAT (or the & operator) is used to:

A. Join text from multiple cells ✔
B. Split text
C. Add numbers
D. Delete text

Correct answer: A. Join text from multiple cells

42. TEXTJOIN differs from CONCAT because it:

A. Can add a delimiter and ignore empty cells ✔
B. Sorts text
C. Deletes text
D. Only joins numbers

Correct answer: A. Can add a delimiter and ignore empty cells

43. LEFT returns:

A. The last characters
B. The middle only
C. The length
D. Characters from the start of a text string ✔

Correct answer: D. Characters from the start of a text string

44. RIGHT returns:

A. Characters from the start
B. Characters from the end of a text string ✔
C. The cell colour
D. The row number

Correct answer: B. Characters from the end of a text string

45. MID returns:

A. Only the first character
B. The sum
C. Characters from the middle of a text string at a given position ✔
D. The average

Correct answer: C. Characters from the middle of a text string at a given position

46. LEN returns:

A. The number of characters in a text string ✔
B. The sum of numbers
C. The column letter
D. The number of rows

Correct answer: A. The number of characters in a text string

47. TRIM removes:

A. All numbers
B. All formulas
C. Cell borders
D. Extra spaces from text ✔

Correct answer: D. Extra spaces from text

48. UPPER converts text to:

A. Capital letters ✔
B. Numbers
C. Proper case
D. Lowercase

Correct answer: A. Capital letters

49. PROPER converts text so that:

A. Each word starts with a capital letter ✔
B. Spaces are removed
C. All letters are capitals
D. All letters are lowercase

Correct answer: A. Each word starts with a capital letter

50. TODAY returns:

A. A fixed past date
B. The current time only
C. The file date
D. The current date ✔

Correct answer: D. The current date

Questions 51 to 75

51. NOW returns:

A. The file size
B. The current date and time ✔
C. Only the year
D. Only the month

Correct answer: B. The current date and time

52. DATEDIF can calculate:

A. Text length
B. Row height
C. Cell colour
D. The difference between two dates in days, months or years ✔

Correct answer: D. The difference between two dates in days, months or years

53. NETWORKDAYS returns:

A. Only holidays
B. Only weekends
C. All calendar days
D. The number of working days between two dates ✔

Correct answer: D. The number of working days between two dates

54. ROUND(3.456, 2) returns:

A. 3
B. 3.46 ✔
C. 3.4
D. 3.45

Correct answer: B. 3.46

55. Absolute references are written with:

A. Brackets, like [A1]
B. Hash signs, like #A1
C. Percent signs, like %A1
D. Dollar signs, like $A$1 ✔

Correct answer: D. Dollar signs, like $A$1

56. A mixed reference like $A1 locks:

A. Both row and column
B. Nothing
C. The column only ✔
D. The row only

Correct answer: C. The column only

57. Pressing F4 while editing a reference:

A. Opens help
B. Cycles through absolute and relative reference types ✔
C. Saves the file
D. Deletes the formula

Correct answer: B. Cycles through absolute and relative reference types

58. A PivotTable is used to:

A. Summarise and analyse large datasets quickly ✔
B. Draw shapes
C. Write macros
D. Protect cells

Correct answer: A. Summarise and analyse large datasets quickly

59. In a PivotTable, the Values area usually contains:

A. Row labels only
B. Chart titles
C. Filters only
D. Numbers to sum, count or average ✔

Correct answer: D. Numbers to sum, count or average

60. A slicer in Excel is used to:

A. Insert images
B. Filter PivotTables or tables visually ✔
C. Split sheets
D. Cut cells

Correct answer: B. Filter PivotTables or tables visually

61. A PivotChart is:

A. A chart linked to a PivotTable ✔
B. A formula
C. A macro
D. A static image

Correct answer: A. A chart linked to a PivotTable

62. Converting a range to an Excel Table (Ctrl+T) gives:

A. Locked cells
B. A macro
C. Structured references, filters and automatic formatting ✔
D. A chart

Correct answer: C. Structured references, filters and automatic formatting

63. Conditional formatting is used to:

A. Format cells automatically based on rules ✔
B. Write formulas
C. Protect sheets
D. Insert pictures

Correct answer: A. Format cells automatically based on rules

64. Data validation is used to:

A. Merge files
B. Create charts
C. Sort data
D. Restrict what users can enter in cells ✔

Correct answer: D. Restrict what users can enter in cells

65. A drop-down list in a cell is created with:

A. Goal Seek
B. Conditional formatting
C. Freeze panes
D. Data validation ✔

Correct answer: D. Data validation

66. Freeze Panes is used to:

A. Lock the file
B. Hide sheets
C. Stop formulas
D. Keep rows or columns visible while scrolling ✔

Correct answer: D. Keep rows or columns visible while scrolling

67. Remove Duplicates is found under:

A. The Draw tab
B. The Insert tab
C. The Data tab ✔
D. The View tab

Correct answer: C. The Data tab

68. Text to Columns is used to:

A. Delete columns
B. Join columns
C. Split text in one column into multiple columns ✔
D. Sort columns

Correct answer: C. Split text in one column into multiple columns

69. Flash Fill (Ctrl+E) is used to:

A. Insert charts
B. Delete rows
C. Automatically fill values based on a detected pattern ✔
D. Fill cells with colour

Correct answer: C. Automatically fill values based on a detected pattern

70. Goal Seek is used to:

A. Sort data
B. Find the input needed to reach a target result ✔
C. Search the web
D. Find duplicates

Correct answer: B. Find the input needed to reach a target result

71. Scenario Manager is used to:

A. Create charts
B. Insert comments
C. Protect workbooks
D. Compare different sets of input values ✔

Correct answer: D. Compare different sets of input values

72. Solver is an add-in used for:

A. Optimisation problems with constraints ✔
B. Spell checking
C. Printing
D. Translation

Correct answer: A. Optimisation problems with constraints

73. Power Query is used to:

A. Record audio
B. Draw shapes
C. Import, clean and transform data ✔
D. Send emails

Correct answer: C. Import, clean and transform data

74. Power Pivot is used to:

A. Edit images
B. Build data models and handle large related datasets ✔
C. Write VBA only
D. Print charts

Correct answer: B. Build data models and handle large related datasets

75. A macro in Excel is:

A. A large chart
B. A cell format
C. A recorded or written set of actions that can be replayed ✔
D. A type of font

Correct answer: C. A recorded or written set of actions that can be replayed

Questions 76 to 100

76. Macros are written in:

A. Python only
B. HTML
C. SQL only
D. VBA (Visual Basic for Applications) ✔

Correct answer: D. VBA (Visual Basic for Applications)

77. A macro-enabled workbook uses the extension:

A. .xlsx
B. .xlsm ✔
C. .csv
D. .txt

Correct answer: B. .xlsm

78. The #N/A error usually means:

A. A lookup value was not found ✔
B. Column too narrow
C. Division by zero
D. Wrong data type

Correct answer: A. A lookup value was not found

79. The #DIV/0! error means:

A. A value is missing
B. A name is wrong
C. A formula is dividing by zero ✔
D. A reference is deleted

Correct answer: C. A formula is dividing by zero

80. The #REF! error means:

A. A formula refers to an invalid or deleted cell ✔
B. A text value is used
C. A number is too large
D. A file is missing

Correct answer: A. A formula refers to an invalid or deleted cell

81. The #VALUE! error usually means:

A. A lookup failed
B. The sheet is protected
C. A wrong type of argument was used ✔
D. A cell is empty

Correct answer: C. A wrong type of argument was used

82. The #NAME? error usually means:

A. A circular reference
B. A missing file
C. Excel does not recognise a function or name ✔
D. Division by zero

Correct answer: C. Excel does not recognise a function or name

83. Ctrl+Shift+L in Excel:

A. Opens VBA
B. Turns filters on or off ✔
C. Inserts a line chart
D. Locks the sheet

Correct answer: B. Turns filters on or off

84. Ctrl+Arrow key moves to:

A. The first sheet
B. The help menu
C. The ribbon
D. The edge of the current data region ✔

Correct answer: D. The edge of the current data region

85. Alt+= inserts:

A. A new sheet
B. A chart
C. An AutoSum formula ✔
D. A comment

Correct answer: C. An AutoSum formula

86. Ctrl+; enters:

A. The current time
B. A semicolon list
C. The current date ✔
D. A formula

Correct answer: C. The current date

87. Named ranges are useful because they:

A. Hide data
B. Make formulas easier to read and maintain ✔
C. Change fonts
D. Delete errors

Correct answer: B. Make formulas easier to read and maintain

88. FILTER is a dynamic array function that:

A. Changes colours
B. Returns rows that meet given criteria ✔
C. Sorts text alphabetically only
D. Deletes rows

Correct answer: B. Returns rows that meet given criteria

89. SORT is a dynamic array function that:

A. Deletes duplicates
B. Merges cells
C. Filters by colour
D. Returns a sorted version of a range ✔

Correct answer: D. Returns a sorted version of a range

90. UNIQUE returns:

A. A list of distinct values from a range ✔
B. Only duplicate values
C. The count of rows
D. The first value

Correct answer: A. A list of distinct values from a range

91. A spill error (#SPILL!) happens when:

A. The file is too large
B. A dynamic array result is blocked by non-empty cells ✔
C. A chart is missing
D. A macro fails

Correct answer: B. A dynamic array result is blocked by non-empty cells

92. Sparklines are:

A. Animated effects
B. Tiny charts inside a single cell ✔
C. Macros
D. Cell borders

Correct answer: B. Tiny charts inside a single cell

93. A combo chart combines:

A. Two workbooks
B. Two or more chart types, such as column and line ✔
C. Text and images
D. Two macros

Correct answer: B. Two or more chart types, such as column and line

94. Protecting a worksheet can prevent users from:

A. Opening the file
B. Seeing charts
C. Editing locked cells ✔
D. Printing ever

Correct answer: C. Editing locked cells

95. Track Changes or comments in Excel help with:

A. Macros
B. Collaboration and review ✔
C. Calculations
D. Charts

Correct answer: B. Collaboration and review

96. What-If Analysis tools include:

A. Spell check and thesaurus
B. Charts and images
C. Fonts and colours
D. Goal Seek, Scenario Manager and Data Tables ✔

Correct answer: D. Goal Seek, Scenario Manager and Data Tables

97. A circular reference occurs when:

A. A formula refers back to its own cell directly or indirectly ✔
B. A sheet is hidden
C. A chart is circular
D. A file is shared

Correct answer: A. A formula refers back to its own cell directly or indirectly

98. To show formulas instead of results you can press:

A. Ctrl+S
B. Ctrl+Z
C. Ctrl+` (grave accent) ✔
D. Ctrl+P

Correct answer: C. Ctrl+` (grave accent)

99. Excel is best described as:

A. A spreadsheet application for calculation, analysis and visualisation ✔
B. A web browser
C. A word processor
D. An email client

Correct answer: A. A spreadsheet application for calculation, analysis and visualisation

100. The PMT function calculates:

A. The periodic payment for a loan ✔
B. The number of periods only
C. The total profit
D. The percentage change

Correct answer: A. The periodic payment for a loan

How to Pass the Microsoft Advance Excel Exam

  • Read every question carefully; most options look similar, so look for the most precise answer.
  • Revise the questions above topic by topic rather than memorising letters, because option order can change in the live test.
  • You need 80 correct answers out of 100 to pass, so aim to understand at least 85 to stay safe.
  • If you do not clear it on the first attempt, review the questions you missed and use the retake option.

Frequently Asked Questions

How many questions are in the Microsoft Advance Excel Certification exam?

The exam has 100 multiple-choice questions, each worth 1 mark.

What is the passing score for the Microsoft Advance Excel Certification?

You need at least 80% (80 out of 100) to pass and receive the certificate.

Can I retake the Microsoft Advance Excel Certification exam?

Yes. If you score below 80%, you can retry the test after reviewing the topics.

Is the Itronix certificate verifiable?

Yes. Every certificate issued by Itronix Solutions has a unique ID that can be checked on the Itronix verify-certificate page.

More Office & Computer Basics Certification Answers

Disclaimer: These answers are provided for learning and revision. Questions and answers are checked regularly; if you find a mistake, let us know in the comments.

About Priya Dogra

Priya Dogra runs priyadogra.com, where she shares free certification courses, internships, jobs, scholarships and exam preparation guides for Indian students and freshers. Follow her on YouTube, LinkedIn and Instagram for the latest opportunities.

Check Also

Unix/ Linux Certification exam answers 2026

Unix/ Linux Certification Exam Answers 2026 (All 100 Questions)

All 100 Unix/ Linux Certification exam questions with correct answers highlighted - updated 2026.

Leave a Reply

Your email address will not be published. Required fields are marked *