Interview Questions on Excel

Excel is one of the most common office software and has a wide range of applications in different departments and teams. The primary reason why companies are asking their employees about Microsoft Excel skills during interviews is that it helps identify whether or not they have any experience with this software. Employers want to make sure that all new hires will be able to perform basic tasks within the company without having to rely on other people.


Below are some frequently asked interview questions and answers you need to prepare and practice Excel. In this article, we will only be covering the basics. However, we further suggest that you learn more or find a mentor to get deeper into these topics.

What should I expect from Excel interview questions?

During an interview, a company may want to test the candidates' proficiency in Excel. Generally, a company can choose either a multiple-choice (paper or online test) or an interactive test. An interactive test is where you are given an Excel spreadsheet of questions or scenarios to answer.

Get the LinkedIn Skill Assessment badge

Many companies look at your LinkedIn profile before they call you for an interview. Hence, this is the best way to display your Excel competency, and even avoid an Excel interview in the very first place.

You can take the Excel skill assessment and if you score in the top 30%, you'll receive a skill badge, which you can opt to display on your profile and in recruiter searches.

To take the assessment, go to your LinkedIn profile, and scroll down to the "Skills & endorsements" section. Click on the "Take skill quiz" button. In the search box, type "Microsoft Excel" and take the assessment.

Once you earned the badge, remember to click on the "Show your badge on your profile and in recruiter searches" option.

skill-assessment

Common Excel interview questions and answers

1. Can you create a Pivot Table using tables from different worksheets?

Yes, you can. Using Power Query, you can create Pivot Tables from different worksheets of the same workbook, or even from different workbooks.


2. What are the functions of different cell references?

There are three cell references and they affect the way formulas are copied from one cell to another.

The absolute cell reference locks the cell no matter where the formula is copied to.

The relative cell reference (default) moves the cell when the formula is copied elsewhere.

Mixed cell reference locks the row or column of the cell when the formula is copied elsewhere.


3. If you don't want to modify the cell addresses when they are copied, what should you do?

In case you don't want the cell addresses to change while copying, use absolute cell reference instead. Absolute cell reference means that references to cells remain unchanged irrespective of their location within the worksheet. To use absolute cell referencing, use the $ sign before the column and row number. E.g. use $A$1 instead of A1.


4. Is there anything like an undo button in Microsoft Excel?

To undo, use keyboard shortcut CTRL+Z for Windows and CMD+Z for Mac, depending on your operating system.


5. How can you disable the automatic sorting in Pivot Tables?

By default, the data in Pivot Table is automatically sorted by its row labels in alphabetical order. If you don't want that, open the Row Labels dropdown menu and select another option.


6. How do you provide a dynamic range in the 'Data Source' of Pivot Tables?

A dynamic range in the data source of Pivot Tables means that the Pivot Table is able to adjust to new data when refreshed automatically. To do that, use an Excel table to hold the data.

To convert existing data to an Excel table, select the entire range. From the menu bar, go to Insert | [Tables] Table. Under the Table Design tab, give a name to this table. Now when creating Pivot Tables using this range, the range will dynamically expand when new data is added.


7. How can you remove duplicate values in a range of cells?

To remove all duplicate values, select the range of cells. Go to Data | [Data Tools] Remove Duplicates. This removes all duplicate values from your spreadsheet, leaving the first record per unique value.


8. How would you highlight cells with errors in it?

In Excel, there are different types of formula errors, such as #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!.

Using conditional formatting, we can highlight all the cells that contain any of these errors.

First, highlighted the desired range of cells. Go to Home | [Styles] Conditional Formatting > Highlight Cells Rules > More Rules > [Select a Rule Type] Use a formula to determine which cells to format. In [Edit the Rule Description] Format values where this formula is true, type: =iserror(<first_cell_of_your_range>). In Format, choose the fill color that you want to highlight.


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

To add a cell, row, or column in Excel, right-click the cell you want to add to and select Insert. You can then add a cell, row, or column before the cell accordingly.


10. How would you clear all the formatting without removing the cell contents?

Excel has a built-in feature to clear cell formatting. To do that, select the desired cells and go to Home | [Editing] Clear > Clear Formats.


