Excel Interview Questions

Friday, November 4th 2022. | Sample Templates

Excel Interview Questions – Home Resources Guide to 50 Best Excel Interview Questions 2023 (Beginner to Advanced)

Introduction to Data ImputationArticle Introduction to Data ImputationArticle Free eBook: The Secret to Unlocking Hidden Potential TableauEbook All About Data Visualization and Why It’s One of the Best Data Skills of 2023 Video Quiz 25 Best Excel Formulas You Should Know [Updated] Article Shortcuts 50 Excel You Should Know in 2023 Program Preview Article: A Live Look at the Caltech Data Science BootcampWebinar Data Science Vs. Data Analytics vs. Machine Learning: Expert TalkArticle

Excel Interview Questions

Excel Interview Questions

Microsoft Excel is the recommended tool if you work with data. From simple calculations to reporting, Excel covers it all. Knowledge of Excel has become a necessity these days. Organizations rely on Excel to store and analyze data. This MS Excel Interview Questions and Answers article will cover the 50 most common Excel interview questions.

Easy To Use Interview Templates & Score Sheets

A cell address is used to identify a specific cell in a worksheet. This is indicated by a combination of the corresponding column letter and row number.

As mentioned above, the highlighted cell belongs to column “D” and row 5, so the cell address is read as D5.

With relational references, a change occurs when a formula is copied from one cell to another relative to the target. cell address

Meanwhile, copying a formula will not change the absolute cell reference, regardless of the cell target.

Top 23 Law Firm Interview Tips For Attorneys And Law Students: How To Excel In Law Firm Interviews

This type of links is present by default. A relative cell reference does not require a dollar sign in the formula.

If you don’t want the formula to change when you copy between cells, an absolute reference requires you to add a dollar sign before and after the column and row address.

Freeze panes keep rows and columns visible as you scroll through the worksheet. To freeze panels, select the View tab and go to Dock Panels.

Excel Interview Questions

To freeze the first two columns of the dataset, select the third column and click the Dock Panes button. It is indicated by a thick gray border.

Advanced Excel Interview Questions And Answers

2. Press Ctrl + Shift + F. The Format Cells tab will appear. Go to the protection tab. Highlight Locked and click OK.

3. Next, go to the Review tab and select the Protect Sheet option. Enter a password to protect the letter.

A formula is like an equation in Excel, the user enters it. It can be any type of calculation depending on the user’s choice.

The order of operations in Excel is known as PEDMAS. The order of priority when performing an Excel operation is shown below.

Top Microsoft Excel Interview Questions And Answers

As seen above, the data in parentheses is applied first, followed by the exponentiation operation. Then they can be division or multiplication operations. The result is then added and finally subtracted to give the final result.

Let’s look at an example of the benefits of PEMDAS in the next question in our list of Excel interview questions.

7. How would you write a formula for the following? – Multiply the value in cell A1 by 10, add 5 to the result and divide it by 2.

Excel Interview Questions

In order to write the formula for the question mentioned above, we need to follow the introduction of PEDMAS. The correct answer is ((A1*10)+5)/2.

Excel Interview Questions And Answers 2022 Latest Samples

Answers like =A1*10+5/2 and =(A1*10)+5/2 are incorrect. After a certain action, we need to put parentheses in parentheses.

The calculation function is used very often in Excel. Here, let’s look at the difference between count and its variations – counta and countblank.

Counts the number of cells that contain only numeric values. Cells that have string values, special characters, and blank cells are not counted. Below is an example of a count function.

Counts the number of cells that contain each form of content. Cells that have string values, special characters, and numeric values ​​will be counted. However, an empty cell is not counted. Below is an example of the counta function.

Excel Interview Questions

As the name suggests, it only counts the number of empty cells. Cells with content will not be taken into account. Below is an example of the countblank function.

The filtering mechanism is used when you want to display only specific data from the entire data set. In this way, no data is changed. The shortcut to add a filter to the table is Ctrl+Shift+L.

Hyperlinks are used to navigate between worksheets and files/pages. The shortcut Ctrl+K is used to create a hyperlink.

Excel Interview Questions

