Advanced Excel Skills

Excel is an extremely powerful program used by businesses all over the world. Users with advanced Excel skills are in demand.

The list below is a collection of advanced Excel tips to make you an Excel superhero.

Advanced Excel Formulas

Formulas are what drive an Excel spreadsheet. Formulas provide the muscle for a spreadsheet to analyse, manipulate, convert and look up data with incredible speed and power.

An advanced Excel user would need advanced formula skills. They would have a commonly used list of functions that they know well, but also the skills and confidence to adapt to any situation requiring formulas.

Ultimate VLOOKUP function guide

5 examples of the groovy SUMPRODUCT function

The INDIRECT function - 5 great examples

4 alternatives to nested IF formulas

List of Excel functions

Learn more advanced Excel formulas

Validating Data Entry

You cannot harness the power of Excel and use features such as filters, PivotTables and formulas if your data is not accurate. Even an Excel superhero will produce wrong results if the data being used is incorrect.

It is therefore a useful skill to do what you can to aid data entry, and then validate the data to prevent inaccurate data getting onto your spreadsheets.

This may not be the most glamorous of the advanced Excel skills to learn, but it will improve the quality of your spreadsheets, saving time and errors down the line.

Create dependent drop down lists

Validate entry of email addresses

Prevent duplicate values in Excel

Data Manipulation

As an Excel user you will often receive spreadsheets from clients, import data from a database, or download it from a website. These spreadsheets can be poorly structured or contain data in the wrong format.

Excel contains many formulas and features that allow us to work with this data and clean it ready for us to work with.

Separating text into different columns

Convert text into a date format

The awesome Flash Fill feature in Excel 2013 (video)

Conditional Formatting

Conditional Formatting is one of the most popular features of Excel. It will automatically format a cell (such as change its colour) dependent upon a condition like a deadline expiring, a target being reached or to identify duplicate values.

Most spreadsheets have a need for Conditional Formatting rules to present the data in a clear and easy to read format allowing us to see the data that is important to us.

An Excel expert will be familiar with this tool and know how to apply formulas to Conditional Formatting rules to get the most out of the feature.

Essential Conditional Formatting tricks

Highlight dates in a list over 30 days old

Format the Saturdays and Sundays in a list

Compare two lists using VLOOKUP and Conditional Formatting

Advanced Charting Techniques

When used correctly, charts are a very powerful reporting tool. They enable you to communicate a table of values in an effective manner.

An advanced Excel user should understand the core chart types and when to use them. These advanced Excel skills will have your friends and colleagues envious of your charting abilities.

Doughnut chart to measure progress toward a goal

Create a rolling chart for the last 6 months

Conditional Formatting with charts - two examples

Highlight Max and Min Values on a Column Chart

Learn more advanced charting techniques


PivotTables are one of the most powerful and impressive features of Excel. You must master this incredible tool to be an Excel superhero.

PivotTables will summarise huge amounts of data in just a few clicks. This makes it easy to drill into the details from an otherwise complicated list of data.

Introduction to PivotTables

Using the GETPIVOTDATA function

5 advanced PivotTable tricks

Learn more PivotTable skills at the blog

Excel Dashboards

An Excel dashboard is a spreadsheet that summarises the most important information neatly on one screen.

An Excel dashboard is normally made up of charts, Conditional Formatting techniques, formulas, PivotTables and interactive elements like lists and buttons.

The key to Excel dashboards is the ability to fit all the most important information into a small space, whilst keeping it visually appealing, easy to use and the information presented effectively.

It is a unique skill to design and develop effective Excel dashboards. There are some essential Excel skills to know and hours of practice.

Create a scrollable chart for Excel dashboards

Interactive chart using combo boxes

FIFA World cup history dashboard (Dashboard Example)

Excel VBA Course

Excel VBA and Macros

Excel VBA enables to to create added functionality to your spreadsheets. It could be something simple to simplify and speed up a regular Excel process.

It could also be used to create a powerful Excel application or more complicated Excel process that requires capturing information from the user and accessing files on a directory..

All of this makes Excel VBA a very important skill to master. If you can create your own customised solutions, nothing can stop you.

Loop through files in a folder using VBA

Format all instances of a word in a range of cells using VBA

Display search results in a UserForm ListBox.

6 ways to speed up your macros

Create a progress bar for your macros

Sign up for the online Excel VBA course to learn Excel VBA easy

Power Query

Power Query (or Get & Transform) is a feature of Excel that enables you to connect to external data sources, clean it and then load it into a worksheet for analysis.

It is available in Excel 2016 and Office 365 by default, but is an add-in in 2010 and 2013.

It is an incredible tool that has revolutionised the way that we can work with data outside of Excel. The entire process of importing, transforming and loading it can be reduced to the click of a button.

Merge Queries - No more VLOOKUP?

Unpivot data in Excel with Power Query

Combine multiple worksheets with the Append Query

View More Free Tips

Learn more advanced Excel skills at our blog

Follow us on

Excel VBA Course