RnewYear2022 RnewYear2022

- SQL Server Blogs -

A Complete Guide On The Top 50 Excel Interview Questions & Answers.

Introduction

Do you want to excel in “MS Excel”? 

With nearly 750  million users worldwide, MS Excel is the perfect tool of choice for presenting data in a structured way. Since 1985 it has been catering to the needs of its users smoothly and has been proclaimed as “Microsoft’s most important consumer product”. 

What makes MS Excel the ultimate tool of choice for its users?

Excel is best suited to store, analyze and report large amounts of data and is used by professionals to manage datasets. Excel has strong computing powers that makes it fit to be used for financial purposes. Excel is used to automate processes in an I.T team. A certified I.T professional knows how to keep the data safe and well-maintained in an Excel file. This can be better achieved through enrolling into our Top I.T online certification courses which are devised to turn you into a pro I.T professional from every aspect.

Apart from Data Science certification courses, a basic knowledge of MS Excel is required to crack your interview. But what are the Excel interview questions that can be asked? This blog will unfold all the important MS Excel interview questions for beginners as well as for experienced candidates.

Let’s start exploring some of the sorted Excel interview questions that will surely help you in your future ahead.

Excel interview questions and answers for Beginners

Before heading for your interview, learn about these professional tips to crack your interview in your dream company.

Q1. Explain MS Excel briefly.

Answer-  MS Excel is a spreadsheet that allows storage of data in the form of a table. It was developed by Microsoft in 1985 and can be operated on any operating system like Windows, MAC OS, or Android. It has built-in functions like SUM, DATE, etc that makes calculation easier. It allows eady data validation and allows data analysis in the form of pie charts or bar graphs.

This question forms one of the most asked MS Excel interview questions by an interviewer.

Q2. What are cells in MS Excel?

Answer- Cells are areas that are formed by the intersection of a row and a column in MS Excel. Cells are specifically made to store and display information. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet. 

This is one of the very basic MS Excel interview questions for beginners that should be answered comfortably.

Q3. What is a cell address?

Answer- A cell address is used to find a particular cell on the spreadsheet. It is denoted by a combination of the respective column letter and a row number. For example-

Q4. What is the difference between Relative cell referencing and Absolute cell referencing?

Answer- 

Relative Cell Referencing

Absolute Cell Referencing

i) In this type of referencing, there is a change when copying from one cell to another with respect to the cell address.

i) There is no change when a cell is copied irrespective of its cell address.

ii) It is present by default.

ii) It has to be added by a dollar sign before and after the column and row address.

iii)

iii)

These types of MS Excel interview questions can be better explained with the help of an example that draws attention of the interviewer. 

Nourish your I.T skills by reading our blog on “Top I.T skills that are in demand in 2022.”

Q5. How can you add cells?

Answer- To add a new cell in Excel, select the cell where you want to insert it and then select the ‘Insert’ option. This can be better understood with the help of an example-

Q6. How can you format MS Excel cells?

Answer- In order to format MS Excel cells, you can use the commands present in the Font group of the Home tab. Some of them are- Name, Alignment, Font, Border, Fill and Protection.

These are non-technical MS Excel interview questions which do not require any example.

Q7. What is the difference between a Formula and a function in MS Excel?

Answer- 

Formula

Function

i) Formula is like an equation in Excel. It can be any type of calculation.

i) Function is a pre-defined calculation which is already built in the excel.

ii) It is time-consuming because of manual typing.

ii) It is less time-consuming and more comfortable while working with functions.

Q8. How to add comments in excel?

Answer- To add comments to a cell, select the cell, right-click on it and then select the New Comment option. These comments will be visible to all those people who have access to the Excel sheet.

In these types of MS Excel interview questions, you may or may not illustrate an example. 

Do you know JanBask Training offers one of the most professional I.T online training programs to its aspirants? Enroll now!

Q9. What is Ribbon?

Answer- The Ribbon is basically your key interface with Excel and it appears at the top of the Excel window. It allows users to access many of the most important commands directly and consists of many tabs such as File, Home, View, Insert, etc.

This is one of the most frequently asked Excel interview questions for freshers. 

Q10. How do you freeze panes in excel?

Answer- To freeze panes in MS Excel, follow these steps-

Select the rows and columns you wish to freeze.

Select Freeze pane in the View tab.

Select from the three given options- Freeze panes, Freeze Top Row and Freeze First Column.

Q11. What is the order of operations used while evaluating a Formula in Excel?

Answer- The order of operations in Excel is known as PEDMAS and it is in this way-

  • Parentheses
  • Exponentiation.
  • Division/ multiplication
  • Addition
  • Subtraction

