Top 50 Microsoft Excel Interview Questions

1
Top 50 Microsoft Excel Interview Questions
Analytics Training Hub
op 50 Microsoft Excel Interview Questions these
are all questions and Answers that give an A
grade in 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 todays time. Since
its initial launch in 1985 MS Excel has come a
long way and with constant updates rolling out
each quarter. It has become one of the most
loved applications with the large community
fanbase of all time. 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 and
recommended. Below are a set of Microsoft Excel
Interview Questions that might be asked during
an interview about the implications of MS
Excel- List of Top 50 Microsoft Excel Interview
Questions Q1. What is Microsoft Excel? It is an
electronic spreadsheet application launched by
Microsoft in the year 1985. It authorizes the
user to store, manage, analyze, and influence
data with the use of functions and formulas
using the spreadsheet system broken
2
into rows and columns. It is also compatible with
other databases making it an all-around and
time-saving application. Q2. Define cells? The
crossway where a row and column intersect on a
worksheet is referred to as a cell. The
rectangular spaces in the excel sheet which take
in data are referred to as a cell. There are
about 10,48,576 rows x 16,384 columns
17,17,98,69,184 Arab cells in one worksheet. Q3.
Describe a Spreadsheet? Spreadsheets also known
as Worksheet are a collection of cells that help
in data management. One Workbook 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
Microsoft Excel Q4. Define Cell Address? 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 the
spreadsheet? To do so, just right-click on the
cell where you wish to add the cell and the
following dialog box shall pop up In this dialog
box select the Insert option and the following
dialog box shall pop up, in which you can select
your preference of adding the cell and click
OK to confirm your choice. Q6. How can a user
format cell in MS Excel? To format any cell, the
user can right-click on the cell and would be
shown a drop-down menu. Similar to the one shown
in the picture below
3
From the drop-down menu, the user needs to choose
the Format Cells option and would be displayed
a dialog box as shown below The first formatting
option would be the Number tab which allows the
user to portray numbers in special types like
currency, date, time, percentage, fraction,
etc. 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 the
screen. The next tab the Border Permits the
user to change or modify the borders of the cell
to be colored, changed, or maybe even removed
permanently. The Fill tab Enables the user to
choose distinct colors and styles to fill up the
cells. Finally, last but not least the
Protection tab allows the operator to lock or
hide any chosen cell. Q7. Describe if a user can
add comments to a cell and 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 picture below 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
  dialog box.
• Q9. Describe what is a Ribbon in MS Excel?
• 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 few examples of the ribbon with
  different tabs and their features-
• 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 panes in MS Excel-
• The user needs to select the rows/columns that
  need to be frozen
• Click on the arrow next to the Freeze Panes
  option from the ribbon of the View tab and
• Choose from the three options, just as showcased
  in the picture below
• Q11. How can a user Wrap text in MS Excel?
• To do so the user may choose the Wrap text
  option from the home tab after selecting the
  cell in which the user wishes to wrap the text as
  shown in the series below
• Q12. How can the user prevent data from being
  copied by someone else?
• To protect data being copied from the spreadsheet
  that you have shared with the users, follow
  these steps-

5

• Go to the Review tab on the Excel ribbon
• Choose Protect Sheet
• Choose the criteria that the user of the sheet is
  allowed to do
• Enter the password and press OK, this is how
  the criteria box looks like
• Q13. Define charts in MS Excel and how to employ
  them?
• 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 the same
• On selecting the type of chart, the user wishes
  to utilize, click on OK and the data will be
  showcased in a chart format, like the one shown
  below
• Q14. How can a user sum up numerical values in
  rows or columns quickly?
• 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 the below
  pictures
• Q15. How can a user apply a single format to all
  the sheets in a Workbook?
• To apply the same format in all the sheets of a
  workbook, the user would need to follow the
  necessary steps-
• Right-click on a sheet name
• Choose the Select all Sheets option
• Make format changes on any one sheet and it would
  be applied to the
