Excel Job Interview Practice Questions and Answers

Excel Basic Q&A

13 Job Interview Practice Questions: Basic Level

These questions aim to assess the candidate's product knowledge and practical experience, and demonstrate how they have leveraged Excel to accomplish tasks and improve their job performance.

Can you describe a specific task or project, either at work or home where you utilised Excel to improve your work efficiency or productivity? How did Excel help you in that situation?
I have used Excel at home to create a 12 month home cash budget and to keep track my valuable electronic devices. I added some charts to create a simple dashboard. This provided me with an excellent review of my previous spending and prepared me for potential short term cash shortfalls. In particular, it enabled me to anticipate the consequence of any future interest rate rises on my home mortgage. I briefly used Excel in my previous job to enter data into tables, and I am comfortable with the basics of navigating around spreadsheets. There was some data validation settings to ensure the accuracy of my entries, but these were created by another person.
Explain to me how you used Excel to create a basic home budget.
To create a basic budget or expense tracker in Excel, I set up a table with columns for different expense categories (e.g., monthly mortgage interest payments, utilities, groceries) and rows for each month. I would then enter the corresponding amounts for each category and use Excel's SUM function to calculate the total expenses for each month and the overall budget.
Have you ever used Excel to perform basic calculations or create simple formulas?
I used Excel to create and maintain a home inventory spreadsheet. I listed all my electrical devices, including their warranty information, serial numbers, cost price, the retailer names. Excel allowed me to easily sort and filter the data, making it convenient to track inventory levels and reorder items when needed.

Okay, here are some technical questions regarding Excel:

What is a cell in Excel?
A cell is the intersection of a column and a row in an Excel worksheet, and it is where you can enter data or formulas.
What is a formula in Excel?
A formula is an expression that performs a calculation in Excel. Formulas always start with an equal sign (=) and use standard mathematical operators like +, -, *, and / to specify the calculation.
How do you sum a column of numbers in Excel?
To sum a column of numbers in Excel, you can use the SUM function. For example, to sum the numbers in column A, you would enter the formula "=SUM(A:A)" in a cell.
How do you create a chart in Excel?
To create a chart in Excel, select the data you want to include in the chart and then click the "Insert" tab. From the "Charts" group, select the type of chart you want to create and then click "OK".
How do you hide rows or columns in Excel?
To hide rows or columns in Excel, select the rows or columns that you want to hide and then right-click on them. From the context menu, select "Hide". To unhide rows or columns, select the rows or columns on either side of the hidden rows or columns and then right-click on them. From the context menu, select "Unhide".
Can you explain how to format cells or ranges in Excel?
To format cells or ranges in Excel, you can select the cells and use the formatting options available in the toolbar. For example, you can change the font style, size, and colour, apply cell borders, and adjust the number format to display currency or percentages. Formatting helps make the data more visually appealing and easier to read.
How do you freeze panes in Excel?
To freeze panes in Excel, select the cell below the row and to the right of the column that you want to freeze. From the "View" tab, click the "Freeze Panes" button and then select "Freeze Panes".
How do you sort data in Excel?
To sort data in Excel, select the data you want to sort and then click the "Data" tab. From the "Sort & Filter" group, click the "Sort" button and then select the sort options you want to use.
How do you filter data in Excel?
To filter data in Excel, select the data you want to filter and then click the "Data" tab. From the "Sort & Filter" group, click the "Filter" button. This will add drop-down arrows to the headers of each column, and you can use these to filter the data by selecting the values you want to see.
What are some common keyboard shortcuts that you use in Excel?
Ctrl + C to copy a selection
Ctrl + V to paste a selection
Ctrl + Z to undo an action
Ctrl + Y to redo an action
Ctrl + P to print a worksheet
Ctrl + S to save a workbook
Ctrl + A to select all
Ctrl + E for Flash Fill (not common just yet, but a powerful time saver!)

Click to see more interview tips

Excel Intermediate Q&A

10 Job Interview Practice Questions: Intermediate Level

These questions aim to assess the candidate's proficiency in intermediate Excel skills and their ability to effectively utilise Excel's features to analyse and manipulate data.

By exploring these areas, the interviewer can determine the candidate's level of Excel proficiency, problem-solving abilities, and their potential to effectively utilise Excel's features in the context of the job requirements.

The questions aim to evaluate the candidate's:

