Excel Interview Questions

  1. What is Microsoft Excel?

Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and manipulate the data with formulas using a spreadsheet system broken up by rows and columns. It also provides the flexibility to use an external database to do analysis, make reports, etc. thus saving lots of time.

 

  1. What is ribbon?

Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus.  The ribbons have various tabs on the top, and each tab has its own group of commands.

 

  1. Explain Spreadsheet and its Basics.

Spreadsheet can be compared to a paper ledger sheet. It consists or rows and columns and their intersection called cells.

 

  1. How many data formats are available in Excel? Name some of them.

Eleven data formats are available in Microsoft Excel for data Storage. Example:

  • Number – Stores data as a number
  • Currency – Stores data in the form of currency
  • Date – Data is stored as dates
  • Percentage – Stores numbers as a percentage
  • Text Formats – Stores data as string of texts

 

  1. Specify the order of operations used for evaluating formulas in Excel.

The order of operations in Microsoft Excel is same as in standard mathematics. It’s defined by the term “PEMDAS” or “BEDMAS”.

  • Parentheses or Brackets
  • Exponent
  • Multiplication
  • Division
  • Addition
  • Subtraction

 

  1. How can you wrap the text within a cell?

You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell.

 

  1. Explain Macro in MS-Excel.

Macros are used for iterating over a group of tasks. Users can create macros for their customized repetitive functions and instructions. Macros can be either written or recorded depending on the user.

 

  1. What is a function in Excel?

If you’re a frequent Excel user, functions are probably second nature to you. You’ve used SUM, AVERAGE, and even VLOOKUP so many times that you don’t even think about what a function actually is when you’re creating spreadsheets.

 

As such, this question may come as a surprise to you — especially because describing what a function is can be a bit difficult if you never think about it.

Here’s an easy way to do it: think of a function like a recipe. It’s used to combine a bunch of ingredients — which may not taste particularlly good individually — into something much more useful.

The name of the function is like the title of the recipe. It describes what the function does, like take a SUM or an AVERAGE.

The arguments of the function describe what ingredients go into it. Individual functions can take any number of arguments, from one to an infinite number. It all depends on the function.

 

Finally, the output of the function is what comes out the other side: a useful quantity that can show you important data, or be used as an input to other functions in your spreadsheet.

 

Advanced Excel Training Course 

 

  1. In your opinion, what are a few of the most useful functions in Excel? How do you use them?

 

This is a tricky question, because it asks you to use your subjective judgement rather than answering objectively. As such, you’ll have a wide range of latitude in your response — and you should have a well thought-out reply prepared that demonstrates both your proficiency with Excel and your wide range of past experience using spreadsheets.

Here are a couple of our top recommendations for features, formulas, and functions to discuss:

INDEX MATCH. VLOOKUP and INDEX MATCH are two of Excel’s most important and commonly-used functions. As veteran Excel users know, they’re used to look up values from an external table, and are important parts of automating your work with dynamic spreadsheets. One of the two is bound to come up in any Excel interview, but if you get this question, we recommend bringing up INDEX MATCH. It’s a slightly more useful function, and also lacks many of the disadvantages of VLOOKUP, like the inability to insert new rows and columns into your sheets. If you go with this function, outline how you’ve used it in the past to dynamically lookup values and populate columns of data that would otherwise need to be manually copied and pasted.

IF statements. IF statements are another staple of any Excel veteran’s arsenal. Bring them up to let your interviewer know that you’ve created advanced spreadsheets that make decisions based on criteria calculated in real-time.

PivotTables. PivotTables are an extraordinarily useful tool, and if you’re applying for a job that requires intermediate or advanced Excel knowledge, they’re sure to be an important criteria used by your interviewer. You may have used PivotTables for any number of things in the past, but be sure to emphasize how useful they are when quick, accurate calculations are necessary based on large sets of data with hundreds or thousands of rows.

 

  1. What is the syntax of the VLOOKUP function? Are there any disadvantages to using this function?

 

