Basic Excel Skills TestExcel Assessment TestExcel Interview QuestionsExcel Test for Interview CandidatesMicrosoft ExcelMicrosoft Office

Top 50 Excel Test Questions

A Microsoft Excel test is a pre-employment hiring assessment that employers use during the recruitment process to check how proficient candidates are with Microsoft Excel. Spreadsheets allow users to input and analyse data efficiently, and are used in all kinds of office jobs; as such, Microsoft Excel Assessment Tests are becoming increasingly common. In this post we will learn how to get prepared for Microsoft Excel Pre Employment Test by looking at Top 50 Excel Test for Job Interview Questions and Answers. We are also going to look at how companies conduct excel skills test, how employers test for Excel skills, review sample questions asked as part of Excel Portion of Job Interview and look at the resources to get prepared for each test the fastest way possible.

 

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 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

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.

 

Excel Skill Assessments through questions

There are different types of questions typically being presented during Excel Test :

  • Multiple Choice Questions
  • Multiple Answer Questions
  • True/False Questions

 

Interactive Test Simulators

It is rare that only one type of question is used during the test – most of the time questions are part of the large pool and testing software randomly selects certain number of questions from the pool and presents them to the candidate.

 

Hands-On Excel Skill Assessment

Sometimes companies also conduct hands on Excel Job Interview Test when they provide candidates with sample data file as ask them to perform certain tasks with the data, based on instructions provided.

 

Excel Skills Tested as Part of Test

During Excel Interview employer assesses candidate’s ability to use Microsoft Excel's user interface quickly and efficiently to access the right features and perform simple tasks in the application. It validates fundamental knowledge from determining the best way to open a recently closed Workbook to using the correct formula to perform automatic calculations. Candidates are required to answer true/false,  multiple-choice questions and multiple answer questions, based on Excel spreadsheets, charts and tables filled with sample data

Below is the list of topics candidates are tested for:

  • Follow the correct steps to apply formatting, use formulas or accomplish certain things in spreadsheet
  • Apply styles, use simple fill patterns and apply formatting
  • Identify the proper keyboard shortcuts to accomplish tasks more quickly.
  • Choose the right formula and cell references to automatically calculate values.
  • Perform basic tasks like hiding rows, preventing cell overflow, and pasting data.

Since each question comes with a strict time limit (one minute or less), the test will also measure a candidate’s ability to quickly format, isolate and manipulate data under a significant amount of pressure without any outside help.

 

Hands On Abilities Evaluated

  • Ability to edit Excel worksheets and workbook by identifying common functions, applying basic keyboard shortcuts, and manipulating columns and rows
  • Ability to apply sorting and filtering options as well as statistical functions to explore and summarize values
  • Ability to customize the look and feel of a Excel workbook using cell formatting tools

 

Complexity Levels of Excel Employment Assessment Tests

Microsoft Excel harbor 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 printing, formatting cells, inserting tables, 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. 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 pivot tables, creating macros, filtering, and functions such as IF, IFS, VLOOKUP, SUMIFS, VBA 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.

What Employers are Looking For?

Microsoft Excel is a comprehensive tool that allows businesses to record, track, and analyze data essential for measuring company performance, maximizing return on investment, and defining goals – when used to its fullest potential. A new employee who can comfortably navigate Excel is prepared to immediately contribute by producing meaningful, data-driven spreadsheets, reports, and graphs to best serve company needs.

Assessing a job candidate’s Microsoft Excel proficiency is an important step in making the right hire. The Beginners 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 assessment test helps predict a job candidate’s ability to:

  • Enter sales figures and properly apply formulas to generate sales totals by date, representative, product or region.
  • Conditionally format cells with the goal of highlighting specific dates, values, or ranges.
  • Create bar graphs and pie charts from large datasets to illustrate critical company data, performance metrics, and outlook.

Tips for Passing Your Excel Job Test!

