Top 50 Microsoft Excel Interview Questions and Answers

By Vadim.MikhailenkoExcel Assessment Test, Excel Interview Questions, Excel Test for Interview Candidates
Excel Assessment Test - Top 50 Question and Answers

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

Top 50 Excel Interview Questions and Answers

1) What is the latest version of Microsoft Excel ?

Microsoft Excel with an Office 365 subscription is the latest version of Excel. Previous versions include Excel 2016, Excel 2013, Excel 2010 , Excel 2007 , and Excel 2003.

 

2) What is Microsoft Excel ?

Microsoft Excel is an electronic spreadsheet program, created by Microsoft Corporation. It enables users to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and column. Microsoft Excel is also used for storing, organizing and manipulating the data with formulas using a spreadsheet system broken up by rows and columns. . Excel also offers programming that supports VBA, and we can use external database to make dynamic reports, analysis etc. Smart use of this program saves a lot of time and helps in creating our own applications too.

 

3) What is Excel Spreadsheet?

Spreadsheets are organized as  tables of values arranged in rows and columns that can be manipulated mathematically using both basic and complex arithmetic operations and functions.  It consists or rows and columns and their intersection called cells.

 

4) What is Excel ribbon?

Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of commands.

 

5) What is the difference between Microsoft Excel 2019 and Excel Online

Microsoft Excel 2019 is an electronic spreadsheet program installed on your computer. To access Excel user needs to launch the application and open the file

With Excel Online you use your web browser to create, view, and edit Excel workbooks. You store excel documents on OneDrive or Dropbox. After you’ve created your online workbook, you can share it with specific groups of people or make it public. People can view your worksheets, sort and filter data, and drill into the details of PivotTables on the web or mobile device.

Big advantage of using Excel Online is that you don't need to have Excel application installed and that multiple people can view and edit Excel workbook at the same time. When you’re working with others in Excel Online, you can edit the worksheet as you usually would. When others edit the worksheet, Excel Online shows their presence and the updates right away.

 

6) How can you wrap the text within a cell?

You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell.

 

7) What is Macro in MS-Excel

Macros are used for iterating over a group of tasks. Users can create macros for their customized repetitive functions and instructions. Macros can be either written or recorded depending on the user.

 

8) What is the Language to write Macros in Microsoft Excel?

VBA (Visual Basic for Applications) is used to write macros in Microsoft Excel.

 

9) Is it possible to prevent someone from copying the cell from your worksheet?

Yes, it is possible. To protect your worksheet from getting copied you need to protect your Excel Sheet.  Follow these steps you need to go into Menu bar >Review > Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.

 

10) What are charts in MS-Excel?

Chart used to enable graphical representation of the data in Excel, charts are provided. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab's Chart group.

 

11) How can you sum up the Rows and Column number quickly in the Excel sheet?

By using SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.

 

12) Specify the order of operations used for evaluating formulas in Excel.

The order of operations in Microsoft Excel is same as in standard mathematics. It's defined by the term "PEMDAS" or "BEDMAS".

  • Parentheses or Brackets
  • Exponent
  • Multiplication
  • Division
  • Addition
  • Subtraction

 

13) What is the keyboard shortcut to put the filter on data in Microsoft Excel 2016 or Excel 2019?

 User would need to select the top row of data you need to apply quick filter to and press Ctrl+Shift+L keyboard shortcut key to put the filter in data.

 

14) How can you add a new Excel worksheet?

To add a new Excel worksheet, you should insert worksheet tab at the bottom of the screen.

 

15) What is the use of NameBox in MS-Excel?

Name Box is used to return to a particular area of the worksheet by typing the range name or cell address in the name box.

 

16) How can you resize the column?

To resize the column, you should change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will get formatted.

 

17) Explain pivot tables and its uses.

A pivot table is a tool that allows for quick summarization of large data. It automatically performs a sort, count, total or average of the data stored in the spreadsheet and displays result in another spreadsheet. It saves a lot of time. Allows to link external data sources to our Excel.

 

18) Which function can you use to capture the contents of an open windows application and insert it into Excel?

A) Click on “Insert tab then select “Icon Set”

B) Click on “Insert tab” then click on “Clip Art”

C) Click on “Home” Tab and then click on “Copy as Picture”

D) Click on “Insert” tab then click on “Screenshot”

Answer: The correct answer is D

 

Cell values can include numbers, text or formulas?

A) True

B) False

Answer: The correct answer is True. Numbers, Text or formulas are all acceptable values in Excel.

 

19) How would you provide a Dynamic range in "Data Source" of Pivot Tables?

To provide a dynamic range in "Data Source" of Pivot tables, first, create a named range using offset function and base the pivot table using a named range created in the first step.

 

