excel case study interview

How to Perform Case Study Using Excel Data Analysis

In the latest Excel 365, performing any case study for business or research, it’s quite easier and smarter. It includes more exclusive features which give our required case studies a better way. I hope, you will learn the easiest way to perform a case study using Excel data analysis with some clear illustrations.

What Is Data Analysis in Excel?

The Analyze Data tool in Excel 365 is a powerful tool to manage our data by writing our queries directly in a search box. So we’ll be able to ask anything about our data without using any complex commands or formulas. Along with that, we can get very enriched and vivid visual illustrations or patterns of our data. In the earlier versions of Excel, the tool is named Data Analysis .

How Data Analysis Helps in Excel

It can search according to the queries through our natural language.
Provides high-level graphical illustrations and patterns.
Easy to get the Pivot Tables and Pivot Charts.
Faster steps save time.
The interest of Fields can be changed easily.

How to Use Excel Data Analysis for Case Study

Now let’s see how to apply the Analyze Data tool to perform a data analysis case study. But first, get introduced to our dataset that represents some categories’ yearly sales and profits of a company.

Read More: How to Install Data Analysis in Excel

With Default Analyze Data Option

First, we’ll see the default analyses that Excel makes automatically. Excel shows the most commonly used analyzes by default.

Click any data from the dataset.
Next, click as follows: Home > Analyze Data .

How to Use Excel Data Analysis for Case Study

Soon after, you will get an Analyze Data field on the right side of your Excel window. Where you will see different kinds of cases like- Pivot Tables and Pivot Charts .

Look, there is a sample Pivot Table of Sales and Profit by Category. Click on Insert Pivot Table .

Now see, the Pivot Table is inserted in a new sheet.

Click on Insert Pivot Chart from the Sales by Category section then you will get the Pivot Chart in a new sheet.

Here’s the chart.

Scroll down more and Excel will show you more possible Pivot Tables and Charts .

Feel free to use them if you prefer.

Read More: How to Analyze Data in Excel Using Pivot Tables

Analyze by Inserting Queries

Here, we’ll learn how to analyze data by inserting queries in the ‘ Ask a question about your data ’ box.

When you will click on the question box, it will show some default questions. Click one of them and it will show the answer according to the question. See, I clicked Total ‘Sales’ of ‘Accessories’ over ‘Year’ .

It’s the answer from Excel.

Or you can write your question. I asked- Profit chart according to Year .
After that, hit the ENTER button .

Now you see, it’s showing the chart of profit by year. Click on Insert PivotChart .

Soon after, a new sheet will open up with the PivotChart .

Also, there is a Setting icon in the Discover insights part, click it and a dialog box will open up to select the customized fields of interest.

Mark your desired fields from here. I marked Category and Profit.
Finally, just click Update .

Now it is showing the answers only about Category and Profit.

Things to Remember

The Analyze Data tool is only available in the latest Excel 365. But in the earlier versions, it is named Data Analysis ToolPak and available as Add-ins by default.

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

I hope the procedures described above will be good enough to perform a case study using Excel Data Analysis. Feel free to ask any question in the comment section and please give me feedback.

How to Use Data Analysis Toolpak in Excel
How to Enter Data for Analysis in Excel
How to Make Histogram Using Analysis ToolPak
[Fixed!] Data Analysis Not Showing in Excel

<< Go Back to Data Analysis with Excel | Learn Excel

What is ExcelDemy?

Tags: Data Analysis with Excel

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

20 Most-Asked Excel Job Interview Questions for Business Analyst

Updated on October 17, 2023

It can be difficult sometimes to pinpoint the exact tasks of a business analyst . That is because every business analyst has multiple tasks that they need to accomplish at the same time. Therefore, even the title of ‘business analyst’ can be considered a very broad term which can include titles such as data, process, project analyst and manager. This article will explore Excel for business analysts .

What are the different parts of Business Plan Template?

The key responsibility of a business analyst can consist of establishing plans and models for the finance departments of companies. It is not only an important task for the department but also this position decides on crucial financial decisions for the company itself. That is why a successful business analyst should focus on learning and improving their financial reporting, forecasting and strategizing.

Some of the duties of business analysts are analyzing the financial opportunities and threats of the company; and giving and sharing business information with and from the shareholders of the company. Establishing comprehensive analyses, identifying and solving financial problems or challenges, and budgeting the internal and external business also comes with the job position of business analyst.

One of the essential skills for a business analyst is the ability to be technically literate. That means that a business analyst should be aware of the various functions of various applications and useful software. One of these programs (and perhaps the most useful and popular of them all) is Microsoft Excel.

