Excel is an extremely powerful program used by businesses all over the world. Users with advanced Excel skills are in huge demand.
If you are wondering “how to improve my Excel skills?”, you are in the right place. The list below is a collection of advanced Excel tips to enhance Excel skills and make you an Excel superhero.
Advanced Excel Formulas
If you asked someone, what are advanced Excel skills? You can be sure that advanced Excel formulas would be a common response.
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
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 Microsoft 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 have strong Excel skills 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
PivotTables
PivotTables are one of the most powerful and impressive features of Excel. Your PivotTable Excel skill levels are often tested in job interviews when Excel skills are in hot demand. You must master this incredible tool.
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.
Using the GETPIVOTDATA function
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
FIFA World cup history dashboard (Dashboard Example)
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 to Excel for analysis.
It is available from Excel 2016 and in Excel 365 by default, but is an add-in in 2013. This is one of the most important Excel skills for accountants. To get and transform a variety of sources of data into Excel is vital.
It is an incredible tool and one of the best Excel skills to know in modern day Excel. It 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
Leave a Reply