How to Pass an Excel Test for an Interview
Most companies are looking for any job candidate to have a good variety of soft and technical skills as well as the right attitude for work ;-).
Over the years, Microsoft Excel has become one of THE most important technical skills that every hiring manager looks for in a potential employee. If you are applying for any office job, the expectation is that you know your way around a spreadsheet. If you don’t, you might fall at the first hurdle as many employers now assess a candidate’s Excel skills with a simple online test.
These Excel tests can be as basic as true or false. However, some employers put candidates through a more practical exam to test their skills firsthand. Practical tests like these will often require an applicant to show their workings in Excel, not just their answer. This way, the employer can validate if the applicant genuinely has the Excel skills they are looking for.
The questions might include using basic functions such as SUM, AVERAGE, MIN, and COUNT. Or more format based questions such as borders, shading, alignment, number formatting, date formatting, currency formatting, and wrapping text.
In this article, we’ve pooled our Excel experience to tackle some of the most common questions asked. If you want to ace the Excel portion of your interview, read on!
Table of Contents
- AutoSum & SUM
- Functions to Round-Up
- Relative, Absolute, and Mixed Reference
- COUNT Functions
- Other Excel Functions
- Frequently Asked Questions
- Final Thoughts
AutoSum & SUM
The AutoSum function is a super quick (and impressive) way to add up, (or sum) a range of cells. To use AutoSum, select a cell adjacent to the row or column of numbers you are looking to sum, and in the editing box on the Home ribbon, select AutoSum.
Another simple method is to enter the SUM formula manually. For example, select cell C6 where the total has to appear. Then, in the formula bar, enter the formula =SUM(C2:C5).
Functions to Round Up
You might be asked to use Excel to round numbers up or down. The MROUND function can help here. This formula, =MROUND(A1,10) will round the number in cell A1 to the nearest multiple of 10.
Similarly, the formula to round A1 to the nearest multiple of 5 would be =MROUND(A1,5).
The CEILING function works in a similar way. The main difference here is that CEILING rounds up to the next highest place value. For example =CEILING(A1,10) would round up cell A1(123.17) to the next highest 10. In this case, 130.
The FLOOR function works in the same way as the CEILING function but instead rounds a number down to the nearest specified multiple. So in our cell A1 example, 123.17 would be rounded down to 120 with =FLOOR(A1,10).
Relative, Absolute, and Mixed Reference
Understanding Relative, Absolute and Mixed References are crucial to understanding how Excel works and will likely appear on any Excel exam.
A Relative Reference is associated with a particular cell, but that reference can change if the formula is moved. In this way it is relative to the information provided. Confused? Let’s look at an example.
In the table below, the formula =C2*D2 is entered in cell E2. As you drag the cursor below, you find that the formula is copied to E3 as = C3*D3 and at E4 as = C4*D4 and so on.
Relative references are very useful when you want to repeat the same formula across multiple rows or columns. Cells in Excel are relative by default. If you want to drag a formula down or across and refer to a fixed cell, then you need an Absolute Reference.
An Absolute Reference specifies a reference to a particular cell’s value used in the spreadsheet across any row or a column. The cell reference doesn’t change and remains the same.
For example, in the worksheet below, the values in cells A2 to A4 are converted using a value given in C2.
You can make part of your formula an Absolute Reference by adding $ signs in front of both the column letter and row number. In this example the formulas will be =A2*$C$2, =A3*$C$2 and =A4*$C$2. You can achieve this by setting up the Absolute Reference in your first cell (B1) and then dragging it down as before.
You can manually add $ symbol before the cell name like this: $C$2. Alternatively, if you are on a PC, the shortcut is F4 to toggle this on and off.
There are circumstances when you need both Relative and Absolute References. This is called a Mixed Reference and might be when you want Excel to refer to the same column but move the row that it looks at. Using our example above, you’d achieve this by doing this:
You can either add a Mixed Reference manually to your formula or toggle through the Absolute and Mixed References using the F4 shortcut.
The COUNT function (you guessed it!) counts the cells containing numbers, dates, or any value stored in numeric format excluding blank values.
In the above example, the formula =COUNT(A3:A8) counts the number of cells that contain numeric values and excludes the blank cells A2 and A7. In total, there are five values, so we get the number 5 returned in cell A12.
COUNTA stands for Count All, and it counts the number of cells with number, text, or any type of value excluding the blanks. So basically, it counts the number of cells populated with a value.
In the above example, the formula =COUNTA(A2:A8) returns the value of 6 in cell A10. That is because 6 cells contain values of some sort (both text and numbers).
This COUNTBLANK function is another in the COUNT family that can be pretty useful. COUNTBLANK works in the same way as COUNT and COUNTA but only counts the number of empty or blank cells.
In the example above, the COUNTBLANK function is applied to get the number of blank cells from A2 to A8. Since there is only one blank cell, the answer we get in A10 is 1.
COUNTIF is one of our absolute favourites. It’s easily one of the most used in the COUNT family as it combines counting with logic in Excel. The COUNTIF function allows you to specify criteria beyond numerical values, texts, and blank cells and returns values based on the criteria you set.
For example, let’s say we have some age data, and we want to find out how many people are over a certain age, in this example, over ten years old. For this, we could use COUNTIF.
First, we would apply the formula to the range we wanted it to look at, in this case, column B, the age data. We would then specify the logic element. By using “>10” as our logic, we are asking Excel to count those numbers in the range that are greater than 10.
In our example, there are two values greater than 10. The result in cell A7 is therefore 2.
If you liked the COUNTIF function, you’re going to love COUNTIFS. It’s like COUNTIF but can accept multiple criteria. For example, it might be unusual to get a test question on COUNTIFS, but knowing this shows an intermediate to advanced knowledge of Excel. So, it’s a great one to have in the bank.
COUNTIFS can count rows in a given range that returns a result based on multiple criteria.
In this example, we’re trying to determine whether there is a Green Ford in our dataset. There are two criteria we are looking for here. First, does a Ford exist in column A? If yes, does a Green Ford exist, and how many!
To find that, we use the formula =COUNTIFS(A2:A5,” Ford”, B2:B5,” Green”)
Here, the condition is looking to find “Ford” in the range A2 to A5, and then the adjacent value in the cells from B2 to B5 must have the word “Green”.
The function returns a 1 if both the conditions are true. Hence, the value 1 in cell D7.
Other Excel Functions
There are SO many other functions, formulas and tips that we don't have time to cover in this piece.
HOWEVER, if you are ready to take the plunge and learn Excel, you might want to check out our best-selling online Excel course.
To round this article off, we've got a couple of great tips on Excel that we think are crucial at any company and could help you pass that dreaded Excel test.
You can protect any worksheet you work with. By doing this, you can share it with others and stop them from messing up your good work. Protecting a sheet is especially useful if you share a spreadsheet with multiple people or have different people working on the same file.
Right-click on the worksheet and click on Protect Sheet to protect a sheet.
Enter a password and set what you wish to restrict for other users.
If your text is too long for your cell it spills out and can make reading anything on a spreadsheet impossible. Wrap text will wrap all your text so it displays in a single cell.
To do this, select the cell you are working on, then on the Home tab, in the Alignment group, click Wrap Text.
All of a sudden your wild text is neatly back in its box :-).
Don’t forget to click on the right border of the cell you are using (in this case A1), and adjust the column width and row height, so it’s a bit more legible.
You can clear any formatting done in the cell. To do this click on Home-> Clear-> Clear Formats
Format Painter is one of the best hacks you’ll find in Microsoft Office. It works in Word, PowerPoint, and Outlook as well as Excel. Format Painter copies the formatting of one cell, or a range of cells and directly applies it to another area in your worksheet.
You can access the Format Painter in the Home tab.
Here is Format Painter in action in Excel (Office 365 edition).
Format a cell by choosing a cell style. You can also create your cell style. To create your cell style, do the following:
On the Home tab, click the Styles group, you find more cell styles.
Click New Cell Style.
Enter a Style name and click the Format button. Select the features you want, like the Number Format, Alignment, Font, Border, Fill, and Protection of your cell style. Uncheck a checkbox if you don't want a particular type of formatting.
Click OK to use this cell style in the entire worksheet.
Frequently Asked Questions
How do I improve my Excel skills quickly?
To improve your Excel skills quickly, follow these steps.
- Learn basic formulas. From there, you'll be able to progress to more complex ones.
- Understand how Excel can help you in your industry/job. Often knowing enough job-specific Excel can go a long way.
- Look for real-life examples to see where you can use Excel and practice.
- Remember, Excel is supposed to make your life easier, not the other way around.
- Use conditional formatting and other essential elements to organize your data.
- Take an online Excel course, like those offered by Excel with Business.
What is a Macro in Excel?
A Macro can automate a set of repetitive tasks in Excel. For example, you can record Macros quickly and then use them in future any number of times with just a click of a button.
In this guide, we've touched on several Excel formulas and functions that might come up in an Excel test or an Excel exam. Unfortunately, Excel has over 500 functions, so there is a good chance any Excel exam will include many, many more than we've had time to cover today!
If you're hungry to take in as much Excel knowledge as you can before your test, and you're wondering what to do now - we recommend the following:
- Learn some essential data analysis functions
- Learn to use the PivotTable function
- Learn other, more advanced formulas like VLOOKUP, and INDEX MATCH
- Learn to use Filters in Excel
- Take an Excel Course
Best wishes for your Excel test and interview!
Enter Your Voucher Code Here
Note: We ask for your billing address for our tax records only. We do not ask for card details for pre-paid items.