TIP #1 – What are the Smartest Ways to Prepare for Assessment Test:

  • Use outlines from Test provider (Indeed.com, SkillsCheck, IKM) and review their sample questions
  • Take Online Training Courses, if available
  • Download and read Assessment Test Books and eBooks
  • Download and Use Practice Tests

TIP #2 – What to do during the test:

  • Try to schedule test in the morning, when you have high levels of energy
  • Get a good sleep before the test.
  • Do not take a test if you are tired
  • Read Question carefully (ideally more than once)
  • Answer easy questions first (if you have a choice)
  • This would allow you to leave harder questions for the end, but you will get easy answers in
  • Validate your answer with more than one method (i.e. Common sense, Manual Calculations, Use Calculator etc.)

TIP #3 – How to Pass Assessment Test Today:

  • Be prepared for Assessment Test at Home
    • You might be monitored and timed
  • Anticipate questions based on the job position
  • Research and practice before the test
  • Reflect after the test and take notes
    • Use these notes to get ready for next test
  • Improve your Skills in Between Tests by taking Practice tests and timing yourself

Excel Test Questions and Answers

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?

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)

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).

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?

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")

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])

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

A) ="Income Statement "&"A2"

B) ="Income Statement "+A2

C) ="Income Statement "&A2

D) ="Income Statement"&A2

Correct Answer is C: ="Income Statement "&A2

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

You need to display last day of the month, based on month id in the year. Which formula should be entered in cell G2 accomplish this objective?

A) =EOMONTH(B2:F2,G1)

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

C) =EOMONTH($B$2,B2 + G1)

D) =MONTH($G$2 + G1)

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.

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

A) =AVERAGE(B2:B4)

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

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

D) =AVERAGE(C2:C4)

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.
  • We use the SUMPRODUCT formula to calculate the final result by supplying correct ranges into the formula.

Assume an organization forecasting revenue of $180,000 for 2014-2018. Which function from the list should to be entered in cell E6, so it shows 2016 revenue, if value is was above forecast, or displays 0, if the value is below forecast?

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)

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])

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

A) =ROUND(A1,0)

B) =MROUND(A1,100)

C) =MROUND(A1,10)

D) =ROUND(A1,1

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)

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

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

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.

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

A) I should change the formatting of the cell to the 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 of 10.5

Correct Answer is A: I should change the formatting of the cell to the number

  • Answer B is incorrect, if we delete the date, the 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.

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?

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.

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

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?

A) 0

B) 135

C) 1522.5

D) 1350

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.

Examine Excel data in the screenshots. What is best type of Excel chart for comparing the product quality test scores?

 

 

 

 

 

 

 

A) A funnel chart

B) A Bar Chart

C) A Radar Chart

D) A Liner Chart

Correct Answer is C: A Radar Chart

  • A radar chart is a two-dimensional chart designed to plot multiple series of numbers and would be the most optimal choice since the question contains 2 series of data: test results series and test score value cross-reference.
  • A radar chart can be used to effectively represent results from both series and simplifies communication of results and provides valuable details for analysis. Consider below details that are possible with Radar Chart:

Higher Passing Score results are represented by the larger area, which is closer to the outside borders of the pentagon.

If the area covered is closer to the middle of the pentagon (in an example of the orange area) it is a representation of failed test results (in an example of Test 2, covered by the orange line)

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

A) Alameda

B) #N/A

C) Phoenix

D) Antioch

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.

Study the screenshot. What do you think will be the result displayed in the highlighted cell D2 if the following formula is entered: =IF(B2="Solicitor",IF(C2="Yes", "Yes","No"),"Not Applicable")

A) No

B) Yes

C) Not Applicable

D) Solicitor

Correct Answer is B: Yes

  • The Correct Answer is B.
  • The formula evaluates whether B2 contains the word Solicitor and if true,
  • It goes on to evaluates cell C2 contains the word, Yes, and then enter Yes in D2 as a result of the formula.

Study the screenshot which displays inventory of hotel room. You need to calculate how many rooms, which sleep 2 or more people are available. Which formula would you use?