Q12. What is the difference between Count, Counta and Counblank?

Answer- The Count function is often used in Excel and it has two variations- Counta and CountBlank. The Count function counts the number of cells that contain numeric values. 

Counta- It counts the number of cells that contain a form of content. These include string values, characters and numeric values. 

Countblank- It counts the number of blank cells only. 

Q13. How do you create hyperlink in Excel?

Answer- To create a hyperlink in MS Excel, the shortcut key is Ctrl+K. After this, ‘Insert Hyperlink’ dialog box appears. Enter the address and the text to be displayed. 

These types of Excel interview questions are technical-based and expose the technical skills of candidates. 

Q14. How do you add a note in Excel?

Answer- To add a Note, select the cell and right-click on the same. Select the New Note option and type in any note that you wish to. In case you want to delete the Note, follow the same procedure and select the Delete Note option. Notes are indicated by a red triangle at the top-right corner of the cell. 

Q15. How do you protect a cell from being copied?

Answer- To protect a cell in a sheet from being copied, follow the steps-

i) Select the cells you want to protect.

ii) Open the Font window from the Home tab.

iii) Select Protection from the Protection pane and check the Hidden Box.

iv) Click on the Review tab and select the Protect Sheet option. 

v) Specify a password for security.

Hey! There's good news for all freshers out there! Know the secret to grab an I.T job without any experience. 

Q16. What are macros?

Answer- Macros are a set of actions that allow you to run the operation as many times as you want. If you repeat a task in Excel, you can record a macro to perform the task automatically. 

See the diagram below to answer better in your Excel interview questions.

Q17. How do you create named Ranges ?

Answer- To create named ranges, follow the given steps:

  • Select the area to which you intend to give a name
  • From Ribbon, select Formulas
  • Click on Define Name from Defined Names group option.
  • Give any name of your choice.

Do you know that MS Excel aids Data Science by allowing users to create named ranges? Get more details on Data Science by enrolling in our Data Science certification course now! 

Q18. How do you create Drop down lists in Excel?

Answer- To create dropdown lists, follow the given steps:

  • Click on Data tab present in the Ribbon
  • From the Data Tools group, click on Data Validation
  • Navigate to Settings>Allow>List
  • Select the source list array.

Q19. What is a Pivot Table?

Answer- A Pivot table is an interactive way to brief your data. It is a statistical table that condenses data with large amounts of information. It analyzes numerical data and answers unanticipated queries about your data.

This forms one of the most asked questions in Excel interview questions.

Q20. What are the features of a Pivot table?

Answer- A Pivot table allows the display of the data to be analyzed, providing various angles to the data. With a Pivot table, comparison of data can be done quickly with accurate reports. Pivot tables can detect different patterns, and relationships and allow you to focus on important details.

Q21. How do you create Pivot tables?

Answer- To create a Pivot table, follow these steps-

i) Arrange the data in a tabular form

ii) There should be a unique heading for each of the columns in the first row.

iii) The columns should contain only one type of data.

iv) There should not be any blank row or column.

v) The data in the Pivot table should not match with other data in the sheet.

Q22. What are Pivot charts in Excel?

Answer- A Pivot chart adds value and category instead of choosing the chart wizard. It is a visual representation of data which allows you to analyze data using various types of graphs and layouts. It is usually needed for business presentations where there is a need for large amounts of data. 

A Pivot chart in Excel looks like this-

Data Scientists work with data by creating pivot charts in Excel. Read out this interesting blog to find out “What does a Data Scientist exactly do?”

Q23. What is the difference between VLOOKUP and LOOKUP function?

Answer- 

VLOOKUP

LOOKUP

i) This function lets the user look for a value in the leftmost column of a table and returns the value from left to right.

i) This function allows the user to look for data in a row/column and returns the data in another row/column.

ii) It is not so easy to use.

ii) It is easier and can replace the VLOOKUP function.

Q24. What is Data Validation? 

Answer- Data Validation in excel is a feature that allows you to control the type of data entered in the sheet. It restricts the type of values that a user can enter into a particular cell. Data Validation is present in the Data Tools group on the Data tab.

Q25. How do you calculate your age from today’s date?

Answer- For this, use the YEARFRAC function and put the start and end date. After that press Enter.

 

Q26. What are the different Functions available in Excel?

Answer- The different Functions available in Excel are as follows-

DAY, DATE, MONTH- Date and Time.

AND, OR, NOT, IF, TRUE, FALSE- Logical

AVERAGE, COUNT, COUNTIF, MAX, MIN- Statistical