• rest of the sheets as well, below is a picture
  showing the option for selecting all the sheets

6

• Q16. Describe the steps to resize one or multiple
  columns?
• The easiest way to do this is to select the
  column you wish to resize and drag the mouse
  horizontally sideways to adjust the size of the
  column, for multiple columns whilst pressing the
  CTRL button select the column the user wishes
  to resize and for all the columns click on the
  diagonal arrow to the left of column A.
• The other way is to-
• Select your columns
• Go on the tfome ribbon
• Go to the cells pane
• Choose Format and select the Column width
  option
• Feed-in the number and the selected columns would
  be adjusted
• Q17. Describe how a user might merge cells in
  Excel?
• To do so follow these steps-
• In the home ribbon under the Alignment pane
• once the user has selected the cells that they
  wish to merge
• Click on the Merge Center button which will
  display a drop-down menu
• From the drop-down menu, the user may select-

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 be
  highlighted.
• The following would showcase the following result
• Q19. Describe the order of operations used when
  evaluating formulas in Excel?
• The following id the order used to evaluate
  formulas in Excel-
• Parenthesis(Brackets)
• Exponentiation()
• Multiplication or Division both have equal
  precedence and is evaluated on whichever comes
  first
• Addition or Subtraction both hold equal
  precedence and is evaluated
• depending on whichever comes first
• 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. Describe the difference between a function
  and a formula in Excel?

8

• For e.g., A1A2 is a formula and SUM(A1A10) is
  a function.
• Q21. What may be defined as the top functions of
  MS Excel?
• This question is often put forward to understand
  the comfort of the applicant with the MS Excel
  functions. There are around 450 functions in MS
  Excel, but there are a few noteworthy functions,
  which as an operator of MS Excel one should know
  about and be fluent with the integration and
  implications of these functions to manipulate
  data-
• 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 Excel formulas?
• There are multiple ways in which a user may
  tackle errors in MS Excel-
• 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

9

• The below-mentioned functions may be employed-
• 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 to these.
• Q24. Which formula can the user employ to find
  out the length of a text string in a cell?
• The user may use the LEN function to
  investigate the length of the text string in a
  cell
• Q25. Describe the Pivot Tables?
• These are statistical tables that reduce data to
  it is the bare minimum. The dial down may
  display fields such as sums, sales, etc. which
  pivot tables can showcase in a smarter simpler
  manner.
• Pivot table features are as follows-
• A precise showcase of data that the user wishes
  to analyze
• Provide numerous perspectives of viewing the data
• Provide the user with attention to crucial facts
• Comparison of data is imperative
• Pivot tables are capable of detecting patterns,
  trends, etc

10

• This can be achieved by providing or creating a
  Named Range using the offset function and use
  the name to base the pivot table.
• Q27. How can the user make a Pivot table with
  numerous sources of data?
• This is possible only 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 Pivot Table?
• To make this inquiry, all that the user needs to
  do is utilize the PivotTableUpdate event in
  the worksheet comprising the pivot table.
• Q29. Describe the process to how can the user disa
  ble automatic sorting in pivot tables?
• The user may follow these steps-
• Got to More Sort Options
• Right-click on Pivot Tables
• Choose the Sort Menu
• Pick More Options
• Ditch the Sort automatically option
• Q30. How can the operator stop the pivot table
  from losing the column width after refreshing?
• The user may stop the loss of format in pivot
  tables post refreshing by changing the options
  for the pivot table. To accomplish this, the user
  under the Pivot Tables Option needs to-
• Switch on the Enable Preserve Formatting and
• Switch off the AutoFormat options

11

• Percentages are the ratios that are calculated as
  a fraction of 100. In literal terms,
• Percentage (Part/Whole) x 100
• In MS-Excel to get a age value, the user would-
• 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
  the dialog 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 doing this-
• After entering the percentile formula
• 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 in
  Excel?
• Yes, to calculate interest in Excel the user can
  make use of the FV function which has certain
  arguments that need to be fed in the function to
  get the desired result.

