Excel Assessment TestExcel Formulas and FunctionsExcel Interview QuestionsExcel Test Questions and AnswersMicrosoft ExcelMicrosoft Office

Top 20 Microsoft Excel Test Questions

If you’re applying for a job that requires knowledge of Microsoft Excel, there's a good chance you will need to take an Excel skills test. Taking an Excel assessment is part of the pre-employment process to ensure that qualified candidates get hired. Scoring high on the skills test can be the deciding factor on whether a company offers you a job or not, so practice tests, like the ones in this post would be very valuable.

 

Question 1: The name Rate has been given to cell B1. Cell B4 contains the formula =$A4*Rate. If you copy this formula to C4, what will be the outcome?

 

The name Rate has been given to cell B1. Cell B4 contains the formula

 

Choose One:

a) 0

b) 135

c) 1522.5

d) 1350

 

Answer: Correct answer is: d) 1350

  • There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, absolute references do not change when copied or filled.
  • You can use an absolute reference to keep a row and/or column constant.
  • An absolute reference is designated in a formula by the addition of a dollar sign ($) before the column and row. If it precedes the column or row (but not both), it's known as a mixed reference.

 

 

Question 2: Which function from the below options will get us the last name in both the cells when dragged after entering in cell B2 to B3?

 

 

Which function from the below options will get us the last name in both the cells when dragged after entering in cell B2 to B3?

 

Choose One:

a) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

b) =LEFT(A2,SEARCH(" ",A2)-1)

c) =RIGHT(A2,SEARCH(" ",A2)-1)

d) =LEFT(A2,LEN(A2)-SEARCH(" ",A2))

 

Answer: Correct answer is: a) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

  • =LEFT(A2,SEARCH(" ",A2)-1) will get us the first name as we are searching for the position of space and then getting the values left of it. Removing 1 character as space.
  • =RIGHT(A2,SEARCH(" ",A2)-1) will not render any meaningful result.
  • =LEFT(A2,LEN(A2)-SEARCH(" ",A2)) will not render any meaningful result.

 

 

Question 3: I entered a date in cell B4 and I need to replace the value in that cell with 10.5 (to be used in the calculation) and have it displayed as “10.5” (without the quotes) how can I do this?

 

how to change the formatting of the cell to number

 

Choose One:

a) I should change the formatting of the cell to Number

b) First I should delete the date with the delete key

c) I can only enter a date in cell B4, not any other type of value

d) I should press the space-bar before entering the value 10.5

 

Answer: Correct answer is: a) I should change the formatting of the cell to Number

  • Answer B is incorrect, if we delete the Date, cell format will be not deleted.
  • Answer C is incorrect, we can enter any value in cell B4.
  • Answer D is incorrect, by pressing the space-bar before entering the value will not change the cell format.

 

 

Question 4: Expecting cell A1 is showing the number "14000.7489". What function ought to be utilized to round this number to the nearest integer?

 

how to round number to the nearest integer

 

Choose One:

a) =ROUND(A1,0)

b) =MROUND(A1,100)

c) =MROUND(A1,10)

d) =ROUND(A1,1

 

Answer: Correct answer is: a) =ROUND(A1,0)

  • The Excel ROUND function returns a number rounded to a given number of digits.
  • The ROUND function can round to the right or left of the decimal point.
  • The syntax for Excel Round is as =ROUND (number, num_digits)

 

 

Question 5: Assume an organization expected that its income should be $180,000 for 2014-2018. What function ought to be entered in cell E6 so it shows income in 2016 if it was above budget, else it'll display 0?

 

How to shows income in 2016 if it was above budget

 

Choose One:

a) =IF(E2<$B$6,E2,0)

b) = IF(E2<$B$6,0,E2)

c) =IF(E2>$B$6,0,E2)

d) =IF(E2>$B$6,E2,0)

 

Answer: Correct answer is: d) =IF(E2>$B$6,E2,0)

  • The IF function is one of the most popular and useful functions in Excel.
  • You use an IF statement to ask Excel to test a condition and to return one value if the condition is met, and another value if the condition is not met.
  • The syntax for Excel IF is as IF(logical_test, [value_if_true], [value_if_false])

 

 

Question 6: You entered below formula in cell E3 =VLOOKUP(2000,A2:C9,2,TRUE). What will be the outcome?

 

You entered below formula in cell E3 =VLOOKUP(2000,A2:C9,2,TRUE). What will be the outcome?

 

Choose One:

a) Alameda

b) #N/A

c) Phoenix