SUM, SUMIF, PRODUCT, SIN, COS- Math and Trig

Q27. How do you calculate the product of two numbers?

Answer- Product in Excel can be calculated by the PRODUCT function. Type any two numbers and type “=” PRODUCT and put the two numbers. The result will be displayed as shown below-

 

Q28. How do you calculate the% of two numbers?

Answer- Here are the steps followed in order to obtain the result:

  • Select the cell destination cell to display the percentage
  • Then, type a “=” sign
  • Type in A1/ A2 then hit the Enter key
  • Click on the Home tab, select % symbol from the numbers group.

See the image below to understand it-

Such types of technical interview questions for Excel skills detect the technical skills of a candidate. If you want to start an I.T career, we can guide you through our blog on “I.T Career pathway.”

Q29.How do you find the average of numbers?

Answer- To find the average of more than one number, Select AVERAGE formula from More Functions.

Q30. How will you fetch the current date in Excel?

Answer- For this, you need to use the TODAY function from the Date and Time option. This function will return the current date in the MS Excel date format.

Q31. How to add filters in excel?

Answer- To add a filter, select a specific cell. Then select Data> Filter. After that,  select the column header arrow and enter the Filter Criteria and press OK.

Alert! Get to know the most-wanted skills by recruiters in 2022 and nurture your skills accordingly.

Q32. How do you find the sum of two numbers in Excel?

Answer- To calculate the sum of two numbers, select SUM function from Math & Trig option.

Q33. How many report formats are there in excel?

Answer- There are three report formats in Excel. They are- Compact report format, Tabular report format and Outline report format. These are available in the Report layout menu of the Excel sheet.

Q34. How does the IF function work in Excel?

Answer- The IF function in Excel performs a logical test. It allows you to make logical comparisons between a value and what you expect. An IF function can have two results- First, if your comparison is True and second, if your comparison is False. 

Q35. How does the index-match function work in Excel?

Answer- This is a combination of two functions- INDEX and MATCH. The INDEX function is used to return an item from a specific position in the list, while the MATCH function is used to return the position of a value to the list. Here’s how an index-match function works in excel-


Data Scientists proactively work to bring data from different sources and analyze them. Check out what are the roles and responsibilities of a Data Scientist and their objectives. 

Q36. How do you find duplicate values in a table using Conditional Formatting?

Answer- To find duplicate values in a table through Conditional Formatting, go to the Home tab and select Conditional Formatting. Then select, Highlight Cells Rules and then select Duplicate Values.

These types of Excel interview questions are better explained through the use of an example.

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

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

Q38. How can you protect workbooks in excel?

Answer- To protect workbooks in excel, you can adopt any of these tree options-

i) By setting a password to open workbooks.

ii) By protecting sheets from being added or deleted.

iii) By protecting Window sizes from changing.

Q39. What are Relative cell addresses in Excel?

Answer- Relative cell addresses in Excel are the default cell references. It is the combination of a column name and a  row number without the dollar sign. Whenever you copy formulas in Excel, the addresses of the cells get modified. This system is called Relative cell address

Q40. What are the wildcards available in Excel?

Answer- In Excel, there are three wildcards that work only with text data.

i) Asterisk (*)- Refers to any number of characters.

ii) Question mark (?)- Represents one single character.

iii) Tilde (~)- Used for identifying any wildcard character in the text.

Learn and grow with our popular Janbask Training e-learning community and forum.

Advanced Excel Interview Questions and Answers

Q41. What is the What-if Analysis in Excel?

Answer- The What-if Analysis is the process of changing the values in cells to see how these changes affect the results in Excel. It is a powerful tool for carrying out complex mathematical calculations and data experimentation. There are three kinds of What-if Analysis in Excel. They are- Scenarios, Goal Seek and Data Tables. These take the input values of various data and display the results.

This is one of the most common MS excel interview questions and answers for experienced candidates.

Q42. What is the difference between a Function and Subroutine in Excel?

Answer- 

Function

Subroutine

i) It is responsible for returning the value of a task.

i) It does not return the value of a task it is performing.

ii) They are used in a fixed way in spreadsheets or formulas.

ii) They are not used directly in spreadsheets or formulas.

iii) They are used to carry out repetitive tasks.

iii) In this case, ussr are required to insert a value before the output.

iv) They are called by a variable.

iv) they can be recalled anywhere.

Q43. How do you debug a VBA code in excel?

 Answer-  To debug a code, use the F8 key. You can also create  a breakpoint from where you want the termination of execution. 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.