The Add Hyperlink box appears. Enter the address and the text to be displayed. Here we are redirected to the Amazon website.

Advanced Excel Interview Questions And Answers: Typical First Question

To merge text strings that exist in multiple cells into a single cell, you can use CONCATENATE(). Below is an example of a concatenation function.

1. Select the cell you want to split. Next, go to the Data tab and then select Text for Columns.

The VLOOKUP function in Excel is used to look up information in a table and extract the relevant data.

Value – Specifies the data you are looking for in the first column of the table.

Top 12 Excel Interview Questions And Answers (2022)

Table –  indicates the data set (table) from which you need to get                the above value.

Col_index – refers to the column in the table from where you want to                    get the value.

Range_lookup – FALSE = exact match [optional] TRUE = approximate                  match (default).

Excel Interview Questions

Below is an example of the VLOOKUP function. We need to find the product associated with the customer name – “Richard”.

Excel Vba Oral Interview Questions

VLOOKUP allows the user to look up a value in the leftmost column of a table. It then returns the value in the left-to-right direction.

The LOOKUP function allows the user to search for data in a row/column. Returns the value in another row/column.

In Excel, the IF() function performs a logical test. Returns a value if the test evaluates to true and another value if the test result is false. Returns a value based on whether the condition applies to the entire selected range.

As you can see above, the IF function returns “Record is valid” if the age is greater than 20 and the salary should be greater than $40,000. Otherwise, it returns “Invalid entry”. Here the final answer will be “the record is valid” because the entire selected range satisfies both conditions.

Interview Questions And Answers [2022]

The SUMIF() function adds cell values ​​specified by given conditions or criteria. Below is an example of the sumif function.

18. Using the COVID data, find the number of days on which the number of deaths in Italy was greater than 200.

We can use the COUNTIFS() function for this. The dataset we will use is below:

Excel Interview Questions

A pivot table is like a summary table of a data set that allows you to create reports and analyze trends. They are useful when you have long rows or columns containing values ​​that you need to track.

Exit Interview Questions Hr List Infographic Template

Drag the fields you want to display in the pivot table. Here we have created a pivot table using Corona data.

To use advanced filters, use the Advanced Filter option available on the Data tab. Select where you want to filter the table. Select “List Range” and “Criteria Range” which contains the conditions you want to filter the table by.

Here we write an index comparison function to find the city Andrew belongs to from the table below.

First, go to the Home tab, and then under Conditional Formatting, select Cell Highlight Rules. Then select Duplicate Values.

Microsoft Office Shortcut Key: Top 225+ Multiple Choice Questions Answers (mcqs) For All Examination And Interview: Office (ms Excel, Ms Word, Ms Powerpoint, And Ms Access) Shortcut Key Question Bank By Er

1. To remove duplicate values ​​in a column, select the highlighted cells and click the Remove button. After removing the values, go to the “Conditional Formatting” option on the Home tab. To remove rules from the sheet, select Clear Rules.

2. Duplicate values ​​can also be removed by selecting the ‘Remove Duplicates’ option in the Data Tools section of the Data tab.

Data validation limits the type of values ​​that a user can enter into a particular cell or range of cells.

Excel Interview Questions

In the following example, we have used data validation on the Name column to accept only text values. Entering anything other than text will cause an error.

Top Excel Macros Interview Questions And Answers

28. Below is a table of students. Write a function to add pass/fail to the result column based on the following criteria.

You can use the IF() function and check with an AND condition to populate the result column.

Learn the best business analysis techniques from Purdue University, IB and EY experts. Apply to our MSc in Business Analytics program today! 30. How are nested IF statements used in Excel?

The IF() function can be nested when we need to meet several conditions. The FALSE value in the first IF function is replaced by another IF function to perform the next test.

Popular Power Bi Interview Questions

31. Find descriptive statistics for the columns in the table below using the Data Analysis ToolPak in Excel.

32.

Advanced excel interview questions, excel questions asked in interview, common excel interview questions, interview questions on excel skills, how to answer excel interview questions, excel interview questions for data analyst, excel data analysis interview questions, excel vba interview questions, excel interview test questions, ms excel interview questions, excel experience interview questions, advanced excel questions and answers for interview