Categories
Microsoft Excel

5 Commonly Asked Excel questions in Job Interviews

Today, every employer expects that you have some advance knowledge of Microsoft Excel, though it may not be the primary function of your job. Advanced excel skills can certainly boost your chances to grab that vacancy. We have selected the Five commonly asked Excel questions in Job interviews:

1: What is a Vlookup?

Vertical Lookup or Vlookup is an Excel function which retrieves or finds the value a specific data from a big list of data organized in a table array. It is a lookup formula which allows you to fetch data based on a criterion.

This Excel function ‘looks up’ from, a particular value in the left-hand column of a table array and retrieves the corresponding value that is assigned to it from another column of the array.

The vlookup syntax looks like this:

VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

Excel VLOOKUP Step 1 - Dynamic Web Training
Click image to enlarge 

Where these parameters mean:

lookup_value is the cell that you have defined to look up. It is the cell D2 in the top image.

table_array is the whole data array or a part of the table. (The table array is A: B)

col_index_num is the column number of the table that you want to retrieve the value from, and that is 2 in the above image.

[range_lookup] is a function that deciphers the exact value or the nearest approximate depending on the command. The [range_lookup] can be set to 0 or FALSE for exact match and 1 or TRUE for the closest value.

The result:

Excel VLOOKUP Step 2 - Dynamic Web Training
Click image to enlarge 

2: Do You Know to Create Charts?

Charts in Excel are of great help for visual reference. Excel has included different type of charts.

The standard charts are – Line chart, Pie Chart, Columns, and Bars. There are also other representations like Area, Scatter, Stock, Doughnut, Surface, etc.

You can find charts by clicking ‘Insert’ and then the Chart button.

Create Excel Chart- Dynamic Web Training
Click image to enlarge 

If someone asks to find the charts by one click, you have a shortcut for that.

Press F11

The default chart is the column chart.

Excel Column Chart - Dynamic Web Training
Click image to enlarge 

You can choose other charts too. For example : The Line Chart

Excel Line Chart - Dynamic Web Training
Click image to enlarge 
Excel Line Chart 2 - Dynamic Web Training
Click image to enlarge 

Here is another option – Pie chart.

Excel Pie Chart - Dynamic Web Training
Click image to enlarge 

3: What are Pivot Tables?

A Pivot Table gathers and summarizes the data from a detailed spreadsheet or a range of a spreadsheet. This table allows you to glance on the information such as:

Number of items based on each data type;

Sum of a column based on specific data types;

Average of the column based on particular data types.

Excel Pivot Table - Dynamic Web Training
Click image to enlarge 

Excel pivot table also has an added feature to extract relevant data from any part of the summarized pivot table.

If you click on sales representative- Brown’s name, you get his sales details only.

Excel Pivot Table - Dynamic Web Training
Click image to enlarge 

4- Do You Know How to Remove Duplicates?

Removing duplicate cells from the spreadsheet is relatively simple. Surprisingly many Excel users don’t know the technique and spend hours using that with Ctrl + F option.

Excel Remove Duplicate 1 - Dynamic Web Training
Click image to enlarge 
Excel Remove Duplicate 2 - Dynamic Web Training
Click image to enlarge 

Select the whole column from which you want to remove the duplicates. Go to the ’Data’ tab and click Remove Duplicates.

Excel Remove Duplicate 3 - Dynamic Web Training
Click image to enlarge 
Excel Remove Duplicate 4 - Dynamic Web Training
Click image to enlarge 

Click OK. The duplicate numbers are removed.

Excel Remove Duplicate 5 - Dynamic Web Training
Click image to enlarge 
Excel Remove Duplicate 5 - Dynamic Web Training
Click image to enlarge 

It is a simple step, but many don’t know about it.

5- Do you know the ways of Protecting the Books and the Sheets?

Protection is key, and if you don’t know how to protect the files, it will not go down well with the interviewer.
There are three levels of protection in Microsoft Excel:

1- Protecting the complete work book

You can protect your entire workbook by putting a password.

Excel Encrypt Password - Dynamic Web Training
Click image to enlarge 

Go to ‘File’ and then ’ Info.’ Choose ‘Encrypt with Password’ and enter a password.

Click Ok, and your book is protected. Nobody can open the file without your password.

Excel Encrypt Password 2 - Dynamic Web Training
Click image to enlarge 

2- The spread sheet:

To protect the spreadsheet in Excel, you click the ‘Review’ Tab and then the ‘Protect Sheet.’

Excel Protect Sheet 1 - Dynamic Web Training
Click image to enlarge 
Excel Protect Sheet 2 - Dynamic Web Training
Click image to enlarge 

Enter a password and click OK. The spreadsheet is now protected.

3- Locking the sheet and making few cells editable:

If you want to make the cell editable in protected spreadsheet selectively, Make a selection of the cells you want to be made editable. Select the Font down arrow and unlock the cells and hit OK as shown below:

Excel Lock Sheet 1 - Dynamic Web Training
Click image to enlarge 
Excel Lock Sheet 2 - Dynamic Web Training
Click image to enlarge 

These are the three protection tips used in Excel to protect your files.

Now you are aware of the commonly asked question; you should do well. There are other topics as well you should be aware of like- Organizing Data, Converting rows to Columns, Finding Top Bottom Performance, Dropdown Lists and Printing all data on one sheet.

To learn more about excel, you can check our excel courses.