d) Antioch

 

Answer: Correct answer is: a) Alameda

  • Setting the last argument to TRUE tells VLOOKUP to find the closest match to the text or number you are looking for.
  • The VLOOKUP starts at the top of the range you specify and looks down (vertically) in each cell to find the value you are looking for (lookup value).
  • It stops searching when it finds a value that is greater than or equal to the lookup value.

 

 

Question 7: What would be the result of this function shown in the image?

 

 

What would be the result of this function shown in the image?

 

Choose One:

a) 18.500%

b) 17.5000%

c) 4.340%

d) 2.770%

 

Answer: Correct answer is: c) 4.340%

  • 18.500% would be incorrect as we can see in the formula the value of B4 is assigned to China.
  • 17.5000%would be incorrect as we can see in the formula the value of B2 is assigned to India.
  • 2.770% would be incorrect as we can see in the formula the value of B5 is assigned to Brazil.

 

 

Question 8: Which formula should be entered in cell G2 to generate a dynamic date that displays the last day of each month?

Which formula should be entered in cell G2 to generate a dynamic date which displays the last day of each month?

 

Choose One:

a) =EOMONTH($B$2,B1)

b) =EOMONTH($B$2,G1)

c) =EOMONTH($B$2,C1)

d) =MONTH($G$2)

 

Answer: Correct answer is: b) =EOMONTH($B$2,G1)

  • The Excel EOMONTH function returns the last day of the month, n months in the past or future.
  • We can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month.
  • Use a positive value for months to get a date in the future, and a negative value to get a date in the past.

 

 

Question 9: Which of the following is the correct formula to calculate the weighted average score in cell C8 as shown below?

Which of the following is the correct formula to calculate the weighted average score in cell C8 as shown below?

 

Choose One:

a) =AVERAGE(B2:B4)

b) =SUMPRODUCT(C2:C4,B2:B4)

c) =SUMPRODUCT(C2:C5,B2:B5)

d) =AVERAGE(C2:C4)

 

Answer: Correct answer is: C)=SUMPRODUCT(C2:C5,B2:B5)

  • Excel's SUMPRODUCT function fits perfectly for this task since it is designed to sum products, which is exactly what we need.
  • So, instead of multiplying each value by its weight individually, you supply two arrays in the SUMPRODUCT formula (in this context, an array is a continuous range of cells), and then divide the result by the sum of weights:

 

 

Question 10: Company A is looking at 4 possible activities and will accept them if the IRR is 10% or above, as appeared in cell E2. What is the formula utilized in cell C2, which can also be copied down to cell C3 through C5, to produce the required results?

What is the Excel formula used in cell C2 to produce the required results?

 

Choose One:

a) =IF(B2>=$E$2,"Accept","Reject")

b) =IF(B2>=E2,"Accept","Reject")

c) =IF(B2>=$E2,"Accept","Reject")

d) =IF(B2>=E$2,"Accept""Reject")

 

Answer: Correct answer is: a) =IF(B2>=$E$2,"Accept","Reject")

  • Answer A is correct as in this case used Absolute References.
  • Answer B is incorrect as E2 used Relative References.
  • Answer C is incorrect as E2 used Mixing Relative and Absolute References.
  • Answer D is incorrect as formula Syntax error, =IF (logical_test, [value_if_true], [value_if_false]).

 

 

Question 11: Which formula can be inserted into cell G5 and copied to all the cells of column G in order to calculate the overall price of the items?

How to calculate the overall price of the items

 

Choose One:

a) =VLOOKUP(E5,$A$2:$C$18,3)*VLOOKUP(E5,$E$2:$F$18,2)

b) =F5*VLOOKUP(E5,A:C,3,0)

c) =FIND(E5,$A$2:$A$18,0)

d) =F5*VLOOKUP(E5,A2:C18,3,FALSE)

 

Answer: Correct answer is: b) =F5*VLOOKUP(E5,A:C,3,0)

  • Answer A is incorrect as in this case, the last parameter must be FALSE to yield an accurate result, since the second VLOOKUP range, the first column includes multiple occurrences of the same value.
  • Answer C is incorrect as “FIND” is not the required function in this case as it doesn’t return a value but a position.
  • Answer D is incorrect since it doesn’t set a fixed lookup range using the $ signs. This means that the lookup range changes according to the row number. It might lead to a case that the lookup value will not be found (e.g. in cell G15).

 

 

Question 12: The excel status bar displays the text circular references. what does this mean?

 

 

The excel status bar displays the text circular references. what does this mean

 

