Pinterest Pixel

Top 7 Excel Interview Questions to Land Your Dream Job!

Microsoft Excel is the most frequently used application around the globe with over 800 million users. It... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Top 7 Excel Interview Questions to Land Your Dream Job!

Microsoft Excel is the most frequently used application around the globe with over 800 million users.

It is a must know software for anyone working on number crunching, data analysis, business reporting, charting, and various ad-hoc reports. If you are searching for a job that has “Microsoft Excel” as a prerequisite, you should be prepared to face a few Excel questions when you have your job interview.

Don’t be afraid as we are here to help you CRUSH these Excel interview questions!

This tutorial will guide you on the most popular Excel interview questions that will be asked by the interviewer when you are looking for jobs related to finance, accounting, auditing, budgeting, management, project management, administration, and sales, just to name a few.

Following are the Top 7 Excel interview questions that you need to prepare for to take you to the next round:

For some bonus tips on crushing your Excel interview questions, listen to our podcast below to get the best Excel interview tips from the Excel experts and MVP’s around the world:

Let’s look at each of these topics one-by-one!

Before you proceed forward, make sure you download this Excel Workbook and follow along:

DOWNLOAD EXCEL WORKBOOK

 

#1 – VLOOKUP Function

  • What are the arguments of a VLOOKUP function?
  • What are the limitations of this function?
  • What are the alternate functions that can be used to overcome the limitations?

 

VLOOKUP is the most used function and hence is most likely to be asked in your Excel interview questions.

It is used to look up the value in the left-most column of the data table and returns a value in the same row from another column (to the right) in the table array.

 

The syntax of VLOOKUP is:

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

where:

  • lookup_value – the value you want to look for in the 1st column of the data table
  • table_array – the data table
  • col_index_num – the column form which you want to derive the value
  • [range_lookup] – Approximate match (TRUE) or Exact match (FALSE). The default value is TRUE. This is an optimal argument.

 

In the example below, we have used the VLOOKUP function to extract the price of the item from the stock list:

See also  CountBlank Formula in Excel

Top 7 Excel Interview Questions to Land Your Dream Job!

To learn about the different ways you can apply the VLOOKUP Function in Excel, CLICK HERE for Excel help!

 

The limitations of this function are:

  • If the lookup value is not the 1st column of the data table, it cannot be used.
  • If you add or delete columns in the data table, the function will produce the wrong results.
  • Vlookup only finds the first match, ignores the rest.
  • The default value of the range_lookup argument is an approximate match.
  • Vlookup is not case-sensitive.

You can use either INDEX-MATCH or XLOOKUP to overcome these limitations. Learn more about these advanced functions and be prepared for your Excel interview questions.

 

#2 – Freeze Panes

  • How to freeze top row or 1st column in Excel?
  • How to fix column headings to keep them visible whilst we are scrolling down data?
  • How to freeze multiple rows in Excel?

 

You can easily freeze top rows or columns in Excel by following the steps below:

Freeze Top Row

Go to View > Freeze Panes > Freeze Top Row

This will freeze the Row 1 of your sheet:

How to Freeze Panes in Excel

Try scrolling down, the first row is always visible!

How to Freeze Panes in Excel

 

Freeze First Column

To freeze the 1st column of your data, Go to View > Freeze Panes > Freeze First Column.

Top 7 Excel Interview Questions to Land Your Dream Job!

The first column of your data will be frozen!

Top 7 Excel Interview Questions to Land Your Dream Job!

 

Freeze Multiple Rows

If you wish to freeze multiple rows (say from Row 1 to 5), select the 6th row and then go to View > Freeze Panes > Freeze Panes.

How to Freeze Panes in Excel

This will freeze all rows above Row 6!

How to Freeze Panes in Excel

You can go through the tutorial on Excel freeze panes by Microsoft by Clicking Here.

 

#3 – Remove Duplicates

  • How to get a unique list of customers from sales data?
  • How to remove duplicate value from a column?

 

Let’s use an example to understand how to answer these Excel interview questions.

To remove duplicates from a set of data, follow the steps below:

STEP 1: Select the column containing duplicate values.

Top 7 Excel Interview Questions to Land Your Dream Job!

 

STEP 2: Go to Data > Remove Duplicates.

Top 7 Excel Interview Questions to Land Your Dream Job!

 

STEP 3: In the Remove Duplicates dialog box, make sure the box for  “My data has headers” is checked and click OK.

Top 7 Excel Interview Questions to Land Your Dream Job!

 

Excel will remove the duplicate values and provide you with a unique list!

See also  Match Two Lists With The MATCH Function

Top 7 Excel Interview Questions to Land Your Dream Job!

 

#4 – Excel Tables

  • How to convert a range of data into Excel Tables?
  • What are the benefits of using an Excel Data Table?

 

Excel Tables are an excellent way to organize and analyze data accurately. It can be used to easily sort and filter data. There are numerous benefits of converting your data into Excel Tables

Converting a range into a formatted table can be done in a matter of seconds. Take the steps below to do so:

STEP 1: Select the Data.

excel interview questions

STEP 2: Go to Insert > Table.

Top 7 Excel Interview Questions to Land Your Dream Job!

STEP 3: In the Create Table dialog box, Click OK. (Make sure you check the box next to My Data has headers)

Top 7 Excel Interview Questions to Land Your Dream Job!

This is how your formatted Excel Data Tables looks like!

Top 7 Excel Interview Questions to Land Your Dream Job!

 