Familiarity with advanced functions
By asking about the use of functions like VLOOKUP, SUMIF, and SUMIFS, the interviewer can gauge the candidate's knowledge and experience in utilising these functions to perform data analysis and manipulation tasks efficiently.
Proficiency with pivot tables
Questions about pivot tables assess the candidate's ability to leverage this powerful feature to summarise and analyse data, demonstrating their understanding of data organisation, filtering, and aggregation.
Skill in conditional formatting
These questions assess the candidate's knowledge of conditional formatting techniques to highlight important information or identify patterns in data, showcasing their ability to present data effectively.
Competence in automating tasks with macros
Questions about creating macros evaluate the candidate's capability to automate repetitive tasks, streamline workflows, and save time by recording and executing series of actions in Excel.
Understanding of data validation
Questions related to data validation assess the candidate's knowledge of ensuring data accuracy and integrity by setting up validation rules and implementing dropdown lists or other validation techniques.
Familiarity with solving optimisation or what-if scenarios
These questions evaluate the candidate's ability to utilise Excel's solver add-in, goal seek, or other optimisation features to analyse complex data and solve business problems effectively.
Ability to create interactive dashboards and reports
Questions about creating interactive dashboards or reports assess the candidate's skills in data visualisation, demonstrating their ability to present data in a visually appealing and user-friendly manner, thereby enhancing data-driven decision-making processes.

Okay, here are some typical questions with sample answers:

How would you use the MAX function in Excel?
The MAX function in Excel is used to return the maximum value in a range of cells.
How would you use the VLOOKUP function in Excel?
The VLOOKUP function in Excel is used to look up and retrieve data from a table or range of cells. It takes four arguments: the value to look up, the range of cells to search in, the column number in the range to return the value from, and a logical value indicating whether or not to search for an exact match.
How would you use the IF function in Excel?
The IF function in Excel allows you to test a condition and return one value if the condition is true, and another value if the condition is false. It takes three arguments: the condition to test, the value to return if the condition is true, and the value to return if the condition is false.
What is Flash Fill
Flash Fill is an extremely useful tool introduced into Excel a few years ago.
You can use Flash Fill in Excel to make data entry easy. It’s a smart tool that tries to identify patterns based on your data entry and does that for you. The shortcut key combination is Ctrl E.
When would you consider switching from automatic to manual calculation in Excel?
While in most cases automatic calculation mode is the way to go, in case you have a formula heavy file where recalculation takes a lot of time every time you change anything in the sheet, then you can switch to manual calculation. Once you have switched to manual calculation, you need to refresh every time you want the sheet to recalculate.
How would you identify cells that have comments in it?
The cells that have a comments added to it are flagged by a small red triangle at the top-right of the cell. When you hover the cursor over the cell, the comments becomes visible.
A named range is a feature in Excel that allows you to give a cell or a range of cells a name. Now you can use this name instead of using the cell references.
What are named ranges? What are its benefits?
Using a named range makes it easier when you’re working with formulas. This becomes specially useful when you have to create formulas that use data from multiple sheets. In such cases, you can create named ranges and then use these instead of the cell references.

You can give descriptive names to these named ranges – which also makes it easier to read and understand the formula. For example, you can use =SUM(SALES) instead of =SUM(C2:C11), which will instantly tell you what the formula is about.
What are the benefits of using an Excel Table?
There are several advantages to using tables in Excel:

Easy to create and modify: It is easy to create a table by simply selecting a range of cells and clicking the "Table" button on the Insert tab. You can then add or delete rows and columns, or move the table to a new location in the worksheet, without affecting the formulas or data in the table.

Auto-expanding: If you add a new row or column to the table, the table will automatically expand to include the new data. This can save time and reduce the risk of errors when you are working with large amounts of data.

Structured referencing: When you use a structured reference in a formula, Excel automatically updates the reference if you add or delete rows or columns in the table. This can save time and reduce the risk of errors when you are working with large amounts of data.

Filtering and sorting: You can easily filter and sort the data in an Excel table by clicking the filter buttons in the table header row. This can be useful for finding specific data or for analysing your data in different ways.

Built-in formatting: When you create a table, Excel applies built-in formatting to the data, including alternating row colours and bolded headings. You can customise the formatting to suit your needs, or you can choose from a variety of predefined table styles.