20) What will happened if you select column C and press icon outlined in red?

  1. It is the Select Center Column button.
  2. It is the content Center alignment button.
  3. It is the Delete Cell Contents button.
  4. It is a content Middle Align button.

Answer: The correct answer is b. To quickly see what the purpose of a button is in Excel, hover your mouse cursor over the icon until a Quick Tip appears with more information about the function.

 

21) Which event do you use to check whether the Pivot Table is modified or not?

To check whether the pivot table is modified or not we use "PivotTableUpdate" in worksheet containing the pivot table.

 

22) How can you disable automatic sorting in pivot tables?

To disable automatic sorting in pivot tables:

Go To > More Sort Options > Right Click 'Pivot tables' > Select 'sort menu' > select 'More Options' > deselect 'Sort automatically'.

 

23) What is Freeze Panes in MS-Excel?

To lock any row or column, freeze panes is used. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.

 

24) What could you do to stop the pivot table from loosing the column width upon refreshing?

Format loss in a pivot table can be stopped simply by changing the pivot table options. Under the "Pivot Table Options" turn on the "Enable Preserve Formatting" and disable "Auto Format" option.

 

25) Explain workbook protection types in Excel.

Excel provides three ways to protect a workbook:

  • Password protection for opening a workbook
  • Protection for adding, deleting, hiding and un-hiding sheets
  • Protection from changing size or position of windows.

 

26) Explain the difference between SUBSTITUTE and REPLACE function in MS-Excel?

The differences between the two functions are as follows: SUBSTITUTE replaces one or more instances of a given character or a text string. So, if you know the text to be replaced, use the Excel SUBSTITUTE function. REPLACE changes characters in a specified position of a text string.

 

27) Difference between COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel.

COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.

COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.

COUNTBLANK count blank cells or cells with an empty string.

COUNTIF and COUNTIFS count cells matching a certain criteria.

 

28) What is IF function in Excel?

To perform the logic test IF function is performed. It checks whether certain conditions is true or false. If the condition is true, then it will give result accordingly if the condition is false then the result or out-put will be different.

Example: For example, you select the cell, and you want to display that cell as "Greater than five," when value is true (=5 or 5) and "less than five" when value is false (<5). For that by using IF condition you can display result.

=IF (Logical test, value if true, value if false)

=IF (A1>5, "Greater than five, "Less than five")

 

29) Can we create shortcuts to Excel functions?

Yes. 'Quick Access Toolbar' above the home button can be customized to display most frequently used shortcuts.

30) When selecting the “Margins” your selection is only applicable to the left and right side of the Worksheet.

A) True

B) False

Answer: The correct answer is False. When changing margins you can change left, right, top or bottom margin. The image displayed in the question is misleading, since it hides selection for top and button changes – see full image below

 

31) How can you apply the same formatting to every sheet in a workbook in MS-Excel?

Right Click 'Worksheet tab' > Choose 'Select All Sheets'. Now any formatting done will be applied to the whole workbook. To apply to a particular group of sheets, select only those sheets that need formatting.

 

32) You can rename the Excel Sheet on the bottom left corner.

A) True

B) False

Answer: The correct answer is True. You can right mouse click on the sheet tab, which contains tab name to access rename menu – see image below. Another option to rename Excel Sheet is to or double click on the sheet name

 

33) To move to the previous worksheet and next sheet, what keys will you press?

To move to the previous worksheet, you will use the keys Ctrl + PgUp, and to move to the next sheet you will use keys Ctrl + PgDown.

 

34) What filter will you use, if you want more than two conditions or if you want to analyze the list using database function?

You will use Advanced Criteria Filter, to analyze the list or if more than two conditions should be tested.

 

35) What is the quick way to return to a particular area of a worksheet?

The quick way to return to a specific area of the worksheet is by using name box. You can type the cell address or range name in name box to return to a specific area of a worksheet.

 

36)You can make text bold or italic in Excel cell?

A) True

B) False

Answer: The correct answer is True. To format text in the cell you need to select the cell and:

      1. You can either access formatting options in the ribbon (Home Tab -> Fonts section) – see image below

  1. Or you can right mouse click on the cell to access them – see image below

 

37) What is the benefit of using formula in Excel sheet?

Calculating the numbers in Excel sheet, not only help you to give the final 'sum up' of the number but, it also calculates automatically the number replaced by another number or digit. Through Excel sheet, the complex calculations become easy like payroll deduction or averaging the student's result.

 

38) What is the "What If" condition in Excel formulas?

The "What If" condition is used to change the data in Microsoft Excel formulas to give different answers.

Example: You are buying a new car and want to calculate the exact amount of tax that will be levied on it then you can use the "What If" function. For instance, there are three cells A4,B4, and C4. First cell says about the amount, the second cell will tell about the percentage (7.5%) of tax and the final cell will calculate the exact amount of tax.

 

