Top 50 Microsoft Excel Interview Questions

1. Top 50 Microsoft Excel Interview Questions | Analytics TrainingHub op 50 Microsoft Excel Interview Questions these are all questions and Answers that give an A+ gradein interviews. MS Excel is the most widely used spreadsheet application in use. The analytical and calculus capabilities of this application have made this application extremely popular with every professional in the corporate or at a mid-scale level in today’stime. Since its initial launch in 1985 MS Excel has come a long way and with constant updates rolling out each quarter. It has become one of the most loved applications with the large community fanbase of alltime. tfaving a keen understanding and implication of this application holds the key to cracking multiple interviews where the use of MS Excel is highly desired andrecommended. Below are a set of Microsoft Excel Interview Questions that might be asked during an interview about the implications of MSExcel:- List of Top 50 Microsoft Excel Interview Questions: Q1. What is MicrosoftExcel? It is an electronic spreadsheet application launched by Microsoft in the year 1985. It authorizes the user to store, manage, analyze, and influence data with the use of functions and formulas using the spreadsheet systembroken

2. into rows and columns. It is also compatible with other databases making it an all-around and time-savingapplication. Q2. Definecells? The crossway where a row and column intersect on a worksheet is referred to as a cell. The rectangular spaces in the excel sheet which take in data are referred to as a cell. There are about 10,48,576 rows x 16,384 columns = 17,17,98,69,184 Arab cells in oneworksheet. Q3. Describe aSpreadsheet? Spreadsheets also known as Worksheet are a collection of cells that help in data management. OneWorkbook may comprise several worksheets. Worksheets may be given a name to distinguish when searching for some particular data. These may be visible at the bottom of the sheet. Detailed introduction of MicrosoftExcel Q4. Define CellAddress? The denomination obtained when you click a random cell in the worksheet, which denotes the letter of the column number and the number of the row respectively is referred to as a cell address or cell number.E.g. Q5. How to add cells to thespreadsheet? To do so, just right-click on the cell where you wish to add the cell and the following dialog box shall popup In this dialog box select the Insert option and the following dialog box shall pop up, in which you can select your preference of adding the cell and click ‘OK’ to confirm yourchoice. Q6. How can a user format cell in MSExcel? To format any cell, the user can right-click on the cell and would be shown a drop-downmenu.Similartotheoneshowninthepicturebelow

3. From the drop-down menu, the user needs to choose the ‘Format Cells’ option and would be displayed a dialog box as shownbelow The first formatting option would be the ‘Number’ tab which allows the user to portray numbers in special types like currency, date, time, percentage, fraction,etc. The second tab is the ‘Alignment’tab Which allows the operator to align, text control, and change the direction in which the text was maybe written. The next tab is the ‘Font’tab which permits the user to set the default printing font style and the one that is displayed on thescreen. The next tab the‘Border’ Permits the user to change or modify the borders of the cell to be colored, changed, or maybe even removedpermanently. The ‘Fill’tab Enables the user to choose distinct colors and stylesto fill up the cells. Finally, last but not least the ‘Protection’ tab allows the operator to lock or hide any chosencell. Q7. Describe if a user can add comments to a celland how? A user may add comments to a cell by selecting the cell and right-clicking on the cell which brings a drop-down menu and selecting the comments option as shown in the picturebelow The comments would be visible to all the people with whom the file may be shared. Q8. How can a user add complete rows and columns to a sheet?

4. The procedure for doing so is similar to right-clicking in the desired location and choosing the insert option, but unlike the cell option, the user may select the bottom two options for inserting complete rows or columns respectively as displayed below in the insert dialogbox. • Q9. Describe what is a Ribbon in MSExcel? • The ribbon is the most important kit in your arsenal of dealing with data in MS Excel and appears at the top of each spreadsheet. The Ribbon permits the user to gain direct access to multiple commands that aid in managing, sorting, and visualizing data in MS Excel. The Ribbon consists of tabs that allow the user to customize the data as per his/her requirement. tfere are a fewexamplesoftheribbonwithdifferenttabsandtheirfeatures:- • Q10. Describe why and how may a user freeze pane in MS Excel? • The feature of freezing panes in MS Excel helps the user to make headings of rows and columns visible even when scrolling deep in an excel sheet either vertically down or horizontally sideways. To freeze panesin MS Excel:- • Theuserneedstoselecttherows/columnsthatneedtobefrozen • Click on the arrow next to the ‘Freeze Panes’ option from the ribbon of the “View” taband • Choose from the three options, just as showcased in the picturebelow • Q11. How can a user ‘Wrap text’ in MSExcel? • To do so the user may choose the ‘Wrap text’ option from the home tab after selecting the cell in which the user wishes to wrap the text as shown in the seriesbelow • Q12. How can the user prevent data from being copied by someoneelse? • To protect data being copied from the spreadsheet that you have shared with the users, follow thesesteps:-