Below in the later chapters of this blog post, you can find 20 job interview questions related to Excel for business analysts.

Question 1: What are the common data formats in Microsoft Excel?

If you are applying for a business analyst job position, you are expected to know certain details about Microsoft Excel. The data formats which Excel allows to run in are one of these details. Due to the fact that Microsoft Excel is a business analyst’s one of the most essential tools at the workplace, knowledge about the common data formats would be crucial. This knowledge is not only required at the work but also is needed in your job interview.

A sample answer for this question can be: “The most common data formats used in Microsoft Excel are numbers, percentages, dates and sometimes texts (as in words and strings of texts).”

Question 2: How are these data formats used in Microsoft Excel?

Similar to every job position and every job interview, only knowing the information is just the half of the work. You need to justify your knowledge with your applications, actions and also, practical knowledge. Therefore, only memorizing the data formats would not be helpful, if you have not used them yourself. You need to know what they are and on top of that, you need to know how they are used in the job.

A sample answer for the uses of data formats in Excel can be: “Numbers can be formatted in data cells as decimals or round values. Percentages show a part of a whole and the whole being 100%. The dates can automatically change depending on the region and location Microsoft Excel is connected from. And the text format is used when analyses, reports or other documents are entered into the Excel spreadsheet as data.”

Question 3: What are the cell references?

Cell references (or in fact, references are general) should be one of the most useful tools an analyst can utilize. The different cell references and their uses can sometimes be seen as trivial details; however, they are absolutely not. They are actually incredible time savers, especially for an always busy business analyst.

A sample answer on the definition of cell references: “Cell references are used to refer to data located in the same Excel spreadsheet but to data in a different cell. There are three different cell reference types: Absolute, relative and mixed cell references.”

Question 4: What are the functions of different cell references?

A business analyst should be able to know specifically the difference between the various cell references. This knowledge will not only come in handy at your job interview but also in communicating and collaborating with your coworkers and employees.

A sample answer can be: “The absolute cell reference forces the data to stay in the cell which it was put in. No matter how many formulas are used on the data itself, an absolute cell reference stays with the data. The relative cell reference moves with the cell when the formula on the cell is moved to another one. And the mixed cell reference indicates that the row or the column related to the data cell is changed or moved.”

Question 5: Which key or combination of keys allow you to toggle between the absolute, relative and mixed cell references?

Supervisors, bosses, company owners, employees, your coworkers and even you have something in common: Everybody wants to save time. No one wants to spend unnecessary time on something they can otherwise do it in a much lesser time. Therefore, everyone loves shortcuts. In addition to that if you are applying for a business analyst position, they are going to expect that you know certain Microsoft Excel shortcuts.

A sample answer on the key to press to shift between the cell references can be: “In Windows devices the F4 key lets you to change the cell references. In Mac devices the combination of keys Command + T will allow you this shift.”

It would be useful to note here that if you are an expert on using Microsoft Excel on only one of the devices, do not worry. You do not need to know every shortcut in each of the operating systems. If you efficiently can use one and show that to the employers, they would be satisfied.

Question 6: What is the function of the dollar sign ($) in Microsoft Excel?

In fact, the dollar sign ($) serves many purposes in Excel and none of them is related to the dollar currency at all. The dollar sign has multiple functions including some of the formulas you can type in. It also can denote any of the currencies written in a spreadsheet. However, in terms of cell references the dollar sign has only one purpose.

A sample answer can be: “The dollar sign when written tells Excel whether to change the location of the reference or not, if the formula for it is copied to other cells.”

Question 7: What is the LOOKUP function in Microsoft Excel?

As you are going to see in the later chapters of this blog post, the LOOKUP function is one of the most popular tools in Excel. If you are applying for a business analyst job, it is also one of the functions that you must be familiar with.

A sample answer on the LOOKUP function can be: “The LOOKUP function allows the user to find exact or partial matches in the spreadsheet. The VLOOKUP option lets the user search for data located in the vertical position. The HLOOKUP option functions the same way but in the horizontal plane.”

Question 8: What is the disadvantage of using the VLOOKUP function?

These kinds of questions test you in a way that you are familiar with the advantages and the good functions of Excel as well as the disadvantages. It also offers you a chance to exercise your critical thinking muscle and combine it with your analytical knowledge.

A sample answer can be: “The major disadvantage of the VLOOKUP function is that it can become ineffective very easily. If you shift data from a column to another, it will also change the column numbers that data was located. This forces you to manually enter the same formulas into (now) different data cells.”

Question 9: How do you insert a comment into an Excel spreadsheet?

