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

The amazing SUMPRODUCT function

Calculate time elapsed in days, hours and minutes

5 awesome date functions

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.

Excel Gantt Chart template for tracking project tasks

Create a battery chart in Excel

Create a histogram

Highlight Max and Min Values on a Column Chart

Learn more advanced charting techniques

PivotTables

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

Ranking fields in a PivotTable

Using the Timeline Slicer in Excel 2013

Learn more PivotTable skills

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

Macros & VBA

You can record macros in Excel to speed up and simplify repetitive tasks. A sequence of Excel actions can be recorded and then run from just one click of a button.

For more complicated procedures you can write macro's using VBA. By learning Excel VBA you are able to automate powerful and complex processes and even extend the functionality of Excel.

Record a macro in Excel

Loop through files in a folder using VBA

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

Spell check a protected worksheet

Macro to remove blank rows on a sheet

Sign up for the online Excel VBA course

View More Free Tips

Learn more advanced Excel skills at our blog

Follow us on

Facebook  Twitter  You Tube 

Excel VBA Course