Choose One:

a) That a workbook contains a link to an external file.

b) That the active sheet contains a pivot table

c) That a formula refers to the cell in which it is located.

d) That the workbook contains macros.

 

Answer: Correct answer is: c) That a formula refers to the cell in which it is located.

  • When an Excel formula refers back to its own cell, either directly or indirectly, it creates a circular reference.
  • This causes the formula to use its result in the calculation, which can create errors.
  • When a workbook contains a circular reference, Excel cannot automatically perform calculations.

 

 

Question 13: What is most likely the explanation for the green rectangles in Column C?

Green Rectangles in Excel Column

Choose One:

a) The numbers are formatted as text.

b) A value used in the formula is of the wrong data type.

c) A value is not available to the formula or function.

d) The formulas in the cells differ from one another in the same column.

 

Answer: Correct answer is: a) The numbers are formatted as text.

  • When numbers are formatted as text, Excel automatically adds error markings to remind the user that these numbers cannot be used within formulas.

 

 

Question 14: You need to configure Excel values (see screenshot) to show Field Names which are not required and include Excel Data Format "Number." Which of the below would not reach at this objective?

how to configure Excel values

 

Choose One:

a) Custom sort the table area by column C → A to Z; and then by column D → Z to A.

b) Sort column C → A to Z; then sort column D → Z to A.

c) Sort column D → Z to A; then filter column C by color → no fill.

d) Filter column D by “number”; then sort column C → A to Z.

 

Answer: Correct answer is: b) Sort column C → A to Z; then sort column D → Z to A.

  • The A, C, and D answers will lead items K and M to appear on top.
  • Option B will lead to the column I appearing on top.
  • In contrary, in answer B, the table will look the same after the first sort, but then if the table is resorted by column D (Z to A), then the original item “I” will be on top, as “Special – Zip Code” precedes both “General or Text” and “Number.”

 

 

Question 15: The graph (A) below was delivered dependent on the range A1:C4. It was not proposed that the diagram will incorporate the leftmost arrangement of columns ("Components"). What should be possible so as to make the correct graph as observed below (graph B)?

What should be possible so as to make the correct graph

 

Choose One:

a) Hit “Select Data” and then “Switch Row/Column”

b) Right-click the leftmost columns and their title and hit “Delete”

c) Change the range of the graph to A2:C4

d) Change the number format of cells A2 and A3 to “Text "and re-insert the chart.

 

Answer: Correct answer is: d) Change the number format of cells A2 and A3 to “Text "and re-insert the chart.

  • Answer A is incorrect and will yield a completely different chart.
  • Answer B is erroneous. This cannot be done.
  • Answer C is insufficient as the legend will still say “Series1” and “Series2” instead of “1” and “2”.
  • Answer D is correct as the issue is gotten from the way that cells A2 and A3 were consequently considered as numbers and were viewed as values of "component", precisely equivalent to 7.11714 and 8.08097 are the values of "subject 1".

 

 

Question 16: Which formula should be used in cell A4 to show the results as shown below?

Which formula should be used in cell A4 to show the results as shown below?

 

Choose One:

a) ="Income Statement "&"A2"

b) ="Income Statement "+A2

c) ="Income Statement "&A2

d) ="Income Statement"&A2

 

Answer: Correct answer is: c) ="Income Statement "&A2

  • Answer A is incorrect as “” used for A2 reference.
  • Answer B is incorrect as +is used before A2 reference.
  • Answer D is incorrect, No space between Statement and USD.

 

 

Question 17: Which option when chosen will show correct header label instead of Row/Column Labels?

 

Which option when chosen will show correct header label instead of Row Column Labels

 

Choose One:

a) Show in Compact Form

b) Show in Outline Form or Tabular Form

c) Show in Compact form and Repeat All Item Labels

d) Do Not Repeat Item Labels

 

Answer: Correct answer is: b) Show in Outline Form or Tabular Form

  • Show in Compact Form is the default state of pivot table and so it would not show correct labels.
  • Show in Compact Form and Repeat all Item labels would not do anything to the header row.
  • Do Not Repeat Item Labels has nothing to do with the Header labels of Pivot Table.

 

 

Question 18: Which action are you supposed to perform first before applying Subtotals to the data in the image?

 

 

Which action are you supposed to perform first before applying Subtotals to the data in the image?

 

Choose One:

a) Format the data in column B

b) Format the data in column A

c) Sort the data in column A

d) Sort the data in column B

 