Q44. Compose a VBA capacity to figure the space of a square shape.

Answer- 

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

In the event that IsMissing(Width) 

Region = Length * Length 

Else 

Region = Length * Width 

End If 

The increased dependence of the organizations on Big Data has led to the increased demand for certified Data Scientists professionals whose salaries remain high due to the gap in demand and supply. Check this blog to know more about a Data Scientist’s salary based on different factors.

Q45. Explain what are Loops in VBA.

Answer- Loops are one of the most powerful programming tools in VBA. They allow users to repeat the same code block until a specific point or condition is attained. In VBA, Loops allow you to go through a set of objects/ values multiple times. 

Q46. How do you run a macro when you open a workbook in VBA?

Answer- Open the VBA editor with the shortcut Alt+F11. Then, on the left side of the project explorer, double-click “ThisWorkbook”. Input the code “Private Sub Workbook_Open()” and hit the enter key. Next, write the recorded code between “Private Sub Workbook_Open()” and “End Sub”. After you close the VBA editor, save your workbook in Excel Macro-Enabled Workbook (XLSM) format.

Q47. What is ADO?

Answer-  ADO stands for Activex Data Objects and is used to access data between client and server. This is Microsoft’s universal data-access technology. It can be used to extract any type of data from the source. There are three versions of ADO. They are- 2.1, 2.5 and 2.6.

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

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

    If IsMissing(Width) Then

        Area = Length * Length

    Else

        Area = Length * Width

    End If

End Function

Q49. How do you find the last row in VBA?

Answer- To find the last row, use the instructions given accordingly:

Sub FindingLastRow()

Dim lastRow As Long

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

MsgBox (lastRow)

End Sub

Q50. What is the difference between ThisWorkbook and ActiveWorkbook in VBA?

Answer- 

ThisWorkbook

ActiveWorkbook

i) Indicates the name of the workbook from where the code is running.

i) This is the workbook that is presently active from the other open workbooks.

When it comes to last minute interview tips, there are certain expert tips that will enable you to ace your interview.

Conclusion

Now that you have an overview of the MS Excel interview questions, you easily prepare yourself for the Excel interview questions and answers. Here we have enumerated the top 50 excel interview questions for freshers as well as for experienced candidates. Regular practicing of Excel and preparing yourself through these Excel interview questions will help you to crack your upcoming interview call. 

Excel is an intrinsic part of an I.T professional. To kickstart your I.T career,  enroll in our professional certification courses that will cover all the concepts from the basic till the advanced levels. 

We hope you find this blog useful. For any query, leave a comment and we will get back to you.

JanBask Training team wishes you good luck for your interview.

FAQs

Q1. How does Excel help in analyzing data?

Ans- MS Excel is one of the most commonly used tools for data analysis. Most data analysts rely on Pivot tables for the statistical data.  Excel helps in faster and simpler data analysis  and provides high-level visual summaries, trends and patterns.

Q2. Is Excel necessary for Data Science?

Ans- Excel is necessary for data scientists and is considered as a base structure for intelligent data. Excel is a smart way to extract insights from data. The basic data analysis tasks are performed by Excel. Excel is used by beginners as well as by experienced Data Scientists in their workplace.

Q3. How can I learn Data Science?

Ans- You can learn Data Science through our online Data Science certification course that will give you practical learnings from basic to advanced around Data Science methods with R & Python, machine learning, AI, deep learning, Big Data Hadoop, and Tableau Data Visualization in complete depth. Our Data Science certification training lets you master the concepts of Data Science based real-life industry cases increasing your job market value. 

Q4. What skills will I learn in a Data Science course?

Ans-   In the Data Scientist course, you will learn R programming, Python, machine learning, deep learning, regression analysis, data architecture, visualization techniques, risk analysis, process improvement, systems engineering, and many more concepts that are important for the Data Scientists certifications exam and to be an industry-ready professional.

Q5. What is the objective of the Data Science course?

Ans- To provide a virtually accessible Data Science course that is similar to the offline classroom session and redeems you from the burden of traveling exhaustively - to help you grasp the Data Scientist demande skills/knowledge around R programming, Python, Data mining, machine learning with R, data visualizations, etc. via live coding with mentors and real industry case scenario assignments.

Q6. Is there any difference between Data Science and Data Analytics?

Ans-  Data Science is a broad field that deals with extracting meaningful information through algorithms, statistics and scientific methods. It is related to Big Data and is considered to be one of the most in-demand skills. Data Analytics is a branch of Data Science and is used to get conclusions by processing raw data. 

Q7. What are the important skills required to become a Data Scientist?

