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.
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.
Easily search through the book to find the solution you need. Plus it works offline too.
No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.