Become an Excel Guru - Excel Intermediate to Advanced
4.5 (78 ratings)
Instead of using a simple lifetime average, Udemy calculates a 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.
914 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Become an Excel Guru - Excel Intermediate to Advanced to your Wishlist.

Add to Wishlist

Become an Excel Guru - Excel Intermediate to Advanced

Learn Pivot Tables, Formulas, Charts, Data Analysis, Dashboards, Macros & More for Excel 2010, Excel 2013, Excel 2016
4.5 (78 ratings)
Instead of using a simple lifetime average, Udemy calculates a 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.
914 students enrolled
Created by Martyn Blythin
Last updated 7/2017
English
English
Current price: $10 Original price: $145 Discount: 93% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 5 hours on-demand video
  • 1 Article
  • 2 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn the most useful functions in Excel used in business
  • Learn to analyse large data sets with Pivot Tables
  • Learn to wow your boss with great looking management dashboards
  • Learn how to automate away boring repetitive tasks using VBA & Macros
  • Access to a Professional Trainer with 12+ years of Excel Training
  • Get your CV/Resume ready to impress on your next job application
View Curriculum
Requirements
  • This course was created with Excel 2013. However it will also work with Excel 2010, 2013 and 2016 for Windows
  • You'll need a basic working knowledge of Excel, including things like SUM formulas, entering data, adding sheets etc. If you're not yet at this level, check out my free 'Getting Started with Excel 2013' course.
Description

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. It's the most powerful tool in the Microsoft Office suite - its capabilities are incredible when you know how.

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 & functions - to more advanced tools like Excel pivot tables and Excel macros. 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 (but I have included a functions cheat sheet with them all on)!

You've probably been looking at 'Intermediate Excel' or 'Advanced Excel' 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 excel functions or excel charts, and then there are a few more advanced tools which can really set you apart from the rest, like summarising data using excel pivot tables, and using automation with Excel macros and VBA.

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 every single button does - you are going to learn the most useful - the 80/20 of Excel; and more importantly you will learn how to use it correctly. 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 Excel formulas and how to use some fundamental Excel tools like adding styles and dropdown options to cells.

Module 2: The second module is data analysis in Excel, and most of this module is a deep dive on Excel pivot tables - an easy to use drag and drop data summary tool. Excel Pivot tables are a powerful way 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 Excel dashboards. You don't have to be a data analysis professional to reap the benefits of this type of knowledge. Being able to present great data analysis in a succinct Excel 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, Excel VBA (sometimes called Excel Macros). It’s not as techy as you might think, and Excel VBA is 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:

  • Full, free lifetime access to course materials
  • All future extra lectures and upgrades are always included for free
  • Unconditional Udemy 30 day money-back guarantee
  • Regular free bonuses to improve your skills even more
  • Now includes 2 comprehensive cheat sheets for your desk or drawer covering all Excel shortcuts and Excel functions
Who is the target audience?
  • This course will take your Excel skills from beginner to advanced level
  • This Microsoft Excel courses will take you through 4 of Microsoft Excel topics (Data, Analysis, Presentation & Automation).
Students Who Viewed This Course Also Viewed
Curriculum For This Course
42 Lectures
05:19:05
+
Course Introduction
1 Lecture 03:26
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.
Preview 03:26
+
Managing & Enriching Data in Excel
17 Lectures 01:42:14
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.
Preview 03:38

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.
Preview 07:02

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.

Preview 06:19

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.

IF
05:42

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.

IFERROR
06:35

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.

ISERROR
05:12

In this lesson we will look at a range of other useful functions in Excel including:

Math formulas:

  • SUM
  • COUNT
  • AVERAGE
  • SUMIF
  • COUNTIF

Date formulas:

  • DAY
  • MONTH
  • YEAR
  • DATE
  • NOW
  • TODAY
  • NETWORKDAYS

Text formulas

  • LEFT
  • RIGHT
  • MID
  • FIND
More Formulas
17:30

In this lesson we will look at how you can learn any of Excels 400+ functions easily.

Learn any function
07:03

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.

Using Paste Special
07:35

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 validation
06:51

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.
Cell styles
04:39

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.

Formatting data as a table
07:25

Using named ranges
06:30

This is a short lesson on how to make navigating a large data sheet much easier by 'freezing' the top columns and rows.

Freeze Panes - navigating large data sheets
04:05

This lesson will cover the key file types you will come across in Excel, their uses and limitations.
File Types - how to save your work in the right format
06:08


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.

All Excel functions and formulas cheat sheet
12 pages
+
Data Analysis in Excel
5 Lectures 54:01

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.

Introduction to Analysis in Excel
01:30

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
Conditional formatting basics
12:48

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
An introduction to pivot tables
16:23

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.

Advanced pivot tables
17:48

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.

Pivot slicers
05:32
+
Presentation in Excel
7 Lectures 49:05
This lesson is an overview of the third module of the course - how to present your analysis in Excel.
Introduction to Presentation in Excel
02:27

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.

Basic Charts in Excel
08:37

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.
Pivot charts
07:15

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.

Password protecting the workbook
07:43

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:

  • How to use a simple template to create great looking reports over and over again
  • How to use pivot tables and pivot charts to create dashboards that can be refreshed at the click of a button
  • How and when to hide superfluous data
  • How to use table and chart styles to create a consistent look and feel across your work
  • How 'double click for details' will instil confidence and engagement in your reports.

Note: this lesson is split into two parts

Designing Dashboards (Part 1)
07:17

This is a continuation of Designing Dashboard (Part 1)

Designing Dashboard (Part 2)
14:38

This is a really quick lesson to show you how hiding the gridlines in Excel can make your work look a lot more professional.
Hiding gridlines for presentation
01:08
+
Automation in Excel
12 Lectures 01:33:20
This lesson is an overview of the final module - Automation in Excel.
Introduction to Automation in Excel
04:39

This lesson is an introduction to VBA and macros in Excel and how to navigate the VBA window .

VBA Introduction
05:45

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.

Using the Macro Recorder
10:30

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.

VBA Referencing
09:02

This lesson will show you how to use variables in VBA to remember and refer to text, date and numerical values throughout your macros
VBA Variables
07:46

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
VBA IFs
04:14

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.
VBA Loops
18:18

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.
VBA Error handling
08:47

In this lesson you will learn how to add buttons on your spreadsheet that you can use to trigger a macro you have written.
Creating buttons to run your macros
03:23

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.
A useful VBA tool you can use to improve your pivot tables.
05:00

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.
VBA Forms - creating interactive forms for your spreadsheet
15:37

Course Complete
00:19
About the Instructor
Martyn Blythin
4.4 Average rating
1,176 Reviews
12,449 Students
2 Courses
Your Excel Mentor - Over 12,000+ successful students

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!