Before applying for a job that requires Excel expertise, Always have hands on expeirence with Advance excel stuff. As you may be asked anything in the interview which requires experience. In this article, trainers from best advance excel Institute in Delhi prepared a list of questions for you. One is needed to exhibit a thorough mastery of Excel’s advanced features and functionalities in order to qualify as an Advanced Excel Job. Get compiled set of top interview questions that will test your understanding of MS Excel in order to completely assess your abilities.
Formulas and functions, macros and automation, data visualisation, and other topics have all been extensively covered by the questions in this set. We advise you to answer all of the questions succinctly and in-depth to demonstrate your command of Excel.
Top 30 Advance Excel Interview Important Questions
1. What is VLOOKUP?
VLOOKUP is an abbreviation for Vertical Lookup. It is an Excel function that searches for specific information in a table of data. It looks for a value in the first column of a table and returns a value from a specified column in the same row.
2. What is an Array Formula?
An array formula is one that conducts several calculations on one or more sets of values and returns one or more results. It is an advanced Excel function that enables us to execute complex computations using a single formula.
3. What is conditional formatting in Excel?
Conditional formatting allows you to format cells based on specific criteria. We just need to
-
- Select the range of cells, go to the Home tab, and click on “Conditional Formatting.”
-
- Choose a rule type (e.g., highlight cells, data bars, color scales) and set the formatting criteria
4. What is the difference between a Workbook and a Template?
A Workbook is a collection of worksheets and data.A Template is a pre-designed Workbook that can be used as a starting point for creating new workbooks.
5. What is the difference between an Excel Workbook and an Excel Worksheet?
An Excel Workbook is a file that contains all of the worksheets. A worksheet is a single tab within a workbook in which data is entered and analysed.
6. How do you protect cells or sheets in Excel?
-
- To protect cells, select the cells, right-click, and choose “Format Cells.”
- In the Format Cells dialog box, go to the Protection tab and uncheck “Locked.”
- Then, go to the Review tab, click on “Protect Sheet,” and set a password if desired.
7. What is the difference between a Relative Reference and an Absolute Reference?
A Relative Reference is a cell reference that changes depending on where the formula is located. An Absolute Reference is a cell reference that remains constant regardless of where the formula is located.
8. Difference between FUNCTION and FORMULA in excel?
A formula is an expression that calculates a value while a function is a predefined formula that performs calculations using specific values in a particular order.
9. How will you fix/lock the cell/range reference?
To fix/lock the cell/range reference, we can use the $ symbol before the column and row reference.
10. What is the difference between VLOOKUP and INDEX-MATCH?
VLOOKUP searches for a value in the first column of a table and returns a value in the same row from another column of the table whereas INDEX-MATCH uses two functions to look up values: INDEX returns a value from an array based on its position and MATCH returns the position of a value in an array.
11. What is a Macro? How do you create a macro in Excel?
A Macro is a series of instructions in Excel that automates processes. By using Visual Basic for Applications (VBA), we can construct macros that can speed up our work and cut down on errors.
We can perform it as follows:
-
- Go to the Developer tab (if not visible, enable it in Excel options).
- Click on “Record Macro” and provide a name and optional shortcut key.
- Perform the actions you want the macro to record.
- Click on “Stop Recording” in the Developer tab when you’re done.
- The macro can be accessed and run from the Developer tab.
12. What is a Named Range?
A Named Range is a cell or range of cells that has a unique name. This is useful for formulas that use the same range of cells over and over.
13. How do you get rid of duplicates in Excel?
-
- Select the range of cells or columns where you want to remove duplicates.
- Go to the Data tab and click on “Remove Duplicates.”
- Choose the columns to check for duplicates and click OK.
14. What is the difference between CONCATENATE and TEXTJOIN?
In Excel, both functions are used to join text strings. However, CONCATENATE can only join two strings, whereas TEXTJOIN can join multiple strings and allows you to specify a delimiter.
15. How do we freeze Excel’s row and columns?
We can follow these steps to freeze both rows and columns in Excel:
- Select the cell that’s below the rows and to the right of the columns that you want to see when you scroll
- Navigate to view > Freeze Panes
16.What is the use of Option Explicit in VBA?
Option Explicit is used to force the declaration of all variables in a module. If a variable is not declared, then it will result in a compile-time error.
17.How will you inspect a function/formula in Excel?
To inspect a function/formula in Excel, we can use the Evaluate Formula feature.
18.What is the default data type in VBA?
The default data type in VBA is Variant.
19. What are the ways to create a dynamic range?
-
- Creating a Table
- Using OFFSET function
- Using INDEX function
20. What is the order of operations that Excel uses while evaluating formulas?
Excel uses the following order of operations while evaluating formulas:
-
- Parentheses
- Exponents
- Multiplication and Division (from left to right)
- Addition and Subtraction (from left to right)
21. What are pivot tables and how are they created?
Excel’s PivotTable feature is a strong tool one can use to summarise and analyse massive volumes of data.
-
- To create a PivotTable, select the data range, go to the Insert tab, click on “PivotTable,” and choose the location for the PivotTable.
- Then, select the fields you want to analyze and drag them into the appropriate areas in the PivotTable Field List.
22. What are Recommended Pivot Tables and Recommended Charts?
Recommended Pivot Tables and Recommended Charts are features in Excel that suggest PivotTables and Charts based on the data selected.
23. What is the use of Slicer and Timeline in Excel?
Slicer is used to filter data in PivotTables while Timeline is used to filter data in PivotCharts.
24. What is the purpose of pivot tables?
It is used to build distinctive reports and can assist you in quickly identifying trends and patterns in your data.
25. What exactly is a spreadsheet?
A spreadsheet is an arrangement of columns and rows. While columns are vertical vectors, rows are horizontal vectors.It is used to collect, compute, and contrast numerical or financial data.
26. Please summarize VBA and its purpose?
VBA (Visual Basic for products) is an event-driven programming language embedded into most desktop Microsoft Office products which is implemented by Microsoft. To automate both easy and difficult Excel activities, macros are created using VBA. The same set of chores will frequently be repeated by us repeatedly. A sequence of manual actions can be reduced to a single button press with VBA by programming macros to automate tasks like these.
Due to its alleged usability, Office’s large installed user base, and its long history in business, VBA is used for professional and end-user development.
27. What distinguishes the functions COUNT, COUNTA, COUNTIF, and COUNTIFS?
-
- COUNT: Counts the number of cells that contain numbers.
- COUNTA: Counts the number of cells that are not empty.
- COUNTIF: Counts the number of cells that meet a specific criterion.
- COUNTIFS: Counts the number of cells that meet multiple criteria.
28. How can one create a drop-down list in Excel?
Select the cells where you want to create the drop-down list.
-
- Go to the Data tab and click on “Data Validation.”
- In the Data Validation dialog box, choose “List” as the validation criteria.
- Enter the values for the drop-down list in the “Source” box, separated by commas.
29. How can a formula be made to change dynamically as new data is added?
-
- Use Excel’s structured references or tables.
- Convert your data range into a table by selecting it and pressing Ctrl+T.
- Use table references in formulas (e.g., TableName[ColumnName]) to refer to the data.
When new data is added to the table, the formulas will automatically update.
30. How to use VLOOKUP in Excel?
VLOOKUP is a function used to search for a value in the first column of a table and retrieve a corresponding value from another column.
-
- Its syntax is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
- lookup_value: The value to search for.
- table_array: The table or range where the data is stored.
- col_index_num: The column number from which to retrieve the data.
- range_lookup: Optional. Specify FALSE for an exact match or TRUE for an approximate match.
It is recommended to stay on top of significant advanced excel functions such as SUMIF, SUMPRODUCT, MAXIFS, IFERROR, NOW, DATEDIF, and so on along with their corresponding errors and fixes.
Hopefully, this list of questions will help you assess your proficiency with advanced Excel and aid in your preparation for advanced Excel-related job interviews.
31. What is the primary limitation of
the VLOOKUP function?
The VLOOKUP function is limited to moving in one direction: left to right. Consequently, the desired data must be located in a column to the right of the lookup value.
Updated versions of Excel now come with XLOOKUP, which is the VLOOKUP substitute. This new function works both ways and defaults to exact matches instead of approximations. Before XLOOKUP completely replaces VLOOKUP, the majority of users must have ceased using prior Excel versions.
32. How will you build named ranges?
Formulas can be written more quickly and intuitively using named ranges For example, we can choose a contextually relevant term (such as Profits) for the full range of cells in a certain column (A1:A50) rather than manually entering those cell references.
Therefore, we can use =SUM(PROFITS) instead of =SUM(A1:A50). Most frequently, the heading of a particular column or row of data serves as the name for a specified range. There are three methods for creating a named range. One way is to use the Name Box directly.
To finish the procedure, we will choose the range of data we want to name, then put the name into the Name Box. This will create a named range that is applicable to the whole workbook.
Instead, we can also make use of the Define Name method to identify a specific worksheet. Just select the data range needed to name , then click Define Name under the Formulas tab. We can then name your range and provide a scope (the entire workbook or a specific worksheet) in the dialogue box that appears.
The third option works best for generating named ranges from column and row headers and for constructing many named ranges at once. We will start by choosing every piece of data and header to give names to.
Next, select Create from Selection under the Formulas tab to bring up a dialogue window. After that, we can select the rows or columns (i.e., the top row) that contain the intended names. Ranged names will be generated concurrently for all specified data the moment we click OK.
33. What is an Excel Dashboard?
Excel has a feature called dashboards that helps to streamline and compress the way data is presented. Their aim is to present copious volumes of data in an easily readable way on a single page, facilitating the rapid consideration of numerous aspects throughout the decision-making process.
Dashboards do this by utilizing a variety of gauges, figures, charts, and graphs that present data in an understandable manner to make it easier to thoroughly analyze big data sets.
For instance, a dashboard can show the department store’s most popular product, total sales, money spent per sale, and consumer type using a range of charts and graphs. If shop executives had access to all this information in one location, they can swiftly analyze the data and determine the best times to run limited specials on particular products.
34. How can one create a dropdown list in Excel?
This can be performed using the ‘Data Validation’ option on the Data tab. One just needs to follow a few steps.
- Select the cells where the drop-down lists will be added.
- Select Data, and then click Data Validation.
- Select List from the Allow menu.
- In the Source tab, enter the values for the items in their dropdown list (separated by commas).
The dropdown list is ready.
35. What do you understand about the Wildcards in excel?
Excel wildcards are characters that can represent one or more characters in search criteria. Whenever users wish to conduct searches or filter data using patterns rather than precise matches, they are handy.
- Asterisk(*): It denotes a character or more. For example, Ex* might stand for Expertise, Excel, Extra, etc.
- Question mark(?): It stands for a silent character. For example, R?in can refer to Rain or Ruin.
- Tilde(~): To negate the impact of a wildcard character, put a tilde (~) before the character to generate a literal asterisk (*), literal question mark (? ), or literal tilde (~).
Wildcards can be used in a variety of Excel functions, including filters and search features, as well as functions like COUNTIF, SUMIF, and VLOOKUP. All you need to remember is to wrap the criterion in double quotations (“”) when using wildcards with Excel functions such as COUNTIF, SUMIF, IF, etc. Furthermore, not all Excel functions can handle wildcard characters, therefore it’s important to refer to the documentation for the particular function in question.
Problem based Advance Excel Interview Questions
The following list of challenging value-based advanced Excel interview questions and their answers:
A. How would you compute compound interest for a particular investment given varied compounding periods?
We can use the equation A = P(1 + r/n)^(nt) to determine compound interest, where A represents the ultimate sum, P represents the principal, r represents the yearly interest rate, n represents the number of compounding periods per year, and t represents the number of years. We can put these details into the formula to determine the compound interest.
B. Can you lay out how to execute a sensitivity analysis in Excel using Data Tables?
Yes, Excel’s Data Tables can be used to perform a sensitivity analysis. By entering various values for one or two variables and analysing the changes in a particular output cell, we can create a data table. This aids in the analysis of how various input values affect the result.
C. What is the best way to make a dynamic chart that updates itself when new data is added to the source range?
We can utilise Excel tables and named ranges to create a dynamic chart. To plot the data in the chart, first define a named range for it. Then, create an Excel table from the data range. Finally, the chart will automatically update to include the new data points every time we add new data to the table.
D. How do you compute the weighted average of an array of figures in Excel?
We can multiply each figure by the weight that corresponds to it, add the results, and divide by the total weight in Excel to determine the weighted average.
For example, assuming we had values in column A and weights in column B, the weighted average formula would be: =SUMPRODUCT(A:A, B:B)/SUM(B:B).
E. Could you describe how to utilise Excel’s Solver add-in to optimise a particular target depending on certain constraints?
Excel’s Solver add-in can be used to locate the ideal solution for a given objective while taking into account specific restrictions. We must define the objective cell, specify the variables to be altered, and determine the constraints. To discover the best solution that satisfies the constraints and maximises or minimises the objective, the solver will then test various combinations of variable values.
F. How would you perform a Monte Carlo simulation in Excel?
We can use Excel’s RAND function to create random numbers, which can then be incorporated into our model to simulate various scenarios as part of a Monte Carlo simulation. By performing several iterations, we can examine the spectrum of potential outcomes and assess the probability of various possibilities.
G. How do you use Excel to perform regression analysis on data?
To perform regression analysis, Excel has the Regression tool in the Data Analysis add-in. We need to enter our data, specify the dependent and independent variables, and will then get the coefficients of the regression equation as well as statistical measurements like R-squared and p-values. This aids in the analysis and prediction of the relationships between variables.
H. How would you use the Goal Seek feature in Excel to find a specific input value that achieves a desired output?
We can identify the input value that causes a certain output using Excel’s Goal Seek feature. We must choose the input cell we want to edit, define the target cell, and describe the desired output value. Excel will then change the input value iteratively until the desired result is obtained.
I. Could you give an illustration of a challenging data analysis task you completed using Excel? What actions did you take, and what resulted from them?
Analysing sales data to spot trends and patterns was one complex data analysis task I did using Excel. The first thing I did was to import the raw data into Excel and clean it up by getting rid of duplicates and managing any missing information. In order to generate interactive reports that let stakeholders sort the data based on different criteria, I utilised PivotTables to summarise the data. I utilised conditional formatting to draw attention to notable variations and data visualisation strategies, such charts and graphs, to convey the results. This analysis produced a thorough understanding of sales trends, enabling wise resource allocation and marketing strategy decisions.
J. How would you go about implementing VBA or macros in Excel to automate monotonous tasks?
When automating repetitive tasks in Excel, we would first record a macro that captured the steps required to perform the task manually. We would then go over and clean up the recorded macro code, making it more efficient and adaptable. To handle various eventualities, if necessary, we would add variables, loops, and conditional statements. In order to ensure the macro functions properly and doesn’t run into any unexpected problems, we would also include error handling procedures. We would save time and lessen the possibility of human error by automating these repetitious procedures.
K. How can enormous datasets be handled in Excel to maximise functionality and reduce file size?
We can use a number of techniques to improve efficiency and reduce file size when working with large datasets in Excel. To import and alter data from other sources, we can first use Excel’s Power Query tool. Before importing data into Excel, Power Query enables us to filter and reshape it, thereby reducing the dataset size. In addition, we would restrict the usage of volatile functions, which recalculate with each update, and refrain from utilising whole-column references in formulas. Instead, we shall limit the range of cells that formulas can reference. Additionally, we’ll make use of Excel’s Table feature, which offers structured data storage and automatic resizing as new or updated data is added or removed. This decreases the size of the file and speeds up computation.
K. When working with enormous databases in Excel, how can the accuracy and integrity of the data be ensured?
Data integrity and accuracy are crucial when working with huge datasets. We should ensure its accuracy by looking for missing values, discrepancies, and formatting problems. To limit the data input to the specified format or range, we can additionally use Excel’s data validation feature. We should keep data backups and use Excel’s undo feature to roll back any changes that might result in errors or data loss in order to assure data integrity. To guarantee transparency and traceability, we should record the data sources and any modifications made to the data. In order to quickly spot and fix any mistakes, we can also use Excel’s conditional formatting and data visualisation features to highlight any anomalies or outliers in the data.
Remember that these are more complex Excel aspects, therefore it’s critical to practise and obtain hands-on expertise with them before answering such interview questions proficiently.
Few Live project Scenario Based Advance Excel Interview Questions
In-depth scenario-based interview questions for the position of Advanced Excel Specialist are provided below, along with sample responses:
-
- You have a sizable dataset with numerous columns that contain various kinds of data. You must take particular data from this dataset and combine it with other data to create a summary report. How would you go about doing this?
I would start by defining the precise requirements or circumstances for extracting the necessary data. I would filter the dataset based on these criteria and produce a subset of the data that satisfies the required conditions using Excel’s advanced filtering or sorting options. The filtered data would then be summed together using functions like SUM, COUNT, AVERAGE, or other suitable formulas to produce the summary report. I can additionally use pivot tables or charts to present the compiled data in a pleasing visual manner.
-
- You are required to automate a monthly reporting procedure in which you get numerous Excel files from various departments. Each file has numerous sheets, and you must combine particular data from these pages into a single master worksheet. How could this procedure be automated?
I would use VBA (Visual Basic for Applications), Excel’s built-in programming language, to automate this operation. I would create a VBA macro that repeatedly opens all of the files in a given folder, cycles through each file, and extracts the necessary data from the appropriate sheets. The macro would then transfer this information in a systematic manner to the master worksheet while making sure that no data would be overwritten. I would develop a reliable and automated solution for the monthly reporting process by putting error-handling strategies into use and applying effective coding practises.
-
- You possess a sizable dataset with client details, such as names, addresses, and purchase histories. The data needs to be cleaned and transformed before analysis. In Excel, how would you manage this data purging and transformation procedure?
I would first look for any discrepancies or flaws in the dataset, such as missing values, duplicate entries, or formatting problems. I would use Excel’s data cleaning tools to eliminate duplicates, extract pertinent information with text functions, and format data consistently. To further assure data integrity and accuracy, I would make use of Excel’s conditional formatting and data validation features. I would utilise formulae or Power Query to convert the data and create new variables or calculate metrics based on the current data. I would make sure that the data is correct, clean, and prepared for analysis by using these methods.
Just keep in mind that you can modify these examples to reflect your specific knowledge and experience. It’s crucial to give thorough explanations, highlight your problem-solving abilities, and show that you have a thorough understanding of Excel’s sophisticated features and operations.
Keep in mind that organisations value your methods and approach as much as the solutions to these challenging problems. You will be highly acknowledged for your ability to communicate and effectively offer your answers.
You won’t be able to answer with confidence just by reading or learning these answers, But only wth real excel knowledge ad skills. Do practical learning by joining Advance Excel course in Top MS excel institute in Delhi