– Excel Interview Questions –
Microsoft Office software such as Word, Excel, and PowerPoint are utilized by millions of small enterprises, freelancers, and entrepreneurs worldwide and given the popularity of these programs, it makes sense to have a basic understanding of how to use them effectively.
In certain jobs, dealing with these applications on a regular basis is a big part of the job.
This instruction can also be utilized by interviewers who want to assess the candidates’ Excel proficiency using the Excel interview questions below.
Excel Interview Questions
We will look at Excel Interview Questions in the order of Basic to Advanced
1) What is Microsoft Excel, and how does it work?
Microsoft Excel is a spreadsheet application that allows users to enter, arrange, compute, and process data using formulae on a spreadsheet system divided into rows and columns.
It also gives you the option of using an external database to perform analysis, generate reports, and so on, saving you a lot of time.
2) Explain Microsoft Spreadsheet and its Basics
Microsoft Spreadsheet can be compared to a paper ledger sheet. It comprises rows and columns and their intersection called cells.
A spreadsheet is computer software that can store and manipulate data in a table.
It is compatible with different operating systems, including Windows, IOS, and Android.
MS Spreadsheet has some of these features:
➺ Permit Data Analysis via tables, charts, filters
➺ Visual Simple Application Accessibility (VBA)
➺ Graphing tools
➺ Enables fast validation of data
➺ Integrated features (SUM, DATE, COUNTIF)
➺ Worksheet process and versatile workbook
3) How many data formats are available in Excel?
Eleven data formats are available in Microsoft Excel for data Storage. Example:
1. Number – Stores data as a number
2. Currency – Stores data as currency
3. Accounting – They line up the currency symbols and decimal points in a column.
4. Time – Time formats display date and time serial numbers as date values.
5. Fraction – They display the cell value as fractions.
6. Scientific – The cell values are displayed scientifically.
7. Special – Special formats are useful for tracking lists and database values.
8. Date – Data is stored as dates
9. Percentage – Stores numbers as a percentage
10. Text Formats – Stores data as a string of texts
11. Custom – Type the number format code, using one of the existing codes as a starting point.
4) Specify the order of operations used for evaluating formulas in Excel
In Microsoft Excel, the order of operations is the same as in traditional mathematics.
The name “PEMDAS” or “BEDMAS” is used to describe it.
➺ Parentheses or Brackets
➺ Exponent
➺ Multiplication
➺ Division
➺ Addition
➺ Subtraction
5) Which are the two major languages employed in MS-Excel?
XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM.
VBA was introduced in Excel 5 and is mostly used presently.
6) How can you sum up the Rows and Column numbers quickly in the Excel sheet?
By using the SUM function, you can get the total rows and columns, in an Excel worksheet.
7) Explain a few useful functions in Excel
Following are the in-built functions used in Excel for manipulating data:
➺ Math and Financial Functions – SQRT, DEGREE, RAND(), GCD
➺ Logical Functions – IF, AND, FALSE, TRUE
➺ Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
➺ Index Match – VLOOKUP and INDEX MATCH
➺ Pivot tables
RELATED READS:
8) What does a red triangle at the top right of a cell indicate?
The red triangle indicates that there are some comments in the cell. On mouse hover, you can read the expanded comment.
9) What is the use of NameBox in MS-Excel?
Name Box is used to return to a particular spot of the worksheet by typing the range name or cell address in the name box.
10) What is the benefit of using formulas in an Excel sheet?
Calculating the numbers in an Excel sheet, not only helps you to give the final sum of the number but, also automatically calculates the number replaced by another number or digit.
Through an Excel sheet, the complex calculations are made easy like percentage deduction or averaging the student’s score.
11) What is the “What If” condition in Excel formulas?
The “What If” condition is used to change the data in Microsoft Excel formulas to give different answers.
For example, You are buying a new house and wants to calculate the exact amount of tax that will be levied on it, you can use the “What If” function.
For instance, there are three cells A4, B4, and C4. Cell A4 is about the amount, Cell B4 talks about the percentage (7.5%) of tax and the final cell C4 will then calculate the exact amount of tax.
12) How can you disable the automating sorting in pivot tables?
To disable the automating sorting in pivot tables,
Go to > “More Sort Options”> Right Click “Pivot table” > Select “Sort” menu > Select “More Options” > Deselect the “Sort automatically when the report is created.”
13) What is the AND function does in Excel?
Like IF function, AND function also does the logical function. To check whether the output will be true or false the AND function will evaluate at least one mathematical expression located in another cell in the spreadsheet.
If you want to see the output of more than one cell in a single cell, it is possible by using AND function.
Example: If you have two cells, A1 and A2, and the value you put in those two cells are >3 and you want the result should display as ‘TRUE’ in cell B1 if value>3, and ‘False’ if any of those values<3. You can use AND function to do that.
14) How cell reference is useful in the calculation?
In order to avoid writing the data again and again for calculating purposes, cell reference is used. When you write any formula, for a specific function, you need to direct Excel to the specific location of that data.
This location is referred to as, a cell reference. So, every time a new value is added to the cell, the cell will calculate according to the reference cell formula.
15) How would you clear all the formatting without removing the cell contents?
You may want to remove all the formatting (colours, borders, font styling, etc.) and just have plain simple data according to preference. It can be done by clearing all the formatting in Excel.
To do this, you need to use the ‘Clear Formats’ option, which can be found in the Home tab in the Editing group. It becomes visible when you click on the ‘Clear’ dropdown.
Note there are other options as well – such as clear content, clear comments, and clear Hyperlinks. In case you want to clear everything – use the ‘Clear All’ option.
16) What is conditional formatting?
Conditional Formatting allows you to format a cell based on the value in it. For example, if you want to highlight all the cells where the value is less than 30 with a red colour, you can do that with Conditional Formatting.
You can read more about Conditional Formatting here.
17) How would you highlight cells with duplicate values in them?
It can simply be done by using conditional formatting. Here are the steps:
➺ Select the data in which you want to highlight duplicate cells.
➺ Go to the Home tab and click on the Conditional Formatting option.
➺ Go to Highlight Cell Rules and click on the ‘Duplicate Values’ option.
18) How can you make text invisible in Excel?
There are multiple ways to do this:
You can simply make the font white and it will appear as if it’s invisible.
OR, You can change make the text invisible by changing the custom format and here are the steps to do this.
➺ Select the cell, press Control + 1 (hold the control key and press 1).
➺ This will open the Format Cells dialogue box.
➺ In the Custom option, type ;;; in the custom options field. This will make the text invisible (but it will still be there).
19) What is the difference between a function and a formula in Excel?
A formula is a user-defined expression that can calculate a value.
While a function is a pre-defined built-in operation that can take the specified number of arguments.
A user can create formulas that can be complex and can have multiple functions in it.
For example, =B1+B2 is a formula and =SUM(B1:B10) is a function.
You can find detailed explanations of the most popular Excel functions here.
20) What are the different types of errors you can encounter in Excel?
When working with Excel, you can encounter the following six types of errors:
➺ #N/A Error: This is called the ‘Value Not Available’ error. You will see this when you use a lookup formula and it can’t find the value (hence Not Available).
➺ #DIV/0! Error: You’re likely to see this error when a number is divided by 0. This is called the division error.
➺ #VALUE! Error: The value error occurs when you use an incorrect data type in a formula.
➺ #REF! Error: This is called the reference error and you will see this when the reference in the formula is no longer valid.
This could be the case when the formula refers to a cell reference and that cell reference does not exist (happens when you delete a row/column or worksheet that was referred to in the formula).
➺ #NAME ERROR: This error is likely to be a result of a misspelt function.
➺ #NUM ERROR: Number errors can occur if you try and calculate a very large value in Excel. For example, =194^643 will return a number error.
21) Which function would you use to get the current date and time in Excel?
The following functions can be used:
TODAY() – This function takes no argument and would return the current date value.
NOW() – This function takes no argument and would return the current date and time value.
Remember that dates and times are stored as numbers in Excel. So you can perform operations such as addition/subtraction with these dates.
22) How can you combine the text from multiple cells using a formula?
To combine text from different cells, you can use any one of the following three methods:
➺ TEXTJOIN function – If you’re using Office 365 subscription, you will have the TEXTJOIN function available in your version. Click here to read how it works.
➺ CONCATENATE function – If you want to combine values in cells A1 and A2, you can use the formula =CONCATENATE(A1, A2)
➺ Ampersand (&) operator: This works just like the CONCATENATE function. To combine text strings in cells A1 and A2, use the formula =A1&A2
You can read more about joining strings in Excel here.
RELATED READS:
23) What is the syntax of the VLOOKUP function?
VLOOKUP is definitely one of the most popular Excel functions. And this is also one of the most asked Excel questions in interviews.
Here is the VLOOKUP syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
➺ lookup_value – this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
➺ table_array – this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains scores for everyone for every subject
➺ col_index – this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
➺ [range_lookup] – here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match.
24) What is a Pivot Chart?
When you create a Pivot Table, you see a summary of your data. You can also plot this summary in a chart that’s connected to the data.
One big benefit of using a Pivot Chart is that it updates when you change the Pivot Table layout.
For example, if you have a number of Users by region, and you update the Pivot Table to show Users data for each Service in the regions, the Pivot Chart would accordingly update.
25) What are the differences between Pivot Charts Vs Regular Charts?
While Pivot Charts have the ability to update when the Pivot Table updates, they are not as flexible as the regular charts.
On the whole, you can do a lot of personalizations in a regular Excel chart, but not in a Pivot chart.
Moreso, if you customize a Pivot Chart, and then update the Pivot Table, you are likely to lose the customization.
Despite the limitations, Pivot Charts are useful and can help create quick views from a Pivot Table.
26) Can you group dates in Pivot Tables?
You can easily group these into the following segments:
➺ Years
➺ Quarters
➺ Months
➺ Weeks
➺ Days
➺ Hours / Minutes / Seconds
The option to group data in Pivot Table is in the Analyze tab, which becomes visible when you select a cell in the Pivot Table in the Rows area.
You can read more about grouping dates in Pivot Table here.
27) Excel Charting Questions
These are the questions that will likely be asked from Charting with Excel.
To enable graphical representation of the data in Excel, charts are provided.
A user can use any chart type, including column, bar, line, pie, scatter, bullet chart etc. by selecting an option from the Insert tab’s Chart group.
Below are some common Excel interview questions about charting that you might get asked in an interview.
a) What is a Column chart?
A column chart is made up of vertical bars that is used to compare values over time or two compare values in different categories.
For example, you can use it to see how the sales have done over the years. Or you can use it to compare which product category has done better sales.
Since you can see all the vertical bars at one go, it is easier to visually see and compare.
You can also create clustered column charts, where you can have multiple columns for the same category or year (something as shown below).
b) What is a Bar chart?
A bar chart is made up of horizontal bars that are used to compare values in different categories.
For example, you can use it to compare which product category has done better sales. Or what has been the response of a survey.
c) What is a Line chart?
Line charts are useful when you want to show a trend over the years (or other time periods such as weeks, months, or quarters).
You can have multiple lines in a line chart. This would allow you to compare different categories over the same period of time (something as shown below).
d) What is a Scatter chart?
A scatter chart is used to compare two sets of values. For example, you can have data of different products on two KPIs, and you can plot the data on a scatter chart (as shown below).
This allows you to see what products are doing well on both the KPIs (the top right quadrant) or doing bad on both the KPIs (bottom-left quadrant).
RELATED ARTICLES:
e) What is a Pie chart?
A Pie chart is a type of graph in which a circle is divided into sectors that each represent a proportion of the whole.
f) What is a Waterfall chart and when is it used?
A waterfall chart shows different values (positive and negative) that lead to the final result value. For example, if you’re analyzing company net income, you can have all the cost components shown in the waterfall chart.
This will help you visually see how the value from revenue to net income is obtained when all the costs are deducted.
g) What is a Bullet chart?
Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target.
One of the best things about bullet charts is that they pack it full of information and take little space in your report or dashboards.
Note that bullet charts are not a default chart type in Excel, and you need to pass through some processes to create these.
You can read more about bullet charts here.
28) Excel Data Analysis Questions
Below are some common Excel interview questions about Data Analysis that you might get asked in an interview.
a) How to replace one value with another in Excel?
You can replace one value with another using the FIND & REPLACE feature in Excel.
To do this,
➺ select the data set and use the keyboard shortcut – CONTROL H (hold the control key and then press H).
This will open the Find & Replace dialogue box.
➺ In this dialogue box, you can specify the value you want to change and the replacement value.
If you’re interested in learning more about Find and Replace, click here.
b) What kind of data filters are available in Excel?
In Excel, you can filter a data set based on the kind of data.
The following types of data filters are available in Excel:
➺ Text Filter
➺ Number Filter
➺ Date Filter
You can apply a filter to a data set, by selecting the data, then clicking the Home tab and clicking on the Filter icon.
When you have tabular data and you apply filters, based on the data in the column, Excel shows you the relevant filter.
For example, if you have text data, it will show you filters related to the text (such as text contains, begins with, ends with, etc.).
c) How can you sort data in Excel?
There is a sorting feature in Excel that can sort data based on text, numbers, or colours.
Here are some ways to sort data in Excel:
➺ Select the data and click on one of the two sort icons in the Data tab.
➺ Select the data and click on the Sort icon. It will open the sort dialogue box and you can specify the column to sort and the criteria (ascending/descending).
➺ Apply data filter, and click on the filter. Along with the filter options, it also shows the data sorting options.
You can read more about data sorting here.
d) How can you transpose a data set in Excel?
There are two popular ways to transpose data in Excel:
➺ Using Paste Special dialogue box.
➺ Using the Transpose Function.
With Paste Special dialogue box, you need to first copy the data that you want to transpose, select the cell where you want to paste it, right-click and go to Paste special, and select the Transpose option (as shown below).
You can read more about transposing data in Excel here.
e) How can you select all blank cells in Excel?
If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA).
To do this, you need to use the Go To Special dialogue box in Excel.
Here are the steps to select all blank cells in Excel:
➺ Select the entire data set (including blank cells)
➺ Press F5 (this opens the Go To dialogue box)
➺ Click the ‘Special’ button (this opens the Go To special dialogue box)
➺ Select Blanks and click OK (this selects all the blank cells in your data set)
You can read more about selecting blank cells in Excel here.
f) How can you remove duplicates from a data set?
Excel has an in-built functionality that allows you to remove duplicate cells/rows in Excel.
You can find the option to remove duplicates in the Data tab.
Here are the steps to remove duplicates in Excel:
➺ Select the data.
➺ Click the Data tab and then click on the Remove Duplicates option.
➺ In the Remove Duplicates dialogue box, if your data has headers, make sure the ‘My data has headers’ option is checked. Select the column from which you want to remove duplicates.
➺ Click OK
You can read more about removing duplicates in Excel here.
g) What is an Advanced Filter in Excel?
Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.
Here are some differences between the regular filter and the Advanced filter:
While the regular data filter will filter the existing data set, you can use Excel advanced filter to extract the data set to some other location as well.
Excel Advanced Filter allows you to use complex criteria. For example, if you have sales data, you can filter data on a criterion where the sales rep is Bob and the region is either North or South.
You can read more about Excel Advanced Filter here.
29) What is a Ribbon and where is it found?
The Ribbon, which is located at the top of the Excel window, is essentially your main Excel interface. It gives users quick access to many of the most important commands.
It has numerous tabs, such as Paper, House, Display, Insert, and so on.
You can also customize the ribbon to meet your specific requirements. To customize the Ribbon, right-click on it and select “Customize Ribbon”.
RECOMMENDED!!!
• Journalist Interview Questions
• ZS Associates Behavioral Interview Questions
• What Do You Do in Your Spare Time Interview Questions
30) How to add a note to a cell?
➺ Right-click on the cell and select it from the drop-down menu to add a note.
➺ Then choose New Note and enter whatever note you like.
➺ If you want to erase the Note, repeat the process and select the Delete Note option.
➺ With a red triangle in the upper right corner of the cell, the Notes show.
In a nutshell, it’s very important to know the nitty-gritty of working with Excel as they’ve become a readily available tool that breaks down complex and copious amounts of data into something comprehensible.
To this end, we have brought you the 30 best excel interview questions and answers for your interview and hope it gives you quality pointers.
If this article was informative and you love to look up some more when they are published, don’t forget to subscribe to stay updated.
Kindly provide us with your phone number and email address in the comment box below.
Also, share with your friends and family on Facebook, Twitter, WhatsApp and Google plus to keep them updated with the latest information.
StudentsandScholarship Team.
Be the first to comment