How Excel Interview is typically organized
In this post we will look how to get ready to Microsoft Excel Interview: you will learn top questions and answers and will find out typical skills that are being tested.
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.
FREE Excel Assessment Test PDF
Download Free Excel Assessment Test PDF EBook - 34 page work book with 10 Sample Questions from the real Excel Pre-Employment Exam. Answers and explanations included!!!
Key Excel Abilities evaluated during the Interview
- 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
Why employers test candidate's Excel Skills during the interview
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) Explain 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) Which are the two macro languages in MS-Excel?
XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and mostly used now.
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 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?
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
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) What are three report formats that are available in Excel?
Following are the types of report formats
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) Is it possible to make Pivot table using multiple sources of data?
If the multiple sources are different worksheets, from the same workbook, then it is possible to make Pivot table using multiple sources of data.
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) What is the use of LOOKUP function in Excel?
In Microsoft Excel, the LOOKUP function returns a value from a range or an array.
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) What are left, right, fill and distributed alignments?
Left /Right alignment align the text to left and right most of the cell.
Fill as the name suggests, fill the cell with same text repetitively.
Distributed, spread the text across the width of the cell.
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) Which function is used to determine the day of the week for a date?
WEEKDAY () returns the day of the week for a particular date counting from Sunday.
Example: Let date at A1 be 12/30/2016
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) Explain few useful 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())
- 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 textsf
44) What does a red triangle at the top right of a cell indicate?
The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can read the full comment.
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:
48) What is the use of LOOK UP function in MS Excel?
The LOOK UP function is used to return a value from an array.
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 to do well on Excel Test for Job Interview
Typically, you are limited on time to get prepared and you can’t learn about all functionality available. The best way to study is to practice with the questions. No matter how much you study, you will likely encounter questions you have never seen any material. There are 2 types of preparation for the exam you need to consider
Practice with Excel to learn the skills
- Step by step tutorials. One of the best ways to learn the application is to start doing something using it. There is a reason why employer wants to test candidates in Excel: it is because the person that will get hired will be using the application on the regular basis.
- Find a mentor. One of the fastest way to learn the technology is to find a mentor and together complete step by step creation of the Excel document, from start to finish. This mentor can be someone you know who already uses the Excel and can even be your High School or College student
- Follow Step by Step Tutorial. Using Step by Step tutorial you follow along and complete required steps in the application. The Tutorial can be an eBook, training course or step by step video guide. Once you completed the steps with tutorial, then you try to do it without the tutorial. This way you learn how to use to tool and can independently complete certain functions.
- Online Resources. There are a lot of videos and step by step tutorials available on the internet, but most of them have been designed for general purpose and are not geared for people, trying to pass Excel Test. Most of the candidates need materials that can help them get prepared quickly to master excel, specifically to pass Excel Assessment Test for Job Application
- eBook. One of the fastest way to learn Excel is to use eBook which covers relevant material. The big advantage of eBook is that it allows you to focus on the topics you already know and skip ahead to learn topics that you need to master.
- Excel Training Course. One of the ways to improve your technical skills is to take excel training course. A lot of quality courses are available that would get you ready
Practice by answering questions to prepare for an Interview
There are many benefits to utilizing practice tests, and adequately prepared for Excel Employment Assessment. With practice tests, students can get a feel for the types of questions they should expect to see during the test period, and they will also help them understand areas they need to improve on. After reviewing their practice test results, however, they can get a better sense of which areas are weakest, noted the User Interface, Navigation, Keyboard shortcuts or something else. Having this knowledge, they can then focus their efforts in a more productive manner.
Another huge benefit of taking practice tests is the familiarity of material. Using these study materials, students can get a feel for the layout and question types the exams will have and won't be as nervous when answering the actual questions. They will feel more self-assured because they will be used to reading and analyzing similar question structures.
Timed Excel Assessment Test can be a big problem for candidates who aren't used to having a strict time limit. By taking practice tests and knowing approximately how long it take for you to answer one question you can calculate allowable time and successfully plan your actual exam. With testing exercises, candidates can take their work home where they can time themselves as they work on questions. Doing these drills will allow students to understand how much time they have to spend on each problem. This way, they can adjust to the timing and be prepared for the final.
Below is the list of resources you could use to get prepared for the Excel Interview:
All the best on your interview!!!