A sample answer about the way you can insert comments can be: “You can insert comments into your spreadsheet simply by selecting the cell, right clicking and choosing the ‘Insert Comment’ option.”

Question 10: What is the difference between a comment and a note?

A sample answer on the difference between the two terms can be: “While replying to comments is available; replying to notes attached to cells is not. The notes can be looked at as single annotations.”

Question 11: How important is VBA for a business analyst?

It can be important to actually sit down, do some research on the areas that you think you are lacking and practice your knowledge of Visual Basic for Applications (VBA). These actions should be done before the job interview. Otherwise, a topic such as VBA which every business analyst should be familiar with can be your weakness. And it also can become the reason that you are rejected for the job position that you have applied for.

A sample answer can be: “If a business analyst wants to be successful and work in respected companies, she or he needs to learn and get familiar with the required tools. They should always follow the trends and practice with the most modern devices and tools. VBA is perhaps the most important function of Microsoft Excel and it is crucial for a business analyst to easily use this tool.”

Question 12: What is conditional formatting?

No matter which job position you hold at the moment or which position you are looking to apply for, conditional formatting is an essential function of Excel. It is so simple and so useful at the same time that you might have been using even without knowing what it is called!

A sample answer can be: “Conditional formatting allows you to change the visual aspect of cells. For example, you want all the cells which include a value of 3 to be highlighted with a yellow highlighter and made italic. Conditional formatting lets you achieve this action in only seconds.”

Question 13: What are the most important functions of Excel to you as a business analyst?

The functions you will list as an answer to this question will show the interviewer what you do in Microsoft Excel. You might me more ready for the interview, if you have at least 5 functions ready for this question. You do not have to memorize every formula and list the most complicated ones. Ask yourself this question about the function you want to list: What are my most used functions?

A sample answer for the functions that a business analyst most use can be: “I most often use the LOOKUP function; followed by COUNT and COUNTA functions. The IF and MAX and MIN functions are also one of the ones I usually use.”

Recommended Reading: Most Used Excel Functions

Question 14: What does the COUNTA function execute in an Excel spreadsheet?

Now, you have listed your favorite and most used functions. Regardless of the functions you list, the interviewer can ask any function’s purpose or use. That is why it is important for you to generally know the most important functions. You can find the functions and formulas every business analyst needs to know later in the chapters below in this blog post.

A sample answer on the COUNTA function can be: “The COUNTA can scan all the rows and columns that contain data, identify them and ignore the empty cells.”

Question 15: What is the difference between the functions of COUNT and COUNTA?

Once again, if the interviewer is certain that using Microsoft Excel is one of the essential skills you need to have in order to be accepted, you will be asked a lot of ‘function questions.’

A sample answer on the difference can be: “While COUNT function counts the cells that contain numbers as their data source; the COUNTA function returns the number of cells that contain any kind of data in it. These can include numbers, texts, letters, dates, etc.”

Question 16: Can you import data from other software into an Excel spreadsheet?

You, as a business analyst, might be using other programs and software to enter your data. However, if the company wants you to fully transfer into Microsoft Excel, you should be able to do that without any worry and more importantly, without any delay.

A sample answer on data can be: “Importing data from various external data sources into an Excel spreadsheet is available. Just go into the ‘Data tab above in the toolbar. And by clicking the ‘Get External Data’ button, you will be able to import data from other software into Excel.”

Recommended Reading: Google Sheets vs. Microsoft Excel: Which is better?

Question 17: Why do you think the knowledge of Microsoft Excel is important for a business analyst?

A sample answer for the importance of Microsoft Excel for a business analyst can be: “Using Excel and dealing with the company’s data is crucial because it’s the only data the organization has. And it is in the hands of the business analyst to analyze and come up with results and solutions for problems. The business analyst is also the financial consultant as well as an analyst. You can become the person that the CEO listens to in order to ‘make’ or ‘break’ certain deals.”

Question 18: As a business analyst, do you choose to store your sensitive data in a Microsoft Excel spreadsheet?

In order to answer these kinds of direct questions, the only thing you need to do is to be honest. That is it. No interviewer wants to see a ‘yes man’ when conducting a job interview. If there are some things you think that are lacking in Microsoft Excel, you should speak and voice your opinion.

A sample answer can be: “Yes, I do store my client’s data in Microsoft Excel. However, if the data I am dealing with is confidential, then I would not be storing that sensitive data in an Excel file.”

Question 19: As a business analyst, how would you operate with sensitive data in Microsoft Excel?

A sample answer for the security of information in an Excel file can be: “Due to the fact that I would be responsible for the transfer, the possible disappearance or the leak of the data, I would store confidential data in a software other than Microsoft Excel.”