Better organisation: An Excel table can help you organise and manage your data more effectively. You can use the table's header row to label the data in each column, and you can use the built-in formatting to highlight important data or to make it easier to read.
How would you tackle errors when working with Excel Formulas?
There are various ways you can tackle the errors in Excel:
You can highlight the errors using conditional formatting. This requires using the ISERROR function in conditional formatting.
You can use the IFERROR function in Excel to get a specific value in case the formula returns an error.
You can use ISERROR to get TRUE in case there is an error and FALSE in case there is not.
You can use IFNA function to tackle the #N/A error.
What's the difference between Relative & Absolute References and when would you use them?
A relative reference is a cell address that is relative to the location of the formula. This means that if you copy a formula containing a relative reference to a new location, the reference in the formula will be updated to reflect the new location.

An absolute reference is a cell address that is fixed and does not change when the formula is copied to a new location. Absolute references are denoted by the dollar sign ($) before the column letter and row number.

You would use absolute references when you want to fix a reference to a specific cell or range of cells, regardless of where the formula is copied. This can be useful in situations where you want to reference a specific cell or range of cells in multiple formulas, or when you want to ensure that a formula always refers to the same cells, even if it is moved or copied to a new location.

Click to see more interview tips

Excel Advanced Q&A

13 Job Interview Practice Questions: Advanced Level

These questions are designed to evaluate the candidate's advanced Excel skills, their ability to solve complex problems, leverage advanced functions and features, and their knowledge of data modelling, automation, and data visualisation techniques.

Can you tell me about some of your experiences in successfully building and implementing solutions using Excel's advanced tools?
In my previous organisation, I was responsible for the automation of repetitive manual tasks, thereby improving productivity and also providing accurate and timely management reports.
And what specific Excel features did you use to achieve this?
Three key Excel tools I used were
- VBA Macros which vastly improved the speed and accuracy of mundane tasks
- Power Query enabled the extraction, cleansing and aggregating vast amounts of disparate data
- Pivot tables and charts to summarise and meaningfully represent the data
How do you create a dynamic named range in Excel?
A dynamic named range in Excel expands or contracts automatically based on the data in the range. To create a dynamic named range, follow these steps:
- Select the cells that you want to include in the range.
- Click the "Formulas" tab, then click the "Define Name" button.
- In the "New Name" dialog box, enter a name for the range in the "Name" field.
- In the "Refers to" field, enter a formula that defines the range.
How do you use the SUMPRODUCT function in Excel?
The SUMPRODUCT function in Excel is used to multiply the corresponding cells in two or more ranges and then return the sum of the products.
How do you use the INDEX and MATCH functions in Excel?
The INDEX and MATCH functions in Excel are often used together to perform a vertical lookup (i.e., search for a value in a column and return a corresponding value from a different row). The INDEX function returns a value from a specific cell in a range, while the MATCH function returns the position of a value within a range.
How do you create a pivot table in Excel?
To create a pivot table in Excel, follow these steps:
Select the data that you want to include in the pivot table.
Click the "Insert" tab, then click the "Pivot Table" button.
In the "Create Pivot Table" dialog box, make sure that the correct data range is selected, then click "OK".
The pivot table will be created on a new worksheet. You can then drag and drop fields from the "Field List" pane onto the pivot table to add them to the rows, columns, or values.
What is Power Pivot?
Power Pivot is an Excel add-in that allows you to perform powerful data analysis and create sophisticated data models. It enables you to import and work with large volumes of data from a variety of sources, such as databases, text files, and Excel worksheets.

With Power Pivot, you can create relationships between different data sets, build calculated fields and measures, and use pivot tables and pivot charts to analyse and visualise your data. You can also use Power Pivot to create dashboards and reports that can be shared with others.

Power Pivot is particularly useful for working with big data and for creating data models that are too complex to be built with standard Excel functions and tools. It is a powerful tool that can help you discover insights and trends in your data that may not be immediately apparent.
What is conditional formatting and give an example of its use.
Conditional formatting in Excel is a feature that allows you to apply formatting to cells based on certain conditions. For example, you can use conditional formatting to highlight cells that contain values above or below a certain threshold, or to highlight cells that contain duplicates or unique values.

Suppose you have a table of student grades that includes a column for the student's name and a column for their grade. You want to highlight the cells in the "Grade" column that contain grades below 70 in red.

To do this, follow these steps:
- Select the cells in the "Grade" column.
- Click the "Home" tab, then click the "Conditional Formatting" button.
- Choose "Less Than" from the list of options.
- In the "Less Than" dialog box, enter "70" in the "Value" field and choose the red fill colour from the list of options.
- Click "OK" to apply the formatting.