Answer: Correct answer is: c) Sort the data in column A

  • Formatting column B is not required as it will not aid in the subtotal process.
  • Formatting column A is not required as it will not aid in the subtotal process.
  • Sorting the data in column B is not required as it will not aid in the process of subtotaling.

 

 

Question 19: If Excel data contains 30 columns and 500 rows, but when you select a print preview, you only see the first two columns and 30 rows. Why?

 

when you select a print preview, you only see the first two columns and 30 rows. Why

 

Choose One:

a) Because the margins are very wide

b) Because you greatly increased the scale in the Adjust to; field in the page setup options.

c) Because you defined a print area

d) Because you checked the Draft quality options in the page setup options.

 

Answer: Correct answer is: c) Because you defined a print area

  • If you print a specific selection on a worksheet frequently, you can define a print area that includes just that selection.
  • A print area is one or more ranges of cells that you designate to print when you don't want to print the entire worksheet.
  • If you print out a large spreadsheet without setting print areas, you run the risk of outputting hard to read, unformatted pages.

 

 

Question 20: Each day, $1 bills as well as $0.01 and $0.05 coins are set aside for charity. Assume that Column B and F are automated upon inserting quantities. Which formula can you use to calculate Total Cash for Charity in G15 without using additional formulas?

Excel Formula for Total Cash for Chairty

 

Choose One:

a) COUNTIF ()

b) SUMIF ()

c) SUM ()

d) COUNT ()

 

Answer: Correct answer is: c) SUM ()

COUNT and COUNTIF are not suitable for this situation as they return the number of cells, which are not unfilled. They don't restore actual values.

SUMIF might be utilized to fill in G15 effectively however the condition will be too complex to be in any way composed without extra function (IF, OR, and so on.)

SUM is totally acceptable and straightforward for the table, even though the question poses it as a condition. One simply needs to determine which cells of the spreadsheet ought to be summed → =SUM(C10,G10,G9)

 

 

Why Employers Test Job Candidates?

Assessing a job candidate’s Microsoft Excel proficiency is an important step in making the right hire. The intermediate Microsoft Excel skills test is helpful in learning if a job candidate understands how to manipulate the many functions, tools, and formulas of Excel to present extensive information, identify key trends, or calculate financial and numerical data.

Microsoft Excel is a complex business application which allows companies to record, track, and analyze data and perform very complex modelling and calculations, essential for measuring company performance, maximizing return on investment, and defining goals. Since Microsoft Excel is used in a lot of organizations, employers would like to make sure that candidates has solid Excel skills and experience using the tool. A new employee who can comfortably navigate is prepared to immediately contribute by producing meaningful, data-driven spreadsheets, reports, and graphs to best serve company needs.

 

 

How to Pass an Excel Skills Test

1. UNDERSTAND THE JOB REQUIREMENTS

It's important to understand what sort of excel skills are required by your potential employer. Are they expecting advanced analytical capabilities or very basic excel abilities? This information can help remove some anxiety around the assessment, and you'll know exactly what to practice. Read the job description closely and don't hesitate to ask the hiring manager for a detailed job description if it's not available.

 

2. LEARN EXCEL BY WATCHING YOUTUBE VIDEOS

There are a lot of different resources available to get ready for Excel Assessment Test on YouTube. Below are some of the recommended resources. Please make sure to take advantage of them to get started. The best thing about YouTube Videos is that they are Free.

  1. Excel Job Test Tutorial For Beginners
  2. How To Pass Microsoft Excel Test - Get Ready for the Interview
  3. How to Pass Excel Assessment Test For Job Applications

 

3. USE PREMIUM RESOURCES TO GET PREPARED FASTER

You can also review out Job Interview and Assessment Test Preparation Resources or Enroll into my Udemy Training Course or visit my Our Store find what you looking for. Premium resources are designed to promote accelerated learning and help you get prepared faster. If you have some time and looking to get ready for the test in the nest 3-6 month make sure to Follow Online Training for Everyone on Social Media to be the first one to get notifications and access to latest Excel Assessment Test Preparation materials.

 

 

Jobs that might require Excel Assessment Test

We have compiled a list of jobs that most likely will require Excel Assessment Test as part of Interview and hiring process. Career fields that may require the Excel skills test are listed below:

  • Accountants
  • Auditors
  • Administrative Assistants
  • Business Analysts
  • Cost Estimators
  • Teachers
  • Instructors
  • Financial Analysts
  • Bankers
  • Loan officers
  • Project Managers
  • Information Clerk
  • Knowledge worker
  • Data Analyst
  • Sales Analyst
  • Controller
  • Sales Managers
  • Marketing Manager
  • Marketing Analyst
  • Engineers
  • SEO Analysts
  • Price Analyst
  • Project Coordinators
  • QA analyst
  • Warehouse Managers
  • a lot of other jobs