A) SUMIF

B) COUNTIFs

C) All of the above

D) SUMIFs

Correct Answer is B: COUNTIFs

  • The Correct Answer is B.
  • You would write a COUNTIFs formula that counts all the instances of rooms that have No in the Booked column
  • Also, it has a value greater than 2 in the Sleeps column.

Study the screenshot below. John wishes to count all the expired products that are stored in the refrigerator by calculating the total count in the cell B7. Which of the functions below will permit him to calculate this?

A) =COUNTIF(B2:B6,"Yes",C2:C6,"Refrigerator")

B) =SUMIFS(B2:B6,"Yes",C2:C6,"Refrigerator")

C) =IFS(B2:B6,"Yes",C2:C6,"Refrigerator")

D) =COUNTIFS(B2:B6,"Yes",C2:C6,"Refrigerator")

Correct Answer is D:=COUNTIFS(B2:B6,"Yes", C2:C6,"Refrigerator").

  • The Correct Answer is D.
  • Answer A is incorrect. (Syntax invalid)
  • Answer B and C are also incorrect. (Syntax invalid)

Which of the following formulas will not return the exact daily average like in cell G8?

A) =AVERAGE(B2:F6)/5)

B) =AVERAGE(G2:G6)/5)

C) =(G7/5)

D) =SUM(B2:F6)/25

Correct Answer is A: =AVERAGE(B2:F6)/5)

  • To calculate the average, Excel adds the numbers together and divides them by the total number of values supplied.
  • All the answers are equivalent to the above statement.
  • Answer A is different B2:F6 is the entire scope of elements to be averaged. Therefore there is no need to divide the result by 5 or by any number whatever.

Every 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?

A) COUNTIF ()

B) SUMIF ()

C) SUM ()

D) COUNT ()

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)

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

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.

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.
  • Answer C is the description of error #N/A. Answer B is the description of the error #VALUE!
  • Answer D is a common error when formulas are copied and then altered. But it is not likely to be an error in this case.

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 column ("Components"). What should be possible so as to make the correct graph as observed below (graph B)?

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

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

  • Answer B is right 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."
  • Answer A is insufficient as the legend will still say “Series1” and “Series2” instead of “1” and “2.”
  • Answer C is erroneous. This cannot be done. Answer D is incorrect and will yield a completely different chart

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?

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.

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
  • On 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.

Applying AutoSum formulas in the range B12:E12 render wrong results, why is that?

A) AutoSum formulas exclude the last row from the results

B) AutoSum formulas refer to the column left of their cells

C) AutoSum formulas are referring to their own cells causing the error

D) AutoSum formulas are including the header in the calculation

Correct Answer is D: AutoSum formulas are including the header in the calculation

  • AutoSum formulas do not exclude the last row.
  • AutoSum formulas are not referring to the left row as the data is on the columns.
  • AutoSum formulas do not refer to the cells they are placed in.

Which function would you choose from below to round up a value to the nearest increment of your choice like for instance up to next 5?

A) ROUNDUP

B) MAX

C) CEILING

D) ROUND

Correct Answer is C: CEILING

  • ROUNDUP function rounds the given value upwards to a specified number of digits
  • MAX function is used for finding the maximum value in a given range
  • ROUND function rounds the given value to specified digits up or down depending upon the value

Which function determine how many cells in a range contain values that meet a single condition?

A) COUNT

B) COUNTIF

C) SUMFS

D) COUNTA

Correct Answer is B: COUNTIF

  • Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.
  • you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify.
  • Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s).

Which worksheet formula does NOT return the number 4?

A) =16^0.5

B) =LOG10(10000)

C) =SQRT(16

D) =ROOT(16,2)

Correct Answer is D: =ROOT(16,2)

  • There is no such formula in Microsoft Excel that returned 4 from =ROOT(16,4).
  • All the remaining options returned 4 in a Microsoft Excel Worksheet.
  • Type all options in Microsoft Excel Worksheet, Only option =ROOT(16,2) will NOT return the 4.