5. Go to the ‘Review’ tab on the Excelribbon Choose ‘ProtectSheet’ Choose the criteria that the user of the sheet is allowed todo Enter the password and press ‘OK’, this is how the criteria box looks like Q13. Define charts in MS Excel and how to employthem? Representation of data into graphs or charts is something that MS Excel allows its users to employ with ease. A user may use different styles of chart formats to make complex data look visually simple and understandable to its viewers. This feature may be accessed by going to the ‘Insert’ tab of the ribbon and choosing the style of the chart the user wishes to display the data. Below is an example of thesame On selecting the type of chart, the user wishes to utilize, click on ‘OK’ and the data will be showcased in a chart format, like the one shownbelow Q14. How can a user sum up numerical values in rows or columnsquickly? This feature may be accessed by the user with the click of a button, on the ‘tfome’ ribbon tab in the editing pane, as mentioned in thebelow pictures Q15. How can a user apply a single format to all the sheets in aWorkbook? To apply the same format in all the sheets of a workbook, the user would need to follow the necessarysteps:- Right-click on a sheetname Choose the ‘Select all Sheets’option Make format changes on any one sheet and it would be applied tothe rest of the sheets as well, below is a picture showing the option for selecting all thesheets

6. Q16. Describe the steps to resize one or multiplecolumns? • The easiest way to do this is to select the column you wish to resize and drag the mouse horizontally sideways to adjust the size of the column, for multiple columns whilst pressing the ‘CTRL’ button select the column the user wishes to resize and for all the columns click on the diagonal arrow to the left of columnA. • The other way isto:- • Select yourcolumns • Go on the tfomeribbon • Go to the cellspane • Choose Format and select the ‘Column width…’option • Feed-in the number and the selected columns would beadjusted • Q17. Describe how a user might merge cells inExcel? • To do so follow thesesteps:- • In the home ribbon under the Alignmentpane • once the user has selected the cells that they wish tomerge • Click on the ‘Merge & Center’ button which will display a drop-down menu • From the drop-down menu, the user may select:- • Merge &Center • MergeAcross • MergeCells • UnmergeCells • The option displayed looks like the picturebelow • Q18. Mention the steps for highlighting cells with negative values? • This can be done by adhering to the followingsteps:-

7. The user needs to choose the cells they wish to highlight the negative values • On the ‘tfome’ tab, go to the ‘Styles’pane • In the ‘Styles’ pane, click on the down arrow in the ‘Conditional Formatting’slab • Choose the ‘tfighlight Cells Rules’option • Select the ‘Less than’option • Feed the value as ‘0’ and the color scheme in which the user wishes the discrepancy to behighlighted. • The following would showcase the followingresult • Q19. Describe the order of operations used when evaluating formulas inExcel? • The following id the order used to evaluate formulas inExcel:- • Parenthesis(Brackets) • Exponentiation(^) • Multiplication or Division – both have equal precedence and is evaluated on whichever comesfirst • Addition or Subtraction – both hold equal precedence and isevaluated • depending on whichever comesfirst • The easiest way to remember this operation is the abbreviation PEMDAS or BEMDAS, which is the initial letter of each operator in the order of their application. • Q20. Describethe difference between a function and a formula inExcel? • A function is a pre-established operation in MS Excel that can take a specified number of arguments. Whereas Formula is a user-defined expression used to calculate avalue. • An operator may fabricate a complex formula that may have multiple functions integrated intoit.

8. For e.g., =A1+A2 is a formula and =SUM(A1:A10) is afunction. • Q21. What may be defined as the top functions of MSExcel? • This question is often put forward to understand the comfort of the applicant with the MS Excel functions. There are around 450+ functions in MS Excel, but there are a few noteworthy functions, which as an operator of MS Excel one should know about and be fluent with the integration and implications of these functions to manipulatedata:- • VLOOKUP • COUNTIF • SUMIF • IFERROR • INDEX/MATCtf • SUMPRODUCT • TEXT • AVERAGE • LEN/LEFT/RIGtfT/MID • SUM • Q22. Describe how may a user tackle errors while working with Excelformulas? • TherearemultiplewaysinwhichausermaytackleerrorsinMSExcel:- • The user may highlight the errors with the help of ‘Conditional Formatting’ in the styles pane of the tfome tab, by choosing the ‘ISERROR’ option within the ‘Conditional Formatting’pane • The user may employ the ‘IFERROR’ function to get a specific value in case the formula sends back anerror. • The user may also utilize the ‘ISERROR’ function to extrapolate a ‘TRUE’ or ‘FALSE’ to validate the presence of anerror • Q23. Describe the functions that may be implied to get the current date and time inExcel?

