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] )
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:
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.
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.
You can choose other charts too. For example : The Line Chart
Here is another option – Pie chart.
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 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.
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.
Select the whole column from which you want to remove the duplicates. Go to the ’Data’ tab and click Remove Duplicates.
Click OK. The duplicate numbers are removed.
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.
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.
2- The spread sheet:
To protect the spreadsheet in Excel, you click the ‘Review’ Tab and then the ‘Protect Sheet.’
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:
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.
