Top [ 45+ ] Excel Interview Questions & Answers [LATEST]
Last updated on 03rd Jun 2020, Blog, Interview Questions
Advanced Excel refers to features and functions of Microsoft Excel tool which helps the user to perform complex and large calculations, data processing on the huge amount of data, performing data analysis, better representation of data, etc.Advanced Excel training can be quite useful for your business and career as time is significant in the Business world
1.Explain MS Excel in brief.
Microsoft Excel is a spreadsheet or a computer application that allows the storage of data in the form of a table. Excel was developed by Microsoft and can be used on various operating systems such as Windows, macOS, IOS and Android.
Some of the important features of MS Excel are:
- Availability of Graphing tools
- Built-in functions such as SUM, DATE, COUNTIF, etc
- Allows data analysis through tables, charts, filters, etc
- The availability of Visual Basic for Application (VBA)
- Flexible workbook and worksheet operations
- Allows easy data validation
2.What do you mean by cells in an Excel sheet?
The area which falls at the intersection of a column and a row where the information is to be inserted is known as a cell. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet.
3.Explain what is a spreadsheet?
Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one worksheet. You can see all the sheets at the bottom of the window, along with the names that you have given them. Take a look at the image below:
4.What do you mean by cell address?
The cell address of an Excel sheet refers to the address that is obtained by the combination of the Row number and the Column alphabet. Each cell of an MS Excel sheet will have a distinct cell address.
5.Can you add cells?
Yes, you can insert new cells into a sheet. To add a new cell, simply select the cell where you want to insert it and then select the Insert option. you will see the following window:
Select the desired option and then click on OK.
6.Can you format MS Excel cells? If yes, then how?
Yes, MS Excel cells can be formatted. In order to format these cells, you can use the commands present in the Font group of the Home tab. When you open the Font window, you will see the following options:
|Number||Allows formatting cells to be of any type such as currency, accounting, date, percentage, etc|
|Alignment||Allows text control, alignment and setting its direction|
|Font||Enables various fonts, styles, sizes, colors, etc|
|Border||Allows cell borders to be changed, removed, colored, etc|
|Fill||Enables you to choose different colors and styles to fill up the cell|
|Protection||Allows you to lock or hide cells|
7.Can you add comments to a cell?
Yes, comments can be added. 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.
8.Can you add new rows and columns to an Excel sheet?
Yes, you can add rows and columns to an Excel sheet. To add new rows and columns select the place where you intend to add them and right-click on it. Then select the Insert option from where you can choose to select an entire row or column.
9.What is Ribbon and where does it appear?
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. It consists of many tabs such as File, Home, View, Insert, etc. You can also customize the ribbon to suit your preferences. To customize the Ribbon, right-click on it and select the “Customize the Ribbon” option. You will see the following window:
You can select or unselect any option of your choice from here.
10.How do you freeze panes in Excel?
MS Excel allows you to freeze panes that will help you see the headings of the rows and the columns even if you scroll down a long way on the sheet.
To Freeze Panes in Excel, follow the given steps:
- First, select the Rows and Columns you wish to freeze
- Then, select Freeze Pane present in the View tab
- Here, you will see the following three options to selectively freeze the rows and columns as shown in the image below:
11.How do you add a Note to a cell?
To add a Note, select the cell and right-click on the same. then 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.
12.Can you protect workbooks in Excel?
Yes, workbooks can be protected. Excel provides three options for this:
- Passwords can be set to open Workbooks
- You can protect sheets from being added, deleted, hidden or unhidden
- Protecting window sizes or positions from being changed
13.How do you apply a single format to all the sheets present in a workbook?
To apply the same format to all the sheets of a workbook, follow the given steps:
- Right-click on any sheet present in that workbook
- Then, click on the Select All Sheets option
- Format any of the sheets and you will see that the format has been applied to all the other sheets as well
14.What do you understand by Relative Cell Addresses?
Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in order to match the position where the formula is copied. This is done by a system that is called Relative Cell Addresses.
Take a look at the image below where I have written the formula in C9 and copying the same formula to C10. As you can see, C10 shows the sum of A10 and B10, unlike A9 and B9.
15.In case you don’t want to modify the cell addresses when they are copied, what should you do?
If you do not want Excel to change the addresses when you copy formulas, you must make use of Absolute Cell Addresses. When you use Absolute Cell References, the row and the column addresses do not get modified and remain the same.
For absolute referencing, you will need to use the $ sign before column and row number. Take a look at the example shown in the given image:
16.What will you do if you want to change either the column letter or the row number but not both?
To do this, you must make use of Mixed Cell Addresses where either the row or column is relative while the other is absolute.
Take a look at the image below where the columns hold relative referencing while the rows are absolute. Therefore, the values that are to be added in C9 are 5 and 5 since the column letter is the same as in the original formula and hence the result.
17.Can you protect the cells of a sheet from being copied?
Yes, you can do it by protecting the required cells or the complete sheet. In order to do this, follow the given steps:
- Select the cells that you want to protect
- Open up the Font window from the Home tab
- From the Protection pane, select Protection and then check the Hidden box
- Click on Review tab present in the Ribbon, and then select Protect sheet option (Excel will not hide the required cells unless you do this)
- Specify a password (This will help you in unprotecting the sheet later)
18.How do you create Named Ranges?
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
- Give any name of your choice
19.What are macros?
Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action or a set of them that you can perform n number of times. For example, if you have to record the sales of each item at the end of the day, you can create a macro that will automatically calculate the sales, profits, loss, etc and use the same for the future instead of manually calculating it every day.
20.How do you create dropdown lists in Excel?
To create dropdown lists, follow the given steps:
- Click on Data tab present in the ribbon
- Then, from the Data Tools group, click on Data Validation
- Navigate to Settings>Allow>List
- Select the source list array
21. Explain Pivot tables along with their features?
Pivot Tables are statistical tables that condense data of those tables that have extensive information. The summary can be based on any field such as sales, averages, sums, etc that the pivot table represents in a simple and intelligent manner.
Some of the features of Excel Pivot Tables are as follows:
- Allow the display of exact data you have to analyze
- Provide various angles to view the data
- Allow you to focus on important details
- Comparison of data is very handy
- Pivot tables can detect different patterns, relationships, data trends, etc
- They can create instant data
- Accurate reports
- Serve the base for Pivot charts
22.How do you create Pivot Tables?
In order to create a Pivot table, you will first need to prepare the data in a tabular format. Keep the following points in mind while preparing the data:
- Arrange the data into rows and columns
- The first row should contain unique heading for each of the columns
- The columns should have only one type of data
- Rows must have data for a single recording only
- No blank rows
- Columns should not be completely blank
- The data for creating Pivot table should be separate from other data present in the sheet
For example, let’s create a Pivot chart for the table shown in the image below:
To create a Pivot table, select the table and click on the Insert tab. then select Pivot Table command and you will see the following window:
Specify where you intend to create the table and then click on OK. Once this is done you will see that an empty pivot table has been created. Also, PivotTables Fields pane will open that will help you configure the Pivot table. Take a look at the image below where I have created a Pivot Table:
23.What are Pivot charts in MS Excel?
MS Excel charts are data visualization tools that help you visualize data in various ways. These charts can be of any type such as Bar, Pie, Area, Line, Doughnut, etc. For example, take a look at the Pivot table in the image below:
Now, if you wish to create a pivot chart for this table, select any cell from the table and then from the Insert tab, choose the Pivot Chart option. You will see the following options:
Choose any chart of your preference and click on OK. You can also format these charts respectively.
24.Can you create Pivot tables using multiple tables?
Yes, you can create Pivot tables using more than one base table. To do this, follow the given steps:
- Press Alt+D and then press P to open up the PivotTable Wizard
- Then select Multiple consolidation ranges option and click on Next and you will see another dialog box as shown below:
- Select I will create the page fields option and click on Next
- In the next window, you will need to add all the required ranges as shown below:
- Once that is done, click on Next
- Specify the region where you want to create the table and then click on Finish
- You will see the pivot table has been created by merging both the tables as shown in the image below:
25. What happens when you check the Defer Layout Update option present in the PivotTable Fields window?
In case you check this option, you will not see dynamic changes while interchanging the table fields. By default, this option is off or unchecked. All the changes will appear only after you click on the Update button when you check this box.
26. Can you create a pivot table using tables from different worksheets?
If both the sheets are from the same workbook, you can create a pivot table for tables from different sheets as well. To create a pivot table from two different sheets, when you specify the tables, go to the respective sheet and select the tables you intend to merge.
27. Is it possible to see the details of the results displayed in a pivot table?
Yes, it is possible to see the details of the results shown by the pivot tables in Excel. In order to see the details for any result, double-click on the value and you will see that a new sheet has been created with a new table having details about the factors that have led to that particular result. For example, if I double-click on one of the city values for Brad in the pivot. The following table that displays the details regarding the same:
28. How are Pivot Tables used to filter data?
Excel Pivot Tables allow you to filter data according to your requirements. To do this, place the field based on which you wish to filter out the data. Then from the pivot table, open the dropdown list present for the field you have placed in the Filter area and select the section of your choice. For example, in the table shown, if you wish to filter the data for different cities, you can do it easily as shown below:
As you can see, I have filtered the data for Chicago.
29.How do you change the value field to show some other result other than the Sum?
In order to change the value field to show results other than the Sum, right-click on the Sum of Amount values and then click on Value Field Settings.
Here is the dialog box that you will see:
From here, you can select any value of your choice and then click on OK.
30.How to stop automatic sorting in PivotTables?
Excel automatically sorts the data present in the Pivot Tables. In case you do not want Excel to do this, open the drop down menu for the Row Labels or the Column Labels, and then click on More Sort Options. You will see the Sort dialog box opening. Click on More Options and unselect the Sort automatically option.
31.What do you understand about Excel functions?
Functions, in Excel, are used to perform specific tasks. Excel has many built- in functions that are used to calculate results of various formulas thereby helping in time conservation. Also, these functions make it very easy to execute formulas which would have been difficult to manually write down.
32.What are the various categories of functions available in Excel?
Functions in Excel are categorized as follows:
|Financial||ACCINTM, DOLLARDE, ACCINT, etc|
|Math & Trig||SUM, SUMIF, PRODUCT, SIN, COS, etc|
|Statistical||AVERAGE, COUNT, COUNTIF, MAX, MIN, etc|
|Lookup & Reference||COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE, etc|
|Database||DAVERAGE, DCOUNT, DMIN, DMAX, etc|
|Text||BAHTTEXT, DOLLAR, LOWER, UPPER, etc|
|Logical||AND, OR, NOT, IF, TRUE, FALSE, etc|
|Information||INFO, ERROR.TYPE, TYPE, ISERROR, etc|
|Engineering||COMPLEX, CONVERT, DELTA, OCT2BIN, etc|
|Cube||CUBESET, CUBENUMBER, CUBEVALUE, etc|
|Compatibility||PERCENTILE, RANK, VAR, MODE, etc|
|Web||ENCODEURL, FILTERXML, WEB SERVICE|
Formulas in Excel are executed according to the BODMAS rules. BODMAS, as many of us know, stands for Brackets Order Division Multiplication Addition and Subtraction. That means, in every formula, brackets are executed first (if they are present) followed by multiplication, division, etc. An example of the same is shown in the image below:
As you can see, the output is 27 i.e obtained by first adding 4+5 and then multiplying it by 3. In case you do not specify the brackets, you will get the result by first multiplying 3×4 and then adding 5 to it i.e 12+5 resulting in 17.
34.Explain SUM and SUMIF functions.
SUM: The SUM function is used to calculate the sum of all the values that are specified as a parameter to it. The syntax of this function is as follows:
SUM(number1, number2, …)
As you can see in the image, the SUM function is calculating the total price for all the vegetables.
SUMIF: This function is used to calculate the sum of values that comply with a given condition.
SUMIF(range, criteria, [sum_range])
- range specifies the range of cells to be evaluated
- criteria provides the condition to be met
- sum_range is optional and provides the actual cells to be summed up
As you can see, the SUMIF function is calculating the sum of goals scored only by Dybala.
35. What is a Scenario Manager?
Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.If you only have one or two variables changing, you can create a one variable or two-variable data table. But if you have 3 or more than 3 variables that can change, then scenario manager is the way to go.
For example, if you’re a regional sales manager and have four areas under you, you can use scenario manager to create different scenarios (such as):
- None of the areas show any growth in sales.
- Area A grows 10% but the other 3 don’t grow.
- Area A and B grow by 10%, but the other two don’t grow.
With scenario managers in Excel, you can easily create the scenarios and analyze these one by one or as a summary of all the scenarios.
Enroll in Advanced Excel Training & Build Your Skills to Next Level
- Instructor-led Sessions
- Real-life Case Studies
36. How do you calculate the percentage in Excel?
Percentages, as we all know, are ratios that are calculated as a fraction of 100. Mathematically, the percentage can be defined as follows:
Percentage = (Part/ Whole) x 100
In Excel, the percentage can be calculated in a similar manner. Take a look at the image below where the percentage has been calculated for the values present in A1 and A2.
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 Home tab, select % symbol from the numbers group
37. Explain how to calculate compound interest in Excel?
To calculate compound interest in Excel, you can use the FV function. FV returns the future value of an investment based on the periodic, constant interest rate and payments.
FV(rate, nper, pmt, pv, type)
To find the rate, the number of periods are used to divide the annual rate (annual rate/ periods). nper is obtained by multiplying the no. of years (term) with the periods (term * periods). Periodic payment (pmt) can be any value (including zero).
The investment amount is $500, rate is 10% for 5 years. There are no periodic payments hence the value for pmt is 0. -B1 means that $500 has been taken from you. Therefore, the FV for this is $822.65.
38. How do you find averages in MS Excel?
Average can be calculated using the AVERAGE function.
AVERAGE(number1, number2, …)
To calculate the average marks scored by Dave and Ava, I have used the AVERAGE function.
39.What is VLOOKUP in Excel?
VLOOKUP is a function present in Excel used to lookup and bring forth data from a given range. V in VLOOKUP stands for Vertical and to use this function, data should be arranged vertically. VLOOKUP is very useful when you have to find some piece of data from a huge amount of data.
40.How does the VLOOKUP function work?
The VLOOKUP function, in Excel, a lookup value and begins to look for the same in the leftmost column. When it finds the first occurrence of the given lookup value, VLOOKUP starts to move right i.e in the row where the value was found. It goes on until the column number specified by the user and returns the desired value. This function is used to match exact and approximate lookup values. However, the default match is an approximate match.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value gives the value to be looked out for
- table_index is the range from where the data is to be taken
- col_index_num specifies the column from which you want to fetch the value
- range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)
41.Explain the exact match with an example.
For an exact match, set the range_lookup value as FALSE.
In case you want to look for the designation of an employee, follow the given steps:
- Select the destination cell and type “=”
- Use VLOOKUP
- Specify the lookup_value (Here, it is the ID) along with the other parameters
- Set range_lookup value to FALSE
- The function will be: =VLOOKUP(104, A1: D8, 3, FALSE)
As you can see, VLOOKUP has returned the designation of the employee having 104 as his ID.
42. Explain the approximate match with an example.
For an approximate match, VLOOKUP will fetch values when there are no exact matches of the given loopup_value. For an approximate match, set the range_lookup value to TRUE. Remember that the table must be sorted in ascending order for VLOOKUP to do an approximate match. So here, VLOOKUP basically starts to look for an approximate match of the given lookup value and then stops at a value that is next largest of the given lookup value. It then moves into that row to return the value from the column that has been specified.
The following image shows an example of an approximate match by VLOOKUP:
- Follow the same steps specified for exact match
- For the range_lookup value, use TRUE
- The function will be: =VLOOKUP(55, A12: C15, 3, TRUE)
The lookup value is 55 and the next largest of the lookup values present in the first column is 40. Hence, the output is ‘Second Class’.
43. Can you use VLOOKUP for multiple tables?
Yes, you can use VLOOKUP for multiple tables as well. In case you have two lookup tables, create named ranges for each table, and then use the IF function to select between each table based on some given condition.
44. How do you perform a horizontal lookup in Excel?
To perform a horizontal lookup, you will have to make use of the HLOOKUP function.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value gives the value to be looked out for
- table_index is the range from where the data is to be taken
- row_index_num specifies the row from which you want to fetch the value
- range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)
45.How will you fetch the current date in Excel?
You can make use of the TODAY function. This function will return the current date in the MS Excel date format.
46. How does the AND function work?
The AND function in Excel is used to decide whether a given condition or a set of conditions is TRUE or not. In case all the conditions are satisfied, this function will return a boolean TRUE.
- AND(logical1, [logical2], …)
- logical1, logical2, … are conditions from 1-255 that you want to check
47. What is the What If Analysis?
What If Analysis is the technique of performing changes to one or more formulas present in the cells in order to see how it affects the result of those formulas in the worksheet. Excel provides three types of What If Analysis tools:
- Goal Seek
- Data Tables
Scenarios and Data Tables take a set of inputs to check for the potential results. Scenarios can work with many variables but input values can be at the max 32. Data tables, on the other hand, work with just one or two variables but can accept many distinct values for each of those variables.
Goal Seek, in contrast to Scenarios and Data Tables, takes the outputs and determines the possible inputs for the same.
48. Can you create shortcuts for most frequently used formulas?
Yes, you can do it by customizing the Quick Access Toolbar. To customize it, right-click anywhere on the Quick Access Toolbar and select the Customize Quick Access Toolbar option. You will see the following window:
From here, select Formulas and then choose any formula that you wish to create a shortcut for.
49. What is the difference between formulas and functions in Excel?
Formulas are defined by the user that is used to calculate some results. Formulas either be simple or complex and they can consist of values, functions, defined names, etc.
A function, on the other hand, is a built-in piece of code that is used to perform some particular action. Excel provides a huge number of built-in functions such as SUM, PRODUCT, IF, SUMIF, COUNT, etc.
50. How do you use wildcards with VLOOKUP?
Wildcards can be used when you are not sure of the exact lookup value. In order to use wildcards in Excel, you should make use of the “*” symbol.
For example, in the table that you see in the image below, if you enter “erg” and then use wildcards with it, VLOOKUP will fetch the output that corresponds to “Sergio”.
51. What are the different data formats in Excel?
The following formats are available in Excel:
- Text Format: This can include text as well as alphanumeric strings (such as ABC123). A text string can also include punctuation and symbols.
- Number Format: There are different formats even within numbers. For example you can have decimals, fractions, have thousand separators, etc. No matter what format has been applied, you can use numbers in calculations such as addition, subtraction, division, and multiplication.
- Date Format: The most important thing to know about dates is that these are stored as numbers in the Excel. However, you can format it to be shown as dates. For example, 01-01-2019 would be saved as 43466 in Excel, which is the number for the given date. In Excel, you can show dates in different formats such as long date (01 January 2019), short date (01-01-2019), etc.
- Accounting / Currency Format : When you apply the accounting / currency format to a number, Excel adds the currency symbol along with two decimal places.
- Percentage Format: You can format numbers to be shown as percentage. For example, you can make 0.1 to show up as 10% when you apply the percentage format to it.
52. How can you wrap text in Excel?
Wrapping text in Excel allows you to avoid any text overflowing out of the cell. By applying the wrap text option, you can make sure all the text fits nicely in one single cell (which may change it’s height though).
To wrap text, select the cell, go to the Home tab and click on the Wrap text option in the Alignment group. Note that this is a toggle button, which means that if you click on it again, it will unwrap the text.
53. How can you merge cells in Excel?
To merge cells, you need to first select the cells that you want to merge, then go to the Home tab, and click on the ‘Merge and Center’ option in the Alignment group.
Note that while using ‘Merge and Center’ gets the job done, it is not the most efficient way to do it. The problem of using ‘Merge and Center’ is that the resulting cells wouldn’t sort properly. The right way merge cells is by using the ‘Center Across Selection’ option.
54. What is ‘Format Painter’ used for?
‘Format Painter’ allows you to copy the format from a cell and apply it on another cell (or range of cells).
55. How would you clear all the formatting without removing the cell contents?
Sometimes, you may want to remove all the formatting (colors, borders, font styling, etc.) and just have plain simple data. You can do that by clearing all the formatting in Excel.
To do this, you need to use the ‘Clear Formats’ option, which can be found in the Home tab in the editing group. It becomes visible when you click on the ‘Clear’ drop down.
Note there are other options as well – such as clear contents, clear comments, and clear Hyperlinks. In case you want to clear everything – use the ‘Clear All’ option.
Get Advanced Excel Courses with Industry Standard Excel SyllabusWeekday / Weekend BatchesSee Batch Details
56. What is conditional formatting?
Conditional Formatting allows you to format a cell based on the value in it. For example, if you want to highlight all the cells where the value is less than 30 with a red color, you can do that with Conditional Formatting.
57. How would you highlight cells with negative values in it?
You can do this using conditional formatting. Here are the steps:
- Select the cells in which you want to highlight the cells with negative value.
- Go to the Home tab and click on the Conditional Formatting option.
- Go to Highlight Cell Rules and click on ‘Less Than’ option.
- In the ‘Less Than’ dialogue box, specify the value as 0 and the formatting.
58. How would you highlight cells with duplicate values in it?
You can do this easily using conditional formatting. Here are the steps:
- Select the data in which you want to highlight duplicate cells.
- Go to the Home tab and click on the Conditional Formatting option.
- Go to Highlight Cell Rules and click on the ‘Duplicate Values’ option.
59. How would you highlight cells with errors in it?
In Excel there can be different types of errors – such as #N/A, #DIV/0!, #VALUE!, #REF!, #NAME, and #NUM.
You can highlight all the cells that contain any of these errors using conditional formatting.
Here are the steps to highlight cells with errors:
- Select the data in which you want to highlight the cells with errors.
- Go to the Home tab and click on the Conditional Formatting option.
- Click on ‘New Rule’.
- In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’ option.
- In the formula field, enter =ISERROR(A1), where A1 is the active cell in the selection.
- Click on the Format button and specify the color in which you want to highlight the cells.
- Click OK.
60. How can you make text invisible in Excel?
There are multiple ways to do this:
- You can simply make the font white and it will appear as if it’s invisible.
- [Better Way] You can make the text invisible by changing the custom format. Here are the steps to do this. Select the cell, press Control + 1 (hold the control key and press 1). This will open the Format Cells dialog box. In the Custom option, type ;;; in custom option field. This will make the text invisible (but it will still be there).
61. What is the order of operations used when evaluating formulas in Excel?
Following is the order of precedence in Excel formulas:
- Parenthesis (Brackets)
- Exponentiation (^)
- Multiplication or Division – both have equal precedence and is evaluated based on whichever comes first
- Addition or Subtraction – both have equal precedence and is evaluated based on whichever comes first
An easy way to remember this is by the acronym PEMDAS: which is the first alphabet of each operator.
62. What is the difference between a function and a formula in Excel?
A formula is a user-defined expression that calculates a value. A function is a predefined built-in operation that can take the specified number of arguments. A user can create formulas that can be complex and can have multiple functions in it.
For example: =A1+A2 is a formula and =SUM(A1:A10) is a function.
63. What according to you are the top 5 functions in Excel?
This question is often asked to understand the comfort of a candidate with Excel functions. While there are 450+ functions in Excel and there is no set criteria to select the top five, here are the ones I believe are worthy:
- INDEX / MATCH
I have chosen the above functions as these are not very basic and are quite useful for someone who does analysis in Excel. You can also consider the following functions – SUMPRODUCT, TEXT, SUM, AVERAGE, LEN/LEFT/RIGHT/MID.
Again, there is no right or wrong answer to this. Just make sure you know the functions that you mention.
64. What is the difference between absolute and relative cell references?
In Excel reference refers to a cell reference – such as A1 or range reference – such as A1:A10.
Relative References: These are cell references that change when you copy and paste the formula that has the references. To give you a simple example, if you put =A10 in cell A1, and then you copy cell A1 and paste it in cell A2, the reference would change to A11. This happens as this is a relative cell reference and it changes relative to the cell it’s copied from.
Absolute References: These are the references that remain the same and don’t change copy and paste the formula that has the references. For example, if you put =$A$10 in cell A1 and then copy cell A1 and paste it in cell A2, the reference would still remain $A$10. The $ sign before the column alphabet and the row number makes it absolute.
65. What are the different types of errors you can encounter in Excel?
When working with Excel, you can encounter the following six types of errors:
- #N/A Error: This is called the ‘Value Not Available’ error. You will see this when you use a lookup formula and it can’t find the value (hence Not Available).
- #DIV/0! Error: You’re likely to see this error when a number is divided by 0. This is called the division error.
- #VALUE! Error: The value error occurs when you use an incorrect data type in a formula.
- #REF! Error: This is called the reference error and you will see this when the reference in the formula is no longer valid. This could be the case when the formula refers to a cell reference and that cell reference does not exist (happens when you delete a row/column or worksheet that was referred in the formula).
- #NAME ERROR: This error is likely a result of a misspelled function.
- #NUM ERROR: Number error can occur if you try to calculate a very large value in Excel. For example, =194^643 will return a number error.
66. How can you tackle errors when working with Excel Formulas?
There are various ways you can tackle the errors in Excel:
- You can highlight the errors using conditional formatting. This requires using the ISERROR function in conditional formatting.
- You can use the IF ERROR Function in Excel to get a specific value in case the formula returns an error.
- You can use ISERROR to get TRUE in case there is an error and FALSE in case there is not.
- You can use IFNA function to tackle the #N/A error.
67. Which function would you use to get the current date and time in Excel?
The following functions can be used:
- TODAY(): This function takes no argument and would return the current date value.
- NOW(): This function takes no argument and would return the current date and time value.
Remember that dates and time are stored as numbers in Excel. So you can perform operations such as addition/subtraction with these dates.
68. How can you combine the text from multiple cells using a formula?
To combine text from different cells, you can use any one of the following three methods:
- TEXTJOIN function – If you’re using Office 365 subscription, you will have the TEXTJOIN function available in your version.
- CONCATENATE function – If you want to combine values in cell A1 and A2, you can use the formula =CONCATENATE(A1,A2)
- Ampersand (&) operator: This works just like the CONCATENATE function. To combine text strings in cell A1 and A2, use the formula =A1&A2
69. What formula would you use to find the length of a text string in a cell?
You can find the length of a string in a cell using the LEN function.
For example, if you want to know the length of the string in cell A1, you can use the formula =LEN(A1)
70. What is the syntax of the VLOOKUP function?
VLOOKUP is definitely one of the most popular Excel functions. And this is also one of the most asked Excel questions that I have seen in interviews.
Here is the VLOOKUP syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]):
- lookup_value: this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
- table_array: this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject
- col_index: this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
- [range_lookup]: here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match..
71. How would you get rid of leading / trailing / double spaces in a text in Excel?
To get rid of leading, trailing, and double spaces, you need to use the TRIM Function.
For example, if you have a text string in cell A1 and you want to remove the spaces, you can use the following formula:
Note that it doesn’t remove single spaces between words.
Excel TRIM function does a good job in removing spaces in Excel, however, it fails when you have non-printing characters (such as line breaks) in your data set. To remove non-printing characters, you can use a combination of TRIM and CLEAN functions.
If you have some text in cell A1 from which you want to remove spaces, use the below formula:
72. What are the known limitations of the VLOOKUP function?
The VLOOKUP function is mighty useful, but it also has a few limitations:
- It can not be used when the lookup value is on the right. For VLOOKUP to work, the lookup value should always be in the left-most column. Now this limitation can be overcome by using it with other formulas, it tends to make formulas complex.
- VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column). You can make the column number dynamic, but if you plan to combine two or more functions, why not use INDEX/MATCH in the first place.
- When used on large data sets, it can make your workbook slow.
73. When would you use the SUBTOTAL function?
When you’re working with tabular data, you can use the SUBTOTAL function to get a variety of subtotals – such as AVERAGE, COUNT, MAX, MIN, STDEV.
One of the highlights of SUBTOTAL function is that it allows you to ignore hidden/filtered cells. So if you have a huge data set and you filter it based on a criteria or hide some rows, SUBTOTAL function will automatically update to give you the result from the visible cells only.
Of course, if you don’t want the data of filtered/hidden cells to be ignored, you can do that too.
74. What are volatile functions? Can you name a few?
A volatile function recalculates the formula again and again (whenever there is any change in the worksheet).A very simple example of a volatile function is the NOW() function (to get the current date and time in a cell). Whenever you edit any cell in a worksheet, it gets recalculated. This is fine if you have a small data set and less number of formulas, but when you have large spreadsheets, this could significantly slow down the processing.
Here is a list of volatile formulas:
- Highly volatile: RAND(), NOW(), TODAY()
- Almost volatile: OFFSET(), CELL(), INDIRECT(), INFO()
75. What are the most useful keyboard shortcuts you use?
There are hundreds of Excel keyboard shortcuts. I am listing my top five shortcuts, but in case you have your own, feel free to use that:
- CONTROL D to fill down the content from the cell above.
- SHIFT Space bar to select the entire row (or CONTROL + Space bar to select the entire column).
- CONTROL: to delete cells/row/column. This opens the delete dialog box where you can choose what to delete.
- CONTROL ; to insert current date (CONTROL SHIFT ; inserts the current time).
- CONTROL PAGE UP/PAGE DOWN: to cycle through the worksheets.
76. What is the shortcut for opening the find and replace dialog box?
- CONTROL F :This opens the Find and Replace dialog box with the Find tab selected.
- CONTROL H: This opens the Find and Replace dialog box with the Replace tab selected.
77. What is the shortcut to open a new Excel workbook?
This works only when you have the Excel application open.
78. How can you select all the cells in the worksheet?
You can use CONTROL A A – hold the control key and hit the A key twice.
In case you don’t have any data around the active cell, hitting the A key once would select the entire worksheet. But in case there is data, hitting the A key once selects the entire data and hitting it again then selects all the cells in the worksheet.
79. How would you insert a new line in the same cell?
To insert a new line in the same cell, use the shortcut ALT Enter – hold the ALT key and press enter.
80. What’s the shortcut to insert a comment in Excel?
Select the cell in which you want to add the comment, hold the ALT key press the F2 key.
81. What is a Pivot Table, and when would you use one?
A Pivot Table is a feature in Microsoft Excel that allows you to quickly summarize huge data sets (with a few clicks).
Even if you’re absolutely new to the world of Excel, you can easily use a Pivot Table. It’s as easy as dragging and dropping rows/columns headers to create reports.
To give you an example, if you have the 10,000 rows sales data from four different regions, you can use a Pivot Table to instantly find what are the total sales in each region. If you want to further drill down and see what are the sales of each product in each region, you can easily do that (it’s literally as easy as dragging a data point in a box).
82. What are the various sections in a Pivot Table?
A Pivot table is made up of four different sections:
- Values Area: This is the area where the values are reported.
- Rows Area: The headings to the left of the Values area makes the Rows area.
- Columns Area: The headings at the top of the Values area makes the Columns area.
- Filter Area: It is an optional filter that you can use to further drill down in the data set.
83. What are slicers?
Slicers were introduced in the Pivot Table in the 2010 version of Excel.
A Pivot Table Slicer enables you to filter the data when you select one or more than one options in the Slicer box (as shown below).
84. What is a Pivot Chart?
When you create a Pivot Table, you get the summary of your data. You can also plot this summary in a chart that is connected to the data.
This chart is called the Pivot Chart. One big benefit of using a Pivot Chart is that it updates when you change the Pivot Table layout. For example, if you have the total sales by region, and you update the Pivot Table to show sales data for each product in the regions, the Pivot Chart would accordingly update.
85. What is the difference between Pivot Charts Vs Regular Charts?
While Pivot Charts are amazing and come with the ability to update when the Pivot Table updates, these are not as flexible as the regular charts.
In general, you can do a lot of customization in a regular Excel chart, but not in a Pivot chart. Also, if you customize a Pivot Chart, and then update the Pivot Table, you are likely to lose the customization.
Despite the limitations, Pivot Charts are useful and can help create quick views from a Pivot Table.
86.How can you refresh a Pivot Table?
To refresh a Pivot table, click on any cell in the Pivot Table, right-click and select Refresh.
Another way of refreshing a Pivot Table is to select any cell in the Pivot Table. It will enable the Pivot Table Tools tab. In the Analyze tab, click on ‘Refresh’.
87. Can you group dates in Pivot Tables?
If you have date wise records, you can easily group these into the following segments:
- Hours / Minutes / Seconds
The option to group data in the Pivot Table is in the Analyze tab, which becomes visible when you select a cell in the Pivot Table in the Rows area.
88. What is a Pivot Cache?
Pivot Cache is something that automatically gets generated when you create a Pivot Table.It is an object that holds a replica of the data source. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache.The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing it. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results.Pivot Cache enables this fast functioning of a pivot table.
89. Can you make a Pivot Table from multiple tables?
Yes, you can create one Pivot Table from multiple different tables. However, there needs to be a connection in these tables.
For example, if you have two tables, one that has a date, Product ID and sale value, and another which has Product ID and Product Name, then you can combine these as the common column in Product ID.
Once you have connected these tables, you can create a Pivot table from these.
A crucial part of this is to set-up table relationships (where you specify the relationship between 2 tables).
90. What is a Column chart?
A column chart is made up of vertical bars that are used to compare values over time or two compare values in different categories.
For example, you can use it to see how the sales have done over the years. Or you can use it to compare which product category has done better sales. Since you can see all the vertical bars at one go, it is easier to visually see and compare.
You can also create clustered column charts, where you can have multiple columns for the same category or year (something as shown below).
91. What is a Bar chart?
A bar chart is made up of horizontal bars that is used to compare values in different categories.
For example, you can use it to compare which product category has done better sales. Or what has been the response of a survey.
You can also create clustered bar charts, where you can have multiple bars for the same category (something as shown below).
92. What is a Line chart?
Line charts are useful when you want to show a trend over the years (or other time periods such as weeks, months, or quarters).
You can have multiple lines in a line chart. This would allow you to compare different categories over the same period of time (something as shown below).
93. What is a Scatter chart?
A scatter chart is used to compare two sets of values. For example, you can have data of different products on two KPIs, and you can plot the data on a scatter chart (as shown below).
This allows you to see what products are doing well on both the KPIs (the top right quadrant) or doing bad on both the KPIs (bottom-left quadrant).
94. Are Pie charts good? Should it be used in reports/dashboard?
There are two schools of thoughts.
There are some who completely hate Pie charts and recommend never to use these (such as this article). And then there are some (including myself), who sometimes use Pie charts in dashboards in reports.
There are many managers who are comfortable with pie charts and find these easy to read. So if you want to show a breakup of revenue by division (where you have only a few divisions), then you can use a pie-chart.
Let me be clear. Pie charts can be completely replaced by a bar chart. There is no additional benefit of using it. But in some cases, Pie charts make a good story (for example showing that one division is bringing in ~75% of the revenue as shown below).
You should avoid using Pie charts:
- In case the difference in values is not significant. The difference is better visualized by a bar chart.
- In case there are too many parts in a pie chart. In such cases, it can look cluttered.
95. What is a Waterfall chart? When would you use it?
A waterfall chart shows different values (positive and negative) that lead to the final result value. For example, if you’re analyzing a company’s net income, you can have all the cost components shown in the waterfall chart.
This will help you visually see how the value from revenue to net income is obtained when all the costs are deducted.
96. What are Combination charts?
Combination charts are those where you combine more than one chart type. A popular example of this is showing a bar chart with a line chart.
Combination charts let you present and compare two different data-sets that are related to each other. For example, you may be interested in plotting the revenue figures of a company, and at the same time, also be able to show how the profit margin has changed. A combination chart (as shown below) is an apt way of doing this.
97. What is a secondary axis in a chart?
In a chart, there is a Y axis where you show the scale on which you can measure the chart (be a bar chart or line chart or others).
In cases where you have two different types of data set with a significant difference in value, you can use the secondary axes.
To give you an example, if you want to show the revenue and net income margin in the same chart, you need to show two different axes. This is because revenue numbers can be in thousands or millions, but net income margin would be in percentage and always less than 100%. In this case, you need to have two axes, one that shows scale for revenue and one that scale for net income margin.
So when you add another axis, it is called the secondary axes. In the figure below, the axes on the right are the secondary axes.
98. What is a Bullet chart? When should we use it?
Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target.
One of the best things about bullet charts is that it is power-packed with information and takes little space in your report or dashboards.
Below is an example of a bullet chart:
Note that bullet charts are not a default chart type in Excel, and you need to use a number of steps to create these.
99. How to replace one value with another in Excel?
You can replace one value with another using the FIND & REPLACE feature in Excel.
To do this, select the data set and use the keyboard shortcut:CONTROL H (hold the control key and then press H). This will open the Find & Replace dialog box.
In this dialog box, you can specify the value you want to change and the replacement value.
100. How can you sort data in Excel?
There is a sorting feature in Excel that can sort data based on text, numbers, or colors.
Here are some ways to sort data in Excel:
- Select the data and click on one of the two sort icons in the Data tab.
- Select the data and click on the Sort icon. It will open the sort dialog box and you can specify the column to sort and the criteria (ascending/descending).
- Apply a data filter, and click on the filter. Along with the filter options, it also shows the data sorting options.