Advanced Excel Skills

Now a days, many jobs require advanced Excel skills. Naturally, you are interested to know what are these Advanced Excel Skills? Based on my experience of training more than 10,000 students in various online & physical training programs, the following 9 areas form the core of advanced Excel skills.

Data Prep and Cleaning
Advanced Formulas & Functions
Conditional Formatting
Pivot Tables & Power Pivot
Advanced Charting
VBA + Automation
Modeling
Using Excel Productively
Beyond Excel

9 Advanced Excel Skills you need in 2024 - Mindmap

The core advanced skills in Excel you need to succeed in 2024 are summarized in this mindmap.

Click on it for a high-res version.

Data, Power Query, Tables & Formatting

Advanced Excel users know how to gather, structure & present their data so that it looks impressive. Good understanding of Excel features like Power Query (Get & Transform Data), Tables, cell styles, formatting options is necessary to make awesome Excel workbooks.

Learn Data, Power Quey, Tables & Formatting

Advanced users rely on Power Query to gather and change shape of your data easily!!!

Advanced Formulas & Functions

Formulas make Excel smart. With out them, Excel is just a data keeping tool. But by using formulas, you can crunch data, analyze it and get answers to most complex questions. While anyone can use a simple SUM or IF formula, an advanced user of it would be able to seamlessly write & combine formulas like SUMIFS, FILTER, XLOOKUP, IFS and other functions.

Apart from knowing the formulas, advanced Excel users know how to debug them, audit them and how to use which formula for which occasion (and they also know few alternatives for any given formula problem).

Advanced Excel Formulas

Conditional Formatting

If there is one Excel feature that can turn a boring spreadsheet into an awesome productive tool, it has to be conditional formatting.

By using conditional formatting, you can tell Excel to highlight portions of your data that meet any given condition. For example,

Highlighting top 10 customers
below average performing employees
All upcoming events in next 90 days

While anyone can set up simple conditional formatting rules, an advanced Excel user can do a lot more. They can combine formulas with conditional formatting to highlight data that meets almost any condition.

Learn Conditional Formatting

Expert Excel users can do magic with conditional formatting and a cup of coffee 💪

Pivot Tables & Power Pivot

Pivot tables & pivot reporting allows us to analyze massive amounts of data & answer questions with just a few clicks. Advanced users of Excel can work seamlessly with data model, table relationships, write DAX measures, use slicers and create dynamic pivot charts & reports.

Link tables with relationships and weave magic with multi-table pivots. 🔗💎

Pivot Tables + Power Pivot

Advanced & Combination Charts

There is no use if all your analysis is buried in a massive spreadsheet. Advanced users of Excel know that by using charts, we can communicate effectively and present results in a stunning manner. The skills required for advanced charting are,

Knowing how to pick right type of chart for any situation
Ability to combine various charts into one (aka combo charts)
Make and format statistical charts like histograms, box plots, distribution curves with ease.
Use features like in-cell charts & conditional formatting charts
Ability to set up dynamic & interactive charts
Use sparklines

Make Advanced Charts

If a chart is worth 1,000 words, then advanced chart is like a book. Dense, fun and useful. 📊⭐

VBA, Macros, Office Scripts & Automation

Many tasks in Excel tend to be repetitive. This is where advanced Excel users know how to automate so that they can save time and look like a BOSS.

Excel offers many automation capabilities like:

Power Query for data automation
VBA for application-level automation and coding
Office scripts for desktop + Excel online automation
Power Automate (Flows) to automate Excel with other office apps like Outlook, SharePoint or OneDrive.

Learn Excel VBA, Macros & automation

Expert Excellers can save time & money by automating tasks with VBA. ⏳💰💻

Financial Modeling with Excel

The main use of Excel is to solve business problems. So you need to know how to model a business or financial situation with Excel and how to simulate real-world scenarios.

That is why advanced Excel users with modelling, business domain and statisical understanding get paid big bucks.

In order to build such models, you need below advanced Excel skills.

How to build the classic 3 statement financial model in Excel (Cash flow, Balance sheet and P&L)
Understanding of financial and business KPIs and how to calculate them with Excel formulas
How to use Solver to find optimum solutions
Doing what-if analysis with data tables in Excel
Ability to build forecasting models using features like LINEST, multiple regression, forecasting and trend lines.
Know how and when to use statistical functions of Excel.

Learn business modeling and problem-solving skills

Superb Excel users can set up solver models to solve tricky business problems. 🛠😎

Using Excel Productively

It is not enough to know various features of Excel. An advanced user knows how to use Excel productively. This includes knowing important keyboard shortcuts, mouse shortcuts, workarounds, Excel customizations & how to make everything looks slick.

Excel Productivity Playlist

5 Videos