9. The below-mentioned functions may beemployed:- • This function returns the current date value without taking any arguments–TODAY() • This function returns the current date and time value without taking any arguments –NOW() • Another point for the user to keep in mind that date and time are coded as numbers in Excel, so addition and subtraction may be made tothese. • Q24. Which formula can the user employ to find out the length of a text string in acell? • The user may use the ‘LEN’ functionto investigate the length of the text string in acell • Q25. Describe the PivotTables? • These are statistical tables that reduce data to it is the bare minimum. The dial down may display fields such as sums, sales, etc. which pivot tables can showcase in a smarter & simplermanner. • Pivot table features are asfollows:- • A precise showcase of data that the user wishes toanalyze • Provide numerous perspectives of viewingthe data • Provide the user with attention to crucialfacts • Comparison of data isimperative • Pivot tables are capable of detecting patterns, trends,etc • Provides instant fabrication ofdata • Precisereports • Pivot chart’s foundationalblock • Q26. Describe the process of providing a dynamic range in “Data Source” of pivottables?

10. This can be achieved by providing or creating a ‘Named Range’ using the offset function and use the name to base the pivottable. • Q27. How can the user make a Pivot table with numerous sources ofdata? • This is possibleonly if the multiple sources of data are from different worksheets of the same workbook. • Q28. Name the event employed to check if any modification was made in the PivotTable? • To make this inquiry,all that the user needs to do is utilize the ‘PivotTableUpdate’eventintheworksheetcomprisingthepivottable. • Q29.Describetheprocesstohowcantheuserdisable automatic sorting in pivottables? • The user may follow thesesteps:- • Got to ‘More SortOptions’ • Right-click on ‘PivotTables’ • Choose the ‘SortMenu’ • Pick ‘MoreOptions’ • Ditch the ‘Sort automatically’option • Q30. How can the operator stop the pivot table from losing the column width afterrefreshing? • The user may stop the loss of format in pivot tables post refreshing by changing the options for the pivot table. To accomplish this, the user under the ‘Pivot Tables Option’ needsto:- • Switch on the ‘Enable Preserve Formatting’and • Switch off the ‘AutoFormat’option’s • Q31. How can a user calculate percentagein Excel?

11. Percentages are the ratios that are calculated as a fraction of 100. In literal terms, • Percentage = (Part/Whole) x100 • In MS-Excel to get a %age value, theuser would:- • First, need to employ the formula =(Part/Whole) into the necessary fields • Then to implicate it as a %age the user needs to right-click on the selected cells and choosing the ‘Format cells…’option • The user then needs to go to the ‘Number’ tab in thedialog box • Choose ‘Percentage’ and click on‘OK’ • On applying this format, the data will be showcased in the% format. • Below is a pictorial representation of the process involved. Another way of doingthis:- • After entering the percentileformula • The user may go on the ‘tfome’ribbon • In the home ribbon, go to the ‘Number’pane • Choose ‘%’ after selecting the cells the user intends on employing the format • Q32. Can a user calculate Compound Interest inExcel? • Yes, to calculate interest in Excel the user can make use of the ‘FV’ function which has certain arguments that need to be fed in the function to get the desiredresult. • So, it showcasesas • =FV(rate, nper, pmt, [pv],[type]) • To find the rate= interest rate/compound years, nper= Years*compoundyear • pmt= any value (includingzero)

12. Below is an example of computing compoundinterest • Q33. Can the user find averages inexcel? • The user may use the =AVERAGE function to get the average for a set of numbers. • Please find the below-displayedexample • Q34. Describe the ‘LOOKUP’function? • The ‘LOOKUP’ function is generally employed to summon a value from an array ofdata. • Q35. Describe the VLOOKUP function inExcel? • It is a function that permits the operator to summon data from a given range. The letter V in VLOOKUP stands for vertical and implies that the data needs to be structured vertically. This function comes in handy when looking for a single piece of data from a pool ofdata. • Q36. Describe the functioning of the VLOOKUPfunction? • This function works by picking up the data displayed in the default argument from the left side of the screen and then moving towards the data showcased in the same field towards the right in the same row where the argument was found to match the remaining arguments fed in the VLOOKUP function. The function showcases the followingarguments • =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Where • lookup_value denotes the value indemand • table_array denotes the range from where the data needs to be summoned • col_index_num identifies the column from which the user intends the value to beraised

