Introduction

Excel spreadsheets have been in use for over 30 years now. Compared to its initial version, Excel has a better interface and many more modern capabilities than today. Spreadsheets are still essential and an excellent data-learning tool. Excel is not the only or best match for all data projects but remains a reliable and cheap analytical tool. It is a fundamental framework for smart data because it improves the understanding of the analytical process. Many sectors and organizations continue to stress the value of Excel skills as they remain an intelligent way of obtaining organizational insights. In this article, we have curated the top 30 basic Excel interview questions to prepare you for your interviews.

Table of Contents

  1. Explain MS Excel in brief.
  2. What do you mean by cells in an Excel sheet?
  3.  Explain what a spreadsheet is. 
  4.  What do you mean by cell address?
  5. Can you format MS Excel cells? If yes, then how?
  6. Can you add comments to a cell?
  7. What is Ribbon, and where does it appear?
  8. How do you freeze panes in Excel?
  9. How do you add a Note to a cell?
  10. Can you protect workbooks in Excel?
  11. How do you create dropdown lists in Excel?
  12. Explain Pivot tables along with their features.
  13. How do you generate Pivot Tables?
  14. What are Pivot charts in MS Excel?
  15. Can you create a pivot table using tables from different worksheets?
  16. Is it possible to see the details of the results displayed in a pivot table?
  17. How are Pivot tables used to filter data?
  18. How to stop automatic sorting in Pivot tables?
  19. What do you understand by Excel functions?
  20. What is the operator precedence of formulas in Excel?
  21. What are the various categories of functions available in Excel?
  22. What is VLOOKUP in Excel?
  23.  How does the VLOOKUP function work?
  24. Can you use VLOOKUP for multiple tables?
  25. How will you obtain the current date in Excel?
  26. What is the ‘What If’ Analysis?
  27. What is the difference between formulas and functions in Excel?
  28. What is the benefit of using formulas in Excel sheets?
  29. How can you disable the automating sorting in Pivot tables?
  30. How is cell reference useful for calculation?

Before we take a look at the MS Excel interview questions, let’s know about MS Excel’s definition.

MS Excel Definition

MS Excel is a commercial table program developed and licensed by Microsoft operating systems for Microsoft Windows and Mac OS. It provides simple calculations, the use of graphical tools, pivot tables, and macros, etc. Spreadsheet applications such as MS Excel use cell selection to organize and manipulate data arranged in rows and columns. You may also display data as diagrams, histograms, and line graphs.

Now that we know the definition of MS Excel, let’s look at the most frequently asked Microsoft Excel interview questions and answers. 

Top 30 Excel Interview Questions

1. Explain MS Excel in brief.

Microsoft Excel is a tablet or a computer program that stores data in a table. Excel is compatible with different operating systems, including Windows, macOS, IOS, and Android. 

MS Excel has some of the main features: 

  • Graphing tools
  • Integrated features (SUM, DATE, COUNTIF)
  • Permit Data Analysis via tables, charts, filters
  • Visual Simple Application Accessibility (VBA) 
  • Worksheet process and versatile workbook 
  • Enables fast validation of data

2. What do you mean by cells in an Excel sheet?

The junction of a column with a row to insert the information is called a cell. In a single sheet of excel, there are a total of 1,048,576 x 16,384 cells.

3. Explain what a spreadsheet is.

Spreadsheets are cell compositions that assist you in data management. There may be more than one worksheet in one workbook. You can see all the sheets and the names you gave them at the bottom of the window. Look at the following picture:

4. What do you mean by cell address?

The cell address of an Excel sheet is the address obtained by combining the row-number and the column-alphabet. A different cell address is given for every cell in an MS Excel sheet.

5. Can you format MS Excel cells? If yes, then how?

Yes, you can format MS Excel cells. You may use the commands in the Font category of the Home tab to format these cells. You can see the following options when you open the Font window:

6. Can you add comments to a cell?

