42 Excel Interview Questions For Data Analyst

Introduction

In this article, we have put together an amazing list of basic, intermediate, and advanced MS Excel interview questions and answers for data analysts and business analysts. These questions are frequently asked in most interviews, and they will definitely help you to be ready to secure your dream job.

Click here to know what does a Data Analyst do?

Now, let’s see 42 excel interview questions and answers for data analyst and business analyst:

Q.1 Explain Microsoft Excel in short.

Microsoft Excel is a software or a desktop application that can be used to store information in the form of rows and columns. It also has features including arithmetic and other mathematical operations, and data visualization. It is available in most operating systems like Mac, Windows, Androids, and so on.

Q.2 Explain the characteristics of Microsoft Excel.

MS Excel is user-friendly and also makes data verification and validation easier.
Availability of Graphing tools, Shapes, Icons, Charts, and so on.
Build-in functions like COUNT, SUM, AVERAGE, DATE, and much more are helpful to a large extent.
Data analysis and Data visualization can be achieved with tables, filters, graphs, and so on.

Q.3 What is a spreadsheet?

Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one worksheet. All the sheets are available at the bottom of the window, along with their names

Q.4 How can you add new rows and columns to an Excel sheet?

To add new rows and columns select the place where you intend to add them and right-click on it. Then select the Insert option from where you can choose to select an entire row or column.

Q.5 Can we rearrange cells in Excel?

Yes, we can rearrange the cells in Excel. It provides the option of rearranging cells by insertion and deletion in the following ways

Shifting cells to the right
Shifting cells down
Inserting/Deleting an entire row
Inserting/Deleting entire column
Shifting cells to left
Shifting cells up

Q.6 How to add comments/notes in MS Excel cells?

Comments can be added to a specific cell by doing a right-click and selecting the insert the comment option. It is also possible to edit, delete and reply to a comment.

Q.7 What Does the Red Triangle in the Cell’s Corner Indicate?

A red triangle in the upper right corner of a cell indicates that a cell comment has been attached to this cell. If you hover over the cell with your cursor, the comment will be displayed.

Q.8 How do you find duplicates in a column?

To find but not delete duplicates in a column, select the target range of data and navigate to the Style group on the Home tab and click the arrow next to Conditional Formatting. You will then be able to choose Highlight Cell Rules, Duplicate Values, and enter the values you wish to find duplicates of. This will highlight duplicates of the values you entered.

Q.9 How to filter a table?

The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.

Q.10 What are the ways to extract unique values in Excel?

Excel can extract unique values by temporarily filtering out duplicates, or by permanently deleting duplicates. The first can be achieved by selecting the desired range of data and navigating to Data > Sort & Filter > Advanced. To permanently delete duplicate values and create a list of unique values only, click Data > Data Tools > Remove Duplicates.

Q.11 Define Excel Charts.

A chart in Excel is a feature that allows you to display data through a range of visually intuitive graphs. These charts and graphs can make it easier and quicker to comprehend data compared to just looking at the numbers on the worksheet. Some of the Excel Charts include,

Bar graphs
Line graphs
Pie charts
Area graph

Q.12 How Do You Hyperlink in Excel?

To create a link in Excel, select the element you wish to use as the anchor (this can be a cell or an object like a picture). You can then either select Link from the Insert tab, right-click and select Link on the menu, or press Ctrl+K.

Q.13 What is meant by ribbon in MS Excel?

Users can access most of the common functionalities of Excel using the toolbars and menus that form a part of the ribbon. The user also has the option of customizing the ribbon. For example, we often add the ‘Developer’ tab on the ribbon. We can remove or add an option with the help of CTRL+F1. The ribbon appears at the top of the application.

Q.14 Explain the significance of Freezing Panes in Microsoft Excel.

Freezing panes are used to have a view of the headers of the columns and rows even if we scroll to a large extent up or down. The freeze pane is achieved by selecting the cell from View and then by selecting one of the freeze options.

Q.15 How to enable Protection in MS Excel?

Protection is achieved in MS Excel to prevent access to certain operations. Protection in MS Excel is achieved in three ways

Protection via password on the opening of the workbook.
Protection against hide/unhide/add/deletion of worksheets.
Window sizes/positions are protected from being modified.

Q.16 What is Relative Cell Address?

The Relative Cell Address is a type of cell reference in Microsoft Excel that is modified and replaced while the Autofill feature is used or copied.

Q.17 What is the Absolute Cell Address?

The absolute cell address is a type of cell reference used when the cell address must remain unchanged while the Autofill feature is used or copied. The ’$’ sign is used to keep the column and row addresses constant.

Q.18 What are Macros in MS Excel?

A macro is a step or a group of steps that are performed more than once. A macro can then be called on whenever necessary to complete the sequence of actions without the user having to type each step manually. This saves valuable time and effort when performing repetitive tasks with larger sets of data.

Q.19 What Is a Dashboard in Excel?

Dashboards are a feature of Excel used to simplify and condense the presentation of data. Their purpose is to display large amounts of data on one page in a format that is easy to view and comprehend, so multiple factors can be quickly considered during the decision-making process. Dashboards achieve this by making use of various charts, graphs, gauges, and figures that display data in an intuitive way to facilitate the thorough analysis of large sets of data.

Q.20 Name the types of Report Formats available.

There are three types of formats available for reports

Tabular
Compact
Report

Q.21 What is Data Validation?

