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
Can You Link A Cell To Different Files And Websites On The Internet
This is a foundational question that tests your fundamentals in Excel. Often, in many analytical roles, you may have to source information or data in your spreadsheet. Also, such a question may segue into a complex Excel interview question.
Example:Yes, we can link a cell to different files and websites on the internet using the hyperlink feature of Excel.
Fair And Inclusive Selection
For improved diversity and inclusion, Test Partnership has optimised all our tests to achieve industry-beating low adverse impact.
All our tests are designed to identify high-performance whilst minimising adverse impact. We achieve this by trialling and re-trialling against tens of thousands of participants from all backgrounds.
Also Check: What Questions To Ask When Being Interviewed
Also Check: Walmart System Design Interview Questions
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 arent met.
What To Expect From Excel Interview Questions
Excel interview questions provide the opportunity to show youre highly knowledgeable about the common spreadsheet software implemented in offices around the world. Because Excel features and benefits are desirable, its a widely used program. As a result, Excel interview questions could be present in a number of interviews, for positions from to accounting.
There are many important functions in Excel and you should be prepared to answer technical questions about Excel versus behavioral ones. Excel interview questions are only likely to make up only a small part of a larger interview strategy, so they will likely focus intensely on the most essential features of the software like formulas, functions and data formats.
You should strive to answer as many Excel interview questions as possible by tying in a personal experience. In the following text, well use the STAR method of answering interview questions, where applicable, to demonstrate how this formulaic approach can be beneficial.
The asks you to consider the following strategy when answering interview questions:
-
Situation: Describe a challenging situation that relates to the question.
-
Task: Explain your role in the solution, demonstrating tasks you deployed to complete it.
-
Action: What action were you responsible for?
-
Resolution: How did your solution lead to a satisfactory outcome?
Related: 50 Questions To Help You Ace an Interview Test on Excel
Read Also: How To Make An Interview Successful
Fair For All Candidates
Weve built our platform and tests on the standards of the Equal Employment Opportunity Commission to remove unconscious bias from the hiring process. This means youll see your best candidates ranked the highest regardless of gender, race, ethnicity, or age. We also periodically use differential item functioning studies performed on data collected from candidates. This helps us to continually remove any potential adverse impact from the hiring process.
Designed For Elimination Not Selection
The most important thing while implementing the pre-employment Excel Test in your hiring process is that it is an elimination tool, not a selection tool. In other words: you want to use the test to eliminate the candidates who do poorly on the test, not to select the candidates who come out at the top. While they are super valuable, pre-employment tests do not paint the entire picture of a candidates abilities, knowledge, and motivations. Multiple easy questions are more predictive of a candidate’s ability than fewer hard questions. Harder questions are often “trick” based questions, which do not provide any meaningful signal about the candidate’s skillset.
Also Check: How To Answer Nursing Interview Questions
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 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.
Recommended Reading: How To Score An Interview
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.
Excel Assessment Test Sample Questions
Below is an example of Excel test questions which are likely to appear on an interactive test. To practise the following questions, copy and paste the chart below to a clean Excel worksheet left click, hold and drag to mark the table, right click and choose copy. On the Excel worksheet select the first cell on the left , right click and press paste. Be certain that the cell named A1 is indeed located at cell A1 on your spreadsheet.
| First name |
|---|
Recommended Reading: Software Engineer Soft Skills Interview Questions
What Is A Spreadsheet
Heres a basic question that might segue into a more complex line of Excel questions. Since not all positions that are required to use spreadsheets are technicalsome may be financial or administrative, for examplea question like this lets you demonstrate functional technical competence in a short answer.
Example:A spreadsheet is a digital ledger for storing values. It contains rows and columns that make up cells where information can be held.
What Is The Use Of Vlookup And How Do We Use It
The function VLOOKUP in Excel is used to look up information in a table and extract the corresponding data.
Syntax: VLOOKUP
value – Indicates the data that you are looking for in the first column of a table.
table – Refers to the set of data from which you have to retrieve the above value.
col_index – Refers to the column in the table from where you are to retrieve the value.
range_lookup – FALSE = exact match TRUE = approximate match .
Shown below is an example of the VLOOKUP function. We are to find the Product related to the Customer Name Richard.
Recommended Reading: What Are Typical Interview Questions
Excel Functions Assessment Test
Intermediate or advanced Excel assessment tests might focus on using Excel functions if that is a key part of the jobâs responsibilities.
What are some functions recruiters should include in the Excel assessment tests?
How To Use Microsoft Excel Tests In The Hiring Process
For candidates who are interviewing for positions that donât use Excel regularly, it may not be as essential to assess their skill level in Excel. If they only need to read an occasional spreadsheet, other skills may be more critical in the hiring process.
For other positions, where simple Excel tasks are needed periodically, it is possible to train employees and teach them the essential Excel skills they will need. If a candidate fits all of the other job requirements, it may be worthwhile to train them in the simple Excel tasks they may need to perform.
However, if the position youâre looking to fill requires proficiency in Microsoft Excel, job candidates should ideally possess the skills and knowledge needed for their role. If they get hired, they should be able to fulfill all their job responsibilities without first learning how to use Excel.
To evaluate each job candidateâs skill level in Excel, test all prospective hires with a Microsoft Excel assessment test.
Depending on their role, employees may need different levels of expertise. You may choose to give different tests depending on each jobs skills.
Also Check: How To Interview A Nanny
Recommended Reading: Nursing Job Interview Questions With Answers
What Is The Difference Between Count Counta And Countblank
The count function is very often used in Excel. Here, lets look at the difference between count, and its variants counta and countblank.
1. COUNT
It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted. Shown below is an example of the count function.
2. COUNTA
It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted. Shown below is an example of the counta function.
3. COUNTBLANK
As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration. Shown below is an example of the countblank function.
Free Course: Business Analysis Basics
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
Recommended Reading: How To Follow Up On An Interview
Heres What Our Customers Say About Us
omg, nice contents! it helped a lot
csda sauer
Love how you take time to review all the answer choices and teach the material so thoroughly at a pace thats easy to stay up with. Thank you!
Saiful Azri Ayob
You just made this topic really simple to understand and to apply. The video edition is great.
How Can We Merge Multiple Cells Text Strings In A Cell
To merge text strings present in multiple cells into one cell, you can use the CONCATENATE. Shown below is an example of the concatenate function.
Another way of combining cell values is by using the & operator, as shown below:
Lets now move onto the next question on our Excel interview questions list.
Recommended Reading: How To Do A Pre Recorded Video Interview
How Do Interview Tests On Excel Work
A company can ask applicants to take an Excel test during the interview process. The test can help employers screen job candidates for their skills. This can be an opportunity for you to show a business your spreadsheet abilities. Some companies provide multiple-choice or interactive Excel tests while others use online tests to evaluate applicants abilities. An interactive test can require you to use Excel to answer questions. You can ask a company which type of test it uses if the information isnt available on the job posting.
Dont Miss:
Stuck Heres The Answer Key
Also Check: What Questions Should I Be Prepared For In An Interview
How Employers Test For Excel Skills
There are multiple providers that conduct the tests and each one of the tests is a little different. Most of the time employers use below providers to conduct the tests
Because Microsoft Excel is so popular and useful, companies are trying to evaluate candidates by conducting pre-employment assessment test. An Excel Interview Test is a screening process employers use as part of the candidate employment interview to test a potential candidate on their knowledge and proficiency of Microsoft Excel.
Each employer might use a slightly different variation of the Excel test. The Excel Assessment Test typically broken down into a multiple-choice section and an interactive portion. Some tests are timed but some of them do not have a limit on how long applicant can take to answer the questions, but there might be exceptions.
What Is A Pivot Table
A pivot table is like a summary table of the dataset that enables you to create reports and analyze trends. They are useful when you have long rows or columns that hold values you need to track.
To create a pivot table, first, go to the Insert tab and select the PivotTable option.
Select the table or the range and choose where you want to place the pivot table.
Drag the fields you wish to show in the pivot table. Here we have created a pivot table using the Coronavirus data.
Read Also: Java Coding Interview Questions For 10 Years Experience
Excel Test Results: Grading
The Excel Test Results file contains the test answers, so do not send this file to your job applicants. It is for grading purposes only and should be used as a guide to examine how well the individual performed on their assessment. Pay attention to individual questions and not just the final score, as some applicants may score lower overall but have more proficiency in the area you need.
Scroll through the images below for information on what each results sheet illustrates about your candidate.
The completed CLUBDATA tab shows how effectively your candidate can input, format, filter, and sort data, as well as use formulas, to standardize client names, email addresses, and other data.
The completed PIVOT tab shows the candidates ability to create and manage a pivot table using provided data and manipulate the data within the pivot table to deliver the desired results.
The completed RECORDCOUNT tab demonstrates the candidates ability to use the created pivot table to count values and grand totals using tabs.
The completed ALLFEMALE tab shows the candidates ability to sort, extract, and format data in the spreadsheet such as name, year, and birthdate.
The completed ALLFEMALESPORTS tab shows the candidates ability to use LEGENDS, VALUES, and AXIS tools to extract data and create an aesthetically pleasing chart.
