UNext Editorial Team

Share

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**

**Explain MS Excel in brief.****What do you mean by cells in an Excel sheet?****Explain what a spreadsheet is.****What do you mean by cell address?****Can you format MS Excel cells? If yes, then how?****Can you add comments to a cell?****What is Ribbon, and where does it appear?****How do you freeze panes in Excel?****How do you add a Note to a cell?****Can you protect workbooks in Excel?****How do you create dropdown lists in Excel?****Explain Pivot tables along with their features.****How do you generate Pivot Tables?****What are Pivot charts in MS Excel?****Can you create a pivot table using tables from different worksheets?****Is it possible to see the details of the results displayed in a pivot table?****How are Pivot tables used to filter data?****How to stop automatic sorting in Pivot tables?****What do you understand by Excel functions?****What is the operator precedence of formulas in Excel?****What are the various categories of functions available in Excel?****What is VLOOKUP in Excel?****How does the VLOOKUP function work?****Can you use VLOOKUP for multiple tables?****How will you obtain the current date in Excel?****What is the What If Analysis?****What is the difference between formulas and functions in Excel?****What is the benefit of using formulas in Excel sheets?****How can you disable the automating sorting in Pivot tables?****How is cell reference useful for calculation?****What does the function “AND” in Excel do?****What is the use of a Macro in Excel?****What are the two macro languages in Microsoft Excel?****In MS-Excel, how do you apply the same formatting to every sheet in a workbook?**

Before we take a look at the MS Excel interview questions, let’s know about MS Excel’s 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.

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

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.

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:

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.

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:

You can add notes, yes. Select the cell, right-click on it, and 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

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

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:

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.

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.

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.

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

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.

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

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

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.

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.

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 dialogue box. Click Additional Options and automatically unselect the Sort option.

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.

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.

The Excel function categories are as follows:

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.

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)

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.

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

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:

**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.**Goal Seek:**Goal Seek takes outputs and decides potential inputs to the same, contrary to Scenarios and Data Tables.**Data Tables:**Data tables operate for just one or two variables, but they can accept several different values for all of these variables.

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.

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.

Automated sorting of pivot tables to be disabled by:

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.

In Excel, the AND Function is a logical function that tests/evaluate multiple conditions and returns “true” or “false” depending on whether or not they are met. The AND function is defined as “=AND(logical1,[logical2]…),” where “logical1” is the first condition to be evaluated.

If all of the logical values or conditions are true, then it returns “true.” If any of the logical values or conditions are evaluated as false, it returns “false.” Depending on the circumstances and the requirement, it may have more logical values. Non-zero values are treated as “true” when evaluating numbers; zero is considered false.

The use of macros allows you to perform a series of tasks repeatedly, and Macros can be used to automate repetitive tasks and instructions.

VBA and XLM (Visual Basic Applications). XLM was used in earlier versions of Excel. VBA was introduced in Excel 5 and is now widely used.

Right-click the ‘Worksheet tab’ and select ‘Select All Sheets.’ Any formatting you do now will be applied to the entire workbook, and select only the sheets that require formatting to apply to a specific group of sheets.

These **Excel**** interview questions **will help clear all your doubts and make you better prepared to crack the interview.

In this article, we discussed the top MS Excel interview questions and answers for the role of Data Scientist that are asked frequently in many companies.

These **Excel interview questions** will help clear all your doubts and make you better prepared to crack the interview. We hope these questions and answers from Excel interviews can help you get a great job in the Data Science domain.

If you wish to enhance your MS Excel skills and become proficient in data analysis, our P**G Certificate Program in Data Science and Machine Learning** is perfect for Data enthusiasts like you.

Want To Interact With Our Domain Experts LIVE?