Top 50 Microsoft Excel Interview Questions
MS Excel is the most widely used spreadsheet application in use. The analytical and calculus capabilities of this application have made this application extremely popular with every professional in the corporate or at a mid-scale level in today’s time.
Since its initial launch in 1985 MS Excel has come a long way and with constant updates rolling out each quarter. It has become one of the most loved applications with a large community fanbase of all time.
Having a keen understanding and implication of this application holds the key to cracking multiple interviews where the use of MS Excel is highly desired and recommended.
Below are a set of Microsoft Excel Interview Questions that might be asked during an interview about the implications of MS Excel:-
It is an electronic spreadsheet application launched by Microsoft in the year 1985. It authorizes the user to store, manage, analyze, and influence data with the use of functions and formulas using the spreadsheet system broken into rows and columns. It is also compatible with other databases making it an all-around and time-saving application.
The crossway where a row and column intersect on a worksheet is referred to as a cell. The rectangular spaces in the excel sheet which take in data are referred to as a cell. There are about 10,48,576 rows x 16,384 columns = 17,17,98,69,184 Arab cells in one worksheet.
Spreadsheets are also known as Worksheet are a collection of cells that help in data management. One Workbook may comprise of several worksheets. Worksheets may be given a name to distinguish when searching for some particular data. These may be visible at the bottom of the sheet. Detailed introduction of Microsoft Excel
The denomination obtained when you click a random cell in the worksheet, which denotes the letter of the column number and the number of the row respectively is referred to as a cell address or cell number. E.g.
To do so, just right click on the cell where you wish to add the cell and the following dialog box shall pop up
In this dialog box select the Insert option and the following dialog box shall pop up, in which you can select your preference of adding the cell and click ‘OK’ to confirm your choice.
To format any cell, the user can right-click on the cell and would be shown a drop-down menu. Similar to the one shown in the picture below
From the drop-down menu, the user needs to choose the ‘Format Cells’ option and would be displayed a dialog box as shown below
The first formatting option would be the ‘Number’ tab which allows the user to portray numbers in special types like currency, date, time, percentage, fraction, etc.
Which allows the operator to align, text control, and change the direction in which the text was maybe written.
which permits the user to set the default printing font style and the one that is displayed on the screen.
Permits the user to change or modify the borders of the cell to colored, changed, or maybe even removed permanently.
The ‘Fill’ tab
Enables the user to choose distinct colors and styles to fill up the cells.
Finally, last but not least the ‘Protection’ tab allows the operator to lock or hide any chosen cell.
The comments would be visible to all the people with whom the file may be shared.
The procedure for doing so is similar to right-clicking in the desired location and choosing the insert option, but unlike the cell option, the user may select the bottom two options for inserting complete rows or columns respectively like displayed below in the insert dialog box.
The ribbon is the most important kit in your arsenal of dealing with data in MS Excel and appears at the top of each spreadsheet. The Ribbon permits the user to gain direct access to multiple commands that aid in managing, sorting, and visualizing data in MS Excel. The Ribbon consists of tabs that allow the user to customize the data as per his/her requirement. Here are a few examples of the ribbon with different tabs and their features:-
The feature of freezing panes in MS Excel helps the user to make headings of rows and columns visible even when scrolling deep in an excel sheet either vertically down or horizontally sideways. To freeze panes in MS Excel:-
To do so the user may choose the ‘Wrap text’ option from the home tab after selecting the cell in which the user wishes to wrap the text as shown in the series below
To protect data being copied from the spreadsheet that you have shared with the users, follow these steps:-
Representation of data into graphs or charts is something that MS Excel allows its users to employ with ease. A user may use different styles of chart formats to make complex data look visually simple and understandable to its viewers.
This feature may be accessed by going to the ‘Insert’ tab of the ribbon and choosing the style of the chart the user wishes to display the data in. Below is an example of the same
On selecting the type of chart, the user wishes to utilize, click on ‘OK’ and the data will be showcased in a chart format, like the one shown below
This feature may be accessed by the user with the click of a button, on the ‘Home’ ribbon tab in the editing pane, as mentioned in the below pictures
To apply the same format in all the sheets of a workbook, the user would need to follow the necessary steps:-
The easiest way to do this is to select the column you wish to resize and drag the mouse horizontally sideways to adjust the size of the column, for multiple columns whilst pressing the ‘CTRL’ button select the column the user wishes to resize and for all the columns click on the diagonal arrow to the left of column A.
The other way is to:-
To do so follow these steps:-
The option displayed looks like the picture below
This can be done by adhering to the following steps:-
The following would showcase the following result
The following id the order used to evaluate formulas in Excel:-
The easiest way to remember this operation is the abbreviation PEMDAS or BEMDAS, which is the initial letter of each operator in the order of their application.
A function is a pre-established operation in MS Excel that can take a specified number of arguments. Whereas Formula is a user-defined expression used to calculate a value.
An operator may fabricate a complex formula that may have multiple functions integrated into it.
For e.g., =A1+A2 is a formula and =SUM(A1:A10) is a function.
This question is often put forward to understand the comfort of the applicant with the MS Excel functions. There are around 450+ functions in MS Excel, but there are a few noteworthy functions, which as an operator of MS Excel one should know about and be fluent with the integration and implications of these functions to manipulate data:-
There are multiple ways in which a user may tackle errors in MS Excel:-
The below-mentioned functions may be employed:-
Another point for the user to keep in mind that date and time are coded as numbers in Excel, so addition and subtraction may be made to these.
The user may use the ‘LEN’ function to investigate the length of the text string in a cell
These are statistical tables that reduce data to it is the bare minimum. The dial down may display fields such as sums, sales, etc. which pivot tables can showcase in a smarter & simpler manner.
Pivot table features are as follows:-
This can be achieved by providing or creating a ‘Named Range’ using the offset function and use the name to base the pivot table.
This is possible only if the multiple sources of data are from different worksheets of the same workbook.
To make this inquiry, all that the user needs to do is utilize the ‘PivotTableUpdate’ event in the worksheet comprising the pivot table.
The user may follow these steps:-
The user may stop the loss of format in pivot tables post refreshing by changing the options for the pivot table. To accomplish this, the user under the ‘Pivot Tables Option’ needs to:-
Percentages are the ratios that are calculated as a fraction of 100. In literal terms,
Percentage = (Part/Whole) x 100
In MS-Excel to get a %age value, the user would:-
Below is a pictorial representation of the process involved.
Another way of doing this:-
Yes, to calculate interest in Excel the user can make use of the ‘FV’ function which has certain arguments that need to be fed in the function to get the desired result.
So, it showcases as
=FV(rate, nper, pmt, [pv], [type])
To find the rate = interest rate/compound years,
nper = Years*compound year
pmt = any value (including zero)
Below is an example of computing compound interest
The user may use the =AVERAGE function to get the average for a set of numbers.
Please find the below-displayed example
The ‘LOOKUP’ function is generally employed to summon a value from an array of data.
It is a function that permits the operator to summon data from a given range. The letter V in VLOOKUP stands for vertical and implies that the data needs to be structured vertically. This function comes in handy when looking for a single piece of data from a pool of data.
This function works by picking up the data displayed in the default argument from the left side of the screen and then moves towards the data showcased in the same field towards the right in the same row where the argument was found to match the remaining arguments fed in the VLOOKUP function. The function showcases the following arguments
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Please find the below-mentioned example showcasing the VLOOKUP feature to lookup the city of the buyer for order ID number 102
The ‘What if’ analysis is the technique of performing modifications to one or more formulas present in the cells to witness how the alterations to those formulas cause an effect in the rest of the sheet. There are 3 types of ‘What if’ tools in Excel:-
Data Tables and scenarios take a set of inputs to check for potential results. Data Tables can work with just 1 or 2 variables but may accept several different values for every one of those variables. Whereas Scenarios may work with many variables but are limited to a maximum of 32 values.
The REPLACE function switches part of the text string with another set of text. =REPLACE(old_text, start_num, num_chars, new_text)
The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])
COUNT is the function in MS Excel utilized to count the cells which hold numeric data minus the blanks in the specified selection.
COUNTIF & COUNTIFS are functions for counting cells that hold numeric data with arguments in the specified selection.
COUNTA is also known as CountAll is a function used to count any cell with numeric data in the sheet and
COUNTBLANK is the function used to count the cells with empty strings or blank cells.
‘IF’ function is performed to execute a logic test. The function is responsible for checking whether the specified condition is TRUE or FALSE. If the search meets the true criteria the result would be shown accordingly, but if the search does not meet the criteria then the results would not match the arguments.
These functions are responsible for recalculating the worksheet every time there are changes made to the worksheet. If the user is operating with nominal or little data it would not be bothersome, but if the user is dealing with large amounts of data than these functions may substantially increase the run time of these functions and the load time of the results which have conspired due to the changes made in the worksheet.
Here are a few examples:-
The operator may use the ‘CTRL + PAGE DOWN’ command if he/she is on the first sheet and the command ‘CTRL + PAGE UP’ if on the last sheet of the workbook.
The function which allows users to get the information about the day of the week according to the date is the ‘WEEKDAY’ function.
Using formulas in excel sheets is not only the best way to compute numbers but because of volatile functions, it recalculates the sheet every time any value which is part of the initial calculation is changed. The use of formulas not only eases the calculation part but enhances the efficiency of the way tasks are handled and completed with the use of MS Excel.
The ‘Quick Access Toolbar’ above the home button can be customized to showcase the most frequently used Excel functions.
The user may use the ‘Advanced Criteria Filter’ in the list or if more than two conditions need to be tested out.
The quickest way is to type the cell address in the ‘Name Box’.
On the fabrication of a formula for a specific function, the user may direct Excel to the specific location of the data for which the formula is being entered. The referring of the cell consisting of the data is known as ‘Cell Referencing’
The shortcut is employed by coming to the end of the field of data and pressing ‘ALT + =’ signs, as shown in the picture below
To employ this task, the user needs to select the data set that he/she wishes to check and then:-
Ms-Excel, VBA & MySQL
Using PowerBI &Tableau