Excel is a ubiquitous software that is widely used in businesses and organizations of all sizes. As such, it is not uncommon for employers to test their potential candidates’ proficiency in Excel during the interview process. For intermediate-level Excel jobs, candidates are often required to demonstrate their ability to perform basic to moderate-level tasks and solve problems using the software. In this article, we have compiled a list of the top 30 intermediate-level Excel interview questions and answers that will help you prepare for your upcoming Excel job interview. Whether you are a seasoned Excel user or a beginner, these questions and answers will provide you with a solid understanding of the most important concepts and functions in Excel, and give you the confidence you need to succeed in your interview.
The top 30 Excel Intermediate level interview questions that are most commonly asked will be covered in this tutorial, along with the answers to these questions.
This Content Covers:
- What are the wildcards available in Excel?
- What is Data Validation? Illustrate with an example.
- Give a student table. Write a function to add pass/fail to the results column based on the following criteria.
- Calculate your age in years from the current date.
- How are nested IF statements used in Excel?
- How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?
- Is it possible to create a Pivot Table using multiple sources of data?
- Which event do you use to check whether the Pivot Table is modified or not?
- How do you create a column in a pivot table?
- How to create Slicer in Excel?
- How do you create a pivot chart in Excel?
- What are macros in Excel? How to create a macro in Excel?
- How can you tackle errors while working with formulas in Excel?
- What are the logical Functions in Excel?
- Name some of the most useful functions in Excel?
- What is Index Match function in Excel?
- What does a red triangle at the top right of a cell indicate?
- What is the use of Name Box in MS-Excel?
- How can you resize the column?
- What are three report formats that are available in Excel?
- How can you disable automatic sorting in pivot tables?
- What kind of data filters are available in Excel and how to apply them?
- What could you do to stop the pivot table from loosing the column width upon refreshing?
- Explain workbook protection types in Excel.
- How can you transport a dataset in Excel?
- Can we create shortcuts to Excel functions?
- What is the use of LOOKUP function in Excel?
- How can you apply the same formatting to every sheet in a workbook in MS-Excel?
- What function can be used to find the length of a text string in a cell?
- To move to the previous worksheet and next sheet, what keys will you press?
- What are the wildcards available in Excel?
Wildcard characters can only be used with text data. There are three wildcards available in Excel and they are:
- * (Asterisk): Any number of characters are represented by this.
- ? (Question Mark): Only one single character is represented or suggested by this.
- ~ (Tilde): The wildcard characters (*, ?, ~) are represented by this.
- What is Data Validation? Illustrate with an example.
Data validation in Excel allows it to limit data entry to only specified cells, asks users to enter accurate data when a cell is selected and shows an error message when incorrect data is entered.
For example, in the Data Validation dialogue box, I have selected the range A2:A5 which contains the product names.
Now whenever I type anything other than those names in the drop-down box it returns an error text.
- Give a student table. Write a function to add pass/fail to the results column based on the following criteria.
If a student’s Class Test Marks>8 and Final Exam Marks>40 then pass, otherwise fail.
To fill up the results column, use the IF() function and a check with an AND condition.
- Calculate your age in years from the current date.
There are two functions in Excel you can use to calculate your age in years from the current date.
One is YEARFRAC() Function:
And the other is DATEDIF() Function:
- How are nested IF statements used in Excel?
When there are multiple conditions to be met, the IF() function can be nested. Another IF function is used to do a second test in place of the FALSE value in the first IF function.
In the below picture, we have categorized results based on marks using nested IF statements.
- How do you provide Dynamic Range in the ‘Data Source’ of Pivot Tables?
Dynamic range can be provided in the Data Source of a Pivot table by creating a named table.
Step 1: Go to Insert tab, select Table and create a table in your worksheet.
Step 2: Give a name to the table under the Table Design tab.
- Is it possible to create a Pivot Table using multiple sources of data?
Yes, multiple worksheets can be used to build a pivot table. In both tables, there must be a common row for this to happen. This will serve as the first table’s Primary key and the second table’s Foreign key. After establishing a connection between the tables, you can create the pivot table.
- Which event do you use to check whether the Pivot Table is modified or not?
We use “PivotTableUpdate” in the worksheet containing the pivot table to check whether the pivot table has been modified or not.
- How do you create a column in a pivot table?
Step 1: Go to PivotTable Analyze>>Fields, Items, & Sets and select Calculated Fields.
Step 2: From Insert Calculated Field dialogue box give the column a name, then use the pivot table’s existing columns to input the formula by selecting a column and clicking on Insert Field. In order to create the column, click Add>>OK.
- How to create Slicer in Excel?
Step 1: Go to Insert>>Slicer.
Step 2: Select the list of fields for which you want to create slicers and click OK.
- How do you create a pivot chart in Excel?
Step 1: To create a pivot chart, select any cell of a pivot table then go to Insert>>Pivot Chart.
Step 2: Select a suitable chart to represent your pivot table data.
- What are macros in Excel? How to create a macro in Excel?
A macro is a piece of software that lives within an Excel file. It is used to automate repetitive tasks that you would like Excel to perform out.
Step 1: To create a macro, go to View>>Macros>>Record Macro.
Step 2: Now record a macro inside Record Macro dialogue box and click OK.
- How can you tackle errors while working with formulas in Excel?
There are a number of ways for tackling errors in Excel.
- IFNA function can be utilized to tackle errors.
- ISERROR function can be used to tackle errors while working with formulas in Excel. If there is an error, ISERROR will return TRUE; otherwise, it will return FALSE.
- If the formula returns an error, Excel’s IFERROR function can be used to extract a specific value.
- What are the logical Functions in Excel?
To determine if a scenario is true or untrue, spreadsheets utilize logical functions. You can then choose to take one of two actions based on the results of that test. These choices can be made in order to show data, do further calculations, or run tests. So decision-making tools for spreadsheet data are provided by logical functions. They provide you the option of viewing a cell’s contents or running a calculation, then comparing the outcome to a specified number or value.
There are three logical functions available in Excel
- IF Function
- AND Function
- OR Function.
- Name some of the most useful functions in Excel?
There are many useful functions in Excel, some of the most useful and widely used functions are:
- Math and Financial Function: DEGREE, RAND(), SQRT.
- Logical Functions: IF, AND.
- Date and Time functions: DATEVALUE(), NOW()
- Index Match: INDEX MATCH, VLOOKUP
- What is the Index Match function in Excel?
The INDEX MATCH function is the combination of two functions in Excel. The INDEX function returns the value of a cell in a table based on the column and row numbers. MATCH function returns the position of a cell in a row or column. Together, the two formulas can search for and return a cell’s value in a table using both vertical and horizontal criteria.
- What does a red triangle at the top right of a cell indicate?
The red triangle indicates that a comment has been made on the cell. The entire comment can be read by moving the cursor over it.
- What is the use of Name Box in MS Excel?
The Name Box in MS-Excel is a tool that represents the cell address when we select a cell. In the below picture cell A3 has been selected sot the Name Box is showing A3 which means the cell is situated at row 3 of column A.
- How can you resize the column?
Change the width of one column first, and then drag the border on the right side of the column header to the desired width to resize the column.
Another way of resizing the column is from the Column Width dialogue box. First select the column you want to resize and then go to Home>>Format>>Column Width. Now type your desired column width and click OK.
- What are three report formats that are available in Excel?
The three report formats available in Excel are:
- Compact
- Report
- Tabular
- How can you disable automatic sorting in pivot tables?
Step 1: Select More Sort Option>>More Options>>Uncheck Auto Sort>>Click OK.
- What kind of data filters are available in Excel and how to apply them?
There are three kinds of data filters available in Excel.
- Text filter
- Date filter
- Number filter
A data set can have a filter applied to it by first choosing the data, then clicking the Home tab>>Sort & Filter>>Filter. Or just select the data set and use CTRL+SHIFT+L shortcut to apply filter in the selected data.
- What could you do to stop the pivot table from losing the column width upon refreshing?
Right click on any cell of pivot table and select Pivot Table Options. Check Preserve Cell formatting on update and uncheck Auto column width on update. Click OK.
- Explain workbook protection types in Excel.
Excel provides you with three options to protect your workbook.
- Using a password to gain access a workbook
- Protection against shifting window size or location.
- Protection for sheet creation, modification, hiding, and unhiding
- How can you transport a dataset in Excel?
There are two ways that you can use to transport a dataset in Excel.
- Paste Special dialogue box: Select the dataset and copy them using CTRL+C. Right-click on the cell you want to transport the data into and click on Paste Special. From the Paste Special dialogue box click select All and click OK.
2. Transpose Function: Copy the dataset, right click on a cell and select Transpose (T).
- Can we create shortcuts to Excel functions?
Yes. To display the most commonly used shortcuts, you can customize the “Quick Access Toolbar” above the Home button.
- What is the use of LOOKUP function in Excel?
The LOOKUP function in Microsoft Excel returns a value from a range or an array.
In the picture below the LOOKUP function have been used to extract the sell value of product Mango for Store 1.
- How can you apply the same formatting to every sheet in a workbook in MS-Excel?
To apply the same formatting to every sheet in a workbook, right click on any sheet and click on Select All Sheets. This will select or group all the sheets in a workbook and now any formatting you apply will be applied on all of the sheets of this workbook.
- What function can be used to find the length of a text string in a cell?
LEN function can be used to find the length of a text string in a cell.
- To move to the previous worksheet and next sheet, what keys will you press?
Move to previous worksheet shortcut: CTRL+PAGE UP
Move to next worksheet shortcut: CTRL+PAGE DOWN
For ready-to-use Dashboard Templates:
