Critical MS Excel Areas to Test – Prior to Hiring

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

Fig 1.1 – MS Excel User Interface. (Source: RightPeople, 2018).

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

Functions and Formulas in Microsoft Excel

Fig 1.2 – Functions and Formulas. (Source: RightPeople, 2018)

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://rightpeople.com.au/product-list/skills-tests/microsoft-excel-test.html

Navigating the world of telework

The rollout of the National Broadband Network (NBN) has created numerous opportunities for more flexible work options, also called telework. A report by Deloitte Access Economics predicted that flexible work created by the NBN would be one of the biggest structural changes to the labour market this decade, creating an additional 25,000 jobs by 2020. The research also found that almost 50% of the mature workers (aged 45-64 years) and one-third of the part-time workers surveyed had at least partially flexible work patterns.

Fast broadband technology and other structural changes are eroding the idea of the traditional ‘office’ workspace. We are increasingly seeing the rise of new forms of flexible work, including working from home, a mixture of working from home and office, regional workspaces, working from client workplaces etc.

» Read more

Ambiverts, Extraverts, and Sales Job Performance

In this article we look at Ambiverts, Extraverts, and Sales Job Performance and announce the new Accounts Payable and Receivable Test

Sales Aptitude Personality Research

Article Highlights

Extraversion vs Introversion is one of the most studied personality dimensions. It was a long-held belief that extraversion would “take a person further” in business, particularly in sales. The most recent research supports a third personality type that is in a way, a combination of qualities of both introverts and extraverts. These personalities stand to make the biggest impact in leadership and in sales. The implications suggest that:

  • Organisations should screen candidates around the “right disposition” and sales aptitude rather than relying on training people to cope with the pressures and emotional aspects of being in sales.
  • Organisations should consider re-thinking their personality style benchmarks for hiring sales staff and other roles where high levels of extraversion might seem like a key criteria.
  • The research continues to show that regardless of “personality type”, the qualities of self confidence, commitment, etc. are always going to impact the balance of this scale.
Sales Aptitude Personality Model

Sales Aptitude Personality Model

» Read more

Employee Engagement Essential in Professional Services Firms

The importance of employee engagement has been discussed in previous posts. Recent research indicates that nowhere is employee engagement more important than in professional services firms. While these firms are necessarily primarily client focused, ultimately the success of professional services firms relies on the success of their employees’ work. And the most successful employees are engaged employees.

In fact, in professional services firms employee engagement is a strategic business initiative.

» Read more

English Second Language Training and Support

In Australia, on average 15 percent of all school students come from a non-English speaking background. In some states it is much higher: in New South Wales this figure is almost 25 percent English Second Language Students, as it is in the Northern Territory, and in Victoria it is 20 percent. According to Adriano Truscott, president of the Australian Council of TESOL (Teachers of English to Speakers of Other Languages) Associations, Australia has an “outstanding tradition” in teaching English as a Second Language (ESL).

It is therefore vital that we have a great system for tracking the progress of our English Second Language Students (ESL) students, to cater for the needs of our high number of ESL students, as well as to safeguard our reputation as a leader in this field.

» Read more

Multitasking Ability and Intelligence

When dividing attention between two tasks (Multitasking), people need to find the most efficient ways of allocating their attentional or processing resources between the tasks. It is reasonable to presume that people with higher levels of intelligence will be better able to allocate their processing resources efficiently. This was investigated in a study by Ben-Shakhar and Sheffer. Read on to learn more about their findings and how RightPeople has used this information to help you make better choices during your recruitment process.

» Read more

Employee Retention Linked to Shared Values and Interests

Research by the American Psychological Association has found that workers are more likely to stay with employers when there is alignment between their values/interests and those of the organisation. They found that these factors were more important than remuneration and benefits.

The Workforce Retention Survey, conducted with American workers aged 18 years and above, examined a range of factors associated with employee retention. Even in the current economic climate, values and meaning were key drivers of employee satisfaction above financial considerations.

» Read more

Skills needed by multi-taskers