Which tool can combine last names, initials, and first names in Column D, if a worksheet column A contains employee last names, column B contains employee middle initials (if any) and C contains employee first names?

A) Columns to Test

B) Concatenation

C) AutoFill

D) Flash Fill

Correct Answer is B: Concatenation

  • Concatenation in Excel is the process of joining two or more values together.
  • This method is often used to combine a few pieces of text that reside in different cells (technically, these are called text strings or simply strings)
  • When you concatenate cells in Excel, you combine only the contents of those cells.

When cell A1 value is 7 its background displays a green color. Changing the value to 8 changes the background color to blue. What type of formatting applied to cell A1?

A) Tabular Formatting

B) Cell Style Formatting

C) Conditional formatting

D) Value formatting

Correct Answer is C: Conditional formatting

  • Conditional formatting allows you to automatically apply formatting, such as colors, icons, and data bars, to one or more cells based on the cell value.
  • To do this, you'll need to create a conditional formatting rule.
  • For example, a conditional formatting rule might be: If the value is equal to 7, color the cell Green. If the value change to 8, color the cell Blue.

If column C is displaying a hash (#), because the column is too narrow. How can we fix column C to display all the data?

A) Double-click Column C

B) Right-Click column C, Select format cell, and then select best-Fit

C) Double-click the vertical boundary between column C and D

D) Right-click column C and select best-Fit

Correct Answer is C: Double-click the vertical boundary between column C and D

  • The "hashtag" error, a string of hash or pound characters like ####### is not technically an error, but it looks like one.
  • In most cases, it indicates the column width is too narrow to display the value as formatted.
  • Drag the column marker to the right until you have doubled or even tripled the width

Which keyboard shortcut would you use to wrap the text and start a new line, when inside a cell?

A) Alt + Enter

B) CTRL + Enter

C) Alt + N

D) Alt + W

Correct Answer is A: Alt + Enter

  • When you enter a text string in Excel which exceeds the width of the cell, you can see the text overflowing to the adjacent cell(s).
  • And in case you have some text in the adjacent cell, the otherwise overflowing text would disappear and you will only see the text that can fit the cell column width
  • This can be done using the wrap text feature in Excel

Which keyboard shortcut would you use to enter a current date in a cell?

A) Alt + D

B) CTRL + ;

C) CTRL + 7

D) CTRL + D

Correct Answer is B: CTRL + ;

  • A static value in a worksheet is one that doesn’t change when the worksheet is recalculated or opened.
  • When you press a key combination such as Ctrl+; to insert the current date in a cell, Excel “takes a snapshot” of the current date and then inserts the date in the cell.
  • Because that cell’s value doesn’t change, it’s considered static.

You use a(n) ________to automate data analyses and organize the answer returned by Excel.

A) Extract data range

B) Extract

C) Formula

D) Data Table

Correct Answer is D: Data Table

  • Today there is an age of data analytics. A python is a pre-dominant tool for data analysis.
  • Each day there is a new library for data analysis. However, it is hard to beat Excel in data analysis for small data sets.
  • If the tables that you need to create from your data keep changing frequently. Then excel remains the tool to use for data analysis.

You can add a ______ when you want to keep others from changing your worksheet.

A) Data Range Encryption

B) Password Protection

C) Protected File Attribute

D) Trust Center Options

Correct Answer is B: Password Protection

  • You can password-protect your Microsoft Excel spreadsheets to prevent unauthorized people from opening or editing them.
  • If you only block edits, anyone can open your spreadsheets.
  • However, only people who know the password can edit them.

You can password-protect your Microsoft Excel spreadsheets to prevent unauthorized people from opening or editing them. If you only block edits, anyone can open your spreadsheets. However, only people who know the password can edit them.

A) COUNT

B) TOTAL

C) SUBTOTAL

D) GROUP

