How Do We Apply Advanced Filters In Excel
To apply advanced filters, use the Advanced Filter option present in the Data tab. Select where you want to filter the table. Choose the list range and the criteria range that has the conditions based on which you would like to filter the table.
The below example shows how to apply advanced filters.
Why Do Employers Use Them
Its not unusual for candidates to exaggerate their skill levels when applying for a job. And although the phrase fake it until you make it may be useful sometimes, there are situations where employers will use aptitude tests to assess a prospective employees suitability.
Imagine applying to be a financial analyst or project manager. You may think youll be able to learn on the job, but with budgeting and calculating expenses, there is no room for mistakes. Employers will ask you to take an Excel Test to make sure youre up for the task .
Complexity Levels Of Excel Employment Assessment Tests
Microsoft Excel harbor very basic calculations to very advanced data processing and analysis that requires in-depth knowledge of every tools of Excel. Depending on the position candidate is applying for you are likely to face a pre-employment Excel test with varying level of difficulty. Microsoft Excel Employment Assessment tests can be categorized into at least three levels of difficulty:
Read Also: How Do Phone Interviews Work
Expert In Microsoft Excel
A Linkedin Learning Instructor, Yolanda teaches more than 20 courses about Excel, Power BI and Management. She was awarded the Microsoft Excel Most Valuable Professional Award 8 years in a row . Previously an economist, Yolanda enjoys teaching and sharing her learnings from her business experience with others.
We decided to start with Maki because they have a wide variety of tests, which adapts to our needs, based on what we are looking for our candidates. Using the tool has been super easy, it is very intuitive and the assessment results are very helpful to quickly identify the best candidates. The Maki team is always ready to help us if we need anything!
The majority of office positions these days require, at minimum, basic computer skills and some software proficiency. Many jobs utilize more advanced computer knowledge or competency in Microsoft Excel, one of the most popular and widely used programs.
Microsoft Excel, from the Microsoft Office Suite, is more than just a workbook. The versatile software includes advanced and complex features with formulas that can perform data calculations.
Implementing a Microsoft Excel assessment test as part of the hiring and interview process goes a long way in ensuring that the job candidate’s skill level meets the jobâs requirements. The assessment test will test their knowledge and proficiency in Excel.
Excel Skills That Are Being Evaluated
As part of Basic Microsoft Excel Skills test employers are looking to ensure that candidates have the following skills
- Ability to navigate and modify an Excel workbook by identifying common tools, applying basic keyboard shortcuts, and manipulating columns and rows
- Ability to apply basic sorting and filtering options as well as statistical functions to explore and summarize values
- Ability to customize the look and feel of a workbook using cell formatting tools
As part of Basic Microsoft Excel Skills test candidates are being tested for their ability to navigate the Excel user interface quickly and efficiently in order to access the right features and perform simple tasks from determining the best way to open a recently closed Workbook to using the correct formula to perform automatic calculations. Candidates will be required to answer multiple-choice questions based on Excel spreadsheets, charts and tables filled with sample data, as well as images of Excel buttons and tools, testing their ability to:
Recommended Reading: What Are Questions Interviewers Ask
What Is The Use Of The Vlookup Function
Questions that are directed toward a formula or function help the interviewer understand your proficiency with Excel. VLOOKUP is an important Excel function and is widely used across different job roles.
When answering such advanced concepts, always use your personal experience to showcase your knowledge and familiarity with the concept.
Example:VLOOKUP helps find data in a large spreadsheet using the lookup value in another spreadsheet. It is an essential function because it provides valuable insights and relationships between the data set.
As a purchasing and inventory supervisor, I worked with Excel to optimise inventory quantities and levels to meet customer demand. After each month, I had to go through the on-hand inventory and inventory usage data and separate it using the product’s code. This helped me develop creative solutions for slow-moving inventory and ensured that I could manage the faster-moving inventory without facing issues.
Using VLOOKUP, I could achieve the goal successfully without wasting too much time. It helped me save so much time that I could calculate the months on hand for each product. This gave me insights into the number of months required for the on-hand inventory to finish. This was something not expected from me and I could provide valuable insights to the management.
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.
Relative References
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? Lets 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.
Absolute Reference
An Absolute Reference specifies a reference to a particular cells value used in the spreadsheet across any row or a column. The cell reference doesnt 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 and then dragging it down as before.
=A2*$C2
Read Also: How To Interview A Project Manager
How To Facilitate An Excel Test
The right time to give your Excel test can vary based on the job youre hiring for and how many applicants you get. The most logical time to have job applicants complete an Excel test is during applicant screening as part of the interview process. You have the candidates commitment at this point as they have learned they are in the running for the job and have been selected for an interview.
The test can be administered by email or in person, depending on your preference.
If you choose to email the test to your candidates, make sure you only email the test sheet and not the answer sheet. The biggest benefit to emailing the Excel test to the applicant is that you can gauge their interest level in the position without too much investment. If they dont respond to your email request, they probably werent that interested or qualified.
The downside to emailing the test to the applicant is that they could use outside sources to complete the test. When youre giving a candidate an Excel test, you really want to know what they knownot what they can look up.
What Skills Are Required To Use Microsoft Excel
Candidates must be aware of the skills needed to use Excel to improve their skillset and develop a deeper understanding of the application.
Do your candidates recognize that numerical reasoning skills, top analytical skills, and attention to detail are critical when using Microsoft Excel? Are they aware that they must also have certain technical skills to use Excel, such as the ability to open workbooks, format cells, use charts, use Excel functions, and manage worksheets?
You May Like: How To Word A Follow Up Email After Interview
Basic Excel Interview Questions
Around the globe, large and small organizations use Excel regularly. This Microsoft application is such a critical part of business operations thanks to the way it facilitates data management
But for Excel to be useful for your organization, you need talent on your team who can use the application efficiently someone who knows their formulas well and is familiar with pivot tables.
Assessing your candidates skills is easy with an Excel skills test. But to be more thorough, you can follow this up with a selection of the 100 basic Excel interview questions featured in this article.
Read the 100 basic Excel interview questions below and use them to hire the right talent!
About The Excel Test Questions
Many job roles require at least a basic Excel knowledge level, including administrative assistants and other clerical positions. To make sure our preparation matches your Excel skills, there are three levels of Excel tests: basic, intermediate and advanced level. Our preparation packs are designed to cover each of these levels to give you an idea of what to expect during your actual assessment.
Our full Excel PrepPacks include two modes full of sample Excel test questions and answers to stimulate your learning process and bring you to the skill level you desire.
You May Like: How To Start An Interview As The Interviewer Example
General Excel Interview Questions
Lets start with 33 general Excel interview questions you can ask candidates to test their Excel knowledge.
Why You Should Use This Excel Online Test
Microsoft Excel online assessment test is designed and validated by industry experts to help recruiters and hiring managers to assess the Excel skills of the candidate.
The Excel test screens candidates for the following skills that hiring managers look for in candidates:
- Usage for formulas like SUM, INDEX, IFERROR vs IF + ISERROR, INDIRECT and OFFSET
- Using lookup formulas like VLOOKUP with MATCH
- Advanced usage of the CELLS and RANGE objects
- Leveraging data with Pivot tables and Pivot charts
- Adding filters to report menus to narrow the records
- Creating powerful calculations with Excel functions
- Using relative, absolute, and mixed references
- Using data tools like sorting, subtotaling and filtering
- Using charts and graphs to represent tabular data
- Data cleanup and transformations
- Combo techniques for Data cleaning
Also Check: How To Write A Rejection Email After Interview
What Is The Excel Test
Microsoft Excel tests measure your ability to perform various operations, from basic functions such as wrap text to more advanced skills like being able to analyze data on an excel spreadsheet. Whether you need to exhibit basic Excel or advanced Excel skills depends on the position youre aiming for, but either way our prep pack has you covered.
Check out the sample Excel test questions and answers below to see where you stand, and what kind of preparation you need.
Read Also: What Questions To Ask A Project Manager Interview
How Do You Use A Pivot Table
A question that asks how you use something or how you do something is a good opportunity to talk in terms of specific examples. After all, at the core of this question is about you and your familiarity with the subject matter, so it makes sense to apply how you have used it in the past.
A pivot table is an important Excel feature that shows up in frequently-asked questions by all sorts of professionals around the internet. This question allows you to explain your resourcefulness with Excel and confirms your understanding of this popular practice.
Example: A pivot table is an essential tool to summarize, aggregate, reorganize, sort, group, count, average or compute data stored across a database. Ive used pivot tables as a database analyst to compare seasonal shopping patterns for my retail clients.
For example, the Belcher Group wanted to know which of its brands did the most sales during the summer. They had a back-to-school campaign that ran July through August, and they asked me to determine which brand performed the best.
I programmed a pivot table where Column A displayed the brands and Row 1 annotated the months July and August. In the body of the table I pulled information from the sales database that computed total sales per brand, per month. Using this information the Belcher Group was able to tell which of its child companies performed the best.
Also Check: How To Write A Follow Up Interview Thank You Email
Tab : Context & Tasks
The Context & Tasks tab includes the various accelerated Excel functions that will be tested and instructions for performing those tasks using data provided across all three tabs. The information in the left columns shows the skills and familiarity required to complete the tasks in each stage.
The test consists of the following stages:
- Stage 1: Standardizing the Dataset This stage tests the individuals ability to process data between different types of variables.
- Stage 2: Conversions This stage tests the individuals ability to convert formulas and use the VLOOKUP function to populate them.
- Stage 3: Pivot Table 1 This stage tests the individuals ability to create a pivot table.
- Stage 4: Pivot Table 2 This stage tests the individuals ability to take an existing pivot table and change its values, creating new value tabs.
- Stage 5: Sorting and Pivotchart This stage tests the individuals ability to sort data within a pivot table or pivotchart.
- Stage 6: Print Setup and Output This stage tests the individuals ability to create a header using Page Setup, as well as print the document to a PDF file.
How Can You Validate Data In A Spreadsheet
Excel offers data validation features under Data Tools on the Data tab in the ribbon. You can select what type of data is allowed in a cell or group of cells. You can add input messages so others can see what type of data belongs in a cell. The data validation features also allow users to create error messages when certain criteria aren’t met.
Read Also: What Is The Best Way To Answer Interview Questions
About Basic Excel Test For Interview
Excel is mainly used as a reporting tool and lets you do the calculations automatically. Working knowledge of MS Excel is required in many entry-level or office administration job roles for maintaining and analyzing the data. Excel executives should be able to perform basic functions like sum, count, cell formatting, data conversion, simple formulae, and workbook. The 20-minute Excel skills test enables employers and recruiters to identify potential hires with excellent Excel skills by evaluating their working skills and job readiness. The basic Excel interview test is created & validated by experienced Subject Matter Experts to assess the skills of the candidates as per industry standards. Basic Excel test for an interview helps recruiters in evaluating the candidates ability to successfully apply his basic Excel skills to perform tasks related to workbooks and data entry in MS Excel. The score report helps to select the candidate who is the right fit for the job that requires basic knowledge of Excel. It helps the recruiter to determine whether the applicants can use workbooks efficiently and can get along well on any projects without organization training. The basic Excel assessment test reports are automated and provide a detailed, analytical view of each candidate.
The basic Excel test may contain MCQs , MAQs , Fill in the Blanks, Descriptive, Whiteboard Questions, Audio / Video Questions, AI-LogicBox , Job-based Simulations, True or False Questions, etc.
How To Test Advanced Excel Skills
Advanced Excel skills are often the most difficult for a recruiter to assess. These skills go beyond the expertise of a normal user with an advanced Excel user is known as a power user someone who uses the software beyond the standard capabilities.
There are more than 475 different Excel formulas that an assessment test could cover. An advanced Excel test, therefore, should focus on the functions that are most relevant to your company. An advanced Excel user should be familiar with most, if not all of Excels functions and can process spreadsheet data with graphs, pivot tables, and automation. These users are able to build spreadsheet models, templates, and dashboards for other users in the organization who are working with limited knowledge.
At Vervoe, we offerpreconfigured advanced Excel tests for you to use, testing Excel features such as:
- Creating a pivot table and other charts
- Advanced conditional formatting rules
- Visual Basic for Applications
- The ability to analyze data using advanced functions and formulas
There are other advanced skills that may apply, depending on your recruitment process. For more ideas on what questions to add to an Excel assessment test, check out our guide, Hiring An Excel Expert: Advanced Excel Skills Checklist.
Don’t Miss: How To Interview A Recruiter