VLOOKUP is one of the most commonly-used functions in the business world, and if you’ve got an Excel interview, you’ll almost certainly be asked about it. Be sure you’re properly prepared for your interview by memorizing the syntax of VLOOKUP by heart:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

VLOOKUP’s arguments can be described as follows:

lookup_value defines a value to look up within an external table of data.

table_array defines a table of data in which to look up the lookup_value. The lookup_value must appear within the first column of this table.

col_index_num tells the function how many columns into the table_array to look for a value to return.

range_lookup is a TRUE / FALSE switch that tells the function whether to look for an exact match, or an approximate match, to the specified lookup_value.

As for disadvantages — VLOOKUP does have one crucial one: since the col_index_num is usually entered manually into the function, VLOOKUP can break if columns of data are inserted into the table_array after the function is written. This may not seem like a major disadvantage, but can cause hours of frustration over broken formulas if you’re not careful.

To fix the problems that sometimes occur when columns of data are inserted, consider replacing your VLOOKUP formulas with INDEX MATCH, which contains similar functionality with none of the disadvantages.

 

  1. What is the difference between absolute and relative cell references? In which situations would you use each?

As many Excel users know, one of the most magical features of Excel is the cell reference. Cell references allow users to include the values of external cells in formulas dynamically — rather than hard-coding particular values manually.

However, cell references can be confusing when copied and pasted to different locations. By default, Excel uses relative cell references, which change dynamically as they are copied and pasted around a sheet. For example, if a reference to cell A1 is copied and pasted one row down and one column to the right, the new reference will point to cell B2. This allows users to perform similar calculations on different ranges of cells quickly and easily.

In contrast, absolute cell references do not change when they are copied and pasted to other locations within a sheet. Absolute cell references can be used on either rows, columns, or both at the same time, and are indicated using the $ sign. For example, if a reference to cell $A$1 is copied and pasted one row down and one column to the right, the new reference will point to cell A1 — it won’t change at all, because both the row and column are locked. If a reference to cell $A1 is copied and pasted one row down and one column to the right, the new reference will point to cell A2 — only the row number will change, because the column letter is locked.

 

  1. What is a PivotTable, and when would you use one? What are the key PivotTable ‘sections’ into which users can drag columns?

As one of the most-used Excel features in business settings, PivotTables are sure to come up during any in-depth Excel interview. Be sure you’re prepared in advance with a firm grasp of what exactly PivotTables are, and why they’re useful in practice.

 

Simply put, a PivotTable is a tool used to summarize large quantities of data quickly and easily. It can help you analyze a data set of tens, hundreds, or even thousands of rows with minimal effort using a number of pre-defined functions — like SUM, COUNT, and AVERAGE.

 

There are many use cases for PivotTables, but they’re most handy when you need to analyze a large data set quickly. If you’ve got high-level, one-off questions on a massive data set — for example, “how many cookies did we sell in February of last year”, or “which salesperson closed the most deals this March”, chances are a PivotTable is the perfect way to answer them.

Each PivotTable is composed of a number of key sections, into which the columns of a target data set can be bucketed:

Report filter. This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January.

Column labels. This section allows us to summarize data across columns, placing data labels along the top of the screen.

Row labels. This section allows us to summarize data across rows, placing data labels along the side of the screen.

Values. This section allows us to specify what we’re summarizing — for example, total sales or number of items ordered.

 

  1. Do PivotTables have any drawbacks? How can they be solved?

Of course, no Excel feature is without its drawbacks, and there’s a chance your interviewer will dive deeper into your PivotTable knowledge by asking you to explore some of their weaknesses. This will help the recruiter assess your in-depth experience on one of Excel’s most important features — after all, PivotTables can’t be used for everything!

If asked about the drawbacks of PivotTables, consider the following:

Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format — meaning that it’s broken down to it’s most granular level. If data is already summarized on a table, PivotTables may not be the best way to analyze it.

