Split Name Column Into First And Last Name Using Text To Column Feature
We need to split name column into first and last name using the text to column feature of Excel. To do that, let’s analyze the column and it looks like we have both first and last name in the column D and it has purchasers name.
To do that let’s select column E right click and click insert to add a new column E next to the column D. So, which would help us to put this extra value that will be created right into column E.
You can use text to column feature select column D and then click on the text to column and it prompts if I’d like to use text to multiple columns and what kind of delimiter we have.
So, we select delimited then we’ll click space, make sure to select that then we click Next.
Now, weve split them accurately, we want to change the general data type into text data type because both of those are text and then we click finish
And it shows that there is already column E in place and shows us the message there is already data here do you want to replace it, if we wouldn’t have created column E, it would have overwritten the column F.
We’ll click OK on this message and you see now it added the values. So, instead of purchaser, column D we will call first name and column E we’ll just call last name
How To Assess Excel And Data Management Skills In A Job Interview
Excel and data management skills are often assumed upon finance and accounting professionals. However, some positions are better served by those who are exceptionally strong in this area. How does a hiring manager assess Excel and data management
-
- Its hard to assess Excel skills in interviews
- Ask what functions theyve used, such as sumif,vlookups, and if
- Ask what their favorite advanced funciton is
- Ask about classes, certifications, and online training
- Before the interview, ask them to bring an Excel analysis theyve done to discuss
- Present an Excel file and ask them to complete it, fix it, interpret it, etc
- Ask about what the conadidate believes are his/her potential Excel weaknesses
- Ask them to create a pivot table or other advnaced function
- Attempt to understand their willingness and ability to learn new things
- Plus much more below!
Heres another take on how to get to the bottom of an applicants Excel skills:
Create Pivot Table From The Data
To do this, let’s navigate to the tab sales data tool – we can click on any cell in the table and we need to click insert and then pivot table.
It prompts us which table would you like to create pivot table from which is table range. We’ll create it in a new worksheet I will click OK.
NOTE: My students often ask me the best way to learn features of Microsoft Excel faster. I typically recommend experimenting. The best way to experiment is to download simple set of data or maybe use copy of production data. Using your data samples, you cannot try to solve real business problems and then set specific business questions. You can always go back by pressing ctrl Z this is undo function in Microsoft Excel.
Recommended Reading: How To Prepare For A Marketing Interview
What Are Common Data Formats In Excel How Are They Used
This type of basic fundamental question might introduce a line of progressively more complex interview questions about Excel. This question is structured to gather your basic understanding and knowledge of the software.
Other basic Excel questions may include:
-
What are some popular formulas youve used?
-
What does a specific symbol mean in Excel?
-
Whats the difference? questions asking to compare common features
Example: Here are the common data formats in excel and how they are used. First, there are numbers that can be formatted as decimal points or round values. Then, there are percentages that display a number as a portion of a whole, in which the whole is equal to 100. There are also a number of ways you can display datesdepending on your region, you can choose the format that makes the most sense. Lastly, strings of text can be a data format as well.
Related: 4 Data Types in Microsoft Excel
Your Complete Solution For Skills Testing
Immersive questions
With 10 unique question types from code challenges to working spreadsheets to engage your candidates and place them inside real world scenarios.
Content library
Over 300 ready to use assessment templates for a variety of different roles plus a verified bank of 300,000+ questions to draw from to create your own.
Add your logo and corporate colours along with personalised intro videos and introduction notes for candidates.
Easy invites
Invite candidates to your assessment by email, or get them to apply by taking the assessment directly from your job ad, social post or careers page. Use an ATS? We have a growing suite of ready to use integrations.
Complete skill profile
Results are displayed with overall scores and rank plus breakdowns of achievements for individual skills. Top performers are easy to spot with badges and trophies.
Candidate report cards
In-depth report cards for each candidate including a breakdown of results and achievements for each skill along with full assessment responses.
Collaboration tools
Easily shortlist, compare and share candidate report cards with hiring stakeholders. Leave comments and tag candidates for further stages.
Recommended Reading: How To Get Ready For An Interview
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
Typical Format Of An Excel Test
The Excel test may be presented as a series of multiple-choicequestions or it may use interactive software to simulate the use of Excels interface.
Some companies will set a test they have built themselves. This would usually be an interactive test and will often involve working with Excel software itself. In the majority of cases, companies will use specifically designed software from a test provider.
Multiple-choice tests will include questions covering every area of the software, so candidates will need to be very familiar with key functions and layout. The multiple-choice questions are pulled at random from a large pool of possible questions, so no test is the same.
The simulation format is more common for Excel tests. Here candidates must complete a series of tasks on an Excel spreadsheet, according to their skill level.
Candidates will be asked to sit a basic, intermediate or advanced level test, depending on the position they are applying for. We’ll cover each in turn.
Read Also: What To Wear To A Job Interview Female
How Do I Interpret Test Scores
The primary thing to keep in mind is that an assessment is an elimination tool, not a selection tool. A skills assessment is optimized to help you eliminate candidates who are not technically qualified for the role, it is not optimized to help you find the best candidate for the role. So the ideal way to use an assessment is to decide a threshold score and invite all candidates who score above the threshold for the next rounds of interview.
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: Technical Interview Questions For Engineering Manager
How Is A Formula Different From A Function In Excel
As seen above, first, the data in the parentheses is operated, followed by the exponentiation operation. After that, it can be either the division or multiplication operations. The result is then added and finally subtracted to give the final result.
Lets look at an example of the PEMDAS precedence in the next question on our Excel interview questions list.
Change Tab Title To Sales Data Assign Data Types To Imported Columns
Selecting correct data type is very important especially when we convert the data from the imported file into Excel table later as part of this exercise.
To do that first, let’s change the tab title. To do, that we will click on the tab and you see it became editable and we’ll just type sales data.
In the next step, we will need to assign data types here to do that you need to select the entire column and this is the date data type so and you see right now it’s general. So, we would need to change it from general to short date. That’s the most appropriate data type.
- Then for the entire column eBook, we use Text datatype.
- Then for price column, we use datatype currency.
- For Purchaser Names column, we will use text datatype.
- And Purchaser Address column will be change to text datatype.
A lot of questions in this video are covered as part of my eBook. If you’re getting ready for Excel interview or assessment test make sure to check out my eBook store to make sure you get prepared faster.
Read Also: How To Do An Effective Exit Interview
Hiring A Cost Estimator
A cost estimator analyzes data to estimate how much it costs to produce a product or create a service. This estimate includes the materials, labor, and time involved. Therefore, reading and understanding raw data is a key part of their job and this requires intimate knowledge of Excel and its functions.
The Excel skills to look for and test are:
How To Identify Candidates With Advanced Microsoft Excel Skills
Professionals skilled in Microsoft Excels advanced features remain sought after, with the program still widely used today. However, with many people using Excel, it’s hard to figure out how skilled job applicants are at using the program’s advanced features. So how can you find the right candidate with the adequate skills for the position?
Recommended Reading: What Interview Questions To Prepare For
What Is The Purpose Of Flash Fill And How Do You Activate It
Flash Fill is an Excel feature that can be used to quickly fill cells with data based on user behaviors. Flash Fill is often turned on by default, so to use it you can begin typing. If Excel identifies a pattern, the program will suggest data for the cell. You can press enter to accept suggested data in Flash Fill. If Flash Fill is turned off, you can activate it by selecting Tools > Options > Advanced > Editing Options and clicking the checkbox that says “Automatically Flash Fill.”
Mention The Charts Available In Microsoft Excel And Their Primary Uses
Interviewers typically ask this question if the role involves data analysis. Your response should demonstrate how to use Excel to analyze and explain data points to non-data inclined people.
Example answer: Excel charts are used primarily to create a graphical representation of trends and relationships in data to make the information easily accessible and understandable. The different chart types include the bar, line, pie, scatter, and column.
Also Check: Best Interview Clothes For A Woman
Read Also: What Questions To Ask In A Nurse Practitioner Interview
Microsoft Excel Skills To Look For
Like other software, Microsoft Excel has varying degrees of difficulty. The softwares skills proficiencies increase from more generalized spreadsheet and table management and interpretation to expert-level calculation and formulation skills. This means that when youre hiring, the skill level youre recruiting for varies depending on the role and the level of data interpretation the position needs.
What Are Charts In Excel And How Are They Used
There are a number of reasons charts can be useful in Excel. For one, they show important information in ways that are easy to understand. They also provide those reading charts with valuable insights about data and the relationships that exist within a subset of data. For this reason, it may be important to demonstrate you understand how charts are used.
Example:Charts are graphical representations of data in Excel. They show data insights, like relationships and trends, in a way thats easy to use and understand.
Related: How To Make a Chart in Excel
Recommended Reading: Edward Jones Financial Advisor Interview Questions
What Roles Require Excel Skills
Virtually every industry needs workers with basic Excel skills one study found that Excel skills are required for 82% of middle-skill positions. Advanced Excel experience is only required for niche positions, such as an economist or data analyst.
CareerKarma compiled a list of some of the best jobs that use Excel in 2022, as well as the market outlook for hiring these roles.
| Job Title |
| -2% |
As you can see, many of these jobs are industry agnostic. Administrative assistants, office clerks, and sales managers can find jobs in many different companies. As you write your job descriptions, think about how your open role will be competing against potential employers who may well be outside of your industry.
Excel skills can be categorized as beginner , intermediate and advanced. Excel assessments vary depending on which skills are considered intermediate vs. advanced. For instance, some consider pivot tables to be relatively intermediate.
It depends on the role for which you are hiring, the industry youre in, and if there are other employees who have compatible skills on your team already.
Heres a breakdown of how to design a skills assessment to understand how your job applicants stack up.
Identifying Advanced Excel Skills
Before you start recruiting, you should determine the required aptitude level for the role. Excel skills are separated into three tiers, with varying proficiency required for each.
Basic: For job applicants, these skills include the ability to use common Excel keyboard shortcuts, cell references, styles, and data ranges. Other basic Excel skills include adjusting view options, showing formulas, and basic spreadsheet formatting.
Intermediate: When recruiting candidates with intermediate Excel skills, they should be able to use formulas and functions like AVERAGE, SUM, and SUMPRODUCT. They may also be required to have a working knowledge of creating graphs and charts.
Advanced: Applicants with expert-level Excel skills will be able to use various formulas, Visual Basic for Applications programming, and complex task management in the program. They should also be capable of using Microsoft Excel for data analytics and simulation.
When you’re recruiting, the role’s needs and duties will determine the level of Microsoft Excel aptitude required. If you need candidates to use spreadsheets, graphs, calculations, and automation to process large amounts of data, you may be looking for advanced Excel skills.
Recommended Reading: What Questions Should You Ask Employer In Interview
Why Purchase Excel Practice Files
