Complete Excel Course: From Beginner to Superhero in 9 Hours
4.6 (34 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.
228 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Complete Excel Course: From Beginner to Superhero in 9 Hours to your Wishlist.

Add to Wishlist

Complete Excel Course: From Beginner to Superhero in 9 Hours

Learn Advanced Excel skills to make you an Excel Superhero
4.6 (34 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.
228 students enrolled
Created by Alan Murray
Last updated 11/2016
English
Current price: $10 Original price: $70 Discount: 86% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 8.5 hours on-demand video
  • 19 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Learn some incredible Excel skills and techniques
  • Learn the most popular Excel formulas for business (over 30 covered in the course)
  • Be equipped to find a solution to any Excel problem
  • Understand the new features to Excel 2013
View Curriculum
Requirements
  • A basic understanding of Excel. This course begins from about mid-introduction level.
  • A version of Microsoft Excel. The course is performed using Excel 2013, but any version is ok.
Description

Become an Excel superhero. Learn the skills to find the solution to any Excel problem.

This course starts with the basics of formulas and managing spreadsheets and quickly moves to advanced Excel techniques required for creating powerful, dynamic and visually appealing spreadsheets.

It covers;

  • Using PivotTables to summarise large amounts of data.
  • Over 30 different Excel functions to handle any problem including INDEX, VLOOKUP, LEN, MID, IF, MATCH, and INDIRECT.
  • Visualising results with charts.
  • Conditional Formatting to highlight data comparisons, progress and issues.
  • Clean up messy data with formulas, Flash Fill and other Excel features.
  • Using macros to automate repetitive tasks.
  • And much much more.

You will receive;

  • Support from the Computergaga team.
  • Lifetime access.
  • Exercises throughout to re-enforce the content.
  • Fun and no-nonsense lessons.

Don't delay. Superhero's take action. Grab a coffee and let's get started. Your journey towards becoming an Excel superhero starts today.

Who is the target audience?
  • Those who want to be amazing at Excel and are willing to accept the responsibility of being an Excel superhero
Compare to Other Excel Courses
Curriculum For This Course
100 Lectures
08:52:54
+
Introduction
2 Lectures 00:53
Introduction
00:44

This lecture contains the exercise files used throughout the course. Use them to follow along and take the exercises. Each file is named after the lecture number and title.

Download the Exercise Files
00:08
+
Working with Windows
3 Lectures 12:14

In this lecture we look at the Freeze Panes feature to fix parts of your worksheet to ensure they are visible at all times. This is typically used for column headings so that you can see what data relates to as you scroll down the worksheet. This tutorial covers;

  • Freezing the column headings in a worksheet
  • Freezing rows and columns in a worksheet at the same time

Applying Freeze Panes is essential for large worksheets to make them easier to navigate and work with.

Preview 04:47

Splitting the panes of a worksheet allows you to reference different areas of the worksheet at the same time. The lecture covers;

  • Splitting panes to make comparing records in a large worksheet easier
  • Using a shortcut to split panes in one movement of the mouse
Preview 04:19

If you need to work on multiple Excel files at the same time, you may benefit from making them all visible on screen at once. Excel provides features to easily do this. This tutorial covers;

  • Arranging multiple windows on screen at the same time
  • Understanding the 4 different ways of viewing multiple workbooks on screen
  • How to return to the single window display
Working with Multiple Workbooks
03:08
+
Sorting and Filtering
3 Lectures 23:26

Lists in Excel can be very large. In fact Excel worksheets can contain as many as 1,048,576 rows of data.

Sorting enables you to easily change the order of a list. A list could be sorted in date order, or alphabetical ,or even by the order amount as largest to smallest.

This tutorial covers;

  • The fastest way to sort a list in Excel
  • Sorting lists by different data types such as date, number or text
  • A common problem when sorting records
  • Sorting a list of records by multiple levels
Sorting Lists
08:18

A key technique used to manipulate and analyse a large list of data in Excel, is filtering. Filtering records in Excel is easy, yet incredibly powerful and versatile.

A list can be filtered to only show the necessary records ready for printing, exporting or to view aggregate totals.

This tutorial covers;

  • Filtering a list of records by a text, date and numerical columns
  • Filter a list using multiple columns
  • How to instantly recognise a filtered list when opening a spreadsheet
  • Quickly removing all the filters from a table and resetting it to normal
  • Viewing totals for a filtered list such as the sum, count or average for a column
Filtering Lists
11:05

In addition to sorting and filtering records in a list by value, you can also sort and filter by the colour of the cell or font.

This technique works awesomely well with Conditional Formatting (covered later in this course) as a way of identifying and then working with records that meet certain criteria.

Sorting and Filtering by Colour
04:03

Open the sorting_and_filtering.xlsx file from the previous tutorials and use the Excel sorting and filtering tools to answer these questions.

Sorting and Filtering Quiz
4 questions
+
Getting Started with Formulas
10 Lectures 52:53

Get started with learning to write formulas in Excel. Formulas are the foundation of Excel's incredible power. To become an Excel superhero, you must be confident in being able to learn and work with any formula.

This tutorial covers;

  • Writing formulas using basic arithmetic (addition, subtraction, multiplication and division)
  • Referencing cells and using constants in a formula
  • The rule of BODMAS that determines the order of calculation
Writing Formulas
09:43

When writing formulas it is common to need to calculate percentages. For example you may need to calculate a discount for a client, add VAT to a product price or calculate a 20% tax savings. This tutorial covers;

  • Calculate the % of a value
  • Increase a value by a certain percentage
  • Decrease a value by a certain percentage
  • Calculate what % one value is of another
Calculating Percentages
04:56

Excel has over 450 functions in its library. A function is a built-in formula. Knowing how to write your formula is a very important step on the way to becoming an Excel superhero, but most of the time you will be working with the built-in functions of Excel.

This tutorial covers;

  • The 5 aggregate functions of Excel (SUM, COUNT, AVERAGE, MAX and MIN)
  • Understanding function syntax making you ready for any function
Using Functions
11:19

The COUNTA and COUNTBLANK functions are two variations of the COUNT function covered earlier in this course. The COUNT function only counts numbers within a range. COUNTA is used to count all the non-empty cells in a range, and the COUNTBLANK function is used to count the empty cells in a range.

This tutorial covers;

  • Ways of finding a function when needed in a given situation
  • An example of the COUNTA function being used
  • An example of the COUNTBLANK function being used
The COUNTA and COUNTBLANK Functions
04:52

The Excel function wizard can be used to assist you in writing a formula. It provides explanations for the parts of the function, results for the information your providing and will also make minor corrections to mistakes.

This tutorial covers;

  • The COUNTIF function
  • Using the function wizard to help find the correct function to use in a given situation
  • Using the function wizard to troubleshoot and correct a mistake in our formula
The Excel Function Wizard
08:02

When referencing a cell from a formula you are using relative cell references. This ensures that when you copy the formula, the reference moves with you. Dollar signs are used to fix a cell reference, making it an absolute reference. This tutorial covers;

  • An example where absolute cell references are used
  • A shortcut to changing the cell reference type
  • An introduction to mixed references
Absolute Cell References
05:33

Creating a defined name can provide an easier way of referencing cells in an Excel workbook, especially when referencing across multiple sheets. It provides a meaningful name that is unique on that workbook.

It can be recalled quickly when writing formulas and also used to select ranges quickly, making it awesome with larger lists.

This tutorial covers;

  • The benefits of using defined names
  • How to create a defined name
  • Naming conventions and a common mistake when naming ranges
  • Using a defined name to quickly select a range
  • How to view, edit and delete existing defined names
  • Using a defined name when writing a formula
Defined Names
07:30

Exercise 1: Writing Formulas
00:18

Exercise 2: Mixed References
00:25

Exercise 3: Using Functions
00:14
+
Using Logical Functions
3 Lectures 26:41

The IF function is used to test a condition and then take an alternative action for whether the condition is met or not. For example, if a customer orders over a £500 then apply a 10% discount, otherwise do not.

It is a logical function of Excel, and is sometimes referred to as a conditional function or a decision making function.

This tutorial covers;

  • How to write the IF function in Excel
  • Understanding the functions syntax
  • An example applying a discount to a customer if they order over a certain quantity
  • An example showing testing a cell contain text before applying a discount
  • An example that tests to see if a date has passed, and then takes a different action whether it has or not
The IF Function
12:12

The AND and OR logical functions of Excel are used to test multiple conditions. They are commonly used with various Excel features including the IF function to perform more advanced decision making.

Any Excel function can be nested within another for more powerful calculations.

This tutorial covers;

  • An explanation of the AND and OR logic and the syntax of the two functions
  • Examples of the AND and OR functions in use
  • An introduction to the art of nesting functions in Excel
Testing Multiple Conditions
02:43

As awesome as the The IF function is, it can only test one condition, and perform one action if the test is true and another if the test is false. If we need the function to perform a more advanced condition, or perform multiple actions we will need to embed other functions for these tasks. This is a technique known as nesting functions.

This tutorial covers;

  • Nesting the AVERAGE function within the IF function
  • Adding the AND function to the formula to test multiple conditions
Nesting Functions to Test Multiple Conditions
11:46
+
Using Lookup Functions
7 Lectures 50:41

The VLOOKUP function is a lookup and reference function. It searches for a record of data in a list and returns information from that record. For example, it could search a list of orders and return the value of an order that you ask for.

VLOOKUP stands for a vertical lookup. It gets this name because it looks down columns.

This tutorial covers;

  • An explanation of the VLOOKUP function and when you would use it
  • Learning the syntax of the VLOOKUP function
  • Using the VLOOKUP function to return the price and units in stock for a particular product in a list
  • A recap on the use of an absolute cell reference
  • A demonstration of the 'Range Lookup' argument
Preview 13:15

The VLOOKUP function is commonly used to find unique entries such as an order, employee or service and return information about that entry.

However, it can also be used to find data within a range. To do this the final argument of the VLOOKUP function known as 'Range Lookup' needs to be omitted or set to True.

This tutorial covers;

  • A detailed explanation of the 'Range Lookup' argument
  • An example of the VLOOKUP function returning exam grades by setting range lookup to True
  • Using the Function Wizard for assistance in understanding a function
VLOOKUP for the Closest Match
06:03

The HLOOKUP function is used to search for a record of data along a row and return information about that record from a different row. For example, it could search a list of employees and return the start date of the one you search for.

This tutorial covers;

  • An explanation of the HLOOKUP function and when you would use it
  • Learning the syntax of the HLOOKUP function
  • Using the HLOOKUP function to return the price and units in stock for a particular product in a list
The HLOOKUP Function
05:05

The MATCH function can be used to return the column index number for the VLOOKUP function. By nesting the MATCH function within the column index number of VLOOKUP, the function can automatically find the necessary column number, making the function more flexible and durable.

This tutorial covers;

  • An understanding of the MATCH function
  • The benefits of using it with VLOOKUP
  • How to use MATCH to return the column index number
VLOOKUP with Nested MATCH Function
07:31

The INDEX and MATCH functions together provide an extremely flexible lookup formula. Look for a record or entry in any column of a table and return related information from a different column of that table. This formula solves some of the limitations of VLOOKUP.

This tutorial covers;

  • The purpose of the INDEX and MATCH functions individually.
  • The advantages of this combination over the more common VLOOKUP.
  • Using INDEX and MATCH to create a reverse lookup that can look to it left.
  • Create a two way lookup that finds the row and column of the data for a very flexible formula.
The INDEX and MATCH Functions
10:02

The #N/A error message is returned when functions such as VLOOKUP and MATCH cannot find what you asked them too. This message can be hidden if required, or an alternative action taken such as looking on a different sheet for it, or displaying different text such as “Not Found”.

This tutorial covers;

  • Using the ISNA function to detect the presence of a #N/A error.
  • Combining this with the IF function to take the appropriate action.
  • Displaying text in the cell instead of the error.
  • Using the new IFNA function.
Handling the #N/A Error Message
08:21

Exercise 1: Lookup Functions
00:24
+
Protecting and Sharing Excel Files
4 Lectures 22:17

If a workbook is saved to a shared drive you may want to prevent unauthorised access to the file. Encrypt the file with a password to ensure that only authorised personnel may open the file.

Encrypt an Excel File
02:37

A workbook can be protected to lock the structure and/or windows of a workbook.

Protecting the structure locks sheet related activity such as users unhiding, renaming, deleting or inserting sheets. Protecting the windows prevents users from using features such as Freeze Panes and Arrange All.

This tutorial covers;

  • Hiding a worksheet
  • Applying workbook protection to prevent users from unhiding the worksheet
Workbook Protection
04:44

Worksheet protection can be used to protect different elements of a spreadsheet. It can be used to protect your formulas and prevent users from unhiding columns among other things.

This tutorial covers;

  • Unlocking the cells of a spreadsheet you do not want to protect
  • Setting the cells containing formulas to hidden
  • Protecting the worksheet with a password
  • A demonstration of the improved usability of a protected worksheet
Worksheet Protection
09:18

Excel spreadsheets have exclusive access by default. This means that only one person at a time can modify them. Sharing a workbook will allow multiple people to edit a spreadsheet at the same time.

This tutorial covers;

  • Sharing a workbook so more than one user can open it at the same time
  • Viewing what users have that spreadsheet open
  • Modifying the settings for workbook sharing
  • Setting a workbook back to exclusive access
Sharing a Workbook
05:38
+
Using Conditional Formatting
7 Lectures 34:18

Apply Conditional Formatting to your spreadsheets to format cells based on a condition. For example, you could format cells red if a deadline is missed, a task is due to start, or a target is achieved.

This tutorial covers;

  • The different types of rules you can create using Conditional Formatting.
  • Formatting the cells that contain a date in the past.
  • Adding a second conditional format to format cells if the date occurs within the next 2 weeks.
Preview 09:49

Data bars and icon sets are visual indicators that can be applied to bring our attention to values that meet certain criteria.

This tutorial covers;

  • Applying icon sets to a range of values to visualise targets being achieved.
Using Icon Sets
05:34

If you are going to be using conditional formatting on a spreadsheet you will need to be able to manage the rules. Excel makes it easy to be able to move, edit or delete conditional formatting rules that exist on a spreadsheet.

This tutorial covers;

  • Viewing all the conditional formatting rules on a worksheet.
  • Editing an existing rule.
  • The importance of the order that conditional formatting rules run.
Managing your Conditional Formatting Rules
05:25

Applying conditional formatting to individual cells is fantastic, but if your data was quite wide, formatting the entire row will make it more visible.

To format the entire row we will need to create the rule a little bit different to the way we have been doing.

This tutorial covers;

  • The benefits of formatting the entire row.
  • Using a formula for a conditional formatting rule.
  • Using a mixed reference in the formula.
Apply Conditional Formatting to the Entire Row
06:23

Exercise 1: Using Conditional Formatting
00:11

Exercise 2: Highlight Unmatched Items
00:18

Exercise 2 Answer: Highlight Unmatched Items
06:37
+
Validating Data Entry
9 Lectures 25:49

If certain text entries need to be a specific number of characters, you can apply validation to a prevent entries that are not that particular text length.

This tutorial looks at validating data entry to be exactly 5 characters in length.

Validate the Text Length of an Entry
02:31

You can validate date entries in Excel to ensure that they are within a certain range or only a date that occurs this week etc.

This tutorial covers;

  • Setting a data validation rule to prevent the entry of future dates
  • Using the TODAY function for validating date entries
Validate Date Entries
02:26

Drop down lists provide an easy and user friendly way for people to enter text data. This ensures that the data is valid. Entries that do not appear in the list will not be allowed.

This tutorial covers;

  • Creating a data validation list by entering the values yourself
  • Creating a data validation list by referring to a range of cells on another sheet
  • Using the drop down list to enter data
Validate using a Drop Down List
04:25

Messages can be created to assist the user with their data entry. They can be used to warn the user of problems and explain common data entry issues.

This tutorial covers;

  • Setting up input messages to inform the user of how the data should be entered
  • Setting up error alerts to prevent incorrect data entry and explain where they may have gone wrong
  • Change the style of the error alert to warn the user, but not prevent them from entering the data
Create your own Input and Error Messages
05:16

You can create a drop down list that is dependent upon the selection made in another list. By doing this a large list can be shortened by only displaying the relevant options.

This tutorial covers;

  • Creating a drop down list of products which is dependent on what product category was chosen
  • Using the INDIRECT function
  • Using defined names in a workbook
Create Dependent Drop Down Lists
06:37

Exercise 1: Validating Entry of Dates
00:14

Exercise 1 Answer: Validating Entry of Dates
02:15

Exercise 2: Validating Entry of Values
Processing..

Exercise 2 Answer: Validating Entry of Values
02:05
+
Analysing Data
6 Lectures 25:29

The subtotals feature of Excel enables you to insert subtotal rows at particular points within a list. A selection of different aggregate functions can be used for the subtotal rows including sum, count and average.

This tutorial covers;

  • Applying subtotals to a list to sum values by each salesperson in the list.
  • Using the outline buttons to view the totals at different levels of the outline.
  • Applying multiple subtotals.
Inserting Subtotal Rows
07:45

The SUMIF function will enable you to sum all the values in a list dependent on a condition. For example, sum all the orders for a specific product, or for a particular month.

This is possibly the most popular analysis function of Excel. For counting and averaging, the COUNTIF and AVERAGEIF functions can be used respectively.

This tutorial covers;

  • Writing a SUMIF function to sum all the order amounts where the order was taken by a specific salesperson.
  • Using a drop down list to make the formula interactive.
The SUMIF Function
05:11

The SUMIFS and COUNTIFS functions can be used to sum and count values based on multiple conditions.

This tutorial covers;

  • Understanding the SUMIFS and COUNTIFS functions syntax
  • Using them to sum or count the order amounts for orders by a particular salesperson, and for a particular product category
SUMIFS and COUNTIFS
05:57

The SUBTOTAL function can be used to perform a function on a filtered list. The SUBTOTAL function will ignore the hidden rows on filtered lists. Functions that SUBTOTAL can perform include sum, count, average and more.

This tutorial covers;

  • Using the SUMIFS and COUNTIFS functions to find the total and count of orders in a list.
  • Filtering the list of orders to see the functions working.
The SUBTOTAL Function
06:18

Exercise 1: Applying Subtotals
00:07

Exercise 2: Using SUMIFS
00:10
8 More Sections
About the Instructor
Alan Murray
4.4 Average rating
497 Reviews
4,413 Students
5 Courses
Founder of Computergaga

Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.

He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.

Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.

Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.