From the time it was released during the 80’s MS Excel has become one of the most widely used business tools. Critical business decisions are often based on Excel analyses. For this reason, it’s one of the most important skills to confirm, prior to hiring. It is also one of the most embellished skills on resumes.
Consider the common scenario where a candidate has been hired and has embellished their Microsoft Excel skills. They constantly bother co-workers with ‘how to’ questions and their mistakes go unnoticed at first. Their errors are eventually picked up when things have gone wrong with an order or, a misleading analysis due to an incorrect formula. The reality is that it’s often some time before mistakes on MS Excel spreadsheets are picked up – after the damage has been done. The individual then requires training on skills they should already possess, costing businesses time, money and unnecessary stress.
Here are our TOP 10 MS Excel skills to test for when hiring to avoid costly hiring mistakes.
1. The MS Excel User Interface
What should they know?
Understanding the user interface is imperative. Users should have a solid grasp of the buttons such as Home, Edit, Undo, Redo and also the Keyboard Shortcuts and Ribbon Options. The should understand how to use workbooks and how to navigate between them.
2. Data Protection
What should they know?
One increasingly important skill is learning how to protect worksheets, reports and information. Businesses employ complex and unique formulas in their reports in order to interpret results and build their corporate strategy. Many datasets include sensitive data coming from individuals, companies or other entities. In order to safeguard formulas from editing or prevent unauthorised third-party access, employees need to know all the “tips and tricks” available, ranging from using passports to locking formula cells.
3. Insertion & Analysis: Sort, Filter, Find & Replace
What should they know?
A single worksheet can contain several tables. Handling the data across these tables is difficult if you were to do it manually. That’s why we have the Sorting function that allows organising entries by last name, price and etc. Additionally, the Filter function is used to identify rows of data entries that match particular criteria. For example, say you need to list of particular customers in an insurance company above the age of 40. A Filter function helps to do this. Last but not least, Find and Replace is as its name suggests, is a function that helps to find and replace characters and other values from within a workbook. It sounds easy but can be quite tricky to work with. Users must be cautious when using it because many areas are often updated simultaneously.
4. Insertion & Analysis: Name Ranging
When you’re working in MS Excel it’s also important to know about Name Ranging. Name Ranging allows a user to give names to cells and formulas. This allows time savings by typing a name instead of having to search the row throughout the workbook.
What should they know?
Knowing how to name cells and formulas is important. Following the naming conventions of Name Ranging is also helpful. Users should know that the first letter of a name can be a letter, an underscore ( _ ) or even a backslash ( \ ) and afterwards, anything from alphanumeric characters, periods ( . ) and even underscores. Also, the names should not be similar to cell references such as A1C2, B$200 and etc. Additionally, users should also note that cells do not distinguish between capital and simple characters.
5. Functions and Formulas
What should they know?
Basic algorithmic functions such as addition, subtraction, IF/AND/OR Functions. The latter gives a Boolean result (true or false) about the data, allowing the user to cluster it in relation to a condition – they’re commonly known as logical operators and allow for the Excel system to make a decision for the user.
6. VLOOKUP an HLOOKUP
VLOOKUP and HLOOKUP’s helps to group and analyse data across columns or rows, for a particular value. Depending on the function, a user can search vertically among the rows or horizontally among the columns.
What should they know?
Using VLOOKUP as an example, they should know that =VLOOKUP is (“the value you’re looking for“, “the table starting from the leftmost column from the first row to the rightmost column on the last row“, “the index of the column starting from the leftmost column 1 to the rightmost column N“, “true = approximate match, false = exact match“).
7. Formatting with Charts & Slicers
What should they know?
Charts help in visualising data and are especially useful for presentations at meetings. The ability to create Pie Charts, Column Charts, Line Charts, Bar Charts, Area Charts and Scatter Charts is important.
Slicers are another commonly used Excel feature that provides buttons that a user can click to filter table data or PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable.
8. Summarising with Pivot Tables
Pivot tables summarise and help to identify unique values in a field. They can, for example, help to quickly identify any typos and the unique values from within a large data set.
What should they know?
Being one of the most substantial Excel tools, the user should know how to use Pivot tables to re-structure and summarise selected columns etc. of data across large amounts of entries. They should be able to generate an output format that is a database table, containing significant or meaningful information on the values selected.
9. Macros
A Macro is a common function utilised by more advanced users. Macros provide an automated input function that replaces a repetitive series of mouse actions, commands or keystrokes. With macros, a proficient user can have Excel deliver troublesome tasks that could take hours, in a matter of a few seconds.
What should they know?
The user should know how to use Macros to apply style and formatting, manipulate data and text, communicate with data sources (database, text files, etc.) and even create entirely new documents.
10. MS Excel Integration
Excel does not act in isolation within a person’s workflow. It is often useful to build in questions on using MS Excel with other tools like PDF, Microsoft Word (MS Word), Microsoft PowerPoint (MS PowerPoint) and Microsoft Outlook (MS Outlook). It is useful for users to know how to fetch data from other sources such as the cloud, sending emails through Excel or, exporting to a properly formatted PDF document.
RightPeople’s Microsoft Excel tests cover all levels including Basic, Intermediate, Advanced and Expert.
For more information and to purchase Microsoft Excel tests for hiring or staff development, visit RightPeople Candidate Testing https://www.rightpeople.com.au/product-list/skills-tests/microsoft-excel-test.html