13. [range_lookup] is a plausibleTRUE or FALSE value, where TRUE searches for the closest match and FALSE searches for the exactmatch • Please find the below-mentioned example below showcasing the VLOOKUP feature to lookup the city of the buyer for order ID number 102 • Q37. Describe the ‘What if analysis inExcel? • The ‘What if the analysis is the technique of performing modifications to one or more formulas present in the cells to witness how the alterations to those formulas cause an effect in the rest of the sheet. There are 3 types of ‘What if’ tools inExcel:- • Datatables • Scenario’s • Goalseek • Data Tables and scenarios take a set of inputs to check for potential results. Data Tables can work with just 1 or 2 variables but may accept several different values for every one of those variables. Whereas Scenarios may work with many variables but are limited to a maximum of 32values. • Q38.DifferentiatebetweenSUBSTITUTEandREPLACE functions inExcel? • The REPLACE function switches part of the text string with another set of text. =REPLACE(old_text, start_num, num_chars,new_text) • The SUBSTITUTE function substitutes one or more instances of old text with the new text in astring. • =SUBSTITUTE(text, old_text, new_text,[instance_num]) • Q39.DifferentiatebetweenCOUNT,COUNTIF,COUNTA, and COUNTBLANK inExcel?

14. COUNT is the function in MS Excel utilized to count the cells which hold numericdataminustheblanksinthespecifiedselection. • COUNTIF & COUNTIFS are functions for counting cells that hold numeric data with arguments in the specifiedselection. • COUNTA is also known as CountAll is a function used to count any cell with numeric data in the sheetand • COUNTBLANK is the function used to count the cells with empty strings or blankcells. • Q40. Describe the ‘IF’ function inExcel? • The ‘IF’ function is performed to execute a logictest. The function is responsible for checking whether the specified condition is TRUE or FALSE. If the search meets the true criteria the result would be shown accordingly, but if the search does not meet the criteria then the results would not match the arguments. • Q41. Describe Volatilefunctions? • These functions are responsible for recalculating the worksheet every time there are changes made to the worksheet. If the user is operating with nominal or little data it would not be bothersome, but if the user is dealing with large amounts of data then these functions may substantially increase the run time of these functions and the load time of the results which have conspired due to the changes made in theworksheet. • tfere are a fewexamples:- • tfighly volatile – TODAY(), RAND(),NOW() • Almost volatile – OFFSET(), CELL(), INDIRECT(),INFO() • Q42. How can a user quickly switch between worksheets without the use of themouse?

15. The operator may use the ‘CTRL + PAGE DOWN’ command if he/she is on the first sheet and the command ‘CTRL + PAGE UP’ if on the last sheet of theworkbook. Q43.Howcanauserdeterminethedayoftheweek according to thedate? The function which allows users to get information about the day of the week according to the date is the ‘WEEKDAY’function. Q44.Describethebenefitsofemployingformulasina worksheet? Using formulas in excel sheets is not only the best way to compute numbers but because of volatile functions, it recalculates the sheet every time any value which is part of the initial calculation is changed. The use of formulas not only eases the calculation part but enhances the efficiency of the way tasksarehandledandcompletedwiththeuseofMSExcel. Q45.HowcantheoperatorcreateshortcutsforExcel functions? The ‘Quick Access Toolbar’ above the home button can be customized to showcase the most frequently used Excel functions. Q46. Which filter may an operator use if he/she wishes to analyze a list using databasefunctions? The user may use the ‘Advanced Criteria Filter’ in the list or if more than two conditions need to be testedout. Q47.Howcanauserreturntoaspecificareaofa worksheet? The quickest way is to type the cell address in the ‘NameBox’.

16. Q48.Describethebenefitofcellreferencingduringthe calculation? • On the fabrication of a formula for a specific function, the user may direct Excel to the specific location of the data for which the formula is being entered. The referring of the cell consisting of the data is known as ‘Cell Referencing’ • Q49. Shortcut for auto-sum ofrows/columns? • The shortcut is employed by coming to the end of the field of data and pressing the ‘ALT + =’ signs, as shown in the picturebelow • Q50. How can a user remove duplicate entries in adataset? • To employ this task, the user needs to select the data set that he/she wishes to check andthen:- • Go to the Dataribbon • In the data toolspane • Choose the ‘Remove duplicates’option • Ensure that the ‘My data has headers’ option is checked if that is the case • Select the column from which the duplicates needto be removed • Click on‘OK • Some useful links arebelow: • To know more about the Microsoft Excel certification course visit – Microsoft Excelcertification • Toknowmoreabout-EssentialExcelskillsforaDataAnalyst • Must visitour official youtube channel to Learn more & Grow your Knowledge - Analytics Training hub