PivotTables need to be refreshed if input data changes. This can lead to confusing and errors when using PivotTables as part of larger dashboards.

PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you’ll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword!

As an alternative to PivotTables, consider using conditional summary functions like SUMIFS and COUNTIFS, particularly when constructing dashboards. They can produce similar results, but are less ‘fluid’ — making your results more predictable and easier to track.

 

  1. What are some best practices when creating complex models in Excel?

Excel can be used for simple calculations, but it’s most effective when constructing complex mathematical models that help predict outcomes, project financial results, or track data over time. If you’re interviewing for a highly analytical role, there’s a good chance your recruiter will ask about how you can use spreadsheets to accomplish these more difficult tasks.

When talking through your answer, be sure to mention the following modeling best practice, which help keep your spreadsheets clean, organized, and flexible:

Create multiple tabs. Keeping different pieces of your model (for example, inputs, outputs, and calculations) on separate tabs can help with model organization, particularly if you’re planning to hand your spreadsheet off to someone who has never seen it before.

Use dynamic inputs. When constructing a model in Excel, values should never be hard-coded into cells — especially if they are flexible assumptions that may change down the line. Always keep assumptions and inputs on their own tab, and use cell references rather than hard-coded values to pull them into your formulas.

Add a table of contents. Large models can be extraordinarily complex, and adding a table of contents to the beginning can help keep things organized and easy to use for yourself and your supervisor.

Comment aggressively. You are the person who understands your models the best, but other people in your organization will doubtless be using them, too. So, be sure to over-comment and explain your calculations line-by-line so that they are as easy to follow as possible for other users.

 

  1. Talk about some of the spreadsheets you’ve made that you’re most proud of.

This is also a softer, more subjective question. It doesn’t have to do with the features of Excel itself — rather, your interviewer may ask it to get a sense for your past experiences with spreadsheets and your enthusiasm for quantitative analysis.

Before walking into your interview, be sure that you have 2-3 examples of your prior spreadsheet use prepared so that you can answer this question. The more excited you are about these examples, the better; it’s likely that your interviewer is also trying to get a sense for the excitement and passion that you’d bring to the job if hired.

Here are some examples of applications of Excel that you might want to talk about, if applicable:

Constructing dashboards in Excel to measure and track business metrics;

Putting together cash flow or revenue projections over time;

Using Excel as a project management dashboard to track progress across multiple workstreams;

Automating day-to-day tasks using spreadsheets with IF statements and other conditional logic; or

Performing back-of-the-envelope calculations to estimate sales volume in various business scenarios.

 

  1. What are cells?

The area where data is stored is known as cell.

 

  1. Does each cell have unique address?

Yes, each cell has a unique address depends on the row and column value of the cell.

 

  1. How can you add cells, rows or columns in Excel?

If you want to add a cell, row or column in Excel, right click the cell you want to add to and after that select insert from the cell menu. The insert menu makes you able to add a cell, a column or a row and to shift the cells affected by the additional cell right or down.

 

  1. How would you format a cell? What are the options?

A cell can be formatted by using the format cells options. There are 6 format cells options:

Number

Alignment

Font

Border

Fill

Protection

 

  1. What is the use of comment? How to add comments to a cell?

Comments are used for a lot of reasons:

Comments are used to clarify the purpose of the cells.

Comments are used to clarify a formula used in the cell.

Comments are used to leave notes for others users about a cell.

To add a comment: Right click the cell and choose insert comment from the cell menu. Type your comment.

 

  1. What does the red triangle indicate at the top right hand corner of the cell?

The red triangle at the top right hand corner of a cell indicates that there is a comment linked to the particular cell. If you put your cursor on it, it will show the comment.

 

  1. How would you add comments to a cell?

To add a comment to a cell, you right click the cell and choose insert comment from the cell menu. Type your comment in the comment area provided. A red triangle at the top right hand corner of a cell indicates that there is a comment linked to that particular cell. To remove a comment from a cell, right lick the cell and then select delete comment from the cell menu.

 

  1. What are charts in MS Excel?