Brown (1998) investigated the concept of “time-sharing” proposed in earlier work on multi-tasking by Fogarty (1982) . Fogarty (1982) describes time-sharing as a factor that emerges when two tasks are undertaken simultaneously. This sharing of time between the tasks is the extra factor over and above those associated with performing each of the tasks in isolation.

In Brown’s paper two experiments were undertaken, one that involved completing a single task (manual tracking) and one dual tasks (manual tracking and a timing task). The following outcomes were found:

 

    • An interference effect. Doing two tasks at the same time disrupted the speed of the timing task and made it more variable.
    • A relationship between practice and interference. Practice on the tracking task under single-task conditions reduced the interference effect in timing. However, practice on the dual-task test was not successful at minimising the interference effect.

» Read more

Getting the most out of psychometric assessment

Our previous post Organisations are reaping the benefits of psychometric assessments summarised the outcomes of recent research by a leading organisational research firm which revealed the relationship between HR success and the use of psychometric assessment tools.

This post provides more information about the ways successful organisations use psychometric tools and incorporate them into their talent search and development programs. The 516 organisations involved in the research were placed into three groups based on criteria indicative of employee performance and satisfaction: best performers (best in class), average performers (industry average) and those lagging behind in their industry (laggards).

» Read more

Organisations are reaping the benefits of psychometric assessments

Organisations are continually looking for tools and techniques to help them make better hiring and promotion decisions, as well as for ways to set themselves apart in the search for talent. A 2011 survey by Aberdeen revealed the high prevalence and impressive benefits of psychometric assessments as part of a talent recruitment and development programs, particularly in an era when many organisations cannot afford to spend significant amounts of money hiring new staff.

The survey involved over 640 organisations, including more than 500 that used assessments as part of their hiring and talent development strategy. The researchers used several benchmarks of organisational HR success. These included: high rates of employees receiving outstanding performance reviews, sound succession plans and manager satisfaction rates. Read on to find out more about what these successful organisations did to set themselves apart…

» Read more

Job/person mismatch is a leading cause of underperformance

Underperforming employees can be very costly to organisations. A 2009 article in the  Australian Financial Review found that unproductive employees can cost Australian businesses up to approximately $33 billion a year!

A primary reason for underperformance is a poor fit between the employee’s skills and interests and the needs of the organisation. Another related reason is managers not clearly specifying their expectations and the requirements of the role. Both of these can lead to an employee struggling to deliver on what is required. These issues are preventable however.

» Read more

Minimising the impact of disruptions in the workplace

Distractions such as telephone calls, emails, online notifications and colleagues stopping by for a chat, or even the time it takes to try to remove such distractions, are part and parcel of the modern workplace. We tend to take it for granted that long periods of uninterrupted work are few and far between.

Recent research has shown that this can have quite a damaging effect on productivity and work safety. A study conducted by Michigan State University found that interruptions as short as 3 seconds are enough to double the chance that an employee will make a mistake.

» Read more

Protecting your business against fraud and misconduct

As the business world becomes more diverse and expands across national boundaries, concerns about security and integrity in the workplace have never been higher. Stakeholder expectations for ethical business operations continue to rise, placing more responsibility on organisations to employ people who have high ethical standards.

To safeguard their organisations, many organisations incorporate background screening checks into their recruitment process. While the benefits of these are clear, there are legal ramifications which arise when a criminal record is revealed.  Refusing to employ an individual because they have a criminal record is against the law in Australia.

» Read more

Multi-score performance ratings work

Multi-score performance ratings are employee performance feedback systems where feedback is obtained from multiple sources (supervisors, peers, clients. They are very popular, with almost all Fortune 500 companies in the US using this approach (Cheung, 1999).

Why are they so popular?

Research shows that there are a number of reasons that multi-score (also often known as 360 degree) performance rating systems work so well. Read on to find out more.

» Read more

A theory on managerial success: managers need “soft skills” too

Research has shown that while intelligence in its traditional form, including tasks assessing verbal, numerical, visuo-spatial, reasoning and working memory, is the best predictor of job performance, other skills are also important for managerial success.

Sternberg’s (1996; 1997) triarchic theory of intelligence proposes that intelligence is comprised of traditional analytic skills, practical skills and creativity. He advises that managers need all these components of intelligence in order to be successful. Practical skills are those used in the workplace to guide interactions, help solve problems and knowing how to act in certain situations. They are usually acquired without the direct help of other people. Creativity in the workplace is about seeing old problems and situations in new ways, or the catchphrase of the early 2000’s: thinking outside the box.

» Read more

Put your money where your mouth is when it comes to employee ethics

A recent study by the Chartered Institute of Personnel and Development has revealed that organisations may not be doing enough to promote and protect their values within the organisation. The study found that 40% of employees believe that their unethical colleagues not only go un-reprimanded, but are also frequently rewarded and promoted for their bad behaviour.  In addition, only 29% of respondents had a good understanding of their organisation’s values and 15% had no awareness of them.

It’s not all bad news, though, with almost three-quarters (73%) reporting that it is at least somewhat important to them that organisations to have well-defined values which guide employee behaviour.

» Read more

Bribery and corruption – are organisations informed and prepared?

A recently released report outlining the findings of Deloitte’s 2012 Bribery and Corruption survey reveals that organisations in Australia and New Zealand may be ill-equipped to identify and manage corruption and bribery risk.

The survey was completed by 390 organisations from Australia and New Zealand, including publicly listed companies, Australian subsidiaries of foreign companies, public sector organisations and private companies.

» Read more

R your employees OK?

Thursday 13 September is National R U OK? Day.

R U OK? Day is a national mental health day on the second Thursday of September to encourage Australians to connect with their colleagues/employees by asking them: Are you okay?

One in five people experience depression at some point in their lives. That means that approximately 4 million Australians will suffer from depression. More than 2,000 Australians suicide each year. Depression is the most common mental illness, followed closely by anxiety.   Approximately 7 percent of Australian employees in any organisation suffer from depression each year.

So chances are that at least one person at each workplace may be suffering from a mental health problem right now.

» Read more

Keeping employees happy

Research has shown that some of the ‘perks’ that keep employees happy in their jobs are not the stereotypical ‘big ticket’ items that only larger businesses are able to offer.

Boredom, lack of opportunity and poor work-life balance are within the top four reasons that people leave their jobs, according to The Australian Human Resources Institute, rather than lack of perks such as corporate cards, cars and fancy technology. The Institute also found in a 2009 survey that employees value good communication and training opportunities over and above higher pay.

Other important considerations for employees, that are particularly relevant to smaller businesses that are unable to compete with large businesses on remuneration and work conditions, according to research undertaken by Deloitte, are:

» Read more

‘GPS building’ for your organisation

A GPS for business?

No, we’re not talking about a Global Positioning System for your car. But it is a similar principle. Many organisations and business leaders are arranging their organisation around GPS, or Guidance and Positioning Statements. Like a GPS in your car, an organisational GPS will guide your organisation to a chosen destination, providing clear directions and guidance along the way to shape the culture of the organisation. These statements can also be referred to as mission statements, values statements or charters.

An effective GPS is a key tool in fostering employee engagement.

A number of successful and high profile organisations understand this. BHP Billiton refers to their GPS (called a charter on their website) as the “single most important means by which we communicate who we are, what we do, and what we stand for as an organisation, and it is the basis for our decision-making”. Their Charter has existed for over 10 years with only minor modifications being made over that time.

» Read more

The hidden costs of employee disengagement

A recent research poll of Australian workers has found that more than 80% of Australian employees feel disengaged and work, with more than 20% being actively disengaged, that is behaving in ways that are actively harmful to the organisation.  They estimated that disengagement costs Australian organisations at least $33.5 Billion a year in lost productivity.  It also has other negative social effects outside the workplace, with disengaged employees taking out their negative feelings on their families and having more health problems.

As discussed in our earlier blog How engaged are your employees, employee engagement refers to the extent to which employees believe in the values and mission of the organisation, are committed to their work and will act in ways that further the organisation’s interests.  It integrates the well-known constructs of job satisfaction and organisational commitment.

Engaged employees are focused and connected at work, supportive of organisational goals and are willing to “go the extra mile” at work.

» Read more

Recruiting the Gen Y employee

What the research shows about Generation Y

Many surveys and studies on Generation Y (individuals born between approximately 1980 and 1995, earlier or later in some definitions) indicate that, as a group, there are a number of characteristics they tend to display that employers should be aware of when hiring and managing these individuals.

Specifically, research has shown that more than any other generation in the workforce, workers from Generation Y (Gen Yers) tend to:

  • Anticipate changing jobs frequently (with some research showing more than half of Gen Yers anticipate changing jobs every 2 years).
  • Be much more likely to move to a new job if their needs for challenge and career development are not met.
  • Have high expectations of their employers, including work-life balance, challenge, high salaries, career advancement and flexibility.
  • Apply for jobs in non-traditional ways, including via social networking tools such as Twitter and supplying applications relying on technology.

» Read more

Streamline your recruitment process to attract top talent!

A recent survey has shown that 79% of job applicants lose interest in a job when faced with a long recruitment process and 45% have withdrawn their application because they didn’t like the interviewer.

This is consistent with the experience of James Nicholson, managing director of the professional recruitment consultancy Robert Walters. Nicholson said he has consistently observed that the organisations that are slow to make decisions or fail to properly sell the role have difficulty attracting  the best talent available. A streamlined recruitment process is critical.

» Read more

Fraud and misconduct prevention

Fraud and misconduct “grey areas” in the workplace

Yesterday’s Human Capital Australia magazine outlined a recent decision by Fair Work Australia (FWA) and the implications for dealing with fraud and misconduct in the workplace.

A supermarket store manager was dismissed for taking groceries without paying, however, the dismissal was overturned by FWA and he was awarded more than $15,000 in compensation as the matter was not properly investigated. The manager claimed that he intended to pay for the groceries at a later stage and in the absence of an appropriate investigation there was inadequate proof that this was not the case.

In his judgement Commissioner Ian Cambridge said that a “proper, comprehensive and balanced” consideration should have been provided before dismissing the employee. A warning or dismissal with a notice period may have been a more suitable response. The supermarket should have undertaken a thorough investigation to determine whether misconduct had occurred and to ensure their rights and the rights of the employee were protected.

This ruling has a number of important implications for employers. The main implication relates to properly investigating alleged misconduct. Another important implication is that employers should be careful about who they employ in the first place.

The old adage “prevention is better than cure” has never been more appropriate.  Dealing with misconduct once it has occurred can be fraught with difficulty, as this case highlights. It is much easier to try to create a workplace where misconduct is unlikely to occur.

RightPeople has a range of psychometric tests designed to identify people who are most and least likely to engage in unethical and illegal behaviour within organisations. These tests look at attitudes, behaviours and other risk factors associated with wrongdoing.  It’s called the Risk Management Profile (RMP).  Specifically, the RMP identifies integrity, honesty, poor impulse control, stress tolerance and conscientiousness.  Used in combination with our personality inventory it can be an invaluable tool for safeguarding your workplace against fraud and misconduct.

Contact us to find out more.

Psychometric personality tests

Our last blog looked at psychometric tests and the difference between psychometric and skills tests.

The main focus was on cognitive ability tests and specific skills sets.  An interesting question, therefore, is where does personality fit in?

Personality tests

Personality tests are psychometric tests. They are based on personality research and theories about how personality is structured and how it can be assessed.  They have robust psychometric properties (high validity and reliability) and normative data gathered from many thousands of people. They look at a different type of individual difference: individual propensities to think and act in certain ways.  Unlike cognitive ability tests and skills tests there is no right or wrong answer. Different jobs and positions in jobs have different personality types that are best suited to them.  Although, there are certain personality traits where performance in a certain range is preferable for many jobs.

» Read more

1 2