Guide to Understanding Beginner, Intermediate & Advanced Excel Skills?
I teach a number of data courses on Udemy, and over the years I’ve had a few students ask me about the differences between beginner, intermediate and advanced skill levels in Excel. Since my courses focus on marketing analytics and market research, and cater to a range of experience levels, I find that my students enroll at various skill levels in Excel. Some are beginners, most are intermediate, and a few tend to be experts.
This is also something that comes up in my day job, as I hire a lot of data professionals, and often find myself guiding them on how to level-up their Excel skills.
So, what exactly separates a beginner from an intermediate or advanced Excel user? There’s no universal standard, but when this question was posed on the r/Excel subreddit years ago, this was the top (and my favourite) answer.
I love this response because it’s both practical and funny. The expectations listed by numberjack (i.e. pivot tables/advanced formulas for intermediate, macros and automation for expert) pretty well align to my own expectations. But amusingly, numberjack’s reality (i.e. being able to print columns on the same page, etc) is also true!
As I mentioned, there’s no standard for demarcating Excel skills based on experience or competence level. But building upon numberjack response, I wanted to add some more insight into what I believe distinguishes Excel beginners from those who are intermediate to advanced.
Before we get into it, I want to preface this by stating that Excel is a program packed with functionality, and there are often many ways to solve a problem. From my experience, no one ever really knows all that functions and features that Excel has to offer, and people even tend to develop their own unique styles and approaches to getting things done. At the end of the day, you know what you know. And as long as you can get the task done quickly, accurately and error free, it doesn’t really matter what path you take.
With that said, let’s explore what some of the common skills sets and competencies might look like for different users of Excel.
Beginner users of Excel
1) Basic functions and formulas
Beginners will usually have knowledge of simple functions like SUM, AVERAGE, MIN, and MAX. They can also perform a range of arithmetic operations to create statistics and analyze data and trends.
2) Formatting
Beyond basic functions, beginners usually have a basic handle over cell formatting, as they’re able to comfortably adjust cell and text colours, align text, apply cell formats (e.g. changing values to numbers, currency, dates, etc).
3) Filtering, sorting and freezing panes
Beginners will often be able to apply basic filters to their data, as well as freeze columns, rows or panes to make traversing larger dataset easier.
4) Basic charts
Beginners in Excel can usually create basic charts, like pie, bar and line charts. However, I’ve noticed that beginners will struggle to understand how to build more complex charts, like scatter plots or radar charts.
5) Cell referencing
Beginners will typically understand the difference between absolute and relative cell references, and they know how to utilize these different approaches when replicating functions across rows or columns.
Intermediate users of Excel
1) Advanced functions and formulas
What often separates the intermediate users from beginners is a larger repertoire of functions they use. In particular, I find that intermediate users can use key functions like VLOOKUP, INDEX, MATCH, and IF/IFS to work more effectively. When you need to do a lot of data processing or transformation, mastering functions like the ones I’ve mentioned can save heaps of time.
2) PivotTables and PivotCharts
I originally had PivotTables in the beginners section, but if I’m being honest I’ve met a lot of people who could create a pivot table, but simply didn’t know how to use them effectively. And so, intermediate users don’t just know how to create PivotTables, they understand how to use them in ways that drive deeper analysis of your data. This can include being able filter, sort and group data in a pivot table, as well as changing how you view data via value field settings. Furthermore, intermediate user can build complex pivot tables with multiple column and filter variables. They also understand the limitations of pivot tables, and can restructure or transform their raw data in order to make it PivotTable ready.
3) Data validation
At a basic level, data validation may be viewed as a beginner skill. But being able to use data validation effectively can really streamline how you work, and help ensure the integrity of your data. Intermediate users will generally use data validation in conjunction with conditional formatting. Combining these two features effectively can reduce data input errors and help you spot data integrity issues.
4) Conditional Formatting
I already mentioned conditional formatting under data validation above. But it’s worth noting as it’s own skill, as not every use of conditional formatting relates to conditional formatting. Intermediate user can typically create and apply a wide range of conditional formatting rules, from cell highlighting and identifying duplicate values, to creating quick visualizations with heat maps and data bars.
5) Basic Macros
Macros can be a game changer if have a task that involves a lot of repetition, as a macro allows you to record a set of actions and run it like a script. Although macros can get a little complicated, I’ve found that intermediate users can sometimes create basic macros to help steamline how they work. Usually, entering the realm of macros requires knowledge of the programming language VBA. However, it is possible to create macros without writing code (e.g. using the ‘record macro’ feature.
Advanced users of Excel
1) Complex data analysis
Advanced users of Excel are usually comfortable with advanced analytical features and functions like What-If Analysis tools, Solver, and the Analysis ToolPak. The Analysis ToolPak also requires a basic foundation in statistics and running various stats tests like regression and t-tests. There can be a steep learning curve to some of these data analysis features in Excel, because they require both a technical and theoretical knowledge to use.
2) Advanced macros and VBA
As a I mentioned earlier, an intermediate user of Excel may be able to do some basic automation with macros. However, advanced users are often automation gurus, and they can write complex VBA scripts to automate a wide range of tasks. Similar to complex data analysis, you may encounter steep learning curves when attempting to advance your macro skills because you’ll need to learn the basics of VBA before you can start applying it to macros in Excel.
3) Power Query
Power Query is a powerful data automation feature in Excel that allows you to import data from external data sources, such as a website or database. But what makes this feature stand out is the Power Query editor, which allows you to completely restructure and transform your data to your needs. As someone who works in market research, there’s one feature in particular that’s a life saver in Power Query, which is the ability to unpivot columns (i.e. converting a table from a wide to long format). When dealing with survey data in Excel, the structure of a multi-select question (where respondents can select multiple options from a list) creates a data structure that isn’t conducive to PivotTables. But using Power Query, you can unpivot a table in seconds which allows you to import your data into a PivotTable for analysis.
4) Power Pivot and data models
Power Pivot allows you to perform powerful data analysis and create data models in Excel. With Power Pivot, you can work with large datasets, create relationships between different data tables, and use DAX (Data Analysis Expressions) to create calculated columns and measures. This may sound complicated, but I found that learning Power Pivot and data models to be easier than learning other things like VBA. And if your job requires manage large and complex datasets or databases, learning how to use Power Pivot is a must.
5) Dynamic arrays
When you think about writing a function, you probably assume that you need one function per cell. So if you want to repeat a function across cells, you have to copy and paste again and again. Dynamic arrays solve this problem by allowing you to write a formula once that returns multiple values and which automatically spill over into neighboring cells. Dynamic arrays are enabled through a number of special functions, like UNIQUE, FILTER and SORT. There are also XLOOKUP and XMATCH which replace the traditional versions of VLOOKUP and MATCH.
Conclusion
As users progress from beginner to advanced, they not only expand their toolkit of functions and features but also develop a deeper understanding of data analysis principles and the ability to think critically about data. Advanced users are often distinguished by their ability to automate tasks, create complex models, and integrate Excel with other data analysis tools and platforms.
But like I mentioned at the beginning of this article, mastering Excel isn’t about learning every single feature. Many Excel users develop their own habits and styles for solving problems. What’s important is that you’re always open ot learning new things, and always asking yourself, ‘is this the fastest or most efficient way to solve this task?’
Do you agree with the skills I’ve listed in this article? Or do you have any thing to add? Let me know in the comments below.