You can add notes, yes. Select the cell, right-click on it, then select the New Comment option to add comments to the cell. All those people with access to the Excel sheet can see these remarks. 

Comments for many reasons are used: 

  • To explain the function of cells
  • To clarify a cell formula
  • To leave notes about a cell for other users

7. What is Ribbon, and where does it appear?

The Ribbon is essentially your main Excel interface, and you can find it at the top of the Excel window. It provides users with direct access to many of the main commands. It includes several tabs, including Paper, House, Display, Insert, etc. You can also modify the ribbon according to your needs. Right-click on the Ribbon to change and choose the option “Customize Ribbon.” The following window will appear:

8. How do you freeze panes in Excel?

MS Excel will freeze panels that allow you to see rows and column headings even while scrolling long distances on the document. Follow the steps given to freeze panes in Excel: 

  • Choose the columns and rows you would like to freeze 
  • Then, in the View tab, pick Freeze Pane 
  • Here you can see three options for freezing rows and columns selectively, as shown in the following picture:

9. How do you add a Note to a cell?

To add a Note, right-click on the cell and pick it. Then select New Note and type in any note you like. If you want to delete the Note, follow the same process and choose the option Delete Note. The Notes appear in the top right corner of the cell with a red triangle.

10. Can you protect workbooks in Excel?

Yes, you can safeguard workbooks. For this, Excel offers three options: 

  • You can access workbooks with passwords. 
  • You may prevent adding, removing, hiding, or unhiding sheets. 
  • Excel offers a defense against changing window sizes or positions.

11. How do you create dropdown lists in Excel?

Follow the steps given to build drop-down lists: 

  • Click on the Ribbon Data tab. 
  • Click on Data Validation from the Data Tools Community. 
  • Setup>Allow>List to navigate 
  • Pick the array of the source list.

12. Explain Pivot tables along with their features.

Pivot tables are statistical tables containing tables with detailed data. The summary can depend on any field like revenue, averages, amounts, etc., which gets efficiently and intelligently described in the pivot table. 

Features: 

The following are some of the attributes of Excel Pivot Tables: 

  • Enable exact data to be displayed for analysis 
  • Offer different viewing angles 
  • Let you concentrate on important information 
  • Data comparison is very realistic 
  • Various trends, link, data trend, etc., may be recognized in pivot tables 
  • You should create immediate data 
  • Reports correct
  • Serve the Pivot charts basis

13. How do you generate Pivot Tables?

You need first to prepare the data in a tabular format to construct a pivot table. Bear in mind during data preparation the following points: 

  • Set the data into columns and rows.
  • For each column, the first row should have a single heading. 
  • Columns should contain only one data form.
  • For a single recording, only Rows must have data.
  • No rows should be empty.
  • Columns should not be exclusively empty.
  • Data should be separated from other data present in the sheet to establish a pivot table.

14. What are Pivot charts in MS Excel?

MS Excel charts are tools to display the data in different ways. These diagrams may include Bar, Pie, Field, Line, Doughnut, etc.

15. Can you create a pivot table using tables from different worksheets?

You could also build a pivot table for the table from separate sheets if the two sheets are from the same workbook. 

16. Is it possible to see the details of the results displayed in a pivot table?

Yes, you can get a detailed view of the results displayed by the pivot tables in Excel. To see the results, double click on the value, and you will see that a new sheet with a new table has come up with details of the factors which led to this specific outcome.

17. How are Pivot tables used to filter data?

You can filter data according to your requirements with Excel Pivot tables. Place the field on which you want the data to be filtered. Then open the drop-down list of the field you put in the Filter area from the pivot table and choose your line.

18. How to stop automatic sorting in Pivot tables?

The data in Pivot Tables get automatically sorted by Excel. If you don’t want Excel, open the Row Labels or Column Labels drop-down menu and select More Sort Options. You can see the opening of the Type dialog box. Click Additional Options and automatically unselect the Sort option.

19. What do you understand by Excel functions?

