Become an Excel Guru
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
Find online courses made by experts from around the world.
Take your courses with you and learn anywhere, anytime.
Learn and practice real-world skills and achieve your goals.
Welcome to Become an Excel Guru, where you are going to learn everything you need to know to become an Excel expert. For the last ten years I've been using Excel to build advanced tools and applications in the investment banking and financial services industries.
So, you probably already know the basics of Excel and you use it in your job on a daily basis, and you're looking to learn more about Excel - but how much more?
Excel has a huge variety of tools and functionality, from the simple - like sum and average formulas - to more advanced tools like pivot tables and automation. But the scope of stuff to learn if you want to know EVERYTHING is enormous - for example, there are over 400 different functions you can use. Do you want to learn them all? Didn't think so!
You've probably been looking at 'intermediate' or 'advanced' level courses - but who decides what constitutes those skill levels? Is it just a case of knowing what all the buttons do or is there something deeper?
From over a decade of using, developing and training Excel I've learned that actually, you don't need to know it all to be an expert.
There is a collection of different tools that I believe are most important for day to day business use, like functions or charts, and then there are a few more advanced tools which can really set you apart from the rest, like summarising data using pivot tables, and using visual basic, Excel's automation toolkit.
My goal in this course is to teach you Excel to an advanced level but without confusing you and overwhelming you with information. I'm not just going to teach you what the buttons do - you are going to learn that of course, but more importantly you will learn how to use it correctly, and I'll make it easy for you to do it over and over again.
I've split the course into four key modules, which follow a logical progression from raw data to end results:
Module 1: The first module covers working with data in Excel, where you'll learn all about how to structure your data, clean it and enrich it using formulas and how to use some fundamental Excel tools like adding styles and dropdown options to cells.
Module 2: The second module is about analysing your data, and most of this module is a deep dive on pivot tables - Excel's easy to use drag and drop data summary tool. We'll cover all kinds of ways to slice and dice your data and really uncover the key trends and insights.
Module 3: This module covers presenting your analysis, with the key focus being on learning how to create great looking management reports and dashboards. You don't have to be a data analyst to reap the benefits of this type of knowledge. Being able to present great analysis in a succinct and informative dashboard is a skill that can really take your career to another level.
Module 4: Finally we'll cover automation in Excel using Microsoft's coding language, VBA. It’s not as techy as you might think, and it's one of the easiest coding languages you can possibly learn. This module will set you on the road to being able to automate away repetitive and boring tasks you do in Excel, and can even help you create totally customized buttons and tools in your spreadsheets for your job or your clients.
I think you'll get a huge amount of value from this course and you will come away with a deep understanding of how to use Excel correctly and with ease. No more throwing the mouse across the room or banging your head against the wall - this will take you to the next level.
About this course:
Not for you? No problem.
30 day money back guarantee.
Learn on the go.
Desktop, iOS and Android.
Certificate of completion.
|Section 1: Course Introduction|
|This is an introduction to the course and includes an overview of the DAPA framework that you will learn over the 4 modules. Remember to check the downloads section in each lesson as workbooks are included throughout the course that you can download to see the examples I have used.|
|Section 2: Managing & Enriching Data in Excel|
|This lesson is an overview of module 1 - managing and enriching data in Excel, as well as some key basic functionality such as protecting your work, and different ways to copy and paste in Excel.|
|This is a short lesson where you will learn 5 key best practice principles to help improve your Excel usage. I've developed these over many years of Excel usage, and starting out with these is a key foundation to the course.|
VLOOKUP is one of the most useful functions in Excel - it allows you to pull data from one table into another where there's common data between tables. Once you truly understand VLOOKUPs you'll already be well on your way to becoming an Excel guru.
IF functions allow you to perform 'if this then do A, otherwise do B' type tasks in Excel. This has so many uses including finding numbers above or below a certain value or finding which cells are equal to a specific word or phrase.
IFERROR is an extension to the useful IF function, and allows you to tell Excel what to display if a formula returns an error. We'll examine how to use this with VLOOKUPS to create better looking formulas.
ISERROR is similar but slightly different to an IFERROR function, allowing you to determine if a cell or a function contains an error. We'll look at how to use this to show if a range of values exist in another table.
In this lesson we will look at a range of other useful functions in Excel including:
In this lesson we will look at how you can learn any of Excels 400+ functions easily.
There are a number of ways to copy and paste data in Excel. We'll look at how and when to use the most useful and common methods.
Cell or Data Validation in Excel is a useful tool to use to control how people can enter data into your spreadsheet. You can use it to create drop down lists, or for example force the user to enter a date between two dates. In this lesson we'll look at how it works, how you can easily set it up, and how to do some more in depth customization such as setting a custom error message for when an incorrect value is entered.
|Cell styles are an easy way to make your Excel files look great by using pre-set colours, fonts etc. In this lesson we'll look at how to apply them and how to create your own cell styles to match your brand or company.|
In this lesson we'll look at how to use Excel's table formatting to enable easy data entry & analysis, as well as making your data look great.
Using named ranges
This is a short lesson on how to make navigating a large data sheet much easier by 'freezing' the top columns and rows.
|This lesson will cover the key file types you will come across in Excel, their uses and limitations.|
All Excel keyboard shortcuts cheat sheet
|Lecture 18||12 pages|
This is a comprehensive list of all the functions available in Excel - I'm sure you won't need them all, but it is an extremely useful reference guide to have to hand at your desk when you're trying out new functionality in Excel.
|Section 3: Data Analysis in Excel|
Welcome to the second module of the course - analysing data in Excel. This is a short introduction to what will be covered in this module.
|This lesson covers conditional formatting, which will allow you to for example: Create a color scale for a set of numbers from low to high Add a red/amber/green status that will automatically update Highlight numbers above or below a value Add icons to cells Add mini charts within cells|
|Pivot Tables are an absolutely fantastic capability in Excel that allow you to analyse data quickly and easily. In this lesson we're going to cover: What are pivot tables How to set them up How to summarise 30,000 rows of data in seconds|
In this lesson we'll take a deep dive on pivot tables and look at how to do more complex summaries such as percentage summaries and running totals.
Pivot slicers allow you to add interactivtiy into your pivot tables, enabling users to filter the pivot table by date ranges or categories. In this lesson we'll look at how to create and use them effectively.
|Section 4: Presentation in Excel|
|This lesson is an overview of the third module of the course - how to present your analysis in Excel.|
Charts are a powerful functionality in Excel, this lesson will teach you how to create some basic charts in Excel. In the next lesson we'll look at pivot charts, which are a much more powerful, easy and fast way of creating charts.
|Pivot charts are charts created from pivot tables. They are a great way to visualise your data quickly, easily and reliably. In this lesson we will explore how to create them and some useful formatting tools you can use to make them look awesome.|
At times it can be useful to protect either elements of your spreadsheets or the entire Excel files themselves. This lesson will take you through all the options available and what they do.
Dashboards are concise management reports that visually display business data in tables and charts. There are limitless ways to create a dashboard in Excel but there are a few best practice principles you can follow to get the best results. This lesson will bring together many things you have learned in the course so far to allow you to create great looking reports.
In this lesson we'll look at:
Note: this lesson is split into two parts
This is a continuation of Designing Dashboard (Part 1)
|This is a really quick lesson to show you how hiding the gridlines in Excel can make your work look a lot more professional.|
|Section 5: Automation in Excel|
|This lesson is an overview of the final module - Automation in Excel.|
This lesson is an introduction to VBA and macros in Excel and how to navigate the VBA window .
This lesson will show you how to use the macro recorder in Excel to record simple tasks and how it is an extremely useful tool for learning VBA.
This lesson will show you how to use VBA to refer to different types of objects in your spreadsheet - such as cells, ranges, rows, columns and sheets.
|This lesson will show you how to use variables in VBA to remember and refer to text, date and numerical values throughout your macros|
|This lesson will show you how to use IF statements in VBA. If statements are very useful tools to add 'if this then do A, otherwise do B' functionality into your macros|
|This lesson will show you how to use Loops in VBA. Loops allow you to repeat actions over and over on different elements of the workbook, allowing you to process large amounts of data very quickly.|
|This lesson will show you how to create an error handling routine in your VBA that will tell Excel what to do if it comes across an error.|
|In this lesson you will learn how to add buttons on your spreadsheet that you can use to trigger a macro you have written.|
|In the Pivot Tables lessons you learned how pivot tables have fantastic functionality to 'double click to drill down' to the raw data. In this lesson I'll show you how to make that data always pop up in a new Excel file to prevent cluttering your spreadsheet - all using some simple VBA.|
|Forms are a great 'advanced' functionality in VBA which allow you to create forms completely separate to the Excel window. They're great for data entry and controlling how users interact with the spreadsheet.|
I'm an Excel developer and trainer with over 10 years using Excel for data analytics and business process improvement in the UK. I also run a consultancy business specialising in custom Excel apps and data analytics. Over the years, I've been asked to run so many training sessions on Excel that I finally decided to put my content online. My focus is on helping people get the most out of Excel with the minimum effort, pain, and the least boredom.
Let's face it, using Excel isn't always the highlight of everyone's day. For many it's a constant pain because they have to go to ask the office guru every time they need to do a VLookup, or a Pivot Table. Perhaps they don't even know what a Pivot Table is! My courses are all about helping you to get the most out of Excel, so that you can be faster and more effective with your spreadsheets, without boring you with technical manuals or long form lessons.
Having worked in a wide range of global financial institutions and consultancies during my career, I am constantly amazed at how much productivity large organisations miss out on by not having their staff fluent in Excel. There is so much opportunity to improve efficiency,automate tasks, get better data insights, reduce errors, and create happier staff, simply by improving the level of competence in Excel across organisations. I have seen countless Excel classes that focus just on what the various buttons in Excel do and how to use them. I never felt this was enough. Excel users need to know WHEN and WHY they should use these tools, not just HOW. There's also huge scope for to coach Excel users away from common pitfalls and bad practice. I created these courses to provide more comprehensive, 360° training for Excel users of all levels from beginners to seasoned experts.