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.
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.
What Are the Top Advanced Excel Skills for Administrative and Accounting Jobs?
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:
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.
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.
Macros are a set of commands the user builds and saves to execute automatically whenever the process is needed.
For example, 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 with commands to create a table from 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.
AutoFilter is another handy feature in Excel that allows users to filter and sort data based on specific criteria. This feature is particularly useful when working with large datasets where filtering through rows and columns manually can be time-consuming. With AutoFilter, you can quickly narrow down your data and focus on the information that is relevant to you.
To use AutoFilter, select any cell within your dataset and go to the “Data” tab. Click on “Filter” and choose from the different filter options available. You can also customize your own filters for more precise results. This feature saves time and allows for efficient analysis of data in Excel.
Add-ins are third-party tools and programs that can be integrated into Excel, enhancing its functionality. These add-ins can range from simple tools like calculators to complex ones that allow for data visualization and analysis. There are a plethora of add-ins available for Excel, catering to various needs and industries.
To install an add-in, go to the “Insert” tab and click on “Get Add-ins”. From there, you can browse through the different add-ins available in the Microsoft Store or search for a specific one. With add-ins, you can customize your Excel experience and increase your productivity.
Want to learn more technical aspects of Microsoft Excel? Check out Microsoft's library of forms and functions.