Question 20: How can you protect your data in Microsoft Excel?

A sample answer for the security of an Excel spreadsheet can be: “From the Review tab, you can choose to protect your sheet with a password. That way the spreadsheet will be password protected and cannot be opened or copied without the password.”

What Skills Do You Need to Be A Business Analyst?

The required skills for a business analyst consist of both soft and hard skills. That means only graduating from a business school or with a business degree will not automatically turn you into the ‘ideal candidate’ for the job.

A business analyst’s skill set should be analytical and technical as much as it should be social and communicative. You can find required skills for a business analyst in regards to both hard skills and soft skill below:

Social Skills

A business analyst should be able to present their findings to a group setting. The presentations can demonstrate the social skills a business analyst has. You should be effectively communicating with your coworkers, supervisors and clients in order to discuss certain issues. This applies to presentations with your employees to the meetings with the CEO itself.

Critical Thinking Skills

One of the most significant actions that business analysts are responsible for is evaluation. And evaluation and assessment are actions that require critical thinking. As it was mentioned above in the previous chapter, being a ‘yes man’ is not something that will boost your confidence and allow you to do a better job.

That is why critical thinking will help you focus on what you can ask and question. This skill will not only help you improve your work but also develop new skills and improve other abilities.

Problem Solving Skills

No matter what position you work in, no matter which company you work for, if you are unable to solve the problems you face, you will not be successful. Period, it is that simple. Because every imaginable job itself is some way related to solving problems.

That means you have to be good at identifying the problems themselves. Then you have to consider which ones can be or should be solved. And after that you would be able to create a plan in order to solve the problems the organization develops and the challenges you as a business analyst face.

To improve your Excel or PowerBI skills for more understandable dashboards and reporting, the most important three tips from experts are to start small, to learn how to create visuals and also to learn by doing. It’s very important to practice what you learn.

Data-Analysis-Excel-Templates-Someka-Banner

What Do Business Analysts Use Excel For?

Simple and complicated spreadsheets.

When a business analyst is working for a client and is processing results or when she or he is doing personal side work, they will be using Microsoft Excel. And they are going to create spreadsheets no matter how basic or complicated they are. In order to analyze and report your results, you are going to always need to create spreadsheets and operate in them.

Filtering, Sorting and Processing Large Amounts of Data

Using Microsoft Excel for filtering and sorting data is something every Excel user does. However, not every user might have the amount of data a business analyst handles and deals with. That is why in order to quickly and efficiently sort and filter vast quantities of data business analysts use Microsoft Excel for the job.

Creating Charts and Graphs

Excel’s endlessly customizable charts and graphs can come in handy for a business analyst when she or he is doing a presentation. These visual illustrations of data can make the audience understand and look at the data in a much clearer way.

Which Microsoft Excel Functions and Formulas Do Business Analysts Need to Know?

Basic Math Operations: There are four basic mathematical operations. They include addition, subtraction, multiplication and division.
LOOKUP Functions ( VLOOKUP and HLOOKUP )
SUM Function
COUNT Function
COUNTA Function
AVERAGE Function
MAX and MIN Functions
IF Function

Here, you can find some of the most essential functions a business analyst should be familiar with. However, there are a lot more to this list. If you are feeling like you should practice more on the functions you know and learn different ones, you should try the Someka Excel Formulas Training Kit .

Excel-Formulas-Practice-Someka-Template-Banner

This Excel template provides you various real life Excel problems and teaches you how to solve them with the necessary functions. There are three levels of function complexity: Beginner, Medium and Advanced Level. The Full Package Training Kit has the total 30 functions and 100 real world questions with it. If you are looking to train and practice on your own, this is the only necessary tool for you!

Other Excel Functions for Business Analysts

Pivot tables.

Pivot table is perhaps the tool that will save you the most time in Excel. It is capable of summarizing and analyzing data in a matter of seconds. And regardless of the size of the data itself, it will provide you the summary of it. With these features, it can be one of the best ‘friends’ of a business analyst who is using Microsoft Excel.

Charts are the essential way for you to present your data. Everyone can turn in pages upon pages of numbers. And sometimes that kind of presentations are required as well. However, as detailed and customizable as the Charts section in Excel is going to help you create incredible looking many types of charts:

Column Chart
Pie Chart (2-D and 3-D)
Doughnut Chart
XY (Scatter) Chart
Bubble Chart
Stock Chart
Surface Chart
Radar Chart
Combo Chart

Calculated Items

