It was 2010, I was just out of college and searching for my first job, I was scrutinised in an interview to check whether I have required excel skills or not.
Thankfully I was fully prepared to answer their questions and got selected for the position.
I think it’s most common situation for young & experienced professionals nowadays. Increasing dependency on excel is actually demanding people with various skills in excel.
Although we have so many analytical software’s with fancy Dashboards & In-depth analytical options, MS Excel takes centre stage.
Certainly, it’s not because it’s developed by Microsoft. I think the capacity to work offline, affordable cost and User-friendly interface will make the difference.
According to a research conducted by Burning Glass, “nearly 8 in 10 middle-skill jobs require digital skills like Spreadsheet & word processing”.
Digitally intensive middle-skill occupations are growing faster than other middle-skill jobs. Digitally intensive jobs have grown 2.5 times more rapidly than middle-skill jobs that do not require spreadsheets, word processing, or other digital skills (between 2003 and 2013, 4.7% growth for digitally intensive jobs compared to 1.9% growth for other positions)
I’m sure the research results are very valid & useful for all of us.
But the real question is within excel what are the skills that employers are most interested.
If you attend an interview tomorrow for a financial analyst job, what type of excel skills related questions you can expect..?
To answer the above questions, I’ve done extensive brainstorming to understand further, and listed down all the required skills.
I honestly believe our infographics will make you stand out of the crowd. Off course, you don’t have to search for excel skills employers looking, like I did before, in fact, I’ve already done that for you.
So let’s get started.
List of Excel Skills employers looking:
In this fast-moving world, your business or management would expect you to complete your work by yesterday. So how do you deal with this..? no worries.!
We’ve listed down most used excel skills where you’d need to work on, and the good news is you can learn all of them by referring our recommended links.
Please note: I’ve provided a couple of tutorial links for each subject for better understanding, If you think they are not up to the mark as per your need, then you may search further.
#1 Basic Navigation
Believe me, nobody has time to teach you how to navigate excel file when you are on the job.
So you should have an understanding about various Menus (Home, Edit, Formulas …) you should know where to find Filter, where to find Sort, how to use Formulas, How to save workbooks in different formats ..etc.
You may refer following tutorial about MS Excel navigation
#2 Formatting
Formatting data as equally important as Analyzing.
It really matters how you present your data. Have a good understanding of various cell formatting options, understand how to use Format painter & Paste special options for repetitive work.
Here is tutorial to help you understand better about Excel Formatting
#3 Sorting
Sorting plays an important role in excel, Sort doesn’t necessarily mean A-Z & Z-A, it has got a lot of options like Sort by Cell colour, Fill colour & Customizing sort as per the requirements.
Based on our experience, sorting data will reduce a chance of crashing workbook, you may refer our tutorial about Excel Sorting.
#4 Filter
It’s hard to imagine excel without filters, it has got a lot of options like filter by colour, Criteria, Top 10, Bottom 10 and many others. If your role requires a lot of filtering work then it’s a good idea to learn “Advance Filter”.
Here is a good video tutorial about Sorting & Filter
#5 Data validation
It plays a significant role in financial modelling. It’s relatively simple & straightforward. Learn how to apply data validation for input cells, you can also try to learn how to use the Indirect function to create an advanced listing.
You may refer basic & advanced guide to Data Validation here
#6 Data protection
Very often business reports will have complex formulas to calculate & interpret results. In order to safeguard formulas from editing, you’d need to lock formula cells with or without a password.
Sometimes, when you are dealing with sensitive data you’ll also need to protect your report from unauthorized access.
Tutorial from Dummies.com about Excel data protection
#7 Find & Replace
It works really great while working on data crunching. It becomes very handy with wildcards like * and ?. Try to understand how to use cell colour to find & replace contents.
Here is our recommended list of guide(s) on Find & Replace in excel
#8 Named range
Get rid of nasty cell address like A1:B1 or $A$1:$B$1, instead, define a name to your range & use it in your formulas. It will be very easy while debugging your formulas. You’d also need to emphasize more on creating dynamic names
Good tutorial on Excel named ranges & Dynamic names
#9 Formulas
MS Excel without formulas is just like a human without the skeleton. There is growing demand for people who can write excellent formulas to solve business problems.
Vlookup is the single most important formula, and sometimes people prefer to use Match & Index instead. It’s really good idea to learn as many formulas as you can.
Maybe start with Sumifs, Countifs, Sumproduct, Iferror, Len, Left, Right, Mid, Trim, Concatenate, Substitute ..etc.
In my opinion, formulas have 50% importance over all other excel skills, hence, I’d request you to pay little extra attention while learning.
Chandoo.org: excellent tutorial on various excel formulas
#10 Pivot Tables
According to Wikipedia, “a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software”.
In excel pivot table is a crucial tool for data summarization. You need to have a basic understanding on how to add/remove/refresh data in a pivot table, you should know how to use calculated fields, pivot charts & formatting etc.
You can refer Matthew MacDonald (author of Excel: The Missing Manual) tutorial on Pivot Tables
#11 Graphs/Charts
I think it’s true, one image/picture is equal to 1000 words. Charts play an important role in visualizing data in a way user can understand easily.
Every business one or the other way use charts to measure & compare their organizational performance. It’s always a good idea to understand, excel built-in charts thoroughly and you should be able to customize as per the requirement.
Read also: How to create Planned vs Actual chart in excel
Know how to use Trendline, Secondary access, Chart formatting and most importantly understand how to interpret results based on charts.
Wiki How tutorial on how to create and edit excel charts
#12 Basic Macros:
Very often discussion on Excel Macros is considered as an advanced excel. Although it may be true, you can have a basic understanding of how to run a macro, how to add a macro to Quick Access toolbar, how to record & how to save etc.
You can refer following tutorial on How to record a macro, and here is another in-depth tutorial on writing Macro in excel using VBA.
Bonus tips:
#13 Conditional formatting
As the name suggests, a user can format data using defined conditions. Often times it has been widely used excel skill in financial modelling to predict trends. Try to understand how to highlight duplicate, top 5 & bottom 5 values.
Give it try on how to use formulas to format data, understand how to use traffic lights. Have a hands-on experience in dealing with Data bars, Color Scales, Icon sets etc.
Here is a Microsoft tutorial on how to apply conditional formatting.
#14 Subtotal, Group & Freeze panes
I think its really good idea to understand how to use subtotal. Try to understand various tips & tricks around it.
Grouping & Ungrouping is also required while building reports. This option is mainly used to collapse & expand data.
Freeze Panes is a simple option used to freeze Row or Column headers.
You may refer tutorial on how to use subtotal & grouping & freeze panes
Conclusion
Suppose, if you have decided to make your career in the mid-level analytical job then Microsoft excel skills play an important role in your interview process.
Yet, if you aren’t ready..? I’d encourage you to download our info-graphics and use it as a checklist to learn all the excel skills.
After all please remember, speed matters a lot when it comes to analytical work. The most efficient way to accelerate your speed by learning some excellent keyboard shortcuts.
Like always I’d like to hear from you, let me know what do you think.