The Top Six Advanced Excel Skills Employers Look for

Many administrative and accounting positions SkillsetGroup fills require advanced Excel skills – from shipping and receiving clerks and office coordinators to financial analysts, accountants and financial planners, etc. You'll want to know excel inside and out, skills like functions, setting up a coherent spreadsheet and vlookup are just the start.

Unfortunately, a lot of employees seeking work in these fields are not taking extra online classes or keeping up with their advanced Excel spreadsheet formulas and skills. Many otherwise fantastic candidates have narrower prospects because of they are missing basic Excel proficiency.

A polished resume that hides gaps in skills is no good for anyone, employer or employee. 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 by an overreaching resume, your business will suffer because you don’t have the expertise you thought you got by hiring the candidate.

Basic Functions

SkillsetGroup coaches and tests candidates on Excel proficiency level 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. An advanced user should know how to use all of the functions laid out in this article, including skills like the SUM function and vlookup.

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 on. (Check out Microsoft's help portal for all kinds if Excel tips and tutorials).

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. XLOOKUP is more because it allows for more precise values to be targeted than VLOOKUP, though both serve their situational purposes.

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 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 understand months and years of weather data at a glance.

INDEX/MATCH

The MATCH function helps you find the relative position of information in the Excel 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. Processing quarterly revenues for different products over the same timeframe, for example, would be a standard use for INDEX/MATCH.

Pivot Tables and Reporting

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

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

As mentioned in the advanced conditional formatting entry above, showing the importance and context of data in a table can be tough without a graphic interface.