In Excel, functions are used to carry out those tasks. Excel has several integrated features that are used to measure the outcomes of different formulas, helping to save time. Furthermore, these functions allow the execution of formulas that were difficult to write down manually quite simple.

20. What is the operator precedence of formulas in Excel?

Excel formulas are carried out under the BODMAS law. As many of us know, BODMAS reflects the addition and subtraction of the Brackets Order Division. That implies, the brackets are performed first in every formula (if present) and then multiply, divide, etc. The following picture provides an example of this:

As you can see, the performance is 36, which means that 5+7 gets added first and three times more. If the brackets are not defined, the result will first be multiplied by 3 to 5 and added 5, i.e., 15 + 7, to 22.

21. What are the various categories of functions available in Excel?

The Excel function categories are as follows:

22. What is VLOOKUP in Excel?

VLOOKUP is an Excel function used for researching and generating data from a particular set. V is Vertical in VLOOKUP, and we should organize data vertically to use this feature. VLOOKUP is incredibly useful if we need to seek a certain amount of data from a large number of data.

23. How does the VLOOKUP function work?

In Excel, the VLOOKUP function has a lookup value, and in the most left column begins to look for it. If the value of that search first occurs, VLOOKUP will step right, i.e., in the search value row. It continues until the user’s stated column number is returned. This function matches correct and estimated search values. The default match, however, is approximate.

Here,

  • lookup_value: The value you want to check for is the lookup value 
  • table_index: Table index is the set of data to be taken from 
  • col_index_num: col index num defines the column you want to extract the value from 
  • range_lookup: Logical value, i.e., TRUE or FALSE, is the range lookup (TRUE will find the closest match; FALSE checks for exact match)

24. Can you use VLOOKUP for multiple tables?

Yes, for many tables, you can also use VLOOKUP. If you have two search tables, build named fields for each table and use the IF function to choose from each table based on a specific condition.

25. How will you obtain the current date in Excel?

The TODAY function obtains the current date. The current date gets returned in the date format of the MS Excel.

SYNTAX: TODAY()

Example:

26. What is the ‘What If’ Analysis?

What if Analysis is the method used to alter one or more of the cellular formulas to see how it impacts the worksheet outcome of such formulas. Excel offers three kinds of What if methods for Analysis:

  1. Scenarios: Scenarios and data tables provide a collection of inputs for future outcomes. Many variables can function with scenarios, but the maximum input values are 32.
  1. Goal Seek: Goal Seek takes outputs and decides potential inputs to the same, contrary to Scenarios and Data Tables.
  1. Data Tables: Data tables operate for just one or two variables, but they can accept several different values for all of these variables.

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

Formulas are specified by the consumer who uses some results to measure them; either simple or complex formulas can consist of values, functions, names, etc. 

On the other hand, a Function is a built-in code that gets employed in some actions. Excel has many integrated functions like SUM, PRODUCT, IF, SUMIF, COUNT, and so forth.

28. What is the benefit of using formulas in Excel sheets?

The Excel sheet calculation not only lets you track the final “sum up” of the number but automatically calculates the number replaced by a new digit or number. The complex calculations are made simple with Excel sheets, such as the payroll deduction or the student averaging results.

29. How can you disable the automating sorting in Pivot tables?

Automated sorting of pivot tables to be disabled by:

30. How is cell reference useful for calculation?

For calculation, the cell reference prevents writing the data repeatedly. You must direct Excel to that data’s specific position when you write any formula for a particular function. The cell reference point is called this spot. Each time the cell adds a new value, the cell is determined based on the reference cell’s formulation.

Conclusion

In this article, we discussed the top 30 MS Excel interview questions and answers for the role of Data Scientist that are asked frequently in many companies. We hope these questions and answers from Excel interviews can help you get a better job in the Data Science domain.


If you wish to enhance your MS Excel skills and become proficient in data analysis, our Full Stack Data Science Program will surely help you. This 6 months online Full Stack Data Science Program is the 1st online Data Science course aligned to the SSC NASSCOM curriculum.

ALSO READ

SHARE