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:
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.
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.
This is a short lesson on how to make navigating a large data sheet much easier by 'freezing' the top columns and rows.
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.
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.
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.
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.
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 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.
Hi, I'm Martyn. I created my courses to help you to get better at Excel and to get your next promotion, job or raise. My courses are a little different to most - you'll learn the most powerful functionality in Excel through in depth online courses, ongoing learning through case studies and exercises, as well as a fantastic online student community to share your experiences of breaking through the career ladder with better skills.
I am also the founder of Effective MI, a dashboard development & process improvement company. We help companies understand their business through data discovery, KPI development and powerful management reporting tools.
If you want to stop drifting and start learning Excel, you are in the right place. Welcome!