Given the spreadsheet’s importance in today’s workplace, it’s no wonder that businesses are always looking for people with strong Excel skills.
However, there are many levels of spreadsheet expertise. To one person, creating an appealing bar chart is advanced, yet others are skilled with macros, pivot tables, and historical modelling.
The most common query among recruiters is, “Am I dealing with a novice or a genuine expert?” Answering that question is more difficult than it sounds, given 80% of candidates lie during interviews, and Excel exams can be skewed by specific preparation or pure luck.
What jobs demand Excel skills?
Over 80% of jobs require what we call basic Excel capabilities, making MS Excel competence one of the most in-demand skills available.
Basic Excel abilities are useful for administrative assistants and junior project officers. These positions necessitate extensive data processing, yet daily responsibilities involve only basic Excel capabilities, such as adding a new row or making simple graphs.
Human resources, sales managers, management, market research, operations, quality control, and finance all require intermediate Excel ability. If a job requires you to analyse data or goods, Excel is likely to be part of the skill set. Project managers commonly use spreadsheet abilities across all economic sectors, as well as accountants, auditors, actuaries, and cost estimators.
Advanced Excel software skills are less useful in everyday situations but essential for power users. Advanced functions refer to complicated coding or mathematical modelling. These Excel abilities are essential for statisticians and enterprise data analysts. They also assist strategists with sales, business development, and marketing.
As a candidate, you’ll frequently see potential employers use Excel interview questions to assess candidate knowledge.
It is critical that you understand Excel in today’s world, especially if you work with data. A spreadsheet specialist must be familiar with a variety of tools, and Excel is among the most basic. There are many other tools that can work with massive datasets, but Excel is where most analysis begins before becoming more advanced. Some basic questions you should be able to solve in Excel include:
1. What are some popular data formats in Microsoft Excel?
Excel supports a variety of basic and complex data formats. Some of the most prevalent ones include numbers, percentages, dates, and text.
2. How do data types work in Excel?
There are numerous ways to use data formats in Excel. For example, numbers can be used as decimals or rounded figures, percentages can be used to show a portion of a whole (the whole being 100%), dates can be changed based on the region and location to which Excel is connected, and text can be used to analyse data and reports imported from other spreadsheets.
These formats are frequently used in tandem when performing more advanced coding and manipulation, which we shall discuss later in the article.
3. What is a spreadsheet, and what are its basic components?
A spreadsheet in Microsoft Excel is made up of cells organised in rows and columns. Information is entered into cells to organise, display, and alter data. A spreadsheet, for example, can include the names of a list of products, as well as the price per unit and the number sold. This allows the reader to peruse and search through the list and calculate which data needs to be analysed.
4. How many different formats can Microsoft Excel export data in?
There are six formats in which data can be exported. They are:
- Excel workbooks are.xlsx,
- while macro-enabled workbooks are.xlsm.
- Excel binary workbook (.xlsb),
- template (.xltx),
- code (.xltm)
- XML data (.xml)
Choosing the format to export may be determined by the team that needs the file or by limitations on file size for its final destination.
5. How do you wrap text inside a cell?
Wrapping text is important for presenting data and keeping your spreadsheet neat. You must first pick the text you wish to wrap, then click the wrap text button on the home tab, and the text will wrap within a cell.
6. What exactly is a ribbon in Excel?
A Ribbon is the topmost part of an application that contains menu items and toolbars in MS Excel. Use CTRL+F1 to show/hide ribbons. The ribbon appears at the top of the application and serves as a replacement for the toolbars and menu dropdowns, which can be difficult to navigate without extensive muscle memory.
The ribbons have various tabs at the top, and clicking on them displays a series of commands that you may use without having to return to the tab header.
Furthermore, hovering over ribbon commands displays the hotkey combination for that function, allowing you to memorise some of the functions you use the most.
7. When analysing calculations in Excel, what order of operations do you follow? In other words, in what sequence will the Excel formula process the code provided?
- The same as you use in mathematics using the terms “PEMDAS” or “BEDMAS”. This refers to:
- Parentheses or Brackets
- Math operations include exponent, multiplication, division, addition, and subtraction.
8. What are Excel’s cells?
Each worksheet in Microsoft Excel has several rows and columns. The points at which the rows and columns connect form a rectangle, also known as a cell.
9. What is a cell address, and how does it appear in Excel?
The cell address is a unique identifying value assigned to each cell on an Excel sheet to make it easier to locate data included inside the document. The cell address is represented by the column letter and row number of the cell. The notation is straightforward: A1 is the left-highest cell in a spreadsheet, and subsequent cells count outward from there.
10. How can you remove unneeded sheets from your Excel workbook?
To delete a sheet, right-click on the bottom sheet tab. The right click will provide a menu of options, including delete. Selecting delete will bring up a prompt asking if you are ready to permanently remove the sheet; make sure you have a saved version or are certain you will no longer need the information contained inside.
11. Can you annotate a cell in your Excel workbook? If so, please explain how.
Excel allows you to mark a cell using a variety of approaches, including colours, callouts, and cell comments.
12. What exactly are cell references?
Cell references are used to refer to data in the same Excel spreadsheet but from a different cell. This is useful when creating custom formulas that employ data from several locations on the same page or from a different sheet.
13. How do I add a comment to an Excel spreadsheet?
To add a comment to a sheet, right-click the sheet and select Insert Comment from the menu. Please enter your opinion in the space provided. Cell comments are directly related to specific cells and are shown by a red triangle in the cell’s corner. Hover your mouse over the cell to view the comment.
14. Do you know how to protect your Excel data? If that’s the case, how?
There are three ways to do this, which are: Creating passwords to access the workbook. Adding, removing, or hiding sheets (make sure you know how to unhide the sheet for future reference). Protecting the sheet against changing window sizes or positions.
15. In Microsoft Excel, how many cell types are possible?
Microsoft Excel 2003 supports 4,000 distinct cell format combinations, while Excel 2007 and later support 64,000. This figure includes any unique combination of formats, such as one cell prepared with Arial font and pink fill colour and another cell formatted with Arial font and blue fill colour.
16. How can you change the formatting of a cell in Excel?
Calibri (black) is the default typeface for all cell content in a worksheet. This, however, can be altered by choosing any cell and then selecting the font dropdown option from the Home tab. You can modify the font, size, and colour of your text and make it bold, italic, or underlined. Other possibilities include: – Cell fill colours. – Cell Borders. – Different cell styles. – Automatic number formatting (including currency and percentage symbols). – Proper alignment. – Automatic text formatting (for example, modifying the appearance of dates and times).
17. What is the significance of the red triangle in the upper right corner of a cell?
A red triangle in the upper right corner of a cell shows that a cell comment is associated with it. The comment appears when you hover your cursor over the cell. Return to the “How do you insert a comment” section above to review the process.
18. What purpose does an Absolute Cell Reference serve in Excel?
Absolute cell referencing is the direct inverse of relative cell referencing. You can make a cell reference fixed (or “absolute”) by adding a $ symbol to the row number and column letter (e.g., A1). This implies that when you copy and paste it into another cell or use AutoFill, the cell references will remain unchanged.
19. What is the purpose of the dollar symbol in Microsoft Excel?
When you put a dollar sign, Excel determines whether or not to modify the location of the reference if the formula is copied to other cells. You can also make only the row or column absolute ($A1, or A$1), which will allow a piece of a reference to be changed while pasting or AutoFilling.
20. What is the shortcut for Excel’s Data Filter?
Ctrl+Shift+L is a shortcut for turning filters on or off. To display the filter dropdown menu, use the key Alt+Down Arrow.
21. What is the distinction between a function and a formula in Excel?
Formulas are written instructions for performing calculations in Excel. All computations in a spreadsheet will be written as formulas (for example, =C3+C4+C5+C7+C8). Functions, on the other hand, are Excel features that consist of prewritten formulas. Excel contains approximately 500 built-in functions that allow users to perform complex calculations without having to construct the formula themselves or type it out completely.
22. How can you clear cell formatting without deleting text?
Go to the Home tab, click the Clear button, and then select the Clear Formats option. Any formatting applied to the cell will be erased without altering the text.
23. How do you input the current date and time on an Excel spreadsheet?
The =NOW() function will return the current date and time. If you only want to enter the current time and not the date, use the keyboard shortcut Ctrl+Shift+semicolon, and for the current date without time, hit Ctrl+Semicolon.
24. What is a dashboard?
A dashboard is used to convey crucial information in graphical form. It is beneficial for showing large amounts of data on a single computer screen, allowing it to be evaluated quickly and important insights to be derived. Color-coded gauges are frequently used in dashboards to indicate whether metrics are within intended bounds or whether a deeper dive into a certain issue is required.
25. How do you separate data in a column into two or more columns?
The Text to Column option on the Data tab could be used to divide the original column into two or more columns. You can separate by the number of characters or by delimiting (searching for a certain indicator). A common example is breaking down a full name like “(Shashank Chaudhary ” into two parts: the first name, which is ” Shashank,” and the last name, which is ” Chaudhary.”
26. What is an Excel chart, and how many different types of charts exist?
A chart in Excel is a function that allows you to display data using a variety of visually appealing graphs. These charts and graphs can make it easier and faster to understand data than simply glancing at the numbers on the spreadsheet. Excel offers the following charts:
- Bar graphs
- Line Graphs
- Pie charts.
- Area graph.
- Scatter graphs.
- Surface Graphs
- Doughnut Graphs
- Radar charts
Each of these charts has pros and cons, depending on the story you want to tell your audience. Make sure you have a purpose for selecting a specific chart, and it is not based on emotion or randomness.
27. What is a macro in Microsoft Excel?
A macro is a sequence of Excel activities that have been recorded, stored, and named for future use.
A macro can then be invoked whenever necessary to finish the series of tasks, saving the user from having to repeat each step manually. This saves time and effort when doing repetitive activities with big amounts of data. You may, for example, need to change a data collection in the same manner every week, but it requires 15 steps to do. A macro allows you to manipulate data in a consistent manner with a single click of a button.
28. How do you standardise the formatting of a workbook’s sheets?
Formatting is automatically transferred to all selected sheets, so to apply a single format to all sheets in a workbook, simply hold down Ctrl (or Cmd on Mac) while selecting each page. This approach will only copy formatting adjustments, not data.
29. What is a “relative cell address”?
A relative cell address (or relative cell reference) is a cell address in a formula that does not begin with a $ symbol before the row number or column letter. This means that when you copy the formula one column over, the cell address changes accordingly.
The formula will calculate relative to its current position rather than from the original position. This is why creating formulas with absolute cell references is critical, as you do not always want your equations to roll ahead automatically.
30. What is a drop-down menu?
A drop-down list allows the designer of a spreadsheet to control the data entered into certain cells. In other words, if the developer only wants one of four planned options to be utilised, rather than depending on possibly unreliable user inputs (misspellings, non-standard categories), they can assure that it is followed by having users select an option from a drop-down menu. Good inputs equal good outputs, and eliminating the need for manual entry will lead to better analysis in the long run.
31. How do you use the Name Box functionality?
The Name Box is an input box located above the Excel sheet and to the left of the formula bar. Its default mode shows the address of the presently chosen cell, but it also has other purposes.
To begin, the Name Box can be used to swiftly choose a specific cell or range of cells: entering in a cell reference, such as G8, will immediately travel to cell G8, or typing in a range of cells, such as G8:G30, will select all cells within that range.
Second, the Name Box can be used to create a named range, which can then be used as a drop-down menu to switch between your many named ranges.
32. What is Microsoft Excel’s LOOKUP function?
The LOOKUP function allows the user to locate precise or partial matches in the spreadsheet. The VLOOKUP option allows the user to look for data in the vertical position. The HLOOKUP option works in the same way but on a horizontal plane.
33. Is it possible to use several data formats in pivot tables?
Data can be imported from several sources by going to the Data tab and clicking Get External Data > From Other Sources.
Excel worksheet data, data feeds, text files, and other data types can be imported; however, before you can use the imported tables to construct a pivot table, you must first create relationships between them and those in your worksheet.
34. How does one make a hyperlink in Excel?
To build a link in Excel, choose the element you want to use as the anchor. You can then proceed in a variety of ways:
- Select Link from the Insert tab.
- Right-click and select Link from the menu.
- Hit Ctrl + K. This will display a list of alternatives in which you can choose the type of content you want to link to, such as a file, a web page, a geographical location, or an email address.
35. How can you discover the day of the week for a specific date in an Excel spreadsheet?
You can accomplish this by utilising the WEEKDAY function. First, enter the Formula WEEKDAY(A1,1) into the appropriate cell. The second option (1) specifies what day Excel will assign the value. Press Enter, and the Formula will return 3, indicating that your date is the third day of the week.
36. What are the advantages of employing the SHEET Formula?
When working in a large workbook with numerous sheet tabs, it can be useful to ensure that you are looking at the correct sheet and have not missed any hidden tabs. The SHEET formula yields the number of the sheet you’re on. Thus, the second sheet tab would produce a value of 2. This is useful as an organisational tool.
37. How do you create a pivot table?
- Click the Data tab in the ribbon section.
- Then click the Data Validation button in the Data Tools category.
- Navigate to Configurations > Allow > List.
- Choose an array for the source list.
38. What is the default value for the final parameter of the VLOOKUP function?
If the last parameter is not given as TRUE or FALSE, the return value will be TRUE (approximate), indicating an approximate match for your request. This is usually not what you want to accomplish, as the primary use case for VLOOKUP is to locate an exact match.
39. How does one update a pivot table?
There are numerous approaches to refresh a pivot table without starting from scratch:
- From the pivot table tools menu, pick Analyse, followed by Refresh.
- Right-click the pivot table and select Refresh.
- Press the keyboard shortcut Alt+F5.
You may refresh all pivot tables in a workbook at once by clicking the arrow next to the Refresh option in the pivot table tools menu and then selecting Refresh All.
40. Which is the most typical Excel error message?
The most common error message in Excel is the #### error message, which appears when a cell is not large enough to display all of the data entered into it. To resolve this problem, simply slide the cell to extend its width or depth.
Other typical mistakes include #VALUE!, which occurs when a portion of the data you are attempting to analyse is in a different format than the remainder, and #NAME!, which occurs when the Formula was erroneously constructed and is not processing properly.
41. How do you discover duplicate values in a column in Excel?
To find but not eliminate duplicates in a column, pick the relevant range of data and then go to the Style group on the Home tab. Click the arrow next to “Conditional Formatting.”
You can then select Highlight Cell Rules and Duplicate Values and input the values you want to identify as duplicates. This will show duplicates of the values you submitted but will not change the data itself.
42. What are the macro languages in Excel?
Macros for Excel are written in VBA. You can use VBA to type out macro actions manually, or you can use Excel’s Macro Recorder to “record” activities and have the software convert them to VBA code for you. Once you’ve mastered VBA, you can go over recorded macros and make your own adjustments to fine-tune your usage scenarios.
43. How does one extract unique values in Excel?
To locate unique values, choose the relevant range of data and go to Data > Sort & Filter > Advanced. To permanently erase duplicate data and generate a list of unique values alone, go to Data > Data Tools > Remove Duplicates.
44. How does one make Cross-Tabulation in Excel?
The result of Excel’s pivot table function is sometimes referred to as a contingency table or cross-tabulation in other applications. On the Insert tab, select the PivotTable option to create a “crosstab”.
45. What exactly are freeze panes?
The Freeze Pane feature in Excel locks rows or columns of a worksheet so that they are always visible. This can be found on the ribbon’s View tab. This is typically useful when you wish to maintain column headers or the row identifier visible while scrolling right.
46. What is the definition of conditional formatting?
Conditional formatting is a strategy that allows us to identify the qualities (conditions) of a cell’s contents that are important to us and highlight cells or ranges that fulfil those criteria. When a cell is updated with fresh information, the conditional formatting detects and updates itself.
For example, if you intended to apply a green highlight to any cell containing the letter Z and then changed Randolph’s name to Ziggy, the conditional format would be applied automatically.
47. What is the limit of the VLOOKUP function?
The VLOOKUP function can only go in one direction: from left to right. As a result, the information you’re looking for must be in the column to the right of the lookup value’s location. VLOOKUP’s successor, XLOOKUP, has been added to recent versions of Excel.
This new function can be used in any direction and defaults to exact matches rather than approximate. XLOOKUP will eventually totally replace VLOOKUP, but not until the bulk of users have transitioned away from older versions of Excel.
48. Does VLOOKUP accept case-sensitive values?
VLOOKUP is not case-sensitive, and it will always return the first value in the match, regardless of the case. In other words, VLOOKUP will treat the name Apgar and the abbreviation APGAR as the same.
It is feasible, however, to trick VLOOKUP into providing case-sensitive values by utilising a helper column or sorting your data in ascending or descending order so that the desired value is always the first encountered by VLOOKUP.
49. How do the INDEX and MATCH functions in Excel work?
Two MATCH functions can be used within an INDEX formula to define a cell reference and return its value. The dynamic formula will return the corresponding data for any two MATCH values you enter.
For example, if you have a database that contains the price per unit and the number of units sold for a range of products, you may use the match index function to retrieve specific information about a single product.
50. What are the differences between the COUNT, COUNTA, COUNTBLANK, and COUNTIF functions?
COUNT: This function counts how many cells in a given range contain numerical data. It will not count any cells that are blank or solely contain text or symbols.
- COUNTA: This function determines how many cells within a given range contain data of any type. It will count all non-blank cells.
- COUNTBLANK: This function counts the number of blank cells in the specified range.
- COUNTIF: This function will only count cells whose values fulfil the user-specified condition.
51. What Is Data Validation?
Data validation limits the types of values a user can enter into a single cell or a set of cells. It once again works to reduce user input errors and keep data input tidy.
52. How do you calculate Excel percentages?
To see the percentage, select the destination cell.
Then, type a “=” sign.
Type in your reference, such as A1/A2, then press the Enter key.
Click the Home tab and select the % symbol from the numbers group. This transforms the value to a percent format.
53. Can you create shortcuts for Excel features?
Yes. It is possible to customise the ‘Easy Access Toolbar’ above the home button to display your most often used shortcuts.
54. What are the alignment options for left, right, fill, and distributed?
The left/right alignment positions the cell’s contents to the left and right.
Fill the cell with the same text repeatedly, as the name says.
Distributed distributes the text across the width of the cell.
55. How to use advanced filters?
Using Advanced Filters, you can extract a unique list of items or a specific item from many worksheets. It’s possible that the Advanced Filter is a more advanced version of Auto Filter.
56. What is an Excel Array Formula?
An array allows a user to execute many computations on multiple elements using a single formula. For example, rather than writing a formula for each calculation, you can put =A1:A10*B1:B10 in cell C1, and the results will stream down the C column.
This is referred to as a multi-cell array since the results will be stored across numerous cells. You can also create a single-cell array if you want to total all of the values calculated by the preceding array. This would look like: =SUM(A1:A10*B1:B10), which would produce a single value calculated by combining the results of all ten multiplications together.
57. How does one take the first name from a full name in Excel?
The FIND function will return the target’s numerical location (where the first character of the text is 1). The LEFT function can then extract the number of characters indicated by the FIND function from the start of the text (on the left).
However, the value given by FIND will contain the space itself, so we must remove 1 from it to get the true ending point of the first name. The formula would be like this:=LEFT(A1,FIND(” “,A1)-1).
A second way would be to split the first and last names and place them in separate new columns, using the Text to Columns function in the Data tab.
We discussed this before in the section on how to separate information in a column. The Text to Columns dialogue box will let you select the delimiter that separates each field (e.g., space) and display a preview of the results. The final step will allow you to select where you want the results to be presented.
58. What’s the distinction between a function and a subroutine in VBA?
It is the function’s responsibility to return the value of the mission it executes. Subroutines, on the other hand, do not return the importance of the assignment they complete. Functions are utilised as formulas in spreadsheets. Subroutines are not utilised directly as formulas in spreadsheets.
59. What is the difference in VBA between thisworkbook and the activeworkbook?
ThisWorkbook provides the name of the workbook where the code is running. ActiveWorkbook is, as the name says, the workbook that is now active among the several open workbooks.
60. How can I easily return to a certain area of a worksheet?
Using the name box is a quick method to navigate back to a certain portion of the worksheet. To return to a specific region of a worksheet, enter the cell address or name of the range in the name box.
61. How would you manage circular references in Excel?
Circular references are when a formula in a cell refers to itself, either directly or indirectly. Excel will display a warning notice if it detects a circular reference, which might cause the programme to calculate indefinitely without producing a result. It is, therefore, vital to address the circular references promptly in order to avoid them interfering with other formulas. To recognize and correct circular references:
- Navigate to the “Formulas” tab.
- In the “Formula Auditing” section, select “Error Checking.”
- To discover where they exist, select “Circular References”.
Check and change the formulas in the highlighted cells to eliminate circularity.
Advanced MS Excel Interview Questions and Answers For Career Success
Excel is one of the most often asked advanced MS Excel interview questions, and it is not intended to catch applicants off guard or determine whether they “know the correct answer.”
Instead, they are utilised in conjunction with portfolios and previous experiences to assess a candidate’s experience and software skills. These questions are not “hard” per se, but they are frequently highly technical.
As a result, if you are familiar with the software and prepare thoroughly for your interview, you will have a good chance of succeeding!