A lot of other jobs may require an Excel assessment as well. As you look at job listings, pay close attention to desired skills and any requirements listed. This may clue you in to whether you’ll be required to complete an Excel skills assessment.

How Employers Test for Excel Skills?

There are multiple providers that conduct the tests and each one of the tests is a little different. Most of the time employers use below providers to conduct the tests

  • Indeed.com
  • IKM
  • SkillCheck
  • TotalTesting
  • Kenexa
  • ESkills.com
  • ProveIt
  • other test providers

Because Microsoft Excel is so popular and useful, companies are trying to evaluate candidates by conducting pre-employment assessment test. An Excel Interview Test is a screening process employers use as part of the candidate employment interview to test a potential candidate on their knowledge and proficiency of Microsoft Excel.

Each employer might use a slightly different variation of the Excel test. The Excel Assessment Test typically broken down into a multiple-choice section and an interactive portion. Some tests are timed but some of them do not have a limit on how long applicant can take to answer the questions, but there might be exceptions.

 

Complexity Levels of Excel Assessment Tests

Microsoft Excel includes very basic calculations to very advanced data processing and analysis that requires in-depth knowledge of every tools of Excel. Depending on the position candidate is  applying for you are likely to face a pre-employment Excel test with varying level of difficulty. Microsoft Excel Employment Assessment tests can be categorized into at least three levels of difficulty:

  • Microsoft Excel Basics TestThe basic test will evaluate your skills performing basic Excel functions. This can include anything from  File Management, Importing Data, Data Entry and FormattingApplying Styles, Doing Basic Sorting and Filtering, Printing, Formatting Cells, Managing Multiple Worksheets and so on. If you have used Excel in the past or familiar with other similar applications you should be able to refresh your skills with the quick tutorial, practice sample excel interview questions to pass assessment test. Basic Excel Test for Interview Candidates is a screening process for entry-level or experienced candidates, including administrative assistants, retail store managers, Sales Representatives, Marketing Representatives, Accountants, Entry level Analysts, or any other position that requires using Microsoft Excel.
  • Intermediate Microsoft Excel test: This test requires more than basic knowledge but is not as complex as the advanced level test. The intermediate Microsoft Excel skills test presents candidates with a series of timed questions designed to assess their knowledge of Microsoft Excel in the areas of Conditional Formatting, Data Manipulation, Validating Data Entry, Excel Tables, Advanced Charting Techniques and more. For passing this test, you will need to study and practice in advance using different tools and resources and ideally have practical experience of solving problems with Microsoft Excel. The Intermediate Excel Test is typically offered to evaluate skills of Business Analysts, Project Managers, Architects and other professionals, that may be responsible for complex decision making.
  • Advanced Microsoft Excel test: This test is designed for job that require to use Excel in a more complex manner. The test will evaluate your skills with Power Query, Pivot Tables and Pivot Reporting, VBA and Macros, Advanced Charting, Excel Dashboards, hands on experience with functions such as IF, IFS, VLOOKUP, SUMIFS, and other advanced concepts used in Microsoft Excel. An advanced Excel user would need to have advanced formula skills. He would have a skill and confidence to make his own formula to adapt to any situation. Typically, this test is offered to Senior Professionals, Executives, Financial Analysts, Scientists and other professionals that may need to use advanced features of Microsoft Excel.

 

Sample Excel Test Questions and Answers - Video Tutorial

Next Steps - Download PDF EBook

If you are trying to get prepared and Pass Excel Assessment Test quickly, please make sure to checkout the Ebook below Top 50 Excel Assessment Test Questions, which contains questions and answers from real Excel Assessment and also includes Tips, Tricks and Hacks on how to prepare and Pass Excel Assessment test today as well as tons of other Bonus Materials.

Download Top 50 Excel Assessment Test Questions and Answers

Top 50 Excel Assessment Test Questions and Answers PDF EBook teaches you everything you need to get ready for Basic and Intermediate Microsoft Excel Interview and prepare for Excel Assessment Test. You will practice most frequently asked questions on the exam and will learn what to do when you arrive at the exam testing center,  how to manage your time when taking the exam, tips to manage your time during assessment test, tricks for answer questions in different formats and much much more.

Download PDF EBook