Calculated items work as custom formulas that use the sum of different items. It calculates the sum of different items and put into a pivot field. It is almost a supportive feature of Pivot Tables. You can use it in calculating delivery and shipment details and also, for the product orders as well.

Conditional Formatting

This feature also goes well when you are presenting your results and the processed data to a group. It helps you differentiate different data cells from each other. You can highlight, emphasize and change the format of the cells to your choosing. As a business analyst, you are going to always deal with numbers. Therefore, presenting them in a much fashionable way will help everyone to easily realize and understand what they are looking at.

Excel Templates for Business Analysts!

Flowcharts can be a handy way to demonstrate your analyses. Someka Automatic Flowchart Maker has incredible customization features. You can change the shapes, colors, categories and decision labels with only the click of a few buttons! You can also use the drag-and-drop tool if you want to stay with the basics.

Automatic-Flowchart-Maker-Excel-Template-Someka-SS2

– This workflow is created by Someka’s Flowchart Maker –

Also, conducting SWOT analysis for the company you are working for can lead to more profit and less problems. A business analyst’s job is to analyze, and so analyzing the company’s strengths, weaknesses, opportunities and the threats is crucial. And you can conduct this analysis with Someka’s free SWOT Analysis Template !

Another important tool is FMEA Excel Template. This tool is for risk analysis to detect possible failure modes and plan preventive actions.

FMEA-Excel-Template-Someka-Excel-Template-SS1

– This Dashboard is from Someka’s FMEA Excel Template –

Someka’s BCG Matrix Template creates a framework in which you can assess the value of your investments. With the four different sections titled Stars, Question Marks, Cash Cow and Poor Dog; you can analyze the market growth rate and relative market share in the Excel template. As a business analyst, this free template will be one of the tools to achieve that goal.

– This is the Dashboard section of Someka’s BCG Matrix Template –

With the Competitive Analysis Template , you can evaluate your company’s competitors. Following that, you can even develop new strategies and financial models and manage the competition in the market. The template provides charts for each of your competitors and visual illustrations of market shares. Download this free and professional template for presentations right now and do not ever lose your place in the market!

FINAL WORDS ON EXCEL FOR BUSINESS ANALYSTS

The technical skill of using Microsoft Excel is not the only factor for becoming a successful business analyst. You need to integrate good social and communicative skills, combine them with analytical and managerial skills as well. It is a job with important and life-changing consequences for the company which you are working for. And perhaps using Microsoft Excel is not the only skill for the job, but it is without a doubt one of the most important ones.

1. What is a cell address in Excel?

A cell address is used to identify a particular cell on a worksheet . It is denoted by a combination of the respective column letter and a row number.

As shown above, the highlighted cell belongs to the column ‘D’ and row 5, so the cell address is read as D5.

Learn Job Critical Skills To Help You Grow!

2. What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?

3. how do you freeze panes in excel.

Freeze panes keep the rows and columns visible while scrolling through a worksheet. To freeze panes, select the View tab and go to Freeze Panes.

If you are looking to freeze the first two columns of a dataset, select the 3rd column, and click ‘Freeze Panes’. A thick grey border indicates this.

4. How can you restrict someone from copying a cell from your worksheet?

1. First, choose the data you want to protect.

2. Hit Ctrl + Shift + F. The Format Cells tab appears. Go to the Protection tab. Check Locked and click OK.

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

Let’s now move onto our next question on our list of Excel interview questions.

5. How is a Formula different from a Function in Excel?

Now, let’s head to our next question in our list of Excel interview questions.

6. Mention the order of operations used in Excel while evaluating formulas.

The order of operations in Excel is referred to as PEDMAS. Shown below is the order of precedence while performing an Excel operation.

Parentheses
Exponentiation
Division/Multiplication
Subtraction

As seen above, first, the data in the parentheses is operated, followed by the exponentiation operation. After that, it can be either the 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 PEMDAS precedence in the next question on our Excel interview questions list.

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

To write a formula for the above-stated question, we have to follow the PEDMAS Precedence. The correct answer is ((A1*10)+5)/2.

Answers such as =A1*10+5/2 and =(A1*10)+5/2 are not correct. We must put parentheses brackets after a particular operation.

The output will look like this:

8. What is the difference between count, counta, and countblank?

The count function is very often used in Excel. Here, let’s look at the difference between count, and it’s variants - counta and countblank.

It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted. Shown below is an example of the count function.

It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted. Shown below is an example of the counta function.

3. COUNTBLANK

As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration. Shown below is an example of the countblank function.

9. What is the shortcut to add a filter to 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.

10. How do you create a hyperlink in Excel?