Charts are used to enable graphical representation of the data in Excel. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab?s Chart group.

 

  1. What is Freeze Panes in MS-Excel?

Freeze Panes are used to lock any row or column. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.

 

  1. Which are the different workbook protection types in Excel?

There are three ways to protect a workbook in Excel:

Password protection for opening a workbook

Protection for adding, deleting, hiding and unhiding sheets

Protection from changing size or position of windows.

 

  1. What is the difference among COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel?

COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.

COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.

COUNTBLANK count blank cells or cells with an empty string.

COUNTIF and COUNTIFS count cells matching a certain criteria.

 

  1. Is it possible to hide or show the ribbon?

You can hide or show (minimize or maximize) the ribbon by pressing CNTRL F1.

 

  1. How to prevent someone from copying the cell from your worksheet?

If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet > Password.

By entering password you can prevent your worksheet from getting copied.

 

  1. How to sum up the rows and column number quickly in the Excel sheet?

The SUM function is used to get the total sum of the rows and columns, in an excel worksheet.

 

  1. How can you resize the column?

There are two ways to resize a column:

To change the width of one column, drag the boundary on the right side of the column until you find your desirable width.

Select the Format from the home tab, and in Format, select the AUTOFIT COLUMN WIDTH under cell section. Click on this to change the cell size.

 

  1. What are the several report formats in Excel?

There are three report formats in Excel:

Compact

Report

Tabular

 

  1. Is it possible to make Pivot table using multiple sources of data?

If the multiple sources are different worksheets from the same workbook, then you can use these multiple sources of data to make Pivot table.

 

  1. How can you check whether the Pivot table is modified or not?

To check whether the Pivot table is modified or not, you should use the “PivotTableUpdate” event in worksheet containing the pivot table.

 

  1. What does the IF function in Excel?

IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.

 

  1. What filter should we use, if you want more than two conditions or if you want to analyze the list using database function?

You should use “Advanced Criteria Filter” to analyze the list or test more than two conditions.

 

  1. What are the advantages of using formula in Excel sheet?

Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the number replaced by another number or digit. It is used to make complex calculations easy.

 

  1. What is the order of sequence of operating mathematical operation in Excel?

The order of sequence is written as BEDMAS:

 

Brackets

Exponents

Division

Multiplication

Addition

Subtraction

 

  1. What is the use of LOOK UP function in MS Excel?

The LOOK UP function is used to return a value from an array.

 

  1. How many rows and columns are there in Microsoft Excel?

Rows: 1048576

Columns: 16384

 

  1. What is the simplest way to create a table to sort data in Excel?