There are a plethora of benefits that you can utilize when you convert your data range into an Excel Table:

  • Structured referencing;
  • Many different built-in Table Styles with color formatting;
  • Use of a “Total Row” which uses built-in functions to calculate the contents of a particular column;
  • Dropdown lists that allow you to Sort & Filter;
  • Insert interactive buttons or Slicers, to filter your Table’s data;
  • When you scroll down from the Table, its Headers replace the Column Letters in the worksheet;
  • Remove Duplicate Rows automatically;
  • Summarize the Table with a Pivot Table;
  • Supports calculated Columns so you can create dynamic formulas outside the Table;

 

#5 – Excel Pivot Tables

  • What is an Excel Pivot Table?
  • Why do you need an Excel Pivot Table?
  • What are the features of Excel Pivot Tables?
  • How can you filter data in Pivot Table?

 

If you want to analyze your data quickly (without using Formulas) and create informative reports, an Excel Pivot Table is your answer!

Pivot Tables in Excel are one of the most powerful features within Microsoft Excel.  It allows you to analyze more than 1 million rows of data with just a few mouse clicks and show the results in an easy to read table.

 

Follow the steps below to learn how to create Excel Pivot Table and answer those Excel interview questions like a pro:

STEP 1: Select the Data.

Top 7 Excel Interview Questions to Land Your Dream Job!

STEP 2: Go to Insert > Pivot Table.

Top 7 Excel Interview Questions to Land Your Dream Job!

STEP 3: In the Create PivotTable dialog box, Click OK.
(Select whether you want Pivot Table in a New or Existing Worksheet)

Top 7 Excel Interview Questions to Land Your Dream Job!

This will create a Pivot Table structure for you. Simply drag and drop fields in the row and values section and your Pivot is ready!

See also  Vlookup Approximate Match in Excel

Top 7 Excel Interview Questions to Land Your Dream Job!

Click here to read about an interactive tutorial on the 50 different things you can do with an Excel Pivot Table!

 

Here are some of the advantages of using Pivot Table in your data:

  • It is very easy to create a summary report of your data.
  • It helps to analyze a large set of data with just a few clicks.
  • You can create a report without writing any formulas.
  • To see the transactions behind a Pivot Table value, simply double click on any value.
  • You can insert inetractive buttons or Slicers to filter your data.
  • You can add new data to your data source and Refresh your Pivot Table to see the new information in your Pivot Table report.

 

To filter data in the Pivot Table, simply create a Slicer:

STEP 1: Select a cell inside the Pivot Table.

Top 7 Excel Interview Questions to Land Your Dream Job!

STEP 2: Go to PivotTable Analyze > Insert Slicer.

Top 7 Excel Interview Questions to Land Your Dream Job!

STEP 3: In the Insert Slicers dialog box, select the checkboxes next to the column (Sales Region)  you want to display as a filter.

Top 7 Excel Interview Questions to Land Your Dream Job!

An Excel Slicer will be created for the fields (Sales Region) you have selected above.

Top 7 Excel Interview Questions to Land Your Dream Job!

STEP 4: Select Americas to filter the data for that particular item.
The data has been filtered!

Top 7 Excel Interview Questions to Land Your Dream Job!

 

#6 – Protect Sheets

  • How to protect sheets in Excel?
  • How to secure formulas in your Excel sheet?

If you have lots of formulas in your Excel Worksheet and you want to secure those formulas, you can protect your sheets. To protect sheets in Excel, follow the steps below:

STEP 1: Press the Go To Special shortcut CTRL+G

Lock & Protect Formula Cells

STEP 2: Select the Constants box and press OK (this highlights all the non-formula cells)

Lock & Protect Formula Cells

STEP 3: Press CTRL+1 to bring up the Format Cells dialogue box

Lock & Protect Formula Cells

Lock & Protect Formula Cells

STEP 4: Select the Protection tab and Un-check the Locked box

Lock & Protect Formula Cells

STEP 5: Go to Review > Protect Sheet > then enter your custom password (optional)

Lock & Protect Formula Cells

This will lock all the cells that are not constant, so all the formula cells 🙂

To know more about how to protect your sheet and to ace that Excel practice test, Click Here!

 

#7 – Merge Cells

  • How to merge cells in Excel?
  • How to combine multiple cells in Excel?

A great way to customize the layout of your Excel worksheet is to merge multiple cells into one cell.

See also  Filter a Pivot Table by Dates

 

Follow the steps below to learn how to answer this Excel interview question on Excel merged cells:

STEP 1: Make sure to select all of the cells you want to merge together:

How to Merge Cells in Excel

 

STEP 2: Go to Home > Alignment > Merge & Center

How to Merge Cells in Excel

The top row is now merged together!

How to Merge Cells in Excel

Excel has a number of approaches on how to merge cells in Excel that spans across rows and/or columns. Click here to learn more about it and answer those Excel questions by the interviewer with confidence.

 

Conclusion

In this article, you have been provided with a detailed guide on the frequently asked Microsoft Excel interview questions and answers in any Excel practice test.

There are many important features in Excel that you must be prepared for before sitting for an interview. You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot Tables, and Macros & VBA!

Go through these Excel help tutorials to gauge your Excel skills and crush your Excel interview questions so that you can land your dream job.

For some bonus tips on crushing your Excel interview questions, listen to our podcast below to get the best Excel interview tips from the Excel experts and MVP’s around the world:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!