Ans- To pursue & apply the Data Science/Scientist job, you are required to specialize in the following technical skills:

  • Great programming skills around Python, R, and SQL
  • Understanding of statistics
  • Data extraction, transformation & loading
  • Data wrangling
  • Machine learning & its algorithms
  • Advanced machine learning - deep learning
  • Data visualization & communications

Q8. Which Data Science certifications are in demand?
Ans- The few in-demand Data Science Certifications which you can think of achieving after completing our intensive & real-industry assignments based Data Science training are:

  • Apache Hadoop certifications
  • Certified Health Data Analyst
  • Data Science EMC Proven Professional
  • IBM Cognos Business Intelligence certifications
  • Microsoft Certified Solutions Associate
  • Microsoft Certified Solutions Expert
  • Oracle Database 11g Administrator Certified Associate

Q9. What can I expect after the certification program?

Ans- After completing our Data science Certification training, you will end up having:

  • Competent skills & knowledge that is ready to be applied over the certification exam of Data Science.
  • Intellectual ways to penetrate in the in-demand job roles.
  • A Data Scientist Training Certificate by a highly acclaimed name in the e-learning world “JanBask Training” - marking as a proof of the successful completion of your learning-filled training journey

Q10. Do you help with job placements?

Ans- At JanBask Training, with our Data Science training, we keep you exposed to the real-time industry’s job market scenario with available facts & numerical figures. We serve you knowledge on the actual scenarios of the data concentric industries/companies, their peak hiring hours, their hiring pattern, and everything that could support you in achieving those great job opportunities real quick. Along with certification examination preparation, we deliver you smart ways on how you can be a part of the job market with ease.


 


     user

    Jyotika Prasad

    Through market research and a deep understanding of products and services, Jyotika has been translating complex product information into simple, polished, and engaging content for Janbask Training.


Comments

  • C

    Corbin Stewart

    I want to know how to address these Excel interview questions and answers and this post helped me in understanding..

     Reply
    • logo16

      JanbaskTraining

      Hi, Thank you for reaching out to us with your query. Drop us your email id here and we will get back to you shortly!

  • K

    Kash Sanchez

    What a nice informative blog, a must-read for people who want to crack Excel interviews.

     Reply
    • logo16

      JanbaskTraining

      Glad you found this useful! For more such insights on your favorite topics, do check out JanBask Blogs and keep learning with us!

  • K

    Kameron Rogers

    Hey, is there any separate guide that you can help me to get prepared for these certification courses?

     Reply
    • logo16

      JanbaskTraining

      Hi, Thank you for reaching out to us with your query. Drop us your email id here and we will get back to you shortly!

  • A

    Andre Reed

    Earlier I thought that in a few places, the sample answers for Excel interview questions and answers were not properly explained and after reading this post I got to know the different factors.

     Reply
    • logo16

      JanbaskTraining

      Glad you found this useful! For more such insights on your favorite topics, do check out JanBask Blogs and keep learning with us!

  • G

    Gunner Cook

    Can anyone from a commerce background pursue a Data Science course? If yes, then whom to contact?

     Reply
    • logo16

      JanbaskTraining

      Hi, Thank you for reaching out to us with your query. Drop us your email id here and we will get back to you shortly!

  • J

    Jake Morgan

    I want to explore a few best courses for career growth, but confused about which one is better, I want to consult a Janbask consultant on this.

     Reply
    • logo16

      JanbaskTraining

      Hi, Thank you for reaching out to us with your query. Drop us your email id here and we will get back to you shortly!

Related Courses

Trending Courses

salesforce

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53
salesforce

Upcoming Class

5 days 08 Jun 2023

salesforce

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
salesforce

Upcoming Class

0 day 03 Jun 2023

salesforce

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
salesforce

Upcoming Class

6 days 09 Jun 2023

salesforce

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
salesforce

Upcoming Class

6 days 09 Jun 2023

salesforce

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
salesforce

Upcoming Class

6 days 09 Jun 2023

salesforce

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
salesforce

Upcoming Class

-1 day 02 Jun 2023

salesforce

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
salesforce

Upcoming Class

-1 day 02 Jun 2023

salesforce

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
salesforce

Upcoming Class

6 days 09 Jun 2023

salesforce

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
salesforce

Upcoming Class

0 day 03 Jun 2023

salesforce

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
salesforce

Upcoming Class

14 days 17 Jun 2023

salesforce

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
salesforce

Upcoming Class

27 days 30 Jun 2023

salesforce

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
salesforce

Upcoming Class

6 days 09 Jun 2023

Interviews