Excel offers the format as table option on the home tab. To format data as a table with a heading that allows simple sorting of data, you select the range of data you want to format as a table and then select the format as table from the styles options on the home tab. The first row of the selected range can be assigned as the table headings. Table headings are used by Excel to allow you to simply sort data within the table. You can sort data alphabetically, by color, or by using number filters.

 

  1. Comments put in cells are called …..
    Smart Tip
    B. Cell Tip
    C. Web Tip
    D. Soft Tip

 

  1. Comments can be added to cells using …..
    Edit -> Comments
    B. Insert -> Comment
    C. File -> Comments
    D. View –> Comments

 

  1. Which menu option can be used to split windows into two?
    Format -> Window
    B. View -> Window-> Split
    C. Window -> Split
    D. View –> Split

 

  1. Getting data from a cell located in a different sheet is called….
    Accessing
    B. Referencing
    C. Updating
    D. Functioning

 

  1. Which of the following is not a valid data type in Excel?
    Number
    B. Character
    C. Label
    D. Date/Time

 

  1. Which elements of a worksheet can be protected from accidental modification?
    A. Contents
    B. Objects
    C. Scenarios
    D. All of the above
  2. A numeric value can be treated as label value if …… precedes it.
    A. Apostrophe (‘)
    B. Exclamation (!)
    C. Hash (#)
    D. Tilde (~)
  3. Concatenation of text can be done using
    A. Apostrophe (‘)
    B. Exclamation (!)
    C. Hash (#)
    D. Ampersand (&)
  4. Which area in an Excel window allows entering values and formulas?
    A. Title Bar
    B. Menu Bar
    C. Formula Bar
    D. Standard Tool Bar

 

  1. Multiple calculations can be made in a single formula using…..
    A. Standard Formulas
    B. Array Formula
    C. Complex Formulas
    D. Smart Formula

 

  1. An Excel Workbook is a collection of …….
    A.Workbooks
    B. Worksheets
    C. Charts
    D. Worksheets and Charts

 

  1. What do you mean by a Workspace?
    A. Group of Columns
    B. Group of Worksheets
    C. Group of Rows
    D. Group of Workbooks
  2. MS-EXCEL is based on ……….?
    A. WINDOWS
    B. DOS
    C. UNIX
    D. OS/2

 

  1. In EXCEL, you can sum a large range of data by simply selecting a tool button called …..?
    A. AutoFill
    B. Auto correct
    C. Auto sum
    D. Auto format
  2. To select an entire column in MS-EXCEL, press?
    A. CTRL + C
    B. CTRL + Arrow key
    C. CTRL + S
    D. None of the above

 

  1. Interviewer: How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?

You: We follow the steps in following order to provide Dynamic Range in ‘Data Source’ of Pivot Tables:

Create a Named range using Offset function.

Base the pivot table using the Named range created in Step 1.

 

  1. Interviewer: If you add either new rows or new columns to the pivot table source data, the pivot table is not updated even when you click on ‘Refresh Data’. Why and what is the solution?

You: This happens because the newly added data is outside the range of Pivot table’s underlying data. To cure this either provide dynamic range to the Pivot Table or manually update the pivot table’s source data.

 

  1. Interviewer: By any means can you repeat ‘row headings’ in the Pivot Table?

You: Yes, however this option is available in Excel 2010 or later.

 

  1. Interviewer: Is it possible to display the text in the data area of Pivot Table?

You: No, it’s not possible as we could only show text either in ‘Row Labels’ or ‘Column Labels’. However, if needed, we can show count of text records.

 

  1. Interviewer: Upon refreshing a Pivot Table, it always loses the formatting like the column width. How this can be corrected?

You: Format loss in Pivot Table can be stopped by simply changing the pivot table options. Go to “Pivot Table Options” and turn on the “Enable Preserve Formatting” and disable the “Auto Format” option.

 

  1. Interviewer: Can you change the default summary function for data from COUNT to SUM?

You: Unfortunately, we can’t change default settings of the data fields. However, by default, if any cell in the selected range is blank or text, it will be default to COUNT else to SUM.

 

  1. Interviewer: When you link to a pivot table cell, a GETPIVOTDATA formula is created. How would you avoid this?

You: In Excel 2007 or later, by disabling the ‘Generate GetPivotData’ and in previous versions by manually giving the cell reference instead of using the mouse pointer to locate the cell.

 

  1. Interviewer: How would you enable automatic refresh in Pivot Table upon opening the workbook without using macros?

You: This can be done from Pivot Table Options. Go to “Table Options” –> Data –> Select ‘Refresh data when opening the file.

 

  1. Interviewer: How can you hide the error values in data field of Pivot Table?

You: This can be done from Pivot Table Options. Go to “Table Options” –> “Layout & Format” –> Enable “For error values show:” and provide the value to be shown for error values. Leave it empty for Null values.

 

  1. Interviewer: Which 3 report formats for Pivot Tables are available in Excel 2007 or later?

You: Compact, Report and Tabular.

 

  1. Interviewer: How can you disable automating sorting in Pivot Tables?

You: Go to ‘More Sort Options’ after you right click on ‘Pivot Table’ and choose ‘Sort’ menu. Then click on ‘More Options’ and finally uncheck the ‘Sort automatically when the report is created.”

 

  1. Interviewer: Which event do you use to check if a Pivot Table is modified?

You: Event ‘PivotTableUpdate’ in worksheet containing that ‘Pivot Table’.

 

 

  1. Interviewer:Which option is used to add column(s) in Pivot Tables to compute the values in run-time?

For eg.if the underlying source table contains cost price and sales price, how would you compute ‘Profit’.

You:We can use ‘Calculated Field’ to dynamically insert formulated values.

 

  1. Interviewer: How will you check the memory consumed by your PivotTable in Kilobytes?

You: You can display the memory used by a pivot cache, by using the following VBA properties of Pivot Table. PivotCaches.MemoryUsed – It returns the total memory used in bytes. Divide the result by 1024 to have it in Kilobytes.

 

  1. What is the default value of last parameter of VLOOKUP?

TRUE/ It refers to finding the closest (approximate) match and assuming the table is sorted in ascending order. Whereas, FALSE/0 refers to exact match.

 

  1. What is the main limitation of VLOOKUP function?

The lookup value should be at the most left side column in the table array. VLOOKUP only looks right. It cannot look right to left.

 

  1. Does VLOOKUP look up case-sensitive values?

No, it is not case-sensitive. The text ‘ram’ and ‘RAM’ is identical for VLOOKUP.

 

  1. 2 ways to extract unique values in excel

Use Advanced Filter option (shortcut key : ALT D F A) and ‘Remove Duplicates’ option under Data tab.

 

  1. How to find duplicates in a column?

Use CONDITIONAL FORMATING to highlight duplicate values. OR use COUNTIF function as shown below. For example, values are stored in cells D4:D7.

=COUNTIF(D4:D7,D4)

Apply filter on the column wherein you applied COUNTIF function and select values greater than 1.

 

  1. How to insert a drop down?

Go to Data tab >> Select Data Validation. Another way to insert a drop down is to enable Developer tab and Insert Combo box.

 

  1. How to sum values based on some conditions?

Use SUMIF or SUMPRODUCT functions. The SUMIF function is explained below –

=SUMIF(range, criteria, sum_range)

=SUMIF(A2:A5,”A”,B2:B5)

Excel : SUMIF Function

 

  1. How to create cross tabulation in Excel?

Use Pivot Table and select one variable in Row label and the other variable in Column label.

 

  1. What is Excel Array Formula?

Excel Array Formula Explained

 

  1. How to extract First Name from a full name?

Suppose you need to pull ‘Neha’ from ‘Neha Sharma’. Use MID and FIND functions.

=MID (A2,1,FIND(” “,A2)-1)

Tutorial : Practical Uses of MID Function

 

  1. How Index and Match Function works?

Index function returns a value from a range based on row number.

= INDEX(range, row_number)

See the image below –

Excel : Index Function

In this case, we are telling EXCEL to return second value of the range A2:A4. It returns 30.

Match function returns the relative position of a value in range.

= MATCH(lookup_value, range, match_type)

match_type can be exact match, largest/smallest value that is less than or greater than equal to lookup_value.

Excel : Match Function

In this case, we are asking EXCEL to find the relative position of 30 in the range A2:A4. It returns 2.

 

  1. How Index and Match Function works together?

=INDEX(range, MATCH(lookup_value, lookup_range, match_type))

Suppose information of Product and Sales are stored in columns A and B. You need to look for product against sales value so you need to tell EXCEL to look from right to left as sales value is placed in the right hand side of the range/table.

=INDEX(A2:A5,MATCH(45,B2:B5,0))

Nested INDEX MATCH Excel Functions

Basic and Intermediate Statistics

The following questions touch upon some basics and intermediate statistics topics. These topics aregenerally taught in undergraduate / graduate courses.