12

• Below is an example of computing compound
  interest
• Q33. Can the user find averages in excel?
• The user may use the AVERAGE function to get the
  average for a set of numbers.
• Please find the below-displayed example
• Q34. Describe the LOOKUP function?
• The LOOKUP function is generally employed to
  summon a value from an array of data.
• Q35. Describe the VLOOKUP function in Excel?
• It is a function that permits the operator to
  summon data from a given range. The letter V in
  VLOOKUP stands for vertical and implies that the
  data needs to be structured vertically. This
  function comes in handy when looking for a
  single piece of data from a pool of data.
• Q36. Describe the functioning of the VLOOKUP
  function?
• 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
  following arguments
• VLOOKUP(lookup_value, table_array,
  col_index_num, range_lookup) Where
• lookup_value denotes the value in demand
• 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 be raised

13

• range_lookup is a plausible TRUE or FALSE
  value, where TRUE searches for the closest match
  and FALSE searches for the exact match
• 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 in Excel?
• 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 in Excel-
• Data tables
• Scenarios
• Goal seek
• Data Tables and scenarios take a set of inputs to
  check for potential results. Data Tables can
  work with just 1 or 2 variables but may accept
  several different values for every one of those
  variables. Whereas Scenarios may work with many
  variables but are limited to a maximum of 32
  values.
• Q38. Differentiate between SUBSTITUTE and REPLACE
  functions in Excel?
• The REPLACE function switches part of the text
  string with another set of text.
  REPLACE(old_text, start_num, num_chars,
  new_text)
• The SUBSTITUTE function substitutes one or more
  instances of old text with the new text in a
  string.
• SUBSTITUTE(text, old_text, new_text,
  instance_num)
• Q39. Differentiate between COUNT, COUNTIF, COUNTA,
  and COUNTBLANK in Excel?

14

• COUNT is the function in MS Excel utilized to
  count the cells which hold numeric data minus
  the blanks in the specified selection.
• COUNTIF COUNTIFS are functions for counting
  cells that hold numeric data with arguments in
  the specified selection.
• COUNTA is also known as CountAll is a function
  used to count any cell with numeric data in the
  sheet and
• COUNTBLANK is the function used to count the
  cells with empty strings or blank cells.
• Q40. Describe the IF function in Excel?
• The IF function is performed to execute a logic
  test. The function is responsible for checking
  whether the specified condition is TRUE or FALSE.
  If the search meets the true criteria the result
  would be shown accordingly, but if the search
  does not meet the criteria then the results would
  not match the arguments.
• Q41. Describe Volatile functions?
• 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 the
  worksheet.
• tfere are a few examples-
• tfighly volatile TODAY(), RAND(), NOW()
• Almost volatile OFFSET(), CELL(), INDIRECT(),
  INFO()
• Q42. How can a user quickly switch between
  worksheets without the use of the mouse?

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
the workbook. Q43. How can a user determine the da
y of the week according to the date? The
function which allows users to get information
about the day of the week according to the date
is the WEEKDAY function. Q44. Describe the benef
its of employing formulas in a 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 tasks are handled and
completed with the use of MS Excel. Q45. How can t
he operator create shortcuts for Excel
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 database functions? The user
may use the Advanced Criteria Filter in the
list or if more than two conditions need to be
tested out. Q47. How can a user return to a specif
ic area of a worksheet? The quickest way is to
type the cell address in the Name Box.
16

• Q48. Describe the benefit of cell referencing duri
  ng the 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 of rows/columns?
• The shortcut is employed by coming to the end of
  the field of data and pressing the ALT
  signs, as shown in the picture below
• Q50. How can a user remove duplicate entries in a
  dataset?
• To employ this task, the user needs to select the
  data set that he/she wishes to check and then-
• Go to the Data ribbon
• In the data tools pane
• 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 need
  to be removed
• Click on OK
• Some useful links are below
• To know more about the Microsoft Excel
  certification course visit Microsoft Excel
  certification

17
(No Transcript)