Correct Answer is C:SUBTOTAL

  • In Microsoft Excel, the Subtotal feature is not limited to only totaling subsets of values within a data set.
  • SUBTOTAL allows you to group and summarize your data using SUM, COUNT, AVERAGE, MIN, MAX, and other functions. Additionally, it creates a hierarchy of groups, known as an outline,
  • SUBTOTAL lets you display or hide the details for each subtotal, or view just a summary of the subtotals and grand totals.

You can change the value of ____ cells at any time.

A) Unprotected

B) Formatted

C) Un-Locked

D) Hidden

Correct Answer is A: Unprotected

  • Protecting cells that contain formulas prevents them from being changed, and can help avoid future errors.
  • However, locking the cell is the first step, and you must perform additional operations to protect the workbook, such as setting a password.
  • By default, when you protect cells in a sheet or workbook, all of the cells will be locked. This means they can't be reformatted or deleted, and the content in them can't be edited. By default, the locked cells can be selected, but you can change that in the protection options.

The query techniques that uses the column heading arrows is called ____. It is automatically enabled when you first create Excel Table.

A) Extract range

B) AutoFilter

C) Sorting

D) Rules Manager

Correct Answer is B:AutoFilter

  • You can use the AutoFilter feature to find, show, or hide values in one or more columns of data.
  • You can filter based on choices you make from a list or search to find the data that you seek.
  • When you filter data, entire rows will be hidden if the values in one or more columns don't meet the filtering criteria.

Which of the following will be displayed when you enter .5 in a cell that is formatted as a percent?

A) 5%

B) 50%

C) .05%

D) 0.5%

Correct Answer is B: 50%

  • In Microsoft Excel, displaying values as percentages is very straightforward.
  • To apply the percent format to a given cell or several cells, select them all, and then click the Percent Style button in the Number group on the Home tab.
  • Keyboard shortcut: Ctrl + Shift + %

When you activating a graph, which sequence of steps adds a trendline to the graph?

A) Click inside the plot area and Select Forecast

B) In the format group, select trendline from the Insert Shapes list

C) Click outside the plot area and select Add Trendline

D) Right-click a data series and select Add Trendline

Correct Answer is D:Right-click a data series and select Add Trendline.

  • A trendline is an additional line that indicates the slope (or trend) in a particular data series. Trendlines can be helpful when you are analyzing data because they can forecast future values based upon your current data.
  • Trendlines are often used for regressions analysis (or curve fitting).
  • You can also use Moving Averages to smooth out the fluctuations in a data series.

By clicking the button in the image below, indicated by the green arrow, what does it do?

A) Excel will Hide or Show formula bar

B) Select All

C) Select Row ID and Column ID

D) Hide or shows the ribbon

Correct Answer is B: Select All

  • If the worksheet contains data, and the active cell is above or to the right of the data, pressing CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.

If cell B2 contains a Text value, Which function returns TRUE?

A) =ISSTRING(B2)

B) =ISTEXT(B2)

C) =ISCHAR(B2)

D) =ISALPHA(B2)

The correct Answer is B:=ISTEXT(B2).

  • ISTEXT will return TRUE when the value is text.
  • For example, =ISTEXT(A1) will return TRUE if A1 contains “Jones".
  • ISTEXT is part of a group of functions called the IS functions that return the logical values TRUE or FALSE.

How can you limit Date Range Entry in an Excel Column C & B?

A) Data Validation

B) Data Selection

C) What-if Analysis

D) Data Filtering

The Correct Answer is A: Data Validation.

  • In Excel, the Data Validation feature is powerful, which can force users to select data from a list, prevent duplicate values from being entered, and can limit date range entry by validating values.

Which function returns the leftmost three characters from cell A2?

A) =GET(A2,3,LEFT)

B) =RETRIEVE(A2,3,Left)

C) =LEFT(A2,3)

D) =LEFTCHAR(A2,LEN(A2)-3)

The Correct Answer is C: =LEFT(A2,3)

  • Sometimes you may need to create a worksheet formula that examines the leftmost characters in a different cell. To allow for this need, Excel provides the LEFT worksheet function.
  • You use the LEFT function by specifying the cell or value to use, along with the number of characters to return.

