Supercharge Your Excel Skills
4.4 (10 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
687 students enrolled

Supercharge Your Excel Skills

Learn functions, analytics, charts, macros & more
4.4 (10 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
687 students enrolled
Created by Martyn Blythin
Last updated 4/2018
English
English [Auto-generated]
Current price: $11.99 Original price: $34.99 Discount: 66% off
1 day left at this price!
30-Day Money-Back Guarantee
This course includes
  • 6.5 hours on-demand video
  • 47 articles
  • 83 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to Udemy's top 3,000+ courses anytime, anywhere.

Try Udemy for Business
What you'll 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 15+ years of Excel Training
  • Get your CV/Resume ready to impress on your next job application
Requirements
  • This course was created with Excel 2016. However it will also work with Excel 2010, 2013 and 2016 for Windows.
  • You'll need a basic working knowledge of using a computer. If you've never used Excel before, don't worry, we start with the basics.
Description

Just imagine what it would feel like if using Excel was effortless - if you could create charts, functions, and analyse data with ease. Becoming great at Excel isn't about learning what every button and every formula does - it's about learning methods you can use over and over again to effortlessly clean, enrich and analyse data. 

I bet your a bit like me, you've tried learning through trial and error, but it takes a long time and involves a lot of mistakes along the way! I've been using Excel for over 15 years and there's still a few functions I've never used. The good news is that you can become great at Excel in just a few hours by learning the right tools and approaches to use. How would it feel to have those skills on your resume and at your next job interview?

So, you want to learn Excel but you don't want to overwhelm yourself with stuff you're never going to use and you need the skills for your job, or the next job you hope to get. As I see it, you have three options. First, you could take a classroom based course and try to learn it all in a day, but from experience I know many people who have just been overwhelmed with the amount of information and the pace of learning. Second, you could do absolutely nothing, stay exactly where you are right now with your Excel skills. Or third, you could give this a try, work it alongside your current job and be genuinely good at Excel within a week or two.

I designed this course using feedback received from over 12,000 students over 3 years who told me they wanted more opportunities to test their Excel skills. This course has it all, including:

  • Downloadable challenges to test your knowledge.
  • Multiple choice quizzes at the end of each module.
  • Each lesson has a downloadable Excel file containing the examples used.
  • Cheat sheets for functions & keyboard shortcuts that you can have at your desk for reference.
  • Ask me any Excel question on the course message boards.
  • A weekly update '3 Tip Friday' email containing useful Excel tips and tricks.
  • A certificate of completion at the end of the course.

The course covers all levels, from beginner to advanced, so you don't have to worry about if the course is right for you, and you don't have to buy multiple courses for each level of your Excel knowledge! The course is split into 6 core modules:

  • Module 1: Master The Basics
  • Module 2: Your Data Gameplan
  • Module 3: Your Functions Toolkit
  • Module 4: Your Data Analysis Strategy
  • Module 5: Your Dashboard Playbook
  • Module 6: Your Excel Automation Machine

The course also comes with everything you'd expect from the Udemy platform, including:

  • Lifetime access to the material
  • Unlimited free updates to the course

If you're prepared to give this a try, then I'm certain you'll see the results as early as the first day you try it.

Who this course is for:
  • This course will take your Excel skills from beginner to advanced level.
  • This Microsoft Excel courses will take you through 6 modules covering all the areas of Excel you need to know to become an expert.
Course content
Expand all 102 lectures 07:07:38
+ Start Here
2 lectures 02:25
Course Roadmap
01:29
One Rule - Keep It Simple
00:55
+ 1. Master The Basics
13 lectures 27:33
Master The Basics
00:35

Difficulty: Easy

Overview: There are a multitude of ways to format data in Excel, including fonts, colours, borders, number formatting (date, currency, numerical, text etc). In this lesson you'll learn:

  1. How to use font formatting
  2. How to use cell alignment tools including merge & wrap text
  3. How to add multiple rows of text within a cell
  4. Data formats (date, currency, numerical, text etc)
  5. Setting cell and table borders
Preview 09:03

Difficulty: Easy

Overview: Text styles are a useful way to apply consistent formatting to different elements of your work. Similar to header styles in word, they can be used to create great looking and well organised spreadsheets with less effort. You can also create your own custom styles.

In this lesson you'll learn:

  1. How to use default text styles
  2. How to create your own text styles
Preview 02:25
Challenge: Create a custom text style
00:12
Some Quick Definitions
01:30

Difficulty: Easy

Overview: It can often be tricky to get your Excel files to print just how you like. In this lesson you'll learn:

  1. How to use Page Area and fixed widths and heights to make your work fit on the page
  2. How to repeat a row at the top of each page
  3. Setting margins
  4. Setting headers and footers
  5. Setting the page size
  6. Print preview
Preparing Your Work for Printing
04:39
Challenge - Prepare a file for printing
00:13
Saving Your Work Correctly
00:54

Difficulty: Easy

Overview: When copying and pasting in Excel, you can choose if to paste all or just some elements of the cell. In this lesson you'll learn:

  1. Regular copy and paste
  2. Paste special as values and as formulas
  3. Paste special column widths
  4. Paste special transpose
  5. Paste special formats
Paste & Paste Special
03:16
Challenge: Using paste special
00:09

Difficulty: Easy

Overview: In this lesson you'll learn how to insert shapes, lines, text boxes and smart art.

Inserting shapes, text boxes and smart art
03:53
Keyboard Shortcuts Cheatsheet
00:10
Quiz: Have you mastered the basics?
6 questions
How are you finding the course so far?
00:32
+ 2. Your Data Game Plan
14 lectures 42:48
Your Data Game Plan
00:51
The 10 immutable laws of structuring data
05:01

Difficulty: Easy

Overview: In this lesson you'll learn how to sort data correctly in Excel, and how to sort by multiple criteria at once.

How to Sort Your Data Correctly
02:46

Difficulty: Easy

Overview: Autofilter is a powerful tool that allows you to slice and dice your data using criteria. In this lesson you'll learn how to use it, and how to harness its more advanced functionality, such as filtering for 'This Month', and searches using wildcard characters.

Using Autofilter to Effectively Search & Filter Your Data
05:45

Difficulty: Easy

Overview: There are 3 ways you can structure your data tables - as a range, as a formatted table or as a named range. In this lesson you'll learn the differences between these and when to use each.

Tables & Named Ranges
06:21
Challenge: Convert a range to a table
00:07

Difficulty: Intermediate

Overview: Data Validation allows you to add restrictions on what can be entered into cells. This can be particularly useful when creating data entry spreadsheets for others to use. In this lesson you'll learn:

  1. How to create a drop down list within a cell.
  2. How to restrict the type of data that can be entered into a cell (date, time, numbers, text etc) and use restrict the range of data that can be entered.
  3. How to create a dynamic drop down list that expands or contracts depending on what is entered in a list.



Creating Drop Down Selections & Restricting Data Entry Using Data Validation
05:14
Challenge - Create a drop down selection for data entry
00:10

Difficulty: Intermediate

Overview: Conditional Formatting allows cells to be automatically coloured depending on the value entered into them. It has a wide range of uses, including creating a red-amber-green status for reports and dashboards. It can be also be used to create mini bar charts and icons that respond to the value in cell. In this lesson you'll learn:

  1. How to highlight top/bottom values in a range
  2. How to create a temperature style colouring for your data
  3. How to create a red-amber-green status for a report metric
How To Colour Cells Depending on Their Contents Using Conditional Formatting
06:55
Challenge - Add conditional formatting for project list
00:08

Difficulty: Advanced

Overview: Excel is often used to analyse data from other sources, such as databases or raw data files. If you are using the same data source over and over again, it can often be linked in Excel, allowing the latest data to be refreshed at the click of a button. In this lesson you'll learn:

  1. How to link Excel to a static data file
  2. How to link Excel to an Access database

How to Import External Data From Databases
03:24

Difficulty: Intermediate

Overview: Excel can be used to 'scrape' web data, with mixed success. If you regularly copy and paste data from a website, for example stock prices or currency rates, it can sometimes be automated by linking the page in Excel. In this lesson you'll learn how to give this a try. Note that it cannot be used to log you in to a website, or to get data from anything other than basic web pages. Some sites block this functionality.

How to Download Data Tables From Websites
02:56
Challenge - Download the latest stock prices
00:06

Difficulty Level: Advanced

Overview: Sometimes importing data into Excel can be troublesome. In this lesson we'll look at how to import a text file that Excel does not recognise using 'Text to Columns'.

What to do When Excel Can't Read Your Data - Text To Columns
03:03
Quiz: Can you work with data in Excel
6 questions
+ 3. Your Functions Toolkit
21 lectures 01:08:01
Your Functions Toolkit
00:50

Difficulty: Easy

Overview: SUM, AVERAGE, MIN and MAX are the easies to use functions in Excel. In this lesson you'll learn how to use them, and familiarise yourself with how functions work.

SUM, AVERAGE, MAX, MIN
02:36
Challenge: Find COUNT, SUM and AVERAGE Sales Value
00:10

Difficulty: Easy

Overview: When writing functions, cell references can be written in two different ways; relative or absolute. In this lesson you'll learn the differences between the two and when to use them.

Absolute vs Relative cell references
05:21

Difficulty: Intermediate

Overview: In addition to the basic SUM, COUNT and AVERAGE functions, there are conditional versions of each. These allow you to sum, count or average items in a range which meet a certain criteria which can be set as numerical or text. In this lesson you'll learn:

  1. COUNTIF & COUNTIFS
  2. SUMIF & SUMIFS 
  3. AVERAGEIF & AVERAGEIFS
SUMIF, COUNTIF, AVERAGEIF
07:17
Challenge: Finding duplicate rows using COUNTIF
00:12

Difficulty: Intermediate

Overview: The way in which functions are written differs depending on if they reference a range, a named range or a formatted table. In this lesson we look at the different ways to reference these tables correctly.

Referencing Tables & Named Ranges Correctly
03:35

Difficulty: Intermediate

Overview: Any element of a function can be replaced with another function, this is known as nesting functions. In this lesson you'll learn how to correctly create and edit nested functions.

Nesting Functions
03:11

Difficulty: Intermediate

Overview: VLOOKUP is one of the most useful functions in Excel. It allows you to cross reference one table to another, where the tables have a field in common. In this lesson you'll learn:

  1. How to create a VLOOKUP
  2. The difference between exact and approximate match
  3. How to create an HLOOKUP for a horizontal table lookup
VLOOKUP & HLOOKUP
07:48
Challenge: Add data from another table using VLOOKUP
00:09

Difficulty: Intermediate

Overview: INDEX and MATCH are two functions which can be used to create a more powerful type of VLOOKUP, one that looks both left and right in the lookup table (VLOOKUP only looks to the right). In this lesson you'll learn:

  1. How to use INDEX & MATCH instead of VLOOKUP
  2. The pros and cons of using INDEX & MATCH vs VLOOKUP
INDEX & MATCH
04:43
Challenge: Perform a lookup using INDEX & MATCH
00:07

Difficulty: Intermediate

Overview: The IF function allows you to create a true and false outcome based on a set of criteria. For example, you could use an IF function to say TRUE if a cell is above a certain value, and FALSE if not. The types of criteria used can be both mathematical and text based. In this lesson you'll learn how to use the function and why it's one of the most versatile functions in Excel.

Create Conditional Formulas with IF
04:16

Difficulty: Intermediate

Overview: Sometimes functions produce errors; this doesn't mean they've been written incorrectly, it just means that Excel wasn't able to resolve that particular instance of the function. For example a VLOOKUP returned an error as the lookup value doesn't exist in the lookup range.

There are several functions will allow you to choose how Excel handles function errors, and they can be used to enhance a formula to create custom error messages. In this lesson you'll learn:

  1. How to create an IFERROR function to create a custom error message for a VLOOKUP
  2. How to use ISERROR to determine if a function is an error or not
  3. How to combine IF and ISERROR to create a vlookup which states whether or not a value exists in another range.
IFERROR & ISERROR
06:50
Challenge: Create a VLOOKUP with a custom error message
00:11

Difficulty: Easy

Overview: There are a number of functions that can be used to extract parts of text in cells. In this lesson you will learn how to use the LEFT, RIGHT and MID functions to return text from either end, or from the middle of a cell.

LEFT, RIGHT, MID & LEN
06:17

Difficulty: Easy

Overview: FIND & SEARCH allow you to determine whether or not a letter or a word appears in a cells, and if it does, returns where in the cell the word starts. In this lesson you'll learn how to use these functions and the difference between them.

FIND, SEARCH & SUBSTITUTE
05:23

Difficulty: Easy

Overview: In this lesson you'll learn how to use TODAY and NOW. These are two easy to use functions and keyboard shortcuts to insert the current date or date and time into a cell.

TODAY & NOW
02:27

Difficulty: Intermediate

Overview: Sometimes you'll need to make a date formulaically, rather than by simply typing it out. The DATE function allows one argument each for the year, month, and day, and outputs an excel recognised date.

DATE
06:09
Challenge: Turn a text date into an Excel date
00:17
Functions Cheat Sheet
00:10
Quiz: Can you use functions in Excel?
6 questions
+ 4. Your Data Analysis Strategy
21 lectures 01:12:25
Your Data Analysis Strategy
01:00

Difficulty: Easy

Overview: Before you get started learning how to create charts and graphs, you must ensure that your data is ready and in the right format. In this lesson we'll look at creating manual summary tables with functions like SUMIF, COUNTIF, and AVERAGEIF.

Preparing your data for charts
02:22
Challenge: Prepare Data for a Chart
00:06

Diffculty: Easy

Overview: In this lesson you'll learn how to create a basic pie or donut chart.

Pie & Donut Charts
05:43

Difficulty: Easy

Overview: In this lesson you'll learn how to create a basic line chart for a time series data set.

Line Charts
04:33
Challenge - Create a pie chart
00:04

Difficulty: Intermediate

Overview: In this lesson you'll learn how to create basic column and bar charts, as well as using stacked bar charts.

Bar & Column Charts
06:48
Challenge: Create a Bar Chart
00:08
Challenge: Create a Stacked Bar Chart
00:12

Difficulty: Intermediate

Overview: Charts have a wide variety of options that can be used to customise the chart. In this lesson you'll learn how to edit axis formatting and how to edit the format of your chart using preset styles and by manually changing different elements.

Chart & Axis Formatting
05:16

Difficulty: Intermediate

Overview: It is possible to have multiple types of chart on a single chart object - for example a bar and a line chart together. You can also have different scales on each side of your chart if you are displaying data sets with different units or scales.

Multi Type Charts & Dual Axis Charts
04:48

Difficulty: Intermediate

Overview: The final common chart types to learn are bubble and scatter diagrams. These can be used to show the spread and relationships of a data set over two or 3 dimensions.

Scatter & Bubble Diagrams
11:01

Difficulty: Intermediate

Overview: Spark Lines are simple in-cell charts that can be used to give quick 'at a glance' views of trends and metrics. In this lesson you'll learn the different types of Spark Lines and how to create them.

Create in-cell charts with Sparklines
04:56

Difficulty: Intermediate

Overview: I have a confession; I wish I could have taught you this lesson on Pivot Tables before all the others in this course so far (but you'd have missed a lot along the way).

Pivot Tables are where the 'magic' happens in Excel - where you can analyse large volumes of data in seconds, without using functions.

Everything you've learned so far - from organising data to enriching data using functions goes into making great pivot tables.

In this first lesson you'll just learn how to create some basic Pivot Tables, and in later lessons of this module you'll learn how to use them for advanced functionality, and how to create Pivot Charts - which are vastly quicker and easier to create than normal charts.

An Introduction to Pivot tables
08:30
Challenge: Create a Pivot Table
00:10

Difficulty: Intermediate

Overview: Pivot Tables can do much more than sum, count and average. In this lesson we'll cover the built in calculation types available.

Pivot Table Calculations
05:18
Challenge: Create a Pivot Table with a Percentage Summary
00:05

Difficulty: Advanced

Overview: Where Excel doesn't have the ready to use calculation you need, you can create a custom field which is made up of a calculation using regular Excel functions.

Pivot Table Custom Fields
03:24
Challenge: Create a Pivot Table with a Custom Field
00:07

Difficulty: Intermediate

Overview: You learned how to create regular charts earlier in this module; now let's take a look at Pivot Charts - which can be automatically generated using a Pivot Table.

Save time & effort by usng Pivot Charts
07:18
Quiz: Can you analyse data in Excel?
6 questions
A small favor...
00:32
+ 5. Your Dashboard Playbook
7 lectures 01:14:26
Your Dashboard Playbook
00:53

Difficulty: Intermediate

Overview: In this lesson you'll learn how to create red/amber/green metrics on your dashboards.

How to create red/amber/green metrics
06:51

Difficulty: Intermediate

Overview: In this lesson you'll learn how to use conditional formatting to create up / down arrows for your dashboards.

How to create up/down metrics
03:35

Difficulty: Easy

Overview: List style dashboards are probably the easiest and often the most effective type of report to create. They lack charts but can display metrics & their movement extremely effectively. In this lesson you'll learn a simple and repeatable method to create a list style dashboard.

How to design list style dashboards
26:30

Difficulty: Intermediate

Overview: The alternative to list style dashboards is one based on charts. These can be more appropriate where displaying trends is more key, or where the report audience work better with charts. In this lesson you'll learn a simple method for creating a chart style dashboard.

How to design chart style dashboards
22:58

Difficulty: Advanced

Overview: Slicers are extremely powerful tools to include on dashboards, but if your report contains multiple data sets, even with common fields, slicers cannot be joined across data sets. There is a workaround for this - in this lesson you'll learn how to stack data sets so that slicers can work across your whole report.

How to create a seamless self service dashboard
12:35
Choosing the right method for distribution
01:04
Quiz: Can you create dashboards in Excel?
6 questions
+ 6. Your Excel Automation Machine
24 lectures 02:20:49
Your Excel Automation Machine
00:59

Difficulty: Easy

Overview: In this lesson you'll learn how to open and navigate the VBA window, which is where all macro coding takes place.

An introduction to the VBA window
06:55

Difficulty: Easy

Overview: Excel has a built in 'recorder' which creates VBA code to replicate simple tasks. In this lesson we'll record some simple tasks using the macro recorder and examine the code that Excel produces.

Using the Macro Recorder for Basic Tasks
06:10

Difficulty: Easy

Overview: Once you've created your first macros, it can be useful to create buttons to run them. In this lesson we'll look at how to create standard buttons and also how to create custom buttons using shapes.

Creating buttons and launching macros
02:45
How to run, debug and step through a macro
01:51

Difficulty: Intermediate

Overview: When working with VBA there are a number of ways to reference cells, ranges, workbooks and sheets. In this lesson you'll learn the most effective ways to reference objects in your code.

Referencing spreadsheet objects in VBA
13:06

Difficulty: Intermediate

Overview: Download & study the attached fact sheet which covers how to copy and paste correctly using VBA, and how to clear ranges when required.

How to copy and paste using VBA
04:00

Difficulty: Intermediate

Overview: Remember that MR button on your calculator back at school? You could use it to store numbers in memory, and then use them in subsequent calculations. VBA has a similar but much more powerful concept called variables. A variable is kind of like a box where you can store different pieces of data. In this lesson we'll look at the most common kinds of variables such as string (text), date, integer, boolean (true/false) and variant.

How to store and recall data using variables
09:20
Challenge: Create a user entered variable
00:09
How to find the edges of your data using VBA
01:00
Challenge: Create a VLOOKUP using VBA
00:10
How to create text strings in VBA
01:05

Difficulty: Intermediate

Overview: Many actions that you'd usually use functions for on a spreadsheet can be performed quicker in VBA using If and Case statements. They perform a similar action to an IF function (Case statements allow for a larger number of permutations), but are written as VBA rather than as functions.

How to create If and Case Statements
09:44
Challenge: Create a Case Select Statement
00:08

Difficulty: Advanced

Overview: Loops are perhaps the most powerful element of VBA, and certainly allows for tasks to be automate more readily than any other element of VBA. A loop allows you to perform the same code over and over, only stopping when you tell it to. There are a number of different types of loop, and in this lesson you'll learn how to use them effectively.

The power of loops
08:33
Challenge: Create a loop
00:10
How to add comments to your code
00:36

Difficulty: Intermediate

Overview: Download the attached fact sheet to learn how to open and close Excel files using VBA, and how to switch between which workbook your code applies to.

How to open and close files
07:21
Challenge: Open a file and import data using VBA
00:10

Difficulty: Advanced

Case Study: In this lesson we'll automate the list style dashboard we created in the previous module, by prompting the user to open a new file, which is then imported and the dashboard can be refreshed using only VBA.

Case study: Build an automated dashboard
13:52

Difficulty: Advanced

Challenge: In this case study we take a messy data set - an export from a timesheet software package, and use VBA to cleanse the data into a well organised table.

Case Study: Build a tool to convert time-sheets into pay slips
33:10
Event based macros
02:34

Difficulty: Advanced

Overview: VBA also has the capability to create custom forms. In this lesson we'll look how to create a basic data entry form, and how to load it, and how to store the data that is entered.

VBA Forms: create custom pop up menus
16:43
Challenge: Build a questionnaire pop up form and store the data in a table
00:14
Quiz: Can you use VBA?
6 questions