300+ [REAL TIME] Excel Data Analysis Interview Questions

1. How To Replace One Value With Another In Excel?

• You can replace one value with another using the FIND & REPLACE feature in Excel.
• To do this, select the data set and use the keyboard shortcut – CONTROL H (hold the control key and then press H). This will open the Find & Replace dialog box.
• In this dialog box, you can specify the value you want to change and the replacement value.

2. What Kind Of Data Filters Is Available In Excel?

In Excel, you can filter a data set based on the kind of data.

The following types of data filters are available in Excel:

• Text Filter
• Number Filter
• Date Filter

You can apply filter to a data set, by selecting the data, then clicking the Home tab and clicking on the Filter icon.

When you have tabular data and you apply filters, based on the data in the column, Excel shows you the relevant filter.

For example:
 if you have text data, it will show you filters related to text (such as text contains, begins with, ends with, etc.).

3. How Can You Sort Data In Excel?

There is a sorting feature in Excel that can sort data based on text, numbers, or colors.

Here are some ways to sort data in Excel:

• Select the data and click on one of the two sort icons in the Data tab.
• Select the data and click on the Sort icon. It will open the sort dialog box and you can specify the column to sort and the criteria (ascending/descending).
• Apply data filter, and click on the filter. Along with the filter options, it also shows the data sorting options.

4. What Is Data Validation?

Data Validation allows you to enter only that data in a cell that satisfies criteria.

For example:

if you want to only have numbers entered in a cell, then you can use Data validation to do this. In case anyone enters something other than numbers, Excel will show an error and not allow that.

Data validation options are available in the data tab.

Data validation can be really helpful when you’re creating forms in Excel.

For example:
 if you want a user to enter only their age, you can use Data Validation to make sure the cell accepts a numeric value only.

5. How Can You Transpose A Data Set In Excel?

There are two popular ways to transposing data in Excel:

• Using Paste Special dialog box.
• Using the Transpose Function.

With Paste Special dialog box, you need to first copy the data that you want to transpose, select the cell where you want to paste it, right-click and go to Paste special, and select the Transpose option.

6. How Can You Select All Blank Cells In Excel?

If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA).

To do this, you need to use the Go To Special dialog box in Excel.

Here are the steps to select all blank cells in Excel:

• Select the entire data set (including blank cells).
• Press F5 (this opens the Go to dialog box).
• Click the ‘Special’ button (this opens the Go To special dialogue box).
• Select Blanks and click OK (this selects all the blank cells in your data set).

7. How Can You Remove Duplicates From A Data Set?

Excel has an in-built functionality that allows you to remove duplicate cells/rows in Excel.

You can find the option to remove duplicates in the Data tab.

Here are the steps to remove duplicates in Excel:

• Select the data.
• Click the Data tab and then click on the Remove Duplicates option.
• In the Remove Duplicates dialog box, if your data has headers, make sure the ‘My data has headers’ option is checked. Select the column from which you want to remove duplicates.
• Click OK.

8. What Is An Advanced Filter In Excel?

Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.

Here are some differences between the regular filter and advanced filter:

• While the regular data filter will filter the existing data set, you can use Excel advanced filter to extract the data set to some other location as well.
• Excel Advanced Filter allows you to use complex criteria. For example, if you have sales data, you can filter data on a criterion where the sales rep is Bob and the region is either North or South.

9. What Is A One-variable Data Table?

One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables.

For example:

 if you want to know how much on monthly installment change if you increase/decrease the number of months, you can set up a one-variable data table for it. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have (6 months, 9 months, 12 months, etc.)

10. What Is A Two-variable Data Table?

• Two variables Data Table in Excel is most suited in situations when you want to see how the final result changes when you change two of the input variables.
• For example, if you want to know how much on monthly installment changes if you increase/decrease the number of months and the interest rate.
• You can set up a two-variable data table for it that will show you the final monthly installment based on different combinations of interest rate and number of months. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have.

11. What Is Scenario Manager?

Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.

If you only have one or two variables changing, you can create a one variable or two-variable data table. But if you have 3 or more than 3 variable that can change, then scenario manager is the way to go.

For example:

 if you’re a regional sales manager and have four areas under you, you can use scenario manager to create different scenarios (such as):

• None of the area shows any growth in sales.
• Area A grows 10% but the other 3 don’t grow.
• Area A and B grow by 10%, but other two don’t grow.

12. What Is Goal Seek?

Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.

For example:

if you’re buying a car and you want to know how many month’s installment you should opt-for so that your monthly payment is not more than $500, you can do this using Goal seek.

13. What Is A Solver?

Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek.

With Solver, you can specify what the constraints are and the objective that you need to achieve. It does the calculation in the back-end to give you a possible solution.