How to prepare for an Excel assessment test (with tips)

By Indeed Editorial Team

Updated 25 November 2022 | Published 30 November 2021

Updated 25 November 2022

Published 30 November 2021

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Due to its sophisticated software, many people find Microsoft Excel intimidating to learn. Excel is an important part of many businesses today, particularly those in accounting and finance. If you're applying for jobs where you have to use Excel, your employer may ask you to complete an Excel assessment to determine whether you are suitable for the role. In this article, we discuss what Excel assessment tests are, with tips for completing it and example questions.

What is an Excel assessment test?

A Microsoft Excel assessment test is a tool that employers use to determine whether a candidate is suitable for a position based on their proficiency with the software. These tests are becoming more common, as many companies use the software to make graphs and analyse data.

If you're applying for roles in the financial sector or jobs that require numerical analysis, expect to take an Excel proficiency test. To test your skills, some companies may give you a multiple-choice assessment or even use online tests, where you have to complete a set number of tasks. If you're unsure of which test the employer may give you, consider sending them an email asking for more clarification so you can thoroughly prepare.

Related: How To Highlight Excel Skills on Your CV

How to prepare for an Excel test

Although Excel tests may differ between employers, there are several things you can do to prepare:

1. Do your research

If you have an interview where you have to undertake an Excel test, do your research to understand how Excel applies to your role. If you're applying for a position that requires basic maths proficiency, such as an administrative assistant, you may only need to understand Excel's basic functions, such as SUM and AVERAGE. If you're applying for a position that is maths centred, like a data journalist or financial analyst, you may need to prepare for more advanced skills, like VLOOKUP or SUMIF.

2. Practice, practice, practice

The easiest way to sharpen your skills and ensure you are ready for an Excel test is to practice doing a mock test. There are many free tests available online that you can use to prepare, and they offer great insight into how proficient you are at Excel. Make sure that you note down any mistakes you make during the mock test to go over again.

3. Watch tutorials

If you struggle with the more complex parts of Excel, there are many resources to help people improve their existing skills. Take the time to watch tutorials on YouTube or ask friends and relatives for advice if they understand the application. If you do know someone experienced in Excel, ask them for a tutorial session.

Related: IT Skills: Definitions and Examples

Different Excel tests and example questions

There are two main types of Excel tests: basic and advanced. The type of test you can practice before your interview depends on the role. The most basic test may ask you a few questions about the different buttons, shortcuts and equations that you need to know to create simple data sets. More complex tests may ask you how to apply your existing Excel skills to a spreadsheet.

Basic Excel test questions

Here are some questions that may appear in a basic Excel assessment, with advice on how to carry out each function so you know how to respond:

1. Use this data to create a bar graph in Excel

To test your abilities, a potential employer could ask you to create a table or graph to highlight your proficiency with managing small data sets. To do this, they may give you a table of data and ask you to transform it into a graph.

To change your data into a bar graph, copy and paste the table you have into Excel. Next, highlight the range of data you want to display in a graph form. Then, click on ‘Insert' in the top toolbar. Now, click on the icon that looks like a bar chart. You can then choose to create a 2D, 3D, clustered or stacked bar graph. Once you have clicked on the bar graph you want, Excel should automatically make it using the inputted data.

Related: 9 Essential Accounting Skills for Career Success

2. What are some functions you can use in Excel?

A function is a code that tells Excel how to calculate values in a formula. There are different functions in Excel, so the interviewer won't expect you to know all of them. Some of the most common functions include:

  • SUM: This formula totals up the values of the selected rows or columns.

  • COUNT: This function counts instances of values.

  • TRUE/FALSE: This logical function can help you to verify Excel data.

  • AVERAGE: This generates an average of selected values in a column or row.

3. What does a red triangle at the top of a cell mean?

If you see a red triangle in the top-right corner of the cell, it signifies that a comment has been made about the cell. To see what the user has written, hover your mouse over the cell. To add your comment, right-click the cell and then click 'Insert Comment'.

4. How can you resize a column?

To resize a column, drag the boundary on the right-hand side of the column until you reach the desired width. You can also resize columns in the Home tab. You can do this by selecting Format > Column width and type in the new size.

Related: How to Become a Data Analyst

5. What is a pivot table?

A pivot table is a tool that creates a quick summary of lots of data. If you have data that you want to add, count, total or average, a pivot table can create this for you and display it on a separate spreadsheet. A pivot table is a good way to look at a range of data at once.

Advanced Excel questions

If you're applying to be an accountant or financial analyst, ensure that you are well-versed in the language of Excel. This is also true if your job requires you to manipulate data. To prepare, look at some examples of advanced questions that may come up during an advanced assessment test:

1. How can you validate data in a spreadsheet?

Excel data variation is a feature that lets you control what type of data users can enter into your spreadsheet. To do this, click on 'Data Tools' which is under the data tab ribbons. There, you can click on the data to add to a group of cells. You can add data input messages to let people know what data they can and can't add.

Related: A guide on how to find duplicates in Excel

2. What is the ‘what if' condition in Excel formulas?

The 'What If' function, or the IF function, is an important tool in Excel as it can ask Excel to test a particular condition. Excel then runs the numbers and can tell you if a condition has been met (TRUE) or not (FALSE). For example, if you wanted to see how many students passed an exam, and the passing score was 65, the IF function would highlight how many students have gotten grades 65 or higher and how many students have failed.

3. What is conditional formatting?

If you want to format your Excel sheet so that certain data shows up in one colour and different data in another, conditional formatting can help you achieve that. To do this, you have to create a conditional formatting rule, which you can do by selecting the desired cells that you want to format. Once highlighted, click on the home tab and then click on the conditional formatting tab. You can then choose your desired conditional formatting.

For example, you can highlight cells that contain a number greater than a certain figure or those less than a certain figure. All you do is log the condition, and Excel can automatically colour the cells which meet the criteria.

Related: 13 Essential Accountant Skills

What roles require an Excel assessment test?

Excel is a popular application with many uses, so some jobs require you to do an assessment test. However, you are more likely to have to complete one if you're applying for roles that regularly deal with numbers and financial information. Here are some of the roles where an assessment test might occur:

  • accountant

  • financial advisor

  • auditor

  • tax advisor

  • project manager

  • administrator

  • computer teacher

  • data journalist

  • retail manager

If you are thinking about a career or have applied to positions in any of these fields, take the time to sharpen your Excel skills and do some practice tests before you send your application.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Related:

  • A complete guide to the Excel MOD function for professionals


Explore more articles