Now, any cell in the "Grade" column that contains a grade below 70 will be highlighted in red. This can help you quickly identify which students are not meeting the minimum grade requirement.
What is data validation and how would is it useful?
Data validation in Excel is a feature that allows you to define rules for the data that can be entered into a cell or range of cells. It is useful in a variety of situations, including:

- Ensuring that data is entered in the correct format (e.g., a date or a number).
- Limiting the range of possible values that can be entered.
- Preventing duplicate values from being entered.
- Ensuring that a value is entered in a cell before a calculation is performed.

Using data validation can help to improve the accuracy and integrity of your data by preventing errors and inconsistencies. It can also make it easier to work with large volumes of data by simplifying data entry and reducing the need for manual checks and corrections.
What are the different ways you can secure an excel workbook?
There are several ways to secure an Excel spreadsheet:

Password protect the workbook
You can set a password that is required to open the workbook. To do this, go to the "Review" tab and click the "Protect Workbook" button. Then, choose "Encrypt with Password" and enter a password when prompted.

Set permissions
You can control who can edit or view the workbook by setting permissions. To do this, go to the "Review" tab and click the "Protect Workbook" button. Then, choose "Restrict Access" and specify which users or groups should have access to the workbook.

Protect sheets and cell
You can protect specific sheets or cells within a workbook so that they cannot be edited. To do this, go to the "Review" tab and click the "Protect Sheet" button. Then, specify which users or groups should have permission to edit the sheet or cells, and enter a password if desired.

Use data validation
You can use data validation to control the data that can be entered into cells. This can help to prevent errors and ensure that data is entered in the correct format.

Use data encryption You can use data encryption to secure the data in the workbook by encoding it so that it can only be accessed by someone with the decryption key. To do this, go to the "File" tab and click "Info". Then, click "Protect Workbook" and choose "Encrypt with Password.
What are structured references?
When you use an Excel Table, you don’t need to use the cell references. Instead, you can use the Table name or the column names. These references are called structured references.
You have been tasked with creating an Excel Dashboard. What questions should you ask?
While the questions would depend on a case to case basis, there are few high-level questions that you should ask when creating a dashboard in Excel.

- What is the Purpose of the Dashboard?
- What are the data sources?
- Who will use this Excel Dashboard?
- How frequently does the Excel Dashboard needs to be updated?
- What version of Office does the client/stakeholder uses?
- What is the role(s) of the intended audience?
When would you use the SUBTOTAL function?
When you’re working with tabular data, you can use the SUBTOTAL function to get variety of subtotals – such as AVERAGE, COUNT, MAX, MIN, STDEV.

One of the highlights of SUBTOTAL function is that it allows you to ignore hidden/filtered cells. So if you have a huge data set and you filter it based on a criteria or hide some rows, SUBTOTAL function will automatically update to give you the result from the visible cells only.

Of course, if you don’t want the data of filtered/hidden cells to be ignored, you can do that too.

Extra Interview Tips

Some extra job interview tips...

Research the company
Learn as much as you can about the company, its products, and its mission. Understand the company's industry and its place within it. It will also be beneficial to find out the company's current initiatives, and understanding their recent news related to the company.

Study the job requirements
Review the job requirements and responsibilities, and understand what skills and experience are required for the position. Prepare examples of how you have demonstrated these skills and qualifications in your past experiences.

Brush up on your technical skills
Review and practise the technical skills required for the job, such as programming languages, frameworks, and tools and related Excel software, for example, Power BI. It's also a good idea to stay up to date with recent developments and new technologies generally.

Prepare answers to common interview questions
Be ready to answer common interview questions such as why you want the job, what interests you about the company, what makes you a good fit for the position, etc.

Plan to ask questions
Prepare questions to ask your interviewer about the company, the team, and the position. This will show your interest in the company and the role and will help you gather information that can help you make an informed decision about whether to accept a job offer.

Prepare a portfolio
If applicable, prepare a portfolio of your work that demonstrates your Excel and related skills and experience. This can include code samples, project reports, or any other relevant material.

Practise your communication skills
Make sure you can clearly and effectively communicate your ideas and experiences. This may involve practicing your interviewing skills with a friend or family member.

Dress for the part and show up on time
Make sure you are dressed professionally and arrive at the interview location on time. Being punctual and professional in your appearance will make a good first impression.

Be Confident
Practise positive self-talk and visualization before your interview, it can be a great way to boost your confidence and help you put your best foot forward.

By following these tips, you can be well-prepared for your Excel job interview and increase your chances of impressing the interviewer and landing the job.

Measure your Excel skill level with our complimentary online competency assessments