11. How would you highlight cells with duplicates values?

You can do this using conditional formatting. To do so, select the range of cells and go to Home | [Styles] Conditional Formatting > Highlight Cell Rules > Duplicate Values | OK. Now all duplicated values (including the first occurrence will be highlighted.


12. How would you highlight cells with negative values?

You can do this using conditional formatting. To do so, select the range of cells and go to Home | [Styles] Conditional Formatting > Highlight Cell Rules > Less Than. Type 0 in the box and press OK.


13. How can you select all the cells in the worksheet?

To select all cells, use keyboard shortcut CTRL+A for Windows and CMD+A for Mac.


14. How can you select all blank cells in Excel?

To select all blank cells, first select the desired range and go to Home | [Editing] Find & Select > Go To Special > [Go To Special] Blanks > OK.


Get The Defeat Excel Handbook Today!

50+ Frequently asked how-tos

No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.

PDF Format

Easily search through the book to find the solution you need. Plus it works offline too.

Effective solutions

No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.

15. How many rows and columns are there in an Excel worksheet?

Excel has 2^20 or 1,048,576 rows, and 2^14 or 16,384 columns (the last column being column XFD).


16. 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 mouse over your cursor on that cell, the comment will be displayed.


17. Do you know about Pivot Tables in Excel?

Pivot Table is a critical concept and is often asked by interviewers.

A Pivot Table is a table that condenses data into summary statistics. The summary statistics can be sums, averages, counts, etc. of the data columns. A Pivot Table is pivotable i.e. it can be easily changed to summarise data in another way.


18. Can you group dates in Pivot Tables?

If you have records with dates, you can group them into Years, Quarters, Months by selecting [PivotTable Tools] Analyze | [Group] Group Selection > [Grouping] By.


19. What is a cell address in Excel?

A cell address is used to identify or locate a particular cell on a worksheet. The address is denoted by combining the column letter and the row number of a cell. For example, the cell address A1 is located under column A and in row 1.


20. Can you link a cell to different files and websites on the internet?

Yes, we can link a cell to different files and websites on the internet using Excel's hyperlink feature. To add a hyperlink, use keyboard shortcut CTRL+K for Windows and CMD+K for Mac.


21. How can you combine the text from multiple cells using a formula?

To combine text from different cells, you can either use the TEXTJOIN function, the CONCAT function, or the & sign.


22. What is the difference between the COUNT and COUNTA functions?

The COUNT function only counts cells that contain numbers, while the COUNTA function counts any cells that are not blank cells.


23. What are some of the questions you should ask a client/stakeholder before creating a dashboard?

These are some suggested high-level questions to ask the stakeholders before creating a dashboard in Excel:

  1. What is the purpose of the dashboard?
  2. What are the data sources? Where are they kept?
  3. Who is going to use this Excel dashboard?
  4. Do the users prefer tables or graphical representation?

24. Can you make the header rows/columns stick and be visible when you scroll?

When working with larger data sets, when you scroll to the right or the bottom, the header rows and columns disappear from view. This can make it difficult to understand the value of a cell.

By using the Freeze Panes option in Excel, you can make column headers and row headers stick even when you scroll.

To do that, select the desired cell. Rows above and columns left of this cell will be made sticky. Go to View | Window | Freeze Panes > Freeze Panes.


25. How do you add a Note to a cell?

To add a Note, right-click on the cell and select New Note. To edit a Note, right-click on the cell and select Edit Note. To delete a Note, right-click on the cell and select Delete Note.


26. What does stating the last argument in the VLOOKUP function as FALSE mean?

Stating FALSE for the last argument in the VLOOKUP function implies an Exact Match. Excel will search for the exact value (though ignoring case sensitivity) in the first column.


Practise makes perfect

You may read up a lot of Excel interview questions beforehand, but the best way is to acquire Excel knowledge by practicing. After all, your Excel competency will be apparent to your employers once you start work.

If you need to quickly achieve Excel competency, contact us for a mentorship.


This article is sponsored by Jooble. Jooble is a job search engine specially to help you find the job of your dreams.