Hyperlinks are used to navigate between worksheets and files/websites. To create a hyperlink, the shortcut used is Ctrl+K.

The ‘Insert Hyperlink’ box appears. Enter the address and the text to display. Here, we are directed to the Amazon Website.

11. How can we merge multiple cells text strings in a cell?

To merge text strings present in multiple cells into one cell, you can use the CONCATENATE() . Shown below is an example of the concatenate function.

Another way of combining cell values is by using the “&” operator, as shown below:

Let’s now move onto the next question on our Excel interview questions list.

12. How can you split a column into 2 or more columns?

You can split a column into 2 or more columns by following the below steps:

1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns.

2. Select the delimiter.

3. Choose the column data format and select the destination you want to display the split.

4. The final output will look like below where the text is split into multiple columns.

13. What is the use of VLOOKUP and how do we use it?

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

Syntax: VLOOKUP (value, table, col_index, [range_lookup])

value - Indicates the data that you are looking for in the first column of a table.

table - Refers to the set of data (table) from which you have to retrieve the above value.

col_index - Refers to the column in the table from where you are to retrieve the value.

range_lookup - FALSE = exact match [optional] TRUE = approximate match (default).

Shown below is an example of the VLOOKUP function . We are to find the Product related to the Customer Name – “Richard”.

14. How is VLOOKUP different from the LOOKUP function?

Your data analytics career is around the corner.

15. How many report formats are available in Excel?

There are three report formats available in Excel; they are:

Compact Form
Outline Form
Tabular Form

16. How does the IF() function in Excel work?

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.

Let’s look at the below example:

As seen above, the IF function returns “Record is Valid” if age is greater than 20, and the salary should be greater than $40000. Else, it will return “Record is Invalid”. Here the final answer will be “Record is Valid” as the entire selected range qualifies both the conditions.

17. How do we use the SUMIF() function in Excel?

The SUMIF() function adds the cell values specified by a given condition or criteria. Given below is an example of the sumif function.

As seen above, the costs corresponding to the years 2010 are added as per the given criteria.

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

To perform this operation, we can use the COUNTIFS() function. The dataset we will be using is shown below:

The COUNTIFS() function we use is - =COUNTIFS(G2:G35777,"Italy",E2:E35777,">200")

19. What is a Pivot Table?

A pivot table is like a summary table of the dataset that enables you to create reports and analyze trends. They are useful when you have long rows or columns that hold values you need to track.

To create a pivot table, first, go to the Insert tab and select the ‘PivotTable’ option.

Select the table or the range and choose where you want to place the pivot table.

Drag the fields you wish to show in the pivot table. Here we have created a pivot table using the Coronavirus data.

20. Create a drop-down list in Excel.

This can be done by using the ‘Data Validation’ option present in the Data tab.

In the example below, we have created a list based on the city column of the dataset.

21. How do we apply advanced filters in Excel?

To apply advanced filters, use the Advanced Filter option present in the Data tab. Select where you want to filter the table. Choose the ‘list range’ and the ‘criteria range’ that has the conditions based on which you would like to filter the table.

The below example shows how to apply advanced filters.

22. Using the below-given sales data, highlight those cells where total sales > $5000.

Here, conditional formatting is used to highlight cells based on the criteria.

1. Select ‘Conditional Formatting’ from the home tab and under Highlight Cells Rules, choose ‘Greater Than option’.

2. Provide the condition and choose the color for the cells to be highlighted.

23. Using the given table, explain how the index-match function works in Excel.

Here, we will write an index-match function to find the city to which Andrew belongs to from the below table.

Here is how you can use the Index-Match function to get the result.

24. How do you find duplicate values in a column?

To find duplicate values in a column, you can either use Conditional Formatting or the COUNTIF() function.

1. Conditional Formatting

First, go to the Home tab, then under Conditional Formatting, select ‘Highlight Cells Rules’. Then choose ‘Duplicate Values’.

Below, we have highlighted the cells in the ‘Name’ column that have been repeated.

2. COUNTIF()

You can write a COUNTIF() function to check if the values in a particular column are repeated.

In the below example, we are fetching the duplicate names using the COUNTIF() function.

25. How can you remove duplicate values in a range of cells?

1. To delete duplicate values in a column, select the highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’ option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet.

2. You can also delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.

Moving forward, let’s have a look at the intermediate level of Excel interview questions.

26. What are the wildcards available in Excel?

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

1. * (Asterisk)

This refers to any number of characters.

The example stated below filters the customers whose name ends with “a”.

For that, we use “*a”.

2. ? (Question mark)

It represents one single character.

The example below shows how to filter a particular customer name.

3. ~ (Tilde)

