Last updated on October 23rd, 2024 at 02:20 pm
Did you know that 80% of the middle skill jobs require excel skills. And that means any job interview that you will apply for will directly or indirectly test you for this skill. So I am going to share my own experience with different types of advance excel interview questions.
So I am from a finance background where as far as I remember, each and every interview I gave tested me for advance excel skills. 14 years since I started working, today I teach and prepare young graduates for interviews and skill. In a nutshell, the importance of excel skills has gone from being a plus to a necessity.
So here is what you will learn;
- Interview questions with examples
- Advance excel interview questions
- Special and complex excel questions
- Practice questions and shortcuts
- ms excel questions
Basic Excel Interview Questions
First of all, let me put your worries to rest and first settle the expectations. You cannot master each and everything in excel. That’s not possible using any course. Also even if you did manage to know each and every function and feature of excel, the benefit and utility is in questions.
So let me first break down Microsoft excel for you based on its use across roles;
- Marketing & Branding functions will generally use data summarisation ( Hence functions like Pivot table, charts are important)
- Finance: Dealing and calculation referencing of data across sheets is more important in finance.
- Accounting: Templates, and updating data regularly and simplified approaches are better.
- HR: General use, data sorting, summary, filters, charts, and pivot
Common Basic Excel Interview Questions
So these questions are the bare minimum required by whether you are working in finance, marketing or HR. So make sure to learn this and get this engrained as second nature.
*create a java script quiz
Basic Functions Questions
- What is the correct formula to calculate the total revenue for a company, given the unit price and quantity sold?
a) =SUM(UnitPrice, QuantitySold)
b) =UnitPrice * QuantitySold
c) =AVG(UnitPrice, QuantitySold)
d) =MIN(UnitPrice, QuantitySold)
- Which function is used to find the highest value in a range of cells?
a) MAX
b) MIN
c) AVERAGE
d) COUNT
- How can you copy a formula from one cell to another in Excel?
a) Press Ctrl+C and Ctrl+V
b) Right-click and select “Copy” and “Paste”
c) Drag the fill handle across the desired cells
d) Type “=COPY()” in
- Which Excel function is used to find the average of a range of cells?
a) SUM
b) AVERAGE
c) COUNT
d) MAX
2. What is the correct formula to calculate the standard deviation of a dataset in Excel?
a) =STDEV()
b) =VAR()
c) =AVERAGE()
d) =SUM()
3. How can you round a number to the nearest whole number in Excel?
a) =ROUNDUP()
b) =ROUNDDOWN()
c) =ROUND()
d) =CEILING()
Answers
- b) =UnitPrice * QuantitySold
- a) MAX
- c) Drag the fill handle across the desired cells
- b) AVERAGE
- a) =STDEV()
- c) =ROUND()
Importance of having a basic knowledge of excel for interview
Image you are working as an accountant, how would your typical work activity look like.
- Get financial data from clients
- You need sort them out and categorise them
- Create a tempalte
- Ensure that the data matches with the bank statements.
Now imagine that this kind of work needs to be done daily not for one client but lets say 10 clients per day.
Similarly, let’s say you are working as a digital marketing expert, how would your work look like.
- Download keywords data
- Understand the search volumes, competition, keyword cost
- Decide the best combination of keywords for maximum benefits.
In summary, you cannot function without learning the functions in todays age.
Project Accountant Review on Excel Skills
Being a project accountant in my final year, I was subjected to an interview that had only one Excel test. They concentrated on basic activities such as filtering unique items using ‘remove duplicates’ or applying UNIQUE function in Office 365 among others. SUMIF(S) as well as COUNTIF were some of the common formulas included.
Nevertheless, pivot tables creation ability would definitely make someone’s excel skills noticeable. Equally important is converting raw data into excel tables by use of Ctrl + T and performing lookups with INDEX(MATCH), VLOOKUP or XLOOKUP in Office 365. No need for extensive knowledge about macros or VBA but basics are sufficient.
Advanced Excel Interview Questions
Now let me swiftly shift into some Advance excel functionalities which are critical for your success in in interviews. These are more complicated and demand certain levels of experience not just technically but also common sense.
Let me give you some examples;
Let’s say you had the following data
- Sales data of your sales agents: John, Peter & Simon
- Total leads assigned to them, their various lead stages; hot, cold, converted etc
- Total number of calls done by them on a daily basis.
Now you have an upcoming review meeting, and as a part of the marketing team you need to present some analysis. Let say the analysis is to compare the various lead stages. Which graph is the best suited for such analysis?
Advanced Excel interview questions and answers pdf
I have created a list of downloadable ms excel interview questions and answers pdf. This pdf contains around 300 questions which covers topics in the following areas of ms excel;
• Advanced Formulas
- Pivot Table
- Data Analysis
- Charting
- VBA and Macros
• Data Cleaning and Preparation
- Conditional Formatting
- Power Query and Power Pivot
Deciding the Charts or Functions
Can it be a pie gram? Or column graph, or Tree map or line graph. This is where advance excel skills graduate into. The actual implementation and problem solving. So above you can see three different types of charts but in advance excel, you need to have the ability to choose the most optimum one.
Excel Interview Questions for Specific Roles
As I said at the beginning that different roles will have different uses of excel. Marketing executive will not use what if analysis function in excel. Similarly an investment banker will not be using look up or data look up functions.
So firstly let me delve into excel interview questions related to the following roles
excel interview questions for data analyst
These are some examples of questions which might turn up either in the form of a test or a case study. Here the objective is to check whether you actually have hands on experience with working with data.
- Which Excel feature allows you to filter data based on specified criteria?
a) Conditional Formatting
b) Data Validation
c) Sorting
d) Auto Filter
2. How can you create a bar chart in Excel?
a) Select the data range, click on the “Insert” tab, and choose “Bar Chart.”
b) Use the “SUM” function to calculate the total of a range of cells.
c) Apply conditional formatting to highlight specific data points.
d) Use the “COUNT” function to determine the number of cells that meet a condition.
Answers:
- d) AutoFilter
- a) Select the data range, click on the “Insert” tab, and choose “Bar Chart”
Business Analyst Related Excel Questions
Here are the top 5 Excel questions for a business analyst role, formatted as multiple-choice questions (MCQs) with three options each:
1.How do you use the INDEX and MATCH functions together in Excel?
– a) INDEX returns a value based on a specified row and column, while MATCH looks up values horizontally.
– b) INDEX returns a value from a specified position in a range, while MATCH finds the position of a value within a range.
– c) INDEX creates a summary table, while MATCH sorts data in ascending order.
-Answer:
b) INDEX returns a value from a specified position in a range, while MATCH finds the position of a value within a range.
2. What is the primary benefit of creating a pivot table in Excel?
– a) To remove duplicates from the dataset.
– b) To summarize and analyze data efficiently by organizing it into rows, columns, values, and filters.
– c) To apply conditional formatting to cells.
-Answer:b) To summarize and analyze data efficiently by organizing it into rows, columns, values, and filters.
3. What does the VLOOKUP function do in Excel?
– a) It looks up a value in the first row of a table and returns a value in the same column from a specified row.
– b) It looks up a value in the first column of a table and returns a value in the same row from a specified column.
– c) It calculates the sum of a range of cells based on multiple criteria.
– Answer:b) It looks up a value in the first column of a table and returns a value in the same row from a specified column.
4.How can you remove duplicates from a dataset in Excel?
– a) Use the “Data Validation” feature.
– b) Use the “Remove Duplicates” feature under the “Data” tab.
– c) Use the “Conditional Formatting” feature.
– Answer:
b) Use the “Remove Duplicates” feature under the “Data” tab.
5:What is the purpose of the SUMIFS function in Excel?
– a) To sum values based on a single criterion.
– b) To sum values based on multiple criteria.
– c) To sum all the values in a column.
Answer
b) To sum values based on multiple criteria.
Pivot table interview questions.
- What is the purpose of a Pivot Table in Excel?
a) To create dynamic formulas
b) To sort data in ascending order
c) To summarize and analyze large data sets
d) To perform complex calculations
- How can you change the summary function of a value field in a Pivot Table?
a) Right-click on the value field, select “Value Field Settings,” and choose the desired summary function.
b) Go to the “Analyze” tab, click on “Field Settings,” and select the desired summary function.
c) Double-click on the value field and the summary function options will appear.
d) Select the value field, go to the “Design” tab, and choose the desired summary function from the drop-down menu.
Answers
- c) To summarize and analyze large data sets
- a) Right-click on the value field, select “Value Field Settings,” and choose the desired summary function
Subjective Pivot Table Related Questions
When I used to interview candidates for MIS related roles, my questions used to be broadly covering the below areas. Notice how the subjective questions, can be equally challenging and you cann
Basic Understanding:
- What is a pivot table and how does it help in analyzing data?
- Can you differentiate between a pivot table and an ordinary Excel table?
Creation and Setup:
- Lead me through the steps of creating a pivot table from a dataset.
- How do you add fields to a pivot table, and in what areas can they be placed (Rows, Columns, Values, Filters)?
Customization and Features:
- How can one group data in a pivot table (e.g., by date, month or numerical range)?
- Elaborate on the use of calculated fields and calculated items in a pivot table.
Analysis and Functions:
- In a pivot table, how are different summary functions applied (e.g., SUM, AVERAGE, COUNT)?
- Explain how you would use pivot tables to investigate trends over time.
Filtering and Sorting:
- How can data be filtered within a PivotTable?
- Describe how to sort data in a PivotTable by multiple columns or fields.
Advanced Techniques:
- Could you explain how to create a PivotChart from a PivotTable?
- What are the benefits of doing so?
- When source data changes, how do you refresh the Pivot Table?
Troubleshooting:
- If incorrect data is displayed in the PivotTable, what could be done?
- What would you do if there were duplicate entries or missing values within your PivotTable?
Real-world Scenarios:
- Give an example where you used Pivot Tables to solve complex business problems.
- Describe methods used to ensure accuracy & integrity of information when working with large datasets via Pivot Tables?
- These questions enable an evaluation of a candidate’s pivot table skills and ability to apply them practically.
mis interview questions on excel
Section 8: Conclusion
• Recap of the importance of Excel interview preparation.
• Encouragement to practice with provided questions and resources.
Advanced excel questions and answers: Macros and Automation
- What is the purpose of recording a macro in Excel?
a) To create a shortcut for frequently used Excel commands.
b) To automate repetitive tasks by recording a series of actions.
c) To import data from external sources into an Excel worksheet.
d) To calculate complex formulas and functions automatically.
- Which programming language is primarily used for writing macros in Excel?
a) Python
b) JavaScript
c) VBA (Visual Basic for Applications)
d) C#
- How can you assign a macro to a button in Excel?
a) Right-click on the button and choose “Assign Macro.”
b) Go to the “Developer” tab and click on “Macro Options.”
c) Use the “ALT” key along with a designated letter key combination.
d) Open the “Properties” window and select the macro from the dropdown menu.
Answers
- b) To automate repetitive tasks by recording a series of actions
- c) VBA (Visual Basic for Applications)
- a) Right-click on the button and choose “Assign Macro”
Advance Excel MCQ Questions- Category: Conditional Formatting and Data Validation
- Which Excel feature is used to highlight cells that meet specific criteria?
a) Conditional Formatting
b) Data Validation
c) Sorting
d) Filtering
- How can you create a data validation rule to allow only numeric entries in a cell?
a) Select the cell, go to Data Validation, and choose “Whole Number” as the validation criteria.
b) Select the cell, go to Data Validation, and choose “Text Length” as the validation criteria.
c) Select the cell, go to Data Validation, and choose “Decimal” as the validation criteria.
d) Select the cell, go to Data Validation, and choose “Custom” as the validation criteria.
Answers
- a) Conditional Formatting
- d) Select the cell, go to Data Validation, and choose “Custom” as the validation criteria
Excel Shortcuts Related microsoft excel questions
- Which keyboard shortcut can be used to select the entire Excel worksheet?
a) Ctrl+C
b) Ctrl+A
c) Ctrl+V
d) Ctrl+X
- How can you insert the current date in a cell using a keyboard shortcut?
a) Ctrl+D
b) Ctrl+;
c) Ctrl+Shift+:
d) Ctrl+Shift+D
Answers
- Answer: b) Ctrl+A
- Answer: b) Ctrl+;
Frequently Asked Questions
- What questions can be asked in advanced Excel?
Questions can depend on the kind of role that you are applying for but these are some of the examples;
1.What is Conditional Formatting in Excel?
2. What is the difference between a Module and a Class Module in VBA?
3. What is the difference between Sheets and Worksheets in VBA?
4. How do you create a UserForm in VBA?
5. What is the purpose of the Application object in VBA?
6. How do you handle errors in VBA?
- How to explain vlookup in interview?
V look up is best explained as a function while uses an anchor value to find related data in a data set. The only condition is that the anchor look up value has to be oriented on the left most column of the data set
- How to find duplicates in Excel?
There is a dedicated functionality of remove duplicates function. First select the entire table, click on remove duplicates. Now excel will prompt whether you want to remove only the selected part or or also related data in other columns.
- What are advanced skills in Microsoft Excel?
Generally speaking in my opinion advance skills in Microsoft excel includes creating dashboards, dealing with Macros and VBA. Data visualisation and simulation analysis using data table
Conclusion
Mastering Excel is a like learning an instrument, there are no limits to the creativity that can be done using it. However, the key is practice. The more problems in real life you try to solve using excel and the more exposure you get, the better it is for your skills to improve.
All the best.
