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.
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.