39) How can you disable the automating sorting in pivot tables?

To disable the automating sorting in pivot tables,

Go to > "More Sort Options"> Right Click "Pivot table" > Select "Sort" menu > Select "More Options" > Deselect the "Sort automatically when the report is created."

 

40) What is the AND function does in Excel?

Like IF function, AND function also does the logical function. To check whether the output will be true or false the AND function will evaluate at least one mathematical expression located in another cell in the spreadsheet. If you want to see the output of more than one cells in a single cell, it is possible by using AND function.

Example: If you have two cells, A1 and A2, and the value you put in those two cells are >5 and you want result should display as 'TRUE' in cell B1 if value>5, and 'False' if any of those values<5. You can use AND function to do that.

 

41) How cell reference is useful in the calculation?

In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write any formula, for specific function, you need to direct Excel the specific location of that data. This location is referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the reference cell formula.

 

42) What are Data Manipulation Functions in Excel.

Following are the functions available in Excel for manipulating the data:

  • Math and Financial Functions – SQRT, DEGREE, RAND(), GCD
  • Logical Functions – IF, AND, FALSE, TRUE
  • Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
  • Lookup and Index Match – VLOOKUP and INDEX MATCH
  • Pivot tables

 

43) What are the typical Microsoft Excel Data Formats? Name some of them.

Eleven data formats are available in Microsoft Excel for data Storage. Example:

  • Number – Stores data as a number
  • Currency – Stores data in the form of currency
  • Date – Data is stored as dates
  • Percentage – Stores numbers as a percentage
  • Text Formats – Stores data as string of text

 

44)Which of the following statements about the Print Preview is true?

a) It allows you to view what your document will look like before you physically print the pages.

b) You access the Print Preview area of the by pressing: Control + P.

c) You access the Print Preview area by clicking on the File tab (Backstage View) to go to the and then on the Print button.

d) All of the above statements are correct.

Answer: The correct answer is d. Print Preview displays document exactly as it will look on the printout. Print Preview can be triggered by pressing Control + P:  . You can also access Print Preview from the File Tab (Backstage View) – see image below

 

45)What filter should we use, if you want more than two conditions or if you want to analyze the list using database function?

You should use "Advanced Criteria Filter" to analyze the list or test more than two conditions.

 

46) What are the advantages of using formula in Excel sheet?

Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the number replaced by another number or digit. It is used to make complex calculations easy.

 

47) What is the order of sequence of operating mathematical operation in Excel?

The order of sequence is written as BEDMAS:

  • Brackets
  • Exponents
  • Division
  • Multiplication
  • Addition
  • Subtraction

 

48) What is the correct name of the area outlined in yellow on the screen print below

  1. Excel formula Bar
  2. Excel Cell Address Bar
  3. Excel Address Bar
  4. Excel Column and Row Address
  5. Excel Name Box

Answer: The correct is e. The Excel Name Box displays either the cell address or given name of a selected cell or group of cells. n the example above, Excel Name Box is displaying B3, which is the cell address of the cell containing text “Business Expenses”.

 

 

49) What is a Macro in Excel? How to create an Excel Macro?

Excel Macro is the set of instructions that is recorded by users for repetition purposes. It is created by the users for repetitive instructions and functions they perform on a regular basis.

How would you reduce the file size? What is the easiest way to reduce the file size?

You can use the following steps to reduce the file size:

  • Find the last cell that contains data in the sheet. Delete all rows and columns after this cell.
  • To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard.
  • Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows.
  • To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard.
  • Columns will get selected till the last row.
  • Press Ctrl+- on the keyboard to delete the blank columns.

 

50) What does the IF function in Excel?

IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.

 

 

Tips and Tricks to Pass Excel Interview and Assessment Test

  • If you can navigate between questions go through the entire test and do the easiest, shortest questions first. Save the more difficult questions to come back to later; perhaps another part of the test will help you out with that question or trigger knowledge that you didn’t think you had and you already answered the questions you know the answers for.
  • Keep a positive attitude throughout the test and stay relaxed. People who are positive and relaxed do better on tests than those who are negative and stressed out.
  • Consider using flashcards to help you study. You can purchase blank flashcards or create them yourself from paper and put your questions and answers. Create flashcards for the questions you have missed in the past, so you can refresh the knowledge.
  • Understand how much time you have to complete the test by dividing number of question in the test to the allotted time to determine time per question average you should use. Use this calculated average as a baseline and try to limit the time you spend on each question.
  • Always watch for time remaining and be prepared to submit test results, before time expires.
  • Even if you have no idea about the answer you still should try to guess and answer the question, since your guess might be correct

 

To learn more tips and tricks to prepare for Excel Assessment Test checkout this ebook.

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