Data Validation restricts the type of values that a user can enter into a particular cell or a range of cells. In the Data tab, select the ‘Data Validation’ option present under Data Tools.

Q.22 What is a Pivot table in Excel?

PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. You can use a Pivot Table to analyze numerical data in detail and answer unanticipated questions about your data. A PivotTable is specially designed for: Querying large amounts of data in many user-friendly ways.

Q.23 Explain the characteristics of the Pivot Tables.

The characteristics of the pivot tables are:

Customized proper reports can be made.
Various data movements and relationships can be determined.
Data can be analyzed from different views.
Operations like sort, sum, and many other mathematical functions.
Links to other data sources can be added.

Q.24 How does a Slicer work in Excel?

To filter data in a Pivot table, we can use slicers.

To create a slicer, go to the Insert tab, and select Slicer present under Filter.
Then, select the list of fields for which you want to create slicers.

Q.25 What do you mean by Pivot Charts?

The pivot charts are imaged depictions of the pivot table. Pivot tables and Pivot charts are related to each other. In order to have a pivot chart, we need to choose a cell from the pivot table and then select an option for a Pivot Chart. This is available under the Insert menu in the ribbon. Examples of charts include a bar, pie, area, and so on.

Q.26 How do you create dropdown lists in Excel?

To create dropdown lists, follow the given steps:

Click on the Data tab present in the ribbon
Then, from the Data Tools group, click on Data Validation
Navigate to Settings>Allow>List
Select the source list array

Q.27 Name the different types of Functions in MS Excel.

Some of the different categories of Functions in MS Excel include:

Financial
Date and Time
Math and Trig
Lookup and Reference
Database
Text
Logical

Q.28 What is the difference between formulas and functions in Excel?

Formulas are defined by the user that is used to calculate some results. Formulas either be simple or complex and they can consist of values, functions, defined names, etc. Example finding Simple interest.
A function is a built-in piece of code that is used to perform some particular action. Excel provides a huge number of built-in functions such as SUM, PRODUCT, IF, SUMIF, COUNT, etc.

Q.29 Explain the Operator Precedence of Formulas in Excel.

BODMAS rules are followed in formulas. The term is known as Bracket, Order, Division, Multiplication, Addition, and Subtraction. If we have a formula that has a bracket and division, then the expression enclosed in the bracket shall be calculated before the division operation.

Q.30 Explain the SUM and SUMIF functions.

SUM function takes n number of arguments and performs a summation of each one of them. It basically sums up all the numbers in the range of cells.

SUMIF function is used to perform summation only if a certain condition is met. Thus SUM and SUMIF functions are almost identical except for the presence of criteria in SUMIF.

Q.31 Explain the COUNT function.

COUNT function shall return the total count of cells that have numbers in the range of cells mentioned in the parameter.

Q.32 What Is the Difference Between COUNT, COUNTA, COUNTBLANK, and COUNTIF?

All four of these functions count cells within a specified range. However, the criteria a cell needs to meet to be counted differs with each one. COUNT totals the number of cells that contain numerical values, COUNTA totals the number of cells that contain any kind of value, COUNTBLANK simply counts blank cells, and COUNTIF totals based on a condition specified by the user.

Q.33 What is the What-If Analysis in Excel?

The What-If Analysis in Excel is a powerful tool to perform complex mathematical calculations, experiment with data, and try out different scenarios.

Q.34 Define VLOOKUP in Excel.

VLOOKUP is a built-in function of excel. It is utilized to find and get data from a cell range. This is actually called a vertical lookup. As the name suggests, the data has to be organized vertically. While we are dealing with a large chunk of data, and we need to get hold of certain parts of the data fulfilling certain conditions, then that is the time when VLOOKUP is used.

Q.35 What is a Horizontal Lookup in Microsoft Excel?

Horizontal Lookup or HLOOKUP looks for a value from the topmost row of the table horizontally and then moves in a downward direction. It searches for a value in the table’s first row and returns another value in the same column from a row according to the given condition.

Q.36 What is the use of VLOOKUP and HLOOKUP?

Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Q.37 How to get the current date in Microsoft Excel?

We can get the current date in MS Excel by using the TODAY () function.

Q.38 How does the AND function work in Microsoft Excel?

AND is an inbuilt function that gives TRUE if all the conditions mentioned in the form of parameters are satisfied.

Q.39 How does the IF function work in Microsoft Excel?

In Excel, the IF() function performs a logical test. It returns a value if the test evaluates to true and another value if the test result is false. It returns the value depending on whether the condition is valid for the entire selected range.

Q.40 How do we wrap a text in Microsoft Excel?

We can wrap a text within a cell by simply selecting the cell, and then clicking on the Wrap Text option which is a part of the Home tab.

Q.41 What are the wildcards available in Excel?

Wildcards only work with text data. Excel has three wildcards.

*(Asterisk)
? (Question mark)
~ (Tilde)

Q.42 How do you apply a single format to all the sheets present in a workbook?

To apply the same format to all the sheets of a workbook, we have to

Right-click on any sheet present in that workbook
Then, click on the Select All Sheets option
Format any of the sheets and you will see that the format has been applied to all the other sheets as well

Conclusion

In this article, we have discussed the various Excel interview questions that can be asked in an interview for Data Analysts and Business Analysts. You can prepare by referring to the given answers for each of these Excel interview questions. Practicing Excel regularly and going through these Excel interview questions will keep you prepared for the interview.

Learn ML for free

Also Read: