The Top Six Advanced Excel Skills Employers Look for

A lot of the administrative and accounting positions SkillsetGroup fills require advanced Excel skills – shipping and receiving clerks, office coordinators, financial analysts, accountants, financial planners, etc.

Unfortunately, a lot of employees seeking work in these fields are not taking extra online classes or keeping up with their spreadsheet skills. Many otherwise fantastic candidates have narrowed prospects because of their skills gaps.

A polished resume that hides gaps in skills is no good for anyone – that goes for our clients and our candidates. It’s especially tough to make a good impression on an employer if the Excel skills listed on your resume don’t match your actual skills. If you are an employer fooled

SkillsetGroup takes the time to coach and test candidates on what our clients are looking for. If our client needs someone with advanced Excel skills, we’ll test them to make sure they’ve accurately represented those skills on their resumes.

Unlike other firms, we share those failed test results with the candidates so they know how to improve, even if we can’t use them for any of our current openings. Taking that small, extra step to develop talent improves the chances of a great employee fit for our client, a successful and lucrative career for our candidate later.

In that spirit, we’ve collected and summarized the six most important advanced Excel skills employers look for. If you’re already an expert, it never hurts to polish up that expertise, especially because Microsoft institutes functionality and other updates to Excel every year.

If you’re looking for a clerical, administrative or accounting job that requires advanced Excel skills, you’ll need to show proficiency in at least the following six functions:

Data Simulations

There are many kinds of data simulations. For instance, GM uses them to forecast net income by predicting structural and purchasing costs; Lilly uses simulations to figure out the optimal plant capacity for each medication it manufactures; financial planners use it to forecast investment returns… the list goes on.

The mathematical technique powering Excel is called the “Monte Carlo simulation.” The algorithm plugs in random numbers for different variables and performs the forecasting function thousands of times to create average probabilities of different numerical outcomes.

If you’re hiring someone with “analyst” in their title, they need to be able to run these simulations.

VLOOKUP and XLOOKUP

These functions allow you to find content in cells of the Excel table. It sounds fairly straightforward, but parsing the parameters of the search is fairly involved. You have to build syntax including the range and set the search for exact or approximate matches, etc. The detail with which you can craft your VLOOKUP search will dictate the detail of the insights you draw from your spreadsheet data, whatever you’re parsing.

XLOOKUP is a functionality similar to VLOOKUP with greater flexibility in returning results.

Advanced Conditional Formatting

Color coding is a powerful tool to visualize data. That’s why you can set cells, columns and rows to a variety of colors in Excel. You can use this feature to play – Microsoft one year made a birthday cake “painting” in Excel to celebrate the program’s anniversary, for example.

But the real value of color coding is to help make information clearer and identify trends in data.

For example, if you’re setting up a historical weather data spreadsheet in black-and-white it will look like an opaque block of text that’s difficult to understand. But if you use conditional formatting to set the cells housing higher-than-average temperature values to appear red, lower-than-average temperatures to appear blue and average temperatures to appear green, you can assimilate months and years of weather data at a glance.

INDEX/MATCH

The MATCH function helps you find the relative position of information in the spreadsheet. For instance, if you have a cell that reads “25,” you can set the MATCH function to look for cells with the number 25, and the results will show you the cell housing the data you’re seeking.

The INDEX formula returns the value of a cell or array of cells row and column numbers.

These functions, together, are useful for parsing numbers in different but similarly arranged data sets – quarterly revenues for different products over the same timeframe, for instance.

Pivot Tables and Reporting

A pivot table is a summary of data from a larger set. The format of the pivot table lets you parse different statistics in different ways, comparing different correlations and relationships among values (“pivoting”). This is handy to draw insights easily from financial or statistical data.

Adding pivot charts to your sheets should also be in your Excel expert skill set. To build a bar graph or other similar data visualization, the pivot charts feature dynamically parses the data you’ve set up in a pivot table.

As mentioned in the advanced conditional formatting entry above, communicating the importance and context of data in a table can be difficult without some sort of graphic interface.

Macros

Macros are simply a set of commands the user builds and saves to execute automatically whenever the process is needed.

For instance, you might want to see all your clients who are past due on their payments at the end of each month. You could set up a macro that includes commands to create a table from your accounts receivable data, limited by date and formatted to show past-due payments in red.

Once you’ve set up this macro, you can run it again and again with a single mouse click.

For more information on getting hired or securing top administrative and accounting talent, contact our Division Manager Cori Garcia at 877-272-6950.