It is used to identify a wildcard character (~, *, ?) in the text.

In the following example, we are filtering How?* using the tilde (~) symbol.

27. What is Data Validation? Illustrate with an example.

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.

Select the kind of data validation you want to apply.

In the following example, we have applied data validation to the ‘Name’ column to accept only text values. If you enter something other than a text, it will throw an error.

28. Given below is a student table. Write a function to add pass/fail to the results column based on the following criteria.

If student marks > 60 and attendance > 75%, then pass else the student fails.

You can use the IF() function and check with an AND condition to fill in the results column.

29. Calculate your age in years from the current date.

Use the YEARFRAC() or DATEDIF() function to return the number of whole days between start_date and end_date

$yearfrac$

Learn best business analysis techniques by Purdue University, IB and EY experts. Sign-up for our Post Graduate Program in Business Analysis TODAY!

30. How are nested IF statements used in Excel?

The function IF() can be nested when we have multiple conditions to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test.

Below, using nested IF statements, we are categorizing results based on the marks.

31. From the below table, find the descriptive statistics of the columns using the Data Analysis ToolPak in Excel.

Add the Analysis ToolPak from Options ----> Add-ins ----> Analysis ToolPak.

Click on the Data Analysis option in the Data tab. Choose Descriptive Statistics.

Below is the summary table for the columns and their respective statistical measures.

32. Using the Coronavirus dataset, create a pivot table to find the total cases in each country belonging to their respective continents.

First, drag the continent and country columns into rows. After that, drag the cases column on to the values section.

33. How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?

Dynamic Range in the data source of pivot tables is used to make your pivot table dynamic to adjust to new data when refreshed automatically.

Create a Named table to provide a dynamic range. Go to the Insert tab and select Table.

Under Table Design, give a name to the table.

Become job-ready with a globally recognized Business Analyst Certification Course. Sign-up today and enrich your career.

34. Is it possible to create a Pivot Table using multiple sources of data?

Yes, you can create a pivot table from multiple worksheets. For this, there must be a common row in both the tables. This will act as the Primary key for the first table and Foreign key for the second table. Create a relationship between the tables and then build the pivot table.

35. Create a pivot table to find the top three countries from each continent based on the total cases using COVID data.

Create a pivot table using the coronavirus dataset by dragging sales into values.
Place the continent and country columns into rows.
Filter the table by selecting ‘Top 3’.

Below is the sequence of steps to follow.

Become The Highest-Paid Business Analysis Expert

36. How do you create a column in a pivot table?

For this, you have to go to the PivotTable Analyze tab and select ‘Fields, Items & Sets’ option. Under that, you need to click ‘Calculate Field’ to create a new column.

The Insert Calculated Field box appears. Give a name to the column and insert the formula by selecting the existing columns from the pivot table. Click Add ----> OK to create the column.

37. 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.

In the below example, we have created two slicers (months, countries, and territory) to filter the pivot table.

38. Use the coronavirus dataset to find the percentage contribution of each country and continent to the total cases?

Create the pivot table to show the total cases by country and continent.
Right-click on the sum of cases column and under Show Value As, select “% of Grand Total.”

39. How do you create a pivot chart in Excel?

To create a pivot chart, first, we need to create a pivot table.

Go to the Insert tab next and select the ‘Pivot Chart’ option. Choose a suitable chart to represent your pivot table data.

Accelerate your career with our Post Graduate Program in Business Analytics in partnership with Carlson School of Management. Enroll and start learning!

40. What are macros in Excel? Create a macro to automate a task.

Macro is a program that resides within the Excel file. The use of it is to automate repetitive tasks that you would like to perform in Excel.

To record a macro, you can either go to the Developer tab and click on Record Macro or access it from the View tab.

Now that we are done with the intermediate level of the Excel interview questions, let’s move on to the advanced level of Excel interview questions.

41. 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.

Consider the following example:

If you get $10,000 worth of sales over the next few months, how much profit can you expect?”

Such scenarios can be solved using the What-If Analysis.

Go to the Data tab and click on What-If Analysis present under Forecast.

Scenario Manager is used for a comparison of different scenarios.

The Goal Seek performs reverse calculations.

The Data Table is used for sensitivity analysis.

To learn more about how What-If analysis works, click on this link: “IQ video link”

42. What is the difference between a function and a subroutine in VBA?

43. what is the difference between thisworkbook and activeworkbook in vba, 44. how will you pass arguments to vba function.

Arguments can be passed to a VBA function as a reference or as a value.

Below is an example to illustrate both the usages.

Dim x As Integer

MsgBox Triple(x)