Which Excel ribbon commands you can use to achieve same result when you select cell A1, hover the pointer over the cell border and drag the cell to a new location?

A) Cut and Format

B) Copy as Picture

C) Cut and Paste

D) Copy and Paste

The Correct Answer is C: Cut and Paste.

  • Dragging the value of the cell is similar to Cut and Paste functionality available in Excel
  • Keyboard Shortcuts:
    Cut CTRL+X
    Paste CTRL+V

How to display the product ID for the Product name (water cooler) in cell E2?

A) =MATCH(A2:A7,MATCH(E2,B2:B7,0))

B) =MATCH(A2:A7,INDEX(E2,B2:B7,0))

C) =INDEX(A2:A7,INDEX(E2,B2:B7,0))

D) =INDEX(A2:A7,MATCH(E2,B2:B7,0))

The Correct Answer is D: =INDEX(A2:A7,MATCH(E2,B2:B7,0))

  • One advantage of the INDEX / MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column.

Row ____ causes adjacent rows to have different formatting so that each row in the table is distinguished from surrounding rows.

A) Banding

B) Conditional formatting

C) Conditional Background

D) Format Painter

The Correct Answer is A: Banding

  • Color banded rows indicate every second/alternate row with color/shading. Banding is used by default in Excel Tables

To Create new Tab in Excel you ___?

A) Click File -> New Tab
B) Use CTRL+T keyboard shortcut
C) Click (+) sign next to the existing excel sheet
D) Click New Icon in Quick Access Toolbar

The Correct Answer is C: Click (+) sign next to the existing excel sheet

  • You create a new Tab (or Sheet) by clicking the (+) sign at the bottom left corner
  • Term Excel Tab is the same as Excel Sheet and these terms are used interchangeably.

To Create new Sheet in Excel you ___?

A) Use CTRL+T keyboard shortcut
B) Click File -> New Sheet
C) Click New Icon in Quick Access Toolbar
D) Right mouse click on Existing Sheet and select insert

The Correct Answer is D: Right mouse click on Existing Sheet and select insert

  • Other choices are designed to trick you to believe that they might be the right answer, but they are not valid options in Excel or incorrect for the particular question

Which Excel date format would you use to display date 01/01/2020 as January-20

A) month-yy
B) mmmm-yy
C) mmm-yy
D) month-shortyear

The Correct Answer is B: mmmm-yy

  • Other choices are designed to trick you to believe that they might be the right answer, but they are not valid options in Excel or incorrect for the particular question.

You have a date value of 01/01/2020 in cell A2. Which Excel formula would increment month to the next and allow display February-20 in the cell A3?

A) =A2+1
B) =DATE(YEAR(A2), MONTH(A2)+1, DAY(A2))
C) =MONTH(A2)+1
D) =DATE(A2+1)

The Correct Answer is B: =DATE(YEAR(A2), MONTH(A2)+1, DAY(A2))

  • Other choices are designed to trick you to believe that they might be the right answer, but they are not valid options in Excel or incorrect for the particular question

Which Excel formula would you use to generate random numbers in the range B2:F13?

A) Rand
B) Randbetween
C) Randarray
D) None of the above

The Correct Answer is C: Randarray

  • Other choices are designed to trick you to believe that they might be the right answer, but they are not valid options in Excel or incorrect for the particular question

About the Author

Vadim Mikhailenko, MBA

Vadim lives in Milwaukee, Wisconsin and works as an Information Technology Consultant, Coach and Educator. Vadim is very passionate about Milwaukee community and teaches classes to underprivileged students at local community college to help them get employed. Throughout his consulting career Vadim studied a lot of tools and technologies and learned hiring process by helping organizations to hire candidates and also supported students to get prepared and to pass interview and assessment tests.

Vadim loves to share his knowledge with and enjoys teaching  new skills to help you get hired for your dream job.

Next Step: Download PDF EBook

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