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 Test: The 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
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.
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
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.
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.
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.
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).
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])
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.
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.
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.
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])
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)
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.
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.
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
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.
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)
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.
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.
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.
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)
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.
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)
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.
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
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.
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.
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
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).
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.
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.
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.
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
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
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.
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.
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.
A) Extract range
B) AutoFilter
C) Sorting
D) Rules Manager
- 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.
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 + %
A) Add a sparkline to sums up information inside a single cell
B) Add a picture of the graph to a comments section of the cell
C) Link a cell to another worksheet that shows a graph when clicked
D) Add a picture of the graph to the worksheet
Correct Answer is A: Add a sparkline to sums up information inside a single cell
- A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data.
- Use sparklines to show trends in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values.
- Position a sparkline near its data for the greatest impact.
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.
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.
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.
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.
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
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.
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
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.
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
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.
