Many of us have used Microsoft Excel spreadsheets to keep track of household expenses, create personal budgets, and handle simple business tasks. But Excel is capable of far more complex formulas and functions than most people give it credit for.
When you’re applying for a new professional role, you may be expected to understand Excel to an entirely different level. While design and creative roles likely wouldn’t require these kinds of questions, those looking for administrative and clerical roles should expect to demonstrate at least an intermediate understanding of the software. Don’t be surprised if your hiring manager tests your proficiency by asking questions about how the software works.
Here, we’ll take a look at 25 questions about Excel that you should be able to answer when you sit down for an interview.
What You Need to Know About Microsoft Excel
First, what exactly is Excel? Let’s zoom out from the rows and columns for a big-picture view of this world-famous Microsoft application.
Excel was first released by Microsoft in 1985 as a follow-up to its previous spreadsheet program, Multiplan. The first edition was only available on Apple Macintosh computers, with a Windows version coming out two years later, in 1987.
Excel really took off with the release of version 5.0 in 1993, which included Visual Basic for Applications, a macro programming language that expanded its functionality.
These days, Excel still has the largest market share for spreadsheet software, although Google Sheets is becoming increasingly popular with younger users.
Excel is the preferred spreadsheet for professional users, in part due to the advanced data modeling capabilities of the PowerPivot add-in.
25 Excel Interview Questions You Should Be Able to Answer
Excel interview questions are designed to test your understanding of the software, as well as how it is used in specific industries. Your interviewer won’t ask you all of these questions, but they may pick and choose the most relevant ones.
Some questions may have a single correct answer, while others call for more personal answers, such as how you would use the software in practice.
As you prepare for your interview, be sure to consider which questions are relevant to your particular industry. From IT to accounting, you should be prepared to impress the HR team with your knowledge of the software based on your own experience.
Here are 25 of the most common Excel interview questions:
1) What are some of the data types used in Excel and how are they displayed?
Numbers are one of the basic data types used in Excel. You can format numbers with or without commas, and to a set number of decimal places. Percentages are another way to format numbers. For example, .05 could also be displayed as 5%.
Dates can be formatted to regional specifications, such as the standard MM/DD/YYYY format used in the U.S. Dates are stored as numbers, counting up from the number of days that have passed since January 1, 1900.
Strings are lines of text that can be made up of numbers, letters, and punctuation.
2) What is the ($) symbol used for?
The ($) symbol, or dollar sign, has two uses in Excel. It can be used to denote currency within a cell, in either the Currency or Accounting format.
It can also be used as an absolute cell reference within a formula. For example, $B$2 will always refer to B2, even if you move the formula.
3) What are absolute, relative, and mixed cell references?
An absolute cell reference means that the cell in question stays consistent, even if the formula is moved. The ($) symbol is used to denote an absolute cell reference.
A relative reference means that when the formula is moved, the reference is changed based on the number of rows and columns by which it is moved.
A mixed reference is one in which either the row or column is absolute, and the other one is relative. For example, B$2 means that when the formula is moved, the column will change, but the row will not, as opposed to $B2, which does the opposite.
4) What order of operations does Excel use?
Excel formulas follow the standard PEMDAS order of operations. Following the (=) sign, Excel works from left to right, starting with Parentheses, then Exponents. Next comes Multiplication and Division, followed by Addition and Subtraction.
5) What is the VLOOKUP function and how is it used?
The VLOOKUP function allows you to find specific data within a given range. You could look up a part number to find a price, or an employee ID to find a name.
The value that you want to find should be to the right of the value that you use to look it up. Use TRUE to find an exact match, and FALSE to find an approximate match.
6) What is the maximum number of rows and columns Excel can support?
MS Excel 2007 and all later versions support a maximum of 1,048,576 rows and 16,384 columns for a total cell count of 17,179,869,184 per worksheet.
If you import a source file with more rows or columns than is allowed, you may get an error message saying “File not loaded completely.”
7) How can you find out the number of rows and columns in your worksheet?
Put your cursor in an empty column and press Ctrl + Down Arrow to go to the last row. Put your cursor in an empty row and press Ctrl + Right Arrow to go to the last column.
8) How can you count the number of cells that contain data?
You can count the number of rows and columns that contain data by clicking on the row selector or column header, or by using the COUNTA function.
9) What is the difference between the COUNT and COUNTA functions?
The COUNT function counts all cells that contain numbers, while the COUNTA function counts all cells that contain data. This means that COUNTA counts all cells that aren’t blank, including those that include non-numerical data.
10) What is a pivot table and what kind of reports do they display?
A pivot table is used to show statistics, such as sums or averages, that are drawn from a larger table. Reports can be displayed in Compact, Outline, and Tabular forms.
11) What are the six options for formatting a cell?
Number, Alignment, Font, Border, Patterns, and Protection
12) What are the six types of data that Excel formats automatically?
Currency, Percentage, Date, Time, Fraction, Scientific
13) What does a red triangle in the upper right corner of a cell mean?
A red triangle is an indicator that there is a note or comment attached to that cell. You can hover your cursor over the cell to view it.
14) What is the difference between a note and a comment?
Notes don’t have a “reply” function, and are used for single annotations and reminders. Comments do have a “reply” function and can be used for threaded conversations.
15) What is the Ribbon and what does it contain?
The Ribbon refers to the row of buttons and icons at the top of your worksheet. These include common tabs like Home, Insert, Page Layout, and Data.
You can customize the Ribbon and collapse or expand it using CTRL+F1. Some tabs only appear when you select a relevant item, such as a chart or table.
16) How can you keep the data in your worksheet safe?
Select Review > Protect Sheet > Password to create a password and lock your sheet. Users cannot copy and paste data from a password protected sheet. If you only want to lock some of the cells, you can select those cells rather than lock the entire sheet.
17) What is the difference between SUBSTITUTE and REPLACE?
Both functions are used to replace part of the text in a string. The main difference is that REPLACE is based on the position of the text you want to replace, while SUBSTITUTE is based on the content of the text and is case-sensitive.
For example, you would use SUBSTITUTE to replace a given string located anywhere in your worksheet. REPLACE, on the other hand, can be used to replace strings located in specific positions, such as to redact the digits in a bank account number with ****.
18) How does the IF function work?
The IF function checks to see whether a given statement is true or false. For example, it can determine whether a sum is greater or lesser than x, and show the result.
19) What are the WEEKDAY and WORKDAY functions used for?
The WEEKDAY function displays a number from 1-7 based on the day of the week for a given date. For example, it would return 01/01/2020 as a 4 for Wednesday.
The default setting is to count from 1, starting on Sunday, but this can be customized to suit your counting scheme.
The WORKDAY function can be used to count the number of workdays from a start date, excluding weekends and holidays.
20) What is an Excel macro?
A macro is a piece of programming code that can be used to automate actions that you do repeatedly. You can record a macro using the Record Macro tool in the Developer tab. Simply perform the tasks that you want to record, then run back the macro.
Use the Visual Basic Editor to make changes to the code. You can direct Excel when to run the macro automatically, such as every time you open a workbook.
21) What are some of the chart types you can use in Excel?
Excel offers standard charts, such as pie charts, line charts, and column charts. It also has more complex charts such as XY (scatter) charts and map charts.
22) What is a volatile function and what are its risks?
A volatile function is one that is recalculated every time a change is made, even if none of the cells in question have been updated. Volatile functions can slow down processing time, especially if they’re used in a worksheet with a large data set.
23) What are some ways to reduce the size of an Excel file?
- Save it as an Excel Binary Workbook (XLSB) file
- Compress images using the Picture Tools Format tab
- Clear the Pivot Cache and remove unused Pivot Tables
- Remove data formatting and delete unused data
24) How can you avoid running malicious macros in Excel files?
Microsoft Word and Excel files can be used to hide malicious malware. By default, Excel disables all macros when opening a file unless you choose to run them. You can change your security settings in the Trust Center to allow trusted macros.
25) What are the three wildcard characters in Excel searches?
A question mark (?) replaces a single character. For example, “Jo?n” returns “John” and “Joan.” An asterisk (*) replaces multiple characters. For example, “J*n” will return''John `` and''Joan,” as well as “Jon.” A tilde (~) before a wildcard character is used to search for that character. For example, to find “*” you would search for “~*.”
Your interviewer may ask you more subjective questions, such as what kinds of Excel spreadsheets you’ve made, or best practices for designing a dashboard.
Remember, your task isn’t to simply recite functions you’ve memorized, but to show that you have a working knowledge of how they’re applied.
Use these questions as a starting point to brush up on your knowledge of Excel, and be prepared to share your personal experience with the program.
Refer your peers to rewarding positions at high-growth organizations.