If you run the cells by passing the values as a reference, it will display 40 both the times. When we pass arguments by reference, we are referencing the original value. The original value of x is changed in the function.

When we pass the arguments by value, we are passing a copy to the function. The original value is not changed. Hence, the second MsgBox will display the original value 10.

45. How do you find the last row and column in VBA?

To find the last row, use the below lines code in the VBA module:

Sub FindingLastRow()

Dim lastRow As Long

lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

MsgBox (lastRow)

To find the last column, use the below lines code in the VBA module:

Sub FindingLastColumn()

lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

MsgBox (lastColumn)

Want to learn the latest business analysis skills? Sign-up for our Post Graduate Program in Business Analytics and gain the skills to excel in the business world. Start learning TODAY!

46. How do we check whether a file exists or not in a specified location?

Sub CheckFileExists()

Dim strFileName As String

Dim strFileExists As String

strFileName = “File location\file_name.xlsx”

strFileExists = Dir(strFileName)

If strFileExists = “” Then

MsgBox “The selected file doesn't exist”

MsgBox “The selected file exists”

47. Explain how to debug a VBA code?

To debug a VBA code line by line, you can use the F8 key. You can also create a breakpoint to terminate the execution wherever you want.

The execution will start from the beginning of the code, and every time you press F8, it will execute the next line and continue until the end of the code. The yellow arrow and the highlighted line tells you the current point to execution.

Become a Data Scientist with Hands-on Training!

48. Write a VBA function to calculate the area of a rectangle.

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Area = Length * Width

End Function

49. Write a VBA function to check if a number is a prime number or not.

Sub Prime()

Dim divisors As Integer, number As Long, i As Long

divisors = 0

number = InputBox(“Enter a number”)

For i = 1 To number

If number Mod i = 0 Then

divisors = divisors + 1

If divisors = 2 Then

MsgBox number & “ is a prime number”

MsgBox number & “ is not a prime number”

Learn the latest tools, work on real-world projects, and attend Masterclasses from IBM and EY experts. Join our Master's program today.

50. Write a VBA code to create a bar chart with the given data.

Consider the below data that has two features. You can use the lines of code below to create a bar chart.

Once you have run the above VBA code lines, below is the bar chart you will get.

So, those were the 50 Excel interview questions that can help you crack your Excel interviews and help you in bagging your dream job.

Now that you know the various Excel interview questions that can be asked in an interview, you can prepare by referring to the given answers for each of these Excel interview questions. Here, we had a look at a plethora of Excel interview questions based on different levels of difficulty. Practicing Excel regularly and going through these Excel interview questions will keep you prepared for any question that is thrown at you.

To kick-start your fruitful career in Excel , enroll in Business Analytics with Excel course today!

We hope this article on Excel interview questions was useful. Do you have any questions related to this article? If so, then please put it in the comments section of the article and our experts will get back to you on that right away.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Get Affiliated Certifications with Live Class programs

Data analyst.

Industry-recognized Data Analyst Master’s certificate from Simplilearn
Dedicated live sessions by faculty of industry experts
PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

IMAGES

How to Perform Case Study Using Excel Data Analysis
Excel Spreadsheet Test For Interview
How to Perform Case Study Using Excel Data Analysis
49 Free Case Study Templates ( + Case Study Format Examples + )
Excel Interview Questions and Answers
excel interview questions

VIDEO

Case Study Interview
Epic Case Study Interview
Case Study Interview
Lyndsey Williams Case Study
case study (interview)
Mirzapur Case Study: Full Explanation. Excel Case Study #exceltips #casestudy #exceltest #excel

COMMENTS

How to Perform Case Study Using Excel Data Analysis
Excel shows the most commonly used analyzes by default. Steps: Click any data from the dataset. Next, click as follows: Home > Analyze Data. Soon after, you will get an Analyze Data field on the right side of your Excel window. Where you will see different kinds of cases like- Pivot Tables and Pivot Charts. Look, there is a sample Pivot Table ...
20 Most-Asked Excel Job Interview Questions for ...
A sample answer for the functions that a business analyst most use can be: “I most often use the LOOKUP function; followed by COUNT and COUNTA functions. The IF and MAX and MIN functions are also one of the ones I usually use.”. Recommended Reading: Most Used Excel Functions.
Top 50 Excel Interview Questions for 2024 (Beginner to
Ex: = SUM (A1:A3) Now, let’s head to our next question in our list of Excel interview questions. 6. Mention the order of operations used in Excel while evaluating formulas. The order of operations in Excel is referred to as PEDMAS. Shown below is the order of precedence while performing an Excel operation.