Mostly asked Microsoft Excel Interview questions and answers for Data Analyst
One of the crucial data analytics tools that is widely used in businesses around the world is Microsoft Excel. Because of this, employers today demand that their employees be proficient in Excel. The most frequently asked interview questions and their respective responses from Excel interviews for the role of a data analyst are covered in this article, which will improve your knowledge and assist you in excel interview processes.
So, lets begin with Excel interview questions and answers.
1. What is Data Analytics?
Data Analytics is a combination of 2 words: Data + Analytics
Data means Information.
Analytics means analyzing something based on which interpretation and insights can be given.
So, Data Analytics is the process of analyzing and doing a detailed study on the given information so that meaningful insights can be given which helps in decision making.
2. What are the top 5 tools used for Data Analytics?
The top 5 and most used data analytics tools are:
- Microsoft Excel (Launched by Microsoft in 1985)
- Python and R Programming (Python invented by Guido van Rossum in late 1980’s and R programming designed by Ross Ihaka and Robert Gentleman in early 1990’s)
- Power BI (Officially released by Microsoft in 2015)
- Tableau (Tableau was created by Pat Hanrahan, Christian Chabot, and Chris Stolte in 2003 and Tableau was acquired by Salesforce in 2019)
- SAS (SAS was founded by Jim Goodnight in 1976. SAS is a part of SAS Institute Inc.)
3. What is the extension of macro files? or Can I save macro files in xlsx format?
The macro files should be saved in .xlsm or .xlsb format.
4. What is cell referencing and what is the difference between Absolute and Relative cell referencing?
In Excel, cell referencing is the concept which is used to keep the formulas static or dynamic.
There are 3 types of cell referencing in excel: Absolute, Relative, and Mixed Referencing
Relative Referencing: It is the default referencing in excel where the formula is working dynamically. If formula copied from one cell and pasted to the different cell(s), the relative positions of rows and columns will change accordingly. For example – A1
Absolute Referencing: This referencing is used if the user doesn’t want to change the cell reference in the formula in any way. This will keep the rows and columns value constant. To apply the absolute referencing, dollar sign ($) is used before row and column. For example – $A$1
Mixed Referencing: It is a part of absolute referencing. If the user wants to fix only a column or a row, then will call it mixed referencing. For example – $A1 or A$1
5. In Excel, how to calculate your age in years from the current date?
Most of the users don’t know that DATEDIF is one of the important formulas in excel.
DATEDIF formula is used to calculate the difference between any 2 dates in terms of years, months, days.
Syntax = DATEDIF(Start_Date,End_Date,Interval)
There is a long list of Intervals. Few of them are:
Y = Years
M = Months
D = Days
YM = Years + Months
YD = Years + Days
And many more….
6. What is the difference between Excel 365 and Excel Perpetual (2019,2016,2013….) versions?
An Excel perpetual license is a type of software license that permits a user to use a specific version of a program. Excel is available with a variety of perpetual licenses, including Excel 2019, Excel 2016, Excel 2013, and more. These are license-based programs, which the user can purchase once and use for the rest of his life. Microsoft add new features to the latest versions of Excel that are not present in earlier versions.
Excel 365 works on a subscription-based model. This means that depending on the type of subscription he chose, the user must pay a regular amount either monthly or yearly to use Excel 365. The benefit of using Excel 365 is that all the updated, most recent features are automatically included, so there is no need to purchase a license for the new versions
7. What is the output of the below formula?
Show answer ➜
8. The Function SUMIF can be always replace the function SUMIFS
9. Write Excel Formula to calculate the Average Salary of all employee whose age is more than 50
10. What is the output of the below formula?
11. What is the output of the below formula?